[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cost
INTO h_cost
FROM FA_BOOKS
WHERE asset_id = h_asset_id
AND book_type_code = h_book
AND date_ineffective is null;
X_last_update_date date default sysdate,
X_last_updated_by number default -1,
X_last_update_login number default -1,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
h_max_cache_rows number := FA_ADJUST_TYPE_PKG.MAX_ADJ_CACHE_ROWS;
h_last_updated_by number;
h_last_update_login number;
h_last_updated_by := X_last_updated_by;
h_last_update_login := X_last_update_login;
h_mesg_name := 'FA_INS_ADJ_CANT_INSERT';
INSERT INTO FA_ADJUSTMENTS_MRC_V
(transaction_header_id,
asset_invoice_id,
source_type_code,
adjustment_type,
debit_credit_flag,
code_combination_id,
book_type_code,
period_counter_created,
asset_id,
adjustment_amount,
period_counter_adjusted,
distribution_id,
annualized_adjustment,
deprn_override_flag,
last_update_date,
last_updated_by,
last_update_login,
track_member_flag, -- Added for Track Member feature
adjustment_line_id,
source_dest_code,
source_line_id)
VALUES (adj_table(h_i).transaction_header_id,
adj_table(h_i).asset_invoice_id,
adj_table(h_i).source_type_code,
adj_table(h_i).adjustment_type,
adj_table(h_i).debit_credit_flag,
adj_table(h_i).code_combination_id,
adj_table(h_i).book_type_code,
adj_table(h_i).period_counter_created,
adj_table(h_i).asset_id,
adj_table(h_i).adjustment_amount,
adj_table(h_i).period_counter_adjusted,
adj_table(h_i).distribution_id,
adj_table(h_i).annualized_adjustment,
adj_table(h_i).deprn_override_flag,
adj_table(h_i).last_update_date,
h_last_updated_by,
h_last_update_login,
adj_table(h_i).track_member_flag,
fa_adjustments_s.nextval, --adjustment_line_id,
adj_table(h_i).source_dest_code,
adj_table(h_i).source_line_id);
INSERT INTO FA_ADJUSTMENTS
(transaction_header_id,
asset_invoice_id,
source_type_code,
adjustment_type,
debit_credit_flag,
code_combination_id,
book_type_code,
period_counter_created,
asset_id,
adjustment_amount,
period_counter_adjusted,
distribution_id,
annualized_adjustment,
deprn_override_flag,
last_update_date,
last_updated_by,
last_update_login,
track_member_flag,
adjustment_line_id,
source_dest_code,
source_line_id)
VALUES (adj_table(h_i).transaction_header_id,
adj_table(h_i).asset_invoice_id,
adj_table(h_i).source_type_code,
adj_table(h_i).adjustment_type,
adj_table(h_i).debit_credit_flag,
adj_table(h_i).code_combination_id,
adj_table(h_i).book_type_code,
adj_table(h_i).period_counter_created,
adj_table(h_i).asset_id,
adj_table(h_i).adjustment_amount,
adj_table(h_i).period_counter_adjusted,
adj_table(h_i).distribution_id,
adj_table(h_i).annualized_adjustment,
adj_table(h_i).deprn_override_flag,
adj_table(h_i).last_update_date,
h_last_updated_by,
h_last_update_login,
adj_table(h_i).track_member_flag,
fa_adjustments_s.nextval, --adjustment_line_id,
adj_table(h_i).source_dest_code,
adj_table(h_i).source_line_id);
adj_table(h_cache_index).last_update_date :=
adj_ptr.last_update_date;
h_mesg_name := 'FA_INS_ADJ_CANT_INSERT';
element => 'Rows in CACHE-INSERT IN TRUE',
value => h_num_rows
,p_log_level_rec => p_log_level_rec);
INSERT INTO FA_ADJUSTMENTS_MRC_V
(transaction_header_id,
asset_invoice_id,
source_type_code,
adjustment_type,
debit_credit_flag,
code_combination_id,
book_type_code,
period_counter_created,
asset_id,
adjustment_amount,
period_counter_adjusted,
distribution_id,
annualized_adjustment,
deprn_override_flag,
last_update_date,
last_updated_by,
last_update_login,
track_member_flag, -- Added for Track Member feature
adjustment_line_id,
source_dest_code,
source_line_id)
VALUES (adj_table(h_i).transaction_header_id,
adj_table(h_i).asset_invoice_id,
adj_table(h_i).source_type_code,
adj_table(h_i).adjustment_type,
adj_table(h_i).debit_credit_flag,
adj_table(h_i).code_combination_id,
adj_table(h_i).book_type_code,
adj_table(h_i).period_counter_created,
adj_table(h_i).asset_id,
adj_table(h_i).adjustment_amount,
adj_table(h_i).period_counter_adjusted,
adj_table(h_i).distribution_id,
adj_table(h_i).annualized_adjustment,
adj_table(h_i).deprn_override_flag,
adj_table(h_i).last_update_date,
h_last_updated_by,
h_last_update_login,
adj_table(h_i).track_member_flag, -- Added for Track Member
fa_adjustments_s.nextval, -- adjustment_line_id,
adj_table(h_i).source_dest_code,
adj_table(h_i).source_line_id);
INSERT INTO FA_ADJUSTMENTS
(transaction_header_id,
asset_invoice_id,
source_type_code,
adjustment_type,
debit_credit_flag,
code_combination_id,
book_type_code,
period_counter_created,
asset_id,
adjustment_amount,
period_counter_adjusted,
distribution_id,
annualized_adjustment,
deprn_override_flag,
last_update_date,
last_updated_by,
last_update_login,
track_member_flag, -- Added for Track Member
adjustment_line_id,
source_dest_code,
source_line_id)
VALUES (adj_table(h_i).transaction_header_id,
adj_table(h_i).asset_invoice_id,
adj_table(h_i).source_type_code,
adj_table(h_i).adjustment_type,
adj_table(h_i).debit_credit_flag,
adj_table(h_i).code_combination_id,
adj_table(h_i).book_type_code,
adj_table(h_i).period_counter_created,
adj_table(h_i).asset_id,
adj_table(h_i).adjustment_amount,
adj_table(h_i).period_counter_adjusted,
adj_table(h_i).distribution_id,
adj_table(h_i).annualized_adjustment,
adj_table(h_i).deprn_override_flag,
adj_table(h_i).last_update_date,
h_last_updated_by,
h_last_update_login,
adj_table(h_i).track_member_flag, -- Track Member
fa_adjustments_s.nextval, -- adjustment_line_id,
adj_table(h_i).source_dest_code,
adj_table(h_i).source_line_id);
FUNCTION fadoact(X_last_update_date date default sysdate,
X_last_updated_by number default -1,
X_last_update_login number default -1,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
h_dist_book varchar2(30);
h_amount_to_insert number :=0;
SELECT DISTRIBUTION_ID,
CODE_COMBINATION_ID,
UNITS_ASSIGNED
FROM FA_DISTRIBUTION_HISTORY
WHERE ASSET_ID = h_asset_id
AND BOOK_TYPE_CODE = h_dist_book
AND (
(adj_ptr.selection_mode <> FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE_PARTIAL
AND date_ineffective is null
)
OR
(adj_ptr.selection_mode = FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE_PARTIAL
AND transaction_header_id_in=adj_ptr.selection_thid
)
)
ORDER BY DISTRIBUTION_ID;
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE
(CALLING_FN => 'FA_INS_ADJUST_PKG.fadoact'
,p_log_level_rec => p_log_level_rec);
if (adj_ptr.selection_mode <> FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE_PARTIAL) then
SELECT COUNT(*)
INTO h_distribution_lines
FROM FA_DISTRIBUTION_HISTORY
WHERE ASSET_ID=h_asset_id
AND book_type_code=h_dist_book
AND DATE_INEFFECTIVE IS NULL;
SELECT COUNT(*)
INTO h_distribution_lines
FROM FA_DISTRIBUTION_HISTORY
WHERE ASSET_ID=h_asset_id
AND book_type_code=h_dist_book
AND transaction_header_id_in=adj_ptr.selection_thid;
h_amount_to_insert := (h_total_amount * h_units_assigned/
adj_ptr.current_units) -
h_adj_dd_amount;
and adj_ptr.selection_mode <> FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE_REVAL) then
h_amount_to_insert := h_total_amount - h_amount_so_far;
h_amount_to_insert := h_adjustment_amount - h_amount_so_far;
IF (NOT FA_UTILS_PKG.faxrnd(X_amount => h_amount_to_insert,
X_book => adj_ptr.book_type_code
,p_log_level_rec => p_log_level_rec)) THEN
FA_SRVR_MSG.add_message(CALLING_FN => 'FA_INS_ADJUST_PKG.fadoact'
,p_log_level_rec => p_log_level_rec);
h_amount_so_far:=h_amount_so_far+h_amount_to_insert;
element => 'Amount so far-amount inserted ',
value => h_amount_so_far
,p_log_level_rec => p_log_level_rec);
X_cat_id => adj_ptr.selection_retid,
X_distribution_id => h_distribution_id,
X_source_type_code => adj_ptr.source_type_code
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE
(CALLING_FN => 'FA_GCCID_PKG.fadoact',
NAME => h_mesg_name
,p_log_level_rec => p_log_level_rec);
value => h_amount_to_insert
,p_log_level_rec => p_log_level_rec);
adj_ptr.adjustment_amount := h_amount_to_insert;
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE (CALLING_FN => 'FA_INS_ADJUST_PKG.fadoact'
,p_log_level_rec => p_log_level_rec);
adj_ptr.amount_inserted := h_amount_so_far;
FUNCTION fadoclr(X_last_update_date date default sysdate,
X_last_updated_by number default -1,
X_last_update_login number default -1,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
h_row_ctr number :=0;
h_selection_thid number;
h_amount_to_insert number :=0;
SELECT distribution_id,
code_combination_id,
units_assigned,
transaction_header_id_in
FROM FA_DISTRIBUTION_HISTORY
WHERE asset_id = h_asset_id
AND book_type_code=h_dist_book
AND (
(adj_ptr.selection_mode <> FA_ADJUST_TYPE_PKG.FA_AJ_CLEAR_PARTIAL
AND (date_ineffective is null
OR transaction_header_id_out=adj_ptr.selection_thid
)
)
OR
(adj_ptr.selection_mode = FA_ADJUST_TYPE_PKG.FA_AJ_CLEAR_PARTIAL
AND transaction_header_id_out=adj_ptr.selection_thid
)
)
ORDER BY distribution_id;
h_selection_thid:=adj_ptr.selection_thid;
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE
(NAME => 'FA_GET_CAT_ID',
CALLING_FN => 'FA_INS_ADJUST_PKG.fadoclr'
,p_log_level_rec => p_log_level_rec);
if (h_thid_in <> adj_ptr.selection_thid) then
-- Call the Query Fin Info function in detail mode to figure
-- out how much to clear for this distribution
H_DPR_ROW.asset_id := adj_ptr.asset_id;
h_amount_to_insert := h_adj_dd_amount;
element => 'amount to insert-after FACDAMT',
value => h_amount_to_insert
,p_log_level_rec => p_log_level_rec);
IF (NOT FA_UTILS_PKG.faxrnd(X_amount => h_amount_to_insert,
X_book => adj_ptr.book_type_code
,p_log_level_rec => p_log_level_rec)) THEN
FA_SRVR_MSG.add_message(
CALLING_FN => 'FA_INS_ADJUST_PKG.fadoclr'
,p_log_level_rec => p_log_level_rec);
adj_ptr.adjustment_amount := h_amount_to_insert;
h_amount_so_far := h_amount_so_far + h_amount_to_insert;
element => 'amount to insert-AFTER ROUNDING',
value => h_amount_to_insert
,p_log_level_rec => p_log_level_rec);
X_cat_id => adj_ptr.selection_retid,
X_distribution_id => h_distribution_id,
X_source_type_code => adj_ptr.source_type_code
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE
(CALLING_FN => 'FA_GCCID_PKG.fadoclr',
NAME => h_mesg_name
,p_log_level_rec => p_log_level_rec);
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE
(NAME => 'FA_GET_CAT_ID',
CALLING_FN => 'FA_INS_ADJUST_PKG.fadoclr'
,p_log_level_rec => p_log_level_rec);
end if; -- thid<>adj_ptr.selection_thid
adj_ptr.amount_inserted := h_amount_so_far;
element => 'AMOUNT INSERTED ',
value => adj_ptr.amount_inserted
,p_log_level_rec => p_log_level_rec);
FUNCTION fadosglf(X_last_update_date date default sysdate,
X_last_updated_by number default -1,
X_last_update_login number default -1,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
h_calculated_ccid number;
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE (CALLING_FN => 'FA_INS_ADJUST_PKG.fadosglf'
,p_log_level_rec => p_log_level_rec);
X_cat_id => adj_ptr.selection_retid,
X_distribution_id => adj_ptr.distribution_id,
X_source_type_code => adj_ptr.source_type_code
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE
(CALLING_FN =>'FA_GCCID_PKG.fadosglf'
,p_log_level_rec => p_log_level_rec);
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE (CALLING_FN => 'FA_INS_ADJUST_PKG.fadosglf'
,p_log_level_rec => p_log_level_rec);
adj_ptr.amount_inserted:=adj_ptr.adjustment_amount;
element => 'Amt Inserted-before return',
value => adj_ptr.amount_inserted
,p_log_level_rec => p_log_level_rec);
FUNCTION fadosgl(X_last_update_date date default sysdate,
X_last_updated_by number default -1,
X_last_update_login number default -1,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
h_calculated_ccid number;
X_cat_id => adj_ptr.selection_retid,
X_distribution_id => adj_ptr.distribution_id,
X_source_type_code => adj_ptr.source_type_code
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE (CALLING_FN =>'FA_GCCID_PKG.fadosgl'
,p_log_level_rec => p_log_level_rec);
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE(CALLING_FN => 'FA_INS_ADJUST_PKG.fadosgl'
,p_log_level_rec => p_log_level_rec);
adj_ptr.amount_inserted:=adj_ptr.adjustment_amount;
FUNCTION fadoret(X_last_update_date date default sysdate,
X_last_updated_by number default -1,
X_last_update_login number default -1,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
h_dist_book varchar2(30);
h_amount_to_insert number:=0;
SELECT DISTRIBUTION_ID,
CODE_COMBINATION_ID,
TRANSACTION_UNITS
FROM FA_DISTRIBUTION_HISTORY
WHERE ASSET_ID = h_asset_id
AND BOOK_TYPE_CODE = h_dist_book
AND RETIREMENT_ID = h_ret_id
ORDER BY DISTRIBUTION_ID;
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE(CALLING_FN => 'FA_INS_ADJUST_PKG.fadoret'
,p_log_level_rec => p_log_level_rec);
h_ret_id := adj_ptr.selection_retid;
SELECT COUNT(*)
INTO h_distribution_lines
FROM FA_DISTRIBUTION_HISTORY
WHERE ASSET_ID = h_asset_id
AND book_type_code = h_dist_book
AND RETIREMENT_ID = h_ret_id;
h_amount_to_insert := (h_total_amount * abs(h_trans_units)/adj_ptr.units_retired)
- h_adj_dd_amount;
h_amount_to_insert :=h_adjustment_amount - h_amount_so_far;
IF (NOT FA_UTILS_PKG.faxrnd(X_amount => h_amount_to_insert,
X_book => adj_ptr.book_type_code
,p_log_level_rec => p_log_level_rec)) THEN
FA_SRVR_MSG.add_message(CALLING_FN => 'FA_INS_ADJUST_PKG.fadoret'
,p_log_level_rec => p_log_level_rec);
h_amount_so_far:=h_amount_so_far+h_amount_to_insert;
X_cat_id => adj_ptr.selection_retid,
X_distribution_id => h_distribution_id,
X_source_type_code => adj_ptr.source_type_code
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE
(CALLING_FN => 'FA_GCCID_PKG.fadoact',
NAME=>h_mesg_name
,p_log_level_rec => p_log_level_rec);
adj_ptr.adjustment_amount := h_amount_to_insert;
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE (CALLING_FN => 'FA_INS_ADJUST_PKG.fadoret'
,p_log_level_rec => p_log_level_rec);
adj_ptr.amount_inserted := h_amount_so_far;
X_last_update_date date default sysdate,
X_last_updated_by number default -1,
X_last_update_login number default -1,
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
BEGIN <>
-- Assign the passed values from adj_ptr_passed to adj_ptr.
adj_ptr := adj_ptr_passed;
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
adj_table.delete;
adj_table.delete;
if (adj_ptr.selection_mode in (FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE , FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE_PARTIAL)) then
if (not fadoact
(X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
adj_table.delete;
elsif (adj_ptr.selection_mode=FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE_REVAL) then -- YYOON
if (not fadoact
(X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
adj_table.delete;
elsif (adj_ptr.selection_mode=FA_ADJUST_TYPE_PKG.FA_AJ_SINGLE) then
if (not fadosgl
(X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
adj_table.delete;
elsif (adj_ptr.selection_mode=FA_ADJUST_TYPE_PKG.FA_AJ_TRANSFER_SINGLE) then
if (not fadosglf
(X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
adj_table.delete;
elsif (adj_ptr.selection_mode in (FA_ADJUST_TYPE_PKG.FA_AJ_CLEAR, FA_ADJUST_TYPE_PKG.FA_AJ_CLEAR_PARTIAL)) then
if (not fadoclr
(X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
adj_table.delete;
elsif (adj_ptr.selection_mode=FA_ADJUST_TYPE_PKG.FA_AJ_RETIRE) then
if (not fadoret
(X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
adj_table.delete;
adj_table.delete;
end if; -- of adj_ptr.selection_mode
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
adj_table.delete;
adj_ptr_passed.amount_inserted := adj_ptr.amount_inserted;
element => 'adj inserted - BEFORE RETURN',
value => adj_ptr_passed.amount_inserted
,p_log_level_rec => p_log_level_rec);
X_last_update_date in date default sysdate,
X_account in varchar2,
X_account_type in varchar2,
X_current_units in number,
X_selection_mode in varchar2,
X_flush_adj_flag in varchar2,
X_gen_ccid_flag in varchar2,
X_leveling_flag in varchar2,
X_asset_invoice_id in number,
X_amount_inserted out nocopy number,
X_last_updated_by number default -1,
X_last_update_login number default -1,
X_init_message_flag varchar2 default 'NO',
p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
return boolean is
adj_ptr_local FA_ADJUST_TYPE_PKG.fa_adj_row_struct;
adj_ptr_local.last_update_date := X_last_update_date;
if (X_selection_mode='ACTIVE') then
adj_ptr_local.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
elsif (X_selection_mode='SINGLE') then
adj_ptr_local.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_SINGLE;
X_last_update_date,
X_last_updated_by,
X_last_update_login
,p_log_level_rec => p_log_level_rec)) then
FA_SRVR_MSG.ADD_MESSAGE(CALLING_FN => 'FA_INS_ADJUST_PKG.faxinadj'
,p_log_level_rec => p_log_level_rec);
X_amount_inserted := adj_ptr_local.amount_inserted;