FA Asset Transfer Report

 FA Asset Transfer Report

 

SELECT Al1.Period_Name,

       Al1.Book_Name,

       Al1.Asset_Number,

       Al1.Asset_Description,

       Al1.Transaction_Units,

       Al1.Date_Effective_Xfer,

       Al1.Cost,

       Al1.Cost_Usd,

       Al1.Company,

       Al1.Glbl_Acct           Gac,

       Al1.Lcl_Acct            Lac,                    

       Al1.Dept,

       Al1.Project             Proj,

       Al1.Country,

       Al1.State,

       Al1.City,

       Al1.Building,

       Al1.Room,

       Al1.Assigned_To_Name Assigned_To_Emp,

       Al1.Assigned_To_Emp_Num,

       Al1.Last_Updt_Name,

       Al1.Last_Updt_Date

  FROM (SELECT Glp.Period_Name               Period_Name,

               Fth.Book_Type_Code            Book_Name,

               Fadd.Asset_Number             Asset_Number,

               Faddtl.Description            Asset_Description,

               Fdh.Transaction_Units         Transaction_Units,

               Fdh.Units_Assigned            Units_Assigned,

               Fdh.Date_Effective            Date_Effective_Xfer,

               Fdh.Last_Update_Date          Last_Updt_Date,

               Fab.Cost                      Cost,

               Fab.Adjusted_Cost             Adjusted_Cost,

               Fab.Original_Cost             Original_Cost,

               Famcb.Cost                    Cost_Usd,

               Famcb.Adjusted_Cost           Adjusted_Cost_Usd,

               Famcb.Original_Cost           Original_Cost_Usd,

               Glcc.Segment1                 Company,

               Glcc.Segment3                 Glbl_Acct,

               Glcc.Segment4                 Lcl_Acct,

               Glcc.Segment5                 Dept,

               Glcc.Segment8                 Project,

               Loc.Segment1                  Country,

               Loc.Segment2                  State,

               Loc.Segment3                  City,

               Loc.Segment4                  Building,

               Loc.Segment5                  Room,

               Fth.Transaction_Type_Code     Transaction_Type,

               Xpv.Full_Name Assigned_To_Name,

               Xpv.Employee_Number Assigned_To_Emp_Num,

               Usr.Username                  Last_Updt_Id,

               Usr.full_name                 Last_Updt_Name,

               Fth.Asset_Id                  Asset_Id,

               Fth.Transaction_Header_Id     Transaction_Header_Id,

               Fdh.Transaction_Header_Id_Out Transaction_Header_Id_Out,

               Fdh.Code_Combination_Id       Code_Combination_Id

          FROM Fa_Transaction_Headers  Fth,

               Fa_Additions_b          Fadd,

               Fa_Additions_Tl         Faddtl,

               Fa_Distribution_History Fdh,

               Fa_Books                Fab,

               Fa_Mc_Books             Famcb,

               Fa_Locations            Loc,

               Gl_Code_Combinations    Glcc,

               Gl_Periods              Glp,

               --Fnd_User                Usr,

   (

   select distinct ppf.person_id,

   pu.username,

   ppf.person_number Employee_Number,

   ppn.full_name

   from per_users pu,

per_all_people_f ppf,

        per_person_names_f ppn

where pu.person_id = ppf.person_id

and ppf.person_id = ppn.person_id

and trunc(sysdate) between trunc(nvl(ppf.effective_start_date,trunc(sysdate))) and trunc(nvl(ppf.effective_end_date,trunc(sysdate)))

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'

   ) Usr,

               --Xxon_Employee_View      xpv

   (

   select ppf.person_id,

   ppf.person_number Employee_Number,

   ppn.full_name

   from per_all_people_f ppf,

        per_person_names_f ppn

where ppf.person_id = ppn.person_id

and trunc(sysdate) between trunc(nvl(ppf.effective_start_date,trunc(sysdate))) and trunc(nvl(ppf.effective_end_date,trunc(sysdate)))

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'

   ) xpv

         WHERE Fth.Asset_Id = Fadd.Asset_Id

           AND Fth.Asset_Id = Faddtl.Asset_Id

           AND Faddtl.Language = 'US'

           AND Fth.Asset_Id = Fdh.Asset_Id

           AND Fth.Book_Type_Code = Fdh.Book_Type_Code

           AND Fth.Transaction_Type_Code = 'TRANSFER'

           AND Loc.Location_Id = Fdh.Location_Id

           AND Fth.Asset_Id = Fab.Asset_Id

           AND Fth.Book_Type_Code = Fab.Book_Type_Code

           AND Fab.Date_Ineffective IS NULL

           AND Fth.Asset_Id = Famcb.Asset_Id(+)

           AND Fth.Book_Type_Code = Famcb.Book_Type_Code(+)

           AND Famcb.Date_Ineffective IS NULL

           AND Fdh.Code_Combination_Id = Glcc.Code_Combination_Id(+)

           AND Trunc(Fdh.Last_Update_Date) BETWEEN Glp.Start_Date AND

               Glp.End_Date

           AND Trunc(Fth.Last_Update_Date) BETWEEN Glp.Start_Date AND

               Glp.End_Date

           AND Glp.Period_Set_Name = '4-4-5'

           AND Usr.Username = Fdh.Last_Updated_By

           AND Fdh.Assigned_To = Xpv.Person_Id(+)

   /*

           AND Trunc(SYSDATE) BETWEEN Xpv.Person_Effective_Start_Date(+) 

   AND Xpv.Person_Effective_End_Date(+)

           */

           AND ( case when Glp.Period_Name in (:p_Period_Name) then 1

              when (COALESCE(NULL,:p_Period_Name) is NULL ) then 1

  end = 1 )

  

           AND ( case when Fth.Book_Type_Code in (:p_book_type_code) then 1

              when (COALESCE(NULL,:p_book_type_code) is NULL ) then 1

  end = 1 )

          

           ) Al1

 ORDER BY Al1.Book_Name, 

                    Al1.Asset_Number