FA Interco Corp Book Transfer Report
ELECT distinct NULL Asset_Number,
Al1.Asset_Description,
Al1.Asset_Key,
Al1.Asset_Category,
Al1.Asset_Subcategory,
Al1.Serial_Number,
NULL Tag_Number,
Al1.Supplier_Number,
Al1.Invoice_Number,
Al1.Po_Number,
NULL Employee_Number,
Al1.project_number,
Al1.Manufacturer_Name,
Al1.Model_Number,
NULL Country,
NULL State,
NULL City,
NULL Building,
NULL Room,
TO_CHAR(Al1.Placed_In_Service,'MM/DD/YYYY') PLACED_IN_SERVICE,
TO_CHAR(Trunc(SYSDATE),'MM/DD/YYYY') Accounting_Date,
Nvl(Al1.Units_Retired, Al1.Units_Assigned) Units,
/*CASE
WHEN Al1.Receiver_Curr = 'USD' THEN
Al1.Cost_Ret_Usd_Calc
WHEN Al1.Receiver_Curr is NULL THEN
Round((Al1.Cost_Ret_Usd_Calc * Al3.Conversion_Rate), 2)
ELSE*/
--Round((Al1.Cost_Ret_Usd_Calc * Al3.Conversion_Rate), 2)
Al1.Cost_Ret_Usd_Calc as Cost_Amt,
0 Ytd_Deprn,
/*CASE
WHEN Al1.Receiver_Curr = 'USD' THEN
Al1.Reserve_Ret_Usd_Calc
WHEN Al1.Receiver_Curr is NULL THEN
Round((Al1.Cost_Ret_Usd_Calc * Al3.Conversion_Rate), 2)
ELSE
--Round((Al1.Reserve_Ret_Usd_Calc * Al3.Conversion_Rate), 2)*/
Al1.Reserve_Ret_Usd_Calc as Accum_Deprn_Amt,
--Receiving_Company Company,
Al1.CO CO,
Al1.LOC LOC,
Al1.Exp_Glbl_Acct EXP_GAC,
Al1.Deprn_Exp_Local_Acct DEPRN_EXP_LAC,
--NULL Dept,-- Replaced with new mapping
al1.Dept,
--'000' Ico,--Replaced with new mapping
Al1.ICO,
--'00000' Proj, -- Replaced with program
Al1.Program Program,
Al1.BU BU,
--'0000' Bu, -- Replaced with PAL
Al1.PAL PAL,
--'000' Fut, --Replaced with new mapping
Al1.future as Fut,
'STL' Deprn_Method,
Al1.Life_Months Life_Months,
'Yes' Depreciate,
'Yes' Inventory,
'Yes' Amortize_Nbv,
TO_CHAR(Trunc(SYSDATE),'MM/DD/YYYY') Amort_Start_Date,
NULL Parent_Asset_Number,
'Owned' Ownership,
Al1.Asset_Comments_Attr2 English_Description,
al1.Exp_Company || ' - ' || Al1.Asset_Number Orig_Asset_Num,
Al1.Orig_Service_Dt_Attr4 Orig_Service_Dt_Attr4,
NULL Impairment_Amt,
'Used' New_Used,
--Al1.Cost_Ret_Usd_Calc Cost_Usd,--infosys replaced Cost_Ret_Usd_Calc for USD Values
Al1.Cost_Ret_USD Cost_Usd,
--Al1.Reserve_Ret_Usd_Calc Accum_Deprn_Usd,--infosys replaced with below column for usd values
Al1.Accum_Deprn_Usd,
--(Al1.Cost_Ret_Usd_Calc) - (Al1.Reserve_Ret_Usd_Calc) Nbv_Usd,--infosys replaced with below column for usd values
(Al1.Cost_Ret_USD) - (Al1.Accum_Deprn_Usd) Nbv_Usd,
/*(CASE
WHEN Al1.Receiver_Curr = 'USD' THEN
Al1.Cost_Ret_Usd_Calc
WHEN Al1.Receiver_Curr is NULL
THEN
Round((Al1.Cost_Ret_Usd_Calc * Al3.Conversion_Rate), 2)
ELSE
--Round((Al1.Cost_Ret_Usd_Calc * Al3.Conversion_Rate), 2)
Al1.Cost_Ret_Usd_Calc
END) - (CASE
WHEN Al1.Receiver_Curr = 'USD'
THEN Al1.Reserve_Ret_Usd_Calc
WHEN Al1.Receiver_Curr is NULL THEN
Round((Al1.Cost_Ret_Usd_Calc * Al3.Conversion_Rate), 2)
ELSE
--Round((Al1.Reserve_Ret_Usd_Calc * Al3.Conversion_Rate), 2)
Al1.Reserve_Ret_Usd_Calc
END)*/
(Al1.Cost_Ret_Usd_Calc - Al1.Reserve_Ret_Usd_Calc) as Nbv_Local_Curr_Recvr,
to_Char(sysdate,'MM/DD/YYYY HH:MI AM') timestamp,
Al3.conversion_rate,
Al1.Cost_Ret_Usd_Calc,
Al1.Receiver_Curr,
Al1.Book_Name
FROM (SELECT Far.Book_Type_Code Book_Name,
Fabc.Book_Class Book_Type,
/*Substr(Fadb.Attribute_Category_Code,
1,
(Instr(Fadb.Attribute_Category_Code, '.') - 1))*/
Facb.segment1 Asset_Category,
/*Substr(Fadb.Attribute_Category_Code,
(Instr(Fadb.Attribute_Category_Code, '.') + 1),
Length(Fadb.Attribute_Category_Code))*/
Facb.segment2 Asset_Subcategory,---infosys asset subcategory name
Fadb.Asset_Number Asset_Number,
Fadt.Description Asset_Description,
Fadb.Tag_Number Tag_Number,
Far.Asset_Id Asset_Id,
Glp.Period_Name Period_Mon_Yy,
Far.Date_Retired Date_Retired,
Trunc(Far.Date_Effective) Date_Effective,
CASE
WHEN Substr(Far.Retirement_Type_Code, 1, 3) = 'ICO' THEN
'Intercompany Transfers'
WHEN Falt.Meaning = 'SCRAPPED' THEN
'Retirements'
WHEN Falt.Meaning = 'EXTSALE' THEN
'Sales to Third Parties'
ELSE
'Other'
END Retirement_Categ,
Substr(Far.Retirement_Type_Code, 8, 3) Receiving_Company,
Far.Retirement_Type_Code Retirement_Type_Code,
Falt.Meaning Retirement_Type,
Falt.Description Ret_Type_Description,
Far.Status Ret_Status,
Glcc.Segment1 CO,
Glcc.Segment1 Exp_Company,
Glcc.Segment2 LOC,
Glcc.Segment3 Exp_Glbl_Acct,
Glcc.Segment4 Deprn_Exp_Local_Acct,
Glcc.segment5 dept,
Glcc.segment6 PAL,
Glcc.segment6 BU,
Glcc.segment7 ICO,
Glcc.segment8 Program,
pab.Segment1 project_number,
Glcc.segment9 Future,
Fask.Segment1 Asset_Key,
Far.Last_Update_Date Last_Updt_Date,
Fndu.User_Id Last_Updt_User_Id,
Far.Sold_To Sold_To,
Far.Units Units_Retired,
Far.Cost_Retired Cost_Retired,
(Nvl(Far.Cost_Retired, 0) - Nvl(Far.Nbv_Retired, 0)) Reserve_Retired,
Far.Nbv_Retired Nbv_Retired,
Far.Cost_Of_Removal Cost_Of_Removal,
Far.Gain_Loss_Amount Gain_Loss_Amt,
Far.Proceeds_Of_Sale Proceeds_Of_Sale,
CASE
WHEN Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code NOT LIKE '%FA ENP%' THEN
0
WHEN Substr(Far.Book_Type_Code, 1, 3) IN
('JPY') THEN
Round((Far.Cost_Retired * Rat.Conversion_Rate), 2)
ELSE
Nvl(Famr.Cost_Retired, Far.Cost_Retired)
END Cost_Ret_Usd_Calc,
Far.Cost_Retired Cost_Ret_USD, --Infosys added to display cost retired in USB in oracle fusion
CASE
WHEN Fabc.Book_Class = 'TAX' AND
Fabc.Book_Type_Code NOT LIKE '%FA ENP%' THEN
0
WHEN Substr(Far.Book_Type_Code, 1, 3) IN
('612', '613', '624') THEN --- japan book code
Round(((Far.Cost_Retired - Far.Nbv_Retired) *
Rat.Conversion_Rate),
2)
ELSE
Nvl((Famr.Cost_Retired - Famr.Nbv_Retired),
(Far.Cost_Retired - Far.Nbv_Retired))
END Reserve_Ret_Usd_Calc,
(Far.Cost_Retired - Far.Nbv_Retired) Accum_Deprn_Usd,--Added to display Accum_Deprn_Usd in USD
Fadh.Units_Assigned Units_Assigned,
Fab.Date_Placed_In_Service Placed_In_Service,
Fab_Fam.Life_In_Months Life_Months,
Fal.Segment1 Country,
Fal.Segment2 State,
Fal.Segment3 City,
Fal.Segment4 Building,
Fal.Segment5 Room,
Fadb.Serial_Number Serial_Number,
Fadb.Manufacturer_Name Manufacturer_Name,
Fadb.Model_Number Model_Number,
Fadb.Attribute2 Asset_Comments_Attr2,
Fadb.Attribute3 Orig_Asset_Num_Attr3,
Fadb.Attribute4 Orig_Service_Dt_Attr4,
Ainv.Invoice Invoice_Number,
Mxpo.Maxpo Po_Number,
Vndr.Vendor_Num Supplier_Number,
Far.Retirement_Id Retirement_Id,
Fadh.Code_Combination_Id Code_Combination_Id,
Far.CREATED_BY Last_Updated_By,
Fadb.Asset_Category_Id Asset_Category_Id,
Rat.Conversion_Rate Conv_Rate_Ret,
CASE
WHEN Substr(Far.Retirement_Type_Code, 8, 2) IN ('14', '15') THEN
'USD'
WHEN Substr(Far.Retirement_Type_Code, 8, 3) IN ('511','512' ,'513', '558', '580' ,'582', '584' )
/*('525', '526', '527', '528') -- Infosys: Modified to include oracle fusion retirement type code*/
THEN
'CNY'
WHEN Substr(Far.Retirement_Type_Code, 8, 3) = '553' THEN
'MYR'
WHEN Substr(Far.Retirement_Type_Code, 8, 3) = '561' THEN
'PHP'
WHEN Substr(Far.Retirement_Type_Code, 8, 3) IN
('612', '613') THEN
'JPY'
ELSE
Lgs.Currency_Code
END Receiver_Curr,
Substr(Far.Retirement_Type_Code, 8, 3) || ' FA CORP' Receiver_Book_Name,
Thdr.Transaction_Name Retirement_Comments
FROM Fa_Retirements Far,
Fa_Mc_Retirements Famr,
Fa_Transaction_Headers Thdr,
Fa_Book_Controls Fabc,
Fa_Additions_b Fadb,
--------------------Code For Project Name Change---------------
FA_ASSET_INVOICES fainv,
PJC_PRJ_ASSET_LNS_ALL pala,
PJF_PROJECTS_ALL_B pab,
---------------------------------------------------------------
Fa_Asset_Keywords Fask,
Fa_Additions_Tl Fadt,
fa_categories_b facb,-- Infosys asset category and subcategory Names
FND_LOOKUP_VALUES Falt,
Fa_Distribution_History Fadh,
Gl_Code_Combinations Glcc,
PER_USERS Fndu,
Fa_Locations Fal,
Fa_Books Fab,
FA_METHODS Fab_Fam,
Gl_Daily_Rates Rat,
Gl_Ledgers Lgr,
Gl_Ledgers Lgs,
Fa_Deprn_Periods Glp,
(SELECT Asset_Id, MAX(Invoice_Number) Invoice
FROM Fa_Asset_Invoices
WHERE Date_Ineffective IS NULL
GROUP BY Asset_Id) Ainv, --- ( fusion table name - Fa_Asset_Invoices)
(SELECT Asset_Id, MAX(Po_Number) Maxpo
FROM Fa_Asset_Invoices
WHERE Date_Ineffective IS NULL
GROUP BY Asset_Id) Mxpo, --- ( fusion table name - Fa_Asset_Invoices)
(SELECT Aiv.Asset_Id, MAX(Supl.Segment1) Vendor_Num
FROM Fa_Asset_Invoices Aiv,
POZ_SUPPLIERS Supl,
Po_Headers_All Pohd
WHERE Aiv.Date_Ineffective IS NULL
AND Pohd.Segment1(+) = Aiv.Po_Number
AND Supl.Vendor_Id(+) = Pohd.Vendor_Id
GROUP BY Aiv.Asset_Id) Vndr --- ( fusion table name - Fa_Asset_Invoices , POZ_SUPPLIERS , PO_HEADERS_ALL)
WHERE Far.Book_Type_Code = Fabc.Book_Type_Code
AND Far.Asset_Id = Fadb.Asset_Id
AND Far.Asset_Id = Fadt.Asset_Id
AND Far.Retirement_Type_Code = Falt.Lookup_Code(+)
AND Far.Asset_Id = Fadh.Asset_Id
and Fadb.Asset_Category_Id=facb.category_ID(+)-- Infosys asset category name
AND Famr.Retirement_Id(+) = Far.Retirement_Id
AND Fadh.Code_Combination_Id = Glcc.Code_Combination_Id
AND Fask.Code_Combination_Id(+) = Fadb.Asset_Key_Ccid
--AND Far.Last_Updated_By = Fndu.User_Id -- Disabled by infosys: Join Change in Oracle Fusion
AND UPPER(Far.Last_Updated_By) = UPPER(Fndu.Username)
AND Fadh.Location_Id = Fal.Location_Id
AND Far.Asset_Id = Fab.Asset_Id
AND Far.Book_Type_Code = Fab.Book_Type_Code
AND Fadt.Language = 'US'
AND Falt.Language(+) = 'US'
AND Falt.Lookup_Type(+) = 'FA_RETIREMENT_TYPE' -- changed as per cloud look up type
AND Fadh.Date_Ineffective IS NULL
AND Fab.Date_Ineffective IS NULL
AND Far.Date_Effective BETWEEN Glp.Period_Open_Date AND
Nvl(Glp.Period_Close_Date, (SYSDATE + 1))
AND Far.Book_Type_Code = Glp.Book_Type_Code
AND Far.RETIREMENT_ID = fadh.RETIREMENT_ID
AND Fab_Fam.METHOD_ID = Fab.METHOD_ID
AND Far.Status = 'PROCESSED'
AND Ainv.Asset_Id(+) = Fadb.Asset_Id
AND Mxpo.Asset_Id(+) = Fadb.Asset_Id
AND Vndr.Asset_Id(+) = Fadb.Asset_Id
AND Rat.Conversion_Type = 'Corporate'
AND Rat.From_Currency =
Decode(Lgr.Currency_Code, 'USD', 'BMD', Lgr.Currency_Code)
AND Rat.To_Currency = 'USD'
AND Rat.Conversion_Date = Trunc(Far.Date_Effective)
AND Lgr.Ledger_Id = Fabc.Set_Of_Books_Id
AND Thdr.Transaction_Header_Id = Far.Transaction_Header_Id_In
AND Lgs.Ledger_Category_Code(+) = 'PRIMARY'
---------------Infosys:Code For Project Name Change---------------
AND Fadb.asset_id=fainv.asset_id(+)
AND fainv.PROJECT_ASSET_LINE_ID=pala.PROJECT_ASSET_LINE_ID(+)
AND pala.PROJECT_ID=pab.PROJECT_ID(+)
----------------------------------------------------------
AND Substr(Lgs.Name(+), 1, 3) =
Substr(Far.Retirement_Type_Code, 8, 3)
AND UPPER(substr(Glp.Period_Name,1,4)||substr(Glp.Period_Name,7,8)) = CASE WHEN :PNAME IS NULL
THEN UPPER(substr(Glp.Period_Name,1,4)||substr(Glp.Period_Name,7,8))
ELSE UPPER(:PNAME)
END
AND NVL(Substr(Far.Retirement_Type_Code, 8, 3),'XX')= CASE WHEN :RCV_CO IS NULL
THEN NVL(Substr(Far.Retirement_Type_Code, 8, 3),'XX')
ELSE :RCV_CO
END
AND Fadb.Asset_Number IN ('96866200','96605209T','RO-905685')
AND Fabc.Book_Class = 'CORPORATE') Al1,
(SELECT Bkd.Category_Id Fa_Category_Id,
Bkd.Book_Type_Code Book_Name,
Ctl.Book_Class Book_Type,
Fac.Segment1 Fa_Category,
Fac.Segment2 Fa_Subcategory,
Fam.Life_In_Months Life_In_Months,
Fam.Name Deprn_Method,
Bkd.Depreciate_Flag Depreciate_Flag,
Fac.Summary_Flag Summary_Flag,
Fac.Enabled_Flag Enabled_Flag,
Fac.Owned_Leased Owned_Leased,
Fac.Category_Type Category_Type,
Fac.Capitalize_Flag Capitalize_Flag,
Fac.Start_Date_Active Start_Date_Active,
Fac.End_Date_Active End_Date_Active,
Fac.Inventorial Inventorial,
Ctb.ASSET_CLEARING_ACCOUNT_CCID Asset_Clearing_Acct,
Ctb.ASSET_COST_ACCOUNT_CCID Asset_Cost_Acct,
Ctb.WIP_CLEARING_ACCOUNT_CCID Cip_Clearing_Acct,
Ctb.WIP_COST_ACCOUNT_CCID Cip_Cost_Acct,
Ctb.DEPRN_EXPENSE_ACCOUNT_CCID Deprn_Expense_Acct,
Glcc5.Segment4 Deprn_Exp_Local_Acct,
Ctb.RESERVE_ACCOUNT_CCID Deprn_Reserve_Acct,
Ctb.REVAL_AMORT_ACCOUNT_CCID Reval_Amort_Acct,
Ctb.REVAL_RESERVE_ACCOUNT_CCID Reval_Reserve_Acct,
Bkd.Percent_Salvage_Value Percent_Salvage,
Bkd.Subcomponent_Life_Rule Subcomponent_Life_Rule,
Bkd.Special_Deprn_Limit_Amount Depreciation_Limit,
Bkd.Last_Update_Date Last_Updt_Date,
Bkd.Creation_Date Creation_Date,
Ctb.IMPAIR_EXPENSE_ACCOUNT_CCID Impair_Exp_Acct,
Ctb.IMPAIR_RESERVE_ACCOUNT_CCID Impair_Resv_Acct
FROM Fa_Category_Book_Defaults Bkd,
FA_METHODS Fam,
Fa_Categories_b Fac,
Fa_Category_Books Ctb,
Fa_Book_Controls Ctl,
Gl_Code_Combinations Glcc5
WHERE Fac.Category_Id = Bkd.Category_Id
AND Bkd.Category_Id = Ctb.Category_Id
AND Bkd.Book_Type_Code = Ctb.Book_Type_Code
AND Ctl.Book_Type_Code = Ctb.Book_Type_Code
AND Ctb.Deprn_Expense_Account_Ccid = Glcc5.Code_Combination_Id(+)
AND Bkd.Method_Id = Fam.Method_Id
/* AND Substr(Bkd.Book_Type_Code, 1, 3) NOT IN
('121',
'139',
'166',
'168',
'225',
'521',
'538',
'557',
'576',
'611',
'621',
'626')*/--commented out by Infosys to disable hardcoded filters
AND Ctl.Date_Ineffective IS NULL
AND Nvl(Bkd.End_Dpis, Trunc(SYSDATE)) >= Trunc(SYSDATE)) Al2, --- ( fusion table name - Fa_Category_Book_Defaults,Fa_Categories_b,Fa_Category_Books,Fa_Book_Controls,Gl_Code_Combinations )
(SELECT Glp.Period_Name Period_Mon_Yy,
Rat.From_Currency From_Curr,
Rat.To_Currency To_Curr,
Decode(Rat.Conversion_Type,
'Corporate',
'Corporate',
'1001',
'EOP',
'1002',
'AVG',
Rat.Conversion_Type) Conversion_Type,
Rat.Conversion_Type Conv_Type_Orig,
Rat.Conversion_Rate Conversion_Rate,
Rat.Conversion_Date Conversion_Date,
To_Number(To_Char(Rat.Conversion_Date, 'DD')) Conversion_Day
FROM Gl_Periods Glp, Gl_Daily_Rates Rat
WHERE Rat.Conversion_Date BETWEEN Glp.Start_Date AND Glp.End_Date
AND Glp.Period_Set_Name = '4-4-5'
--AND Glp.Period_Type = 'Month' --changed by infosys: value change in oracle fusion
AND UPPER(Glp.Period_Type) LIKE '%MONTH%') Al3
WHERE Al1.Asset_Category_Id = Al2.Fa_Category_Id(+)
AND Al1.Receiver_Book_Name = Al2.Book_Name(+)
AND Al1.Date_Retired = Al3.Conversion_Date(+)
AND Al1.Receiver_Curr = Al3.To_Curr(+)
AND Al1.Retirement_Categ = 'Intercompany Transfers'
AND Al1.Asset_Category <> 'LGAAP ASSETS'
AND Al3.From_Curr(+) = 'USD'
AND Al3.Conversion_Type(+) = 'Corporate'
--AND Al1.Receiver_Curr='USD'
No comments:
Post a Comment