[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
3. Inserting back the rows from the temporarary table to
the original table.
4. Commiting the transactions
5. Dropping the temporary tables
By this way, if the data to be deleted is very high, the program's performance
will be better when compared with actually deleting the rows.
If rows to be deleted are very less then script invtxnpg.sql will be better
in terms of performance where we wmploy direct deltion from the tables.
*/
/*
The parameters are
1. x_errbuf -- Error buffer to concurrent program
2. x_retcode -- Indicates the return status of the concurrent program
3. p_organization_id -- Organization for which the purge has to be carried out,
If this is null then the records for all the organizations
will be purged.
4. p_cut_off_date -- The records whose transaction_date below this date
will be deleted. This is mandatory parameter.
This will be also used to check for accounting period.
If the period is open then purge won't be carried out.
*/
/* Configurable Variables are
1. max_rows_to_del - This variable determines which approach has to be selected
for deleting the rows, either the direct approach or the
temp table approach. If the rows to be deleted are less than
this value then the records will be deleted directly else
it will be deleted via temp table approach.
Currently this is set to 100000. But it can be changed
based on the requirements
2. l_bulk_limit - This is the bulk collect limit for deletion.
This can be configured based on the database stats.
This is currently set to 5000.
*/
PROCEDURE Txn_Purge( x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY NUMBER
,p_organization_id IN NUMBER := NULL
,p_cut_off_date IN VARCHAR2
)
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
IS SELECT 'OPEN' sdate
FROM org_acct_periods
WHERE INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(x_period_start_date,x_organization_id)
>= ( SELECT MIN(period_start_date)
FROM org_acct_periods
WHERE organization_id = x_organization_id
AND open_flag = 'Y'
)
AND open_flag = 'Y';
SELECT ROWID
FROM MTL_MATERIAL_TXN_ALLOCATIONS
WHERE transaction_date < l_cut_off_date
AND (p_organization_id IS NULL OR organization_id = p_organization_id);
SELECT ROWID
FROM MTL_MATERIAL_TRANSACTIONS
WHERE transaction_date < l_cut_off_date
AND (p_organization_id IS NULL OR organization_id = p_organization_id);
SELECT ROWID
FROM MTL_TRANSACTION_LOT_NUMBERS
WHERE transaction_date < l_cut_off_date
AND (p_organization_id IS NULL OR organization_id = p_organization_id);
SELECT ROWID
FROM MTL_UNIT_TRANSACTIONS
WHERE transaction_date < l_cut_off_date
AND (p_organization_id IS NULL OR organization_id = p_organization_id);
SELECT ROWID
FROM MTL_TRANSACTION_ACCOUNTS
WHERE transaction_date < l_cut_off_date
AND (p_organization_id IS NULL OR organization_id = p_organization_id);
FOR i in (SELECT ood.organization_id FROM org_organization_definitions ood )
LOOP -- Organization loop
OPEN get_open_period (l_cut_off_date, i.organization_id);
SELECT count(transaction_id)
INTO rows_to_del
FROM mtl_material_txn_allocations
WHERE transaction_date < l_cut_off_date
AND (p_organization_id IS NULL OR organization_id = p_organization_id);
inv_trx_util_pub.TRACE(' exiting out of the loop since there are no more records to delete ' );
DELETE FROM mtl_material_txn_allocations
WHERE ROWID = rowid_list(i);
inv_trx_util_pub.TRACE(' Deleted ' || rows_to_del || ' row(s) from MTL_MATERIAL_TXN_ALLOCATIONS ' );
|| ' SELECT * FROM MTL_MATERIAL_TXN_ALLOCATIONS '
|| ' WHERE 1 = 1 ' ;
s_sql_stmt := 'INSERT INTO MTL_MATERIAL_TXN_ALLOCATIONS SELECT * FROM mtl_material_txn_alloc_bu';
inv_trx_util_pub.TRACE(' Inserted ' || rows_processed || ' row(s) into the table MTL_MATERIAL_TXN_ALLOCATIONS');
SELECT count(transaction_id)
INTO rows_to_del
FROM mtl_material_transactions
WHERE transaction_date < l_cut_off_date
AND (p_organization_id IS NULL OR organization_id = p_organization_id);
inv_trx_util_pub.TRACE(' exiting out of the loop since there are no more records to delete ' );
DELETE FROM mtl_material_transactions
WHERE ROWID = rowid_list(i);
inv_trx_util_pub.TRACE(' Deleted ' || rows_to_del || ' row(s) from MTL_MATERIAL_TRANSACTIONS ' );
|| ' SELECT * FROM MTL_MATERIAL_TRANSACTIONS '
|| ' WHERE 1 = 1 ' ;
s_sql_stmt := 'INSERT INTO MTL_MATERIAL_TRANSACTIONS SELECT * FROM mtl_material_transactions_bu';
inv_trx_util_pub.TRACE(' Inserted ' || rows_processed || ' row(s) into the table MTL_MATERIAL_TRANSACTIONS');
SELECT count(transaction_id)
INTO rows_to_del
FROM mtl_transaction_lot_numbers
WHERE transaction_date < l_cut_off_date
AND (p_organization_id IS NULL OR organization_id = p_organization_id);
inv_trx_util_pub.TRACE(' exiting out of the loop since there are no more records to delete ' );
DELETE FROM mtl_transaction_lot_numbers
WHERE ROWID = rowid_list(i);
inv_trx_util_pub.TRACE(' Deleted ' || rows_to_del || ' row(s) from MTL_TRANSACTION_LOT_NUMBERS ' );
|| ' SELECT * FROM MTL_TRANSACTION_LOT_NUMBERS '
|| ' WHERE 1 = 1 ' ;
s_sql_stmt := 'INSERT INTO MTL_TRANSACTION_LOT_NUMBERS SELECT * FROM mtl_transaction_lot_numbers_bu';
inv_trx_util_pub.TRACE(' Inserted ' || rows_processed || ' row(s) into the table MTL_TRANSACTION_LOT_NUMBERS');
SELECT count(transaction_id)
INTO rows_to_del
FROM mtl_unit_transactions
WHERE transaction_date < l_cut_off_date
AND (p_organization_id IS NULL OR organization_id = p_organization_id);
inv_trx_util_pub.TRACE(' exiting out of the loop since there are no more records to delete ' );
DELETE FROM mtl_unit_transactions
WHERE ROWID = rowid_list(i);
inv_trx_util_pub.TRACE(' Deleted ' || rows_to_del || ' row(s) from MTL_UNIT_TRANSACTIONS ' );
|| ' SELECT * FROM MTL_UNIT_TRANSACTIONS '
|| ' WHERE 1 = 1 ' ;
s_sql_stmt := 'INSERT INTO MTL_UNIT_TRANSACTIONS SELECT * FROM mtl_unit_transactions_bu';
inv_trx_util_pub.TRACE(' Inserted ' || rows_processed || ' row(s) into the table MTL_UNIT_TRANSACTIONS');
/*SELECT count(transaction_id)
INTO rows_to_del
FROM mtl_transaction_accounts
WHERE transaction_date < l_cut_off_date
AND (p_organization_id IS NULL OR organization_id = p_organization_id);*/
SELECT COUNT(transaction_id)
INTO rows_to_del
FROM mtl_transaction_accounts
WHERE transaction_date < l_cut_off_date ;
SELECT COUNT(transaction_id)
INTO rows_to_del
FROM mtl_transaction_accounts
WHERE transaction_date < l_cut_off_date
AND organization_id = p_organization_id ;
inv_trx_util_pub.TRACE(' exiting out of the loop since there are no more records to delete ' );
DELETE FROM mtl_transaction_accounts
WHERE ROWID = rowid_list(i);
inv_trx_util_pub.TRACE(' Deleted ' || rows_to_del || ' row(s) from MTL_TRANSACTION_ACCOUNTS ' );
|| ' SELECT * FROM MTL_TRANSACTION_ACCOUNTS '
|| ' WHERE 1 = 1 ' ;
s_sql_stmt := 'INSERT INTO MTL_TRANSACTION_ACCOUNTS SELECT * FROM mtl_transaction_accounts_bu';
inv_trx_util_pub.TRACE(' Inserted ' || rows_processed || ' row(s) into the table MTL_TRANSACTION_ACCOUNTS');