Oracle Receivables Bill To Query

SELECT  DISTINCT

  HCA.ACCOUNT_NUMBER "Legacy_customer_number" ,

  hcsu.cust_acct_site_id ,

  hcsu.site_use_id,

  HPS.PARTY_SITE_NUMBER "Legacy_Customer_No_part2"  ,

  hp.party_name "Name1"                             ,

  HL1.ADDRESS1 street1                              ,

  HL1.ADDRESS2 street2                              ,

  hl1.address3 street3                              ,

  hl1.address4 street4                              ,

  hl1.house_number house                            ,

  HL1.CITY                                          ,

  HL1.POSTAL_CODE                                   ,

  DECODE(HCP.PHONE_LINE_TYPE,'GEN',HCP.PHONE_AREA_CODE||HCP.PHONE_NUMBER,NULL) PHONE_NUMBER,

  rt.name pay_term                     ,

  HL1.COUNTRY                          ,

  HL1.STATE REGION                     ,

  HCA.ACCOUNT_NUMBER "Customer Number" ,

  DECODE(HCP.PHONE_LINE_TYPE,'FAX',HCP.PHONE_AREA_CODE||HCP.PHONE_NUMBER,NULL) FAX_NUMBER,

  HP.EMAIL_ADDRESS "Email"           ,

FROM hz_parties hp         ,

  hz_locations hl1            ,

  hz_cust_site_uses_all hcsu  ,

  hz_cust_acct_sites_all hcas ,

  hz_party_sites hps          ,

  HZ_CUST_ACCOUNTS hca        ,

  HZ_CONTACT_POINTS HCP       ,

  ra_terms rt

WHERE 1 = 1

   AND hcsu.cust_acct_site_id =hcas.cust_Acct_Site_id

   AND hcas.party_site_id     =hps.party_site_id

   AND hps.location_id        = hl1.location_id

   AND hp.party_id            =hcp.owner_table_id(+)

   AND hp.party_id            =hps.party_id

   AND HCA.CUST_ACCOUNT_ID    =HCAS.CUST_ACCOUNT_ID

   AND hcsu.PAYMENT_TERM_ID            = rt.TERM_ID(+)

   AND hcsu.site_use_code     ='BILL_TO'

   AND HCSU.STATUS            = 'A'

   --AND HCA.ACCOUNT_NUMBER LIKE '26210'

   AND hcas.org_id =736;

----> Sponsored by Nisha Ellora