oracle资产类别删除,彻底删除固定资产 - Oracle ERP - ITPUB论坛－中国专业的IT技术社区...

PROCEDURE delete_fa_data IS

l_utl_file_dir VARCHAR2(1000);

l_in_file      utl_file.file_type;

l_file_name    VARCHAR2(100);

CURSOR c_xla_fa_gl IS

SELECT DISTINCT gjh.je_header_id

FROM fa_adjustments fa, gl_je_headers gjh

WHERE fa.je_header_id = gjh.je_header_id

AND gjh.set_of_books_id = 1;

l_del_header_count NUMBER;

l_del_line_count   NUMBER;

l_result           VARCHAR2(15);

l_error_message    VARCHAR2(1000);

BEGIN

SELECT substr(VALUE, 1, instr(VALUE, ',', 1) - 1)

INTO l_utl_file_dir

FROM v$parameter

WHERE NAME = 'utl_file_dir';

SELECT 'FApurge' || to_char(SYSDATE, 'YYMMDDHHMI') || '.log'

INTO l_file_name

FROM dual;

l_in_file := utl_file.fopen(l_utl_file_dir, l_file_name, 'W');

utl_file.put_line(l_in_file, 'Start delete Journal from FA');

dbms_output.put_line(l_utl_file_dir ||'/'|| l_file_name);

--Journal from FA

FOR i IN c_xla_fa_gl LOOP

xxsrs_gl_purge.sp_gl_purge_je_sob1(i.je_header_id,

'FA',

l_del_header_count,

l_del_line_count,

l_result,

l_error_message);

IF l_result = 'E' THEN

utl_file.put_line(l_in_file,

'     ' || i.je_header_id || l_error_message);

ROLLBACK;

RETURN;

END IF;

END LOOP;

COMMIT;

--Transactions

BEGIN

utl_file.put_line(l_in_file, 'Start  delete Transactions');

DELETE FROM fa_mass_transfers a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_mass_transfers');

DELETE FROM fa_transfer_details a

WHERE NOT EXISTS

(SELECT 1

FROM fa_transaction_headers fth, xxsrs2_fa_purge_book b

WHERE fth.book_type_code = b.book_type_code

AND fth.transaction_header_id = a.transfer_header_id);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_transfer_details');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--Journal source from fixed assets

BEGIN

utl_file.put_line(l_in_file, 'Journal source from fixed assets');

DELETE FROM fa_adjustments a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_adjustments');

DELETE FROM fa_transaction_headers a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_transaction_headers');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--Depreciation Detail

BEGIN

utl_file.put_line(l_in_file, 'Depreciation Detail');

DELETE FROM fa_deprn_detail a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_deprn_detail');

DELETE FROM fa_deprn_periods a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_deprn_periods');

DELETE FROM fa_deprn_summary a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_deprn_summary');

DELETE FROM fa_mass_tax_adjustments a

WHERE NOT EXISTS

(SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.control_book_type_code = b.book_type_code

OR a.adjusted_book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_mass_tax_adjustments');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--5.  Distribution

BEGIN

utl_file.put_line(l_in_file, '5.  Distribution');

DELETE FROM fa_distribution_accounts a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_distribution_accounts');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--6.  Retirements

BEGIN

utl_file.put_line(l_in_file, '6.  Retirements');

DELETE FROM fa_mass_retirements a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_mass_retirements');

DELETE FROM fa_mass_ret_excepts a

WHERE NOT EXISTS

(SELECT 1

FROM fa_books fb, fa_additions fa, xxsrs2_fa_purge_book b

WHERE fa.asset_id = fb.asset_id

AND fa.asset_id = a.asset_id

AND fb.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_mass_ret_excepts');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--7. Revaluation

BEGIN

utl_file.put_line(l_in_file, '7. Revaluation');

DELETE FROM fa_mass_revaluations a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_mass_revaluations');

DELETE FROM fa_mass_revaluation_rules a

WHERE NOT EXISTS

(SELECT 1

FROM fa_books fb, fa_additions fa, xxsrs2_fa_purge_book b

WHERE fa.asset_id = fb.asset_id

AND fa.asset_id = a.asset_id

AND fb.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_mass_revaluation_rules');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--8. Insurance

BEGIN

utl_file.put_line(l_in_file, '8. Insurance');

DELETE FROM fa_ins_lines a

WHERE NOT EXISTS

(SELECT 1

FROM fa_books fb, fa_additions fa, xxsrs2_fa_purge_book b

WHERE fa.asset_id = fb.asset_id

AND fa.asset_id = a.asset_id

AND fb.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_ins_lines');

DELETE FROM fa_ins_mst_pols a

WHERE NOT EXISTS

(SELECT 1

FROM fa_ins_policies fip, xxsrs2_fa_purge_book b

WHERE fip.book_type_code = b.book_type_code

AND fip.asset_policy_id = a.asset_policy_id);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_ins_mst_pols');

DELETE FROM fa_ins_values a

WHERE NOT EXISTS

(SELECT 1

FROM fa_ins_policies fip, xxsrs2_fa_purge_book b

WHERE fip.book_type_code = b.book_type_code

AND fip.asset_policy_id = a.asset_policy_id);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_ins_values');

DELETE FROM fa_ins_policies a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_ins_policies');

DELETE FROM fa_ins_policies a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_ins_policies');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--9.  History

BEGIN

utl_file.put_line(l_in_file, '9.  History');

DELETE FROM fa_asset_history a

WHERE NOT EXISTS

(SELECT 1

FROM fa_books fb, fa_additions fa, xxsrs2_fa_purge_book b

WHERE fa.asset_id = fb.asset_id

AND fa.asset_id = a.asset_id

AND fb.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_asset_history');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--10.  Addition

BEGIN

utl_file.put_line(l_in_file, '10.  Addition');

DELETE FROM fa_additions_tl a

WHERE NOT EXISTS

(SELECT 1

FROM fa_additions_b fab, fa_books fb, xxsrs2_fa_purge_book b

WHERE fab.asset_id = fb.asset_id

AND a.asset_id = fab.asset_id

AND fb.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_additions_tl');

DELETE FROM fa_additions_b a

WHERE NOT EXISTS (SELECT 1

FROM fa_books fb, xxsrs2_fa_purge_book b

WHERE a.asset_id = fb.asset_id

AND fb.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_additions_b');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--11.  Mass addition

BEGIN

utl_file.put_line(l_in_file, '11.  Mass addition');

DELETE FROM fa_massadd_distributions a

WHERE NOT EXISTS

(SELECT 1

FROM fa_mass_additions fma, xxsrs2_fa_purge_book b

WHERE a.mass_addition_id = fma.mass_addition_id

AND fma.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_massadd_distributions');

DELETE FROM fa_deleted_mass_additions a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_deleted_mass_additions');

DELETE FROM fa_mass_changes a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_mass_changes');

DELETE FROM fa_mass_additions a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_mass_additions');

DELETE FROM fa_mass_reclass a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_mass_reclass');

DELETE FROM fa_mass_reclass_itf a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_mass_reclass_itf');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--12. Archive

BEGIN

utl_file.put_line(l_in_file, '12. Archive');

DELETE FROM fa_archive_history a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_archive_history');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--14.  Category and books combination

BEGIN

utl_file.put_line(l_in_file, '14.  Category and books combination');

DELETE FROM fa_category_books a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_category_books');

DELETE FROM fa_category_book_defaults a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_category_book_defaults');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

--17.  Books

BEGIN

utl_file.put_line(l_in_file, '17.  Books');

DELETE FROM fa_books a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_books');

DELETE FROM fa_book_controls a

WHERE NOT EXISTS (SELECT 1

FROM xxsrs2_fa_purge_book b

WHERE a.book_type_code = b.book_type_code);

utl_file.put_line(l_in_file,

'     ' || SQL%ROWCOUNT ||

' rows delete from fa_book_controls');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

utl_file.put_line(l_in_file, '     ' || SQLCODE || SQLERRM);

RETURN;

END;

utl_file.fclose(l_in_file);

END delete_fa_data;

给做参考