客户表结构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; --客户地址基本信息表
浙公网安备 33010602011771号