首页 > 代码库 > 客户信息全SQL
客户信息全SQL
SELECT hp.party_name "客户名称", --客户名称 hca.account_number "客户编号", --客户编号 hca.cust_account_id "客户ID", --客户cust_acc_id decode(hca.customer_type, 'I', '内部客户', 'R', '外部客户', hca.customer_type) "客户类型", hca.customer_class_code "客户分类CODE", flv.meaning "客户分类", hca.price_list_id "价目表ID", cux_om_converged_pub.get_price_list_name(p_list_header_id => hca.price_list_id) "价目表", hps.attribute1 "停靠站", hps.attribute2 "路线", hps.party_site_id "客户PARTY_SITE_ID", --客户party_site_id hcsu.site_use_code "客户地点用途", decode(hl.country, 'CN', '中国', hl.country) "客户国家", --国家 hl.state "客户省份", --省 hl.city "客户城市", --市 hl.address1 "客户地址1", --地址行1 hl.address2 "客户地址2", --地址行2 amt.overall_credit_limit "信用限额", hou.name "业务实体", ---- hp2.party_name "联系人名称", decode(hl3.country, 'CN', '中国', hl3.country) "联系人国家", --国家 hl3.state "联系人省份", --省 hl3.city "联系人城市", --市 hl3.address1 "联系人地址1", --地址行1 hl3.address2 "联系人地址2", --地址行2 hp3.primary_phone_number 联系人电话 /* COUNT(1)*/ FROM --客户 hz_party_sites hps, --客户 party_sites hz_parties hp, --客户party hz_locations hl, --客户 locations hz_cust_accounts hca, hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hr_operating_units hou, ----客户联系人,联系方式 hz_cust_account_roles hcar, hz_parties hp2, --客户联系人 party hz_parties hp3, --客户联系方式 party hz_relationships hr, hz_locations hl3, --客户联系方式 locations hz_party_sites hps3, --客户联系方式 party_sites hz_org_contacts hoc, --仅用于显示职称 --客户配置文件 hz_cust_profile_amts amt, hz_customer_profiles pro, --- fnd_lookup_values_vl flv WHERE --客户信息关联 hps.party_id = hp.party_id AND hps.location_id = hl.location_id AND hca.party_id = hps.party_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND hcas.party_site_id = hps.party_site_id AND hcsu.org_id = hou.organization_id --客户联系人,联系方式关联 AND hca.cust_account_id = hcar.cust_account_id AND hcas.cust_acct_site_id = hcar.cust_acct_site_id AND hcar.role_type = 'CONTACT' AND hcar.party_id = hr.party_id AND hp3.party_id = hr.party_id AND hr.subject_id = hp2.party_id AND hr.directional_flag = 'F' AND hp2.party_type = 'PERSON' AND hoc.party_relationship_id = hr.relationship_id AND hps3.party_id = hp3.party_id AND hps3.location_id = hl3.location_id --客户配置文件 关联 AND amt.cust_account_profile_id = pro.cust_account_profile_id AND pro.cust_account_id = hca.cust_account_id --- AND flv.lookup_type = 'CUSTOMER CLASS' AND trunc(SYSDATE) BETWEEN nvl(flv.start_date_active, trunc(SYSDATE) - 1) AND nvl(flv.end_date_active, SYSDATE) AND flv.enabled_flag = 'Y' AND flv.lookup_code = hca.customer_class_code; /* SELECT * FROM HZ_CUST_ACCOUNT_ROLES */
客户信息全SQL
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。