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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

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';