1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

customer billing address

Discussion in 'Oracle Projects' started by neelambhumij, May 21, 2014.

  1. neelambhumij

    neelambhumij Active Member

    Messages:
    51
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    JOHANNESBURG
    Hi all

    Can anybody help me with sql to find the customers billing address for a particular project.

    I tried the following .. but giving multiple address...
    Ny help is really appreciated...

    select
    distinct hou.name "Company"
    ,ppa.segment1 "project_number"
    ,ppa.description "project description"
    ,ppa.project_status_code
    ,ppc.customer_name
    ,ppc.customer_number "customer_account_number"
    ,ftl.TERRITORY_SHORT_NAME "Country"
    ,hl.address1
    ,hl.address2
    from
    pa_projects_all ppa,
    PA_PROJECT_CUSTOMERS_V ppc,
    hr_organization_units hou,
    HZ_CUST_ACCOUNTS hcz,
    HZ_CUST_ACCT_SITES_ALL hccs,
    HZ_PARTY_SITES hps,
    hz_locations hl,
    fnd_territories_tl ftl
    where ppa.project_id=ppc.project_id
    AND ppa.org_id=HOU.ORGANIZATION_ID
    and hou.name in('1110','1040','1142','1030','1109')
    and hcz.account_number=ppc.customer_number
    and hccs.cust_account_id=hcz.cust_account_id
    and hps.party_site_id=hccs.party_site_id
    and hl.location_id=hps.location_id
    and hl.country=ftl.territory_code
    --and ppa.PROJECT_ID=709388
    order by ppa.segment1

    Please look at the snapshot attached for more details....


    Thanks and Regards
    Neelam
     

    Attached Files:

  2. t4toks

    t4toks Active Member

    Messages:
    3
    Likes Received:
    1
    Trophy Points:
    65
    You should add hz_cust_site_uses_all to the tables. That table has site_use_code and you want the data where the site_use_code = 'BILL_TO' . Join the cust_acct_site_id from this table to your hz_cust_Acct_sites_all and you should be fine
     
    neelambhumij likes this.
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Neelam,

    Your query is almost correct, you just add below condition.

    and ppa.org_id=hccs.org_id
     
    neelambhumij likes this.