If you want to have one line per part and supplier we need to group the sub-select on supplier also. Supplier ID is called vendor_no so it would be like this. (part_no, vendor_no, arrival_date) in(Select part_no, vendor_no, min(arrival_date) arrival_date from IFSAPP.PURCHASE_RECEIPT_NEW where ARRIVAL_DATE > to_date('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') GROUP BY part_no, vendor_no) Hi Tomas This work great. I can see duplicates still happen, but the reason is multiple lines are received at exactly the same time. Sometimes with different order no., sometimes not. See attached pictures. Intended or not, this is useful for counting correct qty of first-delivery. Is it however also possible to exclude part no+supplier duplicates in this instance? In some cases I realise an arbitrary order no. would be chosen, but that would be okay in this case. Thanks again.
I see you are using APPS8. Then there is another view you shall use instead. RECEIPT_INFO was an example from the current latest version of IFS, APPS10. (part_no, arrival_date) in(Select part_no, min(arrival_date) arrival_date from IFSAPP.PURCHASE_RECEIPT_NEW where ARRIVAL_DATE > to_date('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') GROUP BY part_no) Hi Tomas, Wow, that actually worked. I guess I received the error message due to unrecognised statement/code after all:) I see it would be beneficial to be able to make this unique value list with multiple criteria. How can I build upon this code I’ve been given? For instance: I’d like a search result list to show the first delivery of PART_NO + Supplier since >2020. As one part can have multiple suppliers, I’d be interested in receiving a list of the first delivery of that part from multiple suppliers. Apologies for expanding upon my original inquiry.. Again, thanks a lot for your help so far Tomas and No
Could be different reasons, but it seems like Access issues. Try adding the applications owner before the view. Usually it’s ifsapp. Might be different for your company though. So instead of Receipt_info try ifsapp.Receipt_info. In case your company uses a different appowner replace ifsapp with whatever your Application owner is. Hi, I still receive the error message with IFSAPP.RECEIPT_INFO. However, I see I receive the same error message with whatever I type in the RECEIPT_INFO area in your code. If I replace it with ABC123 is still receive: User '[me]' is not allowed to access the database object 'ABC123'.
Sorry didn’t really read that you are using a saved query. Try this instead: (source_part_no, arrival_date) in (Select source_part_no, min(arrival_date) arrival_date from RECEIPT_INFO where ARRIVAL_DATE > to_date( '20200101000000', 'YYYYMMDDHH24:MI:SS' ) Group by source_part_no) i tried it on Apps10 where it’s source_part_no, if you are on a lower version just delete the source_ /Johannes Hi Johannes, I’m fairly new to SQL. I tried pasting: part_no, arrival_date) in (Select part_no, min(arrival_date) arrival_date from RECEIPT_INFO where ARRIVAL_DATE > to_date( '20200101000000', 'YYYYMMDDHH24:MI:SS' ) directly into the “SQL where expression” area, and selected Group on Part No in the table Group/Sum. I’m receiving the error code: User '[me]' is not allowed to access the database object 'RECEIPT_INFO'. Is this an issue of access, or maybe RECIPT_INFO is not a recognized statement?
Already have an account? Login
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.
