[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PREVIEW or RUN . Both have the same calculations but RUN mode updates the core tables
whereas PREVIEW only updates the interface table
*/
PROCEDURE PROCESS_ASSETS(p_mass_tax_adjustment_id IN NUMBER,
p_parent_request_id IN NUMBER,
p_mode IN VARCHAR2,
p_start_range IN NUMBER,
p_end_range IN NUMBER) IS
l_trans_rec FA_API_TYPES.trans_rec_type;
SELECT DISTINCT AD.ASSET_ID/*,
AD.ASSET_NUMBER,
AD.ASSET_TYPE,
AD.ASSET_CATEGORY_ID,
AD.CURRENT_UNITS */ --vmarella .
FROM FA_DEPRN_SUMMARY DS,
FA_BOOKS BK,
FA_ADDITIONS AD,
FA_TRANSACTION_HEADERS TH
WHERE DS.BOOK_TYPE_CODE = g_adj_asset_hdr_rec.book_type_code
AND DS.ASSET_ID = AD.ASSET_ID
AND (DS.PERIOD_COUNTER BETWEEN g_start_pc AND g_end_pc)
AND BK.ASSET_ID = AD.ASSET_ID
AND BK.BOOK_TYPE_CODE = g_adj_asset_hdr_rec.book_type_code
AND BK.DATE_INEFFECTIVE IS NULL
AND TH.ASSET_ID (+) = AD.ASSET_ID
AND TH.BOOK_TYPE_CODE (+) = g_adj_asset_hdr_rec.book_type_code
AND TH.MASS_TRANSACTION_ID (+) = p_mass_tax_adjustment_id
AND TH.MASS_TRANSACTION_ID IS NULL
AND AD.ASSET_ID BETWEEN p_start_range AND p_end_range;
SELECT ADJUSTED_BOOK_TYPE_CODE,
CONTROL_BOOK_TYPE_CODE,
DEPRN_ADJUSTMENT_FACTOR,
FISCAL_YEAR
INTO g_adj_asset_hdr_rec.book_type_code,
g_ctl_asset_hdr_rec.book_type_code,
g_dpr_adj_factor,
g_asset_tax_rsv_adj_rec.fiscal_year
FROM FA_MASS_TAX_ADJUSTMENTS
WHERE MASS_TAX_ADJUSTMENT_ID = p_mass_tax_adjustment_id;
SELECT
DISTINCT 1
INTO
l_is_retired
FROM
FA_TRANSACTION_HEADERS TH
WHERE
TH.ASSET_ID = g_adj_asset_hdr_rec.asset_id AND
TH.BOOK_TYPE_CODE = g_adj_asset_hdr_rec.book_type_code AND
TH.TRANSACTION_TYPE_CODE = 'FULL RETIREMENT';
select distinct 1
into l_is_retired
from FA_TRANSACTION_HEADERS TH,
FA_DEPRN_PERIODS DP1,
FA_DEPRN_PERIODS DP2
where TH.asset_id = g_adj_asset_hdr_rec.asset_id
and TH.book_type_code = g_adj_asset_hdr_rec.book_type_code
and TH.transaction_type_code = 'PARTIAL RETIREMENT'
and DP1.period_counter = g_start_pc and
DP1.book_type_code = g_adj_asset_hdr_rec.book_type_code and
DP1.period_open_date <= TH.date_effective
and DP2.period_counter = g_end_pc and
DP2.book_type_code = g_adj_asset_hdr_rec.book_type_code and
DP2.period_close_date >= TH.date_effective;
select bk1.cost, bk2.cost, bk3.cost, nvl(bk1.adjusted_recoverable_cost, bk1.recoverable_cost)
into l_adj_cost, l_ctl_cost, l_corp_cost, l_adj_rec_cost
from fa_books bk1,fa_books bk2, fa_books bk3
where bk1.book_type_code = g_adj_asset_hdr_rec.book_type_code and
bk1.asset_id = g_adj_asset_hdr_rec.asset_id and
bk1.date_ineffective is null
and bk2.book_type_code = g_ctl_asset_hdr_rec.book_type_code and
bk2.date_ineffective is null and
bk2.asset_id = bk1.asset_id
and bk3.book_type_code = fa_cache_pkg.fazcbc_record.distribution_source_book and
bk3.date_ineffective is null and
bk3.asset_id = bk1.asset_id;
PREVIEW or RUN . Both have the same calculations but RUN mode updates the core tables
whereas PREVIEW only updates the interface table
*/
PROCEDURE Do_Deprn_Adjustment
(p_mass_tax_adjustment_id IN NUMBER,
p_mode IN VARCHAR2,
p_parent_request_id IN NUMBER,
p_total_requests IN NUMBER,
p_request_number IN NUMBER,
x_success_count OUT NOCOPY NUMBER,
x_failure_count OUT NOCOPY NUMBER,
x_worker_jobs OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY NUMBER
) IS
return_status BOOLEAN := TRUE;
SELECT ADJUSTED_BOOK_TYPE_CODE,
CONTROL_BOOK_TYPE_CODE,
DEPRN_ADJUSTMENT_FACTOR,
FISCAL_YEAR
INTO g_adj_asset_hdr_rec.book_type_code,
g_ctl_asset_hdr_rec.book_type_code,
g_dpr_adj_factor,
g_asset_tax_rsv_adj_rec.fiscal_year
FROM FA_MASS_TAX_ADJUSTMENTS
WHERE MASS_TAX_ADJUSTMENT_ID = p_mass_tax_adjustment_id;
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;
SELECT start_range,
end_range
INTO l_start_range,
l_end_range
FROM FA_WORKER_JOBS
WHERE worker_num = p_request_number
AND request_Id = p_parent_request_id
AND status = 'IN PROCESS';
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';
g_phase := 'select min and max asset ids';
SELECT NVL(max(asset_id), 0),
nvl(min(asset_id), 1)
INTO l_max_number,
l_start_number
FROM FA_books
WHERE book_type_code = p_book_type_code
AND transaction_header_id_out is null;
g_phase := 'Loop to insert into FA_WORKER_JOBS: '
|| l_start_number || ', ' || l_end_number;
INSERT INTO FA_WORKER_JOBS (start_range, end_range, status, request_id)
VALUES (l_start_number, least(l_end_number, l_max_number),'UNASSIGNED');
INSERT INTO FA_WORKER_JOBS
(START_RANGE, END_RANGE, WORKER_NUM, STATUS,REQUEST_ID)
SELECT MIN(ASSET_ID), MAX(ASSET_ID), 0,
'UNASSIGNED', p_parent_request_id
FROM ( SELECT /*+ parallel(BK) */
ASSET_ID, FLOOR(RANK()
OVER (ORDER BY ASSET_ID)/l_batch_size ) UNIT_ID
FROM FA_BOOKS BK
WHERE BK.BOOK_TYPE_CODE = p_book_type_code
AND BK.TRANSACTION_HEADER_ID_OUT IS NULL )
GROUP BY UNIT_ID;
fa_debug_pkg.add(l_calling_fn,'Inserted ' || SQL%ROWCOUNT || ' jobs into FA_WORKER_JOBS table','');