Primary phone number is not coming in PO report — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Primary phone number is not coming in PO report

Received Response
251
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:
«1

Answers

  • YGUTTIKONDA
    YGUTTIKONDA Rank 6 - Analytics Lead

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

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

  • Reddy-87640
    Reddy-87640 Rank 4 - Community Specialist

    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 Rank 6 - Analytics Lead

    Hello Commonman,

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

    YG

  • Reddy-87640
    Reddy-87640 Rank 4 - Community Specialist

    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

  • Srikkanthmani
    Srikkanthmani Rank 3 - Community Apprentice

    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

  • Reddy-87640
    Reddy-87640 Rank 4 - Community Specialist

    Hi YGUTTIKONDA,

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

  • Srikkanthmani
    Srikkanthmani Rank 3 - Community Apprentice

    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

  • Reddy-87640
    Reddy-87640 Rank 4 - Community Specialist

    Hi srikanth,

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