理财客户回收利息和本金明细及转让费

select t2.CN_NAME as 客户姓名,
       t2.CARD_ID as 身份证号,
       t2.CUSTOMER_CODE || '-' || t1.LENDING_NO as 出借编号,
       t4.DATA_NAME as 资金出借及回收方式,
       t3.retuen_back_amt as 出借人实际回收利息,
       t3.account_manage_fee as 账户管理费，
       t5.LEVEL_NAME as 账户级别,
       t9.data_name || t6.SUBBRANCH as 开户行,
       t6.ACC_NAME as 账户名,
       t6.ACC_NO as 账号,
       t8.name as 营业部
  from fortune.FT_T_RETURN t3
  left join fortune.FT_T_INVEST_INFO t1
    on t1.ID = t3.INVEST_ID
  left join fortune.CRM_V_CUSTOMER_INFO t2
    on t1.CUSTOMER_ID = t2.ID
  left join fortune.SYS_T_DICTIONARY t4
    on t4.DATA_CODE = t1.PRODUCT_TYPE
  left join fortune.CO_T_ACCOUNT_MANAGE_FEE t5
    on t1.INVEST_LEVEL = t5.ID
  left join fortune.CRM_T_BANK_INFO t6
    on t6.ID = t1.RECOVER_BANK_ACC_ID
  left join fortune.uc_t_staff t7
    on t2.customer_manager = t7.id
  left join fortune.uc_t_department t8
    on t7.department_id = t8.id
  left join fortune.sys_t_dictionary t9
    on t6.bank_name = t9.data_code
 where to_char(t3.RETURN_DATE, 'yyyy-mm-dd') = '2013-10-01'
   and t1.PRODUCT_TYPE = '00140004'
   and t3.STATUS = 1;

 

 

 ---理财到期转让服务费 
select case when t1.LOAN_RETURN_DATE='00120002' then '16号端' else '1号端' end ,t2.CN_NAME,t2.CARD_ID,t2.CUSTOMER_CODE||'-'||t1.LENDING_NO
,t4.DATA_NAME,t1.INVEST_AMT,to_char(t1.INVEST_START_DATE,'yyyy-mm-dd'),to_char(t3.APPOINT_PAYMENT_DATE,'yyyy-mm-dd'),t3.RECEIVABLE_AMT
,t3.RECEIVABLE_AMT-nvl(t3.DEADLINE_EXPECT_SELL_AMT,0)-nvl(t3.EXPECT_SELL_AMT,0)-nvl(t3.MANAG_AMT,0),nvl(t3.EXPECT_SELL_AMT,0)+nvl(t3.DEADLINE_EXPECT_SELL_AMT,0),nvl(t3.MANAG_AMT,0)
,t6.NAME
from fortune.FT_T_SELL_ORDER_MAIN t3 left join fortune.FT_T_INVEST_INFO t1 on t3.INVEST_ID=t1.ID 
left join fortune.CRM_V_CUSTOMER_INFO  t2 on t1.CUSTOMER_ID=t2.ID  
left join fortune.SYS_T_DICTIONARY t4 on t1.PRODUCT_TYPE=t4.DATA_CODE
left join fortune.UC_T_STAFF t5 on t2.CUSTOMER_MANAGER=t5.id
left join fortune.UC_T_DEPARTMENT t6 on t6.ID=t5.DEPARTMENT_ID
where t2.CUSTOMER_CODE||'-'||t1.LENDING_NO in ('F00211203300001-002');