[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FADPTX_INSERT
DESCRIPTION
This procedure insert the data to interim table:FA_DEPRN_TAX_REP_ITF.
===========================================================================*/
procedure fadptx_insert (
errbuf out nocopy varchar2,
retcode out nocopy number,
book in varchar2, /* Book type code */
year in number, /* Target Year */
state_from in varchar2, /* Print Location State from */
state_to in varchar2, /* Print Location State to */
tax_asset_type_seg in varchar2, /* Category Segment Number or Qualifier */
category_from in varchar2, /* Tax Asset Type Category From */
category_to in varchar2, /* Tax Asset Type Category To */
sale_code in varchar2, /* Ritirement type for reason code */
all_state in boolean, -- Obsolete this parameter
rounding in boolean, -- Round bug4919991
eval_nbv_round in varchar2, -- Bug 9145830
request_id in number, /* Request id */
login_id in number /* login id */
) is
v_MainCursor number;
l_select_sm varchar2(1000);
l_select_base varchar2(2500);
l_select_both varchar2(2500);
l_select_end varchar2(2500);
l_select_start varchar2(2500);
l_select_nbv varchar2(2500);
l_select_base_ba2 varchar2(2500);
CURSOR c_last_update
(
p_asset_id NUMBER,
p_book_type_code VARCHAR2,
p_state VARCHAR2,
p_year NUMBER
) IS
SELECT cost,
tax_asset_type,
units_assigned,
life
FROM FA_DEPRN_TAX_REP_NBVS dtn
WHERE dtn.asset_id = p_asset_id
AND dtn.book_type_code = p_book_type_code
AND dtn.state = p_state
AND dtn.year = p_year -1;
CURSOR c_nbv_update --bug#2661575 Removed parameter p_add_year
(
p_asset_id NUMBER,
p_book_type_code VARCHAR2,
p_state VARCHAR2,
p_year NUMBER
) IS
SELECT cost,
theoretical_nbv,
evaluated_nbv,
year,
units_assigned
FROM FA_DEPRN_TAX_REP_NBVS dtn
WHERE dtn.asset_id = p_asset_id
AND dtn.book_type_code = p_book_type_code
AND dtn.state = p_state
AND dtn.year = p_year;
SELECT sum(dtn.cost), -- bug#2661575
sum(dtn.evaluated_nbv),
sum(dtn.theoretical_nbv),
sum(dtn.units_assigned), -- bug#2661575
dtn.year
FROM FA_DEPRN_TAX_REP_NBVS dtn
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND year =
(SELECT MAX(year) FROM fa_deprn_tax_rep_nbvs dtn2
WHERE dtn2.asset_id = p_asset_id
AND dtn2.book_type_code = p_book_type_code
AND dtn2.year < p_year)
group by dtn.year;
select AH.UNITS,
bk.cost
from FA_ASSET_HISTORY AH,
FA_BOOKS BK
Where AH.ASSET_ID = p_asset_id
and AH.ASSET_ID = BK.ASSET_ID
and BK.BOOK_TYPE_CODE = p_book_type_code
and AH.TRANSACTION_HEADER_ID_IN =
(select max(AH.TRANSACTION_HEADER_ID_IN )
from FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH1,
FA_TRANSACTION_HEADERS TH2
where AH.ASSET_ID = p_asset_id
and AH.TRANSACTION_HEADER_ID_IN =TH1.TRANSACTION_HEADER_ID
and AH.TRANSACTION_HEADER_ID_OUT=TH2.TRANSACTION_HEADER_ID(+)
and TH1.TRANSACTION_DATE_ENTERED <= p_target_date
and nvl(TH2.TRANSACTION_DATE_ENTERED,p_target_date +1)
> p_target_date
)
and bk.TRANSACTION_HEADER_ID_IN =
(SELECT MAX(bk.TRANSACTION_HEADER_ID_IN)
from FA_BOOKS bk ,
FA_TRANSACTION_HEADERS TH1,
FA_TRANSACTION_HEADERS TH2
where bk.asset_id= p_asset_id
and bk.book_type_code= p_book_type_code
and BK.transaction_header_id_in = TH1.transaction_header_id
and bk.transaction_header_id_out= TH2.transaction_header_id (+)
and th1.transaction_date_entered <= p_target_date
and nvl(th2.transaction_date_entered,p_target_date+1) > p_target_date
);
l_calling_fn varchar2(50) :='fa_deprn_tax_rep_pkg.fadptx_insert';
SELECT MAX(bk.date_effective)
from FA_BOOKS bk ,
FA_TRANSACTION_HEADERS TH1,
FA_TRANSACTION_HEADERS TH2
where bk.asset_id= p_asset_id
and bk.book_type_code= p_book
and BK.transaction_header_id_in = TH1.transaction_header_id
and bk.transaction_header_id_out= TH2.transaction_header_id (+)
and th1.transaction_date_entered <= p_prior_date
and nvl(th2.transaction_date_entered,p_prior_date+1) > p_prior_date;
SELECT MAX(ah1.date_effective)
from FA_ASSET_HISTORY ah1 ,
FA_TRANSACTION_HEADERS TH1,
FA_TRANSACTION_HEADERS TH2
where ah1.asset_id= p_asset_id
and ah1.transaction_header_id_in = TH1.transaction_header_id
and ah1.transaction_header_id_out= TH2.transaction_header_id (+)
and th1.transaction_date_entered <= p_prior_date
and nvl(th2.transaction_date_entered, p_prior_date+1) > p_prior_date;
SELECT SUM(cost),asset_id
from FA_DEPRN_TAX_REP_NBVS
where book_type_code = p_book
and asset_id = p_asset_id
and year = p_year
group by asset_id;
SELECT MAX(bk.transaction_header_id_in)
from FA_BOOKS bk
where bk.asset_id= p_asset_id
and bk.book_type_code= p_book
and bk.date_effective = p_date_effective;
SELECT MAX(ah1.transaction_header_id_in)
from FA_ASSET_HISTORY ah1
where ah1.asset_id= p_asset_id
and ah1.date_effective = p_date_effective;
select ffv.flex_value
from fnd_flex_value_sets ffvs,
fnd_flex_values ffv,
fnd_flex_values_tl ffvt
where ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
and ffvs.flex_value_set_name = 'Vision FA State'
and ffvt.language = 'US'
and flex_value between p_state_from and p_state_to
and ffv.flex_value not in (select distinct state
from fa_deprn_Tax_rep_itf
where request_id = p_request_id)
and ffv.flex_value in (select fdta.code
from fa_deprn_tax_entities fdte,
fa_deprn_tax_Authorities fdta
where fdte.company_id = fdta.company_id -- Bug 8677658
and fdte.book_type_code = p_book)
order by ffv.flex_value;
fa_rx_util_pkg.debug ('*****START FA_DEPRN_TAX_REP_PKG.FADPTX_INSERT*****');
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Book: '||h_book);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Year: '||h_year);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****State from: '||state_from);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****State to :'||state_to);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Tax Asset Type Segment: '||h_tax_asset_type_segment);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Category low: '||h_category_from);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Category high: '||h_category_to);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Target date: '||h_target_date);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Prior date: '||h_prior_date);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Request id: '||h_request_id);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****login id: '||h_login_id);
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Round: Y');
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Round: N');
fa_rx_util_pkg.debug('fadptx_insert: ' || '*****Rounding of Evaluated NBV: '||eval_nbv_round); -- Bug 9145830
Select DISTRIBUTION_SOURCE_BOOK
Into h_corp_book
From FA_BOOK_CONTROLS
where BOOK_TYPE_CODE =h_book;
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Distribution Source Book : '||h_corp_book);
SELECT SOB.CURRENCY_CODE,
SC.COMPANY_NAME,
SC.CATEGORY_FLEX_STRUCTURE,
SC.LOCATION_FLEX_STRUCTURE
INTO h_currency_code,
h_company_name,
cat_flex_struct,
loc_flex_struct
FROM FA_SYSTEM_CONTROLS SC,
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;
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Currency Code: '||h_currency_code);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Category struct id: '||cat_flex_struct);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Location struct id: '||loc_flex_struct);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Company Name: '||h_company_name);
select meaning
into r_change_life_desc
from FA_LOOKUPS
where lookup_type = 'JP_REASON_TYPE'
and lookup_code = 'CHANGE LIFE';
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Precision: '||h_currency_code);
'SELECT', h_tax_asset_type_segment);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Tax Asset Type Category Segment: '||l_parm_minor);
'SELECT', 'LOC_STATE');
fa_rx_util_pkg.debug('fadptx_insert: ' || 'State Segment: '||l_parm_state);
l_select_sm :=
'Select
AD.ASSET_ID ASSET_ID,
BK.BOOK_TYPE_CODE BOOK_TYPE_CODE,
BK.COST SUM_COST,
'||l_parm_state||' STATE,
SUM(DH.UNITS_ASSIGNED) SUM_UNITS_ASSIGNED ';
l_select_base :=
'Select
AD.ASSET_ID ASSET_ID,
AD.ASSET_NUMBER ASSET_NUMBER,
AD.DESCRIPTION ASSET_DESCRIPTION,
AD.NEW_USED NEW_USED,
BK.BOOK_TYPE_CODE BOOK_TYPE_CODE,
'||l_parm_minor||' MINOR_CATEGORY,
substr('||l_parm_minor||',1,1) TAX_ASSET_TYPE,
SM.STATE STATE,
AH.UNITS UNITS,
SM.SUM_UNITS_ASSIGNED UNITS_ASSIGNED,
SM.SUM_COST COST,
BK.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
decode(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
''E'',''nls_calendar=''''Japanese Imperial''''''),
''M'',''1'',''T'',''2'',''S'',''3'',''H'',''4'',''0'') ERA_NAME_NUM,
to_number(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
''YY'',''nls_calendar=''''Japanese Imperial'''''')) ADD_ERA_YEAR,
decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'',to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))-1,
to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))) ADD_YEAR,
decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'',12,
to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''MM''))) ADD_MONTH,
to_number(translate(MTH.METHOD_CODE,''JP-DBYR'','' '')) LIFE,
BK.ADJUSTED_RATE ADJUSTED_RATE ';
l_select_base_ba2 :=
'Select
AD.ASSET_ID ASSET_ID,
AD.ASSET_NUMBER ASSET_NUMBER,
AD.DESCRIPTION ASSET_DESCRIPTION,
AD.NEW_USED NEW_USED,
BK.BOOK_TYPE_CODE BOOK_TYPE_CODE,
decode(NBV.MINOR_CATEGORY,NULL,decode(NBV.TAX_ASSET_TYPE,substr('||l_parm_minor||',1,1),'||l_parm_minor||',
nvl(NBV.TAX_ASSET_TYPE,'||l_parm_minor||'))
,NBV.MINOR_CATEGORY) MINOR_CATEGORY,
decode(NBV.MINOR_CATEGORY,NULL,nvl(NBV.TAX_ASSET_TYPE,substr('||l_parm_minor||',1,1)),
substr(NBV.MINOR_CATEGORY,1,1)) TAX_ASSET_TYPE,
decode(NBV.ASSET_ID,NULL,SM.STATE,NBV.STATE) STATE,
AH.UNITS UNITS,
decode(NBV.ASSET_ID,NULL,SM.SUM_UNITS_ASSIGNED,
NBV.UNITS_ASSIGNED) UNITS_ASSIGNED,
decode(NBV.ASSET_ID,NULL,SM.SUM_COST,
decode(nvl(BK.COST,0),0,:p_sum_nbvs_cost,SM.SUM_COST)) COST,
BK.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
decode(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
''E'',''nls_calendar=''''Japanese Imperial''''''),
''M'',''1'',''T'',''2'',''S'',''3'',''H'',''4'',''0'') ERA_NAME_NUM,
to_number(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
''YY'',''nls_calendar=''''Japanese Imperial'''''')) ADD_ERA_YEAR,
decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'',to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))-1,
to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))) ADD_YEAR,
decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'',12,
to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''MM''))) ADD_MONTH,
to_number(translate(MTH.METHOD_CODE,''JP-DBYR'','' '')) LIFE,
BK.ADJUSTED_RATE ADJUSTED_RATE,
NBV.ACTION_FLAG ACTION_FLAG ';
l_select_both :=
'Select BA1.ASSET_ID ASSET_ID,
BA1.ASSET_NUMBER ASSET_NUMBER,
BA1.ASSET_DESCRIPTION ASSET_DESCRIPTION,
BA1.NEW_USED NEW_USED,
BA1.BOOK_TYPE_CODE BOOK_TYPE_CODE,
BA1.MINOR_CATEGORY MINOR_CATEGORY,
BA1.TAX_ASSET_TYPE TAX_ASSET_TYPE,
BA1.STATE STATE,
decode(NBV1.ASSET_ID,NULL,0,BA2.UNITS) START_UNITS,
BA1.UNITS END_UNITS,
decode(NBV1.ASSET_ID,NULL,0,BA2.UNITS_ASSIGNED) START_UNITS_ASSIGNED,
BA1.UNITS_ASSIGNED END_UNITS_ASSIGNED,
decode(NBV1.ASSET_ID,NULL,0,BA2.COST) START_COST,
BA1.COST END_COST,
BA1.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
BA1.ERA_NAME_NUM ERA_NAME_NUM,
BA1.ADD_ERA_YEAR ADD_ERA_YEAR,
BA1.ADD_YEAR ADD_YEAR,
BA1.ADD_MONTH ADD_MONTH,
BA2.LIFE START_LIFE,
BA1.LIFE END_LIFE,
BA1.ADJUSTED_RATE ADJUSTED_RATE,
abs(BA1.UNITS_ASSIGNED - BA2.UNITS_ASSIGNED) ABS_UNITS'; -- Added for bug#2629893
l_select_end :=
'Select BA1.ASSET_ID ASSET_ID,
BA1.ASSET_NUMBER ASSET_NUMBER,
BA1.ASSET_DESCRIPTION ASSET_DESCRIPTION,
BA1.NEW_USED NEW_USED,
BA1.BOOK_TYPE_CODE BOOK_TYPE_CODE,
BA1.MINOR_CATEGORY MINOR_CATEGORY,
BA1.TAX_ASSET_TYPE TAX_ASSET_TYPE,
BA1.STATE STATE,
nvl(BA2.UNITS,0) START_UNITS,
nvl(BA1.UNITS,0) END_UNITS,
nvl(BA2.UNITS_ASSIGNED,0) START_UNITS_ASSIGNED,
nvl(BA1.UNITS_ASSIGNED,0) END_UNITS_ASSIGNED,
nvl(BA2.COST,0) START_COST,
nvl(BA1.COST,0) END_COST,
BA1.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
BA1.ERA_NAME_NUM ERA_NAME_NUM,
BA1.ADD_ERA_YEAR ADD_ERA_YEAR,
BA1.ADD_YEAR ADD_YEAR,
BA1.ADD_MONTH ADD_MONTH,
nvl(BA2.LIFE,to_number(null)) START_LIFE,
nvl(BA1.LIFE,to_number(null)) END_LIFE,
BA1.ADJUSTED_RATE ADJUSTED_RATE,
abs(nvl(BA1.UNITS_ASSIGNED,0) - nvl(BA2.UNITS_ASSIGNED,0)) ABS_UNITS,
BA2.ACTION_FLAG ACTION_FLAG
';
l_select_start :=
'Select decode(BA1.ASSET_ID,NULL,BA2.ASSET_ID,
BA1.ASSET_ID) ASSET_ID,
decode(BA1.ASSET_ID,NULL,BA2.ASSET_NUMBER,
BA1.ASSET_NUMBER) ASSET_NUMBER,
decode(BA1.ASSET_ID,NULL,BA2.ASSET_DESCRIPTION,
BA1.ASSET_DESCRIPTION) ASSET_DESCRIPTION,
decode(BA1.ASSET_ID,NULL,BA2.NEW_USED,
BA1.NEW_USED) NEW_USED,
decode(BA1.ASSET_ID,NULL,BA2.BOOK_TYPE_CODE,
BA1.BOOK_TYPE_CODE) BOOK_TYPE_CODE,
decode(BA1.ASSET_ID,NULL,BA2.MINOR_CATEGORY,
BA1.MINOR_CATEGORY) MINOR_CATEGORY,
decode(BA1.ASSET_ID,NULL,BA2.TAX_ASSET_TYPE,
BA1.TAX_ASSET_TYPE) TAX_ASSET_TYPE,
decode(BA1.ASSET_ID,NULL,BA2.STATE,BA1.STATE) STATE,
nvl(BA2.UNITS,0) START_UNITS,
nvl(BA1.UNITS,0) END_UNITS,
nvl(BA2.UNITS_ASSIGNED,0) START_UNITS_ASSIGNED,
nvl(BA1.UNITS_ASSIGNED,0) END_UNITS_ASSIGNED,
nvl(BA2.COST,0) START_COST,
nvl(BA1.COST,0) END_COST,
decode(BA1.ASSET_ID,NULL,BA2.DATE_PLACED_IN_SERVICE,
BA1.DATE_PLACED_IN_SERVICE) DATE_PLACED_IN_SERVICE,
decode(BA1.ASSET_ID,NULL,BA2.ERA_NAME_NUM,
BA1.ERA_NAME_NUM) ERA_NAME_NUM,
decode(BA1.ASSET_ID,NULL,BA2.ADD_ERA_YEAR,
BA1.ADD_ERA_YEAR) ADD_ERA_YEAR,
decode(BA1.ASSET_ID,NULL,BA2.ADD_YEAR,
BA1.ADD_YEAR) ADD_YEAR,
decode(BA1.ASSET_ID,NULL,BA2.ADD_MONTH,
BA1.ADD_MONTH) ADD_MONTH,
nvl(BA2.LIFE,to_number(null)) START_LIFE,
nvl(BA1.LIFE,to_number(null)) END_LIFE,
decode(BA1.ASSET_ID,NULL,BA2.ADJUSTED_RATE,
BA1.ADJUSTED_RATE) ADJUSTED_RATE,
abs(nvl(BA1.UNITS_ASSIGNED,0) - nvl(BA2.UNITS_ASSIGNED,0)) ABS_UNITS,
BA2.ACTION_FLAG ACTION_FLAG
';
and NOT EXISTS ( SELECT ''next_trx_ineffective''
FROM FA_DISTRIBUTION_HISTORY DH1,
FA_TRANSACTION_HEADERS THD3
WHERE DH1.BOOK_TYPE_CODE = :p_corp_book
and DH1.ASSET_ID = DH.ASSET_ID
and DH1.transaction_header_id_in=NVL (DH.transaction_header_id_out ,-111)
and NVL (DH1.transaction_header_id_out, -111) = THD3.transaction_header_id
and THD3.TRANSACTION_DATE_ENTERED <= :p_target_date )
and AD.ASSET_ID = :p_asset_id
and BK.BOOK_TYPE_CODE = :p_book
and THA1.TRANSACTION_DATE_ENTERED <= :p_target_date
and nvl(THA2.TRANSACTION_DATE_ENTERED,:p_target_date+1) > :p_target_date
and THB1.TRANSACTION_DATE_ENTERED <= :p_target_date
and nvl(THB2.TRANSACTION_DATE_ENTERED,:p_target_date+1) > :p_target_date
and THD1.TRANSACTION_DATE_ENTERED <= :p_target_date
and nvl(THD2.TRANSACTION_DATE_ENTERED,:p_target_date+1) > :p_target_date
and MTH.METHOD_CODE like :p_method_code
and MTH.RATE_SOURCE_RULE =''FLAT''
and MTH.CREATED_BY = 1 -- Added to avoid customized method use
and not (BK.period_counter_fully_retired is not null and BK.COST=0)
and '||l_parm_minor||' between :p_category_from and :p_category_to
AND bk.date_effective = :p_target_bk_date_effective
AND bk.transaction_header_id_in = :p_target_bk_thid_in -- Bug3859151
';
l_select_nbv :=
'Select
AD.ASSET_ID ASSET_ID,
AD.ASSET_NUMBER ASSET_NUMBER,
AD.DESCRIPTION ASSET_DESCRIPTION,
AD.NEW_USED NEW_USED,
BK.BOOK_TYPE_CODE BOOK_TYPE_CODE,
decode(NBV.MINOR_CATEGORY,NULL,decode(NBV.TAX_ASSET_TYPE,substr('||l_parm_minor||',1,1),'||l_parm_minor||',
nvl(NBV.TAX_ASSET_TYPE,'||l_parm_minor||'))
,NBV.MINOR_CATEGORY) MINOR_CATEGORY,
decode(NBV.MINOR_CATEGORY,NULL,nvl(NBV.TAX_ASSET_TYPE,substr('||l_parm_minor||',1,1)),
substr(NBV.MINOR_CATEGORY,1,1)) TAX_ASSET_TYPE,
NBV.STATE STATE,
AH.UNITS UNITS,
NBV.UNITS_ASSIGNED UNITS_ASSIGNED,
decode(nvl(BK.COST,0),0,:p_sum_nbvs_cost,BK.COST) COST, -- Bug3975288 Changed from BK.COST
BK.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
decode(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
''E'',''nls_calendar=''''Japanese Imperial''''''),
''M'',''1'',''T'',''2'',''S'',''3'',''H'',''4'',''0'') ERA_NAME_NUM,
to_number(to_char(decode (to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'', BK.DATE_PLACED_IN_SERVICE -1,BK.DATE_PLACED_IN_SERVICE),
''YY'',''nls_calendar=''''Japanese Imperial'''''')) ADD_ERA_YEAR,
decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'',to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))-1,
to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''YYYY''))) ADD_YEAR,
decode(to_char(BK.DATE_PLACED_IN_SERVICE,''MM-DD''),
''01-01'',12,
to_number(to_char(BK.DATE_PLACED_IN_SERVICE,''MM''))) ADD_MONTH,
to_number(translate(MTH.METHOD_CODE,''JP-DBYR'','' '')) LIFE,
BK.ADJUSTED_RATE ADJUSTED_RATE,
NBV.ACTION_FLAG ACTION_FLAG ';
l_parm_view :='('||l_select_sm||l_from_stmt||l_sm_where||l_group_by||') ';
Set Select Statement:
This query is at target date.
===========================================================================*/
l_base_from := l_from_stmt||','||l_parm_view||' SM ';
sql_base := l_select_base||l_base_from||l_base_where;
sql_base_ba2 := l_select_base_ba2||l_base_from||l_from_stmt_ba2||l_base_where||l_where_ba2;
sql_nbv := l_select_nbv||l_from_nbv||l_nbv_where;
l_select_end||' from ('||sql_base||') BA1,
('||replace(sql_base_ba2,':p_target_',':p_prior_')||'
UNION '||sql_nbv||') BA2
where BA1.ASSET_ID=BA2.ASSET_ID (+)
and BA1.BOOK_TYPE_CODE = BA2.BOOK_TYPE_CODE (+)
and BA1.TAX_ASSET_TYPE = BA2.TAX_ASSET_TYPE (+)
and BA1.STATE = BA2.STATE (+)
and not (BA1.COST=0 AND nvl(BA2.COST,-1)=0)
union '||l_select_start||' from ('||sql_base||') BA1,
('||replace(sql_base_ba2,':p_target_',':p_prior_')||'
UNION '||sql_nbv||') BA2
where BA1.ASSET_ID (+)=BA2.ASSET_ID
and BA1.BOOK_TYPE_CODE (+)= BA2.BOOK_TYPE_CODE
and BA1.TAX_ASSET_TYPE (+)= BA2.TAX_ASSET_TYPE
and BA1.STATE (+)= BA2.STATE
and not (nvl(BA1.COST,-1)=0 AND BA2.COST=0)
order by ASSET_ID, BOOK_TYPE_CODE,ABS_UNITS,STATE' -- Added ABS_UNITS for bug#2629893
-- ,k),256*k+1,256); -- bug#2434220
'Select distinct
BK.ASSET_ID
From FA_BOOKS BK,
FA_ADDITIONS AD,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_LOCATIONS LOC,
FA_CATEGORIES CAT,
FA_METHODS MTH,
FA_TRANSACTION_HEADERS TH1,
FA_TRANSACTION_HEADERS TH2,
FA_TRANSACTION_HEADERS TH_DH1,
FA_TRANSACTION_HEADERS TH_DH2,
FA_TRANSACTION_HEADERS TH_AH1,
FA_TRANSACTION_HEADERS TH_AH2
Where AD.ASSET_ID = BK.ASSET_ID
and BK.BOOK_TYPE_CODE =:p_book
and AD.ASSET_TYPE <> ''EXPENSED''
and AD.ASSET_ID = DH.ASSET_ID
and DH.BOOK_TYPE_CODE = :p_corp_book
and DH.LOCATION_ID = LOC.LOCATION_ID
and '||l_parm_state||' between :p_state_from and :p_state_to
and DH.TRANSACTION_HEADER_ID_IN = TH_DH1.TRANSACTION_HEADER_ID
and DH.TRANSACTION_HEADER_ID_OUT = TH_DH2.TRANSACTION_HEADER_ID(+)
and AD.ASSET_ID = AH.ASSET_ID
and AH.CATEGORY_ID = CAT.CATEGORY_ID
and AH.TRANSACTION_HEADER_ID_IN = TH_AH1.TRANSACTION_HEADER_ID
and AH.TRANSACTION_HEADER_ID_OUT = TH_AH2.TRANSACTION_HEADER_ID(+)
and '||l_parm_minor||' between :p_category_from and :p_category_to
and BK.DEPRN_METHOD_CODE = MTH.METHOD_CODE
and BK.TRANSACTION_HEADER_ID_IN =TH1.TRANSACTION_HEADER_ID
and BK.TRANSACTION_HEADER_ID_OUT=TH2.TRANSACTION_HEADER_ID(+)
and MTH.METHOD_CODE like :p_method_code
and MTH.RATE_SOURCE_RULE =''FLAT''
and MTH.CREATED_BY = 1 -- Added to avoid customized method use
Having min(TH1.TRANSACTION_DATE_ENTERED) <= :p_target_date
and max(nvl(TH2.TRANSACTION_DATE_ENTERED,:p_prior_date+1)) > :p_prior_date
and min(TH_DH1.TRANSACTION_DATE_ENTERED) <= :p_target_date
and max(nvl(TH_DH2.TRANSACTION_DATE_ENTERED, :p_prior_date+1)) > :p_prior_date
and min(TH_AH1.TRANSACTION_DATE_ENTERED) <= :p_target_date
and max(nvl(TH_AH2.TRANSACTION_DATE_ENTERED, :p_prior_date+1)) > :p_prior_date
group by BK.ASSET_ID
UNION
select distinct asset_id
from FA_DEPRN_TAX_REP_NBVS NBVS
where NBVS.BOOK_TYPE_CODE = :p_book
and NBVS.STATE between :p_state_from and :p_state_to
and NBVS.YEAR = :p_year - 1
';
fa_rx_util_pkg.debug('fadptx_insert: SUB SQL: '|| l_sub_sql);
fa_rx_util_pkg.debug('fadptx_insert: ***** OPEN CURSOR: v_SubCursor *****');
fa_rx_util_pkg.debug('fadptx_insert: v_SubCursor :'|| v_SubCursor);
fa_rx_util_pkg.debug('fadptx_insert: ***** BIND/DEFINE COLUMN: v_SubCursor *****');
fa_rx_util_pkg.debug('fadptx_insert: ***** EXECUTE: v_SubCursor *****');
fa_rx_util_pkg.debug('fadptx_insert: v_SubReturn :'|| v_SubReturn);
fa_rx_util_pkg.debug('fadptx_insert: ***** OPEN CURSOR: v_MainCursor *****');
fa_rx_util_pkg.debug('fadptx_insert: v_MainCursor :' || v_MainCursor);
fa_rx_util_pkg.debug('fadptx_insert: ***** DEFINE COLUMN: v_MainCursor *****');
fa_rx_util_pkg.debug('fadptx_insert: ***** FETCH LOW: v_SubCursor *****');
fa_rx_util_pkg.debug('fadptx_insert: v_SubFetch :'||v_SubFetch);
fa_rx_util_pkg.debug('fadptx_insert: l_asset_id : h_sum_nbvs_asset_id'||l_asset_id||':'||h_sum_nbvs_asset_id);
fa_rx_util_pkg.debug('fadptx_insert: h_prior_bk_date_effective :'||to_char(h_prior_bk_date_effective,'YYYY-MON-DD HH24:MI:SS'));
fa_rx_util_pkg.debug('fadptx_insert: h_prior_ah_date_effective :'||to_char(h_prior_ah_date_effective,'YYYY-MON-DD HH24:MI:SS'));
fa_rx_util_pkg.debug('fadptx_insert: h_target_bk_date_effective :'||to_Char(h_target_bk_date_effective,'YYYY-MON-DD HH24:MI:SS'));
fa_rx_util_pkg.debug('fadptx_insert: h_sum_nbvs_cost :'||h_sum_nbvs_cost);
fa_rx_util_pkg.debug('fadptx_insert: h_prior_bk_thid_in :'||h_prior_bk_thid_in);
fa_rx_util_pkg.debug('fadptx_insert: h_prior_ah_thid_in :'||h_prior_ah_thid_in);
fa_rx_util_pkg.debug('fadptx_insert: h_target_bk_thid_in :'||h_target_bk_thid_in);
fa_rx_util_pkg.debug('fadptx_insert: ***** EXECUTE: v_MainCursor *****');
fa_rx_util_pkg.debug('fadptx_insert: v_MainReturn :'||v_MainReturn);
fa_rx_util_pkg.debug('fadptx_insert: ***** FETCH LOW: v_MainCursor *****');
fa_rx_util_pkg.debug('fadptx_insert: v_MainFetch :'|| v_MainFetch);
fa_rx_util_pkg.debug('fadptx_insert: h_minor_category(tax asset type) '|| h_minor_category);
fa_rx_util_pkg.debug('fadptx_insert: h_minor_category(after get_description) '|| h_minor_category);
fa_rx_util_pkg.debug('fadptx_insert: **** Queried values by MainCursor ****');
fa_rx_util_pkg.debug('fadptx_insert: asset id: ' || h_asset_id);
fa_rx_util_pkg.debug('fadptx_insert: asset number:' || h_asset_number);
fa_rx_util_pkg.debug('fadptx_insert: asset desc:'|| h_asset_desc);
fa_rx_util_pkg.debug('fadptx_insert: new use:'||h_new_used);
fa_rx_util_pkg.debug('fadptx_insert: minor category:'||h_minor_category);
fa_rx_util_pkg.debug('fadptx_insert: tax asset type: '||h_tax_asset_type);
fa_rx_util_pkg.debug('fadptx_insert: state: '||h_state);
fa_rx_util_pkg.debug('fadptx_insert: start total units: '||h_start_units_total);
fa_rx_util_pkg.debug('fadptx_insert: end total units: '||h_end_units_total);
fa_rx_util_pkg.debug('fadptx_insert: start units assigned:'||h_start_units_assigned);
fa_rx_util_pkg.debug('fadptx_insert: end units assigned: '||h_end_units_assigned);
fa_rx_util_pkg.debug('fadptx_insert: start total cost:'||h_start_cost_total);
fa_rx_util_pkg.debug('fadptx_insert: end total cost: '||h_end_cost_total);
fa_rx_util_pkg.debug('fadptx_insert: date in service:' ||h_date_in_service);
fa_rx_util_pkg.debug('fadptx_insert: era name num: '||h_era_name_num);
fa_rx_util_pkg.debug('fadptx_insert: add era year: '||h_add_era_year);
fa_rx_util_pkg.debug('fadptx_insert: add year: '||h_add_year);
fa_rx_util_pkg.debug('fadptx_insert: add month: '||h_add_month);
fa_rx_util_pkg.debug('fadptx_insert: start life: '||h_start_life);
fa_rx_util_pkg.debug('fadptx_insert: end life: '||h_end_life);
fa_rx_util_pkg.debug('fadptx_insert: adjusted rate: '||h_adjusted_rate);
fa_rx_util_pkg.debug('fadptx_insert: h_abs_units: '||h_abs_units);
fa_rx_util_pkg.debug('fadptx_insert: action flag: '||h_action_flag);
fa_rx_util_pkg.debug('fadptx_insert: **** End of queried values list for '||h_asset_id||' ****');
select count(*),count(theoretical_nbv),count(evaluated_nbv)
into l_chk_nbv_total,l_chk_theoretical_nbv,l_chk_evaluated_nbv
from FA_DEPRN_TAX_REP_NBVS
where asset_id = l_asset_id
and book_type_code = h_book
and year = h_year -1;
delete from FA_DEPRN_TAX_REP_NBVS
where asset_id = l_asset_id
and book_type_code = h_book
and year = h_year;
fa_rx_util_pkg.debug('fadptx_insert: l_asset_id (before c_last_nbv_total):'||l_asset_id);
fa_rx_util_pkg.debug('fadptx_insert: h_book:'||h_book);
fa_rx_util_pkg.debug('fadptx_insert: h_year:'||h_year);
fa_rx_util_pkg.debug('fadptx_insert: c_last_nbv_total: return with values');
fa_rx_util_pkg.debug('fadptx_insert: dist_last_total_cost(0.0):'||dist_total_cost);
fa_rx_util_pkg.debug('fadptx_insert: dist_last_total_units(0.0):'||dist_last_total_units);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_evaluated_nbv(0.0):'||dist_total_evaluated_nbv);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_theoretical_nbv(0.0):'||dist_total_theoretical_nbv);
fa_rx_util_pkg.debug('fadptx_insert: dist_year(0.0):'||dist_year);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_cost(0):'||dist_total_cost);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_units(0):'||dist_total_units);
fa_rx_util_pkg.debug('fadptx_insert: dist_last_total_units(0):'||dist_last_total_units);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_evaluated_nbv(0):'||dist_total_evaluated_nbv);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_theoretical_nbv(0):'||dist_total_theoretical_nbv);
fa_rx_util_pkg.debug('fadptx_insert: dist_year:'||dist_year);
fa_rx_util_pkg.debug('fadptx_insert: l_total_prior_cost(0):'||l_total_prior_cost);
fa_rx_util_pkg.debug('fadptx_insert: l_total_prior_units(0):'||l_total_prior_units);
fa_rx_util_pkg.debug('fadptx_insert: l_total_cost(0):'||l_total_cost);
fa_rx_util_pkg.debug('fadptx_insert: l_total_units(0):'||l_total_units);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_units(0.5):'||dist_total_units);
OPEN c_nbv_update (h_asset_id,h_book,h_state, dist_year);
FETCH c_nbv_update INTO h_up_cost,h_up_theoretical_nbv,
h_up_evaluated_nbv,h_up_year, h_tmp_units_assigned;
fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_up_theoretical_nbv(0):'||h_up_theoretical_nbv);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_up_evaluated_nbv(0):'||h_up_evaluated_nbv);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_up_year(0):'||h_up_year);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_tmp_units_assigned(0):'||h_tmp_units_assigned);
fa_rx_util_pkg.debug('fadptx_insert: h_up_evaluated_nbv(0):'||h_up_evaluated_nbv);
fa_rx_util_pkg.debug('fadptx_insert: h_up_year(0):'||h_up_year);
fa_rx_util_pkg.debug('fadptx_insert: h_tmp_units_assigned(0):'||h_tmp_units_assigned);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_units(1):'||dist_total_units);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_theoretical_nbv(1):'||dist_total_theoretical_nbv);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_evaluated_nbv(1):'||dist_total_evaluated_nbv);
OPEN c_last_update (h_asset_id,h_book,h_state, h_year);
FETCH c_last_update INTO h_up_last_cost, h_up_tax_asset_type,h_up_units_assigned,h_up_life;
IF c_last_update%found THEN
h_last_up_flag :='Y';
CLOSE c_last_update;
fa_rx_util_pkg.debug('fadptx_insert: h_up_nbv_flag:'||h_up_nbv_flag);
fa_rx_util_pkg.debug('fadptx_insert: h_last_up_flag:'||h_last_up_flag);
fa_rx_util_pkg.debug('fadptx_insert: h_up_cost:'||h_up_cost);
fa_rx_util_pkg.debug('fadptx_insert: h_up_theoretical_nbv:'||h_up_theoretical_nbv);
fa_rx_util_pkg.debug('fadptx_insert: h_up_evaluated_nbv:'||h_up_evaluated_nbv);
fa_rx_util_pkg.debug('fadptx_insert: h_up_year:'||h_up_year);
fa_rx_util_pkg.debug('fadptx_insert: h_up_last_cost:'||h_up_last_cost);
fa_rx_util_pkg.debug('fadptx_insert: h_up_tax_asset_type:'||h_up_tax_asset_type);
fa_rx_util_pkg.debug('fadptx_insert: h_up_units_assigned:'||h_up_units_assigned);
fa_rx_util_pkg.debug('fadptx_insert: h_up_life:'||h_up_life);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_units:'||dist_total_units);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_theoretical_nbv:'||dist_total_theoretical_nbv);
fa_rx_util_pkg.debug('fadptx_insert: dist_total_evaluated_nbv:'||dist_total_evaluated_nbv);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_start_asset_id:'||h_start_asset_id);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'start units accm :'||h_start_units_accm);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'start cost accm :'||h_start_cost_accm);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'start cost: '||h_start_cost);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_end_asset_id:'||h_end_asset_id);
if h_last_up_flag ='Y' then -- If NBVs table has cost, update end cost
h_end_cost := h_up_last_cost;
fa_rx_util_pkg.debug('fadptx_insert: ' || 'end units accm:'||h_end_units_accm);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'end cost accm:'||h_end_cost_accm);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'end cost:'||h_end_cost);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'h_diff_year:'||h_diff_year);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'l_start_loop:'||l_start_loop);
CLOSE c_nbv_update;
fa_rx_util_pkg.debug('fadptx_insert: ' || 'theoretical nbv: '||h_theoretical_nbv);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'evaluated nbv: '||h_evaluated_nbv);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'theoretical residual rate: '||h_theoretical_residual_rate);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'evaluated residual rate: '||h_evaluated_residual_rate);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'overwritten start cost: '||h_start_cost);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'overwritten start asset type :'||h_start_asset_type);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'overwritten start units assigned :'||h_start_units_assigned);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'overwritten start life :'||h_start_life);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'State check h_state:'||h_state);
Select distinct 'Y'
into r_addition_flag
from FA_TRANSACTION_HEADERS
where ASSET_ID = h_asset_id
and BOOK_TYPE_CODE =h_book_type_code
and TRANSACTION_DATE_ENTERED >= h_prior_date
and TRANSACTION_DATE_ENTERED <= h_target_date
and (TRANSACTION_TYPE_CODE ='CIP ADDITION'
or (TRANSACTION_TYPE_CODE ='ADDITION'
and not exists
(Select * from FA_TRANSACTION_HEADERS
where ASSET_ID = h_asset_id
and BOOK_TYPE_CODE =h_book_type_code
and TRANSACTION_DATE_ENTERED < h_prior_date
and TRANSACTION_TYPE_CODE ='CIP ADDITION')));
Select TRANSACTION_HEADER_ID
into r_ret_id
from FA_TRANSACTION_HEADERS
where ASSET_ID = h_asset_id
and BOOK_TYPE_CODE = h_book_type_code
and TRANSACTION_HEADER_ID =
(select max(TRANSACTION_HEADER_ID)
from FA_TRANSACTION_HEADERS
where ASSET_ID = h_asset_id
and BOOK_TYPE_CODE= h_book_type_code
and TRANSACTION_DATE_ENTERED >= h_prior_date
and TRANSACTION_DATE_ENTERED <= h_target_date
and (TRANSACTION_TYPE_CODE ='FULL RETIREMENT'
or TRANSACTION_TYPE_CODE ='PARTIAL RETIREMENT'));
Select decode (to_char(TRANSACTION_DATE_ENTERED,'MM-DD'),
'01-01',to_char(TRANSACTION_DATE_ENTERED -1,'E YY.MM',
'NLS_CALENDAR=''Japanese Imperial'''),
to_char(TRANSACTION_DATE_ENTERED,'E YY.MM',
'NLS_CALENDAR=''Japanese Imperial''')),
TRANSACTION_NAME
into r_transfer_date,
r_trn_transaction_name
from FA_TRANSACTION_HEADERS
where ASSET_ID = h_asset_id
and BOOK_TYPE_CODE = h_corp_book
and TRANSACTION_HEADER_ID =
(select max(TRANSACTION_HEADER_ID)
from FA_TRANSACTION_HEADERS
where ASSET_ID = h_asset_id
and BOOK_TYPE_CODE = h_corp_book
and TRANSACTION_DATE_ENTERED >= h_prior_date
and TRANSACTION_DATE_ENTERED <= h_target_date
and TRANSACTION_TYPE_CODE ='TRANSFER');
l_transfer_sql := 'select ''N''
from FA_DISTRIBUTION_HISTORY FDH,
FA_LOCATIONS LOC
where FDH.ASSET_ID = '||h_asset_id ||'
and FDH.TRANSACTION_HEADER_ID_in in ( select TRANSACTION_HEADER_ID
from FA_TRANSACTION_HEADERS
where ASSET_ID = '|| h_asset_id ||'
and BOOK_TYPE_CODE = '''||h_corp_book ||'''
and TRANSACTION_DATE_ENTERED >= '''|| h_prior_date ||'''
and TRANSACTION_DATE_ENTERED <= '''|| h_target_date ||'''
and TRANSACTION_TYPE_CODE =''TRANSFER'')
and FDH.LOCATION_ID = LOC.LOCATION_ID
and '||l_parm_state||' <> '''||h_state ||'''';
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - addition flag:'||r_addition_flag);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - retirement flag:'||r_ret_flag);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - transfer flag:'||r_transfer_flag);
Select RETIREMENT_TYPE_CODE,
SOLD_TO
Into r_ret_type_code,
r_sold_to
From FA_RETIREMENTS
Where TRANSACTION_HEADER_ID_IN = r_ret_id;
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - Retirement type code:'||r_ret_type_code);
Select MEANING
into h_adddec_description
from FA_LOOKUPS
where LOOKUP_TYPE = 'RETIREMENT'
and LOOKUP_CODE = r_ret_type_code;
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - Addition/Decrease Flag:'||h_add_dec_flag);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason - ADD/DEC reason type:'||h_adddec_reason_type);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason- increase_cost:'||h_increase_cost);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason- decrease_cost:'||h_decrease_cost);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason- all reason type:'||h_all_reason_type);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason- all reason code:'||h_all_reason_code);
fa_rx_util_pkg.debug('fadptx_insert: ' || 'Reason- all description:'||h_all_description);
/* Insert end date's data to FA_DEPRN_TAX_REP_ITF */
IF (g_print_debug) THEN
fa_rx_util_pkg.debug ('fadptx_insert: ' || 'h_state_flag :'||h_state_flag);
Insert into FA_DEPRN_TAX_REP_ITF (
REQUEST_ID,
YEAR,
ASSET_ID,
ASSET_NUMBER,
ASSET_DESCRIPTION,
NEW_USED,
BOOK_TYPE_CODE,
MINOR_CATEGORY,
TAX_ASSET_TYPE,
MINOR_CAT_DESC,
STATE,
START_UNITS_ASSIGNED,
END_UNITS_ASSIGNED,
END_COST,
START_COST,
THEORETICAL_NBV,
EVALUATED_NBV,
DATE_PLACED_IN_SERVICE,
ERA_NAME_NUM,
ADD_ERA_YEAR,
ADD_MONTH,
START_LIFE,
END_LIFE,
THEORETICAL_RESIDUAL_RATE,
EVALUATED_RESIDUAL_RATE,
THEORETICAL_TAXABLE_COST,
EVALUATED_TAXABLE_COST,
ADJUSTED_RATE,
INCREASE_COST,
DECREASE_COST,
ALL_REASON_TYPE,
ALL_REASON_CODE,
ALL_DESCRIPTION,
ADDDEC_REASON_TYPE,
ADDDEC_REASON_CODE,
DEC_TYPE,
ADDDEC_DESCRIPTION,
ADD_DEC_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
FUNCTIONAL_CURRENCY_CODE,
ORGANIZATION_NAME
)
values (
h_request_id,
h_year,
h_asset_id,
h_asset_number,
h_asset_desc,
h_new_used,
h_book_type_code,
h_minor_category,
h_tax_asset_type,
h_minor_cat_desc,
h_state,
h_start_units_assigned,
h_end_units_assigned,
h_end_cost,
h_start_cost,
h_theoretical_nbv,
h_evaluated_nbv,
h_date_in_service,
h_era_name_num,
h_add_era_year,
h_add_month,
h_start_life,
h_end_life,
h_theoretical_residual_rate,
h_evaluated_residual_rate,
h_theoretical_taxable_cost,
h_evaluated_taxable_cost,
h_adjusted_rate,
h_increase_cost,
h_decrease_cost,
h_all_reason_type,
h_all_reason_code,
h_all_description,
h_adddec_reason_type,
h_adddec_reason_code,
h_dec_type,
h_adddec_description,
h_add_dec_flag,
h_login_id,
sysdate,
sysdate,
h_login_id,
h_login_id,
h_currency_code,
h_company_name
);
fa_rx_util_pkg.debug ('fadptx_insert: ' || 'Inserted - asset id:'||h_asset_id||', state: '||h_state||', asset type:'||h_tax_asset_type);
fa_rx_util_pkg.debug ('fadptx_insert: ' || 'Reject - asset id:'||h_asset_id||', state: '||h_state||', asset type:'||h_tax_asset_type);
SELECT FA_DEPRN_TAX_REP_NBVS_S.NEXTVAL
INTO h_deprn_tax_rep_nbv_id
FROM dual;
INSERT INTO FA_DEPRN_TAX_REP_NBVS
(
deprn_tax_rep_nbv_id,
asset_id,
book_type_code,
state,
year,
cost,
theoretical_nbv,
evaluated_nbv,
tax_asset_type,
units_assigned,
life,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
minor_category
)
VALUES
(
h_deprn_tax_rep_nbv_id,
h_asset_id,
h_book_type_code,
h_state,
h_year,
h_end_cost,
h_store_theoretical_nbv,
h_store_evaluated_nbv,
h_tax_asset_type,
h_end_units_assigned,
h_end_life,
h_login_id,
sysdate,
h_login_id,
sysdate,
h_login_id,
h_minor_category
);
fa_rx_util_pkg.debug ('fadptx_insert: ' || 'Inserted - asset id:'||h_asset_id||', state: '||h_state||' to NBV table.');
fa_rx_util_pkg.debug('fadptx_insert: ' || '***** End Loop for v_MainCursor *****');
fa_rx_util_pkg.debug('fadptx_insert: ' || '***** Close Cursor v_MainCursor *****');
fa_rx_util_pkg.debug('fadptx_insert: ' || '***** End Loop and Close Cursor v_SubCursor *****');
if the client would update exception of standard code,rate,taxable cost,
he create procedure FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE by himself
and can insert their date.
==================================================================== */
begin
v_ExtCursor := DBMS_SQL.OPEN_CURSOR;
FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE(
c_request_id => :h_request_id,
c_year => :h_year,
c_book_type_code => :h_book_type_code,
c_state => :h_state);
fa_rx_util_pkg.debug(''fadptx_insert: '' ||
''There is FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE'');
''Unhandled error in FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE'');
''Please check your custom procedure FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE'');
fa_rx_util_pkg.debug('fadptx_insert: ' || 'There is no FA_DEPRN_TAX_CUSTOM_PKG.FADPCUSTOM_UPDATE');
INSERT INTO FA_DEPRN_TAX_REP_ITF(
request_id,
year,
book_type_code,
state,
start_cost,
end_cost,
theoretical_nbv,
evaluated_nbv,
theoretical_taxable_cost,
evaluated_taxable_cost,
increase_cost,
decrease_cost,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
VALUES(
request_id,
year,
book,
c_missing_states_rec.flex_value,
0,
0,
0,
0,
0,
0,
0,
0,
login_id,
sysdate,
sysdate,
login_id,
login_id
);
fa_rx_util_pkg.debug ('*****END FA_DEPRN_TAX_REP_PKG.FADPTX_INSERT*****');
Insert into FA_DEPRN_TAX_REP_ITF (
REQUEST_ID,
YEAR,
ASSET_ID,
ASSET_NUMBER,
ASSET_DESCRIPTION,
NEW_USED,
BOOK_TYPE_CODE,
MINOR_CATEGORY,
TAX_ASSET_TYPE,
MINOR_CAT_DESC,
STATE,
START_UNITS_ASSIGNED,
END_UNITS_ASSIGNED,
END_COST,
START_COST,
THEORETICAL_NBV,
EVALUATED_NBV,
DATE_PLACED_IN_SERVICE,
ERA_NAME_NUM,
ADD_ERA_YEAR,
ADD_MONTH,
START_LIFE,
END_LIFE,
THEORETICAL_RESIDUAL_RATE,
EVALUATED_RESIDUAL_RATE,
THEORETICAL_TAXABLE_COST,
EVALUATED_TAXABLE_COST,
ADJUSTED_RATE,
INCREASE_COST,
DECREASE_COST,
ALL_REASON_TYPE,
ALL_REASON_CODE,
ALL_DESCRIPTION,
ADDDEC_REASON_TYPE,
ADDDEC_REASON_CODE,
DEC_TYPE,
ADDDEC_DESCRIPTION,
ADD_DEC_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
FUNCTIONAL_CURRENCY_CODE,
ORGANIZATION_NAME
)
SELECT
request_id,
year,
0,
NULL,
NULL,
NULL,
book,
TO_CHAR(l_tax_type_cntr),
TO_CHAR(l_tax_type_cntr),
l_oth_type_desc,
state_code_v(l_local_cntr),
0,
0,
0,
0,
0,
0,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
0,
0,
0,
0,
0,
0,
0,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
h_login_id,
sysdate,
sysdate,
h_login_id,
h_login_id,
NULL,
NULL
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM fa_deprn_tax_rep_itf
WHERE request_id=h_request_id
and year=h_year
and state=state_code_v(l_local_cntr)
and tax_asset_type=TO_CHAR(l_tax_type_cntr));
if c_nbv_update%ISOPEN then
CLOSE c_nbv_update;
end fadptx_insert;