固定资产部分报废，借贷不平(已解决)

wahaa2011

SR提供的解决方法：
操作步骤：
1.后台执行SQL
DECLARE

l_asset_id number;
l_trx_id number;
l_book varchar2(15);
l_pc number;
l_trx_type varchar2(20);
l_dr_total number;
l_cr_total number;
l_count number;

CURSOR ret_assets IS
SELECT
th.transaction_header_id,
th.asset_id,th.transaction_type_code
FROM
fa_deprn_periods dp,
fa_transaction_headers th
WHERE
th.book_type_code = l_book and
dp.book_type_code = th.book_type_code and
dp.period_counter = l_pc and
th.date_effective between dp.period_open_date and
nvl(dp.period_close_date, sysdate);
BEGIN

l_book := '&BOOK';
l_pc := &PERIOD_COUNTER;
dbms_output.put_line('Assets where debits and credits do not match: ');
dbms_output.put_line('Asset_id and transaction_header_id');
dbms_output.put_line('**********************************');

open ret_assets;
l_count := 0;

loop
fetch ret_assets into l_trx_id, l_asset_id, l_trx_type;
if (ret_assets%NOTFOUND) then
exit;
end if;

select nvl(sum(adjustment_amount),0)
into l_dr_total
from fa_adjustments
where
book_type_code = l_book and
period_counter_created = l_pc and
transaction_header_id = l_trx_id and
asset_id = l_asset_id and
adjustment_type <> 'EXPENSE' and
debit_credit_flag = 'DR';

select nvl(sum(adjustment_amount),0)
into l_cr_total
from fa_adjustments
where
book_type_code = l_book and
period_counter_created = l_pc and
transaction_header_id = l_trx_id and
asset_id = l_asset_id and
adjustment_type <> 'EXPENSE' and
debit_credit_flag = 'CR';

if (l_dr_total <> l_cr_total) then
l_count := l_count + 1;
dbms_output.put_line(l_asset_id || ' and ' || l_trx_id);
end if;
end loop;
dbms_output.put_line('*****************************************');
dbms_output.put_line('Total transactions out of balance: ' || l_count);

END;
上述SQL用于查询哪个固定资产是哪个事务处理ID导致了借贷不平

执行后输出：
Assets where debits and credits do not match:
Asset_id and transaction_header_id
**********************************
12656 and 455749
*****************************************
Total transactions out of balance: 1


2.以1输出的数据为条件(对应asset_id/transaction_header_id字段)update apps.FA_ADJUSTMENTS后台数据(并让成本处指定后台调整哪一笔金额ADJUSTMENT_AMOUNT),如下：
Update apps.FA_ADJUSTMENTS
set ADJUSTMENT_AMOUNT = 25250.09
where asset_id = 12656
and book_type_code = 'XX资产帐簿'
and TRANSACTION_HEADER_ID = 465749
and ADJUSTMENT_AMOUNT = 25250.25

3.重新生成日记账