Hi Everyone,
Recently i had got a requirement to gather all the Email ids , those can be attached to any supplier.
Their are 4 places, we can store Email Addresses for any supplier in Oracle R12.
Recently i had got a requirement to gather all the Email ids , those can be attached to any supplier.
Their are 4 places, we can store Email Addresses for any supplier in Oracle R12.
- Address Book--> Communication Field.
- Contact Directory
- Remittance Email id at Site Level
- Remittance Email id at Header Level
Queries :
- Address Book --> Communication Field
select ass.vendor_name, ass.segment1 supplier_num,hcp.email_address
from hz_party_sites hps,
hz_contact_points hcp,
ap_suppliers ass,
ap_supplier_sites_all assa
where 1=1
and ass.vendor_id=assa.vendor_id
and hcp.owner_table_name='HZ_PARTY_SITES'
and hps.party_id= ass.party_id
and hcp.owner_table_id= hps.party_site_id
and hcp.contact_point_type='EMAIL'
and assa.org_id = xxxx;
--AND ass.segment1='77439'
- Contact Directory
SELECT asu.segment1 Supp_Num
,asu.vendor_name
,hpcp.email_address
FROM
hz_relationships hr
,ap_suppliers asu
,ap_supplier_sites_all assa
--,ap_supplier_contacts asco
,hz_org_contacts hoc
,hz_parties hpc
,hz_parties hpr
,hz_contact_points hpcp
WHERE hoc.party_relationship_id = hr.relationship_id
AND hr.subject_id = asu.party_id
AND hr.relationship_code = 'CONTACT'
AND hr.object_table_name = 'HZ_PARTIES'
AND asu.vendor_id = assa.vendor_id
AND hr.object_id = hpc.party_id
AND hr.party_id = hpr.party_id
AND hpr.party_type='PARTY_RELATIONSHIP'
AND hpcp.contact_point_type='EMAIL'
AND hpcp.contact_point_type='EMAIL'
AND assa.org_id = xxxx
AND hpr.party_id = hpcp.owner_table_id
AND hpcp.owner_table_name = 'HZ_PARTIES'
--AND asu.segment1='77439'
- Remittance Email id at Site Level
SELECT
s.vendor_name "Vendor Name",
s.segment1 "Vendor Number",
s.vendor_type_lookup_code "Vendor Type",
st.vendor_site_code "Vendor Site Code",
ou.name "Operating Unit",
iepa.remit_advice_delivery_method "Remittance Delivery Method",
iepa.remit_advice_email "Remittance Advice Email"
FROM
ap.ap_suppliers s,
ap.ap_supplier_sites_all st,
hr_operating_units ou,
iby.iby_external_payees_all iepa
WHERE
1=1
AND TRUNC (SYSDATE) BETWEEN TRUNC (s.start_date_active) AND TRUNC (NVL (s.end_date_active, SYSDATE+1))
AND s.enabled_flag = 'Y'
AND iepa.supplier_site_id = st.vendor_site_id
AND iepa.payee_party_id = s.party_id
AND st.org_id = ou.organization_id
AND st.vendor_id = s.vendor_id
-- and s.segment1='77439'
and st.org_id= xxx
ORDER BY s.vendor_name, st.vendor_site_code;
- Remittance Email id at Header Level
SELECT aps.vendor_name, aps.segment1 supplier_num,iepa.remit_advice_email
FROM APPS.ap_suppliers aps,
apps.ap_supplier_sites_all apss
,APPS.iby_external_payees_all iepa
WHERE iepa.payee_party_id = aps.party_id
and aps.vendor_id= apss.vendor_id
AND default_payment_method_code is null
-- AND aps.segment1='77439'
and apss.org_id = xxx
and iepa.party_site_id IS null
No comments:
Post a Comment