复杂的视图

CREATE OR REPLACE VIEW ECC_FST.EMP_STATION_CUST_MAG_V AS
SELECT T.PROD_ID PRODUCT_LINE_ID,
       T.CUSTOMER_ID,
       PLV.PRODUCT_CODE,
       PLV.PRODUCT_LINE_NAME,
       SCM.SALES_CHAN_MANAGER_NAME,                    -- 经营体名称
       SCM.SALES_CHANN_MANAGER_ID,                     -- 经营体ID
       SCM.SALES_CHAN_MANAGER_CODE,                    -- 经营体编码
       CIV.DQD_CODE,                                   -- 大渠道编码
       SCP.SALE_CHANNEL_ID,                            -- 小渠道ID
       SCP.SALE_CHANNEL_CODE XQD_CODE,                 -- 小渠道CODE
       SCP.SALE_CHANNEL_NAME,                          -- 小渠道名称
       CIV.TRADE_ID,                                   -- 工贸ID
       CIV.TRADE_CODE,                                 -- 客户所属工贸code
       CIV.TRADE_NAME,                                 -- 客户所属工贸名称
       T.EMPLOYEE_ID,                                  -- 用户ID
       SE.LOGIN_ID EMPLOYEE_CODE,                      -- 用户编码
       SE.NAME EMPLOYEE_NAME,                          -- 用户名称
       NVL(T.NEXT_EMPLOYEE_ID, 0) AS NEXT_EMPLOYEE_ID, -- 下任人员ID
       NE.LOGIN_ID NEXT_EMPLOYEE_CODE,                 -- 下任人员CODE
       NE.NAME NEXT_EMPLOYEE_NAME,                     -- 下任人员名称
       T.FST_ROLE_LOOKUP_CODE ROLE,                    -- 职位类型
       T.FST_SUBMIT_STATION_ID,                        -- 岗位ID
       T.FST_SUBMIT_STATION_CODE,                      -- 岗位code
       T.FST_SUBMIT_STATION_NAME,                      -- 岗位名称
       CIV.CUSTOMER_CODE,                              -- 客户code
       CIV.CUSTOMER_NAME,                              -- 客户名称
       CIV.SUBTYPE_LOOKUP_CODE,                        -- 日日顺客户1004
       CIV.CUSTOMER_FLAG
  FROM
   (
WITH E_S_C_R AS
(
  SELECT
         SER.EMPLOYEE_ID,
         SER.NEXT_EMPLOYEE_ID,
         SC.FST_ROLE_LOOKUP_CODE,
         SC.FST_SUBMIT_STATION_ID,
         SC.FST_SUBMIT_STATION_CODE,
         SC.FST_SUBMIT_STATION_NAME,
         SCR.CUSTOMER_ID,
         sc.PRODUCT_SERIES_CODE,
         sc.PRODUCT_LINE_ID
  FROM
        ECC_FST.STATION_CONFIG            SC,
        ECC_FST.STATION_EMPLOYEE_RELATION SER,
        ECC_FST.STATION_CUSTOMER_RELATION SCR
  WHERE
        SC.DELETE_FLAG = 'F'
   AND SC.ENABLE_FLAG = 'T'
   AND SER.FST_SUBMIT_STATION_ID = SC.FST_SUBMIT_STATION_ID
   AND SER.ENABLE_FLAG = 'T'
   AND SER.DELETE_FLAG = 'F'
   AND SC.FST_SUBMIT_STATION_ID = SCR.FST_SUBMIT_STATION_ID
   AND SCR.ENABLE_FLAG = 'T'
   AND SCR.DELETE_FLAG = 'F'
)
SELECT EMP_PROD.PROD_ID, -- 产品线ID
       E_S_C_R.EMPLOYEE_ID, -- 当任人员ID
       E_S_C_R.NEXT_EMPLOYEE_ID, -- 下任人员ID
       E_S_C_R.FST_ROLE_LOOKUP_CODE,
       E_S_C_R.FST_SUBMIT_STATION_ID,
       E_S_C_R.FST_SUBMIT_STATION_CODE,
       E_S_C_R.FST_SUBMIT_STATION_NAME,
       E_S_C_R.CUSTOMER_ID
       /*COUNT(*)*/
  FROM E_S_C_R,
       (SELECT SEP.EMP_ID, SEP.PROD_ID
          FROM ECC_OMS.SYS_EMP_PROD SEP
         WHERE SEP.ENABLE_FLAG = 'T'
           AND SEP.DELETE_FLAG = 'F'
         GROUP BY SEP.EMP_ID, SEP.PROD_ID) EMP_PROD
 WHERE
    E_S_C_R.EMPLOYEE_ID = EMP_PROD.EMP_ID
    AND E_S_C_R.FST_ROLE_LOOKUP_CODE = 'YHJL'
UNION ALL
SELECT PL.PRODUCT_ID         PROD_ID, ---产品线id
       E_S_C_R.EMPLOYEE_ID, --用户ID
       E_S_C_R.NEXT_EMPLOYEE_ID, --下任人员ID
       E_S_C_R.FST_ROLE_LOOKUP_CODE    ROLE, ---职位类型
       E_S_C_R.FST_SUBMIT_STATION_ID, -- 岗位ID
       E_S_C_R.FST_SUBMIT_STATION_CODE, ---岗位code
       E_S_C_R.FST_SUBMIT_STATION_NAME, ---岗位名称
       E_S_C_R.CUSTOMER_ID
       /*count(*)*/
  FROM E_S_C_R,
       ECC_FND.BU_PRODUCT_GROUP_PL PL
 WHERE
  E_S_C_R.PRODUCT_SERIES_CODE = PL.GROUP_CODE
UNION ALL
SELECT V.PRODUCT_ID         PROD_ID, ---产品线id
       E_S_C_R.EMPLOYEE_ID, --用户ID
       E_S_C_R.NEXT_EMPLOYEE_ID, --下任人员ID
       E_S_C_R.FST_ROLE_LOOKUP_CODE    ROLE, ---职位类型
       E_S_C_R.FST_SUBMIT_STATION_ID, -- 岗位ID
       E_S_C_R.FST_SUBMIT_STATION_CODE, ---岗位code
       E_S_C_R.FST_SUBMIT_STATION_NAME, ---岗位名称
       E_S_C_R.CUSTOMER_ID
       /*count(*)*/
  FROM E_S_C_R,
       ECC_FND.PRODUCT_LINE_V V
 WHERE
       V.PRODUCT_ID = E_S_C_R.PRODUCT_LINE_ID
   AND NVL(E_S_C_R.PRODUCT_SERIES_CODE,'NVL') = 'NVL'
   AND E_S_C_R.FST_ROLE_LOOKUP_CODE = 'PR'
)t,
       ECC_FND.PRODUCT_LINE_V PLV,
       ECC_CUSTOMER.CUSTOMER_INFO_V CIV,
       ECC_FST.SALES_CHANNEL_PROPERTIES SCP, -- 大小渠道基本信息
       ECC_FST.SALES_CHANNEL_MANAGER_RELATION SCMR, -- 经营体与小渠道关系信息(按产品线)
       ECC_FST.SALES_CHANNEL_MANAGER SCM, -- 经营体基本信息
       ECC_OMS.SYS_EMPLOYEE SE,
       ECC_OMS.SYS_EMPLOYEE NE
 WHERE T.PROD_ID = PLV.PRODUCT_ID
   AND T.CUSTOMER_ID = CIV.CUSTOMER_ID
   AND CIV.XQD_CODE = SCP.SALE_CHANNEL_CODE
   AND SCP.ENABLE_FLAG = 'T'
   AND SCP.DELETE_FLAG = 'F'
   AND SCP.SALE_CHANNEL_ID = SCMR.SALE_CHANNEL_ID
   AND T.PROD_ID = SCMR.PRODUCT_LINE_ID
   AND SCMR.SALES_CHANN_MANAGER_ID = SCM.SALES_CHANN_MANAGER_ID
   AND SCM.ENABLE_FLAG = 'T'
   AND SCM.DELETE_FLAG = 'F'
   AND T.EMPLOYEE_ID = SE.ID
   AND SE.ENABLE = 'Y'
   AND T.NEXT_EMPLOYEE_ID = NE.ID(+);