= 10 -- released/completed change ) , Contact AS -- preferred contact is active Person, with Name in alphabetical order ( SELECT [Organization] ,[Name] ,[Phone1] AS [Phone] ,[Phone3] AS [AltPhone] ,[Phone4] AS [Pager] ,[PrimaryEmailId] AS [EmailAddress] ,[SecondaryEmailId] AS [IMAddress] FROM ( SELECT * ,ROW_NUMBER() OVER (PARTITION BY [Organization] ORDER BY [IsActive] DESC, [Name]) AS [RowNum] FROM Person_View Prsn ) OrgPrsn WHERE [RowNum] = 1 ) SELECT DISTINCT [Name] -- Required (TEXT41): The name of the vendor to be created. If it already exists, this line will attempt to create a new address. This must be the first column. ,[AddressName] -- Required (TEXT90): The name of the address to be created. Each address to be created must have a unique name. ,[AddressContact] -- Required (TEXT41): This is the attention/contact/firstline of the address ,[AddressType] -- Required (NMBR): The address type. Valid options are: [10 - Ship To | 20 - Bill To | 30 - Remit To | 40 - Home | 50 - Main Office] ,[IsDefault] -- Optional (BOOL): Specifies this address as a default address of its type. ,[Address] -- Optional (TEXT90): Street address ,[City] -- Optional (TEXT30): City ,[State] -- Optional (TEXT30): The state name or code. The state must already exist in your Fishbowl database. ,[Zip] -- Optional (TEXT30): The zip code ,[Country] -- Optional (TEXT64): The country name or code. ,[Main] -- Optional (TEXT30): The Main contact information. ,[Mobile] -- Optional (TEXT30): The Mobile contact information. ,[Fax] -- Optional (TEXT30): The Fax contact information. ,[Work] -- Optional (TEXT30): The Work contact information. ,[Home] -- Optional (TEXT30): The Home contact information. ,[Pager] -- Optional (TEXT30): The Pager contact information. ,[Email] -- Optional (TEXT30): The Email contact information. --,[Web] -- Optional (TEXT30): The Web contact information. ,[Other] -- Optional (TEXT30): The Other contact information. ,[CurrencyName] -- Optional (TEXT255): The default currency of the vendor. Only used if currency conversion is turned on. ,[CurrencyRate] -- Optional (NMBR): The currency rate of the vendor. Only used if currency conversion is turned on. Value of zero will use the default rate on the currency. --,[DefaultTerms] -- Optional (TEXT30): The default terms for the vendor. --,[DefaultCarrier] -- Optional (TEXT): The default carrier for the vendor. --,[DefaultShippingTerms] -- Optional (TEXT): The default shipping terms for the vendor. ,[Status] -- Optional (TEXT): The status of the vendor. Valid options are: [ Normal | Preferred | Hold PO | Hold Receipt | Hold All ] --,[AccountNumber] -- Optional (TEXT): Your account number with the vendor. ,[Active] -- Optional (BOOL): Specifies if the vendor is active. Valid entries are "true" and "false". --,[AlertNotes] -- Optional (TEXT90): This is displayed when the vendor is selected on the Purchase Order. --,[MinOrderAmount] -- Optional (NMBR): The minimum dollar amount that must be on a Purchase Order for the order to be issued. ,[URL] -- Optional (TEXT256): The URL for this vendor. FROM ( SELECT CASE WHEN LEN(ISNULL(Vndr.[Name],'')) = 0 THEN '' ELSE LEFT(Vndr.[Name],41) END AS [Name] ,RTRIM(LEFT(ISNULL(Vndr.[Name],''),80) + ' (Main)') AS [AddressName] ,CASE WHEN LEN(ISNULL(Cntct.[Name],'')) > 0 THEN Cntct.[Name] WHEN LEN(ISNULL(Vndr.[POBox],'')) > 0 THEN Vndr.[POBox] ELSE '(No contact)' END AS [AddressContact] ,'50' AS [AddressType] ,'true' AS [IsDefault] ,LEFT(ISNULL(Vndr.[Street],''),90) AS [Address] ,LEFT(ISNULL(Vndr.[City],''),30) AS [City] ,LEFT(ISNULL(Vndr.[Region],''),30) AS [State] ,LEFT(ISNULL(Vndr.[PostalCode],''),30) AS [Zip] ,LEFT(ISNULL(Cntry.[Name],''),30) AS [Country] ,LEFT(ISNULL(Vndr.[Phone1],''),30) AS [Main] ,LEFT(ISNULL(Vndr.[Phone3],''),30) AS [Mobile] ,LEFT(ISNULL(Vndr.[Phone2],''),30) AS [Fax] ,LEFT(ISNULL(Cntct.[Phone],''),30) AS [Work] ,LEFT(ISNULL(Cntct.[AltPhone],''),30) AS [Home] ,LEFT(COALESCE(Vndr.[PrimaryEmailId], Cntct.[EmailAddress], ''),30) AS [Email] ,LEFT(COALESCE(Vndr.[Phone4], Cntct.[Pager], ''),30) AS [Pager] ,'' AS [Web] ,LEFT(ISNULL(Cntct.[IMAddress], ''),30) AS [Other] ,LEFT(ISNULL(Crncy.[Name],''),255) AS [CurrencyName] ,'0' AS [CurrencyRate] ,'' AS [DefaultTerms] ,'' AS [DefaultCarrier] ,'' AS [DefaultShippingTerms] ,'Normal' AS [Status] ,'' AS [AccountNumber] ,'true' AS [Active] ,'' AS [AlertNotes] ,'0' AS [MinOrderAmount] ,LEFT(ISNULL(Vndr.[Uri],''),256) AS [URL] FROM Change_View Chg INNER JOIN AffectedItem_View AI ON AI.[ChangeAction] = Chg.[Id] INNER JOIN viewer.ItemView AffItm ON AffItm.[ItemId] = AI.[Item] INNER JOIN SourceItem_View SrcList ON SrcList.[ParentItem] = AI.[Item] AND SrcList.[FromNum] <= Chg.[ChangeNum] AND SrcList.[ToNum] > Chg.[ChangeNum] INNER JOIN BasicItemRevision_View VndrPrtRev ON VndrPrtRev.[ItemId] = SrcList.[BasicItem] AND VndrPrtRev.[FromNum] <= Chg.[ChangeNum] AND VndrPrtRev.[ToNum] > Chg.[ChangeNum] INNER JOIN Item_View VndrPrt ON VndrPrt.[Id] = VndrPrtRev.[ItemId] INNER JOIN Organization_View Vndr ON Vndr.[Id] = VndrPrt.[Owner] INNER JOIN Country_View Cntry ON Cntry.[Id] = Vndr.[Country] INNER JOIN Currency_View Crncy ON Crncy.[Id] = Vndr.[Currency] LEFT JOIN Contact Cntct ON Cntct.[Organization] = Vndr.[Id] LEFT JOIN AffectedItemOwners PrevItmOwnr ON PrevItmOwnr.[Owner] = VndrPrt.[Owner] AND PrevItmOwnr.[ChangeNum] < Chg.[ChangeNum] WHERE Chg.[Id] = @P1 AND AffItm.[IsHomeItem] = 'true' AND PrevItmOwnr.[Owner] IS NULL ) OwnerList ORDER BY [Name] ]]> Table2