Skip to Content
author's profile photo
Former Member

vendor repeating more than one time

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


Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Mar 01, 2017 at 09:34 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Mar 01, 2017 at 11:34 AM

    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

    Add comment
    10|10000 characters needed characters exceeded