Query to get Journal Entry Reserve Ledger Report

Introduction: 

This blog contains an SQL query that can be used to run the Journal Entry Reserve Ledger Report.

Cause of the issue: 

The business requires a report that contains Journal Entry Reserve Ledger Report with some additional columns. But in the standard report, there is a global temporary table which the data has been deleted after every run of the report.

How do we solve: 

Below query has used to populate the same data which the standard report gives:

First run this below script and Pass the parameter Period and Book Type.

SELECT bc.distribution_source_book dbk,
NVL (dp.period_close_date, SYSDATE) ucd,
dp.period_counter upc,
MIN (dp_fy.period_open_date) tod,
MIN (dp_fy.period_counter) tpc,
dp.period_num
FROM fa_deprn_periods dp, fa_deprn_periods dp_fy, fa_book_controls bc
WHERE dp.book_type_code = :book_type_code_
AND dp.period_name = :period_name_
AND dp_fy.book_type_code =  :book_type_code_
AND dp_fy.fiscal_year = dp.fiscal_year
AND bc.book_type_code =  :book_type_code_
GROUP BY bc.distribution_source_book, dp.period_close_date, dp.period_counter, dp.period_num;

 

Once the above script is executed you will see the column pass the value in the below query

SELECT dh.asset_id asset_id,

dh.code_combination_id dh_ccid,

—  APPS_GL_CODE_COMB_UTIL_API.Get_Segment2(dh.code_combination_id) segment2,

cb.deprn_reserve_acct rsv_account,

books.date_placed_in_service start_date,

books.deprn_method_code method,

books.life_in_months life,

books.adjusted_rate rate,

books.production_capacity capacity,

dd_bonus.cost cost,

DECODE (dd_bonus.period_counter, :upc_, dd_bonus.deprn_amount – dd_bonus.bonus_deprn_amount, 0) deprn_amount,

DECODE (SIGN (:tpc_ – dd_bonus.period_counter), 1, 0, dd_bonus.ytd_deprn – dd_bonus.bonus_ytd_deprn) ytd_deprn,

dd_bonus.deprn_reserve – dd_bonus.bonus_deprn_reserve deprn_reserve,

DECODE (th.transaction_type_code, NULL, dh.units_assigned / ah.units * 100) percent,

DECODE (th.transaction_type_code,

NULL, DECODE (th_rt.transaction_type_code, ‘FULL RETIREMENT’, ‘F’, DECODE (books.depreciate_flag, ‘NO’, ‘N’)),

‘TRANSFER’, ‘T’,

‘TRANSFER OUT’, ‘P’,

‘RECLASS’, ‘R’)

t_type,

dd_bonus.period_counter,

NVL (th.date_effective, :ucd_),

”

FROM fa_deprn_detail dd_bonus,

fa_asset_history ah,

fa_transaction_headers th,

fa_transaction_headers th_rt,

fa_books books,

fa_distribution_history dh,

fa_category_books cb

WHERE cb.book_type_code = :book_

AND cb.category_id = ah.category_id

AND ah.asset_id = dh.asset_id

AND ah.date_effective < NVL (th.date_effective, :ucd_)

AND NVL (ah.date_ineffective, SYSDATE) >= NVL (th.date_effective, :ucd_)

AND —  AH.ASSET_TYPE                   = ‘CAPITALIZED’

–AND

dd_bonus.book_type_code = :book_

AND dd_bonus.distribution_id = dh.distribution_id

AND dd_bonus.period_counter =

(SELECT MAX (dd_sub.period_counter)

FROM fa_deprn_detail dd_sub

WHERE dd_sub.book_type_code = :book_

AND dd_sub.asset_id = dh.asset_id

AND dd_sub.distribution_id = dh.distribution_id

AND dd_sub.period_counter <= :upc_)

AND th_rt.book_type_code = :book_

AND th_rt.transaction_header_id = books.transaction_header_id_in

AND books.book_type_code = :book_

AND books.asset_id = dh.asset_id

AND NVL (books.period_counter_fully_retired, :upc_) >= :tpc_

AND books.date_effective <= NVL (th.date_effective, :ucd_)

AND NVL (books.date_ineffective, SYSDATE + 1) > NVL (th.date_effective, :ucd_)

AND th.book_type_code(+) = :book_

AND th.transaction_header_id(+) = dh.transaction_header_id_out

AND th.date_effective(+) BETWEEN :tod_ AND :ucd_

AND dh.book_type_code = :book_

 

 

How to see the data in the global temporary table (FA_RESERVE_LEDGER_GT) after the report last run:

 

We can see the data of the previous run of the report in the global temporary table using the execution of the below script

DECLARE

BOOK        VARCHAR2(200);

PERIOD      VARCHAR2(200);

ERRBUF      VARCHAR2(200);

RETCODE     NUMBER;

OPERATION   VARCHAR2(200);

REQUEST_ID  NUMBER;

BEGIN

OPERATION  := ‘TEST’;

REQUEST_ID := <Pass the request ID of the last run of the program>;

 

FA_RSVLDG_REP_INS_PKG.RSVLDG(

BOOK       => <Pass the Book type code>,

PERIOD     => <Pass the period>,

ERRBUF     => ERRBUF,

RETCODE    => RETCODE,

OPERATION  => OPERATION,

REQUEST_ID => REQUEST_ID

);

 

DBMS_OUTPUT.PUT_LINE(‘ERRBUF  = ‘ || ERRBUF);

DBMS_OUTPUT.PUT_LINE(‘RETCODE = ‘ || RETCODE);

END;

/

 

 

Now we can able to see the data in the Global Temporary table (FA_RESERVE_LEDGER_GT) for the previous run details.

Recent Posts