SELECT
T1.COMPANY_SUBJECT AS 合同公司,
T1.CBGS AS 成本公司,
T1.FFZT AS 发放状态,
T1.CBZX AS 财务核算部门,
T1.CBZXCODE AS 财务核算部门编码,
T1.XINZIMONTH::DATE AS 薪资所属月份,
CASE  WHEN TT.FLAG  IS NULL  THEN '正常' ELSE TT.FLAG END  AS 发放形式,
COUNT(DISTINCT T1.EMPLOYEE_NUMBER) AS 人数,
SUM(T2.SINGLE_INVESTMENT + T2.YCXDSTR) AS 单身投入,	
SUM(CASE WHEN T1.ZGSYF >0 THEN T1.ZGSYF-(T2.SINGLE_INVESTMENT + T2.YCXDSTR)-(T2.JIDU+ T1.PSTC +T2.MONTHLY_COMMISSION + T2.PRIZE_MONEY +  T1.FENHONG + T1.JDJX + T2.PIECE_RATE) 
ELSE T2.SHOULD_SEND-(T2.SINGLE_INVESTMENT + T2.YCXDSTR)-(T2.JIDU+ T1.PSTC +T2.MONTHLY_COMMISSION + T2.PRIZE_MONEY +  T1.FENHONG + T1.JDJX + T2.PIECE_RATE) END) AS 应发工资,
SUM(T2.JIDU+ T1.PSTC +T2.MONTHLY_COMMISSION + T2.PRIZE_MONEY +  T1.FENHONG + T1.JDJX + T2.PIECE_RATE) AS 应发奖金,
SUM(T2.GERENSHEBAO) AS 个人社保合计,
SUM(t2.Gerengjj  ) AS 个人公积金合计,
SUM(T2.TAXES_PAYABLE )AS 应缴个税,
0 AS 一次性单身投入返还,
SUM(T2.GJJEW) AS 公积金额外自费, 
SUM(T2.MUTUAL_AID) AS 互助金,
SUM(T2.AFTER_TAX_ADJUSTMENT) AS 税后调整,
SUM(CASE WHEN T1.SHIFA_PAY-T2.ACTUALCOMPENSATION > 0 THEN T1.SHIFA_PAY-T2.ACTUALCOMPENSATION ELSE 0 END) AS 实发工资,
SUM(T1.SFDD) AS 实发代垫,
SUM(T1.DDHK) AS 实发代垫还款,
SUM(T1.SERVICE_CHARGE + T1.TAX_AMOUNT) AS BPO服务费包含税率,
SUM(CASE WHEN  COALESCE(T1.service_charge, 0) + T1.TAX_AMOUNT >0 THEN T2.AMOUNT_REQUESTED  ELSE 0 END ) AS BPO收款金额,
'1-工资' AS 备注,
  CASE  WHEN TT.FLAG IS NULL THEN T1.XINZIMONTH::DATE  + INTERVAL '1 MONTH' ELSE  TT.DATE END  AS 支付期间 
 FROM PAYROLL_LEDGER T1 
 INNER JOIN V_P_LEDGER T2 ON T1.ID = T2.ID 
 INNER JOIN BATCHES BT ON BT.CODE = T1.BATCH_CODE
 LEFT JOIN (SELECT IPOD.BATCH_PERIOD::VARCHAR AS MONTH,REF_BATCH_ID ,
 IPOD.EMP_NUMBER,CASE WHEN IPOD.PAY_TYPE =2 THEN '缓发' ELSE '正常' END AS FLAG ,
 MAX(IPO.PAY_DATE) AS DATE  
 FROM  ISSUE_PAY_ORDER_DETAIL IPOD 
 INNER JOIN issue_pay_order IPO ON  IPO.id = IPOD.ref_issue_pay_order_id
 WHERE IPOD.SALARY_ITEM_NAME ='支付金额(对私)' 
 AND IPOD.PAY_STATUS <> 4 AND IPOD.IS_DELETE=0  
 AND ES_GETNUMERIC(IPOD.TOTAL_AMOUNT_ENCODE) > 0
 GROUP BY 
 MONTH,IPOD.EMP_NUMBER,IPOD.PAY_TYPE,REF_BATCH_ID 
 UNION ALL 
 SELECT IPOD.BATCH_PERIOD::VARCHAR AS MONTH,IPOD.REF_BATCH_ID ,
 IPOD.EMP_NUMBER,CASE WHEN IPOD.OVERVIEW_TYPE = 2 OR IPOD.OVERVIEW_TYPE =3 THEN '缓发' ELSE '正常' END AS FLAG ,
 MAX(YY.PAY_DATE) AS DATE  
 FROM  CORPORATE_PAY_OVERVIEW_EMPLOYEE IPOD 
INNER JOIN  CORPORATE_PAY_ORDER_DETAIL  XX ON XX.ID = IPOD.REF_CORPORATE_PAY_ORDER_DETAIL_ID
INNER JOIN  CORPORATE_PAY_ORDER YY ON YY.ID = XX.REF_CORPORATE_PAY_ORDER_ID
 WHERE IPOD.SALARY_ITEM_NAME ='支付金额(对公)' 
 AND IPOD.IS_DELETE=0  
 GROUP BY MONTH,IPOD.EMP_NUMBER,IPOD.OVERVIEW_TYPE ,IPOD.REF_BATCH_ID ) TT  
 ON TT.MONTH = T1.XINZIMONTH AND TT.EMP_NUMBER = T1.EMPLOYEE_NUMBER AND TT.REF_BATCH_ID = BT.ID::VARCHAR
 GROUP BY T1.COMPANY_SUBJECT,T1.CBGS,T1.FFZT,T1.CBZX,T1.CBZXCODE,T1.XINZIMONTH,T1.SHIJI,CASE  WHEN TT.FLAG  IS NULL  THEN '正常' ELSE TT.FLAG END,CASE  WHEN TT.FLAG IS NULL THEN T1.XINZIMONTH::DATE  + INTERVAL '1 MONTH' ELSE  TT.DATE END
UNION 
SELECT
'南京宁远人力资源有限公司' AS 合同公司,
'江苏康众汽配有限公司' AS 成本公司,
'' AS 发放状态,
T1.CBZX AS 财务核算部门,
T1.CBZXCODE AS 财务核算部门编码,
T1.XINZIMONTH::DATE AS 薪资所属月份,
CASE  WHEN TT.FLAG  IS NULL  THEN '正常' ELSE TT.FLAG END  AS 发放形式,
0 AS 人数,
0 AS 单身投入,	
0 AS 应发工资,
0 AS 应发奖金,
0 AS 个人社保合计,
0 AS 公积金个人缴纳,
0 AS 应缴个税,
0 AS 一次性单身投入返还,
0 AS 公积金额外自费, 
0 AS 互助金,
0 AS 税后调整,
SUM(CASE WHEN T1.BPOCOST>0 THEN T1.BPOCOST ELSE 0 END) AS 实发工资,
0 AS 实发代垫,
0 AS 实发代垫还款,
SUM(T1.BPOCOST*0.1) AS BPO服务费包含税率,
SUM(T2.BPO) AS BPO收款金额,
'2-费用' AS 备注,
 CASE  WHEN TT.FLAG  IS NULL THEN T1.XINZIMONTH::DATE  + INTERVAL '1 MONTH' ELSE  TT.DATE END  AS 支付期间 
 FROM PAYROLL_LEDGER T1 
 INNER JOIN V_P_LEDGER T2 ON T1.ID = T2.ID 
 INNER JOIN BATCHES BT ON BT.CODE = T1.BATCH_CODE
 LEFT JOIN (SELECT IPOD.BATCH_PERIOD::VARCHAR AS MONTH,REF_BATCH_ID ,
 IPOD.EMP_NUMBER,CASE WHEN IPOD.PAY_TYPE =2 THEN '缓发' ELSE '正常' END AS FLAG ,
 MAX(IPO.PAY_DATE) AS DATE  
 FROM  ISSUE_PAY_ORDER_DETAIL IPOD 
 INNER JOIN issue_pay_order IPO ON  IPO.id = IPOD.ref_issue_pay_order_id
 WHERE IPOD.SALARY_ITEM_NAME ='支付金额(对私)' 
 AND IPOD.PAY_STATUS <> 4 AND IPOD.IS_DELETE=0  
 AND ES_GETNUMERIC(IPOD.TOTAL_AMOUNT_ENCODE) > 0
 GROUP BY 
 MONTH,IPOD.EMP_NUMBER,IPOD.PAY_TYPE,REF_BATCH_ID 
 UNION ALL 
 SELECT IPOD.BATCH_PERIOD::VARCHAR AS MONTH,IPOD.REF_BATCH_ID ,
 IPOD.EMP_NUMBER,CASE WHEN IPOD.OVERVIEW_TYPE = 2 OR IPOD.OVERVIEW_TYPE =3 THEN '缓发' ELSE '正常' END AS FLAG ,
 MAX(YY.PAY_DATE) AS DATE  
 FROM  CORPORATE_PAY_OVERVIEW_EMPLOYEE IPOD 
INNER JOIN  CORPORATE_PAY_ORDER_DETAIL  XX ON XX.ID = IPOD.REF_CORPORATE_PAY_ORDER_DETAIL_ID
INNER JOIN  CORPORATE_PAY_ORDER YY ON YY.ID = XX.REF_CORPORATE_PAY_ORDER_ID
 WHERE IPOD.SALARY_ITEM_NAME ='支付金额(对公)' 
 AND IPOD.IS_DELETE=0  
 GROUP BY MONTH,IPOD.EMP_NUMBER,IPOD.OVERVIEW_TYPE ,IPOD.REF_BATCH_ID ) TT 
 ON TT.MONTH = T1.XINZIMONTH AND TT.EMP_NUMBER = T1.EMPLOYEE_NUMBER AND TT.REF_BATCH_ID = BT.ID::VARCHAR
 WHERE T2.BPO > 0
 GROUP BY T1.CBZX,T1.CBZXCODE,T1.XINZIMONTH, CASE  WHEN TT.FLAG  IS NULL  THEN '正常' ELSE TT.FLAG END,CASE  WHEN TT.FLAG  IS NULL THEN T1.XINZIMONTH::DATE  + INTERVAL '1 MONTH' ELSE  TT.DATE END