I am looking to include the POHeader.OrderDate in the ARForm RDD. InvcHead has the PONum field but it stores it in x(50) while the POHeader stores it as an integer. This creates this error:
It does not seem like you can cast either field within the RDD Join. Does anyone know of a way to get POHeader into the RDD maybe by traversing through a bunch of tables (I have started trying to figure out a way to get there but have not been successful so far)?
Curious why you are trying to join the Customer PO number to your PONumber.
Do you want to do the join to the PO that is linked to a buy to order OrderRelease?
If so, look into the PORelNum buyto field on the OrderRel table. to link the PONum, POLIne, and PORelNum.
Well let’s talk through that because I am actually unsure. I am looking for the Order Date on a PO that will have been created by EDI, turned into a Sales Order on our side, and then invoiced. This order date is being sent out as part of an EDI document that is the invoice we are sending the customer (they require us to include the PO Number and Date of the PO as part of the Invoice document).
Since it comes through EDI, would the Sales Order Date (OrderHed.OrderDate) match the PO Date? If so, it sounds like you can get everything you need from the Sales Order.
I did just have that thought I am going to go look and see if that works how we think it does.
yea… it sounds like you are mixing up document types…
In Epicor Kinetic, a Sales Order is something that your CUSTOMER orders from you using a PO, but that PO is not stored in the purchasing system.
a Purchase order in the Purchasing system is something that you are BUYING from a VENDOR. It might be for a customer.
ALSO… one other interesting comment on joining Invoice header to get order data… note that Epicor Kinetic supports having MULTIPLE ORDERS (and therefore Multiple POs) on one invoice… each line item points to a specific order/line. so looking at the invoice header could be a big mistake in some cases.
EDI shudders
The EDI tables in Epicor are DemandHead, DemaindDetail IIRC they aren’t the normal PO tables. You can also use UD Column Map to map fields from the Demand tables to Order tables. Maybe use that to get the customer’s PONum to the Order table.
EDI is not bad and the links to the Demand tables are kept in the Order tables, but the DemandHead and DemandDetail tables do not have any dates in them, but the DemandLog tables does.
All of the joins below work, but only the uncommented ones are needed to get the date processed from EDI.
I did my own EDI processor, so I know except in rare failures the OrderHed.OrderDate is going to be the date and I would just use it.
select top 10 ih.InvoiceNum,dl.SysDate
from erp.InvcHead ih
--inner join erp.InvcDtl id on ih.InvoiceNum = id.InvoiceNum
inner join erp.OrderHed oh on ih.OrderNum=oh.OrderNum
--inner join erp.OrderDtl od on id.OrderNum=od.OrderNum and id.OrderLine=od.OrderLine
--inner join erp.DemandHead dh on od.DemandHeadSeq=dh.DemandHeadSeq
--inner join erp.DemandDetail dd on od.DemandHeadSeq=dd.DemandHeadSeq and od.DemandDtlSeq=od.DemandDtlSeq
inner join erp.DemandLog dl on oh.DemandHeadSeq=dl.DemandHeadSeq