FA_ASSET_TRANSFER_V

Details

• Schema: FUSION

• Object owner: FA

• Object type: VIEW

Columns

Name
FROM_DISTRIBUTION_ID BOOK_TYPE_CODE ASSET_ID FROM_CODE_COMBINATION_ID FROM_LOCATION_ID FROM_ASSIGNED_TO TO_DISTRIBUTION_ID TO_CODE_COMBINATION_ID TO_LOCATION_ID TO_ASSIGNED_TO TRANSFERRED_UNITS TRANSACTION_HEADER_ID TRANSACTION_TYPE_CODE TRANSACTION_SUBTYPE TRANSACTION_DATE_ENTERED DATE_EFFECTIVE

Query

SQL_Statement

SELECT SRC.Distribution_Id As From_Distribution_Id ,SRC.Book_Type_Code ,SRC.Asset_Id ,SRC.Code_Combination_Id As From_Code_Combination_Id ,SRC.Location_Id As From_Location_Id ,SRC.Assigned_To As From_Assigned_To ,DEST.Distribution_Id As To_Distribution_Id ,DEST.Code_Combination_Id As To_Code_Combination_Id ,DEST.Location_Id As To_Location_Id ,DEST.Assigned_To As To_Assigned_To ,DEST.Transferred_Units ,DEST.Transaction_Header_Id ,DEST.Transaction_Type_Code ,DEST.Transaction_Subtype ,DEST.Transaction_Date_Entered ,DEST.Date_Effective FROM ( SELECT FROM_DH.Distribution_Id ,FROM_DH.Book_Type_Code ,FROM_DH.Asset_Id ,FROM_DH.Code_Combination_Id ,FROM_DH.Location_Id ,FROM_DH.Assigned_To ,FROM_DH.Transaction_Header_Id_In ,FROM_DH.Transaction_Header_Id_Out ,FROM_DH.Transaction_Units FROM Fa_Distribution_History FROM_DH ,Fa_Transaction_Headers TH WHERE FROM_DH.Transaction_Header_Id_Out = TH.Transaction_Header_Id AND FROM_DH.Transaction_Units < 0 ) SRC, ( SELECT FROM_DH.Distribution_Id ,FROM_DH.Book_Type_Code ,FROM_DH.Asset_Id ,FROM_DH.Code_Combination_Id ,FROM_DH.Location_Id ,FROM_DH.Assigned_To ,FROM_DH.Transaction_Header_Id_In ,FROM_DH.Transaction_Header_Id_Out ,FROM_DH.Transaction_Units As Transferred_Units ,TH.Transaction_Header_Id ,TH.Transaction_Type_Code ,TH.Transaction_Subtype ,TH.Transaction_Date_Entered ,TH.Date_Effective FROM Fa_Distribution_History FROM_DH ,Fa_Transaction_Headers TH WHERE FROM_DH.Transaction_Header_Id_Out = TH.Transaction_Header_Id AND FROM_DH.Transaction_Units > 0 Union SELECT TO_DH.Distribution_Id ,TO_DH.Book_Type_Code ,TO_DH.Asset_Id ,TO_DH.Code_Combination_Id ,TO_DH.Location_Id ,TO_DH.Assigned_To ,TO_DH.Transaction_Header_Id_In ,TO_DH.Transaction_Header_Id_Out ,TO_DH.Units_Assigned As Transferred_Units ,TH.Transaction_Header_Id ,TH.Transaction_Type_Code ,TH.Transaction_Subtype ,TH.Transaction_Date_Entered ,TH.Date_Effective FROM Fa_Distribution_History FROM_DH ,Fa_Distribution_History TO_DH ,Fa_Transaction_Headers TH WHERE TO_DH.Transaction_Header_Id_In = TH.Transaction_Header_Id AND FROM_DH.Transaction_Header_Id_Out = TO_DH.Transaction_Header_Id_In AND FROM_DH.transaction_units < 0 AND (TO_DH.Assigned_To , TO_DH.Code_Combination_Id , TO_DH.Location_Id) NOT IN (SELECT DH.Assigned_to , DH.Code_Combination_Id , DH.Location_Id From Fa_Distribution_History DH WHERE DH.Transaction_Header_Id_Out = TO_DH.Transaction_Header_Id_In AND DH.Transaction_Units IS NOT NULL) ) DEST WHERE SRC.Transaction_Header_Id_Out = DEST.Transaction_Header_Id_In AND DEST.transaction_type_code = 'TRANSFER'