D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

List of Customer ship to locations

  • 1.  List of Customer ship to locations

    SILVER CONTRIBUTOR
    Posted Aug 25, 2021 07:32 PM
    How can I get a list of Customer Ship to Locations including addresses?

    ------------------------------
    Alexis Barrios
    Fyffes Group Ltd
    Pompano Beach FL
    ------------------------------


  • 2.  RE: List of Customer ship to locations

    GOLD CONTRIBUTOR
    Posted Aug 26, 2021 08:23 AM
    Hi Alexis,
    Here is some code I whipped up when I had to extract all the customers and currently valid addresses.  Once you run this, you can filter on IsRoleDelivery = 1 to get only the addresses that are Ship-To.

    Select CT.Partition, CT.DataAreaId, CT.AccountNum
    , CT.Party, CT.CompanyChainId
    , DPT.Name As DPTName
    , DPL1.Location
    , DPL1.IsPrimary
    , DPL1.IsPrivate
    , DPL1.IsLocationOwner
    , DPL1.IsPrimaryTaxRegistration
    , DPL1.IsRoleBusiness
    , DPL1.IsRoleDelivery
    , DPL1.IsRoleHome
    , DPL1.IsRoleInvoice
    , DPL1.AttentionToAddressLine
    , LLLPA.Description
    , LLLPA.IsPostalAddress
    , LLLPA.ParentLocation
    , LLLPA.DUNSNumberRecId
    , LLLPA.ValidFrom
    , LLLPA.CountryRegionId
    , LLLPA.State
    , LLLPA.County
    , LLLPA.City
    , LLLPA.ZipCode
    , LLLPA.Street

    From CustTable As CT
    Left Join DirPartyTable As DPT
    On CT.Party = DPT.RecId
    Left Join (
    Select DPL.Partition, DPL.Party, DPL.Location
    , DPL.IsPrimary, DPL.IsPrivate, DPL.IsLocationOwner, DPL.IsPrimaryTaxRegistration
    , DPL.IsRoleBusiness, DPL.IsRoleDelivery, DPL.IsRoleHome, DPL.IsRoleInvoice
    , DPL.AttentionToAddressLine
    From DirPartyLocation As DPL

    ) As DPL1
    On DPT.Partition = DPL1.Partition
    And DPT.RecId = DPL1.Party
    Inner Join (
    /* LogisticsLocation has the Location Description
    LPA has the postal address */
    Select LL1.Partition, LL1.LocationId, LL1.RecId
    , LL1.Description, LL1.IsPostalAddress, LL1.ParentLocation, LL1.DUNSNumberRecId
    , LPA1.ValidFrom, LPA1.CountryRegionId, LPA1.State, LPA1.County, LPA1.City, LPA1.ZipCode, LPA1.Street
    From LogisticsLocation As LL1
    Inner Join (
    Select LPA.Partition, LPA.Location, LPA.ValidFrom
    , CountryRegionId, State, County, City, ZipCode, Street
    From LogisticsPostalAddress As LPA
    Where LPA.ValidFrom <= GetDate()
    And ( LPA.ValidTo = 0 Or LPA.ValidTo >= GetDate() )
    ) As LPA1
    On LL1.Partition = LPA1.Partition
    And LL1.RecId = LPA1.Location
    ) As LLLPA
    On DPL1.Partition = LLLPA.Partition
    And DPL1.Location = LLLPA.RecId
    Left Join CountryCode
    On LLLPA.CountryRegionId = CountryCode.CountryRegionId
    Order By CT.AccountNum Asc, DPL1.IsPrimary Desc, DPL1.Location Asc

    ------------------------------
    Barclay Hershey
    Financial Analyst
    Sugar Creek Packing Co
    Cincinnati OH
    ------------------------------



  • 3.  RE: List of Customer ship to locations

    SILVER CONTRIBUTOR
    Posted Aug 26, 2021 08:38 AM

    Thanks Mr. Hershey

     

    This is very complicated for me.  I was trying to find an out of the box report like the Customer base data report which only shows the main information

     






If you've found this thread useful, dive deeper into User Group community content by role