Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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
Reddy-87640
Reddy-87640 Rank 4 - Community Specialist
in Oracle Analytics Cloud and Server

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

    http://oraclemine.com/query-get-supplier-details-oracle-apps-r12/

    Query to find Supplier, Sites and Contact Information - R12 | Amar Alam Oracle Apps Technical World

    NOTE : This is not the correct forum for this question ..

  • 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

  • YGUTTIKONDA
    YGUTTIKONDA Rank 6 - Analytics Lead

    did you review the links provided in my first post .. re posting again below

    http://oraclemine.com/query-get-supplier-details-oracle-apps-r12/

    Query to find Supplier, Sites and Contact Information - R12 | Amar Alam Oracle Apps Technical World

  • 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.