Skip to Content
0

vendor repeating more than one time

Mar 01, 2017 at 09:07 AM

52

avatar image

Dear Experts

am looking to create a report in which i want to know from which and all Vendor we have purchased an item

i had create a SPT with a quey, am getting same vendor multi time, i think some issue with Query, pls can any one help me by editing the Query.

USE [SAP DEMO]
GO
/****** Object:  StoredProcedure [dbo].[PreferedVendor]    Script Date: 03/01/2017 10:37:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[PreferedVendor]		

AS
Begin






select distinct OPCH.CardCode [Supplier Code],OPCH.CardName [Supplier Name] ,PCH1.ItemCode,PCH1.Dscription,OCRD.CntctPrsn [Contact Person],OCRD.Phone1 [Ph1],OCRD.Phone2 [Ph2],OCRD.Cellular[Mob],OCRD.E_Mail [E-mail] from OPCH
inner join OCRD on OPCH.CardCode = OCRD.CardCode
iNNER JOIN PCH1 ON OPCH.DocEntry = PCH1.DocEntry
where PCH1.TargetType <> 19 and OPCH.CANCELED NOT IN ('N''C') AND OCRD. GroupCode <> 108
group by PCH1.ItemCode,OPCH.CardCode,OPCH.CardName,PCH1.ItemCode,PCH1.Dscription,OCRD.CntctPrsn,OCRD.Phone1,OCRD.Phone2 ,OCRD.Cellular,OCRD.E_Mail


End


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Johan Hakkesteegt Mar 01, 2017 at 09:34 AM
0

Hi,

As far as I can tell, your query simply returns a list of items and vendors that these items have been purchased from.

There is no logic in your query to limit the result set to single vendor-per-item combinations.

Also, what is this supposed to do?:

OPCH.CANCELED NOTIN('N''C')

Regards,

Johan

Show 3 Share
10 |10000 characters needed characters left characters exceeded

yes, it says the items purchased from which vendor in previous time,

i need a report in such a way itself . can u pls tell how can i stop Vendor duplication happening in the report

Regards

0

Could you please explain in a little more detail what this query is supposed to do?

Should it return the most recent vendor of an item, or the most popular vendor of an item, or the cheapest vendor of an item, or simply a list items with their preferred vendors?

0

Hi Johan

Its done, i made some small changes, it giving me perfect Data

Thanks for the support

1
Shahan Shams Mar 01, 2017 at 11:34 AM
0

Hi johan

this is for the updating our Item master data with Preferred Vendor. we are having more that 75000 items, and each item are purchased from different vendors, so the current emp who deals with has resign, so when the substitute come , he should get aware that when an item is for purchasing which and an all Vendor he had to Approach, so we plan to update the item master data >> Preferred Vendor data, but as we had no of items more it not easy to find out , so we think if we get a report from the previous invoices in which the item is been bought from different vendors it would be easy to update the MD .

Hope u get the scenario

Regards

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi,

Yes, I understand. Good that you managed to get it fixed.

Regards,

Johan

0