DevOps, CI/CD and Automation

Dear Gurus,

i have report to generate XML it works great in 12.1.1 UAT, but when i migrate RDF to Production it gives following issue.

unable to understand any issue out of this.

please suggest.



Enter Password:
REP-1401: '': Fatal PL/SQL error occurred.
REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:
REP-1401: '': Fatal PL/SQL error occurred.




both reports version are same Report Builder: Release 10.1.2.3.0


thanks in anticipation

Abdul Rahman


below is procedure in pll, which i have made as a DB procedure now.

CREATE OR REPLACE procedure FA_RSVLDG
(book in varchar2,
period in varchar2,
errbuf out varchar2,
retcode out number)
is
operation varchar2(200);
dist_book varchar2(15);
ucd date;
upc number;
tod date;
tpc number;
schema_name varchar2(30);
schema_success boolean := TRUE;
cid integer;

begin
operation := 'Deleting from FA_RESERVE_LEDGER';
begin

select distinct ou.oracle_username
into schema_name
from fnd_oracle_userid ou,
fnd_product_installations pi
where ou.oracle_id = pi.oracle_id
and pi.application_id = 140
and rownum+0 <= 1;

exception
when others then

DELETE FROM FA_RESERVE_LEDGER;

if (SQL%ROWCOUNT > 0) then
operation := 'Committing Delete';
COMMIT;
else
operation := 'Rolling Back Delete';
ROLLBACK;
end if;

schema_success := FALSE;
end;

IF schema_success THEN
cid := DBMS_SQL.open_cursor;
DBMS_SQL.parse(cid, 'TRUNCATE TABLE ' || schema_name || '.FA_RESERVE_LEDGER',1);
DBMS_SQL.CLOSE_CURSOR(cid);
END IF;

operation := 'Selecting Book and Period information';
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
INTO
dist_book,
ucd,
upc,
tod,
tpc
FROM
FA_DEPRN_PERIODS DP,
FA_DEPRN_PERIODS DP_FY,
FA_BOOK_CONTROLS BC
WHERE
DP.BOOK_TYPE_CODE = book AND
DP.PERIOD_NAME = period AND
DP_FY.BOOK_TYPE_CODE = book AND
DP_FY.FISCAL_YEAR = DP.FISCAL_YEAR
AND BC.BOOK_TYPE_CODE = book
GROUP BY
BC.DISTRIBUTION_SOURCE_BOOK,
DP.PERIOD_CLOSE_DATE,
DP.PERIOD_COUNTER;

operation := 'Inserting into FA_RESERVE_LEDGER';

INSERT INTO FA_RESERVE_LEDGER
(ASSET_ID,
DH_CCID,
DEPRN_RESERVE_ACCT,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
LIFE,
RATE,
CAPACITY,
COST,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
PERCENT,
TRANSACTION_TYPE,
PERIOD_COUNTER,
DATE_EFFECTIVE)
SELECT
DH.ASSET_ID ASSET_ID,
DH.CODE_COMBINATION_ID DH_CCID,
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.COST COST,
decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
YTD_DEPRN,
DD.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.PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd)
FROM
FA_DEPRN_DETAIL DD,
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 + 0 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.BOOK_TYPE_CODE = book AND
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID + 0 AND
DD.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 + 0
AND
BOOKS.BOOK_TYPE_CODE = book AND
BOOKS.ASSET_ID = DH.ASSET_ID + 0 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 (+) = dist_book AND
TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT + 0 AND
TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
AND
DH.BOOK_TYPE_CODE = dist_book AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, sysdate) > tod;


end FA_RSVLDG;