R12.1.3: Poor Performance of GLPAUTOP Program - Automatic Posting Program (Doc ID 1600340.1)

Last updated on NOVEMBER 20, 2016

Applies to:

Oracle General Ledger - Version 12.1.3 and later
Information in this document applies to any platform.
Executable:GLPAUTOP - Program - Automatic Posting

Symptoms

On : 12.1.3 version, Posting

There is performance issue in Automatic Posting program. Autopost program search many times for batches with a criteria provided and each search is taking time.
This following select statement is executed many times for different criteria provided.

SELECT B.je_batch_id, substrb(B.name,1,75), B.status,
B.default_period_name, B.actual_flag, B.request_id
FROM GL_JE_BATCHES B
WHERE B.chart_of_accounts_id = 101
AND B.period_set_name = 'SSA Calendar'
AND B.accounted_period_type = '1'
AND (B.status < 'P' OR B.status > 'P')
AND B.status_verified = 'N'
AND greatest(nvl(B.running_total_dr,0), nvl(B.running_total_cr,0)) =
decode(B.control_total, null,
greatest(nvl(B.running_total_dr,0),
nvl(B.running_total_cr,0)),
B.control_total)
AND NOT EXISTS (
SELECT 1
FROM GL_JE_HEADERS H2
WHERE H2.je_batch_id = B.je_batch_id
AND H2.display_alc_journal_flag IS NULL
AND (H2.ledger_id != 1
))
AND B.approval_status_code IN ('A', 'Z')
AND B.je_batch_id IN (
SELECT H.je_batch_id
FROM GL_JE_HEADERS H
WHERE H.je_batch_id = B.je_batch_id
AND H.display_alc_journal_flag IS NULL
AND H.ledger_id = 1
AND H.je_source = 'Treasury'
)
AND NOT EXISTS (
SELECT 1
FROM GL_JE_HEADERS H4,
GL_PERIOD_STATUSES PS
WHERE H4.je_batch_id = B.je_batch_id
AND H4.display_alc_journal_flag IS NULL
AND B.actual_flag = 'A'
AND PS.application_id = 101
AND PS.ledger_id = H4.ledger_id
AND PS.period_name = H4.period_name
AND PS.closing_status != 'O')
AND NOT EXISTS (
SELECT 1
FROM GL_JE_HEADERS H5,
GL_LEDGERS L5
WHERE H5.je_batch_id = B.je_batch_id
AND H5.display_alc_journal_flag IS NULL
AND H5.je_source = 'Manual'
AND H5.actual_flag = 'A'
AND H5.currency_code != 'STAT'
AND H5.tax_status_code = 'R'
AND L5.ledger_id = H5.ledger_id
AND L5.enable_automatic_tax_flag = 'Y')
ORDER BY B.name, B.default_period_name
FOR UPDATE OF B.status, B.posting_run_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.02 0 0 0 0
Execute 1 14.77 147.64 162442 163531 21 0
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 14.82 147.66 162442 163531 21 0

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms