Oracle Analytics Cloud and Server

Products Banner

Primary phone number is not coming in PO report

Received Response
204
Views
13
Comments

Summary

Primary phone number is not coming in PO report

Content

Hi,

 

I have Used supplier table to get the contact details of the supplier. all the supplier related columns are getting like a the data except primary phone number.

 

can anyone tell me which table i have to use or need to join with any other table.At present i am using HZ_PARTIES table to get the supplier details. but unable to get the phone number.

Tagged:

Answers

  • YGUTTIKONDA
    YGUTTIKONDA ✭✭✭✭✭

    If your question is answered, please mark completed / helpful.

    Again going forward for EBS related issue, please post in the right group

  • Hi YGUTTIKONDA,

    I am asking the the issue is coming in my BI Publisher  for purchase order report.

    IS this not the BI Publisher related group??

  • YGUTTIKONDA
    YGUTTIKONDA ✭✭✭✭✭

    Hello Commonman,

    This is the BI Publisher group .. did you check the links provided ..

    YG

  • This is my query related to phone number., not displaying primary phone number in xml tag.

    HZP.PARTY_NAME

      ||'-'

      ||POZ.SEGMENT1 SUPPLIER_NAME_NUM,

      PVSA.vendor_site_code,

      HZP.ADDRESS1

      ||' '

      ||HZP.ADDRESS2

      ||' '

      ||HZP.ADDRESS3

      ||' '

      ||HZP.ADDRESS4

      ||' '

      ||HZP.CITY

      ||' '

      ||HZP.POSTAL_CODE

      ||' '

      ||HZP.STATE SUPPLIER_ADDRESS,

      hzp.EMAIL_ADDRESS supp_email,

      (SELECT meaning

      FROM FND_LOOKUP_VALUES_TL flv

      WHERE FLV.LOOKUP_CODE = hzp.country

      AND FLV.LOOKUP_TYPE   = 'JEES_EURO_COUNTRY_CODES'

      AND FLV.LANGUAGE      = 'US'

      ) SUPPLIER_COUNTRY,

      (SELECT hla.TELEPHONE_NUMBER_2

      FROM hr_locations_all hla

      WHERE hla.location_id = pha.bill_to_location_id

      ) FAX,

      (SELECT hla.TELEPHONE_NUMBER_1

      FROM hr_locations_all hla

      WHERE hla.location_id = pha.bill_to_location_id

      ) TELEPHONE,

      (SELECT hla.EMAIL_ADDRESS

      FROM hr_locations_all hla

      WHERE hla.location_id = pha.bill_to_location_id

      ) EMAIL,

      (SELECT hla.ADDRESS_LINE_1

        || ' '

        || hla.ADDRESS_LINE_2

        || ' '

        || hla.TOWN_OR_CITY

        || ' '

        || hla.POSTAL_CODE

        || ' '

        ||

        (SELECT meaning

        FROM FND_LOOKUP_VALUES_TL

        WHERE LOOKUP_CODE = hla.country

        AND LANGUAGE      = 'US'

        AND LOOKUP_TYPE   = 'JEES_EURO_COUNTRY_CODES'

        )

      FROM hr_locations_all hla

      WHERE hla.location_id = pha.bill_to_location_id

      ) BILL_TO_LOCATION,

      HZP.PRIMARY_PHONE_NUMBER,

      pha.REVISION_NUM,

      pha.REVISED_DATE,

      PHA.ATTRIBUTE1 Contact_Person ,

      --  pha.ATTRIBUTE_NUMBER1 Contact_Number ,

      --  pha.ATTRIBUTE2 Discount_Amount,

      --  pha.ATTRIBUTE3 Discount_Percent,

      PHA.ATTRIBUTE2 CONTACT_MOBILE,

      PHA.ATTRIBUTE3 CONTACT_NUMBER,

      pha.ATTRIBUTE4 Terms_of_Delivery

    FROM POZ_SUPPLIERS POZ,

      PO_HEADERS_ALL PHA,

      HZ_PARTIES HZP ,

      POZ_SUPPLIER_SITES_ALL_M PVSA,

      AP_TERMS_vL atl,

      PO_LINES_ALL PLA

    WHERE pha.BILLTO_BU_ID      = hw.organization_id

    AND PLLA.PO_HEADER_ID       = PLA.PO_HEADER_ID

    AND PLLA.PO_LINE_ID         = PLA.PO_LINE_ID

    AND POZ.VENDOR_ID(+)        = PHA.VENDOR_ID

    and poz.segment1 =nvl(:p_supp_num,poz.segment1)

    AND HZP.party_id(+)         = POZ.party_id

    AND PHA.VENDOR_SITE_ID      = PVSA.VENDOR_SITE_ID(+)

    AND pha.terms_id            = atl.TERM_ID(+)

    AND PHA.PO_HEADER_ID        = PLA.PO_HEADER_ID(+)

    AND PLA.uom_code            = uom.uom_code(+)

    AND pla.po_line_id          = pda.po_line_id(+)

    AND Pha.ship_to_location_id = HLAS.location_id(+)

    AND lower(PHA.Created_by)   = lower(pu.u_name)

    AND ppf2.obj_id(+)          = pha.po_header_id

  • Hi,

    1.So in your xml tag, the phone no is coming and its displaying not displaying in output ?

    2.Use the HZ Contact Point table, Check this query..

    SELECT DISTINCT hcas.cust_acct_site_id cust_acct_site_id,

                                 h_contact.party_id contact_id,

                                 h_contact.party_name contact_name,

                                 hp.party_id party_id, hp.party_name party_name,

                                 hp.party_number party_number,

                                 cust.account_name account_name,

                                 cust.account_number account_number,

                                 NVL2 (hcas.org_id,

                                       'SITE',

                                       'CUSTOMER'

                                      ) contact_level,

                                 NVL (hcas.org_id, cust.org_id) org_id,

                                 DECODE

                                    (hcp.contact_point_type,

                                     'PHONE', DECODE (hcp.phone_line_type,

                                                      'GEN', 'TELEPHONE',

                                                      hcp.phone_line_type

                                                     ),

                                     hcp.contact_point_type

                                    ) contact_point_type,

                                 DECODE (hcp.phone_line_type,

                                         'GEN', 'TELEPHONE',

                                         hcp.phone_line_type

                                        ) phone_line_type,

                                    DECODE (hcp.phone_country_code,

                                            NULL, NULL,

                                            hcp.phone_country_code || '-'

                                           )

                                 || DECODE (hcp.phone_area_code,

                                            NULL, NULL,

                                            hcp.phone_area_code || '-'

                                           )

                                 || hcp.phone_number phone_no,

                                 hcp.email_address email_address,

                                 hcp.owner_table_id owner_table_id,

                                 hcp.owner_table_name owner_table_name,

                                 hcas.party_site_id party_site_id,

                                 (SELECT party_site_number

                                    FROM hz_party_sites

                                   WHERE party_site_id =

                                             hcas.party_site_id

                                     AND party_id = hp.party_id)

                                                                party_site_number,

                                 (SELECT party_site_name

                                    FROM hz_party_sites

                                   WHERE party_site_id =

                                               hcas.party_site_id

                                     AND party_id = hp.party_id) party_site_name,

                                 DECODE (hcp.status,

                                         'A', 'Active',

                                         NULL, 'Inactive',

                                         'Inactive'

                                        ) contact_status,

                                 cust.cust_account_id cust_account_id,

                                 h_contact.person_title title,

                                 h_contact.person_first_name first_name,

                                 h_contact.person_last_name last_name,

                                 (SELECT MAX (tag_number)

                                    FROM sify_cs_info_all_fusbw_v

                                   WHERE cust_account_id =

                                                hcas.cust_account_id

                                     AND opg_attribute1 = 'Primary') primary_link,

                                 (SELECT MAX (tag_number)

                                    FROM sify_cs_info_all_fusbw_v

                                   WHERE cust_account_id =

                                              hcas.cust_account_id

                                     AND opg_attribute1 = 'Secondary')

                                                                   secondary_link,

                                 (SELECT MAX (tag_number)

                                    FROM sify_cs_info_all_fusbw_v

                                   WHERE cust_account_id =

                                              hcas.cust_account_id

                                     AND opg_attribute1 = 'Tertiary')

                                                                   territory_link,

                                 hcp.phone_country_code phone_country_code,

                                 hcp.phone_area_code phone_area_code,

                                 hcp.phone_number phone_number,

                                 DECODE

                                    (hcp.contact_point_type,

                                     'EMAIL', hcp.email_address,

                                     'PHONE', DECODE (hcp.phone_country_code,

                                                      NULL, NULL,

                                                      hcp.phone_country_code

                                                      || '-'

                                                     )

                                      || DECODE (hcp.phone_area_code,

                                                 NULL, NULL,

                                                 hcp.phone_area_code || '-'

                                                )

                                      || hcp.phone_number,

                                     NULL, NULL

                                    ) contact_point_value,

                                 scs.opg_attribute1 link_type,

                                 scs.tag_number link_value,

                                 hcp.contact_point_id contact_point_id,

                                 DECODE (NVL (hcp.primary_flag, 'N'),

                                         'Y', 'Yes',

                                         'No'

                                        ) primary_flag,

                                 hcp.contact_point_type cpt,

                                 hcp.phone_line_type plt,

                                 SYSDATE last_update_date, 1 last_updated_by,

                                 1 last_update_login, 1 created_by,

                                 SYSDATE creation_date

                            FROM ar.hz_parties hp,

                                 ar.hz_relationships hr,

                                 ar.hz_parties h_contact,

                                 ar.hz_cust_accounts cust,

                                 ar.hz_cust_account_roles hcar,

                                 ar.hz_cust_acct_sites_all hcas,

                                 (SELECT *

                                    FROM hz_contact_points

                                   WHERE status = 'A') hcp                         

                           WHERE hr.subject_id = h_contact.party_id

                             AND hr.object_id = hp.party_id

                             AND cust.party_id = hp.party_id

                             AND hcar.cust_account_id(+) = cust.cust_account_id

                             AND hcar.party_id = hr.party_id

                             AND hcar.cust_acct_site_id = hcas.cust_acct_site_id(+)

                             AND hr.party_id = hcp.owner_table_id(+)

                             AND hcp.owner_table_name = 'HZ_PARTIES'

                             AND current_role_state = 'A'

    Thanks & Regards

    Srikkanth M

  • Hi YGUTTIKONDA,

    I am also posting the issue related to my BIP Report only. if you can help me out it will be greatful.

  • Hi,

    When you ran the query for particular supplier or party, whether the phone no displaying in you sql dev or toad ?

    Thanks & Regards

    Srikkanth M

  • Hi srikanth,

    i am not seeing phone number when i ran the query.

  • AnandGN
    AnandGN ✭✭

    Hi,

    You need to use HZ_CONTACT_POINTS to get primary phone numbers for suppliers

    1. Find Party_id from HZ_PARTIES for each supplier from POZ_SUPPLIERS

    2. Find corresponding PARTY_SITE_ID FROM HZ_PARTY_SITES

    3. Join PARTY_SIDE_ID with OWNER_TABLE_ID from HZ_CONTACT_POINTS

    4.Filter on the below 4 columns within HZ_CONTACT_POINTS

    PRIMARY_PER_PHONE_TYPE="Y"

    PHONE_TYPE

    PRIMARY_FLAG="Y'

    STATUS ="A"

    These will allow you to get primary phone info per supplier.

    Let me know how that goes..:-)

    Thanks,

    Anand

  • AnandGN
    AnandGN ✭✭

    That's interesting. This worked for me. Not sure why would it not work for you.

    Here is the code snippet that you can try running for the above mentioned logic. This gives me supplier phone no per supplier:

    select DISTINCT B.PARTY_NAME, D.RAW_PHONE_NUMBER, B.PARTY_TYPE,A.VENDOR_TYPE_LOOKUP_CODE AS Supplier_Vendor_Type

    FROM

    POZ_SUPPLIERS A,

    HZ_PARTIES B,

    HZ_PARTY_SITES C,

    HZ_CONTACT_POINTS D

    WHERE 1=1

    AND A.PARTY_ID=B.PARTY_ID

    AND B.PARTY_ID=C.PARTY_ID

    AND C.PARTY_SITE_ID=D.OWNER_TABLE_ID

    AND D.PRIMARY_PER_PHONE_TYPE='Y'

    AND D.PRIMARY_FLAG='Y'

    AND D.STATUS='A'

    Thanks,

    Anand

  • Reddy-87640
    Reddy-87640 ✭✭✭

    Hi Anand,

    I have followed the steps which you explained but still i didnt see the phone number . any other solution pls.

    Thanks

    Sudhakar