预付款单列表（付款计划模式)

SELECT PBP.ID AS ID, --ID
       FBH.BOE_NO, --预付款单号
       FBH.BOE_TYPE_CODE AS FORM_TYPE_CODE, --单据类型编码
       (SELECT FFT.FORM_TYPE_NAME
          FROM FSP_EAS.FD_FORM_TYPE FFT
         WHERE FFT.FORM_TYPE_CODE = FBH.BOE_TYPE_CODE) AS FORM_TYPE_NAME, --单据类型名称
       FBH.OPERATION_TYPE_CODE BIZ_CATEGORY_CODE, --业务类型编码
       (SELECT BC.BIZ_CATEGORY_NAME
          FROM FSP_EAS.FD_BIZ_CATEGORY BC
         WHERE BC.BIZ_CATEGORY_CODE = FBH.OPERATION_TYPE_CODE) AS BIZ_CATEGORY_NAME, -- 业务类型（业务大类）
       FBH.VENDOR_ID, --供应商ID
       FV.VENDOR_CODE, --供应商编码
       FV.VENDOR_NAME AS VENDOR_NAME, --供应商名称
       FBH.SUBMIT_DATE, --预付款单提交时间
       (SELECT TO_DATE(L.APPROVAL_TIME, 'YYYY-MM-DD HH24:MI:SS')
          FROM FSP_EAS.XCFSC_APPROVAL_LOG L
         WHERE L.BOE_NO = FBH.BOE_NO
           AND L.STATUS = 'APPROVED'
           AND L.SYNC_STATUS = '30'
           and rownum = 1) AS GX_APPROVAL_TIME, /*共享审核完成时间*/
       FBH.PAYMENT_STATUS,
       DECODE(FBH.PAYMENT_STATUS,
              10,
              '待付款',
              20,
              '付款中',
              30,
              '已付款',
              110,
              '未付款',
              100,
              '不付款') PAYMENT_STATUS_NAME, -- 报账单付款状态
       FBH.PAYMENT_DATE, --报账单付款时间
       FBH.PAYMENT_AMOUNT, --合计付款金额
       FBH.CREATED_BY CREATED_ID,
       (SELECT FUC.EMPLOY_NO
          FROM FSP_EAS.FA_USER FUC
         WHERE FUC.ID = FBH.CREATED_BY) AS CREATED_CODE, -- 填单人工号
       (SELECT FUC.USER_NAME
          FROM FSP_EAS.FA_USER FUC
         WHERE FUC.ID = FBH.CREATED_BY) AS CREATED_NAME, -- 填单人
       FBH.EMPLOYEE_ID,
       (SELECT FUE.EMPLOY_NO
          FROM FSP_EAS.FA_USER FUE
         WHERE FUE.ID = FBH.EMPLOYEE_ID) AS EMPLOY_CODE, -- 报账人工号
       (SELECT FUE.USER_NAME
          FROM FSP_EAS.FA_USER FUE
         WHERE FUE.ID = FBH.EMPLOYEE_ID) AS EMPLOY_NAME, -- 报账人
       FBH.APPLY_POST_ID,
       (SELECT FO.NAME
          FROM FSP_EAS.FA_ORG FO
         WHERE FO.ID = FBH.APPLY_POST_ID) AS APPROVAL_POST_NAME, -- 岗位
       FBH.APPROVAL_DEPT_ID,
       (SELECT FO.NAME
          FROM FSP_EAS.FA_ORG FO
         WHERE FO.ID = FBH.APPROVAL_DEPT_ID) AS APPROVAL_DEPT_NAME, -- 审批部门
       FBH.LE_ID,
       FBH.LE_CODE,
       (SELECT FL.LE_NAME FROM FSP_EAS.FAM_LE FL WHERE FL.ID = FBH.LE_ID) AS LE_NAME, -- 核算主体
       FBH.DEPT_ID OU_ID,
       (SELECT FOU.OU_NAME
          FROM FSP_EAS.FAM_OU FOU
         WHERE FOU.ID = FBH.DEPT_ID) AS OU_NAME, -- 成本中心（费用承担部门）
       PCP.PRODUCT_COST_TYPE, --费用类型
       CC.ID AS CONTRACT_ID, --合同表ID
       CC.CODE AS CONTRACT_CODE, --合同编码
       CC.CONTRACT_STATUS,
       CC.EXECUTE_STATUS,
       CC.SALES_TYPE, --合同签订类型
       CC.CITY_CODE, --城市
       CC.CITY_NAME,
       U.ID AS OUR_SIGNER_ID,
       U.LOGIN_NAME AS OUR_SIGNER_CODE,
       U.USER_NAME AS OUR_SIGNER_NAME,
       CC.DELIVER_CODE,
       CC.DELIVER_NAME,
       CC.DELIVER_NAME_DEPT,
       BCP.CODE AS PROJECT_CODE, --楼盘编码
       BCP.PROJECT_NAME, --楼盘名称
       PCP."IS_BIG_PROPERTY_CODE", --物业类型
       PCP."IS_BIG_PROPERTY_DESC",
       (SELECT C.CODE
          FROM FSP_CMS.SYS_DATA_CODE C
        
         WHERE C.PARENT_ID =
               (SELECT ID
                  FROM FSP_CMS.SYS_DATA_CODE S
                 WHERE S.CODE = '56a04c0d73661a')
           AND C.VALUE = PCP."PROJECT_PROPERTY"
        
        ) AS PROJECT_PROPERTY_CODE, --社区类型编码
       
       PCP."PROJECT_PROPERTY", --社区类型名称
       PCP."TERMINAL_TYPE", --终端类型编码
       CASE
         WHEN PCP.TERMINAL_TYPE = '01' THEN
          '梯内屏'
         WHEN PCP.TERMINAL_TYPE = '02' THEN
          '梯外屏'
         WHEN PCP.TERMINAL_TYPE = '03' THEN
          '框架'
         ELSE
          ''
       END AS TERMINAL_TYPE_DESC, --终端类型名称
       
       PCP.START_DATE, --付款周期开始时间
       PCP.END_DATE, --付款周期结束时间
       PCP.PAY_PERIOD, --付款期数
       (SELECT QA3.SIGNING_NUM
          FROM FSP_CMS.CMS_CONTRACT_APPLY            QA1,
               FSP_CMS.CMS_CONTRACT_APPLY_PROJECT    QA2,
               FSP_CMS.CONTRACT_APPLY_INSTALL_POINTS QA3
         WHERE QA1.ID = QA2.APPLY_ID
           AND QA2.ID = QA3.PROJECT_ID
           AND QA3.TERMINAL_TYPE = PCP.TERMINAL_TYPE
           AND QA2.CRM_PROJECT_ID = PCP.CRM_PROJECT_ID
           AND PCP.RELATION_TYPE = '01'
           AND QA1.ID = CC.CONTRACT_APPLY_ID
           AND ROWNUM = 1) AS LAST_SIGNING_NUM, --签约数
       
       PCP.SIGNING_NUM, --实际点位数
       (SELECT QPC.YEAR_PRICE
          FROM FSP_CMS.CMS_CONTRACT_PAYMENT QP
         INNER JOIN FSP_CMS.CONTRACT_INSTALL_POINTS QPS
            ON QP.INSTAL_POINTS_ID = QPS.ID
         INNER JOIN FSP_CMS.CMS_CONTRACT_PRICE QPC
            ON QPS.ID = QPC.INSTAL_POINTS_ID
         WHERE PCP.RELATION_TYPE = '01'
           AND PCP.RELATION_ID = QP.ID
           AND QP.START_DATE >= QPC.START_TIME
           AND QP.END_DATE <= QPC.END_TIME
           AND ROWNUM = 1) YEAR_PRICE, --年单价
       
       PCP.AMOUNT       AS PRODUCT_PAYMENT_AMOUNT, --应付金额
       PBP.AMOUNT       AS CURRENT_PAYMENT_AMOUNT, --本次付款金
       FBH.ID           AS BOE_HEADER_ID,
       cc.CONTRACT_TYPE, --合同类型
       cc.contract_mode --合作模式
  FROM FSP_EAS.PRODUCT_BOE_PAYMENT PBP
 INNER JOIN FSP_EAS.FEA_BOE_HEADER FBH
    ON FBH.ID = PBP.BOE_HEADER_ID
 INNER JOIN FSP_CMS.CMS_PRODUCT_CONTRACT_PAYMENT PCP
    ON PCP.ID = PBP.CMS_PRODUCT_PAYMENT_ID
 INNER JOIN FSP_CMS.CMS_CONTRACT CC
    ON CC.CODE = PCP.CODE
  LEFT JOIN FSP_EAS.FAM_VENDORS FV
    ON FV.ID = FBH.VENDOR_ID
  LEFT JOIN FSP_FAM.FA_USER U
    ON PCP.OUR_SIGNER_CODE = U.LOGIN_NAME
  LEFT JOIN FSP_CMS.CMS_PROJECT BCP
    ON BCP.ID = PCP.CRM_PROJECT_ID

 WHERE FBH.BOE_TYPE_CODE = 'VENDER_PREPAYMENT_CMS_BOE'
   AND FBH.ENABLED_FLAG = 'Y'
   AND FBH.BOE_STATUS IN ('20', '30')
   AND CC.ENABLED_FLAG = 'Y'
   AND CC.VALIDITY_FLAG = 'Y';