View For Getting Customer Details

DROP VIEW apps.xx_cust_master_v;

CREATE OR replace VIEW apps.xx_cust_master_v (org_id, operting_unit, party_number, customer_class, account_number, party_name, address1, address2, address3, address4, city, postal_code, state, country, county, party_site_number, status, payment_term, party_site_name, cust_account_id, cust_acct_site_id, usage, pan_no, ec_code, price_list_name, tan_no, cst_reg_no, vat_reg_no, type_ibd_dom, branch, rsm, branch1, credit_limit, credit_check, contact_name, primary_phone_number email_id, phone, creation_date, last_update_date ) AS
SELECT DISTINCT hcasa.org_id, 
                hou.name operting_unit, 
                hp.party_number, 
                hca.customer_class_code customer_class, 
                hca.account_number, 
                hp.party_name, 
                hl.address1, 
                hl.address2, 
                hl.address3, 
                hl.address4, 
                hl.city, 
                hl.postal_code, 
                hl.state, 
                hl.country, 
                hl.county, 
                hps.party_site_number, 
                hps.status, 
                ( 
                       SELECT description 
                       FROM   apps.so_payment_terms_v 
                       WHERE  payment_term_id = hca.payment_term_id 
                       AND    ROWNUM = 1) payment_term, 
                hps.party_site_name, 
                hcasa.cust_account_id, 
                hcasa.cust_acct_site_id, 
                ter.site_use_code usage, 
                --bill_to_flag, 
                jcca.pan_no, 
                jcca.ec_code, 
                ( 
                       SELECT name 
                       FROM   apps.qp_list_headers 
                       WHERE  list_header_id = jcca.price_list_id) price_list_name, 
                jcca.tan_no, 
                jcca.cst_reg_no, 
                jcca.vat_reg_no, 
                ter.type_ibd_dom, 
                ter.branch, 
                ter.rsm, 
                ter.branch1, 
                hcpa.overall_credit_limit credit_limit, 
                hcp.credit_checking       credit_check, 
                ( 
                       SELECT hp1.party_name --CUSTOMER ACCOUNT CONTACT INFO 
                       FROM   apps.hz_cust_account_roles hcar , 
                              apps.hz_parties hp1 , 
                              apps.hz_relationships hr 
                       WHERE  1 = 1 
                       AND    hr.object_id = hp.party_id 
                       AND    hr.subject_id = hp1.party_id 
                       AND    hcar.party_id = hr.party_id 
                       AND    hcar.cust_acct_site_id IS NULL 
                       AND    hcar.status LIKE 'A' ) contact_name, 
                hp.primary_phone_number              primary_phone_number, 
                ( 
                       SELECT email_address 
                       FROM   apps.hz_contact_points 
                       WHERE  owner_table_id = hp.party_id 
                       AND    contact_point_type = 'EMAIL' 
                       AND    owner_table_name = 'HZ_PARTIES' 
                       AND    primary_flag = 'Y' 
                       AND    ROWNUM = 1) email_id, 
                ( 
                       SELECT phone_number 
                       FROM   apps.hz_contact_points 
                       WHERE  owner_table_id = hp.party_id 
                       AND    contact_point_type = 'PHONE' 
                       AND    owner_table_name = 'HZ_PARTIES' 
                       AND    primary_flag = 'Y' 
                       AND    ROWNUM = 1) phone, 
                hp.creation_date, 
                hcasa.last_update_date 
                --,ter.branch1 
FROM            apps.hz_locations hl, 
                apps.hz_party_sites hps, 
                apps.hz_cust_acct_sites_all hcasa, 
                apps.hz_parties hp, 
                apps.hz_cust_accounts hca, 
                apps.hr_operating_units hou, 
                apps.jai_cmn_cus_addresses jcca, 
                apps.hz_customer_profiles hcp, 
                apps.hz_cust_profile_amts hcpa, 
                ( 
                       SELECT org_id, 
                              cust_acct_site_id, 
                              rt.segment1 type_ibd_dom, 
                              rt.segment2 branch, 
                              rt.segment3 rsm, 
                              rt.segment4 branch1, 
                              hcu.site_use_code 
                       FROM   apps.hz_cust_site_uses_all hcu, 
                              apps.ra_territories rt 
                       WHERE  rt.territory_id(+) = hcu.territory_id --and hcu.org_id in(103,105,114,134,106,195,578,395,396,397) 
                ) ter 
WHERE           hcasa.party_site_id = hps.party_site_id 
AND             hps.location_id = hl.location_id 
AND             hp.party_id = hca.party_id 
AND             hp.party_id = hps.party_id 
AND             hca.cust_account_id = hcasa.cust_account_id 
AND             hcasa.org_id = hou.organization_id 
AND             hcasa.cust_account_id = jcca.customer_id(+) 
AND             hcasa.cust_acct_site_id = jcca.address_id(+) 
AND             hcasa.cust_acct_site_id = ter.cust_acct_site_id 
AND             hcp.cust_account_profile_id = hcpa.cust_account_profile_id(+) 
AND             hp.party_id = hcp.party_id(+) 
AND             hca.cust_account_id = hcp.cust_account_id 
AND             hcasa.org_id = ter.org_id 
AND             hp.party_type = 'ORGANIZATION' 
AND             hps.status = 'A' 
AND             hcasa.status = 'A' 
AND             hp.status = 'A' 
AND             hca.status = 'A' 
                --And hcasa.bill_to_flag in('P','Y') 
                --and hcasa.org_id = nvl(:p_organiztion_id,hcasa.org_id) 
                --and trunc(hp.CREATION_DATE) between trunc(:p_form_creation_date) and trunc(:p_to_creation_date) 
                --and trunc(hcasa.LAST_UPDATE_DATE) between trunc(:p_form_modified_date) and trunc(:p_to_modified_date)
                --and nvl(ter.branch1,'X')=nvl(:p_branch,nvl(ter.branch1,'X')) 
ORDER BY        1, 
                to_number (hp.party_number), 
                hp.party_name, 
                hca.account_number;