BAQ Help-Valid Sold to/Bill to/Ship tos for a PerCon

Epicor ERP 10
1

Having a hard time wrapping my head about what I need to do to accomplish this task.

I need to create a BAQ that ingests a PerConID and then outputs a list of all valid sold to/ship to/bill to configurations. This is part of an integration with another system, and we are trying to condense 3 calls to 1. The current system first gets a list of available “links” to customers from the CustCnt table. From that selection, which chooses a “Sold To Customer Number”, the next call is to get available Ship Tos for the given sold to, and the third call is to get available Bill Tos for the selected Sold To. We are running into some potential issues with this method however (having a loopback API needed for the browser to be able to call it in the form without a page reload) and wanted to instead have a single call with all the possible configurations, then use filtering in the browser to drill into the selection.

2

Make 3 Union queries?
I’m assuming you want to find all the SoldTos ShipToes and BillToes that Percon is related with?

I would do 3 or 4 different queries and have 1 calculated field in each query so I know whether it was a Sold to a Ship to or a Bill To.

1 Like
3

You can also use the PerconLink table to find all those places. ContextLink (tells you which table) and LinkSysRowID is a direct link to that Entities SysRowID.

4

Ok so the entry point is probably the CustCnt table filtered by PerConID, then union with Customer (sold to), union with customer (Bill To), union with Ship To?

5

nah see my last reply I’d use the PerConLink table it has all the links in one place. Just use that table and filter by ContextLink of (Customer, ShipTo etc)

1 Like
6

Hmm, might be doing something wrong, but I can’t seem to get it to join linksysrowID to the entities’ sysrowID?
image
I’m having a dumb day, so bear with me

This is wild; none of the LinkSysRowIDs in the PerConLnk table are valid…So that table has almost 27K records but no valid joins on the context link tables

7

it links to CustCnt (for customer) not Customer

8

The table name and context that this PerConLnk is related to:
Customer(If related to CustCnt and CustCnt.ShipTonum = “”)
Ship to(If related to CustCnt and CustCnt.ShipTonum <> “”)
Supplier((If related to VendCnt and VendCnt.PurPoint = “”)
Supplier PP((If related to VendCnt and VendCnt.PurPoint <> “”)
Employee(If related to EmpBasic)
Work force (If related to SalesRep)
Buyer(If related to PurAgent)

1 Like
9

Not sure this PerConLnk table gives me everything, since it’s reliant on the link to CustCnt. This gives me the same data as my initial query, which is on CustCnt. The tricky part is then recursively building out a list of {foreach customer in CustCnt}, build a list of that customers ship to’s and bill tos, which may or may not exist in the PerConLnk table…

That’s where I’m thinking the union approach is cleaner

10

I was way overthinking it. I built it out with CTEs and everything, then realized simple subqueries works just fine too (a little slower, but still)

select distinct
	'PerConID' = cnt.PerConID
	,'PerConName' = cnt.Name
	
	,'SoldToCustID' = c.CustID
	,'SoldToName' = c.Name
	,'SoldToCustNum' = c.CustNum

	,'ShipToShipToNum' = ShipTo.ShipToNum
	,'ShipToName' = ShipTo.Name	
	,'ShipToAddress1' = ShipTo.Address1
	,'ShipToAddress2' = ShipTo.Address2
	,'ShipToCity' = ShipTo.City
	,'ShipToState' = ShipTo.State
	,'ShipToZip' = ShipTo.Zip
	,'ShipToCountry' = ShipTo.Country


	,'BTCustID' = BillTo.CustID
	,'BTName' = BillTo.Name
	,'BTCustNum' = billTo.BTCustNum


from Epicor10LIVE.Erp.CustCnt cnt
	inner join 
		(
			select 
				st.CustNum
				,st.ShipToNum
				,st.Name
				,st.Address1
				,st.Address2
				,st.City
				,st.State
				,st.ZIP
				,st.Country
			from Epicor10LIVE.Erp.ShipTo st
		) ShipTo
		on ShipTo.CustNum = cnt.CustNum

	inner join Epicor10LIVE.Erp.Customer c
	on cnt.Company = c.Company
	and cnt.CustNum = c.CustNum
	
	inner join 
		(
			select 
				bt.BTCustNum
				,bt.CustNum
				,c.CustID
				,c.Name
				,c.Address1
				,c.Address2
				,c.Address3
				,c.City
				,c.State
				,c.Zip		
			from Epicor10LIVE.Erp.CustBillTo bt
					inner join Epicor10LIVE.Erp.Customer stc
				on stc.Company = bt.Company
				and stc.CustNum = bt.CustNum

				inner join Epicor10LIVE.Erp.Customer c
				on c.Company = bt.Company
				and c.CustNum = bt.BTCustNum			
			union
				select
		
					'BTCustNum' = c.CustNum
					,'CustNum' = c.CustNum
					,'BTCustID' = c.CustID
					,'BTName' = 'Same as Sold To'
					,c.Address1
					,c.Address2
					,c.Address3
					,c.City
					,c.State
					,c.Zip
				from Epicor10LIVE.Erp.Customer c
		) BillTo
		 on BillTo.CustNum = c.CustNum

where cnt.PerConID = '1878'

This builds out all available valid configurations for a given percon, assuming that percon has been linked to the customer in the CustCnt table. From there, the table can be consumed to build out the configurations of sold to/ship to/bill to

2 Likes