[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
maj_select_column varchar2(50);
min_select_column varchar2(50);
select 'P'
into H_MRCSOBTYPE
from fa_book_controls
where book_type_code = h_book
and set_of_books_id = h_sob_id;
SELECT CATEGORY_FLEX_STRUCTURE,
LOCATION_FLEX_STRUCTURE,
ASSET_KEY_FLEX_STRUCTURE
INTO cat_flex_struct,
loc_flex_struct,
assetkey_flex_struct
FROM FA_SYSTEM_CONTROLS;
SELECT BC.ACCOUNTING_FLEX_STRUCTURE,
SOB.CURRENCY_CODE,
BC.FISCAL_YEAR_NAME,
SOB.CHART_OF_ACCOUNTS_ID,
SOB.NAME,
SOB.SET_OF_BOOKS_ID
INTO acct_flex_struct,
h_currency_code,
h_fy_name,
h_chart_of_accounts_id,
h_organization_name,
h_set_of_books_id
FROM FA_BOOK_CONTROLS BC,
GL_SETS_OF_BOOKS SOB
WHERE BC.BOOK_TYPE_CODE = h_book
AND SOB.SET_OF_BOOKS_ID = BC.SET_OF_BOOKS_ID;
SELECT BC.ACCOUNTING_FLEX_STRUCTURE,
SOB.CURRENCY_CODE,
BC.FISCAL_YEAR_NAME,
SOB.CHART_OF_ACCOUNTS_ID,
SOB.NAME,
SOB.SET_OF_BOOKS_ID
INTO acct_flex_struct,
h_currency_code,
h_fy_name,
h_chart_of_accounts_id,
h_organization_name,
h_set_of_books_id
FROM FA_BOOK_CONTROLS_mrc_v BC,
GL_SETS_OF_BOOKS SOB
WHERE BC.BOOK_TYPE_CODE = h_book
AND SOB.SET_OF_BOOKS_ID = BC.SET_OF_BOOKS_ID;
SELECT PERIOD_COUNTER,
NVL(PERIOD_CLOSE_DATE, SYSDATE)
INTO upc,
ucd
FROM FA_DEPRN_PERIODS
WHERE BOOK_TYPE_CODE = h_book
AND PERIOD_NAME = h_period;
SELECT PERIOD_COUNTER,
NVL(PERIOD_CLOSE_DATE, SYSDATE)
INTO upc,
ucd
FROM FA_DEPRN_PERIODS_mrc_v
WHERE BOOK_TYPE_CODE = h_book
AND PERIOD_NAME = h_period;
'SELECT', 'GL_BALANCING') ||' >= :from_bal or :from_bal is NULL)';
'SELECT', 'GL_BALANCING') ||' <= :to_bal or :to_bal is NULL)';
'SELECT', 'GL_ACCOUNT') ||' >= :from_acct or :from_acct is NULL)';
'SELECT', 'GL_ACCOUNT') ||' <= :to_acct or :to_acct is NULL)';
'SELECT', 'FA_COST_CTR') ||' >= :from_cc or :from_cc is NULL)';
'SELECT', 'FA_COST_CTR') ||' <= :to_cc or :to_cc is NULL)';
'SELECT', 'BASED_CATEGORY') ||'= :major_category or :major_category is NULL)';
'SELECT', 'MINOR_CATEGORY') ||'= :minor_category or :minor_category is NULL)';
'SELECT',cat_seg_num) || '= :cat_seg_val';
maj_select_column := null;
min_select_column := null;
maj_select_column := fa_rx_flex_pkg.flex_sql(140,'CAT#',cat_flex_struct,'CAT','SELECT','BASED_CATEGORY');
maj_select_column := maj_select_column || ' MAJOR_CATEGORY';
min_select_column := fa_rx_flex_pkg.flex_sql(140,'CAT#',cat_flex_struct,'CAT','SELECT','MINOR_CATEGORY');
min_select_column := min_select_column || ' MINOR_CATEGORY';
min_select_column := 'NULL';
'SELECT DISTINCT
CB.ASSET_COST_ACCT COST_ACCOUNT,
CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
AH.CATEGORY_ID CATEGORY_ID,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
BOOKS.DEPRN_METHOD_CODE METHOD,
BOOKS.LIFE_IN_MONTHS LIFE,
BOOKS.ADJUSTED_RATE RATE,
BOOKS.PRODUCTION_CAPACITY CAPACITY,
BOOKS.DEPRECIATE_FLAG BOOK_DEPRN_FLAG,
DH.LOCATION_ID LOCATION_ID,
DH.ASSIGNED_TO ASSIGNED_TO,
DH.UNITS_ASSIGNED / AH.UNITS * 100 PERCENT,
substrb(EMP.full_name, 1, 50) EMP_NAME,
substrb(EMP.employee_number, 1, 15) EMP_NUMBER, --Bug#9206900
CC.CODE_COMBINATION_ID CCID,
AH.ASSET_ID ASSET_ID,
AD.ASSET_NUMBER ASSET_NUMBER,
AD.DESCRIPTION ASSET_DESCRIPTION,
AD.TAG_NUMBER TAG_NUMBER,
AD.serial_number SERIAL_NUMBER,
AD.INVENTORIAL INVENTORIAL,
AD.ASSET_KEY_CCID ASSET_KEY_CCID,
DECODE(AD.ASSET_TYPE,''CIP'',''C'',''EXPENSED'',''E'','''') ASSET_TYPE,
CBD.DEPRECIATE_FLAG CATEGORY_DEPRN_FLAG, ' ||
maj_select_column || ' , ' || min_select_column || '
FROM
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_BOOKS BOOKS,
FA_DISTRIBUTION_HISTORY DH,
GL_CODE_COMBINATIONS CC,
PER_PEOPLE_F EMP,
FA_ADDITIONS AD,
FA_CATEGORIES CAT,
FA_CATEGORY_BOOK_DEFAULTS CBD,
FA_BOOK_CONTROLS BC -- Added for bug#2675646
WHERE
CB.BOOK_TYPE_CODE = :h_book AND
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE <= :ucd AND
NVL(AH.DATE_INEFFECTIVE,:ucd+1) > :ucd
AND
BOOKS.BOOK_TYPE_CODE = :h_book AND
BOOKS.ASSET_ID = DH.ASSET_ID AND
nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, :upc) >= :upc AND
BOOKS.DATE_EFFECTIVE <= :ucd AND
nvl(BOOKS.DATE_INEFFECTIVE,:ucd+1)> :ucd
AND -- Added for Bug#2675646
BC.BOOK_TYPE_CODE = :h_book
AND
DH.BOOK_TYPE_CODE = nvl(BC.DISTRIBUTION_SOURCE_BOOK, :h_book) AND -- Changed from = :h_book
DH.DATE_EFFECTIVE <= :ucd AND
nvl(DH.DATE_INEFFECTIVE,:ucd+1) > :ucd AND
DH.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID AND
DH.ASSIGNED_TO = EMP.PERSON_ID(+)
AND trunc(sysdate) between emp.effective_start_date(+) and emp.effective_end_date(+)
AND AD.ASSET_ID = AH.ASSET_ID
AND CAT.CATEGORY_ID = AH.CATEGORY_ID
AND CBD.CATEGORY_ID = CAT.CATEGORY_ID AND
CBD.BOOK_TYPE_CODE = :h_book AND
CBD.START_DPIS <= BOOKS.DATE_PLACED_IN_SERVICE AND -- Changed for Bug:5276352
nvl(CBD.END_DPIS,sysdate) >= BOOKS.DATE_PLACED_IN_SERVICE';
'SELECT DISTINCT
CB.ASSET_COST_ACCT COST_ACCOUNT,
CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
AH.CATEGORY_ID CATEGORY_ID,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
BOOKS.DEPRN_METHOD_CODE METHOD,
BOOKS.LIFE_IN_MONTHS LIFE,
BOOKS.ADJUSTED_RATE RATE,
BOOKS.PRODUCTION_CAPACITY CAPACITY,
BOOKS.DEPRECIATE_FLAG BOOK_DEPRN_FLAG,
DH.LOCATION_ID LOCATION_ID,
DH.ASSIGNED_TO ASSIGNED_TO,
DH.UNITS_ASSIGNED / AH.UNITS * 100 PERCENT,
substrb(EMP.full_name, 1, 50) EMP_NAME,
substrb(EMP.employee_number, 1, 15) EMP_NUMBER, --Bug#9206900
CC.CODE_COMBINATION_ID CCID,
AH.ASSET_ID ASSET_ID,
AD.ASSET_NUMBER ASSET_NUMBER,
AD.DESCRIPTION ASSET_DESCRIPTION,
AD.TAG_NUMBER TAG_NUMBER,
AD.serial_number SERIAL_NUMBER,
AD.INVENTORIAL INVENTORIAL,
AD.ASSET_KEY_CCID ASSET_KEY_CCID,
DECODE(AD.ASSET_TYPE,''CIP'',''C'',''EXPENSED'',''E'','''') ASSET_TYPE,
CBD.DEPRECIATE_FLAG CATEGORY_DEPRN_FLAG, ' ||
maj_select_column || ' , ' || min_select_column || '
FROM
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_BOOKS_mrc_v BOOKS,
FA_DISTRIBUTION_HISTORY DH,
GL_CODE_COMBINATIONS CC,
PER_PEOPLE_F EMP,
FA_ADDITIONS AD,
FA_CATEGORIES CAT,
FA_CATEGORY_BOOK_DEFAULTS CBD,
FA_BOOK_CONTROLS_mrc_v BC -- Added for bug#2675646
WHERE
CB.BOOK_TYPE_CODE = :h_book AND
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE <= :ucd AND
NVL(AH.DATE_INEFFECTIVE,:ucd+1) > :ucd
AND
BOOKS.BOOK_TYPE_CODE = :h_book AND
BOOKS.ASSET_ID = DH.ASSET_ID AND
nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, :upc) >= :upc AND
BOOKS.DATE_EFFECTIVE <= :ucd AND
nvl(BOOKS.DATE_INEFFECTIVE,:ucd+1)> :ucd
AND -- Added for Bug#2675646
BC.BOOK_TYPE_CODE = :h_book
AND
DH.BOOK_TYPE_CODE = nvl(BC.DISTRIBUTION_SOURCE_BOOK, :h_book) AND -- Changed from = :h_book
DH.DATE_EFFECTIVE <= :ucd AND
nvl(DH.DATE_INEFFECTIVE,:ucd+1) > :ucd AND
DH.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID AND
DH.ASSIGNED_TO = EMP.PERSON_ID(+)
AND trunc(sysdate) between emp.effective_start_date(+) and emp.effective_end_date(+)
AND AD.ASSET_ID = AH.ASSET_ID
AND CAT.CATEGORY_ID = AH.CATEGORY_ID
AND CBD.CATEGORY_ID = CAT.CATEGORY_ID AND
CBD.BOOK_TYPE_CODE = :h_book AND
CBD.START_DPIS <= BOOKS.DATE_PLACED_IN_SERVICE AND -- Changed for Bug:5276352
nvl(CBD.END_DPIS,sysdate) >= BOOKS.DATE_PLACED_IN_SERVICE';
h_mesg_name := 'FA_SHARED_INSERT_FAILED';
SELECT count(*) INTO h_is_retired
FROM FA_RETIREMENTS RET,
fa_transaction_headers th
WHERE RET.ASSET_ID = h_asset_id
and ret.book_type_code = h_book
AND RET.DATE_EFFECTIVE <= ucd
AND RET.STATUS in ('PROCESSED','REINSTATE')
and th.transaction_header_id = ret.transaction_header_id_in
and th.transaction_type_code = 'FULL RETIREMENT';
SELECT count(*) INTO h_is_retired
FROM FA_RETIREMENTS_mrc_v RET,
fa_transaction_headers th
WHERE RET.ASSET_ID = h_asset_id
and ret.book_type_code = h_book
AND RET.DATE_EFFECTIVE <= ucd
AND RET.STATUS in ('PROCESSED','REINSTATE')
and th.transaction_header_id = ret.transaction_header_id_in
and th.transaction_type_code = 'FULL RETIREMENT';
SELECT
SUM(COST),
SUM(RESERVE),
SUM(DEPRN_AMOUNT),
SUM(UNITS)
INTO h_cost,
h_reserve,
h_deprn_amount,
h_units
FROM(
SELECT
DECODE(DD.DEPRN_SOURCE_CODE,'B',
DD.ADDITION_COST_TO_CLEAR,DD.COST) COST,
DD.DEPRN_RESERVE RESERVE,
DECODE(DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT,0) DEPRN_AMOUNT,
DH.UNITS_ASSIGNED UNITS
FROM
FA_DEPRN_DETAIL DD,
FA_DISTRIBUTION_HISTORY DH,
FA_BOOK_CONTROLS BC -- Added for Bug#2675646
WHERE
DD.ASSET_ID = h_asset_id AND
DD.BOOK_TYPE_CODE = h_book AND
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
DD.PERIOD_COUNTER =
(SELECT MAX(DD2.PERIOD_COUNTER)
FROM FA_DEPRN_DETAIL DD2
WHERE DD2.BOOK_TYPE_CODE = h_book
AND DD2.ASSET_ID = h_asset_id
AND DD2.DISTRIBUTION_ID = DD.DISTRIBUTION_ID
AND DD2.PERIOD_COUNTER <= upc)
AND -- Added for Bug#2675646
BC.BOOK_TYPE_CODE = h_book
AND
DH.ASSET_ID = h_asset_id and
DH.BOOK_TYPE_CODE = nvl(BC.DISTRIBUTION_SOURCE_BOOK,h_book) AND
DH.LOCATION_ID = h_location_id AND
(DH.ASSIGNED_TO = h_assigned_to OR
(DH.ASSIGNED_TO is null and h_assigned_to is null)) AND
DH.CODE_COMBINATION_ID = h_ccid AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, ucd+1) > ucd
union all
SELECT
0 COST,
0 RESERVE,
DECODE(DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT,0) DEPRN_AMOUNT,
0 UNITS
FROM
FA_DEPRN_DETAIL DD,
FA_DISTRIBUTION_HISTORY DH,
FA_DISTRIBUTION_HISTORY DH_PRIOR,
FA_BOOK_CONTROLS BC -- Added for Bug#2675646
WHERE
DD.ASSET_ID = h_asset_id AND
DD.BOOK_TYPE_CODE = h_book AND
DD.DISTRIBUTION_ID = DH_PRIOR.DISTRIBUTION_ID AND
DH.CODE_COMBINATION_ID = DH_PRIOR.CODE_COMBINATION_ID AND /* Added for Bug 12996138 */
DD.PERIOD_COUNTER =
(SELECT MAX(DD2.PERIOD_COUNTER)
FROM FA_DEPRN_DETAIL DD2
WHERE DD2.BOOK_TYPE_CODE = h_book
AND DD2.ASSET_ID = h_asset_id
AND DD2.DISTRIBUTION_ID = DD.DISTRIBUTION_ID
AND DD2.PERIOD_COUNTER <= upc)
AND -- Added for Bug#2675646
BC.BOOK_TYPE_CODE = h_book
AND
dh.transaction_header_id_in = dh_prior.transaction_header_id_out
and dh.asset_id = dh_prior.asset_id
and dh.book_type_code = dh_prior.book_type_code
-- Bug 7565805
/*and
DH.ASSET_ID = h_asset_id and
DH.BOOK_TYPE_CODE = nvl(BC.DISTRIBUTION_SOURCE_BOOK,h_book) AND
DH.LOCATION_ID = h_location_id AND
(DH.ASSIGNED_TO = h_assigned_to OR
(DH.ASSIGNED_TO is null and h_assigned_to is null)) AND
DH.CODE_COMBINATION_ID = h_ccid AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, ucd+1) > ucd */
UNION ALL
SELECT
DECODE(LU.LOOKUP_CODE, 'ADDITION COST',
DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', 1, -1) *
ADJ.ADJUSTMENT_AMOUNT,0) COST,
DECODE(LU.LOOKUP_CODE,
'DEPRECIATION RESERVE',
DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', -1, 1) *
ADJ.ADJUSTMENT_AMOUNT, 0) RESERVE,
0 DEPRN_AMOUNT,
DECODE(LU.LOOKUP_CODE,
'ADDITION COST',
DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', 1, -1) *
DH.UNITS_ASSIGNED,0) UNITS
FROM
FA_ADJUSTMENTS ADJ,
FA_LOOKUPS LU,
FA_DISTRIBUTION_HISTORY DH,
FA_BOOK_CONTROLS BC -- Added for Bug#2675646
WHERE
LU.LOOKUP_TYPE = 'JOURNAL ENTRIES' AND
((ADJ.ADJUSTMENT_TYPE IN ('COST','CIP COST') AND
LU.LOOKUP_CODE = 'ADDITION COST')
OR
(ADJ.ADJUSTMENT_TYPE = 'RESERVE' AND
LU.LOOKUP_CODE = 'DEPRECIATION RESERVE')) AND
ADJ.SOURCE_TYPE_CODE NOT IN
('DEPRECIATION','ADDITION', 'CIP ADDITION') AND
ADJ.BOOK_TYPE_CODE = h_book AND
ADJ.ASSET_ID = h_asset_id AND
ADJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
ADJ.PERIOD_COUNTER_CREATED = upc
AND -- Added for Bug#2675646
BC.BOOK_TYPE_CODE = h_book
AND
DH.ASSET_ID = h_asset_id AND
DH.BOOK_TYPE_CODE = nvl(BC.DISTRIBUTION_SOURCE_BOOK,h_book) AND -- Changed from = h_book
DH.LOCATION_ID = h_location_id AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, ucd+1) > ucd AND
(DH.ASSIGNED_to = h_assigned_to OR
(DH.ASSIGNED_TO is null and h_assigned_to is null)) AND
(NOT EXISTS (SELECT 1 FROM FA_DEPRN_DETAIL DD
WHERE DD.ASSET_ID = h_asset_id
AND DD.BOOK_TYPE_CODE = h_book
AND DD.PERIOD_COUNTER = upc)));
SELECT
SUM(COST),
SUM(RESERVE),
SUM(DEPRN_AMOUNT),
SUM(UNITS)
INTO h_cost,
h_reserve,
h_deprn_amount,
h_units
FROM(
SELECT
DECODE(DD.DEPRN_SOURCE_CODE,'B',
DD.ADDITION_COST_TO_CLEAR,DD.COST) COST,
DD.DEPRN_RESERVE RESERVE,
DECODE(DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT,0) DEPRN_AMOUNT,
DH.UNITS_ASSIGNED UNITS
FROM
FA_DEPRN_DETAIL_mrc_v DD,
FA_DISTRIBUTION_HISTORY DH,
FA_BOOK_CONTROLS_mrc_v BC -- Added for Bug#2675646
WHERE
DD.ASSET_ID = h_asset_id AND
DD.BOOK_TYPE_CODE = h_book AND
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
DD.PERIOD_COUNTER =
(SELECT MAX(DD2.PERIOD_COUNTER)
FROM FA_DEPRN_DETAIL_mrc_v DD2
WHERE DD2.BOOK_TYPE_CODE = h_book
AND DD2.ASSET_ID = h_asset_id
AND DD2.DISTRIBUTION_ID = DD.DISTRIBUTION_ID
AND DD2.PERIOD_COUNTER <= upc)
AND -- Added for Bug#2675646
BC.BOOK_TYPE_CODE = h_book
AND
DH.ASSET_ID = h_asset_id and
DH.BOOK_TYPE_CODE = nvl(BC.DISTRIBUTION_SOURCE_BOOK,h_book) AND
DH.LOCATION_ID = h_location_id AND
(DH.ASSIGNED_TO = h_assigned_to OR
(DH.ASSIGNED_TO is null and h_assigned_to is null)) AND
DH.CODE_COMBINATION_ID = h_ccid AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, ucd+1) > ucd
union all
SELECT
0 COST,
0 RESERVE,
DECODE(DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT,0) DEPRN_AMOUNT,
0 UNITS
FROM
FA_DEPRN_DETAIL_mrc_v DD,
FA_DISTRIBUTION_HISTORY DH,
FA_DISTRIBUTION_HISTORY DH_PRIOR,
FA_BOOK_CONTROLS_mrc_v BC -- Added for Bug#2675646
WHERE
DD.ASSET_ID = h_asset_id AND
DD.BOOK_TYPE_CODE = h_book AND
DD.DISTRIBUTION_ID = DH_PRIOR.DISTRIBUTION_ID AND
DH.CODE_COMBINATION_ID = DH_PRIOR.CODE_COMBINATION_ID AND /* Added for Bug 12996138 */
DD.PERIOD_COUNTER =
(SELECT MAX(DD2.PERIOD_COUNTER)
FROM FA_DEPRN_DETAIL_mrc_v DD2
WHERE DD2.BOOK_TYPE_CODE = h_book
AND DD2.ASSET_ID = h_asset_id
AND DD2.DISTRIBUTION_ID = DD.DISTRIBUTION_ID
AND DD2.PERIOD_COUNTER <= upc)
AND -- Added for Bug#2675646
BC.BOOK_TYPE_CODE = h_book
AND
dh.transaction_header_id_in = dh_prior.transaction_header_id_out
and dh.asset_id = dh_prior.asset_id
and dh.book_type_code = dh_prior.book_type_code
-- Bug 7565805
/*and
DH.ASSET_ID = h_asset_id and
DH.BOOK_TYPE_CODE = nvl(BC.DISTRIBUTION_SOURCE_BOOK,h_book) AND
DH.LOCATION_ID = h_location_id AND
(DH.ASSIGNED_TO = h_assigned_to OR
(DH.ASSIGNED_TO is null and h_assigned_to is null)) AND
DH.CODE_COMBINATION_ID = h_ccid AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, ucd+1) > ucd */
UNION ALL
SELECT
DECODE(LU.LOOKUP_CODE, 'ADDITION COST',
DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', 1, -1) *
ADJ.ADJUSTMENT_AMOUNT,0) COST,
DECODE(LU.LOOKUP_CODE,
'DEPRECIATION RESERVE',
DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', -1, 1) *
ADJ.ADJUSTMENT_AMOUNT, 0) RESERVE,
0 DEPRN_AMOUNT,
DECODE(LU.LOOKUP_CODE,
'ADDITION COST',
DECODE(ADJ.DEBIT_CREDIT_FLAG, 'DR', 1, -1) *
DH.UNITS_ASSIGNED,0) UNITS
FROM
FA_ADJUSTMENTS_mrc_v ADJ,
FA_LOOKUPS LU,
FA_DISTRIBUTION_HISTORY DH,
FA_BOOK_CONTROLS_mrc_v BC -- Added for Bug#2675646
WHERE
LU.LOOKUP_TYPE = 'JOURNAL ENTRIES' AND
((ADJ.ADJUSTMENT_TYPE IN ('COST','CIP COST') AND
LU.LOOKUP_CODE = 'ADDITION COST')
OR
(ADJ.ADJUSTMENT_TYPE = 'RESERVE' AND
LU.LOOKUP_CODE = 'DEPRECIATION RESERVE')) AND
ADJ.SOURCE_TYPE_CODE NOT IN
('DEPRECIATION','ADDITION', 'CIP ADDITION') AND
ADJ.BOOK_TYPE_CODE = h_book AND
ADJ.ASSET_ID = h_asset_id AND
ADJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
ADJ.PERIOD_COUNTER_CREATED = upc
AND -- Added for Bug#2675646
BC.BOOK_TYPE_CODE = h_book
AND
DH.ASSET_ID = h_asset_id AND
DH.BOOK_TYPE_CODE = nvl(BC.DISTRIBUTION_SOURCE_BOOK,h_book) AND -- Changed from = h_book
DH.LOCATION_ID = h_location_id AND
DH.DATE_EFFECTIVE <= ucd AND
nvl(DH.DATE_INEFFECTIVE, ucd+1) > ucd AND
(DH.ASSIGNED_to = h_assigned_to OR
(DH.ASSIGNED_TO is null and h_assigned_to is null)) AND
(NOT EXISTS (SELECT 1 FROM FA_DEPRN_DETAIL_mrc_v DD
WHERE DD.ASSET_ID = h_asset_id
AND DD.BOOK_TYPE_CODE = h_book
AND DD.PERIOD_COUNTER = upc)));
INSERT INTO fa_asset_listing_rep_itf (
request_id,
date_placed_in_service,
deprn_method,
life_yr_mo,
ltd_deprn,
cost,
nbv,
period_name,
deprn_expense_acct,
asset_cost_acct,
account_description,
company,
asset_number,
tag_number,
serial_number,
description,
inventorial,
cost_center,
accum_deprn_acct,
book_type_code,
category,
location,
asset_key,
organization_name,
major_category,
minor_category,
employee_name,
employee_number,
set_of_books_id,
functional_currency_code,
company_description,
expense_acct_description,
cost_center_description,
category_description,
adjusted_rate,
deprn_amount,
percent,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
units,
book_deprn_flag,
category_deprn_flag)
VALUES (
h_request_id,
h_dpis,
h_method_code,
fnd_number.canonical_to_number(
decode(h_life,null,null,
to_char(floor(h_life/12)) || '.' || to_char(mod(h_life,12),'FM00'))),
h_reserve,
h_cost,
h_cost - h_reserve,
h_period,
acct_all_segs(h_acct_segnum),
h_asset_cost_acct,
h_account_description,
acct_all_segs(h_bal_segnum),
h_asset_number,
h_tag_number,
h_serial_number,
h_description,
h_inventorial,
acct_all_segs(h_cc_segnum),
h_deprn_rsv_acct,
h_book,
concat_cat_str,
concat_loc_str,
concat_key_str,
h_organization_name,
h_major_category,
h_minor_category,
h_emp_name,
h_emp_number,
h_set_of_books_id,
h_currency_code,
h_company_description,
h_expense_acct_description,
h_cost_center_description,
h_category_description,
h_rate,
h_deprn_amount,
h_percent,
h_user_id,
sysdate,
h_user_id,
sysdate,
login_id,
h_units,
h_book_deprn_flag,
h_category_deprn_flag);
fa_rx_util_pkg.debug('asset_listing_run: ' || 'INSERT END');
IF h_mesg_name in ('FA_SHARED_DETELE_FAILED','FA_SHARED_INSERT_FAILED') THEN
fnd_message.set_token('TABLE','FA_ASSET_LISTING_REP_ITF',FALSE);