Hi Guys,
Appreciate some insights to tune performance of below plsql block..
This is a standard IFS upgrade script from Apps10 and was executing 72+ hours when I was running an upgrade from 7.5 version with no blocking locks what so ever and DB is not in archived log mode.
DB server has adequate memory and there are no issues with the SGA reported in AWR during the execution time.
I have not seen any other potential SQL/ plsql codes in the analysis reports and this particular block is the culprit of taking longer time to finish the upgrade
exec Database_SYS.Log_Detail_Time_Stamp('INVOIC','300.upg','Timestamp_140');
PROMPT Updating columns in invoice_tab, invoice_item_tab and tax_item_tab
DECLARE
non_deduct_tax_dom_amount_ NUMBER;
non_ded_tax_parallel_amt_ NUMBER;
tot_non_deduct_tax_dom_amount_ NUMBER;
tot_non_ded_tax_parallel_amt_ NUMBER;
currency_rounding_ NUMBER;
inverted_ VARCHAR2(5);
actual_net_dom_amount_ NUMBER;
actual_net_parallel_amount_ NUMBER;
inv_actual_net_dom_amt_ NUMBER;
inv_actual_net_parallel_amt_ NUMBER;
inv_non_deduct_tax_dom_amt_ NUMBER;
inv_non_ded_tax_parallel_amt_ NUMBER;
curr_rate_ NUMBER;
vat_method_ VARCHAR2(20);
vat_received_ VARCHAR2(20);
vat_disbursed_ VARCHAR2(20);
acc_currency_ VARCHAR2(3);
parallel_acc_currency_ VARCHAR2(3);
parallel_base_ VARCHAR2(25);
parallel_inverted_ VARCHAR2(5);
accounting_currency_rounding_ NUMBER;
accounting_currency_inverted_ VARCHAR2(5);
parallel_currency_rounding_ NUMBER;
parallel_currency_inverted_ VARCHAR2(5);
parallel_amount_base_ NUMBER;
new_parallel_rate_ NUMBER;
new_parallel_inverted_ VARCHAR2(5);
ref_curr_inverted_ VARCHAR2(5);
curr_code_to_use_ VARCHAR2(3);
ref_curr_code_to_use_ VARCHAR2(3);
is_ref_curr_code_emu_ VARCHAR2(5);
is_curr_code_emu_ VARCHAR2(5);
trans_emu_curr_from_date_ DATE;
acc_emu_curr_from_date_ DATE;
parallel_emu_curr_from_date_ DATE;
TYPE comp_data_rec IS RECORD (
company VARCHAR2(20),
currency_code VARCHAR2(3),
parallel_acc_currency VARCHAR2(3),
parallel_base VARCHAR2(25));
TYPE comp_data_tab IS TABLE OF comp_data_rec INDEX BY PLS_INTEGER;
comp_tab_ comp_data_tab;
current_company_ VARCHAR2(20);
CURSOR get_companies IS
SELECT company, currency_code, parallel_acc_currency, parallel_base
FROM company_finance_tab;
CURSOR get_invoices(company_ IN VARCHAR2) IS
SELECT it.company, it.invoice_id, it.currency, it.curr_rate, it.tax_curr_rate, it.div_factor, it.adv_inv, it.creator,
it.parallel_curr_rate, it.parallel_div_factor, it.invoice_date
FROM invoice_tab it
WHERE it.company = company_
AND it.party_type = 'SUPPLIER'
AND it.actual_net_dom_amount IS NULL;
CURSOR get_curr (company_ IN VARCHAR2, currency_ IN VARCHAR2) IS
SELECT cc.currency_rounding, cc.inverted, cc.emu_currency_from_date
FROM currency_code_tab cc
WHERE cc.company = company_
AND cc.currency_code = currency_;
CURSOR get_inv_items(company_ IN VARCHAR2, invoice_id_ IN NUMBER) IS
SELECT iit.item_id, iit.actual_net_curr_amount, iit.net_parallel_amount, iit.corrected_item_id
FROM invoice_item_tab iit
WHERE iit.non_deduct_tax_dom_amount IS NULL
AND iit.party_type = 'SUPPLIER'
AND iit.company = company_
AND iit.invoice_id = invoice_id_;
CURSOR get_tax_items(company_ IN VARCHAR2, invoice_id_ IN NUMBER, item_id_ IN NUMBER) IS
SELECT t.tax_id, t.fee_code, t.non_deduct_tax_curr_amount, t.tax_parallel_amount
FROM tax_item_tab t
WHERE t.non_deduct_tax_dom_amount IS NULL
AND t.party_type = 'SUPPLIER'
AND t.company = company_
AND t.invoice_id = invoice_id_
AND t.item_id = item_id_;
CURSOR get_tax_details(company_ IN VARCHAR2, fee_code_ IN VARCHAR2) IS
SELECT s.vat_received, s.vat_disbursed
FROM statutory_fee_tab s
WHERE s.company = company_
AND s.fee_code = fee_code_;
BEGIN
OPEN get_companies;
FETCH get_companies BULK COLLECT INTO comp_tab_;
CLOSE get_companies;
FOR i_ IN 1..comp_tab_.COUNT LOOP
-- use local variables to set the current company data that is to be processed
current_company_ := comp_tab_(i_).company;
acc_currency_ := comp_tab_(i_).currency_code;
parallel_acc_currency_ := comp_tab_(i_).parallel_acc_currency;
parallel_base_ := comp_tab_(i_).parallel_base;
parallel_currency_rounding_ := 0;
parallel_currency_inverted_ := 'FALSE';
parallel_emu_curr_from_date_ := NULL;
OPEN get_curr(current_company_, acc_currency_);
FETCH get_curr INTO accounting_currency_rounding_, accounting_currency_inverted_, acc_emu_curr_from_date_;
CLOSE get_curr;
accounting_currency_rounding_ := NVL(accounting_currency_rounding_, 2);
accounting_currency_inverted_ := NVL(accounting_currency_inverted_, 'FALSE');
IF (parallel_acc_currency_ IS NOT NULL) THEN
OPEN get_curr(current_company_, parallel_acc_currency_);
FETCH get_curr INTO parallel_currency_rounding_, parallel_currency_inverted_, parallel_emu_curr_from_date_;
CLOSE get_curr;
END IF;
parallel_currency_rounding_ := NVL(parallel_currency_rounding_, 2);
parallel_currency_inverted_ := NVL(parallel_currency_inverted_, 'FALSE');
-- Invoice Loop
FOR inv_ IN get_invoices(current_company_) LOOP
OPEN get_curr(inv_.company, inv_.currency);
FETCH get_curr INTO currency_rounding_, inverted_, trans_emu_curr_from_date_;
CLOSE get_curr;
currency_rounding_ := NVL(currency_rounding_, 2);
inverted_ := NVL(inverted_, 'FALSE');
IF (parallel_acc_currency_ IS NOT NULL) THEN
-- Parallel currency inverted, begin
IF (parallel_base_ = 'TRANSACTION_CURRENCY') THEN
curr_code_to_use_ := inv_.currency;
ref_curr_code_to_use_ := parallel_acc_currency_;
ref_curr_inverted_ := parallel_currency_inverted_;
IF (trans_emu_curr_from_date_ <= inv_.invoice_date) THEN
is_curr_code_emu_ := 'TRUE';
ELSIF (trans_emu_curr_from_date_ IS NULL) OR (trans_emu_curr_from_date_ > inv_.invoice_date) THEN
is_curr_code_emu_ := 'FALSE';
END IF;
IF (parallel_emu_curr_from_date_ <= inv_.invoice_date) THEN
is_ref_curr_code_emu_ := 'TRUE';
ELSIF (parallel_emu_curr_from_date_ IS NULL) OR (parallel_emu_curr_from_date_ > inv_.invoice_date) THEN
is_ref_curr_code_emu_ := 'FALSE';
END IF;
ELSE
curr_code_to_use_ := parallel_acc_currency_;
ref_curr_code_to_use_ := acc_currency_;
ref_curr_inverted_ := accounting_currency_inverted_;
IF (parallel_emu_curr_from_date_ <= inv_.invoice_date) THEN
is_curr_code_emu_ := 'TRUE';
ELSIF (parallel_emu_curr_from_date_ IS NULL) OR (parallel_emu_curr_from_date_ > inv_.invoice_date) THEN
is_curr_code_emu_ := 'FALSE';
END IF;
IF (acc_emu_curr_from_date_ <= inv_.invoice_date) THEN
is_ref_curr_code_emu_ := 'TRUE';
ELSIF (acc_emu_curr_from_date_ IS NULL) OR (acc_emu_curr_from_date_ > inv_.invoice_date) THEN
is_ref_curr_code_emu_ := 'FALSE';
END IF;
END IF;
IF (ref_curr_code_to_use_ != 'EUR') THEN
IF (is_ref_curr_code_emu_ = 'FALSE' AND is_curr_code_emu_ = 'TRUE') THEN
IF (ref_curr_inverted_ = 'FALSE') THEN
new_parallel_inverted_ := 'FALSE';
ELSE
new_parallel_inverted_ := 'TRUE';
END IF;
ELSIF (is_ref_curr_code_emu_ = 'TRUE' AND is_curr_code_emu_ = 'TRUE') THEN
new_parallel_inverted_ := 'FALSE';
ELSIF (is_ref_curr_code_emu_ = 'TRUE' AND is_curr_code_emu_ = 'FALSE') THEN
new_parallel_inverted_ := 'FALSE';
ELSE
IF (ref_curr_inverted_ = 'FALSE') THEN
new_parallel_inverted_ := 'FALSE';
ELSE
new_parallel_inverted_ := 'TRUE';
END IF;
END IF;
ELSE
IF (ref_curr_inverted_ = 'FALSE') THEN
new_parallel_inverted_ := 'FALSE';
ELSE
new_parallel_inverted_ := 'TRUE';
END IF;
END IF;
-- Parallel currency inverted, end
END IF;
inv_actual_net_dom_amt_ := 0;
inv_actual_net_parallel_amt_ := 0;
inv_non_deduct_tax_dom_amt_ := 0;
inv_non_ded_tax_parallel_amt_ := 0;
-- Invoice Item Loop
FOR items_ IN get_inv_items(inv_.company, inv_.invoice_id) LOOP
tot_non_deduct_tax_dom_amount_ := 0;
tot_non_ded_tax_parallel_amt_ := 0;
-- Tax Item Loop
FOR rec_ IN get_tax_items(inv_.company, inv_.invoice_id, items_.item_id) LOOP
OPEN get_tax_details(inv_.company, rec_.fee_code);
FETCH get_tax_details INTO vat_received_, vat_disbursed_;
CLOSE get_tax_details;
IF (inv_.creator != 'OUTGOING_SUPPLIER_INVOICE_API') THEN
vat_method_ := vat_received_;
ELSE
vat_method_ := vat_disbursed_;
END IF;
IF (vat_method_ IN ('1','2') AND inv_.adv_inv = 'FALSE') THEN
curr_rate_ := inv_.tax_curr_rate;
ELSE
curr_rate_ := inv_.curr_rate;
END IF;
IF (accounting_currency_inverted_ = 'FALSE' AND inv_.div_factor IS NOT NULL AND inv_.div_factor != 0) THEN
non_deduct_tax_dom_amount_ := (rec_.non_deduct_tax_curr_amount * curr_rate_) / inv_.div_factor;
ELSIF (accounting_currency_inverted_ = 'TRUE' AND inv_.curr_rate IS NOT NULL AND inv_.curr_rate != 0) THEN
non_deduct_tax_dom_amount_ := (rec_.non_deduct_tax_curr_amount * inv_.div_factor) / curr_rate_;
END IF;
non_deduct_tax_dom_amount_ := ROUND(NVL(non_deduct_tax_dom_amount_, 0), accounting_currency_rounding_);
-- Parallel Begin
non_ded_tax_parallel_amt_ := NULL;
IF (rec_.tax_parallel_amount IS NOT NULL AND parallel_acc_currency_ IS NOT NULL) THEN
--inv_.company, non_deduct_tax_dom_amount_, rec_.non_deduct_tax_curr_amount, acc_currency_, inv_.currency,
--inv_.parallel_curr_rate, inv_.parallel_div_factor, parallel_inverted_ (need find), parallel_acc_currency_,
--parallel_base_, parallel_currency_rounding_
IF (parallel_acc_currency_ = acc_currency_) THEN
non_ded_tax_parallel_amt_ := non_deduct_tax_dom_amount_;
ELSIF (parallel_acc_currency_ = inv_.currency) THEN
non_ded_tax_parallel_amt_ := rec_.non_deduct_tax_curr_amount;
ELSE
IF NOT ((inv_.parallel_curr_rate = 0) OR (inv_.parallel_curr_rate IS NULL)) THEN
IF (parallel_base_ = 'TRANSACTION_CURRENCY') THEN
parallel_amount_base_ := rec_.non_deduct_tax_curr_amount;
IF (new_parallel_inverted_ = 'TRUE') THEN
new_parallel_rate_ := 1 / (inv_.parallel_curr_rate / inv_.parallel_div_factor);
ELSE
new_parallel_rate_ := inv_.parallel_curr_rate / inv_.parallel_div_factor;
END IF;
ELSIF (parallel_base_ = 'ACCOUNTING_CURRENCY') THEN
parallel_amount_base_ := non_deduct_tax_dom_amount_;
IF (new_parallel_inverted_ = 'TRUE') THEN
new_parallel_rate_ := inv_.parallel_curr_rate / inv_.parallel_div_factor;
ELSE
new_parallel_rate_ := 1 / (inv_.parallel_curr_rate / inv_.parallel_div_factor);
END IF;
END IF;
non_ded_tax_parallel_amt_ := parallel_amount_base_ * new_parallel_rate_;
END IF;
END IF;
IF (non_ded_tax_parallel_amt_ IS NOT NULL) THEN
non_ded_tax_parallel_amt_ := ROUND(non_ded_tax_parallel_amt_, parallel_currency_rounding_);
END IF;
END IF;
non_ded_tax_parallel_amt_ := ROUND(NVL(non_ded_tax_parallel_amt_, 0), parallel_currency_rounding_);
-- Parallel End
-- 1) Update Tax_Item_Tab
UPDATE tax_item_tab t
SET t.non_deduct_tax_dom_amount = non_deduct_tax_dom_amount_,
t.non_ded_tax_parallel_amt = non_ded_tax_parallel_amt_
WHERE t.company = inv_.company
AND t.invoice_id = inv_.invoice_id
AND t.item_id = items_.item_id
AND t.tax_id = rec_.tax_id;
tot_non_deduct_tax_dom_amount_ := tot_non_deduct_tax_dom_amount_ + non_deduct_tax_dom_amount_;
tot_non_ded_tax_parallel_amt_ := tot_non_ded_tax_parallel_amt_ + non_ded_tax_parallel_amt_;
END LOOP;
IF (accounting_currency_inverted_ = 'FALSE' AND inv_.div_factor IS NOT NULL AND inv_.div_factor != 0) THEN
actual_net_dom_amount_ := (items_.actual_net_curr_amount * inv_.curr_rate) / inv_.div_factor;
ELSIF (accounting_currency_inverted_ = 'TRUE' AND inv_.curr_rate IS NOT NULL AND inv_.curr_rate != 0) THEN
actual_net_dom_amount_ := (items_.actual_net_curr_amount * inv_.div_factor) / inv_.curr_rate;
END IF;
actual_net_dom_amount_ := ROUND(NVL(actual_net_dom_amount_, 0), accounting_currency_rounding_);
-- Parallel Begin
actual_net_parallel_amount_ := NULL;
IF (items_.net_parallel_amount IS NOT NULL AND parallel_acc_currency_ IS NOT NULL) THEN
--inv_.company, actual_net_dom_amount_, items_.actual_net_curr_amount, acc_currency_, inv_.currency,
--inv_.parallel_curr_rate, inv_.parallel_div_factor, parallel_inverted_ (need find), parallel_acc_currency_,
--parallel_base_, parallel_currency_rounding_
IF (parallel_acc_currency_ = acc_currency_) THEN
actual_net_parallel_amount_ := actual_net_dom_amount_;
ELSIF (parallel_acc_currency_ = inv_.currency) THEN
actual_net_parallel_amount_ := items_.actual_net_curr_amount;
ELSE
IF NOT ((inv_.parallel_curr_rate = 0) OR (inv_.parallel_curr_rate IS NULL)) THEN
IF (parallel_base_ = 'TRANSACTION_CURRENCY') THEN
parallel_amount_base_ := items_.actual_net_curr_amount;
IF (new_parallel_inverted_ = 'TRUE') THEN
new_parallel_rate_ := 1 / (inv_.parallel_curr_rate / inv_.parallel_div_factor);
ELSE
new_parallel_rate_ := inv_.parallel_curr_rate / inv_.parallel_div_factor;
END IF;
ELSIF (parallel_base_ = 'ACCOUNTING_CURRENCY') THEN
parallel_amount_base_ := actual_net_dom_amount_;
IF (new_parallel_inverted_ = 'TRUE') THEN
new_parallel_rate_ := inv_.parallel_curr_rate / inv_.parallel_div_factor;
ELSE
new_parallel_rate_ := 1 / (inv_.parallel_curr_rate / inv_.parallel_div_factor);
END IF;
END IF;
actual_net_parallel_amount_ := parallel_amount_base_ * new_parallel_rate_;
END IF;
END IF;
IF (actual_net_parallel_amount_ IS NOT NULL) THEN
actual_net_parallel_amount_ := ROUND(actual_net_parallel_amount_, parallel_currency_rounding_);
END IF;
END IF;
actual_net_parallel_amount_ := ROUND(NVL(actual_net_parallel_amount_, 0), parallel_currency_rounding_);
-- Parallel End
-- 2) Update Invoice_Item_Tab
UPDATE invoice_item_tab iit
SET iit.actual_net_dom_amount = actual_net_dom_amount_,
iit.actual_net_parallel_amount = actual_net_parallel_amount_,
iit.non_deduct_tax_dom_amount = tot_non_deduct_tax_dom_amount_,
iit.non_ded_tax_parallel_amt = tot_non_ded_tax_parallel_amt_
WHERE iit.company = inv_.company
AND iit.invoice_id = inv_.invoice_id
AND iit.item_id = items_.item_id;
-- This IF condition is to filter out cancelled invoices
IF (items_.corrected_item_id IS NULL) THEN
inv_actual_net_dom_amt_ := inv_actual_net_dom_amt_ + actual_net_dom_amount_;
inv_actual_net_parallel_amt_ := inv_actual_net_parallel_amt_ + actual_net_parallel_amount_;
inv_non_deduct_tax_dom_amt_ := inv_non_deduct_tax_dom_amt_ + tot_non_deduct_tax_dom_amount_;
inv_non_ded_tax_parallel_amt_ := inv_non_ded_tax_parallel_amt_ + tot_non_ded_tax_parallel_amt_;
END IF;
END LOOP;
-- 3) Update Invoice_Tab
UPDATE invoice_tab it
SET it.actual_net_dom_amount = inv_actual_net_dom_amt_,
it.actual_net_parallel_amount = inv_actual_net_parallel_amt_,
it.non_deduct_tax_dom_amount = inv_non_deduct_tax_dom_amt_,
it.non_ded_tax_parallel_amt = inv_non_ded_tax_parallel_amt_
WHERE it.company = inv_.company
AND it.invoice_id = inv_.invoice_id;
END LOOP;
END LOOP;
COMMIT;
END;
/
I will be attaching the ASH analysis in the first comment
