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