Public API PA_FC_API.RESERVE_FUNDS Performance is Poor (Doc ID 2890177.1)

Last updated on AUGUST 17, 2022

Applies to:

Symptoms

 
Testing PA_FC_API.RESERVE_FUNDS
The process is running for long time, takes about 45 minutes
 
Trace file following SQL which is performing poor:

WITH exp_id as (SELECT DISTINCT orig_transaction_reference , pts.transaction_source
                                       FROM
                                          pa_expenditure_items_all ei,pa_transaction_sources PTS,
                                          pa_cost_distribution_lines_all   cdl
                                       WHERE cdl.system_reference2 = :p_document_header_id
                                       AND cdl.system_reference3 = :p_document_distribution_id
                                       AND cdl.system_reference2 IS NOT NULL
                                       AND cdl.system_reference3 IS NOT NULL
                                       --AND cdl.transfer_status_code = 'V'
-- This needs to be removed when we open FC for only costed transactions.
                                       AND cdl.line_type = 'R'
                                       AND ei.expenditure_item_id= cdl.expenditure_item_id
                                       AND decode(ei.system_linkage_function,'ST','LABOR','OT','LABOR','INV','INV','WIP', 'WIP','PJ','MISC','USG','USAGES') = p_document_type
                                       AND    pts.transaction_source= EI.transaction_source
                                       AND    pts.process_funds_check = 'Y'
                                       AND    nvl(pts.predefined_flag,'N') = 'N'
                                       )
              SELECT nvl(sum(raw_cost) ,0), nvl(sum(nd_flag),0)
              FROM   (                   SELECT sum(ei.acct_raw_cost) raw_cost, sum(decode(ei.cost_distributed_flag, 'N',1,0)) nd_flag
                 FROM   pa_expenditure_items_all EI,
                        --pa_budgetary_control_options pbco,
                        exp_id
                 WHERE  EI.transaction_source is NOT NULL
                 AND    decode(EI.system_linkage_function,'ST','LABOR','OT','LABOR','INV','INV','WIP', 'WIP','PJ','MISC','USG','USAGES') = :p_document_type
                 AND    EI.orig_transaction_reference = exp_id.orig_transaction_reference
                 AND    EI.transaction_source = exp_id.transaction_source);

STEPS:
------
1. Use simple code calling API
Psudo code PL/SQL block:
DECLARE
< declare variables>
BEGIN
..
Call pa_interface_utils_pub.set_global_info to set global variables

Assign all paramaters
p_project_id, p_task_id, p_expenditure_type, p_exp_org_id , p_exp_item_date, p_gl_date  , p_document_type , p_document_header_id,
p_document_distribution_id, p_amount, p_org_id , p_person_id , p_sys_linkage_function , p_txn_amount,  p_txn_ccid, p_source_event and all

Call   pa_fc_api.check_funds API

Then call pa_fc_api.reserve_funds API

2. Verify the peformance and any errors.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.