Wednesday, January 30, 2013

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect