[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION Delete_Zero_Rows(p_bookType IN VARCHAR2,
p_request_id IN NUMBER,
p_reptShrtName IN VARCHAR2)
RETURN BOOLEAN;
SELECT fiscal_year
FROM fa_deprn_periods
WHERE book_type_code = p_bookType
AND period_counter = p_period;
IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
p_retcode := 2;
IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
p_retcode := 2;
IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
p_retcode := 2;
IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
p_retcode := 2;
IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
p_retcode := 2;
IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
p_retcode := 2;
IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
p_retcode := 2;
IF NOT Delete_Zero_Rows(p_bookType,p_request_id,p_reptShrtName) THEN
p_retcode := 2;
SELECT
sc.Company_Name,
sc.Category_Flex_Structure,
sc.Location_Flex_Structure,
sc.asset_key_flex_structure,
bc.Accounting_Flex_Structure,
ct.fiscal_year_name,
sob.Currency_Code
FROM
fa_system_controls sc,
fa_book_controls bc,
gl_sets_of_books sob,
fa_calendar_types ct
WHERE
bc.Book_Type_Code = cp_bookType
AND sob.Set_Of_Books_ID = BC.Set_Of_Books_ID
AND bc.deprn_calendar = ct.calendar_type;
p_mode => 'SELECT',
p_qualifier => 'GL_BALANCING');
p_mode => 'SELECT',
p_qualifier => 'FA_COST_CTR');
p_mode => 'SELECT',
p_qualifier => 'GL_ACCOUNT');
p_mode => 'SELECT',
p_qualifier => 'BASED_CATEGORY');
p_mode => 'SELECT',
p_qualifier => 'MINOR_CATEGORY');
SELECT period_name
FROM fa_deprn_periods
WHERE Book_Type_Code = cp_bookType
AND period_counter = TO_NUMBER(cp_period);
l_select_statement VARCHAR2(15000);
l_select_statement := 'SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id asset_category_id,
cf.description category_description,
ad.asset_key_ccid asset_key_ccid,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(dd.cost,0)) Reval_Cost,
sum(decode(dd.period_counter,'||p_period||',nvl(dd.deprn_amount,0)-nvl(dd.deprn_adjustment_amount,0),0)) Period_Deprn,
sum(nvl(dd.ytd_deprn,0)) YTD_Deprn,
sum(nvl(dd.deprn_reserve,0)) Acc_Deprn_Normal,
0 Acc_Deprn_backlog,
sum(nvl(dd.deprn_reserve,0)) Acc_Deprn_Total
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_asset_history ah,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp
WHERE ad.asset_id = bk.asset_id
AND cf.category_id = ah.category_id
AND cb.category_id = ah.category_id
AND bk.book_type_code = :v_bookType
AND cf.category_id = ' || p_categoryId || '
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND cb.book_type_code = bk.book_type_code
AND dh.distribution_id = dd.distribution_id
AND dh.code_combination_id = cc.code_combination_id
AND dh.asset_id = ah.asset_id
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter =
(SELECT max(period_counter)
FROM fa_deprn_detail ids
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter )
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND bk.asset_id not in
(SELECT asset_id
FROM igi_iac_asset_balances
WHERE book_type_code = bk.book_type_code
AND asset_id = bk.asset_id)
GROUP BY bk.book_type_Code , ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
'ah.category_id,
cf.description,
ad.asset_key_ccid, ' ||
minor_cat_seg_no || ', ' ||
'bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct ' ||
' UNION
SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id asset_category_id ,
cf.description category_description,
ad.asset_key_ccid asset_key_ccid,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(( id.adjustment_cost + dd.cost), 0)) Reval_Cost,
sum(decode(id.period_counter,'||p_period||',nvl(id.Deprn_Period+ifd.Deprn_Period, 0),0)) Period_Deprn,
sum(decode(fd.fiscal_year,'||l_fiscal_year||',nvl(id.Deprn_YTD+ifd.deprn_ytd, 0),0)) YTD_Deprn,
sum(nvl(id.Deprn_Reserve + dd.deprn_Reserve, 0)) Acc_Deprn_Normal ,
sum(nvl(id.Deprn_Reserve_backlog, 0) ) Acc_Deprn_Backlog ,
sum(nvl(id.Deprn_Reserve+dd.deprn_reserve+id.deprn_Reserve_backlog, 0)) Acc_Deprn_Total
FROM fa_additions ad ,
fa_Books bk ,
fa_distribution_history dh,
fa_deprn_Detail dd ,
igi_iac_det_balances id,
igi_iac_fa_deprn ifd,
gl_code_combinations cc,
fa_categories cf,
fa_asset_history ah,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fd,
fa_deprn_periods fdp
WHERE ad.asset_id = bk.asset_id
AND cf.category_id = ah.category_id
AND cb.category_id = ah.category_id
AND bk.book_Type_code = :v_bookType1
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period1
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND dh.book_type_Code = bk.book_type_code
AND dh.book_type_code = dd.book_type_code
AND cb.book_type_Code = bk.book_type_code
AND cf.category_id = ' || p_categoryId || '
AND dh.asset_id = dd.asset_id
AND dh.distribution_id = dd.distribution_id
AND dh.asset_id = ah.asset_id
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter = (SELECT MAX(period_counter)
FROM fa_deprn_detail ids
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND ids.distribution_id = dd.distribution_id
AND period_counter <= fdp.period_counter )
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND dh.distribution_id = id.distribution_id
AND dh.code_Combination_id = cc.code_combination_id
AND id.adjustment_id = ifd.adjustment_id
AND id.distribution_id = ifd.distribution_id
AND id.period_counter = ifd.period_counter
AND id.adjustment_id = ( SELECT max(adjustment_id)
FROM igi_iac_transaction_headers it
WHERE it.asset_id = bk.asset_id
AND it.book_type_code = bk.book_type_Code
AND it.period_counter <= fdp.period_counter
AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
AND fd.period_counter = id.period_counter
AND fd.book_type_code = bk.book_type_code
GROUP BY bk.book_type_Code , ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
'ah.category_id,
cf.description,
ad.asset_key_ccid, ' ||
minor_cat_seg_no || ', ' ||
'bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct ';
OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
p_period, /* :v_period */
p_bookType, /* :v_bookType1 */
p_period; /* :v_period1 */
INSERT INTO igi_iac_asset_rep_itf (
request_id,
company_name,
book_type_code,
period,
fiscal_year_name,
major_category,
cost_center,
depreciation_method,
conc_asset_key,
conc_location,
--deprn_exp_acct,
--deprn_res_acct,
cost_acct,
--iac_reval_resv_acct,
balancing_segment,
--deprn_backlog_acct,
--gen_fund_acct,
--oper_exp_acct,
concat_category,
reval_cost,
minor_category,
deprn_period,
ytd_deprn,
deprn_resv,
deprn_backlog,
deprn_total,
functional_currency_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
p_request_id,
l_company_name,
l_book_code,
l_period_name,
l_fiscal_year_name,
l_fa_cat_seg1,
l_gl_code_seg2,
l_depreciation_method,
l_concat_asset_key,
l_concat_loc,
--l_gl_code_seg3,
--l_depreciation_reserve_account,
l_asset_cost_account,
--l_reval_rsv_acct,
l_gl_code_seg1,
--l_dep_backlog,
--l_gen_fund_acct,
--l_oper_exp_acct,
l_concat_cat,
l_reval_cost,
l_fa_cat_seg2,
l_deprn_period,
l_ytd_deprn,
l_deprn_resv,
l_deprn_backlog,
l_deprn_total,
l_currency_code,
l_user_id,
sysdate,
l_user_id,
sysdate,
p_login_id
);
l_select_statement VARCHAR2(15000);
l_select_statement := 'SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id asset_category_id,
cf.description category_description,
ad.asset_number asset_number,
ad.description asset_description,
ad.tag_number asset_tag,
ad.parent_asset_id parent_id,
ad.serial_number serial_number,
ad.asset_key_ccid asset_key_ccid,
bk.life_in_months life_in_months,
bk.date_placed_in_service date_placed_in_service,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(dd.cost,0)) Reval_Cost,
sum(decode(dd.period_counter,'||p_period||',nvl(dd.deprn_amount,0)-nvl(dd.deprn_adjustment_amount,0),0)) Period_Deprn,
sum(nvl(dd.ytd_deprn,0)) YTD_Deprn,
sum(nvl(dd.deprn_reserve,0)) Acc_Deprn_Normal,
0 Acc_Deprn_backlog,
sum(nvl(dd.deprn_reserve,0)) Acc_Deprn_Total
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_asset_history ah,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp
WHERE ad.asset_id = bk.asset_id
AND cf.category_id = ah.category_id
AND cb.category_id = ah.category_id
AND bk.book_type_code = :v_bookType
AND NOT EXISTS
(SELECT 1
FROM igi_iac_det_balances db
WHERE db.book_type_code = bk.book_type_code
AND db.asset_id = bk.asset_id)
AND fdp.period_counter = :v_period
AND fdp.book_type_code = bk.book_type_code
AND cf.category_id = ' || p_categoryId || '
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND cb.book_type_code = bk.book_type_code
AND dh.book_type_code = bk.book_type_code
AND dh.asset_id = bk.asset_id
AND dh.distribution_id = dd.distribution_id
AND dh.code_combination_id = cc.code_combination_id
AND ah.asset_id = bk.asset_id
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter =
(SELECT max(period_counter)
FROM fa_deprn_detail ids
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter )
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc, ' || cost_ctr_seg_no || ' )
AND nvl( :v_to_cc,' || cost_ctr_seg_no || ')
AND ad.asset_number between nvl( :v_from_asset, ad.asset_number)
AND nvl( :v_to_asset, ad.asset_number)
GROUP BY bk.book_type_Code , ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
'ah.category_id,
cf.description, ' ||
minor_cat_seg_no || ', ' ||
'ad.asset_number,
ad.description,
ad.tag_number ,
ad.parent_asset_id ,
ad.serial_number ,
ad.asset_key_ccid ,
bk.life_in_months ,
bk.date_placed_in_service ,
bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct ' ||
' UNION
SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id asset_category_id ,
cf.description category_description,
ad.asset_number asset_number ,
ad.description asset_description,
ad.tag_number asset_tag,
ad.parent_asset_id parent_id,
ad.serial_number serial_number,
ad.asset_key_ccid asset_key_ccid,
bk.life_in_months life_in_months,
bk.date_placed_in_service date_placed_in_service,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum (nvl((id.adjustment_cost + dd.cost), 0)) Reval_Cost,
sum(nvl(decode(id.period_counter,'||p_period||',id.Deprn_Period+ifd.Deprn_Period,0), 0)) Period_Deprn,
sum(nvl(decode(fd.fiscal_year,'||l_fiscal_year||',id.Deprn_YTD+ifd.deprn_ytd, 0),0)) YTD_Deprn,
sum(nvl(id.Deprn_Reserve + dd.deprn_Reserve, 0)) Acc_Deprn_Normal ,
sum(nvl(id.Deprn_Reserve_backlog, 0) ) Acc_Deprn_Backlog ,
sum(nvl(id.Deprn_Reserve+dd.deprn_reserve+id.deprn_Reserve_backlog, 0)) Acc_Deprn_Total
FROM fa_additions ad ,
fa_Books bk ,
fa_distribution_history dh,
fa_deprn_Detail dd ,
igi_iac_det_balances id,
igi_iac_fa_deprn ifd,
gl_code_combinations cc,
fa_categories cf,
fa_asset_history ah,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fd,
fa_deprn_periods fdp
WHERE ad.asset_id = bk.asset_id
AND bk.transaction_header_id_out IS NULL
AND cf.category_id = ah.category_id
AND cb.category_id = ah.category_id
AND bk.book_Type_code = :v_bookType1
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period1
AND dh.book_type_Code = bk.book_type_code
AND dd.book_type_code = bk.book_type_code
AND cb.book_type_Code = bk.book_type_code
AND cf.category_id = ' || p_categoryId || '
AND dd.asset_id = bk.asset_id
AND dh.distribution_id = dd.distribution_id
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND dh.asset_id = bk.asset_id
AND dh.asset_id = ah.asset_id
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter = (SELECT MAX(period_counter)
FROM fa_deprn_detail ids
WHERE ids.asset_id = bk.asset_id
AND ids.book_type_code = bk.book_type_code
AND ids.distribution_id = dd.distribution_id
AND ids.period_counter <= fdp.period_counter )
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND id.distribution_id = dd.distribution_id
AND dh.code_Combination_id = cc.code_combination_id
AND id.adjustment_id = ifd.adjustment_id
AND id.distribution_id = ifd.distribution_id
AND id.period_counter = ifd.period_counter
AND id.adjustment_id = ( SELECT max(adjustment_id)
FROM igi_iac_transaction_headers it
WHERE it.asset_id = bk.asset_id
AND it.book_type_code = bk.book_type_Code
AND it.period_counter <= fdp.period_counter
AND it.adjustment_status not in( ''PREVIEW'', ''OBSOLETE''))
AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc1, ' || cost_ctr_seg_no || ' )
AND nvl( :v_to_cc1,' || cost_ctr_seg_no || ')
AND ad.asset_number between nvl( :v_from_asset1, ad.asset_number)
AND nvl( :v_to_asset1, ad.asset_number)
AND fd.period_counter = id.period_counter
AND fd.book_type_code = bk.book_type_code
GROUP BY bk.book_type_Code , ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
'ah.category_id,
cf.description, ' ||
minor_cat_seg_no || ', ' ||
'ad.asset_number,
ad.description,
ad.tag_number ,
ad.parent_asset_id ,
ad.serial_number ,
ad.asset_key_ccid,
bk.life_in_months ,
bk.date_placed_in_service ,
bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct';
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** After l_select ** ');
OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
p_period, /* :v_period */
l_from_cc, /* :v_from_cc */
l_to_cc, /* :v_to_cc, */
l_from_asset, /* :v_from_asset */
l_to_asset, /* :v_to_asset */
p_bookType, /* :v_bookType1 */
p_period, /* :v_period1 */
l_from_cc, /* :v_from_cc1 */
l_to_cc, /* :v_to_cc1 */
l_from_asset, /* :v_from_asset1 */
l_to_asset; /* :v_to_asset1 */
INSERT INTO igi_iac_asset_rep_itf (
request_id,
company_name,
book_type_code,
period,
fiscal_year_name,
major_category,
cost_center,
asset_number,
asset_description,
asset_tag,
parent_no,
serial_no,
life_months,
stl_rate,
dpis,
depreciation_method,
conc_asset_key,
conc_location,
--deprn_exp_acct,
--deprn_res_acct,
cost_acct,
--iac_reval_resv_acct,
balancing_segment,
--deprn_backlog_acct,
--gen_fund_acct,
--oper_exp_acct,
concat_category,
reval_cost,
minor_category,
deprn_period,
ytd_deprn,
deprn_resv,
deprn_backlog,
deprn_total,
functional_currency_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
p_request_id,
l_company_name,
l_book_code,
l_period_name,
l_fiscal_year_name,
l_fa_cat_seg1,
l_gl_code_seg2,
l_asset_number,
l_ADDescription,
l_asset_tag,
l_parent_no,
l_serial_number,
l_life_in_months,
l_stl_rate,
l_date_placed_in_service,
l_depreciation_method,
l_concat_asset_key,
l_concat_loc,
--l_gl_code_seg3,
--l_depreciation_reserve_account,
l_asset_cost_account,
--l_reval_rsv_acct,
l_gl_code_seg1,
--l_dep_backlog,
--l_gen_fund_acct,
--l_oper_exp_acct,
l_concat_cat,
l_reval_cost,
l_fa_cat_seg2,
l_deprn_period,
l_ytd_deprn,
l_deprn_resv,
l_deprn_backlog,
l_deprn_total,
l_currency_code,
l_user_id,
sysdate,
l_user_id,
sysdate,
p_login_id
);
l_select_statement VARCHAR2(15000);
l_select_statement := 'SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id category_id,
cf.description category_description,
ad.asset_key_ccid asset_key_ccid,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(dd.cost,0)) Reval_Cost,
0 Oper_Acct_Cost,
0 Oper_Acct_Backlog,
0 Oper_Acct_Net
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_type_code = :v_bookType
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND cb.book_type_code = bk.book_type_code
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND dh.code_combination_id = cc.code_combination_id
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter =
(SELECT max(period_counter)
FROM fa_deprn_summary
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter )
AND bk.asset_id not in
(SELECT asset_id
FROM igi_iac_asset_balances
WHERE book_type_code = bk.book_type_code
AND asset_id = bk.asset_id)
GROUP BY ' || minor_cat_seg_no || ', ' ||
'bk.book_type_Code ,
ah.category_id,
cf.description ,
ad.asset_key_ccid , ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
'bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct ' ||
' UNION
SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id category_id,
cf.description category_description,
ad.asset_key_ccid asset_key_ccid,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum (nvl((id.adjustment_cost + dd.cost), 0)) Reval_Cost,
sum(nvl(id.operating_acct_cost * -1,0)) Oper_Acct_Cost,
sum(nvl(id.operating_acct_backlog * -1 ,0)) Oper_Acct_Backlog,
sum(nvl(id.operating_acct_net * -1 ,0)) Oper_Acct_Net
FROM fa_additions ad ,
fa_Books bk ,
fa_distribution_history dh,
fa_deprn_Detail dd ,
igi_iac_det_balances id ,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND ad.asset_id =dh.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_Type_code = :v_bookType1
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period1
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND dh.book_type_Code = bk.book_type_code
AND dh.book_type_code = dd.book_type_code
AND cb.book_type_Code = bk.book_type_code
AND dh.asset_id = dd.asset_id
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter = (SELECT MAX(period_counter)
FROM fa_deprn_summary
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter )
AND dh.distribution_id = id.distribution_id
AND dh.code_Combination_id = cc.code_combination_id
AND id.adjustment_id =
( SELECT max(adjustment_id)
FROM igi_iac_transaction_headers it
WHERE it.asset_id = bk.asset_id
AND it.book_type_code = bk.book_type_Code
AND period_counter <= fdp.period_counter
AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
GROUP BY ' || minor_cat_seg_no || ', ' ||
'bk.book_type_Code ,
ah.category_id,
cf.description ,
ad.asset_key_ccid,
bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct, ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after select ');
OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
p_period, /* :v_period */
p_bookType, /* :v_bookType1 */
p_period; /* :v_period1 */
INSERT INTO igi_iac_asset_rep_itf (
request_id,
company_name,
book_type_code,
period,
fiscal_year_name,
major_category,
cost_center,
depreciation_method,
conc_asset_key,
conc_location,
--deprn_exp_acct,
--deprn_res_acct,
cost_acct,
--iac_reval_resv_acct,
balancing_segment,
--deprn_backlog_acct,
--gen_fund_acct,
--oper_exp_acct,
concat_category,
reval_cost,
minor_category,
oper_exp,
oper_exp_backlog,
oper_exp_net,
functional_currency_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
p_request_id,
l_company_name,
l_book_code,
l_period_name,
l_fiscal_year_name,
l_fa_cat_seg1,
l_gl_code_seg2,
l_depreciation_method,
l_concat_asset_key,
l_concat_loc,
--l_gl_code_seg3,
--l_depreciation_reserve_account,
l_asset_cost_account,
--l_reval_rsv_acct,
l_gl_code_seg1,
--l_dep_backlog,
--l_gen_fund_acct,
--l_oper_exp_acct,
l_concat_cat,
l_reval_cost,
l_fa_cat_seg2,
l_oper_exp,
l_oper_exp_backlog,
l_oper_exp_net,
l_currency_code,
l_user_id,
sysdate,
l_user_id,
sysdate,
p_login_id
);
l_select_statement VARCHAR2(15000);
l_select_statement := 'SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id category_id,
cf.description category_description,
ad.asset_number asset_number,
ad.description asset_description,
ad.tag_number asset_tag,
ad.parent_asset_id parent_id,
ad.serial_number serial_number,
ad.asset_key_ccid asset_key_ccid,
bk.life_in_months life_in_months,
bk.date_placed_in_service date_placed_in_service,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(dd.cost,0)) Reval_Cost,
0 Oper_Acct_Cost,
0 Oper_Acct_Backlog,
0 Oper_Acct_Net
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_type_code = :v_bookType
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND cb.book_type_code = bk.book_type_code
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND dh.code_combination_id = cc.code_combination_id
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter =
(SELECT max(period_counter)
FROM fa_deprn_summary
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter )
AND bk.asset_id not in
(SELECT asset_id
FROM igi_iac_asset_balances
WHERE book_type_code = bk.book_type_code
AND asset_id = bk.asset_id)
AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc, ' || cost_ctr_seg_no || ' )
AND nvl( :v_to_cc,' || cost_ctr_seg_no || ')
AND ad.asset_number between nvl( :v_from_asset, ad.asset_number)
AND nvl( :v_to_asset, ad.asset_number)
GROUP BY ad.asset_number ,
ad.description , ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code ,
ah.category_id,
cf.description , ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
'ad.tag_number ,
ad.parent_asset_id ,
ad.serial_number ,
ad.asset_key_ccid ,
bk.life_in_months ,
bk.date_placed_in_service ,
bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct ' ||
' UNION
SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id category_id,
cf.description category_description,
ad.asset_number asset_number,
ad.description asset_description,
ad.tag_number asset_tag,
ad.parent_asset_id parent_id,
ad.serial_number serial_number,
ad.asset_key_ccid asset_key_ccid,
bk.life_in_months life_in_months,
bk.date_placed_in_service date_placed_in_service,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum (nvl( id.adjustment_cost,0) + dd.cost) Reval_Cost,
sum(nvl(id.operating_acct_cost * -1 ,0)) Oper_Acct_Cost,
sum(nvl(id.operating_acct_backlog * -1,0)) Oper_Acct_Backlog,
sum(nvl(id.operating_acct_net * -1 ,0)) Oper_Acct_Net
FROM fa_additions ad ,
fa_Books bk ,
fa_distribution_history dh,
fa_deprn_Detail dd ,
igi_iac_det_balances id ,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND ad.asset_id = id.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_Type_code = :v_bookType1
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period1
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND dh.book_type_Code = bk.book_type_code
AND dh.book_type_code = dd.book_type_code
AND cb.book_type_Code = bk.book_type_code
AND dh.asset_id = dd.asset_id
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter = (SELECT MAX(period_counter)
FROM fa_deprn_summary
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter )
AND dh.distribution_id = id.distribution_id
AND dh.code_Combination_id = cc.code_combination_id
AND id.adjustment_id =
( SELECT max(adjustment_id)
FROM igi_iac_transaction_headers it
WHERE it.asset_id = bk.asset_id
AND it.book_type_code = bk.book_type_Code
AND period_counter <= fdp.period_counter
AND it.adjustment_status not in ( ''PREVIEW'', ''OBSOLETE''))
AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc1, ' || cost_ctr_seg_no || ' )
AND nvl( :v_to_cc1,' || cost_ctr_seg_no || ')
AND ad.asset_number between nvl( :v_from_asset1, ad.asset_number)
AND nvl( :v_to_asset1, ad.asset_number)
GROUP BY ad.asset_number ,
ad.description , ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code ,
ah.category_id,
cf.description ,
ad.tag_number ,
ad.parent_asset_id ,
ad.serial_number ,
ad.asset_key_ccid ,
bk.life_in_months ,
bk.date_placed_in_service ,
bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct, ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'after select ');
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** After l_select ** ');
OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
p_period, /* :v_period */
l_from_cc, /* :v_from_cc */
l_to_cc, /* :v_to_cc, */
l_from_asset, /* :v_from_asset */
l_to_asset, /* :v_to_asset */
p_bookType, /* :v_bookType1 */
p_period, /* :v_period1 */
l_from_cc, /* :v_from_cc1 */
l_to_cc, /* :v_to_cc1 */
l_from_asset, /* :v_from_asset1 */
l_to_asset; /* :v_to_asset1 */
INSERT INTO igi_iac_asset_rep_itf (
request_id,
company_name,
book_type_code,
period,
fiscal_year_name,
major_category,
cost_center,
asset_number,
asset_description,
asset_tag,
parent_no,
serial_no,
life_months,
stl_rate,
dpis,
depreciation_method,
conc_asset_key,
conc_location,
--deprn_exp_acct,
--deprn_res_acct,
cost_acct,
--iac_reval_resv_acct,
balancing_segment,
--deprn_backlog_acct,
--gen_fund_acct,
--oper_exp_acct,
concat_category,
reval_cost,
minor_category,
oper_exp,
oper_exp_backlog,
oper_exp_net,
functional_currency_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
p_request_id,
l_company_name,
l_book_code,
l_period_name,
l_fiscal_year_name,
l_fa_cat_seg1,
l_gl_code_seg2,
l_asset_number,
l_ADDescription,
l_asset_tag,
l_parent_no,
l_serial_number,
l_life_in_months,
l_stl_rate,
l_date_placed_in_service,
l_depreciation_method,
l_concat_asset_key,
l_concat_loc,
--l_gl_code_seg3,
--l_depreciation_reserve_account,
l_asset_cost_account,
--l_reval_rsv_acct,
l_gl_code_seg1,
--l_dep_backlog,
--l_gen_fund_acct,
--l_oper_exp_acct,
l_concat_cat,
l_reval_cost,
l_fa_cat_seg2,
l_oper_exp,
l_oper_exp_backlog,
l_oper_exp_net,
l_currency_code,
l_user_id,
sysdate,
l_user_id,
sysdate,
p_login_id
);
l_select_statement VARCHAR2(15000);
l_select_statement := 'SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id category_id,
cf.description category_description,
ad.asset_key_ccid asset_key_ccid,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(dd.cost,0)) Reval_Cost,
0 Reval_Reserve_Cost,
0 Reval_Reserve_Backlog,
0 Reval_Reserve_Gen_Fund,
0 Reval_Reserve_Net
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_type_code = :v_bookType
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND cb.book_type_code = bk.book_type_code
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND dh.code_combination_id = cc.code_combination_id
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter =
(SELECT max(period_counter)
FROM fa_deprn_summary
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter)
AND bk.asset_id not in
(SELECT asset_id
FROM igi_iac_asset_balances
WHERE book_type_code = bk.book_type_code
AND asset_id = bk.asset_id)
GROUP BY ' || balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
'bk.book_type_code,
ah.category_id,
cf.description,
ad.asset_key_ccid, ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct ' ||
' UNION
SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code ,
ah.category_id category_id,
cf.description category_description,
ad.asset_key_ccid asset_key_ccid,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum (nvl((id.adjustment_cost + dd.cost), 0)) Reval_Cost,
sum(nvl(id.reval_reserve_cost, 0) ) Reval_Reserve_Cost,
sum(nvl(id.reval_reserve_backlog, 0) ) Reval_Reserve_Backlog,
sum(nvl(id.reval_reserve_gen_fund, 0)) Reval_Reserve_Gen_Fund,
sum(nvl(id.reval_reserve_net, 0) ) Reval_Reserve_Net
FROM fa_additions ad ,
fa_Books bk ,
fa_distribution_history dh ,
fa_deprn_Detail dd ,
igi_iac_det_balances id ,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = dh.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_Type_code = :v_bookType1
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period1
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND bk.asset_id = ad.asset_id
AND dh.book_type_Code = bk.book_type_code
AND dh.book_type_code = dd.book_type_code
AND cb.book_type_Code = bk.book_type_code
AND dh.asset_id = dd.asset_id
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter = (SELECT MAX(period_counter)
FROM fa_deprn_summary
WHERE asset_id =bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter )
AND dh.distribution_id = id.distribution_id
AND dh.code_Combination_id = cc.code_combination_id
AND id.adjustment_id = ( SELECT max(adjustment_id)
FROM igi_iac_transaction_headers it
WHERE it.asset_id = bk.asset_id
AND it.book_type_code = bk.book_type_Code
AND it.period_counter <= fdp.period_counter
AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
GROUP BY ' || balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
'bk.book_type_Code ,
ah.category_id,
cf.description,
ad.asset_key_ccid, ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct';
OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
p_period, /* :v_period */
p_bookType, /* :v_bookType1 */
p_period; /* :v_period1 */
INSERT INTO igi_iac_asset_rep_itf (
request_id,
company_name,
book_type_code,
period,
fiscal_year_name,
major_category,
cost_center,
depreciation_method,
conc_asset_key,
conc_location,
--deprn_exp_acct,
--deprn_res_acct,
cost_acct,
--iac_reval_resv_acct,
balancing_segment,
--deprn_backlog_acct,
--gen_fund_acct,
--oper_exp_acct,
concat_category,
reval_cost,
minor_category,
reval_resv_cost,
reval_resv_blog,
reval_resv_gen_fund,
reval_resv_net,
functional_currency_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
p_request_id,
l_company_name,
l_book_code,
l_period_name,
l_fiscal_year_name,
l_fa_cat_seg1,
l_gl_code_seg2,
l_depreciation_method,
l_concat_asset_key,
l_concat_loc,
--l_gl_code_seg3,
--l_depreciation_reserve_account,
l_asset_cost_account,
--l_reval_rsv_acct,
l_gl_code_seg1,
--l_dep_backlog,
--l_gen_fund_acct,
--l_oper_exp_acct,
l_concat_cat,
l_reval_cost,
l_fa_cat_seg2,
l_reval_resv_cost,
l_reval_resv_blog,
l_reval_resv_gen_fund,
l_reval_resv_net,
l_currency_code,
l_user_id,
sysdate,
l_user_id,
sysdate,
p_login_id
);
l_select_statement VARCHAR2(15000);
l_select_statement := 'SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id category_id,
cf.description category_description,
ad.asset_number asset_number,
ad.description asset_description,
ad.tag_number asset_tag,
ad.parent_asset_id parent_id,
ad.serial_number serial_number,
ad.asset_key_ccid asset_key_ccid,
bk.life_in_months life_in_months,
bk.date_placed_in_service date_placed_in_service,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(dd.cost,0)) Reval_Cost,
0 Reval_Reserve_Cost,
0 Reval_Reserve_Backlog,
0 Reval_Reserve_Gen_Fund,
0 Reval_Reserve_Net
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_type_code = :v_bookType
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND cb.book_type_code = bk.book_type_code
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND dh.code_combination_id = cc.code_combination_id
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter =
(SELECT max(period_counter)
FROM fa_deprn_summary
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter)
AND bk.asset_id not in
(SELECT asset_id
FROM igi_iac_asset_balances
WHERE book_type_code = bk.book_type_code
AND asset_id = bk.asset_id)
AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc, ' || cost_ctr_seg_no || ' )
AND nvl( :v_to_cc,' || cost_ctr_seg_no || ')
AND ad.asset_number between nvl( :v_from_asset, ad.asset_number)
AND nvl( :v_to_asset, ad.asset_number)
GROUP BY ad.asset_number,
ad.description , ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
'bk.book_type_code,
ah.category_id,
cf.description, ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'ad.tag_number ,
ad.parent_asset_id ,
ad.serial_number ,
ad.asset_key_ccid ,
bk.life_in_months ,
bk.date_placed_in_service ,
bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct ' ||
' UNION
SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code ,
ah.category_id category_id,
cf.description category_description,
ad.asset_number asset_number ,
ad.description asset_description,
ad.tag_number asset_tag,
ad.parent_asset_id parent_id,
ad.serial_number serial_number,
ad.asset_key_ccid asset_key_ccid,
bk.life_in_months life_in_months,
bk.date_placed_in_service date_placed_in_service,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum (nvl((id.adjustment_cost + dd.cost), 0)) Reval_Cost,
sum(nvl(id.reval_reserve_cost, 0) ) Reval_Reserve_Cost,
sum(nvl(id.reval_reserve_backlog, 0) ) Reval_Reserve_Backlog,
sum(nvl(id.reval_reserve_gen_fund, 0)) Reval_Reserve_Gen_Fund,
sum(nvl(id.reval_reserve_net, 0) ) Reval_Reserve_Net
FROM fa_additions ad ,
fa_Books bk ,
fa_distribution_history dh ,
fa_deprn_Detail dd ,
igi_iac_det_balances id ,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = dh.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_Type_code = :v_bookType1
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period1
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND bk.asset_id = ad.asset_id
AND dh.book_type_Code = bk.book_type_code
AND dh.book_type_code = dd.book_type_code
AND cb.book_type_Code = bk.book_type_code
AND dh.asset_id = dd.asset_id
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter = (SELECT MAX(period_counter)
FROM fa_deprn_summary
WHERE asset_id =bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter )
AND dh.distribution_id = id.distribution_id
AND dh.code_Combination_id = cc.code_combination_id
AND id.adjustment_id = ( SELECT max(adjustment_id)
FROM igi_iac_transaction_headers it
WHERE it.asset_id = bk.asset_id
AND it.book_type_code = bk.book_type_Code
AND it.period_counter <= fdp.period_counter
AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc1, ' || cost_ctr_seg_no || ' )
AND nvl( :v_to_cc1,' || cost_ctr_seg_no || ')
AND ad.asset_number between nvl( :v_from_asset1, ad.asset_number)
AND nvl( :v_to_asset1, ad.asset_number)
GROUP BY ad.asset_number ,
ad.description , ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
'bk.book_type_Code ,
ah.category_id,
cf.description, ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'ad.tag_number ,
ad.parent_asset_id ,
ad.serial_number ,
ad.asset_key_ccid ,
bk.life_in_months ,
bk.date_placed_in_service ,
bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct' ;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** After l_select ** ');
OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
p_period, /* :v_period */
l_from_cc, /* :v_from_cc */
l_to_cc, /* :v_to_cc, */
l_from_asset, /* :v_from_asset */
l_to_asset, /* :v_to_asset */
p_bookType, /* :v_bookType1 */
p_period, /* :v_period1 */
l_from_cc, /* :v_from_cc1 */
l_to_cc, /* :v_to_cc1 */
l_from_asset, /* :v_from_asset1 */
l_to_asset; /* :v_to_asset1 */
INSERT INTO igi_iac_asset_rep_itf (
request_id,
company_name,
book_type_code,
period,
fiscal_year_name,
major_category,
cost_center,
asset_number,
asset_description,
asset_tag,
parent_no,
serial_no,
life_months,
stl_rate,
dpis,
depreciation_method,
conc_asset_key,
conc_location,
--deprn_exp_acct,
--deprn_res_acct,
cost_acct,
--iac_reval_resv_acct,
balancing_segment,
--deprn_backlog_acct,
--gen_fund_acct,
--oper_exp_acct,
concat_category,
reval_cost,
minor_category,
reval_resv_cost,
reval_resv_blog,
reval_resv_gen_fund,
reval_resv_net,
functional_currency_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
p_request_id,
l_company_name,
l_book_code,
l_period_name,
l_fiscal_year_name,
l_fa_cat_seg1,
l_gl_code_seg2,
l_asset_number,
l_ADDescription,
l_asset_tag,
l_parent_no,
l_serial_number,
l_life_in_months,
l_stl_rate,
l_date_placed_in_service,
l_depreciation_method,
l_concat_asset_key,
l_concat_loc,
--l_gl_code_seg3,
--l_depreciation_reserve_account,
l_asset_cost_account,
--l_reval_rsv_acct,
l_gl_code_seg1,
--l_dep_backlog,
--l_gen_fund_acct,
--l_oper_exp_acct,
l_concat_cat,
l_reval_cost,
l_fa_cat_seg2,
l_reval_resv_cost,
l_reval_resv_blog,
l_reval_resv_gen_fund,
l_reval_resv_net,
l_currency_code,
l_user_id,
sysdate,
l_user_id,
sysdate,
p_login_id
);
l_select_statement VARCHAR2(15000);
l_select_statement := 'SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id category_id,
cf.description category_description,
ad.asset_key_ccid asset_key_ccid,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(dd.cost,0)) Reval_Cost,
0 Reval_Reserve,
0 Gen_Fund,
0 Operating_Acct,
sum(nvl(dd.deprn_reserve,0)) Acct_Deprn,
0 Backlog
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_type_code = :v_bookType
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND cb.book_type_code = bk.book_type_code
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND dh.code_combination_id = cc.code_combination_id
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter =
(SELECT max(period_counter)
FROM fa_deprn_summary
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter)
AND bk.asset_id not in
(SELECT asset_id
FROM igi_iac_asset_balances
WHERE book_type_code = bk.book_type_code
AND asset_id = bk.asset_id)
GROUP BY ' || balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
'bk.book_type_code,
ah.category_id,
cf.description,
ad.asset_key_ccid, ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct ' ||
' UNION
SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code ,
ah.category_id category_id,
cf.description category_description,
ad.asset_key_ccid asset_key_ccid,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(( (id.adjustment_cost) + dd.cost), 0)) Reval_Cost,
sum(nvl(id.reval_reserve_net, 0) ) Reval_Reserve,
sum(nvl(id.reval_reserve_gen_fund, 0)) Gen_Fund,
sum(nvl(id.operating_acct_net * -1, 0)) Operating_Acct,
sum(nvl(id.deprn_reserve + dd.deprn_reserve, 0) ) Acct_Deprn,
sum(nvl( id.deprn_reserve_backlog , 0)) Backlog
FROM fa_additions ad ,
fa_Books bk ,
fa_distribution_history dh,
fa_deprn_Detail dd ,
igi_iac_det_balances id ,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = dh.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_Type_code = :v_bookType1
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period1
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND bk.asset_id = ad.asset_id
AND dh.book_type_Code = bk.book_type_code
AND dh.book_type_code = dd.book_type_code
AND cb.book_type_Code = bk.book_type_code
AND dh.asset_id = dd.asset_id
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter = (SELECT MAX(period_counter)
FROM fa_deprn_summary
WHERE asset_id =bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter )
AND dh.distribution_id = id.distribution_id
AND dh.code_Combination_id = cc.code_combination_id
AND id.adjustment_id = ( SELECT max(adjustment_id)
FROM igi_iac_transaction_headers it
WHERE it.asset_id = bk.asset_id
AND it.book_type_code = bk.book_type_Code
AND it.period_counter <= fdp.period_counter
AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
GROUP BY ' || balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
'bk.book_type_Code ,
ah.category_id,
cf.description,
ad.asset_key_ccid, ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct';
OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
p_period, /* :v_period */
p_bookType, /* :v_bookType1 */
p_period; /* :v_period1 */
INSERT INTO igi_iac_asset_rep_itf (
request_id,
company_name,
book_type_code,
period,
fiscal_year_name,
major_category,
cost_center,
depreciation_method,
conc_asset_key,
conc_location,
--deprn_exp_acct,
--deprn_res_acct,
cost_acct,
--iac_reval_resv_acct,
balancing_segment,
--deprn_backlog_acct,
--gen_fund_acct,
--oper_exp_acct,
concat_category,
reval_cost,
minor_category,
reval_reserve,
general_fund,
oper_acct,
deprn_resv,
backlog,
functional_currency_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
p_request_id,
l_company_name,
l_book_code,
l_period_name,
l_fiscal_year_name,
l_fa_cat_seg1,
l_gl_code_seg2,
l_depreciation_method,
l_concat_asset_key,
l_concat_loc,
--l_gl_code_seg3,
--l_depreciation_reserve_account,
l_asset_cost_account,
--l_reval_rsv_acct,
l_gl_code_seg1,
--l_dep_backlog,
--l_gen_fund_acct,
--l_oper_exp_acct,
l_concat_cat,
l_reval_cost,
l_fa_cat_seg2,
l_reval_reserve,
l_general_fund,
l_oper_acct,
l_deprn_resv,
l_backlog,
l_currency_code,
l_user_id,
sysdate,
l_user_id,
sysdate,
p_login_id
);
l_select_statement VARCHAR2(15000);
l_select_statement := 'SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code,
ah.category_id category_id,
cf.description category_description,
ad.asset_number asset_number,
ad.description asset_description,
ad.tag_number asset_tag,
ad.parent_asset_id parent_id,
ad.serial_number serial_number,
ad.asset_key_ccid asset_key_ccid,
bk.life_in_months life_in_months,
bk.date_placed_in_service date_placed_in_service,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(dd.cost,0)) Reval_Cost,
0 Reval_Reserve,
0 Gen_Fund,
0 Operating_Acct,
sum(nvl(dd.deprn_reserve,0)) Acct_Deprn,
0 Backlog
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_type_code = :v_bookType
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND cb.book_type_code = bk.book_type_code
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND dh.code_combination_id = cc.code_combination_id
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter =
(SELECT max(period_counter)
FROM fa_deprn_summary
WHERE asset_id = bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter)
AND bk.asset_id not in
(SELECT asset_id
FROM igi_iac_asset_balances
WHERE book_type_code = bk.book_type_code
AND asset_id = bk.asset_id)
AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc , ' || cost_ctr_seg_no || ' )
AND nvl( :v_to_cc ,' || cost_ctr_seg_no || ')
AND ad.asset_number between nvl( :v_from_asset , ad.asset_number) AND nvl( :v_to_asset , ad.asset_number)
GROUP BY ad.asset_number,
ad.description , ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
'bk.book_type_code,
ah.category_id,
cf.description, ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'ad.tag_number ,
ad.parent_asset_id ,
ad.serial_number ,
ad.asset_key_ccid,
bk.life_in_months ,
bk.date_placed_in_service ,
bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct ' ||
' UNION
SELECT ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'bk.book_type_Code book_type_code ,
ah.category_id category_id,
cf.description category_description,
ad.asset_number asset_number ,
ad.description asset_description,
ad.tag_number asset_tag,
ad.parent_asset_id parent_id,
ad.serial_number serial_number,
ad.asset_key_ccid asset_key_ccid,
bk.life_in_months life_in_months,
bk.date_placed_in_service date_placed_in_service,
bk.deprn_method_code depreciation_method,
dh.location_id location_id,
cb.deprn_reserve_acct depreciation_reserve_account,
cb.asset_cost_acct asset_cost_account,
sum(nvl(( id.adjustment_cost + dd.cost), 0)) Reval_Cost,
sum(nvl(id.reval_reserve_net, 0) ) Reval_Reserve,
sum(nvl(id.reval_reserve_gen_fund, 0)) Gen_Fund,
sum(nvl(id.operating_acct_net * -1, 0)) Operating_Acct,
sum(nvl(id.deprn_reserve + dd.deprn_reserve, 0) ) Acct_Deprn,
sum(nvl( id.deprn_reserve_backlog , 0)) Backlog
FROM fa_additions ad ,
fa_Books bk ,
fa_distribution_history dh,
fa_deprn_Detail dd ,
igi_iac_det_balances id ,
gl_code_combinations cc,
fa_categories cf,
fa_category_books cb,
fa_book_controls fb,
fa_deprn_periods fdp,
fa_asset_history ah
WHERE ad.asset_id = dh.asset_id
AND ah.asset_id = bk.asset_id
AND cf.category_id=ah.category_id
AND cb.category_id = ah.category_id
AND cf.category_id = ' || p_categoryId || '
AND bk.book_Type_code = :v_bookType1
AND fdp.book_type_code = bk.book_type_code
AND fdp.period_counter = :v_period1
AND nvl(bk.period_counter_fully_retired,fdp.period_counter+1) > fdp.period_counter
AND bk.transaction_header_id_in = (SELECT max(ifb.transaction_header_id_in)
FROM fa_books ifb
WHERE ifb.book_type_code = bk.book_type_code
AND ifb.asset_id = bk.asset_id
AND ifb.date_effective < nvl(fdp.period_close_date,sysdate))
AND bk.asset_id = ad.asset_id
AND dh.book_type_Code = bk.book_type_code
AND dh.book_type_code = dd.book_type_code
AND cb.book_type_Code = bk.book_type_code
AND dh.asset_id = dd.asset_id
AND dh.distribution_id = dd.distribution_id
AND nvl(dh.date_ineffective,sysdate) >= nvl(fdp.period_close_date,sysdate)
AND dh.transaction_header_id_in >= ah.transaction_header_id_in
AND dh.transaction_header_id_in < nvl(ah.transaction_header_id_out,dh.transaction_header_id_in+1)
AND fb.book_type_code = bk.book_type_code
AND dd.period_counter = (SELECT MAX(period_counter)
FROM fa_deprn_summary
WHERE asset_id =bk.asset_id
AND book_type_code = bk.book_type_code
AND period_counter <= fdp.period_counter )
AND dh.distribution_id = id.distribution_id
AND dh.code_Combination_id = cc.code_combination_id
AND id.adjustment_id = ( SELECT max(adjustment_id)
FROM igi_iac_transaction_headers it
WHERE it.asset_id = bk.asset_id
AND it.book_type_code = bk.book_type_Code
AND it.period_counter <= fdp.period_counter
AND adjustment_status not in (''PREVIEW'', ''OBSOLETE''))
AND ' || cost_ctr_seg_no || ' between nvl( :v_from_cc1 , ' || cost_ctr_seg_no || ' )
AND nvl( :v_to_cc1 ,' || cost_ctr_seg_no || ')
AND ad.asset_number between nvl( :v_from_asset1 , ad.asset_number) AND nvl( :v_to_asset1 , ad.asset_number)
GROUP BY ad.asset_number ,
ad.description , ' ||
balancing_seg_no || ', ' ||
cost_ctr_seg_no || ', ' ||
account_seg_no || ', ' ||
'bk.book_type_Code ,
ah.category_id,
cf.description, ' ||
major_cat_seg_no || ', ' ||
minor_cat_seg_no || ', ' ||
'ad.tag_number ,
ad.parent_asset_id ,
ad.serial_number ,
ad.asset_key_ccid,
bk.life_in_months ,
bk.date_placed_in_service ,
bk.deprn_method_code ,
dh.location_id ,
cb.deprn_reserve_acct ,
cb.asset_cost_acct';
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' ** After l_select ** ');
OPEN ret_lines FOR l_select_statement USING p_bookType, /* :v_bookType */
p_period, /* :v_period */
l_from_cc, /* :v_from_cc */
l_to_cc, /* :v_to_cc, */
l_from_asset, /* :v_from_asset */
l_to_asset, /* :v_to_asset */
p_bookType, /* :v_bookType1 */
p_period, /* :v_period1 */
l_from_cc, /* :v_from_cc1 */
l_to_cc, /* :v_to_cc1 */
l_from_asset, /* :v_from_asset1 */
l_to_asset; /* :v_to_asset1 */
INSERT INTO igi_iac_asset_rep_itf (
request_id,
company_name,
book_type_code,
period,
fiscal_year_name,
major_category,
cost_center,
asset_number,
asset_description,
asset_tag,
parent_no,
serial_no,
life_months,
stl_rate,
dpis,
depreciation_method,
conc_asset_key,
conc_location,
--deprn_exp_acct,
--deprn_res_acct,
cost_acct,
--iac_reval_resv_acct,
balancing_segment,
--deprn_backlog_acct,
--gen_fund_acct,
--oper_exp_acct,
concat_category,
reval_cost,
minor_category,
reval_reserve,
general_fund,
oper_acct,
deprn_resv,
backlog,
functional_currency_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
p_request_id,
l_company_name,
l_book_code,
l_period_name,
l_fiscal_year_name,
l_fa_cat_seg1,
l_gl_code_seg2,
l_asset_number,
l_ADDescription,
l_asset_tag,
l_parent_no,
l_serial_number,
l_life_in_months,
l_stl_rate,
l_date_placed_in_service,
l_depreciation_method,
l_concat_asset_key,
l_concat_loc,
--l_gl_code_seg3,
--l_depreciation_reserve_account,
l_asset_cost_account,
--l_reval_rsv_acct,
l_gl_code_seg1,
--l_dep_backlog,
--l_gen_fund_acct,
--l_oper_exp_acct,
l_concat_cat,
l_reval_cost,
l_fa_cat_seg2,
l_reval_reserve,
l_general_fund,
l_oper_acct,
l_deprn_resv,
l_backlog,
l_currency_code,
l_user_id,
sysdate,
l_user_id,
sysdate,
p_login_id
);
FUNCTION Delete_Zero_Rows(p_bookType IN VARCHAR2,
p_request_id IN NUMBER,
p_reptShrtName IN VARCHAR2)
RETURN BOOLEAN IS
l_path VARCHAR2(150);
l_path := g_path||'Delete_Zero_Rows';
DELETE FROM igi_iac_asset_rep_itf
WHERE book_type_code = p_bookType
AND request_id = p_request_id
AND reval_cost = 0
AND deprn_period = 0
AND ytd_deprn = 0
AND deprn_resv = 0
AND deprn_backlog = 0
AND deprn_total = 0;
DELETE FROM igi_iac_asset_rep_itf
WHERE book_type_code = p_bookType
AND request_id = p_request_id
AND reval_cost = 0
AND oper_exp = 0
AND oper_exp_backlog = 0
AND oper_exp_net = 0;
DELETE FROM igi_iac_asset_rep_itf
WHERE book_type_code = p_bookType
AND request_id = p_request_id
AND reval_cost = 0
AND reval_resv_cost = 0
AND reval_resv_blog = 0
AND reval_resv_gen_fund = 0
AND reval_resv_net = 0;
DELETE FROM igi_iac_asset_rep_itf
WHERE book_type_code = p_bookType
AND request_id = p_request_id
AND reval_cost = 0
AND reval_reserve = 0
AND general_fund = 0
AND oper_acct = 0
AND deprn_resv = 0
AND backlog = 0;
igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deleted rows with all zero values');
igi_iac_debug_pkg.debug_other_string(g_unexp_level,l_path,'Exception within Delete_Zero_Rows : '|| sqlerrm);
END Delete_Zero_Rows;
SELECT fc.deprn_reserve_acct,
fc.deprn_expense_acct
FROM fa_category_books fc
WHERE fc.Book_Type_Code = cp_bookType
AND fc.Category_id = cp_categoryId;
SELECT cb.backlog_deprn_rsv_ccid,
cb.general_fund_ccid,
cb.operating_expense_ccid,
cb.reval_rsv_ccid
FROM igi_iac_category_books cb
WHERE cb.Book_Type_Code = cp_bookType
AND cb.Category_id = cp_categoryId;