Query to find customer site level contact points

Customer Site level contact points

/* *******************************************************************
Modules Involved : AR
Description      : get the customer site level communication details
****************************************************************** */
select HP.PARTY_NAME               PARTY_NAME,
       hps.party_site_number       Site_Number,
       hcp.contact_point_type      Contact_Type,
       hcp.phone_line_type         Phone_Line_Type,
       hcp.phone_country_code      Country_Code,
       hcp.phone_number            Phone_number,
       hcp1.contact_point_type     Contact_Type,
       hcp1.email_address          Email_Address ,
       HCP1.EMAIL_FORMAT           EMAIL_FORMAT,
       HCP1.CONTACT_POINT_PURPOSE  CONTACT_POINT_PURPOSE,
       HL.ADDRESS1||','||HL.ADDRESS2||' '||HL.ADDRESS3||' '||HL.ADDRESS4||' '||HL.CITY||','||HL.STATE||','
       ||hl.POSTAL_CODE||','||hl.country SITE_ADDRESS
  FROM apps.hz_parties             hp,
       apps.hz_party_sites         hps,
       APPS.HZ_CONTACT_POINTS      HCP,
       APPS.HZ_CONTACT_POINTS      HCP1,
       apps.hz_locations           hl
WHERE 1=1  
  AND hp.party_id              = hps.party_id
  AND hcp.owner_table_name     = 'HZ_PARTY_SITES'
  AND hcp.owner_table_id       = hps.party_site_id
  AND hcp.contact_point_type   = 'PHONE'
  AND hcp1.owner_table_name    = 'HZ_PARTY_SITES'
  AND hcp1.owner_table_id      = hps.party_site_id
  and HCP1.CONTACT_POINT_TYPE  = 'EMAIL'  
  and hps.location_id          = hl.location_id
  and HPS.PARTY_SITE_NUMBER    = '100095'
  ;

   

Query to get the order and invoice details

Joins between OM and AR

/* *******************************************************************
Modules Involved : AR, OM
Description      : get the invoices and order details
****************************************************************** */

SELECT ooha.order_number        Order_Number,
       rct.trx_number           Invoice_number,
       rctl.line_type           Invoice_Line_Type,
       msi.segment1             Item_Number,
       rctl.quantity_ordered    Quantity_ordered,
       rctl.quantity_invoiced   Quantity_invoiced,
       rctl.unit_standard_price Unit_standard_price,
       rctl.unit_selling_price  Unit_selling_price,
       RCTL.EXTENDED_AMOUNT     EXTENDED_AMOUNT
       --,rctl.*
  FROM apps.oe_order_headers_all      ooha,
       apps.ra_customer_trx_all       rct,
       APPS.RA_CUSTOMER_TRX_LINES_ALL RCTL,
       apps.mtl_system_items_b          msi
 WHERE 1=1
   AND to_char(ooha.order_number)      = rct.interface_header_attribute1
   AND rct.customer_trx_id             = rctl.customer_trx_id
   and RCTL.INVENTORY_ITEM_ID          = MSI.INVENTORY_ITEM_ID  
   AND msi.organization_id             = 65 -- Master
   and RCT.TRX_NUMBER                  = '284968'
   --and RCTL.INTERFACE_LINE_ATTRIBUTE2  = 'Test_Order_Type'
   ;