[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /* leading(dh) index(dh FA_DISTRIBUTION_HISTORY_U1) */
dh.distribution_id,
ad.asset_number,
ad.asset_id,
ad.asset_type,
ad.asset_category_id,
dh.code_combination_id,
bks.bonus_rule,
bks.group_asset_id,
bks.tracking_method
FROM fa_distribution_accounts da2,
fa_additions_b ad,
fa_distribution_history dh,
fa_books bks
WHERE dh.date_ineffective is null
AND dh.book_type_code = p_dist_source_book
AND da2.book_type_code(+) = p_book_type_code
AND da2.distribution_id(+) = dh.distribution_id
AND ad.asset_id = dh.asset_id
/* BUG# 1339219: do not select distributions for assets
which have been fully retired in a prior year */
AND bks.asset_id = dh.asset_id
AND bks.book_type_code = p_book_type_code
AND bks.transaction_header_id_out is null
AND (bks.period_counter_fully_retired is null OR
bks.period_counter_fully_retired >= p_FY_first_pc)
-- end BUG# 1339219
and dh.distribution_id between p_start_range and p_end_range
AND (((p_pregen_asset_acct = 'Y') AND
da2.deprn_expense_account_ccid is NULL) OR
((p_pregen_cat_acct = 'Y') AND
(((ad.asset_type <> 'GROUP') AND
(da2.asset_cost_account_ccid is NULL or
da2.asset_clearing_account_ccid is NULL)) OR
--da2.deprn_expense_account_ccid is NULL OR
da2.deprn_reserve_account_ccid is NULL OR
((ad.asset_type = 'CIP') AND
(da2.cip_cost_account_ccid is NULL OR
da2.cip_clearing_account_ccid is NULL)) OR
((p_allow_reval_flag = 'YES' and
ad.asset_type <> 'GROUP') AND
(da2.reval_amort_account_ccid is NULL OR
da2.reval_rsv_account_ccid is NULL)) OR
((bks.bonus_rule is not null ) AND
(da2.bonus_exp_account_ccid is NULL OR
da2.bonus_rsv_account_ccid is NULL)))) OR
((p_pregen_book_acct = 'Y' and
ad.asset_type <> 'GROUP') AND
(da2.nbv_retired_gain_ccid is NULL OR
da2.nbv_retired_loss_ccid is NULL OR
da2.proceeds_sale_gain_ccid is NULL OR
da2.proceeds_sale_loss_ccid is NULL OR
da2.cost_removal_gain_ccid is NULL OR
da2.cost_removal_loss_ccid is NULL OR
da2.proceeds_sale_clearing_ccid is NULL OR
da2.cost_removal_clearing_ccid is NULL OR
((p_book_class = 'TAX') AND
(((da2.deferred_exp_account_ccid is NULL OR
da2.deferred_rsv_account_ccid is NULL)) OR
((p_allow_deprn_adjust = 'YES') AND
da2.deprn_adj_account_ccid is NULL)))) OR
((p_allow_reval_flag = 'YES') AND
(da2.reval_rsv_gain_account_ccid is NULL OR
da2.reval_rsv_loss_account_ccid is NULL))));
select dp.period_counter
into G_FY_first_pc
from fa_deprn_periods dp
where dp.book_type_code = p_book_type_code
and dp.fiscal_year = fa_cache_pkg.fazcbc_record.current_fiscal_year
and dp.period_num =
(select min(period_num)
from fa_deprn_periods dp2
where dp2.book_type_code = p_book_type_code
and dp2.fiscal_year = fa_cache_pkg.fazcbc_record.current_fiscal_year);
SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
NVL(sum(decode(status,'FAILED', 1, 0)),0),
NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
count(*)
INTO l_unassigned_cnt,
l_failed_cnt,
l_wip_cnt,
l_completed_cnt,
l_total_cnt
FROM FA_WORKER_JOBS
WHERE request_id = p_parent_request_id;
UPDATE FA_WORKER_JOBS
SET status = 'IN PROCESS',
worker_num = p_request_number
WHERE status = 'UNASSIGNED'
AND request_id = p_parent_request_id
AND rownum < 2;
fa_debug_pkg.add(l_calling_fn, 'selecting', 'ranges'
,p_log_level_rec => g_log_level_rec);
SELECT start_range,
end_range
INTO l_start_range,
l_end_range
FROM FA_WORKER_JOBS
WHERE request_id = p_parent_request_id
AND worker_num = p_request_number
AND status = 'IN PROCESS';
END LOOP; -- bulk update loop
UPDATE FA_WORKER_JOBS
SET status = 'COMPLETED'
WHERE request_id = p_parent_request_id
AND worker_num = p_request_number
AND status = 'IN PROCESS';
UPDATE FA_WORKER_JOBS
SET status = 'FAILED'
WHERE request_id = p_parent_request_id
AND worker_num = p_request_number
AND status = 'IN PROCESS';
SELECT ASSET_COST_ACCOUNT_CCID,
ASSET_CLEARING_ACCOUNT_CCID,
DEPRN_EXPENSE_ACCOUNT_CCID,
DEPRN_RESERVE_ACCOUNT_CCID,
CIP_COST_ACCOUNT_CCID,
CIP_CLEARING_ACCOUNT_CCID,
NBV_RETIRED_GAIN_CCID,
NBV_RETIRED_LOSS_CCID,
PROCEEDS_SALE_GAIN_CCID,
PROCEEDS_SALE_LOSS_CCID,
COST_REMOVAL_GAIN_CCID,
COST_REMOVAL_LOSS_CCID,
PROCEEDS_SALE_CLEARING_CCID,
COST_REMOVAL_CLEARING_CCID,
reval_rsv_gain_account_ccid,
reval_rsv_loss_account_ccid,
deferred_exp_account_ccid,
deferred_rsv_account_ccid,
deprn_adj_account_ccid,
reval_amort_account_ccid,
reval_rsv_account_ccid,
bonus_exp_account_ccid,
bonus_rsv_account_ccid
FROM FA_DISTRIBUTION_ACCOUNTS
WHERE BOOK_TYPE_CODE = X_book_type_code
AND DISTRIBUTION_ID = X_distribution_id;
INSERT INTO FA_DISTRIBUTION_ACCOUNTS(
BOOK_TYPE_CODE,
DISTRIBUTION_ID,
ASSET_COST_ACCOUNT_CCID,
ASSET_CLEARING_ACCOUNT_CCID,
DEPRN_EXPENSE_ACCOUNT_CCID,
DEPRN_RESERVE_ACCOUNT_CCID,
CIP_COST_ACCOUNT_CCID,
CIP_CLEARING_ACCOUNT_CCID,
NBV_RETIRED_GAIN_CCID,
NBV_RETIRED_LOSS_CCID,
PROCEEDS_SALE_GAIN_CCID,
PROCEEDS_SALE_LOSS_CCID,
COST_REMOVAL_GAIN_CCID,
COST_REMOVAL_LOSS_CCID,
COST_REMOVAL_CLEARING_CCID,
PROCEEDS_SALE_CLEARING_CCID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
deferred_exp_account_ccid,
deferred_rsv_account_ccid,
reval_rsv_gain_account_ccid,
reval_rsv_loss_account_ccid,
deprn_adj_account_ccid,
reval_amort_account_ccid,
reval_rsv_account_ccid,
bonus_exp_account_ccid,
bonus_rsv_account_ccid
)VALUES(
X_book_type_code,
X_distribution_id,
h_cost_acct_ccid,
h_clearing_acct_ccid,
h_expense_acct_ccid,
h_reserve_acct_ccid,
h_cip_cost_ccid,
h_cip_clearing_ccid,
h_nbv_gain_ccid,
h_nbv_loss_ccid,
h_pos_gain_ccid,
h_pos_loss_ccid,
h_cor_gain_ccid,
h_cor_loss_ccid,
h_cor_clearing_ccid,
h_pos_clearing_ccid,
sysdate,
h_user_id,
h_user_id,
sysdate,
h_login_id,
h_deferred_exp_ccid,
h_deferred_rsv_ccid,
h_reval_rsv_gain_ccid,
h_reval_rsv_loss_ccid,
h_deprn_adj_ccid,
h_reval_amort_ccid,
h_reval_rsv_ccid,
h_bonus_exp_ccid,
h_bonus_rsv_ccid
);
UPDATE FA_DISTRIBUTION_ACCOUNTS
SET ASSET_COST_ACCOUNT_CCID = h_cost_acct_ccid,
ASSET_CLEARING_ACCOUNT_CCID = h_clearing_acct_ccid,
DEPRN_EXPENSE_ACCOUNT_CCID = h_expense_acct_ccid,
DEPRN_RESERVE_ACCOUNT_CCID = h_reserve_acct_ccid,
CIP_COST_ACCOUNT_CCID = h_cip_cost_ccid,
CIP_CLEARING_ACCOUNT_CCID = h_cip_clearing_ccid,
NBV_RETIRED_GAIN_CCID = h_nbv_gain_ccid,
NBV_RETIRED_LOSS_CCID = h_nbv_loss_ccid,
PROCEEDS_SALE_GAIN_CCID = h_pos_gain_ccid,
PROCEEDS_SALE_LOSS_CCID = h_pos_loss_ccid,
COST_REMOVAL_GAIN_CCID = h_cor_gain_ccid,
COST_REMOVAL_LOSS_CCID = h_cor_loss_ccid,
COST_REMOVAL_CLEARING_CCID = h_cor_clearing_ccid,
PROCEEDS_SALE_CLEARING_CCID = h_pos_clearing_ccid,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = h_user_id,
LAST_UPDATE_LOGIN = h_login_id,
deferred_exp_account_ccid = h_deferred_exp_ccid,
deferred_rsv_account_ccid = h_deferred_rsv_ccid,
reval_rsv_gain_account_ccid = h_reval_rsv_gain_ccid,
reval_rsv_loss_account_ccid = h_reval_rsv_loss_ccid,
deprn_adj_account_ccid = h_deprn_adj_ccid,
reval_amort_account_ccid = h_reval_amort_ccid,
reval_rsv_account_ccid = h_reval_rsv_ccid,
bonus_exp_account_ccid = h_bonus_exp_ccid,
bonus_rsv_account_ccid = h_bonus_rsv_ccid
WHERE BOOK_TYPE_CODE = X_book_type_code
AND DISTRIBUTION_ID = X_distribution_id;
INSERT INTO FA_WORKER_JOBS
(START_RANGE, END_RANGE, WORKER_NUM, STATUS,REQUEST_ID)
SELECT MIN(DISTRIBUTION_ID), MAX(DISTRIBUTION_ID), 0,
'UNASSIGNED', p_parent_request_id FROM ( SELECT /*+ parallel(DH) */
DISTRIBUTION_ID, FLOOR(RANK()
OVER (ORDER BY DISTRIBUTION_ID)/l_batch_size ) UNIT_ID
FROM FA_DISTRIBUTION_HISTORY DH
WHERE DH.BOOK_TYPE_CODE = l_dist_source_book )
GROUP BY UNIT_ID;
fa_debug_pkg.add(l_calling_fn, 'rows inserted into worker jobs: ', SQL%ROWCOUNT
,p_log_level_rec => g_log_level_rec);