The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MET.rowid row_id, MET.*
FROM fa_mass_external_transfers MET
WHERE MET.batch_name = 'FA_MODIFY_DISTS'
AND MET.transaction_status = 'POST'
AND MET.transaction_type in ('UNIT ADJUSTMENT', 'TRANSFER')
ORDER BY MET.BOOK_TYPE_CODE,
MET.FROM_ASSET_ID,
MET.TRANSACTION_REFERENCE_NUM,
MET.TRANSACTION_TYPE;
asgn_table.delete;
UPDATE fa_mass_external_transfers MET
SET MET.transaction_status = l_transaction_status
WHERE MET.rowid = asgn_table(i).row_id
AND MET.transaction_status = 'POST';
asgn_table.delete;
if (METInfo.last_update_login is NULL) then
METInfo.last_update_login := METInfo.last_updated_by;
insert_dist_table( row_id => METInfo.row_id,
asset_id => METInfo.from_asset_id,
transfer_units => METInfo.transfer_units,
transaction_date_entered
=>
METInfo.transaction_date_entered,
from_dist_id => METInfo.from_distribution_id,
from_location_id => METInfo.from_location_id,
from_assigned_to => METInfo.from_employee_id,
from_ccid => METInfo.from_gl_ccid,
to_dist_id => METInfo.to_distribution_id,
to_location_id => METInfo.to_location_id,
to_assigned_to => METInfo.to_employee_id,
to_ccid => METInfo.to_gl_ccid,
attribute1 => METInfo.attribute1,
attribute2 => METInfo.attribute2,
attribute3 => METInfo.attribute3,
attribute4 => METInfo.attribute4,
attribute5 => METInfo.attribute5,
attribute6 => METInfo.attribute6,
attribute7 => METInfo.attribute7,
attribute8 => METInfo.attribute8,
attribute9 => METInfo.attribute9,
attribute10 => METInfo.attribute10,
attribute11 => METInfo.attribute11,
attribute12 => METInfo.attribute12,
attribute13 => METInfo.attribute13,
attribute14 => METInfo.attribute14,
attribute15 => METInfo.attribute15,
attribute_category_code
=> METInfo.attribute_category_code,
post_batch_id => METInfo.post_batch_id,
last_updated_by => METInfo.last_updated_by,
last_update_date => METInfo.last_update_date,
last_update_login => METInfo.last_update_login,
p_Log_level_rec => g_log_level_rec);
PROCEDURE insert_dist_table(
row_id IN ROWID,
asset_id IN NUMBER,
transfer_units IN NUMBER,
transaction_date_entered
IN DATE,
from_dist_id IN NUMBER,
from_location_id IN NUMBER,
from_assigned_to IN NUMBER,
from_ccid IN NUMBER,
to_dist_id IN NUMBER,
to_location_id IN NUMBER,
to_assigned_to IN NUMBER,
to_ccid IN NUMBER,
attribute1 IN VARCHAR2,
attribute2 IN VARCHAR2,
attribute3 IN VARCHAR2,
attribute4 IN VARCHAR2,
attribute5 IN VARCHAR2,
attribute6 IN VARCHAR2,
attribute7 IN VARCHAR2,
attribute8 IN VARCHAR2,
attribute9 IN VARCHAR2,
attribute10 IN VARCHAR2,
attribute11 IN VARCHAR2,
attribute12 IN VARCHAR2,
attribute13 IN VARCHAR2,
attribute14 IN VARCHAR2,
attribute15 IN VARCHAR2,
attribute_category_code IN VARCHAR2,
post_batch_id IN NUMBER,
last_updated_by IN NUMBER,
last_update_date IN DATE,
last_update_login IN NUMBER
, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
X_row_id ROWID := NULL;
SELECT DH.rowid row_id, DH.*
FROM fa_distribution_history DH
WHERE DH.asset_id = X_asset_id
AND DH.distribution_id = NVL(X_dist_id, DH.distribution_id)
AND DH.location_id = NVL(X_location_id, DH.location_id)
AND DH.code_combination_id = NVL(X_ccid, DH.code_combination_id)
AND NVL(DH.assigned_to, -1) = NVL(X_assigned_to, -1)
AND DH.date_ineffective IS NULL;
UPDATE fa_mass_external_transfers MET
SET MET.transaction_status = 'IGNORED'
WHERE MET.rowid = row_id;
p_record_status => 'UPDATE', p_log_level_rec => g_log_level_rec);
p_record_status => 'UPDATE',
p_attribute1 => attribute1,
p_attribute2 => attribute2,
p_attribute3 => attribute3,
p_attribute4 => attribute4,
p_attribute5 => attribute5,
p_attribute6 => attribute6,
p_attribute7 => attribute7,
p_attribute8 => attribute8,
p_attribute9 => attribute9,
p_attribute10 => attribute10,
p_attribute11 => attribute11,
p_attribute12 => attribute12,
p_attribute13 => attribute13,
p_attribute14 => attribute14,
p_attribute15 => attribute15,
p_attribute_category_code => attribute_category_code,
p_last_updated_by => last_updated_by,
p_last_update_date => last_update_date,
p_last_update_login => last_update_login,
p_Log_level_rec => g_log_level_rec);
p_record_status => 'INSERT', p_log_level_rec => g_log_level_rec);
p_record_status => 'INSERT',
p_attribute1 => attribute1,
p_attribute2 => attribute2,
p_attribute3 => attribute3,
p_attribute4 => attribute4,
p_attribute5 => attribute5,
p_attribute6 => attribute6,
p_attribute7 => attribute7,
p_attribute8 => attribute8,
p_attribute9 => attribute9,
p_attribute10 => attribute10,
p_attribute11 => attribute11,
p_attribute12 => attribute12,
p_attribute13 => attribute13,
p_attribute14 => attribute14,
p_attribute15 => attribute15,
p_attribute_category_code => attribute_category_code,
p_last_updated_by => last_updated_by,
p_last_update_date => last_update_date,
p_last_update_login => last_update_login,
p_Log_level_rec => g_log_level_rec);
p_record_status => 'UPDATE', p_log_level_rec => g_log_level_rec);
p_record_status => 'UPDATE',
p_attribute1 => attribute1,
p_attribute2 => attribute2,
p_attribute3 => attribute3,
p_attribute4 => attribute4,
p_attribute5 => attribute5,
p_attribute6 => attribute6,
p_attribute7 => attribute7,
p_attribute8 => attribute8,
p_attribute9 => attribute9,
p_attribute10 => attribute10,
p_attribute11 => attribute11,
p_attribute12 => attribute12,
p_attribute13 => attribute13,
p_attribute14 => attribute14,
p_attribute15 => attribute15,
p_attribute_category_code => attribute_category_code,
p_last_updated_by => last_updated_by,
p_last_update_date => last_update_date,
p_last_update_login => last_update_login,
p_Log_level_rec => g_log_level_rec);
UPDATE fa_mass_external_transfers MET
SET MET.transaction_status = 'ERRORED'
WHERE MET.rowid = row_id;
UPDATE fa_mass_external_transfers MET
SET MET.transaction_status = 'ERRORED'
WHERE MET.rowid = row_id;
END insert_dist_table;
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_last_update_login IN NUMBER
, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
BEGIN
if (g_asgn_count=0) then /* initialize the table */
asgn_table.delete;
asgn_table(g_asgn_count).last_updated_by := p_last_updated_by;
asgn_table(g_asgn_count).last_update_date := p_last_update_date;
asgn_table(g_asgn_count).last_update_login := p_last_update_login;
SELECT AD.*
FROM fa_additions_b AD
WHERE AD.asset_id = X_asset_id;
SELECT LEA.rowid row_id, LEA.*
FROM fa_leases LEA
WHERE LEA.lease_id = X_lease_id;
p_last_update_date => asgn_table(1).last_update_date,
p_created_by => asgn_table(1).last_updated_by,
p_creation_date => asgn_table(1).last_update_date,
p_last_updated_by => asgn_table(1).last_updated_by,
p_last_update_login => asgn_table(1).last_update_login,
p_attribute1 => asgn_table(1).attribute1,
p_attribute2 => asgn_table(1).attribute2,
p_attribute3 => asgn_table(1).attribute3,
p_attribute4 => asgn_table(1).attribute4,
p_attribute5 => asgn_table(1).attribute5,
p_attribute6 => asgn_table(1).attribute6,
p_attribute7 => asgn_table(1).attribute7,
p_attribute8 => asgn_table(1).attribute8,
p_attribute9 => asgn_table(1).attribute9,
p_attribute10 => asgn_table(1).attribute10,
p_attribute11 => asgn_table(1).attribute11,
p_attribute12 => asgn_table(1).attribute12,
p_attribute13 => asgn_table(1).attribute13,
p_attribute14 => asgn_table(1).attribute14,
p_attribute15 => asgn_table(1).attribute15,
p_attribute_category_code
=>
asgn_table(1).attribute_category_code,
-- Asset Header Info --
p_asset_id => ADDInfo.Asset_Id,
p_book_type_code => book_type_code
);
SELECT AD.*
FROM fa_additions_b AD
WHERE AD.asset_id = X_asset_id;
select count(1)
into l_count
from fa_deprn_summary ds
where ds.book_type_code = l_book
and ds.asset_id = l_asset
and ds.deprn_source_code = 'BOOKS'
and ds.period_counter = l_current_pc - 1;
p_last_update_date => asgn_table(1).last_update_date,
p_last_updated_by => asgn_table(1).last_updated_by,
p_created_by => asgn_table(1).last_updated_by,
p_creation_date => asgn_table(1).last_update_date,
p_last_update_login => asgn_table(1).last_update_login,
p_attribute1 => asgn_table(1).attribute1,
p_attribute2 => asgn_table(1).attribute2,
p_attribute3 => asgn_table(1).attribute3,
p_attribute4 => asgn_table(1).attribute4,
p_attribute5 => asgn_table(1).attribute5,
p_attribute6 => asgn_table(1).attribute6,
p_attribute7 => asgn_table(1).attribute7,
p_attribute8 => asgn_table(1).attribute8,
p_attribute9 => asgn_table(1).attribute9,
p_attribute10 => asgn_table(1).attribute10,
p_attribute11 => asgn_table(1).attribute11,
p_attribute12 => asgn_table(1).attribute12,
p_attribute13 => asgn_table(1).attribute13,
p_attribute14 => asgn_table(1).attribute14,
p_attribute15 => asgn_table(1).attribute15,
p_attribute_category_code
=> asgn_table(1).attribute_category_code,
-- Asset Header Info --
p_asset_id => asset_id,
p_book_type_code => book_type_code
);
select fa_transaction_headers_s.nextval
into X_Transaction_Header_Id
from sys.dual;
select greatest(calendar_period_open_date,
least(sysdate, calendar_period_close_date)),
calendar_period_open_date,
calendar_period_close_date,
period_counter
into X_Transaction_Date_Entered,
X_Calendar_Period_Open_Date,
X_Calendar_Period_Close_Date,
X_Current_PC
from fa_deprn_periods
where book_type_code = X_Book_Type_Code
and period_close_date is null;
select fiscal_year_name, current_fiscal_year
into lv_fiscal_year_name, lv_fiscal_year
from fa_book_controls
where book_type_code = X_Book_Type_Code;
select start_date, end_date
into X_FY_Start_Date, X_FY_End_Date
from fa_fiscal_year
where fiscal_year = lv_fiscal_year
and fiscal_year_name = lv_fiscal_year_name;
select max(transaction_date_entered)
into X_Max_Transaction_Date
from fa_transaction_headers
where asset_id = X_Asset_Id
and book_type_code = X_Book_Type_Code;
SELECT count(*)
FROM fa_book_controls BC
WHERE BC.book_type_code = X_corp_book_type_code
AND BC.book_class = 'CORPORATE'
AND rownum <= 1;
SELECT count(*)
FROM fa_locations loc
WHERE loc.location_id = NVL(X_location_id, -1)
AND loc.enabled_flag = 'Y'
AND rownum <= 1;
SELECT count(*)
FROM gl_code_combinations glcc
WHERE glcc.code_combination_id = NVL(X_ccid_id, -1)
AND glcc.enabled_flag = 'Y'
AND nvl(glcc.end_date_active, sysdate) >= sysdate
AND rownum <= 1;
SELECT count(*)
FROM gl_code_combinations glcc
WHERE glcc.code_combination_id = NVL(X_ccid_id, -1)
AND glcc.enabled_flag = 'Y'
AND nvl(glcc.end_date_active, sysdate) >= sysdate
AND rownum <= 1;