1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Supplier Contacts Information

Discussion in 'Oracle SCM & Manufacturing' started by Bharat, Jul 30, 2012.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi all,

    While writing query to retrieving suppliers information I have used AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL. And to get contacts information for supplier I have used AP_SUPPLIER_CONTACTS table. But here Iam getting duplicates in my query. Does supplier contacts are assigned to site level or they assigned to supplier level.

    I didn't found any setup steps to set particular contact to site. So am unable to restrict them in my query. If I have 11 sites for a supplier and 7 contacts Iam getting 77 records totally. Anyone Provide me some assistance to do this.

    Code (Text):

    SELECT ASP.VENDOR_NAME "Vendor Name"
          ,ASSA.VENDOR_SITE_CODE "Vendor Site"
          ,RTRIM(TRIM(ASSA.ADDRESS_LINE1||' '||ASSA.CITY||','||ASSA.STATE||' '||ASSA.ZIP),',') "Address"
    FROM AP_SUPPLIERS ASP
        ,AP_SUPPLIER_SITES_ALL ASSA

    WHERE ASP.VENDOR_NAME = '3M Health Care'
      AND ASSA.VENDOR_ID = ASP.VENDOR_ID

    Output: -
    Vendor Name       Vendor Site      Address
    3M Health Care  BEI LIN CHINA   16th, Floor, Shuion Square Shangai,Shangai
    3M Health Care  CORP HQ         100 3M Way St. Paul,MN 55144
    3M Health Care  CORP HQ         100 3M Way St. Paul,MN 55144
    3M Health Care  DAS2GEN         Bld des Généraux , 75011
    3M Health Care  DAS2GEN         Bld des Généraux , 75011
    3M Health Care  KOREA  
    3M Health Care  LEEDS           6 Neville Street Leeds, LS1 5JL
    3M Health Care  MAIN            123 Fox Road Atlanta,GA 30033
    3M Health Care  MAIN            St. Paul, MN 55144-1000 ,MN 55144-1000
    3M Health Care  PARIS           Rue Balzac , 75008
    3M Health Care  VHS 3M         St. Paul, MN 55144-1000 St. Paul,MN 55144_1000



     
    Now I want to add contacts table to retrieve contacts information to the above query.

    Code (Text):

    SELECT ASP.VENDOR_NAME "Vendor Name"
          ,ASSA.VENDOR_SITE_CODE "Vendor Site Code"
          ,RTRIM(TRIM(ASSA.ADDRESS_LINE1||' '||ASSA.CITY||','||ASSA.STATE||' '||ASSA.ZIP),',')
          ,nvl(fu.email_address,hp_email.email_address)"EMAIL"
          ,hp.person_first_name
          ,hp.person_last_name
          ,FU.USER_NAME
          ,hp_email.primary_phone_area_code||' '||hp_email.primary_phone_number"Mobile#"

    FROM AP_SUPPLIERS ASP
        ,AP_SUPPLIER_SITES_ALL ASSA
        ,HZ_PARTIES HP_EMAIL
        ,HZ_RELATIONSHIPS HR
        ,HZ_PARTIES HP
        ,FND_USER FU

    WHERE ASP.VENDOR_NAME = '&VENDOR_NAME'
      AND ASSA.VENDOR_ID = ASP.VENDOR_ID
      AND HR.OBJECT_ID=ASP.PARTY_ID
      AND HR.PARTY_ID=HP_EMAIL.PARTY_ID
      AND HR.SUBJECT_ID=HP.PARTY_ID
      AND FU.PERSON_PARTY_ID(+)=HR.SUBJECT_ID;

    For this query Iam getting 77 records which is like 7 contacts * 11 sites. Totally 77 records.

     

    Am I missing any join here?
     
  2. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    Bharatappa,

    The problem is in R12 you should be querying HZ_Parties and not AP_Supplier_Contacts.
    Check the below
    SELECT asp.vendor_id ,
    asp.segment1 "Supplier Num" ,
    asp.vendor_name "Supplier Name" ,
    ass.vendor_site_code "site name" ,
    hou.name "Operating Unit Name" ,
    ass.address_line1 ,
    ass.city ,
    ass.state ,
    ass.zip ,
    ass.country ,
    ass.phone ,
    person.person_first_name ,
    person.person_last_name ,
    pty_rel.primary_phone_number ,
    pty_rel.email_address
    FROM ap_suppliers asp ,
    ap_supplier_sites_all ass ,
    ap_supplier_contacts apsc ,
    hz_parties person ,
    hz_parties pty_rel,
    hr_operating_units hou
    WHERE ass.vendor_id = asp.vendor_id
    AND apsc.per_party_id = person.party_id
    AND apsc.rel_party_id = pty_rel.party_id
    AND ass.org_id = hou.organization_id
    AND apsc.org_party_site_id = ass.party_site_id
    AND asp.vendor_name = 'LS Supplier';