客户APP(我的查看募集期产品)

代码如下：

 SELECT *
   FROM (SELECT FP.ID 产品ID,
                FP.CPDM 产品代码,
                CASE
                  WHEN YY.CZYYTJ = 4 THEN
                   XM.CPMC_APP
                  ELSE
                   XM.XMMC
                END 产品名称,
                CASE
                  WHEN YY.CZYYTJ = 4 THEN
                   XM.CPMC_APP
                  ELSE
                   XM.XMMC
                END 产品全称,
                YY.ID 预约ID,
                YY.GLSP 视频ID,
                'DZHT_' || YY.ID || '.pdf' 电子合同名称,
                '/home/crm2/data/app/TCUST_APP_KHQYLS/' ||
                (SELECT HT.ID
                   FROM TCUST_APP_KHQYLS HT
                  WHERE HT.CPYYID = YY.ID) || '.FJ' 电子合同存放路径,
                CASE
                  WHEN NVL(FP.SFJZ, 0) = 0 THEN
                   (SELECT SYJB.SYL
                      FROM TFP_CPSYJB SYJB
                     WHERE SYJB.ID = YY.SYJB) || '%'
                  WHEN NVL(FP.SFJZ, 0) = 1 THEN
                   '浮动'
                END "业绩比较基准",
                NVL(FP.SFJZ, 0) 是否净值,
                YY.YYGMJE / 10000 预约金额, --万元
                (SELECT SYJB.QX || SYJB.QXDW
                   FROM TFP_CPSYJB SYJB
                  WHERE SYJB.ID = YY.SYJB) 产品期限,
                --预约状态ID
                CASE
                  WHEN YY.YYZT = -1 THEN
                   -1
                  WHEN YY.YYZT = 1 THEN
                   1
                  WHEN YY.YYZT = 2 AND
                       (SELECT COUNT(1)
                          FROM TCUST_APP_KHQYLS BD
                         WHERE BD.CPYYID = YY.ID) = 0 AND
                       (SELECT COUNT(1)
                          FROM lchtrsgsqbd BD
                         WHERE BD.CPYYID = YY.ID) = 0 THEN
                   2
                  WHEN YY.YYZT = 2 AND
                       ((SELECT COUNT(1)
                           FROM TCUST_APP_KHQYLS BD
                          WHERE BD.CPYYID = YY.ID) > 0 OR
                       (SELECT COUNT(1)
                           FROM lchtrsgsqbd BD
                          WHERE BD.CPYYID = YY.ID
                            and shzt <> -1) > 0) THEN
                   3
                END 预约状态ID,
                
                --预约状态
                CASE
                  WHEN YY.YYZT = -1 THEN
                   '已关闭'
                  WHEN YY.YYZT = 1 THEN
                   '排队中'
                  WHEN YY.YYZT = 2 AND
                       (SELECT COUNT(1)
                          FROM TCUST_APP_KHQYLS BD
                         WHERE BD.CPYYID = YY.ID) = 0 AND
                       (SELECT COUNT(1)
                          FROM lchtrsgsqbd BD
                         WHERE BD.CPYYID = YY.ID) = 0 THEN
                   '待签约'
                  WHEN YY.YYZT = 2 AND
                       ((SELECT COUNT(1)
                           FROM TCUST_APP_KHQYLS BD
                          WHERE BD.CPYYID = YY.ID) > 0 OR
                       (SELECT COUNT(1)
                           FROM lchtrsgsqbd BD
                          WHERE BD.CPYYID = YY.ID
                            and shzt <> -1) > 0) THEN
                   '待成立'
                END 预约状态,
                
                --是否可修改金额 0|否;1|是
                CASE
                  WHEN (YY.YYZT = 1 /*AND XM.SJBZ=1*/
                       AND (SELECT COUNT(1)
                               FROM TCUST_APP_YYXG XG
                              WHERE XG.CPYYID = YY.ID
                                AND SHZT IN (0, 1)) = 0) OR
                       (YY.YYZT = 2 AND
                       (SELECT COUNT(1)
                           FROM TCUST_APP_YYXG XG
                          WHERE XG.CPYYID = YY.ID
                            AND SHZT IN (0, 1)) = 0 AND
                       (SELECT COUNT(1)
                           FROM TCUST_APP_KHQYLS LS
                          WHERE LS.CPYYID = YY.ID) = 0 AND
                       (SELECT COUNT(1)
                           FROM TSPXXB SP
                          WHERE SP.ID = YY.GLSP
                            AND LZZT IN (1, 2)) = 0
                       /*AND XM.SJBZ=1*/
                       ) THEN
                   1
                  ELSE
                   0
                END 是否可修改金额,
                
                --是否允许双录 0|否;1|是
                CASE
                  WHEN YY.YYZT = 2 AND
                       (SELECT COUNT(1)
                          FROM TCUST_APP_YYXG XG
                         WHERE XG.CPYYID = YY.ID
                           AND SHZT IN (0, 1)) = 0 AND
                       (SELECT COUNT(1)
                          FROM TSPXXB SP
                         WHERE SP.CPYYID = YY.ID
                           AND LZZT IN (1, 2)) = 0
                  
                   THEN
                   1
                  ELSE
                   0
                END 是否允许双录,
                
                --是否允许签约 0|否;1|是
                CASE
                  WHEN YY.YYZT = 2 AND YY.CZYYTJ = 4
                      --WENGJIE 20190121 屏蔽上架控制、新增必须有模板控制
                      --AND XM.SJBZ=1
                       AND EXISTS
                   (select 1
                          from TDZHTYSMB MB
                         WHERE MB.CPID in
                               (SELECT ID FROM TFP_CPDM F WHERE XTJH = FP.XTJH)
                           AND SFSX = 1)
                      --YANGPEI 20190319 获取之前的合同模板
                       AND (SELECT COUNT(1)
                              FROM TCUST_APP_KHQYLS BD
                             WHERE BD.CPYYID = YY.ID) = 0 AND
                       (SELECT COUNT(1)
                          FROM LCHTRSGSQBD BD
                         WHERE BD.CPYYID = YY.ID
                           AND SHZT <> -1) = 0 AND
                       (SELECT COUNT(1)
                          FROM TCUST_APP_YYXG XG
                         WHERE XG.CPYYID = YY.ID
                           AND SHZT IN (0, 1)) = 0 AND
                       (YY.RYXX IS NOT NULL AND NOT EXISTS
                        (SELECT 1
                           FROM T_CUST_APP_GXFP
                          WHERE USR_ID = YY.USR_ID
                            AND FPZT != 3)) THEN
                   1
                  ELSE
                   0
                END 是否允许签约,
                
                (SELECT NVL(MAX(LZZT), 0)
                   FROM TSPXXB SP
                  WHERE SP.ID = YY.GLSP) 录制状态, --0|未录制;1|已录制;2|已上传;
                (SELECT NOTE
                   FROM TXTDM ZD
                  WHERE FLDM = 'TZLY'
                    AND ZD.IBM = FP.TZLY) 产品类型,
                (SELECT SP.FJH FROM TSPXXB SP WHERE SP.ID = YY.GLSP) 双录房间号,
                (SELECT SP.ANYCHATID FROM TSPXXB SP WHERE SP.ID = YY.GLSP) ANYCHATID,
                CASE
                  WHEN (SELECT COUNT(1)
                          FROM TCUST_APP_KHQYLS BD
                         WHERE BD.CPYYID = YY.ID) = 0 THEN
                   0
                  ELSE
                   1
                END 是否查看合同
                
           FROM TFP_CPDM FP, TCPYY YY, TXSXM XM
           
          WHERE FP.ID = YY.CPID
            AND YY.XSXM = XM.ID
            AND YY.YYZT <> -1
            AND (FP.CLRQ IS NULL /* 20190319 YANGPEI 产品未成立即显示在募集中*/
                OR NOT EXISTS (SELECT 1
                                 from THTXX HT, THTSYR SYR
                                WHERE HT.CPYYID = YY.ID
                                  AND SYR.HTXX = HT.ID))
            AND 1 = 1
            AND YY.QZKHID = 16468)
  ORDER BY NVL(双录房间号, 0) DESC,
           REPLACE(预约状态ID, -1, 99) ASC,
           预约ID DESC
           
          --select *  from tqzkhxx where khxm='张文杰' order by id desc 
          -- SELECT *  FROM tcpyy where khmc='涂志文'   
          --select *  from tspxxb  where cpyyid in(7374)
          --select * from tfp_cpdm where cpdm='FZ0C56'