FA Interco Tax Book Transfer Adjustment Report
WITH Allprd AS
(SELECT substr(a.Period_Name,1,4)||a.period_year period_name
FROM (SELECT To_Number(To_Char(To_Date(Gp.Period_Name, 'MON-YY'),
'YYYYMM')) Periods,
Gp.Period_Name,
Gp.START_DATE,
Gp.Period_year
FROM Gl_Periods Gp
WHERE Gp.Period_Set_Name = '4-4-5') a,
(SELECT To_Number(To_Char(To_Date(Gp.Period_Name, 'MON-YY'),
'YYYYMM')) Start_Period,
Gp.START_DATE
FROM Gl_Periods Gp
WHERE Gp.Period_Set_Name = '4-4-5'
AND UPPER(Period_Name) = :PSTART) Starting,
(SELECT To_Number(To_Char(To_Date(Gp.Period_Name, 'MON-YY'),
'YYYYMM')) End_Period,
Gp.END_DATE
FROM Gl_Periods Gp
WHERE Gp.Period_Set_Name = '4-4-5'
AND UPPER(Period_Name) = :PEND ) Ending
WHERE a.START_DATE BETWEEN Starting.START_DATE AND Ending.END_DATE
)
SELECT
faab.asset_number,
adnp.addn_fa_period,
to_char(adnp.addn_period_date,'mm/dd/yyyy') addn_period_date,
--faab.attribute_category_code CATEGORY,
Facb.segment1||'.'||Facb.segment2 CATEGORY,
fab_corp.book_type_code Corp_Book,
pl_gl.currency_code PL_Currency,
--fab_corp.original_cost Corp_COST,--infosys disabled to add below local currency
fmcb_corp.original_cost Corp_COST,
CASE
WHEN pl_gl.currency_code = 'USD' THEN
fab_corp.original_cost
WHEN substr(fab_corp.book_type_code, 1, 3) NOT IN ('JPN')/*(
'612',
'613',--- Infosys : corrected the book type code to match fusion data.
'624'
)*/ THEN
fmcb_corp.original_cost
ELSE
round((fab_corp.original_cost * rat.conversion_rate), 2)
END Corp_Cost_USD,
--- currencies will be in USD , this case statement first condition wil be always true.
to_char(fab_corp.date_placed_in_service,'mm/dd/yyyy') Corp_DPIS,
fab_enp.book_type_code ENP_Book,
--fab_enp.cost ENP_Cost,--infosys disabled to add below local currency
fmcb_enp.cost ENP_Cost,
CASE
WHEN pl_gl.currency_code = 'USD' THEN
fab_enp.cost
WHEN substr(fab_enp.book_type_code, 1, 3) NOT IN ('JPN')
/*(
'612',
'613',--- Infosys : corrected the book type code to match fusion data.
'624'
)*/ THEN
fmcb_enp.cost
ELSE
round((fab_enp.cost * rat.conversion_rate), 2)
END ENP_Cost_USD,
to_char(fab_enp.date_placed_in_service,'mm/dd/yyyy') ENP_DPIS,
fask.segment3 Asset_Key, --- Dff configuration changed in UAT
--fask.segment2 Asset_Key, --- Infosys fa_asset_keywords column changed to Segment2
fask.segment1 ||' - '|| faab.asset_number xfer_from_asset,
corp_init_ad.deprn_reserve corp_init_ad,
CASE
WHEN pl_gl.currency_code = 'USD' THEN
corp_init_ad.deprn_reserve
WHEN substr(fab_enp.book_type_code, 1, 3) NOT IN ('JPN')/*(
'612',
'613',--- Infosys : corrected the book type code to match fusion data.
'624'
)*/ THEN
corp_init_ad.deprn_reserve_usd
ELSE
round((corp_init_ad.deprn_reserve * rat.conversion_rate), 2)
END corp_init_ad_usd
FROM
fa_additions_b faab,
fa_books fab_corp,
fa_mc_books fmcb_corp,
fa_book_controls fbc_corp,
gl_ledgers pl_gl,
gl_daily_rates rat,
fa_asset_keywords fask,
fa_books fab_enp,
fa_mc_books fmcb_enp,
fa_categories_b facb,-- Infosys asset category name
Allprd,
(
SELECT
bks.asset_id,
bks.book_type_code,
dpr.period_name addn_fa_period,
dpr.calendar_period_open_date addn_period_date,
dpr.period_open_date addn_open_date,
nvl(dpr.period_close_date, sysdate + 1) addn_close_date
FROM
fa_books bks,
fa_deprn_periods dpr
WHERE
bks.book_type_code = dpr.book_type_code
AND bks.date_effective BETWEEN dpr.period_open_date AND nvl(dpr.period_close_date, sysdate + 1)
AND bks.transaction_header_id_in = (
SELECT
MIN(bks2.transaction_header_id_in)
FROM
fa_books bks2
WHERE
bks2.book_type_code = bks.book_type_code
AND bks2.asset_id = bks.asset_id
)
) adnp,
(
SELECT
bks.asset_id,
bks.book_type_code,
dpr.period_name addn_fa_period,
dpr.calendar_period_open_date addn_period_date,
dpr.period_open_date addn_open_date,
nvl(dpr.period_close_date, sysdate + 1) addn_close_date
FROM
fa_books bks,
fa_deprn_periods dpr
WHERE
bks.book_type_code = dpr.book_type_code
AND bks.date_effective BETWEEN dpr.period_open_date AND nvl(dpr.period_close_date, sysdate + 1)
AND bks.transaction_header_id_in = (
SELECT
MIN(bks2.transaction_header_id_in)
FROM
fa_books bks2
WHERE
bks2.book_type_code = bks.book_type_code
AND bks2.asset_id = bks.asset_id
)
) enp_adnp,
(
SELECT
fds_init.asset_id,
fds_init.book_type_code,
fds_init.deprn_reserve,
fmds_init.deprn_reserve deprn_reserve_usd
FROM
fa_deprn_summary fds_init,
fa_mc_deprn_summary fmds_init
WHERE
fds_init.deprn_source_code = 'BOOKS'
AND fmds_init.deprn_source_code (+) = 'BOOKS'
AND fds_init.asset_id = fmds_init.asset_id (+)
AND fds_init.book_type_code = fmds_init.book_type_code (+)
AND fds_init.period_counter = fmds_init.period_counter (+)
) corp_init_ad
WHERE
faab.asset_id = fab_corp.asset_id
AND fmcb_corp.transaction_header_id_in (+) = fab_corp.transaction_header_id_in
AND fab_corp.book_type_code = fbc_corp.book_type_code
AND fbc_corp.set_of_books_id = pl_gl.ledger_id
AND nvl(adnp.addn_close_date, sysdate + 1) BETWEEN fab_corp.date_effective AND nvl(fab_corp.date_ineffective, adnp.addn_close_date)
AND fbc_corp.book_class = 'CORPORATE'
AND rat.conversion_type = 'Corporate'
AND rat.to_currency = 'USD'
AND rat.from_currency = 'JPY'
AND rat.conversion_date = adnp.addn_period_date
AND faab.Asset_Category_Id=facb.category_ID(+)-- Infosys asset category name
AND faab.asset_id = fab_enp.asset_id
AND fmcb_enp.transaction_header_id_in (+) = fab_enp.transaction_header_id_in
AND fab_enp.book_type_code LIKE '%ENP%'
AND nvl(enp_adnp.addn_close_date, sysdate + 1) BETWEEN fab_enp.date_effective AND nvl(fab_enp.date_ineffective, enp_adnp.addn_close_date)
AND adnp.asset_id = fab_corp.asset_id
AND adnp.book_type_code = fab_corp.book_type_code
AND enp_adnp.asset_id = fab_enp.asset_id
AND enp_adnp.book_type_code = fab_enp.book_type_code
AND fab_corp.cost <> 0
AND UPPER(adnp.addn_fa_period) = UPPER(allprd.Period_Name)
AND adnp.addn_fa_period = enp_adnp.addn_fa_period
--AND fask.segment2 = 'ICXFR' --- Infosys fa_asset_keywords column changed to Segment2
AND fask.segment3 = 'ICXFR' --- Dff configuration changed in UAT
AND faab.asset_key_ccid = fask.code_combination_id (+)
AND fab_corp.asset_id = corp_init_ad.asset_id (+)
AND fab_corp.book_type_code = corp_init_ad.book_type_code (+)
AND substr(fab_corp.book_type_code,1,3) =
(CASE WHEN :CO IS NULL
THEN NVL(substr(fab_corp.book_type_code,1,3),'XX')
ELSE :CO END )
order by fab_corp.asset_id--
No comments:
Post a Comment