[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(sum(fixed_assets_cost), 0)
into l_total_cost
from fa_mass_additions
where split_merged_code = 'MC'
and parent_mass_addition_id = px_mass_add_rec.mass_addition_id
or mass_addition_id = px_mass_add_rec.mass_addition_id;
update fa_mass_additions
set EXPENSE_CODE_COMBINATION_ID = px_mass_add_rec.EXPENSE_CODE_COMBINATION_ID,
location_id = px_mass_add_rec.location_id,
asset_category_id = px_mass_add_rec.asset_category_id,
asset_key_ccid = px_mass_add_rec.asset_key_ccid,
posting_status = 'POST',
queue_name = 'POST',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
where mass_addition_id = px_mass_add_rec.mass_addition_id;
update fa_mass_additions
set EXPENSE_CODE_COMBINATION_ID = px_mass_add_rec.EXPENSE_CODE_COMBINATION_ID,
location_id = px_mass_add_rec.location_id,
asset_category_id = px_mass_add_rec.asset_category_id,
asset_key_ccid = px_mass_add_rec.asset_key_ccid,
posting_status = 'ON HOLD',
queue_name = 'ON HOLD',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
where mass_addition_id = px_mass_add_rec.mass_addition_id;
update fa_mass_additions
set EXPENSE_CODE_COMBINATION_ID = nvl(px_mass_add_rec.EXPENSE_CODE_COMBINATION_ID,
EXPENSE_CODE_COMBINATION_ID),
location_id = nvl(px_mass_add_rec.location_id,
location_id),
asset_category_id = nvl(px_mass_add_rec.asset_category_id,
asset_category_id),
asset_key_ccid = nvl(px_mass_add_rec.asset_key_ccid,
asset_key_ccid),
posting_status = 'ON HOLD',
queue_name = 'ON HOLD',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
where mass_addition_id = px_mass_add_rec.mass_addition_id;
select nvl(sum(fixed_assets_cost), 0)
into l_total_cost
from fa_mass_additions
where split_merged_code = 'MC'
and parent_mass_addition_id = px_mass_add_rec.mass_addition_id
and merged_code = 'MC'
and posting_status = 'MERGED';
select l_total_cost + nvl(cost, 0)
into l_total_cost
from fa_books
where book_type_code = px_mass_add_rec.book_type_code
and asset_id = px_mass_add_rec.add_to_asset_id
and transaction_header_id_out is null;
update fa_mass_additions
set add_to_asset_id = px_mass_add_rec.add_to_asset_id,
posting_status = 'POST',
queue_name = 'POST',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
where mass_addition_id = px_mass_add_rec.mass_addition_id;
update fa_mass_additions
set posting_status = 'POST',
queue_name = 'POST',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
where mass_addition_id = px_mass_add_rec.mass_addition_id;
select ad.asset_id
into l_asset_id
from fa_additions ad, fa_books bk
where asset_key_ccid = px_mass_add_rec.asset_category_id
and asset_key_ccid = px_mass_add_rec.ASSET_KEY_CCID
and ad.asset_id = bk.asset_id
and bk.book_type_code = px_mass_add_rec.book_type_code
and bk.transaction_header_id_out is null
and rownum < 2;
l_select varchar2(2000);
SELECT Gl_sob.Set_Of_Books_id, Chart_Of_Accounts_ID
INTO l_sob_id, l_coa
FROM GL_Sets_Of_Books GL_sob, FA_Book_Controls FA_BC
WHERE GL_sob.Set_Of_Books_ID = FA_BC.Set_Of_Books_ID
AND Book_Type_Code = px_mass_add_rec.book_type_code;
l_plsql_block := 'BEGIN EJINMAP.get_asset_key_map(:l_table,:l_product,:l_sob_id,:l_select,:l_from,:l_where); END;';
USING l_table, l_product, l_sob_id, l_select, l_from, l_where;
l_select,
l_from,
l_where);*/
l_query := 'select ' || l_select || ',Payables_Code_Combination_ID
from ' || l_from ||
',FA_Mass_Additions FA where ' || l_where ||
' AND TRANS.Invoice_ID= FA.Invoice_ID
AND TRANS.Distribution_Line_Number=FA.AP_Distribution_Line_Number
AND Posting_Status IN (''NEW'',''ON HOLD'')
AND book_type_code=:px_mass_add_rec.book_type_code
AND Feeder_System_Name=''ORACLE PAYABLES''';
l_plsql_block := 'BEGIN EJINMAP.get_asset_key_map(:l_table,:l_product,:l_sob_id,:l_select,:l_from,:l_where); END;';
USING l_table, l_product, l_sob_id, OUT l_select, OUT l_from, OUT l_where;
l_query := 'SELECT AFF.Code_Combination_ID ,Mass_Addition_ID
,Invoice_Number ,FA.AP_Distribution_Line_Number
,Feeder_System_Name ,Fixed_Assets_Cost
,FA.Description Description ,Queue_Name ' ||
l_select || ',Payables_Code_Combination_ID
from ' || l_from ||
',FA_Mass_Additions FA where ' || l_where ||
'AND TRANS.Je_Header_ID = FA.Je_Header_ID
AND TRANS.Je_Line_Num = FA.Je_Line_Num
AND book_type_code = :px_mass_add_rec.book_type_code
AND Posting_Status IN (''NEW'',''ON HOLD'')
AND Feeder_System_Name = ''GL''';
SELECT asset_key_segment1,
asset_key_segment2,
asset_key_segment3,
asset_key_segment4,
asset_key_segment5,
asset_key_segment6,
asset_key_segment7,
asset_key_segment8,
asset_key_segment9,
asset_key_segment10
INTO l_akey_segment(1),
l_akey_segment(2),
l_akey_segment(3),
l_akey_segment(4),
l_akey_segment(5),
l_akey_segment(6),
l_akey_segment(7),
l_akey_segment(8),
l_akey_segment(9),
l_akey_segment(10)
FROM fa_mass_additions
WHERE mass_addition_id = px_mass_add_rec.mass_addition_id;
SELECT code_combination_id
INTO l_akey_ccid
FROM fa_asset_keywords
WHERE nvl(segment1, '-1') = nvl(l_akey_segment(1), '-1')
and nvl(segment2, '-1') = nvl(l_akey_segment(2), '-1')
and nvl(segment3, '-1') = nvl(l_akey_segment(3), '-1')
and nvl(segment4, '-1') = nvl(l_akey_segment(4), '-1')
and nvl(segment5, '-1') = nvl(l_akey_segment(5), '-1')
and nvl(segment6, '-1') = nvl(l_akey_segment(6), '-1')
and nvl(segment7, '-1') = nvl(l_akey_segment(7), '-1')
and nvl(segment8, '-1') = nvl(l_akey_segment(8), '-1')
and nvl(segment9, '-1') = nvl(l_akey_segment(9), '-1')
and nvl(segment10, '-1') = nvl(l_akey_segment(10), '-1');
SELECT FA_Asset_keywords_S.Nextval
INTO akey_ccid_seq
FROM DUAL;
l_debug_str := 'Insert the asset key';
INSERT INTO fa_asset_keywords
(CODE_COMBINATION_ID,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SUMMARY_FLAG,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES
(akey_ccid_seq,
l_akey_segment(1),
l_akey_segment(2),
l_akey_segment(3),
l_akey_segment(4),
l_akey_segment(5),
l_akey_segment(6),
l_akey_segment(7),
l_akey_segment(8),
l_akey_segment(9),
l_akey_segment(10),
'Y',
'Y',
NULL,
NULL,
sysdate,
FND_GLOBAL.USER_ID,
-1);
SELECT code_combination_id
INTO px_mass_add_rec.asset_key_ccid
FROM fa_asset_keywords
WHERE nvl(segment1, '-1') = nvl(l_asset_key_segment1, '-1')
and nvl(segment2, '-1') = nvl(l_asset_key_segment2, '-1')
and nvl(segment3, '-1') = nvl(l_asset_key_segment3, '-1')
and nvl(segment4, '-1') = nvl(l_asset_key_segment4, '-1')
and nvl(segment5, '-1') = nvl(l_asset_key_segment5, '-1')
and nvl(segment6, '-1') = nvl(l_asset_key_segment6, '-1')
and nvl(segment7, '-1') = nvl(l_asset_key_segment7, '-1')
and nvl(segment8, '-1') = nvl(l_asset_key_segment8, '-1')
and nvl(segment9, '-1') = nvl(l_asset_key_segment9, '-1')
and nvl(segment10, '-1') = nvl(l_asset_key_segment10, '-1');
SELECT FA_Asset_keywords_S.Nextval
INTO akey_ccid_seq
FROM DUAL;
INSERT INTO fa_asset_keywords
(CODE_COMBINATION_ID,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SUMMARY_FLAG,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES
(akey_ccid_seq,
l_akey_segment(1),
l_akey_segment(2),
l_akey_segment(3),
l_akey_segment(4),
l_akey_segment(5),
l_akey_segment(6),
l_akey_segment(7),
l_akey_segment(8),
l_akey_segment(9),
l_akey_segment(10),
'Y',
'Y',
NULL,
NULL,
sysdate,
-1,
-1);
select lookup_code
from fa_lookups
where lookup_type = c_lookup_type
and enabled_flag = 'Y';
Select sob.chart_of_accounts_id
into h_chart_of_accounts_id
From fa_book_controls bc, gl_sets_of_books sob
Where sob.set_of_books_id = bc.set_of_books_id
And bc.book_type_code = px_mass_add_rec.book_type_code;
select Segment1,
Segment2,
Segment3,
Segment4,
Segment5,
Segment6,
Segment7,
Segment8,
Segment9,
Segment10,
Segment11,
Segment12,
Segment13,
Segment14,
Segment15,
Segment16,
Segment17,
Segment18,
Segment19,
Segment20,
Segment21,
Segment22,
Segment23,
Segment24,
Segment25,
Segment26,
Segment27,
Segment28,
Segment29,
Segment30,
enabled_flag
into l_segment(1),
l_segment(2),
l_segment(3),
l_segment(4),
l_segment(5),
l_segment(6),
l_segment(7),
l_segment(8),
l_segment(9),
l_segment(10),
l_segment(11),
l_segment(12),
l_segment(13),
l_segment(14),
l_segment(15),
l_segment(16),
l_segment(17),
l_segment(18),
l_segment(19),
l_segment(20),
l_segment(21),
l_segment(22),
l_segment(23),
l_segment(24),
l_segment(25),
l_segment(26),
l_segment(27),
l_segment(28),
l_segment(29),
l_segment(30),
l_gl_ccid_enabled_flag
from gl_code_combinations
where code_combination_id =
px_mass_add_rec.payables_code_combination_id
and chart_of_accounts_id = h_chart_of_accounts_id;
select max(code_combination_id)
into l_clearing_acct_ccid
from gl_code_combinations gl_ccid
where decode(l_segment(1), null, '-1', gl_ccid.Segment1) =
nvl(l_segment(1), '-1')
and decode(l_segment(2), null, '-1', gl_ccid.Segment2) =
nvl(l_segment(2), '-1')
and decode(l_segment(3), null, '-1', gl_ccid.Segment3) =
nvl(l_segment(3), '-1')
and decode(l_segment(4), null, '-1', gl_ccid.Segment4) =
nvl(l_segment(4), '-1')
and decode(l_segment(5), null, '-1', gl_ccid.Segment5) =
nvl(l_segment(5), '-1')
and decode(l_segment(6), null, '-1', gl_ccid.Segment6) =
nvl(l_segment(6), '-1')
and decode(l_segment(7), null, '-1', gl_ccid.Segment7) =
nvl(l_segment(7), '-1')
and decode(l_segment(8), null, '-1', gl_ccid.Segment8) =
nvl(l_segment(8), '-1')
and decode(l_segment(9), null, '-1', gl_ccid.Segment9) =
nvl(l_segment(9), '-1')
and decode(l_segment(10), null, '-1', gl_ccid.Segment10) =
nvl(l_segment(10), '-1')
and decode(l_segment(11), null, '-1', gl_ccid.Segment11) =
nvl(l_segment(11), '-1')
and decode(l_segment(12), null, '-1', gl_ccid.Segment12) =
nvl(l_segment(12), '-1')
and decode(l_segment(13), null, '-1', gl_ccid.Segment13) =
nvl(l_segment(13), '-1')
and decode(l_segment(14), null, '-1', gl_ccid.Segment14) =
nvl(l_segment(14), '-1')
and decode(l_segment(15), null, '-1', gl_ccid.Segment15) =
nvl(l_segment(15), '-1')
and decode(l_segment(16), null, '-1', gl_ccid.Segment16) =
nvl(l_segment(16), '-1')
and decode(l_segment(17), null, '-1', gl_ccid.Segment17) =
nvl(l_segment(17), '-1')
and decode(l_segment(18), null, '-1', gl_ccid.Segment18) =
nvl(l_segment(18), '-1')
and decode(l_segment(19), null, '-1', gl_ccid.Segment19) =
nvl(l_segment(19), '-1')
and decode(l_segment(20), null, '-1', gl_ccid.Segment20) =
nvl(l_segment(20), '-1')
and decode(l_segment(21), null, '-1', gl_ccid.Segment21) =
nvl(l_segment(21), '-1')
and decode(l_segment(22), null, '-1', gl_ccid.Segment22) =
nvl(l_segment(22), '-1')
and decode(l_segment(23), null, '-1', gl_ccid.Segment23) =
nvl(l_segment(23), '-1')
and decode(l_segment(24), null, '-1', gl_ccid.Segment24) =
nvl(l_segment(24), '-1')
and decode(l_segment(25), null, '-1', gl_ccid.Segment25) =
nvl(l_segment(25), '-1')
and decode(l_segment(26), null, '-1', gl_ccid.Segment26) =
nvl(l_segment(26), '-1')
and decode(l_segment(27), null, '-1', gl_ccid.Segment27) =
nvl(l_segment(27), '-1')
and decode(l_segment(28), null, '-1', gl_ccid.Segment28) =
nvl(l_segment(28), '-1')
and decode(l_segment(29), null, '-1', gl_ccid.Segment29) =
nvl(l_segment(29), '-1')
and decode(l_segment(30), null, '-1', gl_ccid.Segment30) =
nvl(l_segment(30), '-1')
and chart_of_accounts_id = h_chart_of_accounts_id
and exists
(select 1
from fa_category_books cat
where cat.ASSET_CLEARING_ACCOUNT_CCID =
gl_ccid.code_combination_id
and cat.book_type_code = px_mass_add_rec.book_type_code);
select category_id
into px_mass_add_rec.ASSET_CATEGORY_ID
from fa_category_books
where ASSET_CLEARING_ACCOUNT_CCID = l_clearing_acct_ccid
and book_type_code = px_mass_add_rec.book_type_code
and rownum = 1;
select max(code_combination_id)
into l_clearing_acct_ccid
from gl_code_combinations gl_ccid
where decode(l_segment(1), null, '-1', gl_ccid.Segment1) =
nvl(l_segment(1), '-1')
and decode(l_segment(2), null, '-1', gl_ccid.Segment2) =
nvl(l_segment(2), '-1')
and decode(l_segment(3), null, '-1', gl_ccid.Segment3) =
nvl(l_segment(3), '-1')
and decode(l_segment(4), null, '-1', gl_ccid.Segment4) =
nvl(l_segment(4), '-1')
and decode(l_segment(5), null, '-1', gl_ccid.Segment5) =
nvl(l_segment(5), '-1')
and decode(l_segment(6), null, '-1', gl_ccid.Segment6) =
nvl(l_segment(6), '-1')
and decode(l_segment(7), null, '-1', gl_ccid.Segment7) =
nvl(l_segment(7), '-1')
and decode(l_segment(8), null, '-1', gl_ccid.Segment8) =
nvl(l_segment(8), '-1')
and decode(l_segment(9), null, '-1', gl_ccid.Segment9) =
nvl(l_segment(9), '-1')
and decode(l_segment(10), null, '-1', gl_ccid.Segment10) =
nvl(l_segment(10), '-1')
and decode(l_segment(11), null, '-1', gl_ccid.Segment11) =
nvl(l_segment(11), '-1')
and decode(l_segment(12), null, '-1', gl_ccid.Segment12) =
nvl(l_segment(12), '-1')
and decode(l_segment(13), null, '-1', gl_ccid.Segment13) =
nvl(l_segment(13), '-1')
and decode(l_segment(14), null, '-1', gl_ccid.Segment14) =
nvl(l_segment(14), '-1')
and decode(l_segment(15), null, '-1', gl_ccid.Segment15) =
nvl(l_segment(15), '-1')
and decode(l_segment(16), null, '-1', gl_ccid.Segment16) =
nvl(l_segment(16), '-1')
and decode(l_segment(17), null, '-1', gl_ccid.Segment17) =
nvl(l_segment(17), '-1')
and decode(l_segment(18), null, '-1', gl_ccid.Segment18) =
nvl(l_segment(18), '-1')
and decode(l_segment(19), null, '-1', gl_ccid.Segment19) =
nvl(l_segment(19), '-1')
and decode(l_segment(20), null, '-1', gl_ccid.Segment20) =
nvl(l_segment(20), '-1')
and decode(l_segment(21), null, '-1', gl_ccid.Segment21) =
nvl(l_segment(21), '-1')
and decode(l_segment(22), null, '-1', gl_ccid.Segment22) =
nvl(l_segment(22), '-1')
and decode(l_segment(23), null, '-1', gl_ccid.Segment23) =
nvl(l_segment(23), '-1')
and decode(l_segment(24), null, '-1', gl_ccid.Segment24) =
nvl(l_segment(24), '-1')
and decode(l_segment(25), null, '-1', gl_ccid.Segment25) =
nvl(l_segment(25), '-1')
and decode(l_segment(26), null, '-1', gl_ccid.Segment26) =
nvl(l_segment(26), '-1')
and decode(l_segment(27), null, '-1', gl_ccid.Segment27) =
nvl(l_segment(27), '-1')
and decode(l_segment(28), null, '-1', gl_ccid.Segment28) =
nvl(l_segment(28), '-1')
and decode(l_segment(29), null, '-1', gl_ccid.Segment29) =
nvl(l_segment(29), '-1')
and decode(l_segment(30), null, '-1', gl_ccid.Segment30) =
nvl(l_segment(30), '-1')
and chart_of_accounts_id = h_chart_of_accounts_id
and exists
(select 1
from fa_category_books cat
where cat.WIP_CLEARING_ACCOUNT_CCID =
gl_ccid.code_combination_id
and cat.book_type_code = px_mass_add_rec.book_type_code);
select category_id
into px_mass_add_rec.ASSET_CATEGORY_ID
from fa_category_books
where WIP_CLEARING_ACCOUNT_CCID = l_clearing_acct_ccid
and book_type_code = px_mass_add_rec.book_type_code
and rownum = 1;
Select MASS_ADDITION_ID,
ASSET_NUMBER,
TAG_NUMBER,
DESCRIPTION,
ASSET_CATEGORY_ID,
MANUFACTURER_NAME,
SERIAL_NUMBER,
MODEL_NUMBER,
BOOK_TYPE_CODE,
DATE_PLACED_IN_SERVICE,
FIXED_ASSETS_COST,
PAYABLES_UNITS,
FIXED_ASSETS_UNITS,
PAYABLES_CODE_COMBINATION_ID,
EXPENSE_CODE_COMBINATION_ID,
LOCATION_ID,
ASSIGNED_TO,
FEEDER_SYSTEM_NAME,
CREATE_BATCH_DATE,
CREATE_BATCH_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
REVIEWER_COMMENTS,
INVOICE_NUMBER,
VENDOR_NUMBER,
PO_VENDOR_ID,
PO_NUMBER,
POSTING_STATUS,
QUEUE_NAME,
INVOICE_DATE,
INVOICE_CREATED_BY,
INVOICE_UPDATED_BY,
PAYABLES_COST,
INVOICE_ID,
PAYABLES_BATCH_NAME,
DEPRECIATE_FLAG,
PARENT_MASS_ADDITION_ID,
PARENT_ASSET_ID,
SPLIT_MERGED_CODE,
AP_DISTRIBUTION_LINE_NUMBER,
POST_BATCH_ID,
ADD_TO_ASSET_ID,
AMORTIZE_FLAG,
NEW_MASTER_FLAG,
ASSET_KEY_CCID,
ASSET_TYPE,
DEPRN_RESERVE,
YTD_DEPRN,
BEGINNING_NBV,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
SALVAGE_VALUE,
ACCOUNTING_DATE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY_CODE,
FULLY_RSVD_REVALS_COUNTER,
MERGE_INVOICE_NUMBER,
MERGE_VENDOR_NUMBER,
PRODUCTION_CAPACITY,
REVAL_AMORTIZATION_BASIS,
REVAL_RESERVE,
UNIT_OF_MEASURE,
UNREVALUED_COST,
YTD_REVAL_DEPRN_EXPENSE,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
MERGED_CODE,
SPLIT_CODE,
MERGE_PARENT_MASS_ADDITIONS_ID,
SPLIT_PARENT_MASS_ADDITIONS_ID,
PROJECT_ASSET_LINE_ID,
PROJECT_ID,
TASK_ID,
SUM_UNITS,
DIST_NAME,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
GLOBAL_ATTRIBUTE_CATEGORY,
CONTEXT,
INVENTORIAL,
SHORT_FISCAL_YEAR_FLAG,
CONVERSION_DATE,
ORIGINAL_DEPRN_START_DATE,
GROUP_ASSET_ID,
CUA_PARENT_HIERARCHY_ID,
UNITS_TO_ADJUST,
BONUS_YTD_DEPRN,
BONUS_DEPRN_RESERVE,
AMORTIZE_NBV_FLAG,
AMORTIZATION_START_DATE,
TRANSACTION_TYPE_CODE,
TRANSACTION_DATE,
WARRANTY_ID,
LEASE_ID,
LESSOR_ID,
PROPERTY_TYPE_CODE,
PROPERTY_1245_1250_CODE,
IN_USE_FLAG,
OWNED_LEASED,
NEW_USED,
ASSET_ID,
MATERIAL_INDICATOR_FLAG,
cast(multiset (select MASSADD_DIST_ID dist_id,
MASS_ADDITION_ID mass_add_id,
UNITS,
DEPRN_EXPENSE_CCID,
LOCATION_ID,
EMPLOYEE_ID
from FA_MASSADD_DISTRIBUTIONS mass_dist
where mass_dist.mass_addition_id =
mass_add.mass_addition_id) as
fa_mass_add_dist_tbl) dists
FROM fa_mass_additions mass_add
where posting_status in ('NEW', 'ON-HOLD', 'POST')
and book_type_code = l_book
and nvl(merged_code, '1') not in ('MC')
and (asset_key_ccid is null or asset_category_id is null);
l_debug_str := 'Calling update_mass_additions';
FA_MASSADD_PREPARE_PKG.update_mass_additions(l_mass_add_rec_tbl,
p_log_level_rec => p_log_level_rec) then
l_debug_str := 'update_mass_additions returned failure';
SELECT Mass_Addition_ID,
Asset_Key_CCID,
Asset_Category_ID,
book_type_code
FROM FA_Mass_Additions
WHERE Posting_Status IN ('NEW', 'ON HOLD', 'POST')
AND Asset_Category_ID IS NOT NULL
AND Asset_Key_CCID IS NOT NULL
and book_type_code = l_book_type_code
order by asset_category_id,
asset_key_ccid,
decode(merged_code, 'MP', 1, NULL, 2), -->Use an existing parent if any
fixed_assets_cost desc,
decode(posting_status, 'POST', 1, 'NEW', 2, 'ON HOLD', 3);
select mass_addition_id, asset_category_id, asset_key_ccid
from fa_mass_additions
where posting_status in ('NEW', 'ON HOLD', 'POSTED')
and mass_addition_id <> l_mass_add_id
and asset_category_id = l_asset_category_id
and asset_key_ccid = l_asset_key_ccid
and merged_code is null
and book_type_code = l_book_type_code;
update fa_mass_additions
set posting_status = 'MERGED',
MERGE_PARENT_MASS_ADDITIONS_ID = parent_rec.mass_addition_id,
merged_code = 'MC',
last_update_date = sysdate,
last_updated_by = -1,
last_update_login = -1
where mass_addition_id = child_rec.mass_addition_id;
update fa_mass_additions
set merged_code = 'MP',
last_update_date = sysdate,
last_updated_by = -1,
last_update_login = -1
where mass_addition_id = parent_rec.mass_addition_id;
select FLEXBUILDER_DEFAULTS_CCID
into l_default_ccid
from fa_book_controls
where book_type_code = px_mass_add_rec.book_type_code;
SELECT deprn_expense_account_ccid, asset_cost_account_ccid
INTO l_DEPRN_EXPENSE_ACCT_CCID, h_cost_acct_ccid
FROM fa_category_books
WHERE book_type_code = px_mass_add_rec.book_type_code
AND category_id = px_mass_add_rec.asset_category_id;
Select sob.chart_of_accounts_id
into h_chart_of_accounts_id
From fa_book_controls bc, gl_sets_of_books sob
Where sob.set_of_books_id = bc.set_of_books_id
And bc.book_type_code = px_mass_add_rec.book_type_code;
select lookup_code
from fa_lookups
where lookup_type = c_lookup_type
and enabled_flag = 'Y';
select location_id
into l_location_id
from fa_locations
where rownum = 1;
select Segment1,
Segment2,
Segment3,
Segment4,
Segment5,
Segment6,
Segment7,
Segment8,
Segment9,
Segment10,
Segment11,
Segment12,
Segment13,
Segment14,
Segment15,
Segment16,
Segment17,
Segment18,
Segment19,
Segment20,
Segment21,
Segment22,
Segment23,
Segment24,
Segment25,
Segment26,
Segment27,
Segment28,
Segment29,
Segment30,
enabled_flag
into l_segment(1),
l_segment(2),
l_segment(3),
l_segment(4),
l_segment(5),
l_segment(6),
l_segment(7),
l_segment(8),
l_segment(9),
l_segment(10),
l_segment(11),
l_segment(12),
l_segment(13),
l_segment(14),
l_segment(15),
l_segment(16),
l_segment(17),
l_segment(18),
l_segment(19),
l_segment(20),
l_segment(21),
l_segment(22),
l_segment(23),
l_segment(24),
l_segment(25),
l_segment(26),
l_segment(27),
l_segment(28),
l_segment(29),
l_segment(30),
l_gl_ccid_enabled_flag
from gl_code_combinations
where code_combination_id =
px_mass_add_rec.payables_code_combination_id;
l_loc_query := 'select max(location_id) from fa_locations fa_loc where fa_loc.' ||
l_loc_seg_name || '= ' ||
l_segment(to_number(substr(l_loc_seg_name, 8))) || '
and location_id in (select location_id
from fa_locations fa_loc2, gl_code_combinations gl_code
where fa_loc.segment1 = gl_code.' ||
loc_seg_clr_acct_map(1) ||
' and fa_loc.segment2 = gl_code.' ||
loc_seg_clr_acct_map(2) ||
' and fa_loc.segment3 = gl_code.' ||
loc_seg_clr_acct_map(3) ||
' and fa_loc.segment4 = gl_code.' ||
loc_seg_clr_acct_map(4) ||
' and fa_loc.segment5 = gl_code.' ||
loc_seg_clr_acct_map(5) ||
' and fa_loc.segment6 = gl_code.' ||
loc_seg_clr_acct_map(6) ||
' and fa_loc.segment7 = gl_code.' ||
loc_seg_clr_acct_map(7);
update fa_mass_additions
set location_id = px_mass_add_rec.location_id,
expense_code_combination_id = px_mass_add_rec.expensE_code_combination_id
where mass_addition_id = px_mass_add_rec.mass_addition_id;