web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Sales Orders billing a...
Microsoft Dynamics AX (Archived)

Sales Orders billing address

(0) ShareShare
ReportReport
Posted on by AdnDalhi 336

Hi,

I have a quick technical question about Sales Orders and address,

I need to retrieve the billing or the Invoice address of all my sales orders.

I found the delivery address on SO form in AX 2012, but i can't find the invoice address related to each sales order.

If anyone explain to me the logic behind in AX please, i will be gratful.

Now im using a SQL Query which make 2 major test to each sales Order

  1. Check if the current SO have an Invoice account. If YES > then i check if there is an invoice address for this invoice account, if not i take the business address.
  2. Check if the current SO don't have an invoice account, in this case automatically i check if the customer account have an invoice address, if he don't have i take the business address.

Could anyone please explain to me if this logic is correct or not.

and if it's possible to help by providing a SQL Query which can help to retrieve the Invoice address of Sales Orders.

Thanks in advance :)

*This post is locked for comments

I have the same question (0)
  • AdnDalhi Profile Picture
    AdnDalhi 336 on at
    RE: Sales Orders billing address

    Any advices please :)

  • Suggested answer
    Satish Panwar Profile Picture
    Satish Panwar 14,671 Moderator on at
    RE: Sales Orders billing address

    Here is the sequence:

    1. If invoice type address then it's Invoice address

    2. if no invoice type address present then delivery address

    If no invoice and no delivery type exists and primary address exists, then primary address is used. If order is already invoiced then pull address based on custInvoiceJour.InvoicePostalAddress and not from sales order.

  • Suggested answer
    AdnDalhi Profile Picture
    AdnDalhi 336 on at
    RE: Sales Orders billing address

    Hello, thanks for you help, i would like to mark this ticket as resolved also,

    And here is my SQL Query used for this question, i retrieve all SO invoices for last 2 years.

    by checking the case if the customer have an invoice account or not, and if have Invoice address or not.

    SELECT

sales.SALESID			AS [Sales order],
sales.CUSTACCOUNT		AS [Cust account],
sales.INVOICEACCOUNT	AS [Invoice account],

(CASE WHEN sales.INVOICEACCOUNT != sales.CUSTACCOUNT AND sales.INVOICEACCOUNT != ''

	THEN 
	
	(SELECT CASE WHEN (SELECT TOP 1  LOGISTICSLOCATION.DESCRIPTION +', '+ LOGISTICSPOSTALADDRESS.STREET +', '+ LOGISTICSPOSTALADDRESS.ADDRESS +', '+ LOGISTICSPOSTALADDRESS.ZIPCODE +', '+ LOGISTICSPOSTALADDRESS.CITY +', '+ LOGISTICSPOSTALADDRESS.COUNTRYREGIONID
						 
	FROM DIRPARTYLOCATION, DIRPARTYLOCATIONROLE, LOGISTICSLOCATION, LOGISTICSLOCATIONROLE,LOGISTICSPOSTALADDRESS

	WHERE LOGISTICSPOSTALADDRESS.LOCATION = LOGISTICSLOCATION.RecId
	AND LOGISTICSLOCATIONROLE.TYPE = '1'	--9 Business / 1 Invoice > 
	AND DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
	AND DIRPARTYLOCATION.PARTY = (SELECT TOP 1 CUSTTABLE.PARTY FROM CUSTTABLE WHERE CUSTTABLE.ACCOUNTNUM = sales.INVOICEACCOUNT AND CUSTTABLE.DATAAREAID = '304' AND sales.DATAAREAID = '304')
	AND DIRPARTYLOCATION.RECID = DIRPARTYLOCATIONROLE.PARTYLOCATION
	AND DIRPARTYLOCATIONROLE.LOCATIONROLE = LOGISTICSLOCATIONROLE.RECID)IS NULL
	--AND DIRPARTYLOCATION.ISPRIMARY	= 1)  
		
	THEN
		
	(SELECT TOP 1 LOGISTICSLOCATION.DESCRIPTION +', '+ LOGISTICSPOSTALADDRESS.STREET +', '+ LOGISTICSPOSTALADDRESS.ADDRESS +', '+ LOGISTICSPOSTALADDRESS.ZIPCODE +', '+ LOGISTICSPOSTALADDRESS.CITY +', '+ LOGISTICSPOSTALADDRESS.COUNTRYREGIONID
						 
	FROM DIRPARTYLOCATION, DIRPARTYLOCATIONROLE, LOGISTICSLOCATION, LOGISTICSLOCATIONROLE,LOGISTICSPOSTALADDRESS

	WHERE LOGISTICSPOSTALADDRESS.LOCATION = LOGISTICSLOCATION.RecId
	AND LOGISTICSLOCATIONROLE.TYPE = '9'	--9 Business / 1 Invoice > 
	AND DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
	AND DIRPARTYLOCATION.PARTY = (SELECT TOP 1 CUSTTABLE.PARTY FROM CUSTTABLE WHERE CUSTTABLE.ACCOUNTNUM = sales.INVOICEACCOUNT AND CUSTTABLE.DATAAREAID = '304' AND sales.DATAAREAID = '304')
	AND DIRPARTYLOCATION.RECID = DIRPARTYLOCATIONROLE.PARTYLOCATION
	AND DIRPARTYLOCATIONROLE.LOCATIONROLE = LOGISTICSLOCATIONROLE.RECID)
	--AND DIRPARTYLOCATION.ISPRIMARY	= 1)
	
	ELSE
	
	(SELECT TOP 1  LOGISTICSLOCATION.DESCRIPTION +', '+ LOGISTICSPOSTALADDRESS.STREET +', '+ LOGISTICSPOSTALADDRESS.ADDRESS +', '+ LOGISTICSPOSTALADDRESS.ZIPCODE +', '+ LOGISTICSPOSTALADDRESS.CITY +', '+ LOGISTICSPOSTALADDRESS.COUNTRYREGIONID
						 
	FROM DIRPARTYLOCATION, DIRPARTYLOCATIONROLE, LOGISTICSLOCATION, LOGISTICSLOCATIONROLE,LOGISTICSPOSTALADDRESS

	WHERE LOGISTICSPOSTALADDRESS.LOCATION = LOGISTICSLOCATION.RecId
	AND LOGISTICSLOCATIONROLE.TYPE = '1'	--9 Business / 1 Invoice > 
	AND DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
	AND DIRPARTYLOCATION.PARTY = (SELECT TOP 1 CUSTTABLE.PARTY FROM CUSTTABLE WHERE CUSTTABLE.ACCOUNTNUM = sales.INVOICEACCOUNT AND CUSTTABLE.DATAAREAID = '304' AND sales.DATAAREAID = '304')
	AND DIRPARTYLOCATION.RECID = DIRPARTYLOCATIONROLE.PARTYLOCATION
	AND DIRPARTYLOCATIONROLE.LOCATIONROLE = LOGISTICSLOCATIONROLE.RECID)
	--AND DIRPARTYLOCATION.ISPRIMARY	= 1)
	
	END)
	
	ELSE
	
	(SELECT CASE WHEN (SELECT TOP 1  LOGISTICSLOCATION.DESCRIPTION +', '+ LOGISTICSPOSTALADDRESS.STREET +', '+ LOGISTICSPOSTALADDRESS.ADDRESS +', '+ LOGISTICSPOSTALADDRESS.ZIPCODE +', '+ LOGISTICSPOSTALADDRESS.CITY +', '+ LOGISTICSPOSTALADDRESS.COUNTRYREGIONID
						 
	FROM DIRPARTYLOCATION, DIRPARTYLOCATIONROLE, LOGISTICSLOCATION, LOGISTICSLOCATIONROLE,LOGISTICSPOSTALADDRESS

	WHERE LOGISTICSPOSTALADDRESS.LOCATION = LOGISTICSLOCATION.RecId
	AND LOGISTICSLOCATIONROLE.TYPE = '1'	--9 Business / 1 Invoice > 
	AND DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
	AND DIRPARTYLOCATION.PARTY = (SELECT TOP 1 CUSTTABLE.PARTY FROM CUSTTABLE WHERE CUSTTABLE.ACCOUNTNUM = sales.CUSTACCOUNT AND CUSTTABLE.DATAAREAID = '304' AND sales.DATAAREAID = '304')
	AND DIRPARTYLOCATION.RECID = DIRPARTYLOCATIONROLE.PARTYLOCATION
	AND DIRPARTYLOCATIONROLE.LOCATIONROLE = LOGISTICSLOCATIONROLE.RECID)IS NULL 
	--AND DIRPARTYLOCATION.ISPRIMARY	= 1) 
		
	THEN
		
	(SELECT TOP 1 LOGISTICSLOCATION.DESCRIPTION +', '+ LOGISTICSPOSTALADDRESS.STREET +', '+ LOGISTICSPOSTALADDRESS.ADDRESS +', '+ LOGISTICSPOSTALADDRESS.ZIPCODE +', '+ LOGISTICSPOSTALADDRESS.CITY +', '+ LOGISTICSPOSTALADDRESS.COUNTRYREGIONID
						 
	FROM DIRPARTYLOCATION, DIRPARTYLOCATIONROLE, LOGISTICSLOCATION, LOGISTICSLOCATIONROLE,LOGISTICSPOSTALADDRESS

	WHERE LOGISTICSPOSTALADDRESS.LOCATION = LOGISTICSLOCATION.RecId
	AND LOGISTICSLOCATIONROLE.TYPE = '9'	--9 Business / 1 Invoice > 
	AND DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
	AND DIRPARTYLOCATION.PARTY = (SELECT TOP 1 CUSTTABLE.PARTY FROM CUSTTABLE WHERE CUSTTABLE.ACCOUNTNUM = sales.CUSTACCOUNT AND CUSTTABLE.DATAAREAID = '304' AND sales.DATAAREAID = '304')
	AND DIRPARTYLOCATION.RECID = DIRPARTYLOCATIONROLE.PARTYLOCATION
	AND DIRPARTYLOCATIONROLE.LOCATIONROLE = LOGISTICSLOCATIONROLE.RECID)
	--AND DIRPARTYLOCATION.ISPRIMARY	= 1)
	
	ELSE
	
	(SELECT TOP 1  LOGISTICSLOCATION.DESCRIPTION +', '+ LOGISTICSPOSTALADDRESS.STREET +', '+ LOGISTICSPOSTALADDRESS.ADDRESS +', '+ LOGISTICSPOSTALADDRESS.ZIPCODE +', '+ LOGISTICSPOSTALADDRESS.CITY +', '+ LOGISTICSPOSTALADDRESS.COUNTRYREGIONID
						 
	FROM DIRPARTYLOCATION, DIRPARTYLOCATIONROLE, LOGISTICSLOCATION, LOGISTICSLOCATIONROLE,LOGISTICSPOSTALADDRESS

	WHERE LOGISTICSPOSTALADDRESS.LOCATION = LOGISTICSLOCATION.RecId
	AND LOGISTICSLOCATIONROLE.TYPE = '1'	--9 Business / 1 Invoice > 
	AND DIRPARTYLOCATION.LOCATION = LOGISTICSLOCATION.RECID
	AND DIRPARTYLOCATION.PARTY = (SELECT TOP 1 CUSTTABLE.PARTY FROM CUSTTABLE WHERE CUSTTABLE.ACCOUNTNUM = sales.CUSTACCOUNT AND CUSTTABLE.DATAAREAID = '304' AND sales.DATAAREAID = '304')
	AND DIRPARTYLOCATION.RECID = DIRPARTYLOCATIONROLE.PARTYLOCATION
	AND DIRPARTYLOCATIONROLE.LOCATIONROLE = LOGISTICSLOCATIONROLE.RECID)
	--AND DIRPARTYLOCATION.ISPRIMARY	= 1)
	
	END)
	END)

	
	AS [Invoice address]
	
					

FROM SALESTABLE sales
WHERE sales.CREATEDDATETIME >= DATEADD(year,-2,GETDATE())
AND sales.DATAAREAID = '304'

ORDER BY sales.CREATEDDATETIME, sales.SALESID


Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

#2
Guy Terry Profile Picture

Guy Terry 2 Moderator

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans