You’re offline. This is a read only version of the page.
Hello Pardon the Nuking of the screenshot,
I inherited a system that has, what seems to be, an override for Primary Bill To and Primary Ship To on the Sales Customer Detail Zoom on the Sales order.
The logic behind this row is that the system seems to use the Primary Bill To and Primary Ship To unless the field is populated, then it is overwritten with the new field.
The issue is that I was tasked to pull a query of all sales orders with their bill to and ship to addresses, and I cant seem to find where the heck these fields are held on the database!
I checked SOP10100 and SOP30200 and it seems like those only have primarys(PRBTADD and PRSTADD), which makes me think this is a custom field created, but what table would a field like this be held?
Screenshot of the two fields in question:
*This post is locked for comments
I have the same question (0)
This looks standard functionality to me.
Customer addresses are held in table RM00102
Customer records are in table RM00101
You need to join the composite key of both the customer number and PRBTADD or PRSTADD field in the order (SOP10100 or SOP30200) to RM00102 to get the address. These fields are just a pointer to the address in the "address book" that can be updated at any time and immediately effect where those documents will go.
The order itself takes a copy of the ship to address specified by the primary ship to address from the address book into the order, that can then be amended on a per order basis by the user. These changes do not reflect back in to the address book, and changes to the original address in the address book do not reflect back into the order. They are disassociated at the point the order is created.
Primary statement, shipto, bill to address codes are all in RM00101 for the customer.
So if you are doing retrospective reporting those billto addresses may have been edited since the order was processed.
Please also note that order lines can have ship addresses too, say half order needs to go to one site and the rest to another, so you may need to join into the SOP10200 and pull the address from there if this functionality is used. Again you will find a copy of the shipping address at the line level here. Older versions of GP may not have some of these fields (noticed your tags).
Something like:
You may need to use SOP10200 instead of SOP10100 if your company does use line level shipping addresses...
WITH CTE_WorkAndHist
AS (
SELECT SOPTYPE
,SOPNUMBE
,CUSTNMBR
,PRSTADCD
,PRBTADCD
,CNTCPRSN
,ShipToName
,ADDRESS1
,ADDRESS2
,ADDRESS3
,CITY
,[STATE]
,COUNTRY
,ZIPCODE
FROM SOP10100
UNION ALL
SELECT SOPTYPE
,SOPNUMBE
,CUSTNMBR
,PRSTADCD
,PRBTADCD
,CNTCPRSN
,ShipToName
,ADDRESS1
,ADDRESS2
,ADDRESS3
,CITY
,[STATE]
,COUNTRY
,ZIPCODE
FROM SOP30200
)
SELECT TOP(100)
CTE_WorkAndHist.SOPTYPE
, CTE_WorkAndHist.SOPNUMBE
,CTE_WorkAndHist.CUSTNMBR
,CTE_WorkAndHist.CNTCPRSN as OrdershipCNTCPRSN
,CTE_WorkAndHist.ShipToName as OrdershipShipToName
,CTE_WorkAndHist.ADDRESS1 as OrdershipADDRESS1
,CTE_WorkAndHist.ADDRESS2 as OrdershipADDRESS2
,CTE_WorkAndHist.ADDRESS3 as OrdershipADDRESS3
,CTE_WorkAndHist.CITY as OrdershipCITY
,CTE_WorkAndHist.[STATE] as OrdershipSTATE
,CTE_WorkAndHist.COUNTRY as OrdershipCOUNTRY
,CTE_WorkAndHist.ZIPCODE as OrdershipZIPCODE
,BillTo.CNTCPRSN as BilltoCNTCPRSN
,BillTo.ShipToName as BilltoShipToName
,BillTo.ADDRESS1 as BilltoADDRESS1
,BillTo.ADDRESS2 as BilltoADDRESS2
,BillTo.ADDRESS3 as BilltoADDRESS3
,BillTo.CITY as OBilltoCITY
,BillTo.[STATE] as BilltoSTATE
,BillTo.COUNTRY as BilltoCOUNTRY
,BillTo.ZIP as BilltoZIPCODE
,ShipTo.CNTCPRSN as ShiptoCNTCPRSN
,ShipTo.ShipToName as ShiptoShipToName
,ShipTo.ADDRESS1 as ShiptoADDRESS1
,ShipTo.ADDRESS2 as ShiptoADDRESS2
,ShipTo.ADDRESS3 as ShiptoADDRESS3
,ShipTo.CITY as ShiptotoCITY
,ShipTo.[STATE] as ShiptoSTATE
,ShipTo.COUNTRY as ShiptoCOUNTRY
,ShipTo.ZIP as ShiptoZIPCODE
FROM CTE_WorkAndHist
LEFT JOIN RM00102 AS BillTo ON CTE_WorkAndHist.CUSTNMBR = BillTo.CUSTNMBR
AND CTE_WorkAndHist.PRBTADCD = BillTo.ADRSCODE
LEFT JOIN RM00102 AS ShipTo ON CTE_WorkAndHist.CUSTNMBR = ShipTo.CUSTNMBR
AND CTE_WorkAndHist.PRSTADCD = ShipTo.ADRSCODE
Share
Report
All responses (
Answers (
5,713
