SQL & PL/SQL

Hi All,

I am using Oracle Applications R12 Standard tables for the below query:

SELECT  DISTINCT hca.account_number,

(SELECT cust_acct_site_id  FROM hz_cust_site_uses_all hc WHERE hcas.cust_acct_site_id =hc.cust_acct_site_id AND  site_use_code = 'BILL_TO' AND hc.primary_flag = 'Y') Bill_to_Loc_Id,

(SELECT cust_acct_site_id  FROM hz_cust_site_uses_all hc WHERE hcas.cust_acct_site_id =hc.cust_acct_site_id AND  site_use_code = 'SHIP_TO' AND hc.primary_flag = 'Y') Ship_to_Loc_Id   

           FROM hz_cust_accounts hca,

                hz_cust_acct_sites_all hcas,              

                hz_parties hp

          WHERE     hca.cust_account_id = hcas.cust_account_id                                                                                    

                AND hca.party_id = hp.party_id

                AND hca.account_number=100;

I want to get the Bill to site id and Ship to site id in one row as below:

Required O/P:

Account_number    Bill_to_Loc_Id      Ship_to_Loc_Id   

100                                       211                  311

But I am getting the o/p as 2 records as below:      

O/P:

Account_number    Bill_to_Loc_Id      Ship_to_Loc_Id   

100                                  211                 

100                                                                 311

Could anyone please help me on this.

Thanks,

KVR