ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【转】客户表结构SQL语句

【转】客户表结构SQL语句

原创 Linux操作系统 作者:papaya14 时间:2012-03-18 12:29:17 0 删除 编辑

1. 客户主数据SQL:

   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:

   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:

  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:

  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:

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:

  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:

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 hca.cust_account_id        = hcar.cust_account_id
         AND hca.cust_account_id        = p_customer_id
         AND hcar.cust_acct_site_id     = p_customer_site_id
       ORDER BY hr.creation_date;

8. 客户账户地点地址SQL:

   SELECT hp.party_id,
       hca.cust_account_id,
       hcasa.cust_acct_site_id,
       hcasa.bill_to_flag,
       hcasa.ship_to_flag,
       hcsua.site_use_id,
       hcasa.party_site_id,
       hcsua.site_use_code,
       hcsua.primary_flag,
       hcsua.location, hcsua.org_id
  FROM hz_parties             hp,
       hz_cust_accounts       hca,
       hz_party_sites         hps,
       hz_cust_acct_sites_all hcasa,
       hz_cust_site_uses_all  hcsua
 WHERE hp.party_id = hca.party_id
   AND hca.cust_account_id = hcasa.cust_account_id
   AND hcasa.party_site_id = hps.party_site_id
   AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

9 客户主配置文件SQL:

   SELECT * FROM hz_cust_profile_classes;
SELECT * FROM hz_customer_profiles;
SELECT * FROM hz_cust_prof_class_amts;
SELECT * FROM hz_cust_profile_amts; 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26555596/viewspace-718861/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-23

  • 博文量
    19
  • 访问量
    29018