Hi,
Is it possible to add custom field to InventoryTransactionHist with a infromation Latest_Delivery_Date from CustomerOrderInvHead? I tought that I can match it by source_ref1 from customer order number.
Querry Designer shows me that there is no references. Anybody got some ideas?
InventoryTransactionHist reference to
+6
Hi
I'm on version 23R2 in case there are any differences.
It should be possible to add a custom attribute according to the entity itself. The most likely reason that there is no relation between the tables is because there is no direct relation between the tables (Many to many).
There could be many deliveries on an order and because of this multiple invoices as well.
This could cause you to retrieve an incorrect value.
Having said that, you can use a quick report to do an create a sample query.
In this case I think what you are looking for is something along the lines of:
SELECT Latest_delivery_date
FROM Customer_Order_Inv_Head COIH WHERE COIH.CREATORS_REFERENCE =’[ORDERNUMBER]’ AND
EXISTS(select * FROM Inventory_Transaction_Hist ITH ITH.SOURCE_REF1 = COIH.CREATORS_REFERENCE AND COIH.ORDER_NO AND ITH.SOURCE_REF_TYPE_DB = ‘CUST ORDER’ )
FETCH FIRST 1 ROWS ONLY
However again, I would emphasize on rethinking what exactly the goal is here.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.