Primary phone number is not coming in PO report

AnandGN · May 2, 2018 7:00AM

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 · May 14, 2018 7:00AM

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 · May 14, 2018 7:00AM

Hi Anand,

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

Thanks

Sudhakar

Oracle Analytics Cloud and Server