Oracle Analytics and AI

Primary phone number is not coming in PO report

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.

Find more posts tagged with

Reporting

Accepted answers

All comments

YGUTTIKONDA

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

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

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

Reddy-87640

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

Hello Commonman,

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

YGUTTIKONDA

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

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

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

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

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

Hi srikanth,

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