Categories
- All Categories
- 93 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14.2K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 53 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations Gallery
- 2 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
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.
Answers
-
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 ..
0 -
If your question is answered, please mark completed / helpful.
Again going forward for EBS related issue, please post in the right group
0 -
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??
0 -
Hello Commonman,
This is the BI Publisher group .. did you check the links provided ..
YG
0 -
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
0 -
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
0 -
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
0 -
Hi YGUTTIKONDA,
I am also posting the issue related to my BIP Report only. if you can help me out it will be greatful.
0 -
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
0 -
Hi srikanth,
i am not seeing phone number when i ran the query.
0