Since I am not so strong in SQL, I tried query builder to come up with a report with the below columns for Inventory parts
Part No
Part Description
Site = X only
Created Date (on or after 1-1-2020)
Has 5 or more transactions
“ Primary supplier for the purchase part” with Supplier name and supplier ID (2 columns)
However, for the number of transactions column I am facing a challenge since there is no view for me to chose in query builder. So I omitted that column, but still need the report sorted for parts with only for 5 or more transactions.
How should I do? an advise with the right SQL statement would also help.
Best answer by Tj12
Thanks @Tj12. Created Date (on or after 1-1-2020) is not included in it right?
Try This
select t1.PART_NO, t1.DESCRIPTION, t1.CONTRACT, t2.VENDOR_NO, SUPPLIER_API.Get_Vendor_Name(VENDOR_NO) as "Supplier Name", (select Count(TRANSACTION_ID) from INVENTORY_TRANSACTION_HIST2 t3 where t1.part_no = t3.part_no and t1.contract = t3.contract) as "Trans Count", t1.create_date from INVENTORY_PART t1, PURCHASE_PART_SUPPLIER t2 where t1.PART_NO = t2.PART_NO and t1.CONTRACT = t2.CONTRACT and t2.PRIMARY_VENDOR_DB = 'Y' and t1.CONTRACT = 'X' and (select Count(TRANSACTION_ID) from INVENTORY_TRANSACTION_HIST2 t3 where t1.part_no = t3.part_no and t1.contract = t3.contract) >= 5 and To_char(t1.create_date,'YYYY-MM-DD') >= '2020-01-01'
Not sure about the query builder. But if you have access to build quick reports or SQL query tool below will give the data you want
I also could not find an exact view relate to inv transaction his in query builder. hope someone will comment about that
select t1.PART_NO, t1.DESCRIPTION, t1.CONTRACT, t2.VENDOR_NO, SUPPLIER_API.Get_Vendor_Name(VENDOR_NO) as "Supplier Name", (select Count(TRANSACTION_ID) from INVENTORY_TRANSACTION_HIST2 t3 where t1.part_no = t3.part_no and t1.contract = t3.contract) as "Trans Count" from INVENTORY_PART t1, PURCHASE_PART_SUPPLIER t2 where t1.PART_NO = t2.PART_NO and t1.CONTRACT = t2.CONTRACT and t2.PRIMARY_VENDOR_DB = 'Y' and t1.CONTRACT = 'X' and (select Count(TRANSACTION_ID) from INVENTORY_TRANSACTION_HIST2 t3 where t1.part_no = t3.part_no and t1.contract = t3.contract) >= 5
Thanks @Tj12. Created Date (on or after 1-1-2020) is not included in it right?
Try This
select t1.PART_NO, t1.DESCRIPTION, t1.CONTRACT, t2.VENDOR_NO, SUPPLIER_API.Get_Vendor_Name(VENDOR_NO) as "Supplier Name", (select Count(TRANSACTION_ID) from INVENTORY_TRANSACTION_HIST2 t3 where t1.part_no = t3.part_no and t1.contract = t3.contract) as "Trans Count", t1.create_date from INVENTORY_PART t1, PURCHASE_PART_SUPPLIER t2 where t1.PART_NO = t2.PART_NO and t1.CONTRACT = t2.CONTRACT and t2.PRIMARY_VENDOR_DB = 'Y' and t1.CONTRACT = 'X' and (select Count(TRANSACTION_ID) from INVENTORY_TRANSACTION_HIST2 t3 where t1.part_no = t3.part_no and t1.contract = t3.contract) >= 5 and To_char(t1.create_date,'YYYY-MM-DD') >= '2020-01-01'
