FA Pending Mass Additions Report
SELECT DISTINCT adn.mass_addition_id mass_addition_id,
adn.book_type_code book_name,
adn.asset_number asset_number,
adb.asset_number add_to_asset_number,
cat.segment1 fa_category,
cat.segment2 fa_sub_category,
adn.description asset_description,
adn.life_in_months life_months,
adn.queue_name queue,
adn.posting_status status,
adn.payables_units units,
adn.fixed_assets_cost cost_usd,
mcr.fixed_assets_cost cost,
adn.creation_date creation_date,
adn.date_placed_in_service placed_in_service,
--To_char(adn.date_placed_in_service, 'MM/DD/YYYY HH:MI:SS PM') placed_in_service,
adn.invoice_number invoice_number,
aia.doc_sequence_value doc_sequence,
aia.invoice_currency_code inv_cy_cd,
adn.invoice_line_number invoice_line,
adn.ap_distribution_line_number ap_dist_line,
-- to_char(TO_DATE(adn.invoice_date, 'YYYY-MM-DD'),'DD-mon-YYYY') invoice_date,
--to_char(adn.invoice_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=ENGLISH') invoice_date,
adn.invoice_date invoice_date,
vnd.vendor_name vendor_name,
vnd.segment1 vendor_number,
adn.po_number po_number,
cde.segment1 co,
cde.segment3 gac,
cde.segment4 lac,
cde.segment5 dept,
cde.segment7 ico,
cde.segment8 proj,
prj.description,
upper(fu2.person_id) deliver_to_person_id,
fu2.first_name || ' ' || fu2.last_name deliver_to_person,
-- To_char(adn.last_update_date, 'MM/DD/YYYY HH:MI:SS PM') last_updt_date,
adn.last_update_date last_updt_date,
(select display_name from per_person_names_f ppn
where Usr.person_id = ppn.person_id
and trunc(sysdate) between trunc(nvl(ppn.effective_start_date,trunc(sysdate))) and trunc(nvl(ppn.effective_end_date,trunc(sysdate)))
and nvl(ppn.name_type,'GLOBAL') = 'GLOBAL'
) last_updt_name,
CASE WHEN upper(adn.feeder_system_name)=upper('Oracle Manual') THEN 'MANUAL'
ELSE ADN.feeder_system_name
END source,
adn.payables_batch_name ap_batch_name,
-- To_char(rec.transaction_date, 'DD/MM/YYYY HH:MI:SS PM') Receipt_Date,
rec.transaction_date Receipt_Date,
rec.receipt_num Receipt_Number,
-- sysdate timestamp
sysdate timestamp
FROM gl_code_combinations cde,
poz_suppliers_v vnd,
FA_MASSADD_DISTRIBUTIONS fmd,
fa_mass_additions adn,
po_distributions_all pda,
(
SELECT person_id,
first_name,last_name
FROM per_person_names_f
WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date
AND name_type='GLOBAL') fu2,
fa_categories_b cat,
per_users usr,
fa_mc_mass_rates mcr,
fa_additions_b adb,
ap_invoice_lines_all AIL,
ap_invoice_distributions_all AID,
ap_invoices_all aia,
(
SELECT ffvv.description,
ffvv.flex_value
FROM fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvv
WHERE 1=1
AND ffvs.flex_value_set_name = 'XXON_GLS8_PROG'
and ffvs.flex_value_set_id = ffvv.flex_value_set_id) prj,
(
SELECT DISTINCT rsh.receipt_num,
rct.transaction_id,
rct.transaction_type,
rct.transaction_date
FROM rcv_transactions rct,
rcv_shipment_headers rsh,
ap_invoice_distributions_all aid2
WHERE aid2.rcv_transaction_id = rct.transaction_id
AND rct.transaction_type = 'RECEIVE'
AND rct.shipment_header_id = rsh.shipment_header_id) rec
WHERE vnd.vendor_id(+) = adn.po_vendor_id
and fmd.mass_addition_id = adn.mass_addition_id
AND cde.code_combination_id (+) =nvl(adn.payables_code_combination_id, fmd.DEPRN_EXPENSE_CCID)
AND adn.queue_name NOT IN ( 'POSTED','SPLIT','MERGED', 'DELETE' )
AND pda.po_distribution_id (+) = adn.po_distribution_id
AND pda.deliver_to_person_id = fu2.person_id (+)
AND cat.category_id (+) = adn.asset_category_id
AND usr.username (+) = adn.last_updated_by
AND mcr.mass_addition_id (+) = adn.mass_addition_id
AND adb.asset_id (+) = adn.add_to_asset_id
AND aia.invoice_id (+) = adn.invoice_id
AND cde.segment8 = prj.flex_value(+)
AND ail.invoice_id (+) = adn.invoice_id
AND ail.line_number (+) = adn.invoice_line_number
AND ail.line_type_lookup_code(+) = 'ITEM'
AND aid.invoice_id (+) = adn.invoice_id
AND aid.invoice_line_number(+) = adn.invoice_line_number
AND aid.invoice_distribution_id(+) = adn.invoice_distribution_id
AND aid.rcv_transaction_id = rec.transaction_id(+)
AND ( case when adn.Book_Type_Code in (:P_Book_Type_Code) then 1
when (COALESCE (NULL,:P_Book_Type_Code) is NULL ) then 1
end = 1 )
--and adn.description IN ('TOOL 1 capitalized','506 OU ON SEMICONDUCTOR KOREA, LTD.+MACHINERY EQUIPMENT')
--AND ADN.ASSET_NUMBER='FUT-561-ARO'
--and adn.mass_addition_id =620074
----FA To GL Recon ReportFA To GL Recon Report
WITH accts as (select distinct Glcc.segment3 gac_cost, null gac_reserve, null gb_co,null gb_gac from Fa_Category_Books Ctb,
Gl_Code_Combinations Glcc
WHERE 1=1
AND Ctb.Asset_Cost_Account_Ccid = Glcc.Code_Combination_Id
and Glcc.segment3 in ('12110','12115','12120','12122','12125','12130','12135','12140','12156','12158','12161','12162','12181','12182','12320','12340','12400','12420','18011')
union
select distinct null gac_cost, Glcc.segment3 gac_reserve,null gb_co,null gb_gac from Fa_Category_Books Ctb,
Gl_Code_Combinations Glcc
WHERE 1=1
AND Ctb.Reserve_Account_Ccid = Glcc.Code_Combination_Id
and Glcc.segment3 in ('12215','12220','12222','12225','12230','12235','12240','12258','12163','12259','12260','12430','18012')
union
select distinct null gac_cost,null gac_reserve,Glcc.segment1 gb_co,null gb_gac from -- Fa_Category_Books Ctb,
Gl_Code_Combinations Glcc
WHERE 1=1
--AND Ctb.Reserve_Account_Ccid = Glcc.Code_Combination_Id
and Glcc.segment1 in ('122','123','140','151','153','158','161','172','174','192','193','211','227','231','242','252','273','283','291','292','297','298','317','331','333','335','342','343','352','506','511','512','513','517','523','524','532','533','541','542','553','555','558','559','561','564','566','572','577','580','582','584','592','612','613','621','623','625','628')
union
select distinct null gac_cost,null gac_reserve, null gb_co ,Glcc.segment3 gb_gac from -- Fa_Category_Books Ctb,
Gl_Code_Combinations Glcc
WHERE 1=1
--AND Ctb.Reserve_Account_Ccid = Glcc.Code_Combination_Id
and Glcc.segment3 in ('12460','12430','18012','12258','12235','12215','12259','12250','12225','12230','12220','12260','12158','12135','12115','12181','12125','12130','12110','12420','12163','12162','12120','12182','18011')
),
accts_consol AS (
SELECT
gac_cost gac
FROM
accts
WHERE
gac_cost IS NOT NULL
UNION ALL
SELECT
gac_reserve gac
FROM
accts
WHERE
gac_reserve IS NOT NULL
),
cost_accts as(SELECT
gac_cost gac
FROM
accts
WHERE
gac_cost IS NOT NULL
),
gb_co AS (
SELECT
gb_co
FROM
accts
WHERE
gb_co IS NOT NULL
),
gb_gac AS (
SELECT
gb_gac
FROM
accts
WHERE
gb_gac IS NOT NULL
),
--select * from gb_gac
gb as (SELECT /*+ materialize */
'SL'
|| '.'
|| glcc.segment1
|| '.'
|| glcc.segment2
|| '.'
|| glcc.segment3
|| '.'
|| glcc.segment4 acct_cd,
---SUM((nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0))) + SUM((nvl(gb.begin_balance_dr, 0) - nvl(gb.begin_balance_cr, 0))) eb -- commented by Sandeep
SUM((nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0))) + SUM((nvl(gb.begin_balance_dr_beq, 0) - nvl(gb.begin_balance_cr_beq, 0))) eb ---added by sandeep
FROM
gl_balances gb,
gl_code_combinations glcc,
gl_ledgers gl,
gb_co,
gb_gac,
(
SELECT
period_name
FROM
gl_periods
WHERE
period_set_name = '4-4-5'
AND period_name =(SUBSTR(:P_Period, 1,4)||SUBSTR(:P_Period, 7))
) prd,
accts_consol accts
WHERE
gb.code_combination_id = glcc.code_combination_id
AND gb.actual_flag = 'A'
AND gb.ledger_id = gl.ledger_id
AND gb.translated_flag IS NULL
AND gb.period_name = prd.period_name
--AND glcc.segment2 = accts.gac --- commented by sandeep
AND glcc.segment3 = accts.gac --- added by sandeep
and gl.ledger_category_code = 'SECONDARY'
and glcc.segment1 = gb_co.gb_co
--and glcc.segment2 = gb_gac.gb_gac --commented by sandeep
and glcc.segment3 = gb_gac.gb_gac --- added by sandeep
and 'SL'=:LEDGER_TYPE
GROUP BY
glcc.segment1,
glcc.segment2,
glcc.segment3,
glcc.segment4
union ALL
SELECT /*+ materialize */
'CL'
|| '.'
|| glcc.segment1
|| '.'
|| glcc.segment2
|| '.'
|| glcc.segment3
|| '.'
|| glcc.segment4 acct_cd,
---SUM((nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0))) + SUM((nvl(gb.begin_balance_dr, 0) - nvl(gb.begin_balance_cr, 0))) eb ---commented by Sandeep
SUM((nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0))) + SUM((nvl(gb.begin_balance_dr_beq, 0) - nvl(gb.begin_balance_cr_beq, 0))) eb --- added by sandeep
FROM
gl_balances gb,
gl_code_combinations glcc,
gl_ledgers gl,--- added by sandeep
gb_co,
gb_gac,
(
SELECT
period_name
FROM
gl_periods
WHERE
period_set_name = '4-4-5'
AND period_name =(SUBSTR(:P_Period, 1,4)||SUBSTR(:P_Period, 7))
) prd,
accts_consol accts
WHERE
gb.code_combination_id = glcc.code_combination_id
AND gb.actual_flag = 'A'
AND gb.ledger_id = gl.ledger_id --- added by sandeep
-- AND gb.ledger_id = 2235.1
AND gb.translated_flag IS NULL
AND gb.period_name = prd.period_name
AND glcc.segment3 = accts.gac
and glcc.segment1 = gb_co.gb_co
and glcc.segment3 = gb_gac.gb_gac
and gl.ledger_category_code = 'PRIMARY' --- added by sandeep
and 'CL'=:LEDGER_TYPE
GROUP BY
glcc.segment1,
glcc.segment2,
glcc.segment3,
glcc.segment4)
select * from
(select *
from (select nvl(mn.acct_code, gb.acct_cd) || '.ALL' acct_code,
nvl(mn.Current_Cost_Usd,0) fa_bal,
nvl(gb.eb,0) gl_bal,
case when mn.trx = 'COST' then
nvl(gb.eb,0) - nvl(mn.Current_Cost_Usd,0)
when mn.trx = 'DPR' then
nvl(gb.eb,0) - nvl(-mn.Current_Cost_Usd,0)
else
case when REGEXP_SUBSTR(nvl(mn.acct_code, gb.acct_cd), '[^.]+', 1, 3) in (select gac from cost_accts)
then nvl(gb.eb,0) - nvl(mn.Current_Cost_Usd,0)
else nvl(gb.eb,0) - nvl(-mn.Current_Cost_Usd,0)
end
end delta
from (select
cst.acct_code_cost acct_code,
sum(cst.Current_Cost_Usd) Current_Cost_Usd,
'COST' trx
from(
SELECT 'CL'
|| '.'
|| Glc2.segment1
|| '.'
|| Glc2.segment2
|| '.'
|| Glc2.segment3
|| '.'
|| Glc2.segment4 acct_code_cost,
'CL'
|| '.'
|| Glc3.segment1
|| '.'
|| Glc3.segment2
|| '.'
|| Glc3.segment3
|| '.'
|| Glc3.segment4 acct_code_reserve,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
--WHEN (Fabc.Book_Class !='TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
----WHEN Lgr.Currency_Code = 'USD' THEN
--- Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned), 2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN ('JPN') THEN
--NOT IN ('612', '613', '624') THEN
--Round((Famb.Cost / Faah.Units * Fadh.Units_Assigned), 2)
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned *
Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Current_Cost_Usd,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
--WHEN (Fabc.Book_Class !='TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
-- WHEN Lgr.Currency_Code = 'USD' THEN
--Round((Fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned), 2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN ('JPN') THEN
--NOT IN ('612', '613', '624') THEN
--Round((Mcdt.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned), 2)
Round((fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
Round((Fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned *
Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Life_Deprn_Usd
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
-- -- Fa_Distribution_Accounts Fada,
fa_category_books Fada,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
accts,
Gl_Periods Glp1,
Fa_Deprn_Summary Fds,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdp3,
Fa_Deprn_Periods Fdpmc,
Fa_Calendar_Periods Fcp,
Fa_Locations Fal,
Fa_Categories_b Fcb,
Fa_Asset_Keywords Fask,
--PER_ALL_PEOPLE_F xpv,-- Added by Sandeep
-- --Apps.Xxon_Employee_View xpv,
(SELECT Asset_Id,
Book_Type_Code,
Ytd_Deprn,
Deprn_Reserve,
Adjusted_Cost,
Impairment_Reserve,
Period_Counter
FROM Fa_Mc_Deprn_Summary Mcds
WHERE CASE
WHEN Mcds.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE 1=1
AND Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code) THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE 1=1
AND Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a, Fa_Mc_Deprn_Summary b
WHERE a.Period_Counter = b.Period_Counter
AND a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Mcds.Asset_Id
AND b.Book_Type_Code = Mcds.Book_Type_Code
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Periods Ck,
Fa_Mc_Deprn_Summary Fdk
WHERE 1=1
AND Ck.Period_Name =:P_Period
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND Ck.Period_Counter = Fdk.Period_Counter
AND b.Asset_Id = Fdk.Asset_Id)
AND a.Period_Counter = --changed from > to = Sandeep
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE 1=1
AND Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code)))
END = Mcds.Period_Counter) Mcdt,
(SELECT Ldep.Asset_Id,
Ldep.Book_Type_Code,
Ldep.Deprn_Amount,
Ldep.Ytd_Deprn,
Ldep.Period_Counter
FROM Fa_Deprn_Summary Ldep
WHERE Ldep.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Ldrn
WHERE Ldrn.Book_Type_Code = Ldep.Book_Type_Code
AND Ldrn.Deprn_Run = 'Y'
AND Ldrn.Period_Name =:P_Period )) Fdsc,
(SELECT Mdep.Asset_Id,
Mdep.Book_Type_Code,
Mdep.Deprn_Amount,
Mdep.Ytd_Deprn,
Mdep.Period_Counter
FROM Fa_Mc_Deprn_Summary Mdep
WHERE Mdep.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Mdrn
WHERE Mdrn.Book_Type_Code = Mdep.Book_Type_Code
AND Mdrn.Deprn_Run = 'Y'
AND Mdrn.Period_Name =:P_Period )) Mdsc,
(SELECT Mbks.Asset_Id,
Mbks.Book_Type_Code,
Mbks.Cost,
Mbks.Original_Cost,
Mbks.Adjusted_Cost,
Mbks.Salvage_Value,
Mbks.Date_Ineffective,
Mbks.Date_Effective,
Mbks.Transaction_Header_Id_In
FROM Fa_Mc_Books Mbks) Famb,
(SELECT Rat.Conversion_Rate
FROM Gl_Daily_Rates Rat
WHERE Rat.Conversion_Type = 'Corporate'
AND Rat.To_Currency = 'USD'
AND Rat.From_Currency = 'JPY'
AND Rat.Conversion_Date = Trunc(SYSDATE)) Jrt,
(SELECT Bks.Asset_Id,
Bks.Book_Type_Code,
Dpr.Period_Name Addn_Fa_Period
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
WHERE 1=1
AND Fadb.Asset_Id = Fab.Asset_Id
AND Fadb.Parent_Asset_Id = Fadb2.Asset_Id(+)
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
and Fabc.deprn_calendar = fcp.calendar_type
AND Fab.Asset_Id = Fadh.Asset_Id
AND Fab.Book_Type_Code=Fadh.Book_Type_Code
AND Faah.Asset_Id = Fadb.Asset_Id
AND Fab.Book_Type_Code=Faah.Book_Type_Code-- Added by Sandeep
--AND Fadh.Assigned_To = Xpv.Person_Id(+)-- Added by Sandeep
--AND Trunc(SYSDATE) BETWEEN Xpv.Person_Effective_Start_Date(+) AND Xpv.Person_Effective_End_Date(+)-- Added by Sandeep
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id -- commented by Sandeep
AND Fadh.code_combination_id = Glcc.Code_Combination_Id-- Added by Sandeep
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fdp.Book_Type_Code = Fds.Book_Type_Code
AND Fab.Book_Type_Code = Fdp3.Book_Type_Code(+)
AND Fab.Period_Counter_Fully_Retired = Fdp3.Period_Counter(+)
AND Fadb.Asset_Id = Fds.Asset_Id
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fds.Period_Counter <= --changed condition from = to <= by Sandeep
(SELECT MAX(Period_Counter)
FROM Fa_Deprn_Summary Fds2
WHERE Fadb.Asset_Id = Fds2.Asset_Id
AND Fdp.Book_Type_Code = Fds2.Book_Type_Code)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
AND Fdp2.Book_Type_Code = Fds.Book_Type_Code
AND Fdp2.Period_Counter = Fds.Period_Counter
AND Fdpmc.Book_Type_Code(+) = Mcdt.Book_Type_Code
AND Fdpmc.Period_Counter(+) = Mcdt.Period_Counter
AND Fadh.Location_Id = Fal.Location_Id
AND Fabc.Date_Ineffective IS NULL
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fab.Date_Effective AND
Nvl(Fab.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Faah.Date_Effective AND
Nvl(Faah.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fadh.Date_Effective AND
Nvl(Fadh.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.category_id = fadb.asset_category_id --- added by Sandeep
AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
AND glc2.segment3 = accts.gac_cost
AND Glc3.Code_Combination_Id = Fada.Reserve_Account_Ccid
AND Mcdt.Asset_Id(+) = Fab.Asset_Id
AND Mcdt.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fdsc.Asset_Id(+) = Fab.Asset_Id
AND Fdsc.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fdsc.Period_Counter(+) = Fds.Period_Counter
AND Mdsc.Asset_Id(+) = Fab.Asset_Id
AND Mdsc.Book_Type_Code(+) = Fab.Book_Type_Code
/*AND Nvl((SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >= -- changed the condition from = to >= by sandeep
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)*/
AND (NVL(
(SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >=
CASE
WHEN :INCLUDE_RETIRED_ASSETS = 'Y' THEN
(SELECT MIN(Period_Counter)
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
AND Fiscal_Year = (
SELECT Fiscal_Year
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
ELSE
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
END)
AND Famb.Book_Type_Code(+) = Fab.Book_Type_Code
AND Famb.Asset_Id(+) = Fab.Asset_Id
AND Famb.Transaction_Header_Id_In(+) = Fab.Transaction_Header_Id_In
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id-- added by Sandeep
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fds.Deprn_Source_Code = 'DEPRN'
AND CASE
WHEN Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code) THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a, Fa_Deprn_Summary b
WHERE a.Period_Counter = b.Period_Counter
AND a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Fds.Asset_Id
AND b.Book_Type_Code = Fds.Book_Type_Code
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Periods Ck, Fa_Deprn_Summary Fdk
WHERE Ck.Period_Name =:P_Period
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND Ck.Period_Counter = Fdk.Period_Counter
AND b.Asset_Id = Fdk.Asset_Id)
AND Nvl(Fab.Period_Counter_Fully_Reserved, 0) <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code))
AND a.Period_Counter <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)))
END = Fds.Period_Counter
-- AND Fada.Distribution_Id = Fadh.Distribution_Id
--AND (Fab.Book_Type_Code like '%FA CORP%' )---comented on 240725
AND (Fab.Book_Type_Code IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Fdp2.Period_Name = Fdp2.Period_Name
UNION ALL
SELECT 'CL'
|| '.'
|| Glc2.segment1
|| '.'
|| Glc2.segment2
|| '.'
|| Glc2.segment3
|| '.'
|| Glc2.segment4 acct_code_cost,
'CL'
|| '.'
|| Glc3.segment1
|| '.'
|| Glc3.segment2
|| '.'
|| Glc3.segment3
|| '.'
|| Glc3.segment4 acct_code_reserve,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
--WHEN (Fabc.Book_Class !='TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
----WHEN Lgr.Currency_Code = 'USD' THEN
--- Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned), 2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN ('JPN') THEN
--NOT IN ('612', '613', '624') THEN
-- Round((Famb.Cost / Faah.Units * Fadh.Units_Assigned), 2)
Round((fab.Cost / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned *
Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Current_Cost_Usd,
0 Life_Deprn_Usd
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
--Fa_Distribution_Accounts Fada,
fa_category_books Fada,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
accts,
Gl_Periods Glp1,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdp3,
Fa_Calendar_Periods Fcp,
Fa_Locations Fal,
Fa_Categories_b Fcb,
Fa_Asset_Keywords Fask,
-- Apps.Xxon_Employee_View xpv,
--PER_ALL_PEOPLE_F xpv,
(SELECT Mbks.Asset_Id,
Mbks.Book_Type_Code,
Mbks.Cost,
Mbks.Original_Cost,
Mbks.Adjusted_Cost,
Mbks.Salvage_Value,
Mbks.Date_Ineffective,
Mbks.Date_Effective,
Mbks.Transaction_Header_Id_In
FROM Fa_Mc_Books Mbks) Famb,
(SELECT Rat.Conversion_Rate
FROM Gl_Daily_Rates Rat
WHERE Rat.Conversion_Type = 'Corporate'
AND Rat.To_Currency = 'USD'
AND Rat.From_Currency = 'JPY'
AND Rat.Conversion_Date = Trunc(SYSDATE)) Jrt,
(SELECT Bks.Asset_Id,
Bks.Book_Type_Code,
Dpr.Period_Name Addn_Fa_Period
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
WHERE 1=1
AND Fab.Depreciate_Flag = 'NO'
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Summary Fds
WHERE Fds.Asset_Id = Fab.Asset_Id
AND Fds.Book_Type_Code = Fab.Book_Type_Code
AND Deprn_Source_Code = 'DEPRN')
AND Fadb.Asset_Id = Fab.Asset_Id
AND Fadb.Parent_Asset_Id = Fadb2.Asset_Id(+)
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
and Fabc.deprn_calendar = fcp.calendar_type
AND Fab.Asset_Id = Fadh.Asset_Id
AND Faah.Asset_Id = Fadb.Asset_Id
--AND Fadh.Assigned_To = Xpv.Person_Id(+) --un commented by Sandeep
---AND Trunc(SYSDATE) BETWEEN Xpv.Person_Effective_Start_Date(+) AND Xpv.Person_Effective_End_Date(+) --un commented by Sandeep
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id --ciommented by Sandeep
AND Fadh.code_combination_id = Glcc.Code_Combination_Id--- added by Sandeep
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Book_Type_Code = Fdp3.Book_Type_Code(+)
AND Fab.Period_Counter_Fully_Retired = Fdp3.Period_Counter(+)
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
AND Fdp2.Book_Type_Code = Fab.Book_Type_Code
AND Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fab.Book_Type_Code)
AND Fadh.Location_Id = Fal.Location_Id
AND Fabc.Date_Ineffective IS NULL
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fab.Date_Effective AND
Nvl(Fab.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Faah.Date_Effective AND
Nvl(Faah.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fadh.Date_Effective AND
Nvl(Fadh.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.category_id = fadb.asset_category_id-- added by Sandeep
AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
and glc2.segment3 = accts.gac_cost
AND Glc3.Code_Combination_Id = Fada.Reserve_Account_Ccid
/*AND Nvl((SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999)>= -- changed condition from = to >= by sandeep
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)*/
AND (NVL(
(SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >=
CASE
WHEN :INCLUDE_RETIRED_ASSETS = 'Y' THEN
(SELECT MIN(Period_Counter)
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
AND Fiscal_Year = (
SELECT Fiscal_Year
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
ELSE
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
END)
AND Famb.Book_Type_Code(+) = Fab.Book_Type_Code
AND Famb.Asset_Id(+) = Fab.Asset_Id
AND Famb.Transaction_Header_Id_In(+) = Fab.Transaction_Header_Id_In
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code
--AND Fada.Distribution_Id = Fadh.Distribution_Id
--AND (Fab.Book_Type_Code like '%FA CORP%' )-- comented on 240725
AND (Fab.Book_Type_Code IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Fdp2.Period_Name = Fdp2.Period_Name
) cst
where 1=1
AND cst.acct_code_cost not like '%12161%'
group by cst.acct_code_cost
union all
select
dpr.acct_code_reserve acct_code,
sum(dpr.Life_Deprn_Usd) Current_Cost_Usd,
'DPR' trx
from(
SELECT 'CL'
|| '.'
|| Glc2.segment1
|| '.'
|| Glc2.segment2
|| '.'
|| Glc2.segment3
|| '.'
|| Glc2.segment4 acct_code_cost,
'CL'
|| '.'
|| Glc3.segment1
|| '.'
|| Glc3.segment2
|| '.'
|| Glc3.segment3
|| '.'
|| Glc3.segment4 acct_code_reserve,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
--WHEN (Fabc.Book_Class !='TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
----WHEN Lgr.Currency_Code = 'USD' THEN
--- Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned), 2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN ('JPN') THEN
--NOT IN ('612', '613', '624') THEN
--Round((Famb.Cost / Faah.Units * Fadh.Units_Assigned), 2)
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned *
Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Current_Cost_Usd,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
--WHEN (Fabc.Book_Class !='TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
-- WHEN Lgr.Currency_Code = 'USD' THEN
--Round((Fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned), 2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN ('JPN') THEN
--NOT IN ('612', '613', '624') THEN
-- Round((Mcdt.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned), 2)
Round((fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
Round((Fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned *
Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Life_Deprn_Usd
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
--Fa_Distribution_Accounts Fada,
fa_category_books Fada,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
accts,
Gl_Periods Glp1,
Fa_Deprn_Summary Fds,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdp3,
Fa_Deprn_Periods Fdpmc,
Fa_Calendar_Periods Fcp,
Fa_Locations Fal,
Fa_Categories_b Fcb,
Fa_Asset_Keywords Fask,
--Apps.Xxon_Employee_View xpv,
(SELECT Asset_Id,
Book_Type_Code,
Ytd_Deprn,
Deprn_Reserve,
Adjusted_Cost,
Impairment_Reserve,
Period_Counter
FROM Fa_Mc_Deprn_Summary Mcds
WHERE CASE
WHEN Mcds.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code) THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a, Fa_Mc_Deprn_Summary b
WHERE a.Period_Counter = b.Period_Counter
AND a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Mcds.Asset_Id
AND b.Book_Type_Code = Mcds.Book_Type_Code
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Periods Ck,
Fa_Mc_Deprn_Summary Fdk
WHERE Ck.Period_Name =:P_Period
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND Ck.Period_Counter = Fdk.Period_Counter
AND b.Asset_Id = Fdk.Asset_Id)
AND a.Period_Counter = --- changed < to = Sandeep
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code)))
END = Mcds.Period_Counter) Mcdt,
(SELECT Ldep.Asset_Id,
Ldep.Book_Type_Code,
Ldep.Deprn_Amount,
Ldep.Ytd_Deprn,
Ldep.Period_Counter
FROM Fa_Deprn_Summary Ldep
WHERE Ldep.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Ldrn
WHERE Ldrn.Book_Type_Code = Ldep.Book_Type_Code
AND Ldrn.Deprn_Run = 'Y'
AND Ldrn.Period_Name =:P_Period )) Fdsc,
(SELECT Mdep.Asset_Id,
Mdep.Book_Type_Code,
Mdep.Deprn_Amount,
Mdep.Ytd_Deprn,
Mdep.Period_Counter
FROM Fa_Mc_Deprn_Summary Mdep
WHERE Mdep.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Mdrn
WHERE Mdrn.Book_Type_Code = Mdep.Book_Type_Code
AND Mdrn.Deprn_Run = 'Y'
AND Mdrn.Period_Name =:P_Period )) Mdsc,
(SELECT Mbks.Asset_Id,
Mbks.Book_Type_Code,
Mbks.Cost,
Mbks.Original_Cost,
Mbks.Adjusted_Cost,
Mbks.Salvage_Value,
Mbks.Date_Ineffective,
Mbks.Date_Effective,
Mbks.Transaction_Header_Id_In
FROM Fa_Mc_Books Mbks) Famb,
(SELECT Rat.Conversion_Rate
FROM Gl_Daily_Rates Rat
WHERE Rat.Conversion_Type = 'Corporate'
AND Rat.To_Currency = 'USD'
AND Rat.From_Currency = 'JPY'
AND Rat.Conversion_Date = Trunc(SYSDATE)) Jrt,
(SELECT Bks.Asset_Id,
Bks.Book_Type_Code,
Dpr.Period_Name Addn_Fa_Period
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
WHERE 1=1
AND Fadb.Asset_Id = Fab.Asset_Id
AND Fadb.Parent_Asset_Id = Fadb2.Asset_Id(+)
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
and Fabc.deprn_calendar = fcp.calendar_type
AND Fab.Asset_Id = Fadh.Asset_Id
AND Fab.BOOK_TYPE_CODE=Fadh.Book_Type_Code --- added by Sandeep
AND Faah.Asset_Id = Fadb.Asset_Id
and Fab.BOOK_TYPE_CODE=Faah.Book_Type_Code --- added by Sandeep
--AND Fadh.Assigned_To = Xpv.Person_Id(+)
--AND Trunc(SYSDATE) BETWEEN Xpv.Person_Effective_Start_Date(+) AND Xpv.Person_Effective_End_Date(+)
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id -- commentd by Sandeep
AND Fadh.code_combination_id = Glcc.Code_Combination_Id--- added by Sandeep
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fdp.Book_Type_Code = Fds.Book_Type_Code
AND Fab.Book_Type_Code = Fdp3.Book_Type_Code(+)
AND Fab.Period_Counter_Fully_Retired = Fdp3.Period_Counter(+)
AND Fadb.Asset_Id = Fds.Asset_Id
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fds.Period_Counter <= --- Changed = to <= Sandeep
(SELECT MAX(Period_Counter)
FROM Fa_Deprn_Summary Fds2
WHERE Fadb.Asset_Id = Fds2.Asset_Id
AND Fdp.Book_Type_Code = Fds2.Book_Type_Code)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
AND Fdp2.Book_Type_Code = Fds.Book_Type_Code
AND Fdp2.Period_Counter = Fds.Period_Counter
AND Fdpmc.Book_Type_Code(+) = Mcdt.Book_Type_Code
AND Fdpmc.Period_Counter(+) = Mcdt.Period_Counter
AND Fadh.Location_Id = Fal.Location_Id
AND Fabc.Date_Ineffective IS NULL
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fab.Date_Effective AND
Nvl(Fab.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Faah.Date_Effective AND
Nvl(Faah.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fadh.Date_Effective AND
Nvl(Fadh.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.category_id = fadb.asset_category_id --- added by Sandeep
--AND Fada.Distribution_Id = Fadh.Distribution_Id
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
and glc2.segment3 = accts.gac_cost
AND Glc3.Code_Combination_Id = Fada.Reserve_Account_Ccid
AND Mcdt.Asset_Id(+) = Fab.Asset_Id
AND Mcdt.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fdsc.Asset_Id(+) = Fab.Asset_Id
AND Fdsc.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fdsc.Period_Counter(+) = Fds.Period_Counter
AND Mdsc.Asset_Id(+) = Fab.Asset_Id
AND Mdsc.Book_Type_Code(+) = Fab.Book_Type_Code
/*AND Nvl((SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999)>= --- Changed from = to >= by Sandeep
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)*/
AND (NVL(
(SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >=
CASE
WHEN :INCLUDE_RETIRED_ASSETS = 'Y' THEN
(SELECT MIN(Period_Counter)
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
AND Fiscal_Year = (
SELECT Fiscal_Year
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
ELSE
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
END)
AND Famb.Book_Type_Code(+) = Fab.Book_Type_Code
AND Famb.Asset_Id(+) = Fab.Asset_Id
AND Famb.Transaction_Header_Id_In(+) = Fab.Transaction_Header_Id_In
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fds.Deprn_Source_Code = 'DEPRN'
AND CASE
WHEN Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code) THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a,Fa_Deprn_Summary b
WHERE a.Period_Counter = b.Period_Counter
AND a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Fds.Asset_Id
AND b.Book_Type_Code = Fds.Book_Type_Code
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Periods Ck, Fa_Deprn_Summary Fdk
WHERE Ck.Period_Name =:P_Period
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND Ck.Period_Counter = Fdk.Period_Counter
AND b.Asset_Id = Fdk.Asset_Id)
AND Nvl(Fab.Period_Counter_Fully_Reserved, 0)<
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code))
AND a.Period_Counter <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)))
END = Fds.Period_Counter
--AND (Fab.Book_Type_Code like '%FA CORP%' )-- comented on 240725
AND (Fab.Book_Type_Code IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Fdp2.Period_Name = Fdp2.Period_Name
UNION ALL
SELECT 'CL'
|| '.'
|| Glc2.segment1
|| '.'
|| Glc2.segment2
|| '.'
|| Glc2.segment3
|| '.'
|| Glc2.segment4 acct_code_cost,
'CL'
|| '.'
|| Glc3.segment1
|| '.'
|| Glc3.segment2
|| '.'
|| Glc3.segment3
|| '.'
|| Glc3.segment4 acct_code_reserve,
CASE
WHEN (Fabc.Book_Class = 'CORPORATE' OR Fabc.Book_Class = 'TAX' OR
--WHEN (Fabc.Book_Class !='TAX' OR
(Fabc.Book_Class = 'TAX' AND Fab.Book_Type_Code LIKE '%ENP%')) THEN
CASE
----WHEN Lgr.Currency_Code = 'USD' THEN
--- Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned), 2)
WHEN Substr(Fab.Book_Type_Code, 1, 3) NOT IN ('JPN') THEN
--NOT IN ('612', '613', '624') THEN
-- Round((Famb.Cost / Faah.Units * Fadh.Units_Assigned), 2)
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned), 2)
ELSE
Round((Fab.Cost / Faah.Units * Fadh.Units_Assigned *
Jrt.Conversion_Rate),
2)
END
ELSE
NULL
END Current_Cost_Usd,
0 Life_Deprn_Usd
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
-- Fa_Distribution_Accounts Fada,
fa_category_books Fada,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
accts,
Gl_Periods Glp1,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdp3,
Fa_Calendar_Periods Fcp,
Fa_Locations Fal,
Fa_Categories_b Fcb,
Fa_Asset_Keywords Fask,
--Apps.Xxon_Employee_View xpv,
(SELECT Mbks.Asset_Id,
Mbks.Book_Type_Code,
Mbks.Cost,
Mbks.Original_Cost,
Mbks.Adjusted_Cost,
Mbks.Salvage_Value,
Mbks.Date_Ineffective,
Mbks.Date_Effective,
Mbks.Transaction_Header_Id_In
FROM Fa_Mc_Books Mbks) Famb,
(SELECT Rat.Conversion_Rate
FROM Gl_Daily_Rates Rat
WHERE Rat.Conversion_Type = 'Corporate'
AND Rat.To_Currency = 'USD'
AND Rat.From_Currency = 'JPY'
AND Rat.Conversion_Date = Trunc(SYSDATE)) Jrt,
(SELECT Bks.Asset_Id,
Bks.Book_Type_Code,
Dpr.Period_Name Addn_Fa_Period
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
WHERE 1=1
AND Fab.Depreciate_Flag = 'NO'
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Summary Fds
WHERE Fds.Asset_Id = Fab.Asset_Id
AND Fds.Book_Type_Code = Fab.Book_Type_Code
AND Deprn_Source_Code = 'DEPRN')
AND Fadb.Asset_Id = Fab.Asset_Id
AND Fadb.Parent_Asset_Id = Fadb2.Asset_Id(+)
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
and Fabc.deprn_calendar = fcp.calendar_type
AND Fab.Asset_Id = Fadh.Asset_Id
AND Faah.Asset_Id = Fadb.Asset_Id
-- AND Fadh.Assigned_To = Xpv.Person_Id(+)
-- AND Trunc(SYSDATE) BETWEEN Xpv.Person_Effective_Start_Date(+) AND Xpv.Person_Effective_End_Date(+)
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id --- Commented by Sandeep
AND Fadh.code_combination_id = Glcc.Code_Combination_Id-- added by Sandeep
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Book_Type_Code = Fdp3.Book_Type_Code(+)
AND Fab.Period_Counter_Fully_Retired = Fdp3.Period_Counter(+)
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
AND Fdp2.Book_Type_Code = Fab.Book_Type_Code
AND Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fab.Book_Type_Code)
AND Fadh.Location_Id = Fal.Location_Id
AND Fabc.Date_Ineffective IS NULL
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fab.Date_Effective AND
Nvl(Fab.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Faah.Date_Effective AND
Nvl(Faah.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fadh.Date_Effective AND
Nvl(Fadh.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.category_id = fadb.asset_category_id
-- AND Fada.Distribution_Id = Fadh.Distribution_Id
AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
and glc2.segment3 = accts.gac_cost
AND Glc3.Code_Combination_Id = Fada.Reserve_Account_Ccid
/*AND Nvl((SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999)>= --- changed from = to >= by sandeep
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)*/
AND (NVL(
(SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >=
CASE
WHEN :INCLUDE_RETIRED_ASSETS = 'Y' THEN
(SELECT MIN(Period_Counter)
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
AND Fiscal_Year = (
SELECT Fiscal_Year
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
ELSE
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
END)
AND Famb.Book_Type_Code(+) = Fab.Book_Type_Code
AND Famb.Asset_Id(+) = Fab.Asset_Id
AND Famb.Transaction_Header_Id_In(+) = Fab.Transaction_Header_Id_In
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code
-- AND (Fab.Book_Type_Code like '%FA CORP%' )
AND (Fab.Book_Type_Code IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Fdp2.Period_Name = Fdp2.Period_Name
) dpr
group by dpr.acct_code_reserve) mn
JOIN --- changed from full join to equi jion by sandeep
gb
ON mn.acct_code = gb.acct_cd)
where 1=1
and 'CL'=:LEDGER_TYPE
union ALL
select * from (select nvl(mn.acct_code, gb.acct_cd) || '.ALL' acct_code,
nvl(mn.Current_Cost,0) fa_bal,
nvl(gb.eb,0) gl_bal,
case when mn.trx = 'COST' then
nvl(gb.eb,0) - nvl(mn.Current_Cost,0)
when mn.trx = 'DPR' then
nvl(gb.eb,0) - nvl(-mn.Current_Cost,0)
else
case when REGEXP_SUBSTR(nvl(mn.acct_code, gb.acct_cd), '[^.]+', 1, 3) in (select gac from cost_accts)
then nvl(gb.eb,0) - nvl(mn.Current_Cost,0)
else nvl(gb.eb,0) - nvl(-mn.Current_Cost,0)
end
end delta
from
(select cst.acct_code_cost acct_code,
sum(cst.Current_Cost) Current_Cost,
'COST' trx
from (SELECT 'SL'
|| '.'
|| Glc2.segment1
|| '.'
|| Glc2.segment2
|| '.'
|| Glc2.segment3
|| '.'
|| Glc2.segment4 acct_code_cost,
'SL'
|| '.'
|| Glc3.segment1
|| '.'
|| Glc3.segment2
|| '.'
|| Glc3.segment3
|| '.'
|| Glc3.segment4 acct_code_reserve,
--(Fab.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
(Famb.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
--(Fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned) Life_Deprn
(mcdt.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned) Life_Deprn
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
--Fa_Distribution_Accounts Fada,
fa_category_books Fada,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
accts,
Gl_Periods Glp1,
Fa_Deprn_Summary Fds,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdp3,
Fa_Deprn_Periods Fdpmc,
Fa_Calendar_Periods Fcp,
Fa_Locations Fal,
Fa_Categories_b Fcb,
Fa_Asset_Keywords Fask,
--Apps.Xxon_Employee_View xpv,
(
SELECT
plr.ledger_id,
plr.name pl_ledger,
slr.name sl_ledger,
slr.currency_code sl_cy_cd
FROM
gl_ledgers plr,
gl_ledger_relationships rel,
gl_ledgers slr
WHERE
plr.ledger_category_code = 'PRIMARY'
AND slr.name NOT LIKE 'ZZ%'
AND plr.name NOT LIKE 'ZZ%'
AND rel.primary_ledger_id = plr.ledger_id
AND rel.target_ledger_category_code = 'SECONDARY'
AND slr.ledger_id = rel.target_ledger_id
) sl,
(SELECT Asset_Id,
Book_Type_Code,
Ytd_Deprn,
Deprn_Reserve,
Adjusted_Cost,
Impairment_Reserve,
Period_Counter
FROM Fa_Mc_Deprn_Summary Mcds
WHERE CASE
WHEN Mcds.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code) THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a, Fa_Mc_Deprn_Summary b
WHERE a.Period_Counter = b.Period_Counter
AND a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Mcds.Asset_Id
AND b.Book_Type_Code = Mcds.Book_Type_Code
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Periods Ck,
Fa_Mc_Deprn_Summary Fdk
WHERE Ck.Period_Name =:P_Period
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND Ck.Period_Counter = Fdk.Period_Counter
AND b.Asset_Id = Fdk.Asset_Id)
AND a.Period_Counter <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code)))
END = Mcds.Period_Counter) Mcdt,
(SELECT Ldep.Asset_Id,
Ldep.Book_Type_Code,
Ldep.Deprn_Amount,
Ldep.Ytd_Deprn,
Ldep.Period_Counter
FROM Fa_Deprn_Summary Ldep
WHERE Ldep.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Ldrn
WHERE Ldrn.Book_Type_Code = Ldep.Book_Type_Code
AND Ldrn.Deprn_Run = 'Y'
AND Ldrn.Period_Name =:P_Period )) Fdsc,
(SELECT Mdep.Asset_Id,
Mdep.Book_Type_Code,
Mdep.Deprn_Amount,
Mdep.Ytd_Deprn,
Mdep.Period_Counter
FROM Fa_Mc_Deprn_Summary Mdep
WHERE Mdep.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Mdrn
WHERE Mdrn.Book_Type_Code = Mdep.Book_Type_Code
AND Mdrn.Deprn_Run = 'Y'
AND Mdrn.Period_Name =:P_Period )) Mdsc,
(SELECT Mbks.Asset_Id,
Mbks.Book_Type_Code,
Mbks.Cost,
Mbks.Original_Cost,
Mbks.Adjusted_Cost,
Mbks.Salvage_Value,
Mbks.Date_Ineffective,
Mbks.Date_Effective,
Mbks.Transaction_Header_Id_In
FROM Fa_Mc_Books Mbks) Famb,
(SELECT Rat.Conversion_Rate
FROM Gl_Daily_Rates Rat
WHERE Rat.Conversion_Type = 'Corporate'
AND Rat.To_Currency = 'USD'
AND Rat.From_Currency = 'JPY'
AND Rat.Conversion_Date = Trunc(SYSDATE)) Jrt,
(SELECT Bks.Asset_Id,
Bks.Book_Type_Code,
Dpr.Period_Name Addn_Fa_Period
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
WHERE 1=1
AND Fadb.Asset_Id = Fab.Asset_Id
AND Fadb.Parent_Asset_Id = Fadb2.Asset_Id(+)
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
and Fabc.deprn_calendar = fcp.calendar_type
AND Fab.Asset_Id = Fadh.Asset_Id
AND Fab.BOOK_TYPE_CODE=Fadh.Book_Type_Code --- added by Sandeep
AND Faah.Asset_Id = Fadb.Asset_Id
and Fab.BOOK_TYPE_CODE=Faah.Book_Type_Code --- added by Sandeep
-- AND Fadh.Assigned_To = Xpv.Person_Id(+)
-- AND Trunc(SYSDATE) BETWEEN Xpv.Person_Effective_Start_Date(+) AND Xpv.Person_Effective_End_Date(+)
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id -- commented by Sandeep
AND Fadh.code_combination_id = Glcc.Code_Combination_Id --- added by Sandeep
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fdp.Book_Type_Code = Fds.Book_Type_Code
AND Fab.Book_Type_Code = Fdp3.Book_Type_Code(+)
AND Fab.Period_Counter_Fully_Retired = Fdp3.Period_Counter(+)
AND Fadb.Asset_Id = Fds.Asset_Id
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fds.Period_Counter <= --- Changed from = to <= by Sandeep
(SELECT MAX(Period_Counter)
FROM Fa_Deprn_Summary Fds2
WHERE Fadb.Asset_Id = Fds2.Asset_Id
AND Fdp.Book_Type_Code = Fds2.Book_Type_Code)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
AND Fdp2.Book_Type_Code = Fds.Book_Type_Code
AND Fdp2.Period_Counter = Fds.Period_Counter
AND Fdpmc.Book_Type_Code(+) = Mcdt.Book_Type_Code
AND Fdpmc.Period_Counter(+) = Mcdt.Period_Counter
AND Fadh.Location_Id = Fal.Location_Id
AND Fabc.Date_Ineffective IS NULL
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fab.Date_Effective AND
Nvl(Fab.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Faah.Date_Effective AND
Nvl(Faah.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fadh.Date_Effective AND
Nvl(Fadh.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.category_id = fadb.asset_category_id --- added by Sandeep
--AND Fada.Distribution_Id = Fadh.Distribution_Id
AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
and glc2.segment3 = accts.gac_cost
AND Glc3.Code_Combination_Id = Fada.Reserve_Account_Ccid
AND Mcdt.Asset_Id(+) = Fab.Asset_Id
AND Mcdt.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fdsc.Asset_Id(+) = Fab.Asset_Id
AND Fdsc.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fdsc.Period_Counter(+) = Fds.Period_Counter
AND Mdsc.Asset_Id(+) = Fab.Asset_Id
AND Mdsc.Book_Type_Code(+) = Fab.Book_Type_Code
/*AND Nvl((SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >= ---- changed from from = to >= by Sandeep
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)*/
AND (NVL(
(SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >=
CASE
WHEN :INCLUDE_RETIRED_ASSETS = 'Y' THEN
(SELECT MIN(Period_Counter)
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
AND Fiscal_Year = (
SELECT Fiscal_Year
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
ELSE
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
END)
AND Famb.Book_Type_Code(+) = Fab.Book_Type_Code
AND Famb.Asset_Id(+) = Fab.Asset_Id
AND Famb.Transaction_Header_Id_In(+) = Fab.Transaction_Header_Id_In
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id
AND Lgr.ledger_id = sl.ledger_id
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fds.Deprn_Source_Code = 'DEPRN'
AND CASE
WHEN Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code) THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a, Fa_Deprn_Summary b
WHERE a.Period_Counter = b.Period_Counter
AND a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Fds.Asset_Id
AND b.Book_Type_Code = Fds.Book_Type_Code
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Periods Ck, Fa_Deprn_Summary Fdk
WHERE Ck.Period_Name =:P_Period
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND Ck.Period_Counter = Fdk.Period_Counter
AND b.Asset_Id = Fdk.Asset_Id)
AND Nvl(Fab.Period_Counter_Fully_Reserved, 0) <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code))
AND a.Period_Counter <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)))
END = Fds.Period_Counter
--AND (Fab.Book_Type_Code like '%FA CORP%' )--comented on 240725
AND (Fab.Book_Type_Code IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Fdp2.Period_Name = Fdp2.Period_Name
union ALL
SELECT 'SL'
|| '.'
|| Glc2.segment1
|| '.'
|| Glc2.segment2
|| '.'
|| Glc2.segment3
|| '.'
|| Glc2.segment4 acct_code_cost,
'SL'
|| '.'
|| Glc3.segment1
|| '.'
|| Glc3.segment2
|| '.'
|| Glc3.segment3
|| '.'
|| Glc3.segment4 acct_code_reserve,
--(Fab.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
(Famb.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
0 Life_Deprn
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
-- Fa_Distribution_Accounts Fada,
fa_category_books Fada,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
accts,
Gl_Periods Glp1,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdp3,
Fa_Calendar_Periods Fcp,
Fa_Locations Fal,
Fa_Categories_b Fcb,
Fa_Asset_Keywords Fask,
--Apps.Xxon_Employee_View xpv,
(
SELECT
plr.ledger_id,
plr.name pl_ledger,
slr.name sl_ledger,
slr.currency_code sl_cy_cd
FROM
gl_ledgers plr,
gl_ledger_relationships rel,
gl_ledgers slr
WHERE
plr.ledger_category_code = 'PRIMARY'
AND slr.name NOT LIKE 'ZZ%'
AND plr.name NOT LIKE 'ZZ%'
AND rel.primary_ledger_id = plr.ledger_id
AND rel.target_ledger_category_code = 'SECONDARY'
AND slr.ledger_id = rel.target_ledger_id
) sl,
(SELECT Mbks.Asset_Id,
Mbks.Book_Type_Code,
Mbks.Cost,
Mbks.Original_Cost,
Mbks.Adjusted_Cost,
Mbks.Salvage_Value,
Mbks.Date_Ineffective,
Mbks.Date_Effective,
Mbks.Transaction_Header_Id_In
FROM Fa_Mc_Books Mbks) Famb,
(SELECT Rat.Conversion_Rate
FROM Gl_Daily_Rates Rat
WHERE Rat.Conversion_Type = 'Corporate'
AND Rat.To_Currency = 'USD'
AND Rat.From_Currency = 'JPY'
AND Rat.Conversion_Date = Trunc(SYSDATE)) Jrt,
(SELECT Bks.Asset_Id,
Bks.Book_Type_Code,
Dpr.Period_Name Addn_Fa_Period
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
WHERE 1=1
AND Fab.Depreciate_Flag = 'NO'
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Summary Fds
WHERE Fds.Asset_Id = Fab.Asset_Id
AND Fds.Book_Type_Code = Fab.Book_Type_Code
AND Deprn_Source_Code = 'DEPRN')
AND Fadb.Asset_Id = Fab.Asset_Id
AND Fadb.Parent_Asset_Id = Fadb2.Asset_Id(+)
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
and Fabc.deprn_calendar = fcp.calendar_type
AND Fab.Asset_Id = Fadh.Asset_Id
AND Faah.Asset_Id = Fadb.Asset_Id
-- AND Fadh.Assigned_To = Xpv.Person_Id(+)
-- AND Trunc(SYSDATE) BETWEEN Xpv.Person_Effective_Start_Date(+) AND Xpv.Person_Effective_End_Date(+)
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id --commentd by Sandeep
AND Fadh.code_combination_id = Glcc.Code_Combination_Id -- added by Sandeep
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Book_Type_Code = Fdp3.Book_Type_Code(+)
AND Fab.Period_Counter_Fully_Retired = Fdp3.Period_Counter(+)
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
AND Fdp2.Book_Type_Code = Fab.Book_Type_Code
AND Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fab.Book_Type_Code)
AND Fadh.Location_Id = Fal.Location_Id
AND Fabc.Date_Ineffective IS NULL
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fab.Date_Effective AND
Nvl(Fab.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Faah.Date_Effective AND
Nvl(Faah.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fadh.Date_Effective AND
Nvl(Fadh.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.category_id = fadb.asset_category_id --- added by Sandeep
AND Fab.Book_Type_Code=Fadh.Book_Type_Code
-- AND Fada.Distribution_Id = Fadh.Distribution_Id
AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
and glc2.segment3 = accts.gac_cost
AND Glc3.Code_Combination_Id = Fada.Reserve_Account_Ccid
/*AND Nvl((SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >= ---changed from = to >= by Sandeep
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)*/
AND (NVL(
(SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >=
CASE
WHEN :INCLUDE_RETIRED_ASSETS = 'Y' THEN
(SELECT MIN(Period_Counter)
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
AND Fiscal_Year = (
SELECT Fiscal_Year
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
ELSE
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
END)
AND Famb.Book_Type_Code(+) = Fab.Book_Type_Code
AND Famb.Asset_Id(+) = Fab.Asset_Id
AND Famb.Transaction_Header_Id_In(+) = Fab.Transaction_Header_Id_In
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id
AND Lgr.ledger_id = sl.ledger_id
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code
--AND (Fab.Book_Type_Code like '%FA CORP%' )--comented on 240725
AND (Fab.Book_Type_Code IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Fdp2.Period_Name = Fdp2.Period_Name
) cst
where cst.acct_code_cost not like '%12161%'
group by cst.acct_code_cost
union all
select
dpr.acct_code_reserve acct_code,
sum(dpr.Life_Deprn) Current_Cost,
'DPR' trx
from (SELECT 'SL'
|| '.'
|| Glc2.segment1
|| '.'
|| Glc2.segment2
|| '.'
|| Glc2.segment3
|| '.'
|| Glc2.segment4 acct_code_cost,
'SL'
|| '.'
|| Glc3.segment1
|| '.'
|| Glc3.segment2
|| '.'
|| Glc3.segment3
|| '.'
|| Glc3.segment4 acct_code_reserve,
--(Fab.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
(Famb.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
--(Fds.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned) Life_Deprn
(mcdt.Deprn_Reserve / Faah.Units * Fadh.Units_Assigned) Life_Deprn
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
-- Fa_Distribution_Accounts Fada,
fa_category_books Fada,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
accts,
Gl_Periods Glp1,
Fa_Deprn_Summary Fds,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdp3,
Fa_Deprn_Periods Fdpmc,
Fa_Calendar_Periods Fcp,
Fa_Locations Fal,
Fa_Categories_b Fcb,
Fa_Asset_Keywords Fask,
--Apps.Xxon_Employee_View xpv,
(
SELECT
plr.ledger_id,
plr.name pl_ledger,
slr.name sl_ledger,
slr.currency_code sl_cy_cd
FROM
gl_ledgers plr,
gl_ledger_relationships rel,
gl_ledgers slr
WHERE
plr.ledger_category_code = 'PRIMARY'
AND slr.name NOT LIKE 'ZZ%'
AND plr.name NOT LIKE 'ZZ%'
AND rel.primary_ledger_id = plr.ledger_id
AND rel.target_ledger_category_code = 'SECONDARY'
AND slr.ledger_id = rel.target_ledger_id
) sl,
(SELECT Asset_Id,
Book_Type_Code,
Ytd_Deprn,
Deprn_Reserve,
Adjusted_Cost,
Impairment_Reserve,
Period_Counter
FROM Fa_Mc_Deprn_Summary Mcds
WHERE CASE
WHEN Mcds.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code) THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a,Fa_Mc_Deprn_Summary b
WHERE a.Period_Counter = b.Period_Counter
AND a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Mcds.Asset_Id
AND b.Book_Type_Code = Mcds.Book_Type_Code
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Periods Ck,
Fa_Mc_Deprn_Summary Fdk
WHERE Ck.Period_Name =:P_Period
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND Ck.Period_Counter = Fdk.Period_Counter
AND b.Asset_Id = Fdk.Asset_Id)
AND a.Period_Counter <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Mcds.Book_Type_Code)))
END = Mcds.Period_Counter) Mcdt,
(SELECT Ldep.Asset_Id,
Ldep.Book_Type_Code,
Ldep.Deprn_Amount,
Ldep.Ytd_Deprn,
Ldep.Period_Counter
FROM Fa_Deprn_Summary Ldep
WHERE Ldep.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Ldrn
WHERE Ldrn.Book_Type_Code = Ldep.Book_Type_Code
AND Ldrn.Deprn_Run = 'Y'
AND Ldrn.Period_Name =:P_Period )) Fdsc,
(SELECT Mdep.Asset_Id,
Mdep.Book_Type_Code,
Mdep.Deprn_Amount,
Mdep.Ytd_Deprn,
Mdep.Period_Counter
FROM Fa_Mc_Deprn_Summary Mdep
WHERE Mdep.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Mdrn
WHERE Mdrn.Book_Type_Code = Mdep.Book_Type_Code
AND Mdrn.Deprn_Run = 'Y'
AND Mdrn.Period_Name =:P_Period )) Mdsc,
(SELECT Mbks.Asset_Id,
Mbks.Book_Type_Code,
Mbks.Cost,
Mbks.Original_Cost,
Mbks.Adjusted_Cost,
Mbks.Salvage_Value,
Mbks.Date_Ineffective,
Mbks.Date_Effective,
Mbks.Transaction_Header_Id_In
FROM Fa_Mc_Books Mbks) Famb,
(SELECT Rat.Conversion_Rate
FROM Gl_Daily_Rates Rat
WHERE Rat.Conversion_Type = 'Corporate'
AND Rat.To_Currency = 'USD'
AND Rat.From_Currency = 'JPY'
AND Rat.Conversion_Date = Trunc(SYSDATE)) Jrt,
(SELECT Bks.Asset_Id,
Bks.Book_Type_Code,
Dpr.Period_Name Addn_Fa_Period
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
WHERE 1=1
AND Fadb.Asset_Id = Fab.Asset_Id
AND Fadb.Parent_Asset_Id = Fadb2.Asset_Id(+)
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
and Fabc.deprn_calendar = fcp.calendar_type
AND Fab.Asset_Id = Fadh.Asset_Id
AND Fab.BOOK_TYPE_CODE=Fadh.Book_Type_Code --- added by Sandeep
AND Faah.Asset_Id = Fadb.Asset_Id
-- AND Fadh.Assigned_To = Xpv.Person_Id(+)
-- AND Trunc(SYSDATE) BETWEEN Xpv.Person_Effective_Start_Date(+) AND Xpv.Person_Effective_End_Date(+)
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id-- commented by Sandeep
AND Fadh.code_combination_id = Glcc.Code_Combination_Id --- Added by Sandeep
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fdp.Book_Type_Code = Fds.Book_Type_Code
AND Fab.Book_Type_Code = Fdp3.Book_Type_Code(+)
AND Fab.Period_Counter_Fully_Retired = Fdp3.Period_Counter(+)
AND Fadb.Asset_Id = Fds.Asset_Id
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fds.Period_Counter <= ---Changed from = to <= by Sandeep
(SELECT MAX(Period_Counter)
FROM Fa_Deprn_Summary Fds2
WHERE Fadb.Asset_Id = Fds2.Asset_Id
AND Fdp.Book_Type_Code = Fds2.Book_Type_Code)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
AND Fdp2.Book_Type_Code = Fds.Book_Type_Code
AND Fdp2.Period_Counter = Fds.Period_Counter
AND Fdpmc.Book_Type_Code(+) = Mcdt.Book_Type_Code
AND Fdpmc.Period_Counter(+) = Mcdt.Period_Counter
AND Fadh.Location_Id = Fal.Location_Id
AND Fabc.Date_Ineffective IS NULL
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fab.Date_Effective AND
Nvl(Fab.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Faah.Date_Effective AND
Nvl(Faah.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fadh.Date_Effective AND
Nvl(Fadh.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.category_id = fadb.asset_category_id --- added by Sandeep
-- AND Fada.Distribution_Id = Fadh.Distribution_Id
AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
and glc2.segment3 = accts.gac_cost
AND Glc3.Code_Combination_Id = Fada.Reserve_Account_Ccid
AND Mcdt.Asset_Id(+) = Fab.Asset_Id
AND Mcdt.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fdsc.Asset_Id(+) = Fab.Asset_Id
AND Fdsc.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fdsc.Period_Counter(+) = Fds.Period_Counter
AND Mdsc.Asset_Id(+) = Fab.Asset_Id
AND Mdsc.Book_Type_Code(+) = Fab.Book_Type_Code
/*AND Nvl((SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >= --changed from = to >= by sandeep
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)*/
AND (NVL(
(SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >=
CASE
WHEN :INCLUDE_RETIRED_ASSETS = 'Y' THEN
(SELECT MIN(Period_Counter)
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
AND Fiscal_Year = (
SELECT Fiscal_Year
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
ELSE
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
END)
AND Famb.Book_Type_Code(+) = Fab.Book_Type_Code
AND Famb.Asset_Id(+) = Fab.Asset_Id
AND Famb.Transaction_Header_Id_In(+) = Fab.Transaction_Header_Id_In
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id
AND Lgr.ledger_id = sl.ledger_id
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code
AND Fds.Deprn_Source_Code = 'DEPRN'
AND CASE
WHEN Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code) THEN
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)
ELSE
(SELECT MAX(a.Period_Counter)
FROM Fa_Deprn_Periods a, Fa_Deprn_Summary b
WHERE a.Period_Counter = b.Period_Counter
AND a.Book_Type_Code = b.Book_Type_Code
AND b.Asset_Id = Fds.Asset_Id
AND b.Book_Type_Code = Fds.Book_Type_Code
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Periods Ck, Fa_Deprn_Summary Fdk
WHERE Ck.Period_Name =:P_Period
AND Ck.Book_Type_Code = a.Book_Type_Code
AND Fdk.Book_Type_Code = Ck.Book_Type_Code
AND Ck.Period_Counter = Fdk.Period_Counter
AND b.Asset_Id = Fdk.Asset_Id)
AND Nvl(Fab.Period_Counter_Fully_Reserved, 0) <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code))
AND a.Period_Counter <
((SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)))
END = Fds.Period_Counter
-- AND (Fab.Book_Type_Code like '%FA CORP%' )-- comented on 240725
AND (Fab.Book_Type_Code IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Fdp2.Period_Name = Fdp2.Period_Name
union ALL
SELECT 'SL'
|| '.'
|| Glc2.segment1
|| '.'
|| Glc2.segment2
|| '.'
|| Glc2.segment3
|| '.'
|| Glc2.segment4 acct_code_cost,
'SL'
|| '.'
|| Glc3.segment1
|| '.'
|| Glc3.segment2
|| '.'
|| Glc3.segment3
|| '.'
|| Glc3.segment4 acct_code_reserve,
--(Fab.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
(Famb.Cost / Faah.Units * Fadh.Units_Assigned) Current_Cost,
0 Life_Deprn
FROM Fa_Additions_b Fadb,
Fa_Additions_b Fadb2,
Fa_Additions_Tl Fadt,
Fa_Books Fab,
Fa_Book_Controls Fabc,
Gl_Ledgers Lgr,
Fa_Distribution_History Fadh,
Fa_Asset_History Faah,
--Fa_Distribution_Accounts Fada,
fa_category_books Fada,
Gl_Code_Combinations Glcc,
Gl_Code_Combinations Glc2,
Gl_Code_Combinations Glc3,
accts,
Gl_Periods Glp1,
Fa_Deprn_Periods Fdp,
Fa_Deprn_Periods Fdp2,
Fa_Deprn_Periods Fdp3,
Fa_Calendar_Periods Fcp,
Fa_Locations Fal,
Fa_Categories_b Fcb,
Fa_Asset_Keywords Fask,
-- Apps.Xxon_Employee_View xpv,
(
SELECT
plr.ledger_id,
plr.name pl_ledger,
slr.name sl_ledger,
slr.currency_code sl_cy_cd
FROM
gl_ledgers plr,
gl_ledger_relationships rel,
gl_ledgers slr
WHERE
plr.ledger_category_code = 'PRIMARY'
AND slr.name NOT LIKE 'ZZ%'
AND plr.name NOT LIKE 'ZZ%'
AND rel.primary_ledger_id = plr.ledger_id
AND rel.target_ledger_category_code = 'SECONDARY'
AND slr.ledger_id = rel.target_ledger_id
) sl,
(SELECT Mbks.Asset_Id,
Mbks.Book_Type_Code,
Mbks.Cost,
Mbks.Original_Cost,
Mbks.Adjusted_Cost,
Mbks.Salvage_Value,
Mbks.Date_Ineffective,
Mbks.Date_Effective,
Mbks.Transaction_Header_Id_In
FROM Fa_Mc_Books Mbks) Famb,
(SELECT Rat.Conversion_Rate
FROM Gl_Daily_Rates Rat
WHERE Rat.Conversion_Type = 'Corporate'
AND Rat.To_Currency = 'USD'
AND Rat.From_Currency = 'JPY'
AND Rat.Conversion_Date = Trunc(SYSDATE)) Jrt,
(SELECT Bks.Asset_Id,
Bks.Book_Type_Code,
Dpr.Period_Name Addn_Fa_Period
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
WHERE 1=1
AND Fab.Depreciate_Flag = 'NO'
AND NOT EXISTS
(SELECT 1
FROM Fa_Deprn_Summary Fds
WHERE Fds.Asset_Id = Fab.Asset_Id
AND Fds.Book_Type_Code = Fab.Book_Type_Code
AND Deprn_Source_Code = 'DEPRN')
AND Fadb.Asset_Id = Fab.Asset_Id
AND Fadb.Parent_Asset_Id = Fadb2.Asset_Id(+)
AND Fab.Asset_Id = Fadt.Asset_Id
AND Fadt.Language = 'US'
AND Fab.Book_Type_Code = Fabc.Book_Type_Code
and Fabc.deprn_calendar = fcp.calendar_type
AND Fab.Asset_Id = Fadh.Asset_Id
AND Faah.Asset_Id = Fadb.Asset_Id
-- AND Fadh.Assigned_To = Xpv.Person_Id(+)
-- AND Trunc(SYSDATE) BETWEEN Xpv.Person_Effective_Start_Date(+) AND Xpv.Person_Effective_End_Date(+)
--AND Fada.Deprn_Expense_Account_Ccid = Glcc.Code_Combination_Id --- Commented by Sandeep
AND Fadh.code_combination_id = Glcc.Code_Combination_Id
AND Trunc(Fadb.Creation_Date) BETWEEN Glp1.Start_Date AND Glp1.End_Date
AND Glp1.Period_Set_Name = '4-4-5'
AND Fdp.Period_Counter = Fabc.Last_Period_Counter
AND Fdp.Book_Type_Code = Fabc.Book_Type_Code
AND Fab.Book_Type_Code = Fdp3.Book_Type_Code(+)
AND Fab.Period_Counter_Fully_Retired = Fdp3.Period_Counter(+)
AND Fcb.Category_Id = Fadb.Asset_Category_Id
AND Fadb.Asset_Key_Ccid = Fask.Code_Combination_Id(+)
AND Fab.Date_Placed_In_Service BETWEEN Fcp.Start_Date(+) AND
Fcp.End_Date(+)
AND Fdp2.Book_Type_Code = Fab.Book_Type_Code
AND Fdp2.Period_Counter =
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fab.Book_Type_Code)
AND Fadh.Location_Id = Fal.Location_Id
AND Fabc.Date_Ineffective IS NULL
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fab.Date_Effective AND
Nvl(Fab.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Faah.Date_Effective AND
Nvl(Faah.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Nvl(Fdp2.Period_Close_Date, SYSDATE) BETWEEN Fadh.Date_Effective AND
Nvl(Fadh.Date_Ineffective, Nvl(Fdp2.Period_Close_Date, SYSDATE))
AND Fada.Book_Type_Code = Fab.Book_Type_Code
AND Fada.category_id = fadb.asset_category_id --added by Sandeep
--AND Fada.Distribution_Id = Fadh.Distribution_Id
AND Glc2.Code_Combination_Id = Fada.Asset_Cost_Account_Ccid
and glc2.segment3 = accts.gac_cost
AND Glc3.Code_Combination_Id = Fada.Reserve_Account_Ccid
/*AND Nvl((SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >= ---changed from = to >= by Sandeep
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name =:P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code)*/
AND (NVL(
(SELECT Fab_Rt.Period_Counter_Fully_Retired
FROM Fa_Books Fab_Rt
WHERE Fab_Rt.Asset_Id = Fab.Asset_Id
AND Fab_Rt.Book_Type_Code = Fab.Book_Type_Code
AND Fab_Rt.Date_Ineffective IS NULL),
99999999) >=
CASE
WHEN :INCLUDE_RETIRED_ASSETS = 'Y' THEN
(SELECT MIN(Period_Counter)
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
AND Fiscal_Year = (
SELECT Fiscal_Year
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
)
ELSE
(SELECT Period_Counter
FROM Fa_Deprn_Periods Fadppar
WHERE Fadppar.Period_Name = :P_Period
AND Fadppar.Book_Type_Code = Fdp2.Book_Type_Code
)
END)
AND Famb.Book_Type_Code(+) = Fab.Book_Type_Code
AND Famb.Asset_Id(+) = Fab.Asset_Id
AND Famb.Transaction_Header_Id_In(+) = Fab.Transaction_Header_Id_In
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id
AND Lgr.ledger_id = sl.ledger_id
AND Adnp.Asset_Id(+) = Fab.Asset_Id
AND Adnp.Book_Type_Code(+) = Fab.Book_Type_Code
-- AND (Fab.Book_Type_Code like '%FA CORP%' )-- comented in 240725
AND (Fab.Book_Type_Code IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)
AND Fdp2.Period_Name = Fdp2.Period_Name
)dpr
group by dpr.acct_code_reserve)
mn
JOIN ----- changed from full join to equi join by sandeep
gb
ON mn.acct_code = gb.acct_cd
)
where 1=1
and 'SL'=:LEDGER_TYPE
)
where 1=1
order by acct_code ASC
No comments:
Post a Comment