SBX - Search With Button

SBX - Forum Post Title

Sales Orders billing address

Microsoft Dynamics AX Forum

Adax asked a question on 15 May 2019 7:43 AM
My Badges

Question Status

Suggested Answer

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 :)

Reply
Adax responded on 17 May 2019 9:27 AM
My Badges

Any advices please :)

Reply
Satish Panwar responded on 17 May 2019 5:24 PM
Suggested Answer

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.

Reply
Adax responded on 24 May 2019 4:27 AM
My Badges
Suggested Answer

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


Reply
Satish Panwar responded on 17 May 2019 5:24 PM
Suggested Answer

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.

Reply
Adax responded on 24 May 2019 4:27 AM
My Badges
Suggested Answer

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


Reply

SBX - Two Col Forum

SBX - Migrated JS