客户表结构SQL

1. 客户主数据SQL:

view plaincopy to clipboardprint?
01.SELECT hca.cust_account_id     customer_id,  
02.             hp.party_number         customer_number,  
03.             hp.party_name           customer_name,  
04.             hp.party_name           customer_short_name,  
05.             hca.customer_type       customer_type,  
06.             alt.meaning             customer_type_meaning,  
07.             hca.customer_class_code customer_class,  
08.             alc.meaning             customer_class_meaning,  
09.             hp.tax_reference        tax_registered_name,  
10.             rt.name                 term_name,  
11.             hca.creation_date       creation_date,  
12.             hca.created_by          created_by,  
13.             hca.last_update_date    last_update_date,  
14.             hca.last_updated_by     last_updated_by,  
15.             hca.last_update_login   last_update_login  
16.        FROM hz_parties           hp,  
17.             hz_cust_accounts     hca,  
18.             ar_lookups           alt,  
19.             ar_lookups           alc,  
20.             hz_customer_profiles hcp,  
21.             ra_terms             rt  
22.       WHERE hp.party_id               = hca.party_id  
23.         AND hca.customer_type         = alt.lookup_code(+)  
24.         AND alt.lookup_type           = 'CUSTOMER_TYPE' 
25.         AND hca.customer_class_code   = alc.lookup_code(+)  
26.         AND alc.lookup_type(+)        = 'CUSTOMER CLASS' 
27.         AND hca.cust_account_id       = hcp.cust_account_id(+)  
28.         AND hcp.standard_terms        = rt.term_id(+) 
SELECT hca.cust_account_id     customer_id,
             hp.party_number         customer_number,
             hp.party_name           customer_name,
             hp.party_name           customer_short_name,
             hca.customer_type       customer_type,
             alt.meaning             customer_type_meaning,
             hca.customer_class_code customer_class,
             alc.meaning             customer_class_meaning,
             hp.tax_reference        tax_registered_name,
             rt.name                 term_name,
             hca.creation_date       creation_date,
             hca.created_by          created_by,
             hca.last_update_date    last_update_date,
             hca.last_updated_by     last_updated_by,
             hca.last_update_login   last_update_login
        FROM hz_parties           hp,
             hz_cust_accounts     hca,
             ar_lookups           alt,
             ar_lookups           alc,
             hz_customer_profiles hcp,
             ra_terms             rt
       WHERE hp.party_id               = hca.party_id
         AND hca.customer_type         = alt.lookup_code(+)
         AND alt.lookup_type           = 'CUSTOMER_TYPE'
         AND hca.customer_class_code   = alc.lookup_code(+)
         AND alc.lookup_type(+)        = 'CUSTOMER CLASS'
         AND hca.cust_account_id       = hcp.cust_account_id(+)
         AND hcp.standard_terms        = rt.term_id(+)

2. 客户收款方法SQL:

view plaincopy to clipboardprint?
01.SELECT arm.name receipt_method_name  
02.       FROM hz_cust_accounts        hca,  
03.            ra_cust_receipt_methods rcrm,  
04.            ar_receipt_methods      arm  
05.      WHERE hca.cust_account_id    = rcrm.customer_id  
06.        AND rcrm.receipt_method_id = arm.receipt_method_id  
07.        AND hca.cust_account_id    = p_customer_id  
08.      ORDER BY rcrm.creation_date; 
SELECT arm.name receipt_method_name
        FROM hz_cust_accounts        hca,
             ra_cust_receipt_methods rcrm,
             ar_receipt_methods      arm
       WHERE hca.cust_account_id    = rcrm.customer_id
         AND rcrm.receipt_method_id = arm.receipt_method_id
         AND hca.cust_account_id    = p_customer_id
       ORDER BY rcrm.creation_date;

3. 客户账户层银行账户信息SQL:

view plaincopy to clipboardprint?
01.SELECT hca.cust_account_id   cust_account_id,  
02.             hp.party_id           party_id,  
03.             bank.party_id         bank_id,  
04.             bank.party_name       bank_name,  
05.             branch.party_id       branch_id,  
06.             branch.party_name     bank_branch_name,  
07.             ieba.bank_account_num bank_account_num  
08.        FROM hz_cust_accounts      hca,  
09.             hz_parties            hp,  
10.             iby_account_owners    iao,  
11.             iby_ext_bank_accounts ieba,  
12.             hz_parties            bank,  
13.             hz_parties            branch  
14.       WHERE hca.party_id            = hp.party_id  
15.         AND hp.party_id             = iao.account_owner_party_id(+)  
16.         AND iao.ext_bank_account_id = ieba.ext_bank_account_id(+)  
17.         AND ieba.bank_id            = bank.party_id(+)  
18.         AND ieba.branch_id          = branch.party_id(+)  
19.         AND hca.cust_account_id     = p_customer_id  
20.       ORDER BY ieba.creation_date;              
SELECT hca.cust_account_id   cust_account_id,
             hp.party_id           party_id,
             bank.party_id         bank_id,
             bank.party_name       bank_name,
             branch.party_id       branch_id,
             branch.party_name     bank_branch_name,
             ieba.bank_account_num bank_account_num
        FROM hz_cust_accounts      hca,
             hz_parties            hp,
             iby_account_owners    iao,
             iby_ext_bank_accounts ieba,
             hz_parties            bank,
             hz_parties            branch
       WHERE hca.party_id            = hp.party_id
         AND hp.party_id             = iao.account_owner_party_id(+)
         AND iao.ext_bank_account_id = ieba.ext_bank_account_id(+)
         AND ieba.bank_id            = bank.party_id(+)
         AND ieba.branch_id          = branch.party_id(+)
         AND hca.cust_account_id     = p_customer_id
       ORDER BY ieba.creation_date;             

4. 客户开户行地址信息SQL:

view plaincopy to clipboardprint?
01.SELECT hl.country  || '-' ||   
02.             hl.province || '-' ||   
03.             hl.city     || '-' ||  
04.             hl.address1 || '-' ||   
05.             hl.address2 || '-' ||   
06.             hl.address3 || '-' ||  
07.             hl.address4 bank_address  
08.        FROM hz_party_sites hps,   
09.             hz_locations   hl  
10.       WHERE hps.location_id = hl.location_id  
11.         AND hps.party_id    = p_bank_id  
12.       ORDER BY hps.creation_date; 
SELECT hl.country  || '-' ||
             hl.province || '-' ||
             hl.city     || '-' ||
             hl.address1 || '-' ||
             hl.address2 || '-' ||
             hl.address3 || '-' ||
             hl.address4 bank_address
        FROM hz_party_sites hps,
             hz_locations   hl
       WHERE hps.location_id = hl.location_id
         AND hps.party_id    = p_bank_id
       ORDER BY hps.creation_date;

5. 客户账户层联系人信息：联系人、电话、手机和Email SQL:

view plaincopy to clipboardprint?
01.SELECT hr.party_id            party_id,  
02.             hcar.cust_account_id   cust_account_id,  
03.             hcar.cust_acct_site_id cust_acct_site_id,  
04.             hp.person_last_name   || ' ' ||   
05.             hp.person_middle_name || ' ' ||  
06.             hp.person_first_name   contact_person,  
07.             hcpp.phone_area_code   phone_area_code,  
08.             hcpp.phone_number      phone_number,  
09.             hcpp.phone_extension   phone_extension,  
10.             hcpm.phone_area_code   mobile_phone_area_code,  
11.             hcpm.phone_number      mobile_phone_number,  
12.             hcpe.email_address     email_address  
13.        FROM hz_relationships      hr,  
14.             hz_cust_account_roles hcar,  
15.             hz_org_contacts       hoc,  
16.             hz_contact_points     hcpp,  
17.             hz_contact_points     hcpm,  
18.             hz_contact_points     hcpe,  
19.             hz_parties            hp,  
20.             hz_cust_accounts      hca  
21.       WHERE hr.object_id               = hp.party_id  
22.         AND hr.party_id                = hcar.party_id  
23.         AND hr.relationship_id         = hoc.party_relationship_id(+)  
24.         AND hcpp.owner_table_id(+)     = hr.party_id  
25.         AND hcpm.owner_table_id(+)     = hr.party_id  
26.         AND hcpe.owner_table_id(+)     = hr.party_id  
27.         AND hr.object_type             = 'PERSON' 
28.         AND hr.relationship_code(+)    = 'CONTACT' 
29.         AND hcpp.owner_table_name(+)   = 'HZ_PARTIES' 
30.         AND hcpm.owner_table_name(+)   = 'HZ_PARTIES' 
31.         AND hcpe.owner_table_name(+)   = 'HZ_PARTIES' 
32.         AND hcpp.contact_point_type(+) = 'PHONE' 
33.         AND hcpp.phone_line_type(+)    = 'GEN' 
34.         AND hcpm.contact_point_type(+) = 'PHONE' 
35.         AND hcpm.phone_line_type(+)    = 'MOBILE' 
36.         AND hcpe.contact_point_type(+) = 'EMAIL' 
37.         AND hcpe.phone_line_type       IS NULL  
38.         AND hr.subject_id              = hca.party_id  
39.         AND hcar.cust_acct_site_id     IS NULL  
40.         AND hca.cust_account_id        = p_customer_id  
41.       ORDER BY hr.creation_date; 
SELECT hr.party_id            party_id,
             hcar.cust_account_id   cust_account_id,
             hcar.cust_acct_site_id cust_acct_site_id,
             hp.person_last_name   || ' ' ||
             hp.person_middle_name || ' ' ||
             hp.person_first_name   contact_person,
             hcpp.phone_area_code   phone_area_code,
             hcpp.phone_number      phone_number,
             hcpp.phone_extension   phone_extension,
             hcpm.phone_area_code   mobile_phone_area_code,
             hcpm.phone_number      mobile_phone_number,
             hcpe.email_address     email_address
        FROM hz_relationships      hr,
             hz_cust_account_roles hcar,
             hz_org_contacts       hoc,
             hz_contact_points     hcpp,
             hz_contact_points     hcpm,
             hz_contact_points     hcpe,
             hz_parties            hp,
             hz_cust_accounts      hca
       WHERE hr.object_id               = hp.party_id
         AND hr.party_id                = hcar.party_id
         AND hr.relationship_id         = hoc.party_relationship_id(+)
         AND hcpp.owner_table_id(+)     = hr.party_id
         AND hcpm.owner_table_id(+)     = hr.party_id
         AND hcpe.owner_table_id(+)     = hr.party_id
         AND hr.object_type             = 'PERSON'
         AND hr.relationship_code(+)    = 'CONTACT'
         AND hcpp.owner_table_name(+)   = 'HZ_PARTIES'
         AND hcpm.owner_table_name(+)   = 'HZ_PARTIES'
         AND hcpe.owner_table_name(+)   = 'HZ_PARTIES'
         AND hcpp.contact_point_type(+) = 'PHONE'
         AND hcpp.phone_line_type(+)    = 'GEN'
         AND hcpm.contact_point_type(+) = 'PHONE'
         AND hcpm.phone_line_type(+)    = 'MOBILE'
         AND hcpe.contact_point_type(+) = 'EMAIL'
         AND hcpe.phone_line_type       IS NULL
         AND hr.subject_id              = hca.party_id
         AND hcar.cust_acct_site_id     IS NULL
         AND hca.cust_account_id        = p_customer_id
       ORDER BY hr.creation_date;

6. 客户地址SQL:

view plaincopy to clipboardprint?
01.SELECT hcasa.cust_acct_site_id customer_site_id,  
02.            hcasa.cust_account_id   customer_id,  
03.            hps.party_site_number   customer_site_code,  
04.            hps.party_site_name     customer_site_name,  
05.            hl.address1             address_line1,  
06.            hl.address2             address_line2,  
07.            hl.address3             address_line3,  
08.            hl.address4             address_line4,  
09.            hcasa.org_id            org_id,  
10.            hl.country              country,  
11.            hl.province             province,  
12.            hl.city                 city,  
13.            hl.county               county,  
14.            hl.postal_code          zip,  
15.            hcasa.bill_to_flag      bill_to_flag,  
16.            hcasa.ship_to_flag      ship_to_flag,  
17.            hca.creation_date       creation_date,  
18.            hca.created_by          created_by,  
19.            hca.last_update_date    last_update_date,  
20.            hca.last_updated_by     last_updated_by,  
21.            hca.last_update_login   last_update_login  
22.       FROM hz_cust_accounts       hca,  
23.            hz_cust_acct_sites_all hcasa,  
24.            hz_party_sites         hps,  
25.            hz_locations           hl  
26.      WHERE hca.cust_account_id = hcasa.cust_account_id  
27.        AND hcasa.party_site_id = hps.party_site_id  
28.        AND hps.location_id     = hl.location_id  
29.        AND hca.cust_account_id = p_customer_id; --客户地址基本信息表 
SELECT hcasa.cust_acct_site_id customer_site_id,
             hcasa.cust_account_id   customer_id,
             hps.party_site_number   customer_site_code,
             hps.party_site_name     customer_site_name,
             hl.address1             address_line1,
             hl.address2             address_line2,
             hl.address3             address_line3,
             hl.address4             address_line4,
             hcasa.org_id            org_id,
             hl.country              country,
             hl.province             province,
             hl.city                 city,
             hl.county               county,
             hl.postal_code          zip,
             hcasa.bill_to_flag      bill_to_flag,
             hcasa.ship_to_flag      ship_to_flag,
             hca.creation_date       creation_date,
             hca.created_by          created_by,
             hca.last_update_date    last_update_date,
             hca.last_updated_by     last_updated_by,
             hca.last_update_login   last_update_login
        FROM hz_cust_accounts       hca,
             hz_cust_acct_sites_all hcasa,
             hz_party_sites         hps,
             hz_locations           hl
       WHERE hca.cust_account_id = hcasa.cust_account_id
         AND hcasa.party_site_id = hps.party_site_id
         AND hps.location_id     = hl.location_id
         AND hca.cust_account_id = p_customer_id; --客户地址基本信息表

7. 客户账户层地址contact person信息:phone,mobile,email SQL:

view plaincopy to clipboardprint?
01.SELECT hr.party_id            party_id,  
02.             hcar.cust_account_id   cust_account_id,  
03.             hcar.cust_acct_site_id cust_acct_site_id,  
04.             hp.person_last_name   || ' ' ||   
05.             hp.person_middle_name || ' ' ||  
06.             hp.person_first_name   contact_person,  
07.             hcpp.phone_area_code   phone_area_code,  
08.             hcpp.phone_number      phone_number,  
09.             hcpp.phone_extension   phone_extension,  
10.             hcpm.phone_area_code   mobile_phone_area_code,  
11.             hcpm.phone_number      mobile_phone_number,  
12.             hcpe.email_address     email_address  
13.        FROM hz_relationships      hr,