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