[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| insert_trial_balance_wu will insert data into |
| tb table if transfer to GL is spawned by an |
| application other than Payables and acctng class|
| code is registered in Post Programs. |
| 23-Sep-2008 rajose bug#7364921 Upgraded invoices not appearing in |
| the TB report for a given date range. |
| 19-Nov-2008 rajose bug#7552876 data manager upload_pvt procedure |
| errors out with ORA-01403: no data found |
| 27-Nov-2008 rajose bug#7600550 TB remodeling phase 4. Addresses the|
| issue where Open Account AP balances Listing |
| shows no data if new Journal source is added to |
| Definition part of QA bug 7431923 |
| 21-Jan-2008 rajose bug#7717479 data not appearing for definition |
| code rebuild of reporting ledger |
| 12-Mar-2009 VGOPISET Trial balance not rebuild for 11i Data for the |
| Definition Code defined by SEGMENT |
| 19-Mar-2009 rajose bug#8333978 Open Account Balances Data Manager |
| main program is taking time to complete. |
| 22-Jul-2009 nksurana 8348885:Added NO_INDEX on XAL in TBInsert script|
| to ignore usage of MIS_XLA_AE_LINES_N1 index. |
| 23-Feb-2011 VGOPISET 11770090:TB Data Manager errors when trying to |
| insert data for Third Party Merge in table |
| XLA_TB_USER_TRANS_VIEW in populate_user_trans_v |
| 05-Jan-2012 NMIKKILI 13464353: Data Manager Program ends in error |
| when it is cancelled and only when FND log is |
| enabled.Made a codefix to show an exception even|
| when fnd log is disabled |
| 13-Jan-2012 NMIKKILI Adding nvl on minimum_accountable_unit to avoid |
| null values for entered_rounded_cr |
+===========================================================================*/
--
-- Global Variables - WHO Column Information
--
g_request_id NUMBER(15);
g_tb_insert_sql VARCHAR2(32000);
C_TB_INSERT_SQL CONSTANT VARCHAR2(32000) := '
INSERT INTO xla_trial_balances (
record_type_code
,source_entity_id
,event_class_code
,source_application_id
,applied_to_entity_id
,applied_to_application_id
,gl_date
,trx_currency_code
,entered_rounded_dr
,entered_rounded_cr
,entered_unrounded_dr
,entered_unrounded_cr
,acctd_rounded_dr
,acctd_rounded_cr
,acctd_unrounded_dr
,acctd_unrounded_cr
,code_combination_id
,balancing_segment_value
,natural_account_segment_value
,cost_center_segment_value
,intercompany_segment_value
,management_segment_value
,ledger_id
,definition_code
,party_id
,party_site_id
,party_type_code
,ae_header_id
,generated_by_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date)
SELECT /*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */
DECODE(xdl.applied_to_entity_id
,NULL
,''SOURCE''
,''APPLIED'') record_type_code
,xah.entity_id source_entity_id
,xet.event_class_code event_class_code
,xah.application_id source_application_id
,xdl.applied_to_entity_id applied_to_entity_id
,xdl.applied_to_application_id applied_to_application_id
,xah.accounting_date gl_date
,xal.currency_code trx_currency_code
-- changes for incorrect trial balance amounts bug 6366295
-- entered_rounded_dr
,decode(nvl(sum(xdl.unrounded_entered_cr), sum(xdl.unrounded_entered_dr)), null, null,
CASE xlo.acct_reversal_option_code
WHEN ''SIDE'' THEN
CASE SIGN(
NVL(SUM(xdl.unrounded_entered_dr),0) - NVL(SUM(xdl.unrounded_entered_cr),0)+
NVL(SUM(xdl.doc_rounding_entered_amt), 0)
)
WHEN -1 THEN null
WHEN 1 THEN
ROUND(
(NVL(SUM(xdl.unrounded_entered_dr),0) - NVL(SUM(xdl.unrounded_entered_cr),0)+
NVL(SUM(xdl.doc_rounding_entered_amt), 0))
/nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
+ decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
*nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
ELSE
CASE SIGN(NVL(SUM(xdl.unrounded_accounted_dr),0) - NVL(SUM(xdl.unrounded_accounted_cr),0)
+NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
WHEN -1 THEN null
WHEN 1 THEN 0
ELSE DECODE(sum(xdl.unrounded_accounted_cr), 0, to_number(null), 0)
END
END
ELSE DECODE(sum(xdl.unrounded_accounted_cr), null ,
ROUND(
(SUM(xdl.unrounded_entered_dr)-NVL(SUM(xdl.doc_rounding_entered_amt), 0))
/nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
+decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',(.5-power(10, -30)),''DOWN'',-(.5-power(10, -30)),0))
*nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
,ROUND(
SUM(xdl.unrounded_entered_dr) /nvl(minimum_accountable_unit, power(10, -1* precision))
+decode(rounding_rule_code,''NEAREST'', 0,''UP'',(.5-power(10, -30)),''DOWN'',-(.5-power(10, -30)),0))
*nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
)
END ) entered_rounded_dr
-- entered_rounded_cr
,decode(nvl(sum(xdl.unrounded_entered_cr), sum(xdl.unrounded_entered_dr)), null, null,
CASE xlo.acct_reversal_option_code
WHEN ''SIDE'' THEN
CASE SIGN(
NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0)+
NVL(SUM(xdl.doc_rounding_entered_amt), 0)
)
WHEN -1 THEN null
WHEN 1 THEN
ROUND(
(NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0)+
NVL(SUM(xdl.doc_rounding_entered_amt), 0))
/nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
+ decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
*nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
ELSE
CASE SIGN(NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0)
+NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
WHEN -1 THEN null
WHEN 1 THEN 0
ELSE DECODE(sum(xdl.unrounded_accounted_cr), 0, 0, null)
END
END
ELSE DECODE(SUM(xdl.unrounded_entered_cr), null, to_number(null) ,
ROUND(
(SUM(xdl.unrounded_entered_cr) +
NVL(SUM(xdl.doc_rounding_entered_amt), 0))
/nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision)) -- Bug 13464353
+decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
*nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))) -- Bug 13464353
END ) entered_rounded_cr
--entered_unrounded_dr
,CASE SIGN(NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0)
)
WHEN 1 THEN null
WHEN -1 THEN (NVL(SUM(xdl.unrounded_entered_dr),0) - NVL(SUM(xdl.unrounded_entered_cr),0))
ELSE 0
END entered_unrounded_dr
--entered_unrounded_cr
,CASE SIGN(NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0)
)
WHEN 1 THEN (NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0))
WHEN -1 THEN NULL
ELSE 0
END entered_unrounded_cr
-- accounted_rounded_dr
, decode(nvl(sum(xdl.unrounded_accounted_cr), sum(xdl.unrounded_accounted_dr)), null, null,
CASE xlo.acct_reversal_option_code
WHEN ''SIDE'' THEN
CASE SIGN(
NVL(SUM(xdl.unrounded_accounted_dr),0) - NVL(SUM(xdl.unrounded_accounted_cr),0)-
NVL(SUM(xdl.doc_rounding_acctd_amt), 0)
)
WHEN -1 THEN null
WHEN 1 THEN
ROUND(
(NVL(SUM(xdl.unrounded_accounted_dr),0) - NVL(SUM(xdl.unrounded_accounted_cr),0)-
NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
/nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
+decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
*nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
ELSE
CASE SIGN(NVL(SUM(xdl.unrounded_entered_dr),0) - NVL(SUM(xdl.unrounded_entered_cr),0)-
NVL(SUM(xdl.doc_rounding_entered_amt), 0))
WHEN -1 THEN null
WHEN 1 THEN 0
ELSE DECODE(sum(xdl.unrounded_accounted_cr), 0, to_number(null), 0)
END
END
ELSE
decode(SUM(xdl.unrounded_accounted_cr), null,
ROUND(
(SUM(xdl.unrounded_accounted_dr)-NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
/nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
+decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
*nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
,ROUND(
SUM(xdl.unrounded_accounted_dr) /nvl(minimum_accountable_unit, power(10, -1* precision))
+decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
*nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
)
END) accounted_rounded_dr
-- accounted_rounded_cr
, decode(nvl(sum(xdl.unrounded_accounted_cr), sum(xdl.unrounded_accounted_dr)), null, null,
CASE xlo.acct_reversal_option_code
WHEN ''SIDE'' THEN
CASE SIGN(
NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0)+
NVL(SUM(xdl.doc_rounding_acctd_amt), 0)
)
WHEN -1 THEN null
WHEN 1 THEN
ROUND(
(NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0)+
NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
/nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
+decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
*nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
ELSE
CASE SIGN(NVL(SUM(xdl.unrounded_entered_cr),0) - NVL(SUM(xdl.unrounded_entered_dr),0)+
NVL(SUM(xdl.doc_rounding_entered_amt), 0))
WHEN -1 THEN null
WHEN 1 THEN 0
ELSE DECODE(sum(xdl.unrounded_accounted_cr), 0, 0, null)
END
END
ELSE DECODE(SUM(xdl.unrounded_accounted_cr), null, to_number(null) ,
ROUND(
(SUM(xdl.unrounded_accounted_cr) +
NVL(SUM(xdl.doc_rounding_acctd_amt), 0))
/nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
+decode(xlo.rounding_rule_code,''NEAREST'', 0,''UP'',.5-power(10, -30),''DOWN'',-(.5-power(10, -30)),0))
*nvl(fdc.minimum_accountable_unit, power(10, -1* fdc.precision))
)
END) accounted_rounded_cr
-- acctd_unrounded_dr
,CASE SIGN(NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0)
)
WHEN 1 THEN NULL
WHEN -1 THEN (NVL(SUM(xdl.unrounded_accounted_dr),0) - NVL(SUM(xdl.unrounded_accounted_cr),0))
ELSE 0
END acctd_unrounded_dr
-- acctd_unrounded_cr
,CASE SIGN(NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0)
)
WHEN 1 THEN (NVL(SUM(xdl.unrounded_accounted_cr),0) - NVL(SUM(xdl.unrounded_accounted_dr),0))
WHEN -1 THEN NULL
ELSE 0
END acctd_unrounded_cr
--end changes bug 6366295
,xal.code_combination_id code_combination_id
,$bal_segment$ balancing_segment_value
,$acct_segment$ natural_account_segment_value
,$cc_segment$ cost_center_segment_value
,$ic_segment$ intercompany_segment_value
,$mgt_segment$ management_segment_value
,xah.ledger_id ledger_id
,xtd.definition_code DEFINITION_code
,xal.party_id party_id
,xal.party_site_id party_site_id
,xal.party_type_code party_type_code
,xah.ae_header_id ae_header_id
,''SYSTEM'' generated_by_code
,SYSDATE creation_date
,:1 -- g_user_id
,SYSDATE
,:2 -- g_user_id
,:3 -- g_login_id
,:4 -- g_request_id
,:5 -- g_prog_appl_id
,:6 -- g_program_id
,sysdate
FROM
xla_ae_headers xah
,xla_ae_lines xal
,xla_distribution_links xdl
,xla_ledger_options xlo
,fnd_currencies fdc
,gl_ledgers gl
,gl_code_combinations gcc
,xla_event_types_b xet
,xla_tb_definitions_b xtd
$l_from$
WHERE xah.ae_header_id BETWEEN :7 AND :8
AND xah.upg_batch_id IS NULL -- added bug 6704677
$l_ledger_where$
AND xah.gl_transfer_status_code IN (''Y'',''NT'')
AND xah.application_id = xal.application_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.application_id = xdl.application_id (+)
AND xal.ae_header_id = xdl.ae_header_id (+)
AND xal.ae_line_num = xdl.ae_line_num (+)
AND xtd.enabled_flag = ''Y''
$l_where$
AND xal.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id = :coa_id
AND xah.application_id = xet.application_id
AND xah.event_type_code = xet.event_type_code
AND xlo.ledger_id(+) = xah.ledger_id
AND xlo.application_id(+) = xah.application_id
AND xah.ledger_id = gl.ledger_id
AND xah.ledger_id = xtd.ledger_id --added bug 7359012,one definition code showing data for multilple ledgers in TB report
AND fdc.currency_code = gl.currency_code
--- remodeling
$l_accounting_class_code_where$
AND xah.event_type_code <> ''MANUAL''
--- remodeling
GROUP BY
DECODE(xdl.applied_to_entity_id
,NULL
,''SOURCE''
,''APPLIED'')
,xtd.definition_code
,xet.event_class_code
,xah.application_id
,xdl.applied_to_entity_id
,xdl.applied_to_application_id
,xal.party_id
,xal.party_site_id
,xal.party_type_code
,xah.entity_id
,xah.ledger_id
,xah.accounting_date
,xah.ae_header_id
,xal.currency_code
,xal.code_combination_id
,$bal_segment$
,$acct_segment$
,$cc_segment$
,$ic_segment$
,$mgt_segment$
,xlo.acct_reversal_option_code
,xlo.rounding_rule_code
,fdc.minimum_accountable_unit
,fdc.precision
';
C_TB_INSERT_UPG_SQL_AE CONSTANT VARCHAR2(32000) := '
INSERT INTO xla_trial_balances xtb(
record_type_code
,source_entity_id
,event_class_code
,source_application_id
,applied_to_entity_id
,applied_to_application_id
,gl_date
,trx_currency_code
,entered_rounded_dr
,entered_rounded_cr
,entered_unrounded_dr
,entered_unrounded_cr
,acctd_rounded_dr
,acctd_rounded_cr
,acctd_unrounded_dr
,acctd_unrounded_cr
,code_combination_id
,balancing_segment_value
,natural_account_segment_value
,cost_center_segment_value
,intercompany_segment_value
,management_segment_value
,ledger_id
,definition_code
,party_id
,party_site_id
,party_type_code
,ae_header_id
,generated_by_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date)
SELECT /*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */
DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED'')) record_type_code
,xah.entity_id source_entity_id
,xet.event_class_code event_class_code
,xah.application_id source_application_id
,DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',xteu.entity_id,DECODE(xteu.entity_id, xah.entity_id,NULL,xteu.entity_id)) applied_to_entity_id
,200 applied_to_application_id
,trunc(xah.accounting_date) gl_date
,xal.currency_code trx_currency_code
,SUM(NVL(xal.entered_dr,0)) entered_rounded_dr
,SUM(NVL(xal.entered_cr,0)) entered_rounded_cr
,SUM(NVL(xal.entered_dr,0)) entered_unrounded_dr
,SUM(NVL(xal.entered_cr,0)) entered_unrounded_cr
,SUM(NVL(alb.accounted_dr, 0)) acctd_rounded_dr
,SUM(NVL(alb.accounted_cr, 0)) acctd_rounded_cr
,SUM(NVL(alb.accounted_dr,0)) acctd_unrounded_dr
,SUM(NVL(alb.accounted_cr,0)) acctd_unrounded_cr
,xal.code_combination_id code_combination_id
,DECODE(fsav.balancing_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
balancing_segment_value
,DECODE(fsav.account_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
natural_account_segment_value
,DECODE(fsav.cost_crt_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
cost_center_segment_value
,DECODE(fsav.intercompany_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
intercompany_segment_value
,DECODE(fsav.management_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
management_segment_value
,xah.ledger_id ledger_id
,xtd.definition_code DEFINITION_code
,xal.party_id party_id
,xal.party_site_id party_site_id
,xal.party_type_code party_type_code
,xah.ae_header_id ae_header_id
,''SYSTEM'' generated_by_code
,SYSDATE creation_date
,-1 created_by
,SYSDATE last_update_date
,-1 last_updated_by
,-1 last_update_login
,-1 request_id
,-1 program_application_id
,-1 program_id
,SYSDATE program_update_date
FROM
ap_liability_balance alb
,xla_ae_headers PARTITION (AP) xah
,xla_event_types_b xet
,xla_tb_definitions_b xtd
$l_from$
,xla_transaction_entities_upg PARTITION (AP) xteu
,xla_ae_lines PARTITION (AP) xal
,gl_code_combinations gcc
,( SELECT /*+ NO_MERGE*/ id_flex_num
,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_BALANCING'', application_column_name, NULL)) balancing_segment
,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_ACCOUNT'', application_column_name, NULL)) account_segment
,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''FA_COST_CTR'', application_column_name, NULL)) cost_crt_segment
,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_INTERCOMPANY'', application_column_name, NULL)) intercompany_segment
,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_MANAGEMENT'', application_column_name, NULL)) management_segment
FROM fnd_segment_attribute_values fsav1 -- Need alias here also.
WHERE application_id = 101
AND id_flex_code = ''GL#''
AND attribute_value = ''Y''
GROUP BY id_flex_num) fsav
WHERE
xah.gl_transfer_status_code IN (''Y'',''NT'')
AND xah.application_id = xal.application_id
AND xah.ae_header_id BETWEEN :1 AND :2
AND xah.application_id = 200
AND xah.ledger_id = :3
AND xah.upg_batch_id IS NOT NULL
AND xah.ae_header_id = xal.ae_header_id
AND xal.code_combination_id = gcc.code_combination_id
AND xal.code_combination_id = alb.code_combination_id
AND xah.application_id = xet.application_id
AND xteu.application_id = 200
AND xteu.entity_code = ''AP_INVOICES''
AND NVL(xteu.source_id_int_1,-99) = alb.invoice_id
--AND xteu.ledger_id = alb.set_of_books_id
AND xteu.ledger_id = $l_derived_primary_ledger$
AND alb.ae_header_id IS NOT NULL
AND alb.ae_line_id IS NOT NULL
AND alb.ae_header_id = xah.completion_acct_seq_value
AND 200 = xah.completion_acct_seq_version_id
AND alb.ae_line_id = xal.ae_line_num
AND xah.upg_source_application_id = 200
AND xah.event_type_code = xet.event_type_code
AND gcc.chart_of_accounts_id = fsav.id_flex_num
$l_where$
AND xtd.ledger_id = alb.set_of_books_id
AND alb.code_combination_id = xal.code_combination_id
--- remodeling
AND xal.accounting_class_code = ''LIABILITY''
AND xah.event_type_code <> ''MANUAL''
--- remodeling
GROUP BY
DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED''))
,xah.entity_id
,xet.event_class_code
,xah.application_id
,DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',xteu.entity_id,DECODE(xteu.entity_id, xah.entity_id,NULL,xteu.entity_id))
,xah.accounting_date
,xal.currency_code
,xal.code_combination_id
,DECODE(fsav.balancing_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
,DECODE(fsav.account_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
,DECODE(fsav.cost_crt_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
,DECODE(fsav.intercompany_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
,DECODE(fsav.management_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
,xah.ledger_id
,xtd.definition_code
,xal.party_id
,xal.party_site_id
,xal.party_type_code
,xah.ae_header_id
';
C_TB_INSERT_UPG_SQL_SLE CONSTANT VARCHAR2(32000) := '
INSERT INTO xla_trial_balances xtb(
record_type_code
,source_entity_id
,event_class_code
,source_application_id
,applied_to_entity_id
,applied_to_application_id
,gl_date
,trx_currency_code
,entered_rounded_dr
,entered_rounded_cr
,entered_unrounded_dr
,entered_unrounded_cr
,acctd_rounded_dr
,acctd_rounded_cr
,acctd_unrounded_dr
,acctd_unrounded_cr
,code_combination_id
,balancing_segment_value
,natural_account_segment_value
,cost_center_segment_value
,intercompany_segment_value
,management_segment_value
,ledger_id
,definition_code
,party_id
,party_site_id
,party_type_code
,ae_header_id
,generated_by_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date)
SELECT /*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */
DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED'')) record_type_code
,xah.entity_id source_entity_id
,xet.event_class_code event_class_code
,xah.application_id source_application_id
,DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',xteu.entity_id,DECODE(xteu.entity_id, xah.entity_id,NULL,xteu.entity_id)) applied_to_entity_id
,200 applied_to_application_id
,trunc(xah.accounting_date) gl_date
,xal.currency_code trx_currency_code
,SUM(NVL(xal.entered_dr,0)) entered_rounded_dr
,SUM(NVL(xal.entered_cr,0)) entered_rounded_cr
,SUM(NVL(xal.entered_dr,0)) entered_unrounded_dr
,SUM(NVL(xal.entered_cr,0)) entered_unrounded_cr
,SUM(NVL(alb.accounted_dr, 0)) acctd_rounded_dr
,SUM(NVL(alb.accounted_cr, 0)) acctd_rounded_cr
,SUM(NVL(alb.accounted_dr,0)) acctd_unrounded_dr
,SUM(NVL(alb.accounted_cr,0)) acctd_unrounded_cr
,xal.code_combination_id code_combination_id
,DECODE(fsav.balancing_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
balancing_segment_value
,DECODE(fsav.account_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
natural_account_segment_value
,DECODE(fsav.cost_crt_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
cost_center_segment_value
,DECODE(fsav.intercompany_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
intercompany_segment_value
,DECODE(fsav.management_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
management_segment_value
,xah.ledger_id ledger_id
,xtd.definition_code DEFINITION_code
,xal.party_id party_id
,xal.party_site_id party_site_id
,xal.party_type_code party_type_code
,xah.ae_header_id ae_header_id
,''SYSTEM'' generated_by_code
,SYSDATE creation_date
,-1 created_by
,SYSDATE last_update_date
,-1 last_updated_by
,-1 last_update_login
,-1 request_id
,-1 program_application_id
,-1 program_id
,SYSDATE program_update_date
FROM
ap_liability_balance alb
,xla_ae_headers PARTITION (AP) xah
,xla_event_types_b xet
,xla_tb_definitions_b xtd
$l_from$
,xla_transaction_entities_upg PARTITION (AP) xteu
,xla_ae_lines PARTITION (AP) xal
,gl_code_combinations gcc
,( SELECT /*+ NO_MERGE*/ id_flex_num
,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_BALANCING'', application_column_name, NULL)) balancing_segment
,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_ACCOUNT'', application_column_name, NULL)) account_segment
,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''FA_COST_CTR'', application_column_name, NULL)) cost_crt_segment
,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_INTERCOMPANY'', application_column_name, NULL)) intercompany_segment
,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, ''GL_MANAGEMENT'', application_column_name, NULL)) management_segment
FROM fnd_segment_attribute_values fsav1 -- Need alias here also.
WHERE application_id = 101
AND id_flex_code = ''GL#''
AND attribute_value = ''Y''
GROUP BY id_flex_num) fsav
WHERE
xah.gl_transfer_status_code IN (''Y'',''NT'')
AND xah.application_id = xal.application_id
AND xah.ae_header_id BETWEEN :1 AND :2
AND xah.application_id = 200
AND xah.ledger_id = :3
AND xah.upg_batch_id IS NOT NULL
AND xah.ae_header_id = xal.ae_header_id
AND xal.code_combination_id = gcc.code_combination_id
AND xal.code_combination_id = alb.code_combination_id
AND xah.application_id = xet.application_id
AND xteu.application_id = 200
AND xteu.entity_code = ''AP_INVOICES''
AND NVL(xteu.source_id_int_1,-99) = alb.invoice_id
--AND xteu.ledger_id = alb.set_of_books_id
AND xteu.ledger_id = $l_derived_primary_ledger$
AND alb.sle_header_id IS NOT NULL
AND alb.sle_line_num IS NOT NULL
AND alb.sle_header_id = xah.completion_acct_seq_value
AND alb.journal_sequence_id = xah.completion_acct_seq_version_id
AND alb.sle_line_num = xal.ae_line_num
AND xah.upg_source_application_id = 600
AND xah.upg_batch_id = -5672
AND xah.event_type_code = xet.event_type_code
AND gcc.chart_of_accounts_id = fsav.id_flex_num
$l_where$
AND xtd.ledger_id = alb.set_of_books_id
AND alb.code_combination_id = xal.code_combination_id
--- remodeling
AND xal.accounting_class_code = ''LIABILITY''
AND xah.event_type_code <> ''MANUAL''
--- remodeling
GROUP BY
DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED''))
,xah.entity_id
,xet.event_class_code
,xah.application_id
,DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',xteu.entity_id,DECODE(xteu.entity_id, xah.entity_id,NULL,xteu.entity_id))
,xah.accounting_date
,xal.currency_code
,xal.code_combination_id
,DECODE(fsav.balancing_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
,DECODE(fsav.account_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
,DECODE(fsav.cost_crt_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
,DECODE(fsav.intercompany_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
,DECODE(fsav.management_segment,
''SEGMENT1'', gcc.segment1, ''SEGMENT2'', gcc.segment2, ''SEGMENT3'', gcc.segment3,
''SEGMENT4'', gcc.segment4, ''SEGMENT5'', gcc.segment5, ''SEGMENT6'', gcc.segment6,
''SEGMENT7'', gcc.segment7, ''SEGMENT8'', gcc.segment8, ''SEGMENT9'', gcc.segment9,
''SEGMENT10'', gcc.segment10, ''SEGMENT11'', gcc.segment11, ''SEGMENT12'', gcc.segment12,
''SEGMENT13'', gcc.segment13, ''SEGMENT14'', gcc.segment14, ''SEGMENT15'', gcc.segment15,
''SEGMENT16'', gcc.segment16, ''SEGMENT17'', gcc.segment17, ''SEGMENT18'', gcc.segment18,
''SEGMENT19'', gcc.segment19, ''SEGMENT20'', gcc.segment20, ''SEGMENT21'', gcc.segment21,
''SEGMENT22'', gcc.segment22, ''SEGMENT23'', gcc.segment23, ''SEGMENT24'', gcc.segment24,
''SEGMENT25'', gcc.segment25, ''SEGMENT26'', gcc.segment26, ''SEGMENT27'', gcc.segment27,
''SEGMENT28'', gcc.segment28, ''SEGMENT29'', gcc.segment29, ''SEGMENT30'', gcc.segment30,
null)
,xah.ledger_id
,xtd.definition_code
,xal.party_id
,xal.party_site_id
,xal.party_type_code
,xah.ae_header_id
';
INSERT INTO xla_trial_balances (
record_type_code
,source_entity_id
,event_class_code
,source_application_id
,applied_to_entity_id
,gl_date
,trx_currency_code
,entered_rounded_dr
,entered_rounded_cr
,entered_unrounded_dr
,entered_unrounded_cr
,acctd_rounded_dr
,acctd_rounded_cr
,acctd_unrounded_dr
,acctd_unrounded_cr
,code_combination_id
,balancing_segment_value
,natural_account_segment_value
,cost_center_segment_value
,intercompany_segment_value
,management_segment_value
,ledger_id
,definition_code
,party_id
,party_site_id
,party_type_code
,ae_header_id
,generated_by_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date)
SELECT ''SOURCE'' record_type_code
,-1 source_entity_id
,''-1'' event_class_code
,xsu.application_id source_application_id
,NULL applied_to_entity_id
,xdd.balance_date gl_date
,:1 trx_currency_code
,$ent_rounded_amt_dr$ entered_rounded_dr
,$ent_rounded_amt_cr$ entered_rounded_cr
,$ent_unrounded_amt_dr$ entered_unrounded_dr
,$ent_unrounded_amt_cr$ entered_unrounded_cr
,$acct_rounded_amt_dr$ acctd_rounded_dr
,$acct_rounded_amt_cr$ acctd_rounded_cr
,$acct_unrounded_amt_dr$ acctd_unrounded_dr
,$acct_unrounded_amt_cr$ acctd_unrounded_cr
,xdd.code_combination_id code_combination_id
,$bal_segment$ balancing_segment_value
,$acct_segment$ natural_account_segment_value
,$cc_segment$ cost_center_segment_value
,$ic_segment$ intercompany_segment_value
,$mgt_segment$ management_segment_value
,:2 ledger_id
,:3 definition_code
,NULL party_id
,NULL party_site_id
,NULL party_type_code
,NULL ae_header_id
,''SYSTEM'' generated_by_code
,SYSDATE creation_date
,:4 -- g_user_id
,SYSDATE
,:5 -- g_user_id
,:6 -- g_login_id
,:7 -- g_request_id
,:8 -- g_prog_appl_id
,:9 -- g_program_id
,sysdate
FROM
gl_code_combinations gcc
,xla_subledgers xsu
,xla_tb_defn_je_sources xjs
,xla_tb_defn_details xdd
WHERE xdd.definition_code = :10
AND xdd.owner_code = ''S''
and xdd.code_combination_id = gcc.code_combination_id
AND xsu.je_source_name = xjs.je_source_name
AND xjs.owner_code = ''S''
AND xjs.definition_code = :11
AND gcc.chart_of_accounts_id = :12
';
PROCEDURE delete_tb_log
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_tb_log';
trace('BEGIN delete_tb_log',C_LEVEL_PROCEDURE,l_Log_module);
DELETE xla_tb_logs
WHERE request_id = g_request_id;
trace('END delete_tb_log',C_LEVEL_PROCEDURE,l_Log_module);
(p_location => 'xla_tb_data_manager_pvt.delete_tb_log');
END delete_tb_log;
DELETE FROM xla_tb_def_seg_ranges
WHERE definition_code = p_definition_code;
trace('# of rows deleted = ' || SQL%ROWCOUNT
,C_LEVEL_STATEMENT
,l_Log_module);
SELECT defined_by_code
INTO l_define_by_code
FROM xla_tb_definitions_b xtd
WHERE xtd.definition_code = p_definition_code;
INSERT INTO xla_tb_def_seg_ranges
(definition_code
,line_num
,balance_date
,owner_code
,segment1_from
,segment1_to
,segment2_from
,segment2_to
,segment3_from
,segment3_to
,segment4_from
,segment4_to
,segment5_from
,segment5_to
,segment6_from
,segment6_to
,segment7_from
,segment7_to
,segment8_from
,segment8_to
,segment9_from
,segment9_to
,segment10_from
,segment10_to
,segment11_from
,segment11_to
,segment12_from
,segment12_to
,segment13_from
,segment13_to
,segment14_from
,segment14_to
,segment15_from
,segment15_to
,segment16_from
,segment16_to
,segment17_from
,segment17_to
,segment18_from
,segment18_to
,segment19_from
,segment19_to
,segment20_from
,segment20_to
,segment21_from
,segment21_to
,segment22_from
,segment22_to
,segment23_from
,segment23_to
,segment24_from
,segment24_to
,segment25_from
,segment25_to
,segment26_from
,segment26_to
,segment27_from
,segment27_to
,segment28_from
,segment28_to
,segment29_from
,segment29_to
,segment30_from
,segment30_to)
SELECT tdd.definition_code definition_code
,ROWNUM
,tdd.balance_date balance_date
,tdd.owner_code owner_code
,gcc.segment1 segment1_from
,gcc.segment1 segment1_to
,gcc.segment2 segment2_from
,gcc.segment2 segment2_to
,gcc.segment3 segment3_from
,gcc.segment3 segment3_to
,gcc.segment4 segment4_from
,gcc.segment4 segment4_to
,gcc.segment5 segment5_from
,gcc.segment5 segment5_to
,gcc.segment6 segment6_from
,gcc.segment6 segment6_to
,gcc.segment7 segment7_from
,gcc.segment7 segment7_to
,gcc.segment8 segment8_from
,gcc.segment8 segment8_to
,gcc.segment9 segment9_from
,gcc.segment9 segment9_to
,gcc.segment10 segment10_from
,gcc.segment10 segment10_to
,gcc.segment11 segment11_from
,gcc.segment11 segment11_to
,gcc.segment12 segment12_from
,gcc.segment12 segment12_to
,gcc.segment13 segment13_from
,gcc.segment13 segment13_to
,gcc.segment14 segment14_from
,gcc.segment14 segment14_to
,gcc.segment15 segment15_from
,gcc.segment15 segment15_to
,gcc.segment16 segment16_from
,gcc.segment16 segment16_to
,gcc.segment17 segment17_from
,gcc.segment17 segment17_to
,gcc.segment18 segment18_from
,gcc.segment18 segment18_to
,gcc.segment19 segment19_from
,gcc.segment19 segment19_to
,gcc.segment20 segment20_from
,gcc.segment20 segment20_to
,gcc.segment21 segment21_from
,gcc.segment21 segment21_to
,gcc.segment22 segment22_from
,gcc.segment22 segment22_to
,gcc.segment23 segment23_from
,gcc.segment23 segment23_to
,gcc.segment24 segment24_from
,gcc.segment24 segment24_to
,gcc.segment25 segment25_from
,gcc.segment25 segment25_to
,gcc.segment26 segment26_from
,gcc.segment26 segment26_to
,gcc.segment27 segment27_from
,gcc.segment27 segment27_to
,gcc.segment28 segment28_from
,gcc.segment28 segment28_to
,gcc.segment29 segment29_from
,gcc.segment29 segment29_to
,gcc.segment30 segment30_from
,gcc.segment30 segment30_to
FROM xla_tb_defn_details tdd
,gl_code_combinations gcc
WHERE tdd.definition_code = p_definition_code
AND gcc.code_combination_id = tdd.code_combination_id;
trace('# of rows inserted (Flexfield) = ' || SQL%ROWCOUNT
,C_LEVEL_STATEMENT
,l_Log_module);
FOR c_segs IN (SELECT DISTINCT flexfield_segment_code
FROM xla_tb_defn_details
WHERE definition_code = p_definition_code)
LOOP
l_seg_num := SUBSTR(c_segs.flexfield_segment_code,8,2);
'INSERT INTO xla_tb_def_seg_ranges
(definition_code
,line_num '
|| l_ins_columns
|| ')
SELECT xtd.definition_code
,ROWNUM '
|| l_sel_columns
||' FROM xla_tb_definitions_b xtd '
|| l_tables
||' WHERE xtd.definition_code = :1 '
|| l_joins;
trace('# of rows inserted (Segment) = ' || SQL%ROWCOUNT
,C_LEVEL_STATEMENT
,l_Log_module);
(p_msg => '# rows inserted = '||SQL%ROWCOUNT
,p_level => C_LEVEL_PROCEDURE
,p_module =>l_log_module);
| delete_trial_balances |
| |
| Delete Trial Balances for given apps id and ae_header_id. |
| Used for data fix. |
+------------------------------------------------------------*/
PROCEDURE delete_trial_balances
(p_application_id IN NUMBER
,p_ae_header_id IN NUMBER)
IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_trial_balances';
trace('BEGIN delete_trial_balances'
,C_LEVEL_PROCEDURE
,l_Log_module);
DELETE xla_trial_balances
WHERE source_application_id = p_application_id
AND ae_header_id = p_ae_header_id;
trace('# of records deleted '||SQL%ROWCOUNT
,C_LEVEL_STATEMENT
,l_Log_module);
trace('END delete_trial_balances'
,C_LEVEL_PROCEDURE
,l_Log_module);
(p_location => 'xla_tb_data_manager_pvt.delete_trial_balances');
END delete_trial_balances;
| delete_trial_balances |
| |
| DELETE Trial Balance Report Non-Setup Data |
| |
+------------------------------------------------------------*/
PROCEDURE delete_trial_balances
(p_definition_code IN VARCHAR2) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_trial_balances';
trace('BEGIN delete_trial_balances',C_LEVEL_PROCEDURE,l_Log_module);
DELETE xla_tb_user_trans_views
WHERE definition_code = p_definition_code;
DELETE xla_tb_work_units
WHERE definition_code = p_definition_code;
DELETE xla_tb_def_seg_ranges
WHERE definition_code = p_definition_code;
DELETE xla_tb_logs
WHERE definition_code = p_definition_code;
DELETE xla_tb_logs
WHERE definition_code = p_definition_code
AND je_source_name = g_je_source_name;
DELETE xla_tb_user_trans_views
WHERE definition_code = p_definition_code
AND application_id = g_application_id;
DELETE xla_trial_balances
WHERE definition_code = p_definition_code
AND source_application_id = g_application_id;
trace('rows deleted'||sql%rowcount,C_LEVEL_PROCEDURE,l_Log_module);
trace('rows deleted'||sql%rowcount,C_LEVEL_PROCEDURE,l_Log_module);
trace('END delete_trial_balances',C_LEVEL_PROCEDURE,l_Log_module);
(p_location => 'xla_tb_data_manager_pvt.delete_trial_balances');
END delete_trial_balances;
| delete_definition |
| |
| DELETE Trial Balance Report DEFINITION |
| |
+------------------------------------------------------------*/
PROCEDURE delete_definition
(p_definition_code IN VARCHAR2) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_definition';
trace('BEGIN delete_definition',C_LEVEL_PROCEDURE,l_Log_module);
DELETE xla_tb_definitions_b
WHERE definition_code = p_definition_code;
DELETE xla_tb_definitions_tl
WHERE definition_code = p_definition_code;
DELETE xla_tb_defn_details
WHERE definition_code = p_definition_code;
DELETE xla_tb_defn_je_sources
WHERE definition_code = p_definition_code;
DELETE xla_tb_user_trans_views
WHERE definition_code = p_definition_code;
DELETE xla_tb_work_units
WHERE definition_code = p_definition_code;
DELETE xla_tb_def_seg_ranges
WHERE definition_code = p_definition_code;
DELETE xla_tb_logs
WHERE definition_code = p_definition_code;
DELETE xla_tb_defn_je_sources
WHERE definition_code = p_definition_code
AND je_source_name = g_je_source_name;
DELETE xla_tb_logs
WHERE definition_code = p_definition_code
AND je_source_name = g_je_source_name;
DELETE xla_tb_user_trans_views
WHERE definition_code = p_definition_code
AND application_id = g_application_id;
DELETE xla_trial_balances
WHERE definition_code = p_definition_code
AND source_application_id = g_application_id;
trace('END delete_definition',C_LEVEL_PROCEDURE,l_Log_module);
(p_location => 'xla_tb_data_manager_pvt.delete_definition');
END delete_definition;
SELECT xtd.definition_code
,xtd.ledger_id
--,xtd.je_source_name
,xtd.enabled_flag
,xtd.balance_side_code
,xtd.defined_by_code
,xtd.definition_status_code
,xtd.owner_code
INTO l_definition_info.definition_code
,l_definition_info.ledger_id
--,l_definition_info.je_source_name
,l_definition_info.enabled_flag
,l_definition_info.balance_side_code
,l_definition_info.defined_by_code
,l_definition_info.definition_status_code
,l_definition_info.owner_code
FROM xla_tb_definitions_b xtd
WHERE xtd.definition_code = p_definition_code;
SELECT work_unit
,num_of_workers
INTO g_work_unit
,g_num_of_workers
FROM xla_gl_ledgers
WHERE ledger_id = p_ledger_id ;
SELECT gl.ledger_id
,gl.NAME
,gl.short_name
,gl.ledger_category_code
,gl.currency_code
,gl.chart_of_accounts_id
,gl.object_type_code
INTO l_ledger_info.ledger_id
,l_ledger_info.ledger_name
,l_ledger_info.ledger_short_name
,l_ledger_info.ledger_category_code
,l_ledger_info.currency_code
,l_ledger_info.coa_id
,l_ledger_info.object_type_code
FROM gl_ledgers gl
WHERE gl.ledger_id = p_ledger_id;
(SELECT gl.ledger_id
FROM gl_ledgers gl
,gl_ledger_set_assignments sa
WHERE gl.ledger_id = sa.ledger_id
AND sa.ledger_set_id = :9) ';
SELECT application_id
INTO g_application_id
FROM xla_subledgers
WHERE je_source_name = g_je_source_name;
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;
SELECT DISTINCT
xut.application_id
,xec.entity_code
,xut.event_class_code
,xut.reporting_view_name
FROM xla_tb_user_trans_views xut
,xla_event_classes_b xec
WHERE xut.application_id = xec.application_id
AND xut.event_class_code = xec.event_class_code
AND xut.select_string = '###'
AND xut.request_id = p_request_id
;
l_select_string VARCHAR2(4000);
trace('Inserting user transaction views'
,C_LEVEL_STATEMENT
,l_Log_module);
INSERT INTO xla_tb_user_trans_views
(definition_code
,application_id
,event_class_code
,reporting_view_name
,select_string
,from_string
,where_string
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
SELECT DISTINCT
xjs.definition_code
,xsu.application_id
,xeca.event_class_code
,xeca.reporting_view_name
,'###'
,'###'
,'###'
,SYSDATE
,g_user_id
,SYSDATE
,g_user_id
,g_login_id
,g_request_id
,g_prog_appl_id
,g_program_id
,SYSDATE
FROM
xla_subledgers xsu,
xla_tb_defn_je_sources xjs,
xla_event_class_attrs xeca
WHERE xeca.event_class_code <> 'MANUAL'
AND xeca.application_id = xsu.application_id
AND xsu.je_source_name = xjs.je_source_name
AND xjs.definition_code = p_definition_code
AND xeca.reporting_view_name IS NOT NULL
AND NOT EXISTS
(
SELECT 'x'
FROM xla_tb_user_trans_views xut
WHERE xut.definition_code = xjs.definition_code
AND xut.application_id = xsu.application_id
AND xut.event_class_code = xeca.event_class_code
);
INSERT INTO xla_tb_user_trans_views
(definition_code
,application_id
,event_class_code
,reporting_view_name
,select_string
,from_string
,where_string
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
SELECT DISTINCT
xtd.definition_code
,xah.application_id
,xet.event_class_code
,xeca.reporting_view_name
,'###'
,'###'
,'###'
,SYSDATE
,g_user_id
,SYSDATE
,g_user_id
,g_login_id
,g_request_id
,g_prog_appl_id
,g_program_id
,SYSDATE
from xla_ae_headers xah,
xla_event_types_b xet,
xla_event_class_attrs xeca,
xla_tb_definitions_b xtd
WHERE xet.event_class_code <> 'MANUAL'
AND xet.event_type_code = xah.event_type_code
AND xet.event_class_code = xeca.event_class_code
AND xeca.application_id = xet.application_id
AND xah.application_id = xet.application_id
AND xah.ledger_id = xtd.ledger_id
AND xah.ledger_id = p_ledger_id
AND xah.group_id = p_group_id
AND xeca.reporting_view_name IS NOT NULL -- added for bug11770090
AND NOT EXISTS
(
SELECT 'x'
FROM xla_tb_user_trans_views xut
WHERE xut.definition_code = xtd.definition_code
AND xut.application_id = xah.application_id
AND xut.event_class_code = xet.event_class_code
AND xut.event_class_code = xeca.event_class_code
AND xut.application_id = xeca.application_id
);
INSERT INTO xla_tb_user_trans_views
(definition_code
,application_id
,event_class_code
,reporting_view_name
,select_string
,from_string
,where_string
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
)
SELECT DISTINCT
xjs.definition_code
,xsu.application_id
,xeca.event_class_code
,xeca.reporting_view_name
,'###'
,'###'
,'###'
,SYSDATE
,g_user_id
,SYSDATE
,g_user_id
,g_login_id
,g_request_id
,g_prog_appl_id
,g_program_id
,SYSDATE
FROM xla_subledgers xsu,
xla_tb_defn_je_sources xjs,
xla_event_class_attrs xeca
WHERE xeca.event_class_code <> 'MANUAL'
AND xsu.application_id = xeca.application_id
AND xsu.je_source_name = xjs.je_source_name
AND xeca.reporting_view_name IS NOT NULL
AND NOT EXISTS
(SELECT 'x'
FROM xla_tb_user_trans_views xut
WHERE xut.definition_code = xjs.definition_code
AND xut.application_id = xsu.application_id
AND xut.event_class_code = xeca.event_class_code
);
of processing all the distinct event_class_codes would already be inserted even if this error is
raised.
*/
EXCEPTION
WHEN dup_val_on_index THEN
NULL;
END; -- Exception handling for INSERT
trace('# of rows inserted = ' || SQL%ROWCOUNT
,C_LEVEL_STATEMENT
,l_Log_module);
,p_select_str => l_select_string
,p_from_str => l_from_string
,p_where_str => l_where_string);
(p_msg => 'l_select_string = ' || l_select_string
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
UPDATE xla_tb_user_trans_views
SET select_string = l_select_string
,from_string = l_from_string
,where_string = l_where_string
WHERE request_id = g_request_id
AND application_id = l_application_id
AND event_class_code = l_event_class_code
;
trace('# of rows updated = ' || SQL%ROWCOUNT
,C_LEVEL_STATEMENT
,l_Log_module);
insert_trial_balance_upg
DESCRIPTION
Insert Trial Balance for a system generated definition code
SCOPE - PRIVATE
ARGUMENTS
NOTES
+===========================================================================*/
PROCEDURE insert_trial_balance_upg
(p_definition_code IN VARCHAR2)
IS
l_defined_by_code xla_tb_definitions_b.defined_by_code%TYPE;
l_log_module := C_DEFAULT_MODULE||'.insert_trial_balance_upg';
trace('insert_trial_balance_upg.Begin',C_LEVEL_PROCEDURE,l_log_module);
trace('Inserting trial balances - Upgrade ',C_LEVEL_STATEMENT,l_Log_module);
trace('Number of rows inserted = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
trace('insert_trial_balance_upg.End',C_LEVEL_PROCEDURE,l_log_module);
trace('Unexpected error in insert_trial_balance_upg'
,C_LEVEL_UNEXPECTED
,l_log_module);
(p_location => 'xla_tb_data_manager_pvt.insert_trial_balance_upg');
END insert_trial_balance_upg;
insert_trial_balance_def
DESCRIPTION
Insert Trial Balance for a specific definition code
SCOPE - PRIVATE
ARGUMENTS
NOTES
+===========================================================================*/
PROCEDURE insert_trial_balance_def
(p_definition_code IN VARCHAR2
,p_application_id IN NUMBER DEFAULT NULL -- for Data Fix
,p_from_header_id IN NUMBER -- 8761772
,p_to_header_id IN NUMBER -- 8761772
) IS
l_defined_by_code xla_tb_definitions_b.defined_by_code%TYPE;
l_log_module := C_DEFAULT_MODULE||'.insert_trial_balance_def';
trace('Begin of insert_trial_balance_def',C_LEVEL_PROCEDURE,l_log_module);
FOR i IN ( SELECT xsu.application_id
FROM xla_subledgers xsu,
xla_tb_defn_je_sources xjs
WHERE xsu.je_source_name = xjs.je_source_name
AND xjs.definition_code = p_definition_code)
LOOP
l_application_id := i.application_id;
select accounting_class_code
from xla_acct_class_assgns xac, xla_post_acct_progs_b xpa
where xac.program_owner_code = xpa.program_owner_code
and xac.program_code = xpa.program_code
and xac.program_code = 'OPEN_ACCT_BAL_DATA_MGR_'||l_application_id
)
LOOP
l_post_programs_where := l_post_programs_where||
','''||c1.accounting_class_code||'''';
l_sql := g_tb_insert_sql;
l_upg_sql := C_TB_INSERT_UPG_SQL_AE;
SELECT defined_by_code
,owner_code
INTO l_defined_by_code
,l_owner_code
FROM xla_tb_definitions_b
WHERE definition_code = p_definition_code;
trace('Inserting trial balances - by Flexfield ',C_LEVEL_STATEMENT,l_Log_module);
,l_ledger_info.coa_id -- :coa_id in C_TB_INSERT_SQL
;
trace('# of rows inserted for R12 data = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
SELECT ledger_id
FROM gl_ledgers
WHERE ledger_category_code = 'PRIMARY'
AND configuration_id =
(SELECT configuration_id
FROM gl_ledgers WHERE ledger_id = g_ledger_id )
)
LOOP
l_derived_primary_ledger := i.ledger_id;
trace('Inserting trial balances for Upgraded Data - by Flexfield ',C_LEVEL_STATEMENT,l_Log_module);
trace('# of rows inserted for Upgraded data = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
l_upg_sql := C_TB_INSERT_UPG_SQL_SLE;
trace('Inserting trial balances for AX Upgraded Data - by Flexfield ',C_LEVEL_STATEMENT,l_Log_module);
trace('# of rows inserted for AX Upgraded data = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
trace('Inserting trial balances for R12 Data by Segment ',C_LEVEL_STATEMENT,l_Log_module);
trace('# of rows inserted for R12 Data by Segment = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
SELECT ledger_id
FROM gl_ledgers
WHERE ledger_category_code = 'PRIMARY'
AND configuration_id =
(SELECT configuration_id
FROM gl_ledgers WHERE ledger_id = g_ledger_id )
)
LOOP
l_derived_primary_ledger := i.ledger_id;
trace('Inserting trial balances for Upgraded Data - by Segment',C_LEVEL_STATEMENT,l_Log_module);
trace('# of rows inserted for Upgraded data = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
l_upg_sql := C_TB_INSERT_UPG_SQL_SLE;
trace('Inserting trial balances for AX Upgraded Data - by Segment',C_LEVEL_STATEMENT,l_Log_module);
trace('# of rows inserted for AX Upgraded data - by Segment = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
trace('End of insert_trial_balance_def',C_LEVEL_PROCEDURE,l_log_module);
trace('Unexpected error in insert_trial_balance_def'
,C_LEVEL_UNEXPECTED
,l_log_module);
(p_location => 'xla_tb_data_manager_pvt.insert_trial_balance_def');
END insert_trial_balance_def;
| Delete Trial Balances for given apps id and ae_header_id. |
| And re-extract journal entries to populate trila balances. |
| Used for Data Fix. |
+------------------------------------------------------------*/
PROCEDURE recreate_trial_balances
(p_application_id IN NUMBER
,p_ae_header_id IN NUMBER)
IS
l_definition_info r_definition_info;
g_tb_insert_sql := C_TB_INSERT_SQL;
DELETE xla_trial_balances
WHERE source_application_id = p_application_id
AND ae_header_id = p_ae_header_id
RETURNING definition_code BULK COLLECT INTO l_array_defn_code;
insert_trial_balance_def
(p_definition_code => l_array_defn_code(i)
,p_application_id => p_application_id
,p_from_header_id => p_ae_header_id
,p_to_header_id => p_ae_header_id);
insert_tb_logs
DESCRIPTION
SCOPE - PRIVATE
ARGUMENTS
NOTES
+===========================================================================*/
PROCEDURE insert_tb_logs IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.insert_tb_logs';
trace('insert_tb_logs.Begin',C_LEVEL_PROCEDURE,l_Log_module);
trace('Inserting into the xla_tb_logs table.',C_LEVEL_STATEMENT,l_Log_module);
INSERT INTO xla_tb_logs
( REQUEST_ID
, LEDGER_ID
, GROUP_ID
, PROCESS_MODE_CODE
, DEFINITION_CODE
, DEFINITION_STATUS_CODE
, REQUEST_STATUS_CODE
)
VALUES
(g_request_id
,g_ledger_id
,g_group_id
,g_process_mode_code
,g_definition_code
,NULL
,C_WU_PROCESSING
);
trace('END insert_tb_logs',C_LEVEL_PROCEDURE,l_Log_module);
(p_location => 'xla_tb_data_manager_pvt.insert_tb_logs');
END insert_tb_logs;
insert_trial_balance_wu
DESCRIPTION
SCOPE - PRIVATE
ARGUMENTS
p_ledger_id - PRIMARY/secondary ledger identifier.
NOTES
+===========================================================================*/
PROCEDURE insert_trial_balance_wu
(p_from_header_id IN NUMBER -- 8761772
,p_to_header_id IN NUMBER -- 8761772
,p_je_source_name IN VARCHAR2 -- pass the je source name
) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.insert_trial_balance_wu';
trace('BEGIN insert_trial_balance_wu',C_LEVEL_PROCEDURE,l_Log_module);
l_sql := g_tb_insert_sql;
FOR i IN ( SELECT xsu.application_id
FROM xla_subledgers xsu,
(SELECT distinct je_source_name FROM xla_tb_defn_je_sources) xjs
WHERE xsu.je_source_name = xjs.je_source_name
AND xjs.je_source_name = p_je_source_name)
LOOP
l_application_id := i.application_id;
select accounting_class_code
from xla_acct_class_assgns xac, xla_post_acct_progs_b xpa
where xac.program_owner_code = xpa.program_owner_code
and xac.program_code = xpa.program_code
and xac.program_code = 'OPEN_ACCT_BAL_DATA_MGR_'||l_application_id
)
LOOP
l_post_programs_where := l_post_programs_where||
','''||c1.accounting_class_code||'''';
trace('Inserting trial balances ',C_LEVEL_STATEMENT,l_Log_module);
The failed worker units in xla_tb_work_units table is updated with the
current transfer to GL's parent request id in recover_failed_requests procedure.
The ae_header_id of that failed request would be picked up by the worker's and
will be passed to this procedure for inserting into xla_trial_balances table.
Part of recovery is handled here.
*/
FOR i in ( SELECT group_id FROM xla_tb_work_units
WHERE FROM_HEADER_ID = p_from_header_id )
LOOP
l_group_id := i.group_id;
trace('Number of rows inserted = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_Log_module);
trace('END insert_trial_balance_wu',C_LEVEL_PROCEDURE,l_Log_module);
(p_location => 'xla_tb_data_manager_pvt.insert_trial_balance_wu');
END insert_trial_balance_wu;
SELECT xtb.request_id, xtb.process_mode_code
FROM xla_tb_logs xtb
,fnd_concurrent_requests fcr
WHERE xtb.ledger_id = p_ledger_id
AND xtb.definition_code = p_definition_code
AND xtb.request_status_code = 'PROCESSING'
AND xtb.request_id = fcr.request_id
AND fcr.phase_code NOT IN ('R','P','I');
SELECT xtb.request_id
FROM xla_tb_logs xtb
,fnd_concurrent_requests fcr
WHERE xtb.ledger_id = p_ledger_id
AND xtb.definition_code IS NULL
AND xtb.request_status_code = 'PROCESSING'
AND xtb.request_id = fcr.request_id
AND fcr.phase_code NOT IN ('R','P','I');
UPDATE xla_tb_work_units
SET status_code = C_WU_UNPROCESSED
,parent_request_id = g_request_id
WHERE definition_code = g_definition_code;
trace('Work units updated (g_wu_count) = ' || g_wu_count,C_LEVEL_STATEMENT,l_Log_module);
DELETE xla_tb_logs
WHERE request_id = failed_req_rec.request_id;
UPDATE xla_tb_work_units
SET status_code = C_WU_UNPROCESSED
,parent_request_id = g_request_id
WHERE parent_request_id = failed_req_rec.request_id;
trace('Work units updated = ' || g_wu_count,C_LEVEL_STATEMENT,l_Log_module);
trace('Work units updated rowcount = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_Log_module);
DELETE xla_tb_logs
WHERE request_id = failed_req_rec.request_id;
DELETE xla_tb_logs xtl
WHERE request_id NOT IN
( SELECT xtb.request_id
FROM xla_tb_logs xtb
,fnd_concurrent_requests fcr
WHERE xtb.ledger_id = p_ledger_id
AND nvl(xtb.definition_code,'###') = NVL(p_definition_code,'###')
AND xtb.request_status_code = 'PROCESSING'
AND xtb.request_id = fcr.request_id
AND fcr.phase_code IN ('R','P','I'))
/* bug#7338524 Added this and clause as records of only the current ledger
needs to be deleted from the logs*/
AND ledger_id = p_ledger_id;
| update_definition_status |
| |
+------------------------------------------------------------*/
PROCEDURE update_definition_status
(p_definition_code IN VARCHAR2
,p_status_code IN VARCHAR2) IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.update_definition_status';
(p_msg => 'BEGIN of update_definition_status'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
UPDATE xla_tb_definitions_b
SET definition_status_code = p_status_code
,last_updated_by = g_user_id
,last_update_date = SYSDATE
,last_update_login = g_login_id
,request_id = g_request_id
,program_application_id = g_prog_appl_id
,program_id = g_program_id
,program_update_date = SYSDATE
WHERE definition_code = p_definition_code;
(p_msg => 'END of update_definition_status'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'xla_tb_data_manager_pvt.update_definition_status');
END update_definition_status;
INSERT INTO xla_tb_work_units
(group_id
,upg_batch_id
,from_header_id
,to_header_id
,status_code
,parent_request_id
)
SELECT
p_group_id
,l_upg_batch_id
,min(ae_header_id)
,max(ae_header_id)
,C_WU_UNPROCESSED
,g_request_id
FROM
(SELECT ae_header_id,
FLOOR
(
sum(count(*)) over
(ORDER BY ae_header_id
ROWS unbounded preceding
)/g_work_unit
) wu
FROM xla_ae_headers
WHERE group_id = p_group_id
AND ledger_id IN (
SELECT lg.ledger_id
FROM gl_ledgers lg
,gl_ledger_set_assignments sa
WHERE lg.ledger_id = sa.ledger_id
AND sa.ledger_set_id = p_ledger_id)
GROUP BY ae_header_id
)
GROUP BY wu;
INSERT INTO xla_tb_work_units
(group_id
,upg_batch_id
,from_header_id
,to_header_id
,status_code
,parent_request_id
)
SELECT
p_group_id
,l_upg_batch_id
,min(ae_header_id)
,max(ae_header_id)
,C_WU_UNPROCESSED
,g_request_id
FROM
(SELECT ae_header_id,
FLOOR
(
sum(count(*)) over
(ORDER BY ae_header_id
ROWS unbounded preceding
)/g_work_unit
) wu
FROM xla_ae_headers
WHERE group_id = p_group_id
AND ledger_id = p_ledger_id
GROUP BY ae_header_id
)
GROUP BY wu;
INSERT INTO xla_tb_work_units
(group_id
,upg_batch_id
,from_header_id
,to_header_id
,status_code
,parent_request_id
,definition_code
)
SELECT
NULL
,l_upg_batch_id
,min(ae_header_id)
,max(ae_header_id)
,C_WU_UNPROCESSED
,g_request_id
,p_definition_code
FROM
(SELECT ae_header_id,
FLOOR
(
sum(count(*)) over
(ORDER BY ae_header_id
ROWS unbounded preceding
)/C_WORK_UNIT
) wu
FROM xla_ae_headers aeh
,xla_subledgers xsu
,xla_tb_definitions_b xtd
,xla_tb_defn_je_sources xjs
WHERE gl_transfer_status_code IN ('Y','NT')
AND aeh.ledger_id IN (
SELECT lg.ledger_id
FROM gl_ledgers lg
,gl_ledger_set_assignments sa
WHERE lg.ledger_id = sa.ledger_id
AND sa.ledger_set_id = p_ledger_id)
AND xtd.definition_code = p_definition_code
AND xtd.definition_code = xjs.definition_code
AND xjs.je_source_name = xsu.je_source_name
AND aeh.application_id = xsu.application_id
GROUP BY ae_header_id
)
GROUP BY wu;
INSERT INTO xla_tb_work_units
(group_id
,upg_batch_id
,from_header_id
,to_header_id
,status_code
,parent_request_id
,definition_code
)
SELECT
NULL
,l_upg_batch_id
,min(ae_header_id)
,max(ae_header_id)
,C_WU_UNPROCESSED
,g_request_id
,p_definition_code
FROM
(SELECT ae_header_id,
FLOOR
(
sum(count(*)) over
(ORDER BY ae_header_id
ROWS unbounded preceding
)/C_WORK_UNIT
) wu
FROM xla_ae_headers aeh
,xla_subledgers xsu
,xla_tb_definitions_b xtd
,xla_tb_defn_je_sources xjs
WHERE gl_transfer_status_code IN ('Y','NT')
AND aeh.ledger_id = p_ledger_id
AND xtd.definition_code = p_definition_code
AND xtd.definition_code = xjs.definition_code
AND xjs.je_source_name = xsu.je_source_name
AND aeh.application_id = xsu.application_id
GROUP BY ae_header_id
)
GROUP BY wu;
INSERT INTO xla_tb_work_units
(group_id
,upg_batch_id
,from_header_id
,to_header_id
,status_code
,parent_request_id
,definition_code
)
SELECT
NULL
,l_upg_batch_id
,min(ae_header_id)
,max(ae_header_id)
,C_WU_UNPROCESSED
,g_request_id
,p_definition_code
FROM
(SELECT ae_header_id,
FLOOR
(
sum(count(*)) over
(ORDER BY ae_header_id
ROWS unbounded preceding
)/C_WORK_UNIT
) wu
FROM xla_ae_headers aeh
,xla_subledgers xsu
,xla_tb_definitions_b xtd
,xla_tb_defn_je_sources xjs
WHERE gl_transfer_status_code IN ('Y','NT')
AND aeh.ledger_id IN (
SELECT lg.ledger_id
FROM gl_ledgers lg
,gl_ledger_set_assignments sa
WHERE lg.ledger_id = sa.ledger_id
AND sa.ledger_set_id = p_ledger_id)
AND xtd.definition_code = p_definition_code
AND xtd.definition_code = xjs.definition_code
AND xjs.je_source_name = xsu.je_source_name
AND xsu.je_source_name = g_je_source_name
AND aeh.application_id = xsu.application_id
AND aeh.accounting_date
>= fnd_date.canonical_to_date(g_gl_date_from)
AND aeh.accounting_date
<= fnd_date.canonical_to_date(g_gl_date_to)
GROUP BY ae_header_id
)
GROUP BY wu;
INSERT INTO xla_tb_work_units
(group_id
,upg_batch_id
,from_header_id
,to_header_id
,status_code
,parent_request_id
,definition_code
)
SELECT
NULL
,l_upg_batch_id
,min(ae_header_id)
,max(ae_header_id)
,C_WU_UNPROCESSED
,g_request_id
,p_definition_code
FROM
(SELECT ae_header_id,
FLOOR
(
sum(count(*)) over
(ORDER BY ae_header_id
ROWS unbounded preceding
)/C_WORK_UNIT
) wu
FROM xla_ae_headers aeh
,xla_subledgers xsu
,xla_tb_definitions_b xtd
,xla_tb_defn_je_sources xjs
WHERE gl_transfer_status_code IN ('Y','NT')
AND aeh.ledger_id = p_ledger_id
AND xtd.definition_code = p_definition_code
AND xtd.definition_code = xjs.definition_code
AND xjs.je_source_name = xsu.je_source_name
AND xsu.je_source_name = g_je_source_name
AND aeh.application_id = xsu.application_id
AND aeh.accounting_date
>= fnd_date.canonical_to_date(g_gl_date_from)
AND aeh.accounting_date
<= fnd_date.canonical_to_date(g_gl_date_to)
GROUP BY ae_header_id
)
GROUP BY wu;
UPDATE xla_tb_work_units
SET status_code = C_WU_PROCESSING
WHERE parent_request_id = p_parent_request_id
AND status_code = C_WU_UNPROCESSED
AND ROWNUM = 1
RETURNING from_header_id
,to_header_id
,definition_code
INTO p_from_header_id, p_to_header_id, p_definition_code;
trace('Number of work units updated = ' || SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
| update_definition |
| |
| |
+======================================================================*/
PROCEDURE truncate_partition
(p_definition_code VARCHAR2
) IS
l_log_module VARCHAR2(240);
g_array_wu_requests.DELETE;
| p_process_mode_code = DELETED, CHANGED, NEW |
+======================================================================*/
PROCEDURE upload
(p_errbuf IN OUT NOCOPY VARCHAR2
,p_retcode IN OUT NOCOPY NUMBER
,p_application_id IN NUMBER DEFAULT NULL
,p_ledger_id IN NUMBER
,p_group_id IN NUMBER
,p_definition_code IN VARCHAR2 DEFAULT NULL
,p_process_mode_code IN VARCHAR2
,p_je_source_name IN VARCHAR2 DEFAULT NULL
,p_upg_batch_id IN NUMBER DEFAULT NULL
,p_gl_date_from IN VARCHAR2 DEFAULT NULL
,p_gl_date_to IN VARCHAR2 DEFAULT NULL
) IS
l_req_data VARCHAR2(10);
insert_tb_logs;
IF p_process_mode_code = 'DELETED' THEN
delete_definition
( p_definition_code => p_definition_code);
delete_trial_balances
( p_definition_code => p_definition_code);
IF nvl(p_process_mode_code,'N') NOT IN ('DELETED') THEN
-- Derive processing unit;
delete_tb_log;
| delete_wu
|
|
+======================================================================*/
PROCEDURE delete_wu
(p_from_header_id NUMBER )IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.delete_wu';
trace('BEGIN of delete_wu',C_LEVEL_PROCEDURE,l_Log_module);
DELETE xla_tb_work_units
WHERE from_header_id = p_from_header_id;
trace('END of delete_wu',C_LEVEL_PROCEDURE,l_Log_module);
(p_location => 'xla_tb_data_manager_pvt.delete_wu');
END delete_wu;
g_tb_insert_sql := C_TB_INSERT_SQL;
insert_trial_balance_def
(p_definition_code => l_definition_code
,p_from_header_id => l_from_header_id
,p_to_header_id => l_to_header_id
);
insert_trial_balance_wu
(p_from_header_id => l_from_header_id
,p_to_header_id => l_to_header_id
,p_je_source_name => p_je_source_name -- pass the je_source_name
);
delete_wu(p_from_header_id => l_from_header_id);
For upgraded report definitions, insert_trial_balance_upg will fail
with ORA-1400 as balance date is null.
FOR c_def IN (SELECT definition_code
FROM xla_tb_definitions_b
WHERE definition_code = NVL(p_definition_code,definition_code)
AND ledger_id = p_ledger_id
AND owner_code = 'S')
LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace('System Generated Report Definition ' || c_def.definition_code
,C_LEVEL_STATEMENT
,l_log_module);
DELETE FROM xla_trial_balances
WHERE definition_code = c_def.definition_code
AND source_entity_id = -1;
insert_trial_balance_upg
(p_definition_code => c_def.definition_code);
update_wu_status
(p_from_header_id => l_from_header_id
,p_status_code => C_WU_ERROR);
*/ -- update processes status
p_errbuf := SQLERRM;
| Delete_Non_UI_Rows |
| |
| Deletes rows from the following tables: |
| - xla_tb_logs |
| - xla_tb_def_seg_ranges |
| - xla_tb_user_trans_views |
| - xla_tb_work_units |
| |
| For xla_trial_balances, call drop_partition separately |
| Called from TbReportDefnsAMImpl.java. |
+======================================================================*/
PROCEDURE delete_non_ui_rows
(p_definition_code IN VARCHAR2)
IS
l_log_module VARCHAR2(240);
trace('BEGIN delete_non_ui_data'
,C_LEVEL_PROCEDURE
,l_log_module);
DELETE xla_tb_logs
WHERE definition_code = p_definition_code;
DELETE xla_tb_def_seg_ranges
WHERE definition_code = p_definition_code;
DELETE xla_tb_user_trans_views
WHERE definition_code = p_definition_code;
DELETE xla_tb_work_units
WHERE definition_code = p_definition_code;
trace('END delete_non_ui_rows'
,C_LEVEL_PROCEDURE
,l_log_module);
trace('Unexpected error in delete_non_ui_rows'
,C_LEVEL_UNEXPECTED
,l_log_module);
(p_location => 'xla_tb_data_manager_pvt.delete_non_ui_rows');
END delete_non_ui_rows;