Getting default bill to address SAP BUSINESS ONE

[former_member666435]

Hi all,

I need to get the default bill to address for the query below, i did a join which is not giving me the correct results, appreciate if someone can assist.

select 
t10."Customer / Supplier Nominal Code"
,t10."Customer Name"

,t10."Transaction Number"
,t10."Description"
--,sum(t10."Net Value") as "Net Value"
--,sum(t10."Tax") as "Tax"
,sum(t10."Gross Value") as "Gross Value"
,sum(t10."Balance") as "Balance"
,cast(t10."Order" as varchar) as "Order"
,t10."Address"
from
(




		select 
		 '' as "Customer / Supplier Nominal Code"
		,'Opening Balance' as "Customer Name"


		,'' as "Transaction Number"
		,'' as "Description"

		,concat(t7."address",' ',t7.City,' ',t7.ZipCode,' ',t7.County,' ',t7.Country) as "Address"
	
		--,'' as "Address"

		,COALESCE(T0."Debit",0)-COALESCE(T0."Credit",0) as "Gross Value"

		,COALESCE(T0.BalDueDeb,0)-COALESCE(T0.BalDueCred,0) as "Balance"

		,case when COALESCE(T0.BalDueDeb,0)-COALESCE(T0.BalDueCred,0) <> 0 then 'Y' 
		ELSE ''
		 end as "Recon Type",

		 'N' AS "Unreconciled"

		 ,1 as "Order"

		from JDT1 t0
		inner join OCRD t1 ON t1."CardCode" = T0."ShortName" 

		LEFT JOIN OPCH T2 ON T0."TransId"  = T2."TransId" AND T0."TransType"='18' 
		LEFT JOIN ORPC T3 ON T0."TransId" = T3."TransId" AND T0."TransType"='19' 

		left join OINV t4 on T0."TransId"  = T4."TransId" AND T0."TransType"='13' 
		LEFT JOIN ORIN T5 ON T0."TransId" = T5."TransId"  AND T0."TransType"='14' 

		left join crd1 T7 ON T7.CardCode=T0."ShortName" AND T7.Address=T1.BillToDef	





		UNION ALL






		select 
		 t1."CardCode" as "Customer / Supplier Nominal Code"
		,t1."CardName" as "Customer Name"


		,t0.Ref1 as "Transaction Number"


		,CASE WHEN t0.TransType in (13,18) THEN 'Invoice'
		WHEN t0.TransType in (19,14) THEN 'Credit Note' 
		WHEN t0.TransType in (46,24) THEN 'Payment' 
		WHEN t0.TransType = 30 THEN 'Journal' 
		ELSE 'Other' END AS "Description"

		,concat(t7."address",' ',t7.City,' ',t7.ZipCode,' ',t7.County,' ',t7.Country) as "Address"

		--,concat(t1."address",' ',t1.City,' ',t1.ZipCode,' ',t1.County,' ',t1.Country) as "Address"

	

		,COALESCE(T0."Debit",0)-COALESCE(T0."Credit",0) as "Gross Value"

		,COALESCE(T0.BalDueDeb,0)-COALESCE(T0.BalDueCred,0) as "Balance"

		,case when COALESCE(T0.BalDueDeb,0)-COALESCE(T0.BalDueCred,0) <> 0 then 'Y' 
		ELSE ''
		 end as "Recon Type",

		 'N' AS "Unreconciled"

		 ,2 as "Order"

		from JDT1 t0
		inner join OCRD t1 ON t1."CardCode" = T0."ShortName" 

		LEFT JOIN OPCH T2 ON T0."TransId"  = T2."TransId" AND T0."TransType"='18' 
		LEFT JOIN ORPC T3 ON T0."TransId" = T3."TransId" AND T0."TransType"='19' 

		left join OINV t4 on T0."TransId"  = T4."TransId" AND T0."TransType"='13' 
		LEFT JOIN ORIN T5 ON T0."TransId" = T5."TransId"  AND T0."TransType"='14' 
	
		inner join crd1 T7 ON T7.CardCode=T1.cardcode AND T7.Address=T1.BillToDef

		--left join crd1 T7 ON T7.CardCode=T0."ShortName" AND T7.Address=T1.BillToDef	
		



		
		union all





		select 
		 '' as "Customer / Supplier Nominal Code"
		,'Closing Balance' as "Customer Name"


		,'' as "Transaction Number"
		,'' as "Description"

		,concat(t7."address",' ',t7.City,' ',t7.ZipCode,' ',t7.County,' ',t7.Country) as "Address"
	
		--,'' as "Address"


		,COALESCE(T0."Debit",0)-COALESCE(T0."Credit",0) as "Gross Value"

		,COALESCE(T0.BalDueDeb,0)-COALESCE(T0.BalDueCred,0) as "Balance"

		,case when COALESCE(T0.BalDueDeb,0)-COALESCE(T0.BalDueCred,0) <> 0 then 'Y' 
		ELSE ''
		 end as "Recon Type",

		 'N' AS "Unreconciled"

		 ,3 as "Order"

		from JDT1 t0
		inner join OCRD t1 ON t1."CardCode" = T0."ShortName" 

		LEFT JOIN OPCH T2 ON T0."TransId"  = T2."TransId" AND T0."TransType"='18' 
		LEFT JOIN ORPC T3 ON T0."TransId" = T3."TransId" AND T0."TransType"='19' 

		left join OINV t4 on T0."TransId"  = T4."TransId" AND T0."TransType"='13' 
		LEFT JOIN ORIN T5 ON T0."TransId" = T5."TransId"  AND T0."TransType"='14'
		

		left join crd1 T7 ON T7.CardCode=T0."ShortName" AND T7.Address=T1.BillToDef	

	

		



) as t10



group by
t10."Customer / Supplier Nominal Code"
,t10."Customer Name"
,t10."Transaction Number"
,t10."Description"
,t10."Order"
,t10."Address"

order by 
t10."Order"

[mgregur · Accepted solution]

Hi,

you just need to add in your join to CRD1 condition AdresType = 'B'. This indicates it is Bill-to, as Ship-to and Bill-to can have the same ID.

BR,

Matija

[former_member666435 · Accepted solution]

Many thanks Matija, worked like a charm.

Best Regards,

Daniel