|
|
楼主 |
发表于 2012/12/4 13:31:34
|
显示全部楼层
本帖最后由 zhuhgjava 于 2013/1/23 17:26 编辑
这沙发还的自己来坐。
牺牲了一个周末，终于解决了这个问题。
经过不断的测试。发现，EBS（AR）的创建会计科目并发出来的的“会计程序”内部处理非冲销类的收款和冲销类额收款，内部调用的2个不同的PKG来处理。通过跟踪记录，发现其中一段SQL运行时间特别长。把这段SQL单纯提出来，然后进行优化测试，给他加上强制索引，然后速度就变快很多了。
优化的PKG：XLA_AE_LINES_PKG.ACCOUNTINGREVERSAL
优化前的代码：
SELECT /*+ index(xdl XLA_DISTRIBUTION_LINKS_N3)) */
-- populates ae_header_id which is same as event_id till this point
l_array_event_id(i)
-- populates temp_line_num which is (-ve) of original line
,0 - xdl.temp_line_num
-- populates event_id which is the event_id of event under process
,l_array_event_id(i)
-- populates ref_ae_header_id which is ae_header_id of original line
,ael.ae_header_id
-- populates ref_ae_line_num which is ae_line_num of original line
,ael.ae_line_num
-- populates ref_temp_line_num which is ae_line_num of original line
,xdl.temp_line_num
-- populates ref_event_id which is event_id of original line
,xdl.event_id
,aeh.balance_type_code
,l_array_ledger_id(i)
,ael.accounting_class_code
,xdl.event_class_code
,xdl.event_type_code
,xdl.line_definition_owner_code
,xdl.line_definition_code
,xdl.accounting_line_type_code
,xdl.accounting_line_code
,C_CREATED
,ael.code_combination_id
,C_CREATED
,ael.code_combination_id
,ael.description
,ael.gl_transfer_mode_code
,xdl.merge_duplicate_code
--,decode(ael.gain_or_loss_flag, 'Y', decode(xdl.calculate_g_l_amts_flag, 'Y', 'Y', 'N'), 'N')
-- 5055878 switch_side_flag
-- 5055878 amounts modified for reversal method of SIDE or SIGN
, decode(ael.gain_or_loss_flag, 'Y', decode(xdl.calculate_g_l_amts_flag, 'Y', 'Y', 'N'), l_default_switch_side_flag)
--bug#6933157 24-Apr-2008
,DECODE(l_array_switch_side_flag(i),'Y',xdl.unrounded_entered_cr, -xdl.unrounded_entered_dr)
,DECODE(l_array_switch_side_flag(i),'Y',xdl.unrounded_entered_dr, -xdl.unrounded_entered_cr)
,DECODE(l_array_switch_side_flag(i),'Y',xdl.unrounded_accounted_cr,-xdl.unrounded_accounted_dr)
,DECODE(l_array_switch_side_flag(i),'Y',xdl.unrounded_accounted_dr,-xdl.unrounded_accounted_cr)
--
,xdl.calculate_acctd_amts_flag
,xdl.calculate_g_l_amts_flag
,ael.gain_or_loss_flag
,xdl.rounding_class_code
,xdl.document_rounding_level
,NULL -- xdl.doc_rounding_acctd_amt 4669308 creates wrong ROUNDING line for MPA reversal
,NULL -- xdl.doc_rounding_entered_amt 4669308 creates wrong ROUNDING line for MPA reversal
/*
,xdl.entered_amount
,xdl.ledger_amount
-- populates entered_dr. amount should be equal to the entered amount in distribution links
,DECODE(ael.entered_cr,NULL,NULL,xdl.entered_amount)
-- populates entered_cr. amount should be equal to the entered amount in distribution links
,DECODE(ael.entered_dr,NULL,NULL,xdl.entered_amount)
-- populates accounted_dr. amount should be equal to the ledger amount in distribution links
,DECODE(ael.accounted_cr,NULL,NULL,xdl.ledger_amount)
-- populates accounted_cr. amount should be equal to the ledger amount in distribution links
,DECODE(ael.accounted_dr,NULL,NULL,xdl.ledger_amount)
*/
-- ,ael.entered_cr
-- ,ael.entered_dr
-- ,ael.accounted_cr
-- ,ael.accounted_dr
-- bug8642358 ,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,decode(ael.gain_or_loss_flag, 'Y', 0.01, nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))
,ael.currency_code
,ael.currency_conversion_date
,ael.currency_conversion_rate
,ael.currency_conversion_type
,ael.statistical_amount
,ael.party_id
,ael.party_site_id
,ael.party_type_code
,ael.ussgl_transaction_code
,ael.jgzz_recon_ref
-- if there are no distributions for this event
-- populate with the distribution if lines that is being reversed
,NVL(l_array_tax_line_ref(i),xdl.tax_line_ref_id) -- bug7159711
,NVL(l_array_tax_summary_line_ref(i),xdl.tax_summary_line_ref_id) -- bug7159711
,NVL(l_array_tax_rec_nrec_dist_ref(i),xdl.tax_rec_nrec_dist_ref_id) -- bug7159711
,NVL(l_array_source_dist_id_char_1(i),xdl.source_distribution_id_char_1)
,NVL(l_array_source_dist_id_char_2(i),xdl.source_distribution_id_char_2)
,NVL(l_array_source_dist_id_char_3(i),xdl.source_distribution_id_char_3)
,NVL(l_array_source_dist_id_char_4(i),xdl.source_distribution_id_char_4)
,NVL(l_array_source_dist_id_char_5(i),xdl.source_distribution_id_char_5)
,NVL(l_array_source_dist_id_num_1(i),xdl.source_distribution_id_num_1)
,NVL(l_array_source_dist_id_num_2(i),xdl.source_distribution_id_num_2)
,NVL(l_array_source_dist_id_num_3(i),xdl.source_distribution_id_num_3)
,NVL(l_array_source_dist_id_num_4(i),xdl.source_distribution_id_num_4)
,NVL(l_array_source_dist_id_num_5(i),xdl.source_distribution_id_num_5)
,NVL(l_array_source_dist_type(i),xdl.source_distribution_type)
,xdl.source_distribution_id_char_1
,xdl.source_distribution_id_char_2
,xdl.source_distribution_id_char_3
,xdl.source_distribution_id_char_4
,xdl.source_distribution_id_char_5
,xdl.source_distribution_id_NUM_1
,xdl.source_distribution_id_NUM_2
,xdl.source_distribution_id_NUM_3
,xdl.source_distribution_id_NUM_4
,xdl.source_distribution_id_NUM_5
,xdl.source_distribution_type
-- populate reversal_code indicating that line is result of reversal
,'REVERSAL'
-- denormalises entry status from headers to line to determine the status of entry being created
-- populates 0 for a valid entry (F or D) else poulates 1
,DECODE(aeh.accounting_entry_status_code,'F',0,'D',0,1)
,ael.encumbrance_type_id -- 4458381
,'N' -- 4219869 inherit_desc_flag Should it be from l_array?
-- ,0 -- 4669308 combine header with reversal of original entry -- commented for bug8505463
, DECODE(aeh.parent_ae_header_id , NULL , 0 ,
CASE
WHEN aeh.accounting_date <= l_array_gl_date(i)
THEN 0
ELSE -1 * dense_rank() over ( partition by aeh.parent_ae_header_id , aeh.parent_ae_line_num
order by ael.ae_header_Id )
END
) HEADER_NUM -- added for bug8505463 for MPA Cancellation
,'N' -- 4262811 mpa_accrual_entry_flag
-- ,l_array_gl_date(i) -- 5189664 aeh.accounting_date -- 4955764 -- commented for bug8505463
, DECODE(aeh.parent_ae_header_id , NULL , l_array_gl_date(i) , -- Regular Cancellation, hence Cancellation Event Date
CASE
WHEN aeh.accounting_date <= l_array_gl_date(i)
THEN l_array_gl_date(i) -- Accounting Date of Cancellation Event for prior Period's MPA Accounting
ELSE aeh.accounting_date -- Accounting Date of Original MPA Accounting
END
) ACCOUNTING_DATE -- added for bug8505463 for MPA Cancellation
, xdl.APPLIED_TO_APPLICATION_ID APPLIED_TO_APPLICATION_ID
, xdl.APPLIED_TO_ENTITY_CODE APPLIED_TO_ENTITY_CODE
, xdl.APPLIED_TO_ENTITY_ID APPLIED_TO_ENTITY_ID
, xdl.APPLIED_TO_SOURCE_ID_NUM_1 APPLIED_TO_SOURCE_ID_NUM_1
, xdl.APPLIED_TO_SOURCE_ID_NUM_2 APPLIED_TO_SOURCE_ID_NUM_2
, xdl.APPLIED_TO_SOURCE_ID_NUM_3 APPLIED_TO_SOURCE_ID_NUM_3
, xdl.APPLIED_TO_SOURCE_ID_NUM_4 APPLIED_TO_SOURCE_ID_NUM_4
, xdl.APPLIED_TO_SOURCE_ID_CHAR_1 APPLIED_TO_SOURCE_ID_CHAR_1
, xdl.APPLIED_TO_SOURCE_ID_CHAR_2 APPLIED_TO_SOURCE_ID_CHAR_2
, xdl.APPLIED_TO_SOURCE_ID_CHAR_3 APPLIED_TO_SOURCE_ID_CHAR_3
, xdl.APPLIED_TO_SOURCE_ID_CHAR_4 APPLIED_TO_SOURCE_ID_CHAR_4
, xdl.APPLIED_TO_DISTRIBUTION_TYPE APPLIED_TO_DISTRIBUTION_TYPE
, xdl.APPLIED_TO_DIST_ID_NUM_1 APPLIED_TO_DIST_ID_NUM_1
, xdl.APPLIED_TO_DIST_ID_NUM_2 APPLIED_TO_DIST_ID_NUM_2
, xdl.APPLIED_TO_DIST_ID_NUM_3 APPLIED_TO_DIST_ID_NUM_3
, xdl.APPLIED_TO_DIST_ID_NUM_4 APPLIED_TO_DIST_ID_NUM_4
, xdl.APPLIED_TO_DIST_ID_NUM_5 APPLIED_TO_DIST_ID_NUM_5
, xdl.APPLIED_TO_DIST_ID_CHAR_1 APPLIED_TO_DIST_ID_CHAR_1
, xdl.APPLIED_TO_DIST_ID_CHAR_2 APPLIED_TO_DIST_ID_CHAR_2
, xdl.APPLIED_TO_DIST_ID_CHAR_3 APPLIED_TO_DIST_ID_CHAR_3
, xdl.APPLIED_TO_DIST_ID_CHAR_4 APPLIED_TO_DIST_ID_CHAR_4
, xdl.APPLIED_TO_DIST_ID_CHAR_5 APPLIED_TO_DIST_ID_CHAR_5
, xdl.alloc_to_application_id alloc_to_application_id
, xdl.alloc_to_entity_code alloc_to_entity_code
, xdl.alloc_to_source_id_num_1 alloc_to_source_id_num_1
, xdl.alloc_to_source_id_num_2 alloc_to_source_id_num_2
, xdl.alloc_to_source_id_num_3 alloc_to_source_id_num_3
, xdl.alloc_to_source_id_num_4 alloc_to_source_id_num_4
, xdl.alloc_to_source_id_char_1 alloc_to_source_id_char_1
, xdl.alloc_to_source_id_char_2 alloc_to_source_id_char_2
, xdl.alloc_to_source_id_char_3 alloc_to_source_id_char_3
, xdl.alloc_to_source_id_char_4 alloc_to_source_id_char_4
, xdl.alloc_to_distribution_type alloc_to_distribution_type
, xdl.alloc_to_dist_id_char_1 alloc_to_dist_id_char_1
, xdl.alloc_to_dist_id_char_2 alloc_to_dist_id_char_2
, xdl.alloc_to_dist_id_char_3 alloc_to_dist_id_char_3
, xdl.alloc_to_dist_id_char_4 alloc_to_dist_id_char_4
, xdl.alloc_to_dist_id_char_5 alloc_to_dist_id_char_5
, xdl.alloc_to_dist_id_num_1 alloc_to_dist_id_num_1
, xdl.alloc_to_dist_id_num_2 alloc_to_dist_id_num_2
, xdl.alloc_to_dist_id_num_3 alloc_to_dist_id_num_3
, xdl.alloc_to_dist_id_num_4 alloc_to_dist_id_num_4
, xdl.alloc_to_dist_id_num_5 alloc_to_dist_id_num_5
, decode(ael.analytical_balance_flag, 'Y','P',
'P','P',
null) analytical_balance_flag --8417496
,nvl2(ael.analytical_balance_flag,'DUMMY_ANC_'||ael.ae_header_id||ael.ae_line_num,null) ANC_ID_1 --Bug 8691573
FROM
xla_ae_lines ael
,xla_ae_headers aeh
,xla_distribution_links xdl
,fnd_currencies fcu
,xla_events xe
WHERE aeh.application_id = l_application_id
AND aeh.ledger_id = l_array_ledger_id(i)
AND aeh.entity_id = l_array_entity_id(i)
AND aeh.ae_header_id = ael.ae_header_id /* bug 9194744 */
-- AND aeh.event_number < l_array_event_number(i)
AND xdl.application_id = aeh.application_id
AND xdl.ae_header_id = aeh.ae_header_id -- 5499367
-- AND xdl.ref_temp_line_num IS NULL -- 5019460 old
AND NVL(xdl.temp_line_num,0) >= 0 -- 5019460 new
AND ael.application_id = xdl.application_id
AND ael.ae_header_id = xdl.ae_header_id -- 5499367
AND ael.ae_line_num = xdl.ae_line_num
AND ael.currency_code = fcu.currency_code
AND NOT EXISTS (
SELECT /*+ no_unnest */ 1
FROM xla_distribution_links
WHERE ref_ae_header_id = xdl.ae_header_id
AND temp_line_num = xdl.temp_line_num * -1
AND application_id = xdl.application_id
)
AND NOT EXISTS (
SELECT /*+ no_unnest */ 1
FROM xla_ae_lines_gt
WHERE ref_ae_header_id = xdl.ae_header_id
AND temp_line_num = xdl.temp_line_num * -1
AND ledger_id = l_array_ledger_id(i)
)
AND xe.application_id = aeh.application_id
AND xe.event_id = aeh.event_id
AND NVL(xe.budgetary_control_flag,'N') = DECODE(p_accounting_mode
,'FUNDS_CHECK','Y'
,'FUNDS_RESERVE','Y'
,'N')
AND ((aeh.parent_ae_header_id IS NOT NULL AND aeh.accounting_entry_status_code = 'F') OR -- 4669308
(aeh.parent_ae_header_id IS NULL)) -- 4669308
GROUP BY
(0 - xdl.temp_line_num)
,ael.ae_header_id
,ael.ae_line_num
,xdl.temp_line_num
,xdl.event_id
,aeh.balance_type_code
,ael.accounting_class_code
,xdl.event_class_code
,xdl.event_type_code
,xdl.line_definition_owner_code
,xdl.line_definition_code
,xdl.accounting_line_type_code
,xdl.accounting_line_code
,xdl.unrounded_entered_cr
,xdl.unrounded_entered_dr
,xdl.unrounded_accounted_cr
,xdl.unrounded_accounted_dr
,xdl.calculate_acctd_amts_flag
,xdl.calculate_g_l_amts_flag
,ael.gain_or_loss_flag
,xdl.rounding_class_code
,xdl.document_rounding_level
,xdl.doc_rounding_acctd_amt
,xdl.doc_rounding_entered_amt
,ael.code_combination_id
,ael.description
,ael.gl_transfer_mode_code
,xdl.merge_duplicate_code
-- ,ael.entered_cr
-- ,ael.entered_dr
-- ,ael.accounted_cr
-- ,ael.accounted_dr
,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,ael.currency_code
,ael.currency_conversion_date
,ael.currency_conversion_rate
,ael.currency_conversion_type
,ael.statistical_amount
,ael.party_id
,ael.party_site_id
,ael.party_type_code
,ael.ussgl_transaction_code
,ael.jgzz_recon_ref
,aeh.accounting_entry_status_code
,ael.encumbrance_type_id
,xdl.tax_line_ref_id -- bug7226263
,xdl.tax_summary_line_ref_id -- bug7226263
,xdl.tax_rec_nrec_dist_ref_id -- bug7226263
,xdl.source_distribution_id_char_1
,xdl.source_distribution_id_char_2
,xdl.source_distribution_id_char_3
,xdl.source_distribution_id_char_4
,xdl.source_distribution_id_char_5
,xdl.source_distribution_id_NUM_1
,xdl.source_distribution_id_NUM_2
,xdl.source_distribution_id_NUM_3
,xdl.source_distribution_id_NUM_4
,xdl.source_distribution_id_NUM_5
,xdl.source_distribution_type
,aeh.accounting_date
, aeh.parent_ae_header_id -- added for bug8505463
, aeh.parent_ae_line_num -- added for bug8505463
, xdl.APPLIED_TO_APPLICATION_ID
, xdl.APPLIED_TO_ENTITY_CODE
, xdl.APPLIED_TO_ENTITY_ID
, xdl.APPLIED_TO_SOURCE_ID_NUM_1
, xdl.APPLIED_TO_SOURCE_ID_NUM_2
, xdl.APPLIED_TO_SOURCE_ID_NUM_3
, xdl.APPLIED_TO_SOURCE_ID_NUM_4
, xdl.APPLIED_TO_SOURCE_ID_CHAR_1
, xdl.APPLIED_TO_SOURCE_ID_CHAR_2
, xdl.APPLIED_TO_SOURCE_ID_CHAR_3
, xdl.APPLIED_TO_SOURCE_ID_CHAR_4
, xdl.APPLIED_TO_DISTRIBUTION_TYPE
, xdl.APPLIED_TO_DIST_ID_NUM_1
, xdl.APPLIED_TO_DIST_ID_NUM_2
, xdl.APPLIED_TO_DIST_ID_NUM_3
, xdl.APPLIED_TO_DIST_ID_NUM_4
, xdl.APPLIED_TO_DIST_ID_NUM_5
, xdl.APPLIED_TO_DIST_ID_CHAR_1
, xdl.APPLIED_TO_DIST_ID_CHAR_2
, xdl.APPLIED_TO_DIST_ID_CHAR_3
, xdl.APPLIED_TO_DIST_ID_CHAR_4
, xdl.APPLIED_TO_DIST_ID_CHAR_5
, xdl.alloc_to_application_id
, xdl.alloc_to_entity_code
, xdl.alloc_to_source_id_num_1
, xdl.alloc_to_source_id_num_2
, xdl.alloc_to_source_id_num_3
, xdl.alloc_to_source_id_num_4
, xdl.alloc_to_source_id_char_1
, xdl.alloc_to_source_id_char_2
, xdl.alloc_to_source_id_char_3
, xdl.alloc_to_source_id_char_4
, xdl.alloc_to_distribution_type
, xdl.alloc_to_dist_id_char_1
, xdl.alloc_to_dist_id_char_2
, xdl.alloc_to_dist_id_char_3
, xdl.alloc_to_dist_id_char_4
, xdl.alloc_to_dist_id_char_5
, xdl.alloc_to_dist_id_num_1
, xdl.alloc_to_dist_id_num_2
, xdl.alloc_to_dist_id_num_3
, xdl.alloc_to_dist_id_num_4
, xdl.alloc_to_dist_id_num_5
, ael.analytical_balance_flag --8417496
, nvl2(ael.analytical_balance_flag,'DUMMY_ANC_'||ael.ae_header_id||ael.ae_line_num,null) --Bug 8691573
;
优化后的代码：
SELECT /*+ index(xdl XLA_DISTRIBUTION_LINKS_N3) INDEX(AEH XLA_AE_HEADERS_N3) index(xe XLA_EVENTS_U1) */
-- populates ae_header_id which is same as event_id till this point
l_array_event_id(i)
-- populates temp_line_num which is (-ve) of original line
,0 - xdl.temp_line_num
-- populates event_id which is the event_id of event under process
,l_array_event_id(i)
-- populates ref_ae_header_id which is ae_header_id of original line
,ael.ae_header_id
-- populates ref_ae_line_num which is ae_line_num of original line
,ael.ae_line_num
-- populates ref_temp_line_num which is ae_line_num of original line
,xdl.temp_line_num
-- populates ref_event_id which is event_id of original line
,xdl.event_id
,aeh.balance_type_code
,l_array_ledger_id(i)
,ael.accounting_class_code
,xdl.event_class_code
,xdl.event_type_code
,xdl.line_definition_owner_code
,xdl.line_definition_code
,xdl.accounting_line_type_code
,xdl.accounting_line_code
,C_CREATED
,ael.code_combination_id
,C_CREATED
,ael.code_combination_id
,ael.description
,ael.gl_transfer_mode_code
,xdl.merge_duplicate_code
--,decode(ael.gain_or_loss_flag, 'Y', decode(xdl.calculate_g_l_amts_flag, 'Y', 'Y', 'N'), 'N')
-- 5055878 switch_side_flag
-- 5055878 amounts modified for reversal method of SIDE or SIGN
, decode(ael.gain_or_loss_flag, 'Y', decode(xdl.calculate_g_l_amts_flag, 'Y', 'Y', 'N'), l_default_switch_side_flag)
--bug#6933157 24-Apr-2008
,DECODE(l_array_switch_side_flag(i),'Y',xdl.unrounded_entered_cr, -xdl.unrounded_entered_dr)
,DECODE(l_array_switch_side_flag(i),'Y',xdl.unrounded_entered_dr, -xdl.unrounded_entered_cr)
,DECODE(l_array_switch_side_flag(i),'Y',xdl.unrounded_accounted_cr,-xdl.unrounded_accounted_dr)
,DECODE(l_array_switch_side_flag(i),'Y',xdl.unrounded_accounted_dr,-xdl.unrounded_accounted_cr)
--
,xdl.calculate_acctd_amts_flag
,xdl.calculate_g_l_amts_flag
,ael.gain_or_loss_flag
,xdl.rounding_class_code
,xdl.document_rounding_level
,NULL -- xdl.doc_rounding_acctd_amt 4669308 creates wrong ROUNDING line for MPA reversal
,NULL -- xdl.doc_rounding_entered_amt 4669308 creates wrong ROUNDING line for MPA reversal
/*
,xdl.entered_amount
,xdl.ledger_amount
-- populates entered_dr. amount should be equal to the entered amount in distribution links
,DECODE(ael.entered_cr,NULL,NULL,xdl.entered_amount)
-- populates entered_cr. amount should be equal to the entered amount in distribution links
,DECODE(ael.entered_dr,NULL,NULL,xdl.entered_amount)
-- populates accounted_dr. amount should be equal to the ledger amount in distribution links
,DECODE(ael.accounted_cr,NULL,NULL,xdl.ledger_amount)
-- populates accounted_cr. amount should be equal to the ledger amount in distribution links
,DECODE(ael.accounted_dr,NULL,NULL,xdl.ledger_amount)
*/
-- ,ael.entered_cr
-- ,ael.entered_dr
-- ,ael.accounted_cr
-- ,ael.accounted_dr
-- bug8642358 ,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,decode(ael.gain_or_loss_flag, 'Y', 0.01, nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision)))
,ael.currency_code
,ael.currency_conversion_date
,ael.currency_conversion_rate
,ael.currency_conversion_type
,ael.statistical_amount
,ael.party_id
,ael.party_site_id
,ael.party_type_code
,ael.ussgl_transaction_code
,ael.jgzz_recon_ref
-- if there are no distributions for this event
-- populate with the distribution if lines that is being reversed
,NVL(l_array_tax_line_ref(i),xdl.tax_line_ref_id) -- bug7159711
,NVL(l_array_tax_summary_line_ref(i),xdl.tax_summary_line_ref_id) -- bug7159711
,NVL(l_array_tax_rec_nrec_dist_ref(i),xdl.tax_rec_nrec_dist_ref_id) -- bug7159711
,NVL(l_array_source_dist_id_char_1(i),xdl.source_distribution_id_char_1)
,NVL(l_array_source_dist_id_char_2(i),xdl.source_distribution_id_char_2)
,NVL(l_array_source_dist_id_char_3(i),xdl.source_distribution_id_char_3)
,NVL(l_array_source_dist_id_char_4(i),xdl.source_distribution_id_char_4)
,NVL(l_array_source_dist_id_char_5(i),xdl.source_distribution_id_char_5)
,NVL(l_array_source_dist_id_num_1(i),xdl.source_distribution_id_num_1)
,NVL(l_array_source_dist_id_num_2(i),xdl.source_distribution_id_num_2)
,NVL(l_array_source_dist_id_num_3(i),xdl.source_distribution_id_num_3)
,NVL(l_array_source_dist_id_num_4(i),xdl.source_distribution_id_num_4)
,NVL(l_array_source_dist_id_num_5(i),xdl.source_distribution_id_num_5)
,NVL(l_array_source_dist_type(i),xdl.source_distribution_type)
,xdl.source_distribution_id_char_1
,xdl.source_distribution_id_char_2
,xdl.source_distribution_id_char_3
,xdl.source_distribution_id_char_4
,xdl.source_distribution_id_char_5
,xdl.source_distribution_id_NUM_1
,xdl.source_distribution_id_NUM_2
,xdl.source_distribution_id_NUM_3
,xdl.source_distribution_id_NUM_4
,xdl.source_distribution_id_NUM_5
,xdl.source_distribution_type
-- populate reversal_code indicating that line is result of reversal
,'REVERSAL'
-- denormalises entry status from headers to line to determine the status of entry being created
-- populates 0 for a valid entry (F or D) else poulates 1
,DECODE(aeh.accounting_entry_status_code,'F',0,'D',0,1)
,ael.encumbrance_type_id -- 4458381
,'N' -- 4219869 inherit_desc_flag Should it be from l_array?
-- ,0 -- 4669308 combine header with reversal of original entry -- commented for bug8505463
, DECODE(aeh.parent_ae_header_id , NULL , 0 ,
CASE
WHEN aeh.accounting_date <= l_array_gl_date(i)
THEN 0
ELSE -1 * dense_rank() over ( partition by aeh.parent_ae_header_id , aeh.parent_ae_line_num
order by ael.ae_header_Id )
END
) HEADER_NUM -- added for bug8505463 for MPA Cancellation
,'N' -- 4262811 mpa_accrual_entry_flag
-- ,l_array_gl_date(i) -- 5189664 aeh.accounting_date -- 4955764 -- commented for bug8505463
, DECODE(aeh.parent_ae_header_id , NULL , l_array_gl_date(i) , -- Regular Cancellation, hence Cancellation Event Date
CASE
WHEN aeh.accounting_date <= l_array_gl_date(i)
THEN l_array_gl_date(i) -- Accounting Date of Cancellation Event for prior Period's MPA Accounting
ELSE aeh.accounting_date -- Accounting Date of Original MPA Accounting
END
) ACCOUNTING_DATE -- added for bug8505463 for MPA Cancellation
, xdl.APPLIED_TO_APPLICATION_ID APPLIED_TO_APPLICATION_ID
, xdl.APPLIED_TO_ENTITY_CODE APPLIED_TO_ENTITY_CODE
, xdl.APPLIED_TO_ENTITY_ID APPLIED_TO_ENTITY_ID
, xdl.APPLIED_TO_SOURCE_ID_NUM_1 APPLIED_TO_SOURCE_ID_NUM_1
, xdl.APPLIED_TO_SOURCE_ID_NUM_2 APPLIED_TO_SOURCE_ID_NUM_2
, xdl.APPLIED_TO_SOURCE_ID_NUM_3 APPLIED_TO_SOURCE_ID_NUM_3
, xdl.APPLIED_TO_SOURCE_ID_NUM_4 APPLIED_TO_SOURCE_ID_NUM_4
, xdl.APPLIED_TO_SOURCE_ID_CHAR_1 APPLIED_TO_SOURCE_ID_CHAR_1
, xdl.APPLIED_TO_SOURCE_ID_CHAR_2 APPLIED_TO_SOURCE_ID_CHAR_2
, xdl.APPLIED_TO_SOURCE_ID_CHAR_3 APPLIED_TO_SOURCE_ID_CHAR_3
, xdl.APPLIED_TO_SOURCE_ID_CHAR_4 APPLIED_TO_SOURCE_ID_CHAR_4
, xdl.APPLIED_TO_DISTRIBUTION_TYPE APPLIED_TO_DISTRIBUTION_TYPE
, xdl.APPLIED_TO_DIST_ID_NUM_1 APPLIED_TO_DIST_ID_NUM_1
, xdl.APPLIED_TO_DIST_ID_NUM_2 APPLIED_TO_DIST_ID_NUM_2
, xdl.APPLIED_TO_DIST_ID_NUM_3 APPLIED_TO_DIST_ID_NUM_3
, xdl.APPLIED_TO_DIST_ID_NUM_4 APPLIED_TO_DIST_ID_NUM_4
, xdl.APPLIED_TO_DIST_ID_NUM_5 APPLIED_TO_DIST_ID_NUM_5
, xdl.APPLIED_TO_DIST_ID_CHAR_1 APPLIED_TO_DIST_ID_CHAR_1
, xdl.APPLIED_TO_DIST_ID_CHAR_2 APPLIED_TO_DIST_ID_CHAR_2
, xdl.APPLIED_TO_DIST_ID_CHAR_3 APPLIED_TO_DIST_ID_CHAR_3
, xdl.APPLIED_TO_DIST_ID_CHAR_4 APPLIED_TO_DIST_ID_CHAR_4
, xdl.APPLIED_TO_DIST_ID_CHAR_5 APPLIED_TO_DIST_ID_CHAR_5
, xdl.alloc_to_application_id alloc_to_application_id
, xdl.alloc_to_entity_code alloc_to_entity_code
, xdl.alloc_to_source_id_num_1 alloc_to_source_id_num_1
, xdl.alloc_to_source_id_num_2 alloc_to_source_id_num_2
, xdl.alloc_to_source_id_num_3 alloc_to_source_id_num_3
, xdl.alloc_to_source_id_num_4 alloc_to_source_id_num_4
, xdl.alloc_to_source_id_char_1 alloc_to_source_id_char_1
, xdl.alloc_to_source_id_char_2 alloc_to_source_id_char_2
, xdl.alloc_to_source_id_char_3 alloc_to_source_id_char_3
, xdl.alloc_to_source_id_char_4 alloc_to_source_id_char_4
, xdl.alloc_to_distribution_type alloc_to_distribution_type
, xdl.alloc_to_dist_id_char_1 alloc_to_dist_id_char_1
, xdl.alloc_to_dist_id_char_2 alloc_to_dist_id_char_2
, xdl.alloc_to_dist_id_char_3 alloc_to_dist_id_char_3
, xdl.alloc_to_dist_id_char_4 alloc_to_dist_id_char_4
, xdl.alloc_to_dist_id_char_5 alloc_to_dist_id_char_5
, xdl.alloc_to_dist_id_num_1 alloc_to_dist_id_num_1
, xdl.alloc_to_dist_id_num_2 alloc_to_dist_id_num_2
, xdl.alloc_to_dist_id_num_3 alloc_to_dist_id_num_3
, xdl.alloc_to_dist_id_num_4 alloc_to_dist_id_num_4
, xdl.alloc_to_dist_id_num_5 alloc_to_dist_id_num_5
, decode(ael.analytical_balance_flag, 'Y','P',
'P','P',
null) analytical_balance_flag --8417496
,nvl2(ael.analytical_balance_flag,'DUMMY_ANC_'||ael.ae_header_id||ael.ae_line_num,null) ANC_ID_1 --Bug 8691573
FROM
xla_ae_lines ael
,xla_ae_headers aeh
,xla_distribution_links xdl
,fnd_currencies fcu
,xla_events xe
WHERE aeh.application_id = l_application_id
AND aeh.ledger_id = l_array_ledger_id(i)
AND aeh.entity_id = l_array_entity_id(i)
AND aeh.ae_header_id = ael.ae_header_id /* bug 9194744 */
-- AND aeh.event_number < l_array_event_number(i)
AND xdl.application_id = aeh.application_id
AND xdl.ae_header_id = aeh.ae_header_id -- 5499367
-- AND xdl.ref_temp_line_num IS NULL -- 5019460 old
AND NVL(xdl.temp_line_num,0) >= 0 -- 5019460 new
AND ael.application_id = xdl.application_id
AND ael.ae_header_id = xdl.ae_header_id -- 5499367
AND ael.ae_line_num = xdl.ae_line_num
AND ael.currency_code = fcu.currency_code
AND NOT EXISTS (
SELECT /*+ no_unnest */ 1
FROM xla_distribution_links
WHERE ref_ae_header_id = xdl.ae_header_id
AND temp_line_num = xdl.temp_line_num * -1
AND application_id = xdl.application_id
)
AND NOT EXISTS (
SELECT /*+ no_unnest */ 1
FROM xla_ae_lines_gt
WHERE ref_ae_header_id = xdl.ae_header_id
AND temp_line_num = xdl.temp_line_num * -1
AND ledger_id = l_array_ledger_id(i)
)
AND xe.application_id = aeh.application_id
AND xe.event_id = aeh.event_id
AND NVL(xe.budgetary_control_flag,'N') = DECODE(p_accounting_mode
,'FUNDS_CHECK','Y'
,'FUNDS_RESERVE','Y'
,'N')
AND ((aeh.parent_ae_header_id IS NOT NULL AND aeh.accounting_entry_status_code = 'F') OR -- 4669308
(aeh.parent_ae_header_id IS NULL)) -- 4669308
GROUP BY
(0 - xdl.temp_line_num)
,ael.ae_header_id
,ael.ae_line_num
,xdl.temp_line_num
,xdl.event_id
,aeh.balance_type_code
,ael.accounting_class_code
,xdl.event_class_code
,xdl.event_type_code
,xdl.line_definition_owner_code
,xdl.line_definition_code
,xdl.accounting_line_type_code
,xdl.accounting_line_code
,xdl.unrounded_entered_cr
,xdl.unrounded_entered_dr
,xdl.unrounded_accounted_cr
,xdl.unrounded_accounted_dr
,xdl.calculate_acctd_amts_flag
,xdl.calculate_g_l_amts_flag
,ael.gain_or_loss_flag
,xdl.rounding_class_code
,xdl.document_rounding_level
,xdl.doc_rounding_acctd_amt
,xdl.doc_rounding_entered_amt
,ael.code_combination_id
,ael.description
,ael.gl_transfer_mode_code
,xdl.merge_duplicate_code
-- ,ael.entered_cr
-- ,ael.entered_dr
-- ,ael.accounted_cr
-- ,ael.accounted_dr
,nvl(fcu.minimum_accountable_unit, power(10, -1* fcu.precision))
,ael.currency_code
,ael.currency_conversion_date
,ael.currency_conversion_rate
,ael.currency_conversion_type
,ael.statistical_amount
,ael.party_id
,ael.party_site_id
,ael.party_type_code
,ael.ussgl_transaction_code
,ael.jgzz_recon_ref
,aeh.accounting_entry_status_code
,ael.encumbrance_type_id
,xdl.tax_line_ref_id -- bug7226263
,xdl.tax_summary_line_ref_id -- bug7226263
,xdl.tax_rec_nrec_dist_ref_id -- bug7226263
,xdl.source_distribution_id_char_1
,xdl.source_distribution_id_char_2
,xdl.source_distribution_id_char_3
,xdl.source_distribution_id_char_4
,xdl.source_distribution_id_char_5
,xdl.source_distribution_id_NUM_1
,xdl.source_distribution_id_NUM_2
,xdl.source_distribution_id_NUM_3
,xdl.source_distribution_id_NUM_4
,xdl.source_distribution_id_NUM_5
,xdl.source_distribution_type
,aeh.accounting_date
, aeh.parent_ae_header_id -- added for bug8505463
, aeh.parent_ae_line_num -- added for bug8505463
, xdl.APPLIED_TO_APPLICATION_ID
, xdl.APPLIED_TO_ENTITY_CODE
, xdl.APPLIED_TO_ENTITY_ID
, xdl.APPLIED_TO_SOURCE_ID_NUM_1
, xdl.APPLIED_TO_SOURCE_ID_NUM_2
, xdl.APPLIED_TO_SOURCE_ID_NUM_3
, xdl.APPLIED_TO_SOURCE_ID_NUM_4
, xdl.APPLIED_TO_SOURCE_ID_CHAR_1
, xdl.APPLIED_TO_SOURCE_ID_CHAR_2
, xdl.APPLIED_TO_SOURCE_ID_CHAR_3
, xdl.APPLIED_TO_SOURCE_ID_CHAR_4
, xdl.APPLIED_TO_DISTRIBUTION_TYPE
, xdl.APPLIED_TO_DIST_ID_NUM_1
, xdl.APPLIED_TO_DIST_ID_NUM_2
, xdl.APPLIED_TO_DIST_ID_NUM_3
, xdl.APPLIED_TO_DIST_ID_NUM_4
, xdl.APPLIED_TO_DIST_ID_NUM_5
, xdl.APPLIED_TO_DIST_ID_CHAR_1
, xdl.APPLIED_TO_DIST_ID_CHAR_2
, xdl.APPLIED_TO_DIST_ID_CHAR_3
, xdl.APPLIED_TO_DIST_ID_CHAR_4
, xdl.APPLIED_TO_DIST_ID_CHAR_5
, xdl.alloc_to_application_id
, xdl.alloc_to_entity_code
, xdl.alloc_to_source_id_num_1
, xdl.alloc_to_source_id_num_2
, xdl.alloc_to_source_id_num_3
, xdl.alloc_to_source_id_num_4
, xdl.alloc_to_source_id_char_1
, xdl.alloc_to_source_id_char_2
, xdl.alloc_to_source_id_char_3
, xdl.alloc_to_source_id_char_4
, xdl.alloc_to_distribution_type
, xdl.alloc_to_dist_id_char_1
, xdl.alloc_to_dist_id_char_2
, xdl.alloc_to_dist_id_char_3
, xdl.alloc_to_dist_id_char_4
, xdl.alloc_to_dist_id_char_5
, xdl.alloc_to_dist_id_num_1
, xdl.alloc_to_dist_id_num_2
, xdl.alloc_to_dist_id_num_3
, xdl.alloc_to_dist_id_num_4
, xdl.alloc_to_dist_id_num_5
, ael.analytical_balance_flag --8417496
, nvl2(ael.analytical_balance_flag,'DUMMY_ANC_'||ael.ae_header_id||ael.ae_line_num,null) --Bug 8691573
;
用优化后的程序过账那些已冲销的收款数据，处理500单，只用了不到5分钟。
|
|
|Archiver|小黑屋|手机版|壹佰网 ERP100
( 京ICP备19053597号-2 )
IP卡
狗仔卡
发表于 2012/11/27 16:34:18
该贴已经同步到 
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
抢沙发
千斤顶
显身卡
发表于 2013/1/9 15:24:15