Query to fetch Purchase Receipt and its related Purchase order, Vendor details in Oracle Apps R12

 Key Tables:

RCV_TRANSACTIONS

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES

PO_DISTRIBUTIONS_ALL

PO_LINES_ALL

PO_LINE_LOCATIONS_ALL

PO_HEADERS_ALL

AP_SUPPLIERS

AP_SUPPLIER_SITES_ALL

Query:

SELECT hou.name ou_name, ph.segment1 po_num, ph.po_header_id, pol.po_line_id, pol.line_num, order_type.displayed_field line_type, COALESCE(pd.quantity_ordered,pll.quantity,pol.quantity) quantity, (pol.unit_price * (pd.quantity_ordered - pd.quantity_cancelled)) line_amount, rsh.receipt_source_code, flv_ship_source.meaning receipt_source_code, papf.full_name received_by, rsh.vendor_id, aps.vendor_name, aps.segment1 vendor_number, assa.vendor_site_id, assa.vendor_site_code, ph.org_id, rsh.shipment_num, rsh.receipt_num, rsh.ship_to_location_id, hl.location_code deliver_to, rsl.item_description, rsl.shipment_line_id, rsl.quantity_shipped, rsl.quantity_received, rsl.unit_of_measure, rsl.vendor_item_num, flv_shipment.meaning shipment_line_status_code, flv_inspection.meaning inspection_status_code, rsl.shipment_line_status_code, rct.inspection_status_code, flv_tran_type.meaning transaction_type, rct.transaction_type transaction_type_lookup_code, rct.transaction_id, nvl(rct.source_doc_quantity,0) transaction_qty, rct.transaction_date, assa.attribute1 vendor_global_code, assa.attribute2 entity_supplier_code, assa.attribute3 vendor_communication_language, pd.attribute1 company_specific_gl_code, pol.attribute2 sanction_number, pol.attribute3 inspection_required, pol.attribute4 end_user_details, rsh.attribute1 Invoice_number, rct.comments gre_comments, rsh.attribute2 exchange_rate_information, rsh.attribute3 gst_invoice_amt, rsh.attribute4 gst_exchange_rate, rsh.attribute5 custom_form_no, rct.attribute1 receipt_line_level_tax_rate from rcv_transactions rct, rcv_shipment_headers rsh, per_all_people_f papf, rcv_shipment_lines rsl, po_distributions_all pd, po_lines_all pol, po_line_locations_all pll, po_headers_all ph, ap_suppliers aps, ap_supplier_sites_all assa, hr_all_organization_units hou, hr_locations hl, fnd_lookup_values flv_shipment, fnd_lookup_values flv_inspection, fnd_lookup_values flv_tran_type, fnd_lookup_values flv_ship_source, po_lookup_codes order_type, po_line_types_b plt WHERE 1 = 1 and rct.po_header_id = ph.po_header_id and rct.po_line_location_id = pll.line_location_id AND rct.po_line_id = pol.po_line_id AND pol.po_line_id = pd.po_line_id and rct.shipment_line_id = rsl.shipment_line_id and rsl.shipment_header_id = rsh.shipment_header_id AND rct.po_distribution_id = pd.po_distribution_id AND rct.employee_id = papf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date and rsh.vendor_id = aps.vendor_id and ph.vendor_site_id = assa.vendor_site_id AND hou.organization_id = ph.org_id AND rct.deliver_to_location_id = hl.location_id AND rsl.shipment_line_status_code = flv_shipment.lookup_code AND flv_shipment.lookup_type = 'SHIPMENT LINE STATUS' AND flv_shipment.language = USERENV('LANG') AND rct.inspection_status_code = flv_inspection.lookup_code AND flv_inspection.lookup_type = 'INSPECTION STATUS' AND flv_inspection.language = USERENV('LANG') AND rct.transaction_type = flv_tran_type.lookup_code AND flv_tran_type.lookup_type = 'RCV TRANSACTION TYPE' AND flv_tran_type.language = USERENV('LANG') AND flv_ship_source.lookup_type = 'SHIPMENT SOURCE TYPE' AND flv_ship_source.language = USERENV('LANG') AND flv_ship_source.lookup_code = rsh.receipt_source_code AND order_type.lookup_type = 'ORDER TYPE' AND order_type.lookup_code = plt.order_type_lookup_code AND pol.line_type_id = plt.line_type_id;

Regards,

Amar Alam

Oracle Apps + Query to get customer contact information

 -- Retrieve Party Contact Points

SELECT cp.*
FROM ar.hz_cust_accounts c,
AR.HZ_PARTIES p,
ar.hz_contact_points cp
WHERE
c.account_number = :P_CUST_ACCT_NUM
AND p.party_id = c.party_id
AND cp.owner_table_name = 'HZ_PARTIES'
AND cp.owner_table_id = p.party_id;

-- Site contact point (this is different from site contact)

SELECT p.party_number,
c.cust_account_id,
c.account_number,
cp.*
FROM ar.hz_cust_accounts c,
AR.HZ_PARTIES p,
ar.hz_party_sites s,
ar.hz_contact_points cp
WHERE c.account_number = :P_CUST_ACCT_NUM
AND p.party_id = c.party_id
AND p.party_id = s.party_id
AND cp.owner_table_name = 'HZ_PARTY_SITES'
AND cp.owner_table_id = s.party_site_id;

-- Site Contacts with contact points

  SELECT p.party_name,

p.party_id,
p.party_number,
p.party_type,
c.cust_account_id,
c.account_number,
pp.party_name contact_name,
pp.PARTY_ID contact_party_id,
pp.party_type,
r.role_type,
co.phone_country_code,
co.phone_area_code,
co.phone_number,
co.phone_line_type,
co.raw_phone_number,
co.email_address,
co.contact_point_purpose,
co.primary_flag,
co.last_update_date
FROM ar.hz_cust_accounts c,
AR.HZ_PARTIES p,
ar.hz_cust_account_roles r,
AR.HZ_PARTIES cp,
ar.hz_contact_points co,
ar.hz_cust_acct_sites_all s, --needed only if you require org_id
ar.hz_parties pp,
AR.hz_relationships rel
WHERE s.cust_account_id = c.cust_account_id
AND p.party_id = c.party_id
AND r.cust_account_id = c.cust_account_id
AND cp.PARTY_ID = r.party_id
AND co.owner_table_name = 'HZ_PARTIES'
AND co.OWNER_TABLE_ID = cp.party_id
AND r.cust_acct_site_id IS NOT NULL
AND r.cust_acct_site_id = s.cust_acct_site_id
AND pp.party_id = rel.subject_id
AND rel.party_id = cp.party_id
AND rel.relationship_code = 'CONTACT_OF'
AND rel.directional_flag = 'F'
AND rel.subject_table_name = 'HZ_PARTIES'
AND c.account_number = :P_CUST_ACCT_NUM
ORDER BY c.cust_account_id DESC;

-- CUSTOMER ACCOUNT CONTACT INFO

select hp.party_name
,hp1.party_name Contact_name
,hoc.contact_number
,hcar.cust_acct_site_id
,hca.cust_account_id
from apps.hz_parties hp
,apps.hz_cust_accounts hca
,apps.hz_org_contacts hoc
,apps.hz_cust_account_roles hcar
,apps.hz_parties hp1
,apps.hz_relationships hr
where 1 = 1
and hca.party_id = hp.party_id
AND hr.subject_id = hp1.party_id
AND hr.object_id = hp.party_id
AND hcar.party_id = hr.party_id
AND hoc.party_relationship_id = hr.relationship_id
AND hcar.cust_acct_site_id is null
AND hcar.STATUS like 'A';

--CUSTOMER ACCOUNT SITE CONTACT INFO

select hp.party_name
,hp1.party_name Contact_name
,hoc.contact_number
,hps.party_site_id
,hps.PARTY_SITE_NUMBER
from apps.hz_parties hp
,apps.hz_party_sites hps
,apps.hz_cust_accounts hca
,apps.hz_cust_acct_sites_all hcas
,apps.hz_org_contacts hoc
,apps.hz_cust_account_roles hcar
,apps.hz_parties hp1
,apps.hz_relationships hr
where 1 = 1
and hp.party_id = hps.party_id
and hca.party_id = hp.party_id
and hca.cust_account_id = hcas.cust_account_id
and hcas.party_site_id = hps.party_site_id
and hcar.cust_acct_site_id(+) = hcas.cust_acct_site_id
AND hr.subject_id = hp1.party_id
AND hr.object_id = hp.party_id
AND hcar.party_id = hr.party_id
AND hoc.party_relationship_id = hr.relationship_id
AND hcar.STATUS like 'A';

Regards,

Amar Alam