Oracle Receivables SHIP TO query

SELECT DISTINCT

  HCA.ACCOUNT_NUMBER "Legacy_customer_number" ,

  HPS.PARTY_SITE_NUMBER "Legacy_Customer_No_part2"          ,

  NULL "Systemid"                                           ,

  NULL "Sap_Hierarchy"                                      ,

  NULL "AcctGroup"                                          ,

  hp.party_name "Name1"                                     ,

  NULL "Name2"                                              ,

  NULL "Name3"                                              ,

  NULL "Name4 Do Not Use"                                   ,

  HL1.ADDRESS1 street1                                      ,

  HL1.ADDRESS2 street2                                      ,

  hl1.address3 street3                                      ,

  hl1.address4 street4                                      ,

  hl1.house_number house                                    ,

  HL1.CITY                                                  ,

  NULL "District"                                           ,

  HL1.POSTAL_CODE                                           ,

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

  NULL "VatReg"                        ,

  rt.name pay_term                     ,

  HL1.COUNTRY                          ,

  HL1.STATE REGION                     ,

  NULL IncoTerms                       ,

  NULL Currency                        ,

  HCA.ACCOUNT_NUMBER "Customer Number" ,

  NULL "PayerLoc"                      ,

  NULL "ACCTATCUST"                    ,

  NULL "KVGR3"                         ,

  NULL "Industry_code"                 ,

  NULL "Language"                      ,

  NULL "NaturalPerson"                 ,

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

  NULL "SearchTerm"                  ,

  NULL "Vat/TaxRate"                 ,

  NULL "PriceList"                   ,

  NULL "Industry_Code_1"             ,

  NULL "IncoTerms_2"                 ,

  NULL "SalesOffice"                 ,

  NULL "SalesGroup"                  ,

  NULL "SAP_Hierarchy_Name"          ,

  NULL "Additional Payers"           ,

  NULL "Tax_number_1"                ,

  NULL "Tax_number_2"                ,

  NULL "Tax_number_3"                ,

  NULL "Tax_number_4"                ,

  NULL "ICMS law"                    ,

  NULL "IPI Law"                     ,

  NULL "CFOP Category"               ,

  NULL "Subtrib group"               ,

  NULL "ICMS Exempt"                 ,

  NULL "IPI Exempt"                  ,

  HP.EMAIL_ADDRESS "Email"           ,

  NULL "Note"                        ,

  NULL "Reconciliation Account"

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     ='SHIP_TO'

   AND HCSU.STATUS            = 'A'

   AND HCA.ACCOUNT_NUMBER LIKE '26210'

   AND hcas.org_id =736;

--AND hcas.org_id =457;

----> Sponsored by Nisha Ellora