博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

查询客户的SQL

Posted on 2013-01-16 18:52  奥客  阅读(493)  评论(0编辑  收藏  举报

(--客户需排除 贸易商、内部客户、代理商、佣金客户、竟争对手、供应商
          SELECT  hp.party_id, hca.cust_account_id customer_id
          , hp.PARTY_NUMBER CRM_CUST_NUMBER ,hp.PARTY_NAME customer_name ,  hca.ACCOUNT_NUMBER customer_number
          from HZ_PARTIES hp,  --CRM客户
          HZ_CUST_ACCOUNTS hca,  --ERP客户
          (--取CLIENT_SORT08贸易商编码、CLIENT_SORT09内部客户、CLIENT_SORT06代理商、CLIENT_SORT07佣金客户
          SELECT  ASSIGN.OWNER_TABLE_ID PARTY_ID
          FROM HZ_CODE_ASSIGNMENTS ASSIGN
               , AS_HZ_CLASS_CODE_DENORM CODE
          WHERE ASSIGN.CLASS_CODE = CODE.CLASS_CODE AND CODE.CLASS_CODE = CODE.ANCESTOR_CODE
            AND CODE.LANGUAGE = userenv('LANG') AND ASSIGN.CLASS_CATEGORY = CODE.CLASS_CATEGORY
            AND ASSIGN.OWNER_TABLE_NAME = 'HZ_PARTIES'
            AND ASSIGN.CLASS_CATEGORY = 'SECOM_CLIENT_SORT'             --判断是否为贸易商大类
            AND ASSIGN.STATUS = 'A' and ASSIGN.End_Date_Active is null  --类别为有效而且有效结束日期为空的
            AND ASSIGN.CLASS_CODE in ('CLIENT_SORT08','CLIENT_SORT09','CLIENT_SORT06','CLIENT_SORT07')  --取CLIENT_SORT08贸易商编码、CLIENT_SORT09内部客户、CLIENT_SORT06代理商、CLIENT_SORT07佣金客户
            group by ASSIGN.OWNER_TABLE_ID
          ) sst
          WHERE hp.PARTY_ID= hca.PARTY_ID(+)
          AND hp.party_id=sst.PARTY_ID(+)
          AND sst.PARTY_ID IS NULL   --需排除 贸易商、内部客户、代理商、佣金客户
          AND hp.CATEGORY_CODE in('PROSPECT','CUSTOMER') --只取PROSPECT 潜在客户 CUSTOMER 客户 ;排除 VENDOR 供应商  COMPETITOR OR 竟争对手
         ) rc