归纳整理业务

----居间人分账
select *  from AC_T_LEDGER@Dl.core where  id=1;
   
----居间人分户
select *  from Ac_t_Ledger_Finance@Dl.core where ledger_id=1;

-----投资人 分账
select *  from AC_T_LEDGER@Dl.core where account in (
     select acc_id from  FT_T_INVEST_INFO where id in(541733661)
  );
                               
----投资人 AC_T_LEDGER_FINANCE
select * from Ac_t_Ledger_Finance@Dl.core fi where fi.ledger_id in(
 select f.id from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
));

----借款人分账
select *  from AC_T_LEDGER@Dl.core where  id in(
 select t.ledger_id from ac_t_ledger_loan@dl.core t where t.id in (570560345)
);

----借款人分户
select * from ac_t_ledger_loan@dl.core t where t.id in (570560345);

----投资收款后生成的final
select fi.DEBT_AMOUNT/(fi.DEBT_PROPORTION+fi.FROZEN_PORPORTION)*fi_order_dtl.scale as 收款后债权本金,
  fi.INTEREST_RECEIVABLE/(fi.DEBT_PROPORTION+fi.FROZEN_PORPORTION)*fi_order_dtl.scale as 收款后应收利息,
  (92007.3088134-loan.outstanding)*fi_order_dtl.scale as 约交割日实际交易利息,
  (92134.0090095-92007.3088134)*fi_order_dtl.scale as 逾期付款利息,
  fi.INTEREST_DEVIATION/(fi.DEBT_PROPORTION+fi.FROZEN_PORPORTION)*fi_order_dtl.scale as 误差利息,
  fi.loan_id,fi.*                                                
  from Ac_t_Ledger_Finance@Dl.core fi
  left join FT_T_ORDER_DTL fi_order_dtl on fi_order_dtl.loan_id=fi.loan_id
  left join FT_T_ORDER_MAIN fi_order_main on fi_order_main.id=fi_order_dtl.order_id
  left join FT_T_INVEST_INFO invest on invest.id=fi_order_main.invest_id
  left join Ac_t_Ledger_Loan@Dl.core loan on loan.id=fi_order_dtl.loan_id
  where invest.id=541733661 and fi.ledger_id=2 and fi.loan_id='560686831'
union  
select fi.DEBT_AMOUNT/(fi.DEBT_PROPORTION+fi.FROZEN_PORPORTION)*fi_order_dtl.scale as 收款后债权本金,
  fi.INTEREST_RECEIVABLE/(fi.DEBT_PROPORTION+fi.FROZEN_PORPORTION)*fi_order_dtl.scale as 收款后应收利息,
  (92007.3088134-loan.outstanding)*fi_order_dtl.scale as 约交割日实际交易利息,
  (92134.0090095-92007.3088134)*fi_order_dtl.scale as 逾期付款利息,
  fi.INTEREST_DEVIATION/(fi.DEBT_PROPORTION+fi.FROZEN_PORPORTION)*fi_order_dtl.scale as 误差利息,
  fi.loan_id,fi.*                                                
  from Ac_t_Ledger_Finance@Dl.core fi
  left join FT_T_ORDER_DTL fi_order_dtl on fi_order_dtl.loan_id=fi.loan_id
  left join FT_T_ORDER_MAIN fi_order_main on fi_order_main.id=fi_order_dtl.order_id
  left join FT_T_INVEST_INFO invest on invest.id=fi_order_main.invest_id
  left join Ac_t_Ledger_Loan@Dl.core loan on loan.id=fi_order_dtl.loan_id
  where invest.id=541733661 and fi.ledger_id=2 and fi.loan_id='560686831'; 
 
 

----修改1 结息前算好预期结果(当天必须 计息)
select t.outstanding as 实际结果outstanding,(t.AMOUNT_SPARE-t.outstanding)*fi_order_dtl.scale as Fianl应收INTEREST_RECEIVABLE,
t.outstanding-(repay.AMT-(t.AMOUNT_SPARE-t.outstanding)) as 结息后显示的outstanding,
(repay.AMT-((t.AMOUNT_SPARE-t.outstanding))) as 理财人应收本金总额,repay.AMT,
repay.OUTSTANDING,repay.PRINCIPAL_AMT,repay.INTEREST_AMT,t.id
 from ac_t_ledger_loan@dl.core t left join ft_t_order_dtl fi_order_dtl on fi_order_dtl.loan_id=t.id
                                left join FT_T_ORDER_MAIN fi_order_main on fi_order_main.id=fi_order_dtl.order_id
                                left join FT_T_INVEST_INFO invest on invest.id=fi_order_main.invest_id
                                left join ac_t_repayment_plan@dl.core repay on repay.loan_id=fi_order_dtl.loan_id                              
                                where invest.id=541733661 and t.id in (570560345) and to_char(REPAY_DAY,'yyyy-MM-dd')='2013-10-01' and rownum=1; 
                                

 

----修改回款计划预期值
select sum(repay.AMT*fi_order_dtl.scale)
from ac_t_ledger_loan@dl.core t left join ft_t_order_dtl fi_order_dtl on fi_order_dtl.loan_id=t.id
                                left join FT_T_ORDER_MAIN fi_order_main on fi_order_main.id=fi_order_dtl.order_id
                                left join FT_T_INVEST_INFO invest on invest.id=fi_order_main.invest_id
                                left join ac_t_repayment_plan@dl.core repay on repay.loan_id=fi_order_dtl.loan_id                              
                                where invest.id=541733661 and t.id in(570560345)and to_char(REPAY_DAY,'yyyy-MM-dd')='2013-10-01'; 
                                                                                             
----huik jihua
select * from ac_t_repayment_plan@dl.core t where t.loan_id in(570560345) and to_char(REPAY_DAY,'yyyy-MM-dd')='2013-10-01';

---预期资价值
select sum(EXPECTED_ASSETS) from (                            
(select fi_order_dtl.loan_id,'560686831',92419.7227397*(fi_order_dtl.scale) as EXPECTED_ASSETS from ft_t_return ret left join ft_t_invest_info invest on invest.id=ret.invest_id
                              left join FT_T_ORDER_MAIN fi_order_main on invest.id=fi_order_main.invest_id
                              left join FT_T_ORDER_DTL fi_order_dtl on fi_order_dtl.order_id=fi_order_main.id
                              where invest.id in(541733661) and fi_order_dtl.loan_id='560686831')
union
(select fi_order_dtl.loan_id,'560686816',71311.4584587*(fi_order_dtl.scale) as EXPECTED_ASSETS from ft_t_return ret left join ft_t_invest_info invest on invest.id=ret.invest_id
                              left join FT_T_ORDER_MAIN fi_order_main on invest.id=fi_order_main.invest_id
                              left join FT_T_ORDER_DTL fi_order_dtl on fi_order_dtl.order_id=fi_order_main.id
                              where invest.id in(541733661) and fi_order_dtl.loan_id='560686816'));

-----回款计划
select t.*,t.return_amt-t.pay_amt-t.retuen_back_amt-t.account_manage_fee,t.account_manage_fee,t.recommend_amt,t.return_amt-t.pay_amt-t.account_manage_fee as 预期回收金额,t.account_manage_fee from ft_t_return t where t.invest_id in(
   select t.id from  FT_T_INVEST_INFO t where id in(541733661)
); 

 

----投资信息
select * from  FT_T_INVEST_INFO where id in(541733661);

--银行信息 (划扣)
select * from CRM_T_BANK_INFO where id in (
 select PAY_BANK_ACC_ID from  FT_T_INVEST_INFO where id in(541733661)
);

---银行信息 (回款)
select * from CRM_T_BANK_INFO where id in (
 select RECOVER_BANK_ACC_ID  from  FT_T_INVEST_INFO where id in(541733661)
);

---新建投资 产生核心用户
select * from AC_T_CUSTOMER@Dl.core where id in (
  select total_account_id  from AC_T_LEDGER@Dl.core where account in (
     select acc_id from  FT_T_INVEST_INFO where id in(541733661)
  )
);

-----新建投资 产生的分账
select *  from AC_T_LEDGER@Dl.core where account in (
     select acc_id from  FT_T_INVEST_INFO where id in(541733661)
  );
 
----AC_T_LEDGER_FINANCE（买方） 
select * from Ac_t_Ledger_Finance@Dl.core fi where fi.ledger_id in(
select f.id from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
));           
 
---AC_T_LEDGER_FINANCE（卖方）      
select * from Ac_t_Ledger_Finance@Dl.core fi where  fi.loan_id in (
  select loan_id from FT_T_ORDER_DTL where order_id in (
  select id from FT_T_ORDER_MAIN where invest_id in(select id from  FT_T_INVEST_INFO where id in(541733661))))
  and fi.ledger_id=2;

-----根据crm 投资id 查询fortune投资信息 （一个表嵌套一个表查询）
select (select data_name from sys_t_dictionary b where a.regular_type=b.data_code) as 投资方式,
      (select data_name from sys_t_dictionary b where a.product_type=b.data_code) as 理财产品,
      (select data_name from sys_t_dictionary b where a.LOAN_RETURN_DATE=b.data_code) as 债权还款日,
      (select data_name from sys_t_dictionary b where a.USE_RISK_RESERVE=b.data_code) as 是否风险补偿,
      (select data_name from sys_t_dictionary b where a.AGREEMENT_VER=b.data_code) as 协议版本, 
      (select data_name from sys_t_dictionary b where a.PAYMENT_TYPE=b.data_code) as 付款方式,
      (select NODE_NAME from FT_T_FLOW_DEF c where a.flow_id=c.node_code) as 流程ID,
      (select data_name from sys_t_dictionary b where a.handle_type=b.data_code) as 到期处理方式,
      a.*
 from FT_T_INVEST_INFO a where id in(541733661);
 

----shoukuan 订单主表
select * from ft_t_order_main t where t.invest_id in(
   select id from  FT_T_INVEST_INFO where id in(541733661)
 ) and t.flow_id not in('03000010','03000006');

----订单明细
select * from ft_t_order_dtl t where t.order_id in(
select t.id from ft_t_order_main t where t.invest_id in(
   select id from  FT_T_INVEST_INFO where id in(541733661)
 ) and t.flow_id not in('03000010','03000006')
);

----交易单
select t.* from FT_T_TRANSACTION t where t.sell_order_dtl_id in(
  select sell_order_dtl.id from FT_T_SELL_ORDER_DTL sell_order_dtl left join
              FT_T_ORDER_DTL order_dtl on order_dtl.sell_order_dtl_id=sell_order_dtl.id
              left join FT_T_ORDER_MAIN order_main on order_dtl.order_id=order_main.id
              left join FT_T_INVEST_INFO info on order_main.invest_id=info.id
              where info.id in(541733661)
);

----卖单主表
select * from FT_T_SELL_ORDER_MAIN t where t.id in(
   select sell_order_dtl.sell_order_id from FT_T_SELL_ORDER_DTL sell_order_dtl left join
              FT_T_ORDER_DTL order_dtl on order_dtl.sell_order_dtl_id=sell_order_dtl.id
              left join FT_T_ORDER_MAIN order_main on order_dtl.order_id=order_main.id
              left join FT_T_INVEST_INFO info on order_main.invest_id=info.id
              where info.id in(541733661)
);

--卖单明细
select * from FT_T_SELL_ORDER_DTL sell_order_dtl left join
              FT_T_ORDER_DTL order_dtl on order_dtl.sell_order_dtl_id=sell_order_dtl.id
              left join FT_T_ORDER_MAIN order_main on order_dtl.order_id=order_main.id
              left join FT_T_INVEST_INFO info on order_main.invest_id=info.id
              where info.id in(541733661);

----应收应付
select * from AC_T_DEBT@Dl.core f where f.account in(
 select acc_id from  FT_T_INVEST_INFO where id in(541733661)
);
 

---AC_T_LEDGER（买方）
select f.* from AC_T_LEDGER@Dl.core f where f.account in (
  select acc_id from  FT_T_INVEST_INFO where id in(541733661)
);                                 

----AC_T_LEDGER_DETAIL（买方投资金额)       
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 1011 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);

--AC_T_LEDGER_DETAIL（买方应付逾期利息)      2006 
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 2006 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);
       
--AC_T_LEDGER_DETAIL（买方应收利息)  1005
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 1005 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);   
       
--AC_T_LEDGER_DETAIL（买方利息支出)  3036   
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3036 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
); 
          
--AC_T_LEDGER_DETAIL（买方误差处理)  3034       
  select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3034 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);         

----AC_T_LEDGER_FINANCE（买方） 
select * from Ac_t_Ledger_Finance@Dl.core fi where fi.ledger_id in(
select f.id from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
));
                                                           
----AC_T_LEDGER_FINANCE（卖方）      
select * from Ac_t_Ledger_Finance@Dl.core fi where  fi.loan_id in (
  select loan_id from FT_T_ORDER_DTL where order_id in (
  select id from FT_T_ORDER_MAIN where invest_id in(select id from  FT_T_INVEST_INFO where id in(541733661))))
  and fi.ledger_id=2;
----------------------AC_T_LEDGER（卖方）
select AC_T_LEDGER.* from AC_T_LEDGER@Dl.core where id='1'; 

--- AC_T_LEDGER_DETAIL（卖方投资金额)   
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 1011 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.id=1
);
       
--AC_T_LEDGER_DETAIL（卖方应收逾期利息)  1007
 select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core where ac_t_ledger_detail.type = 1007;

--AC_T_LEDGER_DETAIL（卖方应收利息)  1005
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 1005 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.id=1
);
       
--AC_T_LEDGER_DETAIL（卖方利息收入)  3001
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3001 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.id=1
);
   
       
--AC_T_LEDGER_DETAIL（卖方误差处理)  3034
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3034 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.id=1
);
   
       
--AC_T_LEDGER_DETAIL（卖方管理费支出)    3023
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3023 order by ac_t_ledger_detail.id desc;
   
--AC_T_LEDGER_DETAIL（卖方转让服务费支出)  3025
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3025 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.id=1
);

--AC_T_LEDGER_DETAIL（卖方紧急转让服务费支出)  3030   
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3030;
       
--AC_T_LEDGER_DETAIL（公司现金账户预收款项)  2008
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 2008;        
       
--AC_T_LEDGER_DETAIL（管理费收入-公司账户)  3005     
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3005;  
       
-----AC_T_LEDGER_DETAIL（转让服务费收入-公司账户)  3007
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3007;        
       
-----AC_T_LEDGER_DETAIL（紧急转让服务费收入-公司账户) 3008
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3008;
 

 

 
---流水
select * from ac_t_flow@dl.core where account in(
select acc_id from  FT_T_INVEST_INFO where id in(541733661)
 ) order by id desc,entry_no;

 

----根据crm 投资id 查询fortune投资信息 （一个表嵌套一个表查询）
select (select data_name from sys_t_dictionary b where a.regular_type=b.data_code) as 投资方式,
      (select data_name from sys_t_dictionary b where a.product_type=b.data_code) as 理财产品,
      (select data_name from sys_t_dictionary b where a.LOAN_RETURN_DATE=b.data_code) as 还款日,
      (select data_name from sys_t_dictionary b where a.USE_RISK_RESERVE=b.data_code) as 风险补偿,
      (select data_name from sys_t_dictionary b where a.AGREEMENT_VER=b.data_code) as 协议版本, 
      (select data_name from sys_t_dictionary b where a.PAYMENT_TYPE=b.data_code) as 付款方式,
      (select NODE_NAME from FT_T_FLOW_DEF c where a.flow_id=c.node_code) as 流程ID,
      (select data_name from sys_t_dictionary b where a.handle_type=b.data_code) as 到期处理,
      a.* from FT_T_INVEST_INFO a where a.id in(541733661);

-----回款金额
select sum(fi.DEBT_PROPORTION*(select pay.amt from ac_t_repayment_plan@dl.core pay where pay.loan_id=fi.loan_id
and to_char(pay.REPAY_DAY,'yyyy-MM-dd')='2013-09-16'))
 from Ac_t_Ledger_Finance@dl.core fi where fi.ledger_id in(
select t.id from AC_T_LEDGER@dl.core t where t.account in(
  select f.acc_id from ft_t_invest_info f where id in(541733661)
));

----回款计划
select * from ft_t_return t where t.invest_id in(541733661);

 

 

-----jiexi回款计划
select t.*,t.return_amt-t.pay_amt-t.retuen_back_amt-t.account_manage_fee,t.account_manage_fee,t.recommend_amt from ft_t_return t where t.invest_id in(
   select t.id from  FT_T_INVEST_INFO t where id in(541733661)
);

----debt
select * from ac_t_debt@dl.core t where t.account in(select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661));
 

----订单主表
select * from ft_t_order_main t where t.invest_id in(
   select id from  FT_T_INVEST_INFO where id in(541733661)
 ) and t.flow_id not in('03000010','03000006');

----订单明细
select * from ft_t_order_dtl t where t.order_id in(
select t.id from ft_t_order_main t where t.invest_id in(
   select id from  FT_T_INVEST_INFO where id in(541733661)
 ) and t.flow_id not in('03000010','03000006')
);

----交易单
select t.* from FT_T_TRANSACTION t where t.sell_order_dtl_id in(
  select sell_order_dtl.id from FT_T_SELL_ORDER_DTL sell_order_dtl left join
              FT_T_ORDER_DTL order_dtl on order_dtl.sell_order_dtl_id=sell_order_dtl.id
              left join FT_T_ORDER_MAIN order_main on order_dtl.order_id=order_main.id
              left join FT_T_INVEST_INFO info on order_main.invest_id=info.id
              where info.id in(541733661)
);

 

-------ac_t_ledger_loan（借款人） 
select * from ac_t_ledger_loan@Dl.core t where t.id in(
select t.loan_id from ft_t_order_dtl t where t.order_id in(
select t.id from ft_t_order_main t where t.invest_id in(
  select t.id from  FT_T_INVEST_INFO t where id in(541733661))
)
);

------还款
select * from ac_t_repayment_plan@dl.core pay where pay.loan_id in(
 select t.id from ac_t_ledger_loan@Dl.core t where t.id in(
select t.loan_id from ft_t_order_dtl t where t.order_id in(
select t.id from ft_t_order_main t where t.invest_id in(
  select t.id from  FT_T_INVEST_INFO t where id in(541733661))
)
)
) and to_char(pay.REPAY_DAY,'yyyy-MM-dd')='2013-09-16';

-----ac_t_ledger（借款人）
select * from  ac_t_ledger@Dl.core  where id in(
select t.ledger_id from ac_t_ledger_loan@Dl.core t where t.id in(
select t.loan_id from ft_t_order_dtl t where t.order_id in(
select t.id from ft_t_order_main t where t.invest_id in(
  select t.id from  FT_T_INVEST_INFO t where id in(541733661))
)
)
);

-----（借款金额--借款人) 
select * from ac_t_ledger_detail@Dl.core where ac_t_ledger_detail.type = 2007 and
LEDGER_ID in(
select id from  ac_t_ledger@Dl.core  where id in(
select t.ledger_id from ac_t_ledger_loan@Dl.core t where t.id in(
select t.loan_id from ft_t_order_dtl t where t.order_id in(
select t.id from ft_t_order_main t where t.invest_id in(
  select t.id from  FT_T_INVEST_INFO t where id in(541733661))
)
)
)
);
-----（利息支出-借款人)
select * from ac_t_ledger_detail@Dl.core where ac_t_ledger_detail.type = 3036 and
LEDGER_ID in(
select id from  ac_t_ledger@Dl.core  where id in(
select t.ledger_id from ac_t_ledger_loan@Dl.core t where t.id in(
select t.loan_id from ft_t_order_dtl t where t.order_id in(
select t.id from ft_t_order_main t where t.invest_id in(
  select t.id from  FT_T_INVEST_INFO t where  id in(541733661))
)
)
)
);
   
-----应付利息-借款人) 
select * from ac_t_ledger_detail@Dl.core where ac_t_ledger_detail.type = 2002 and
LEDGER_ID in(
select id from  ac_t_ledger@Dl.core  where id in(
select t.ledger_id from ac_t_ledger_loan@Dl.core t where t.id in(
select t.loan_id from ft_t_order_dtl t where t.order_id in(
select t.id from ft_t_order_main t where t.invest_id in(
  select t.id from  FT_T_INVEST_INFO t where  id in(541733661))
)
)
)
); 
----------理财人
select f.* from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where  id in(541733661)
);

-----------fianal
select * from ac_t_ledger_finance@Dl.core t where t.ledger_id in(
 select f.id from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where  id in(541733661)
) );

----AC_T_LEDGER_DETAIL（投资金额-债权人账户)   
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 1011 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where  id in(541733661)
)
);

----AC_T_LEDGER_DETAIL（利息收入-债权人账户)     
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3001 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where  id in(541733661)
)
);

-----AC_T_LEDGER_DETAIL（应收利息-债权人账户)       
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 1005 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);      
 
-----AC_T_LEDGER_DETAIL（误差处理-债权人)       
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3034 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where  id in(541733661)
)
);    
     
----AC_T_LEDGER_DETAIL（公司现金账户预收款项)     
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 2008 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where  id in(541733661)
)
);

 
------[收取管理费]   买方
 select f.* from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where  id in(541733661)
);

---AC_T_LEDGER_DETAIL（管理费支出)    
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3023 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where  id in(541733661)
)
);         

---AC_T_LEDGER_DETAIL（公司现金账号预收款项) 
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 2008 and  ac_t_ledger.id=5;

---AC_T_LEDGER_DETAIL（公司账号管理费收入)
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3005 and ac_t_ledger.id=6;

----自动账单收付款 交易单
select * from FT_T_TRANSACTION where loan_id in (
  select loan_id from FT_T_ORDER_DTL where order_id in (
  select id from FT_T_ORDER_MAIN where invest_id in(select id from  FT_T_INVEST_INFO where id in(541733661)))
); 

 

----计算账单
select * from FT_T_STATEMENT_MAIN t where t.invest_id in(
  select id from  FT_T_INVEST_INFO where id in(541733661)
);

----账单明细
select * from FT_T_STATEMENT_DTL f where f.statment_id in (
   select t.id from FT_T_STATEMENT_MAIN t where t.invest_id in(
  select id from  FT_T_INVEST_INFO where id in(541733661)
)
);

----订单主表
select * from ft_t_order_main t where t.invest_id in(
   select id from  FT_T_INVEST_INFO where id in(541733661)
 ) and t.flow_id not in('03000010','03000006') ;
 
 
--------订单详细
select t.loan_id,t.* from ft_t_order_dtl t where t.order_id in(
select t.id from ft_t_order_main t where t.invest_id in(
  select t.id from  FT_T_INVEST_INFO t where id in(541733661))  and t.flow_id not in('03000010','03000006')
);

----卖单主表
select * from FT_T_SELL_ORDER_MAIN t where t.id in(
   select sell_order_dtl.sell_order_id from FT_T_SELL_ORDER_DTL sell_order_dtl left join
              FT_T_ORDER_DTL order_dtl on order_dtl.sell_order_dtl_id=sell_order_dtl.id
              left join FT_T_ORDER_MAIN order_main on order_dtl.order_id=order_main.id
              left join FT_T_INVEST_INFO info on order_main.invest_id=info.id
              where info.id in(541733661)
);

--卖单明细
select * from FT_T_SELL_ORDER_DTL sell_order_dtl left join
              FT_T_ORDER_DTL order_dtl on order_dtl.sell_order_dtl_id=sell_order_dtl.id
              left join FT_T_ORDER_MAIN order_main on order_dtl.order_id=order_main.id
              left join FT_T_INVEST_INFO info on order_main.invest_id=info.id
              where info.id in(541733661);

----理财人
select f.* from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
);

----AC_T_LEDGER_DETAIL（买方投资金额)       
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 1011 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);
       
----AC_T_LEDGER_DETAIL（买方应付逾期利息)       
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 2006 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);
       
----AC_T_LEDGER_DETAIL（买方应收利息)  1005
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 1005 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);   
       
----AC_T_LEDGER_DETAIL（买方利息支出)  3036   
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3036 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
); 

----AC_T_LEDGER_DETAIL（买方误差处理)  3034       
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3034 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);

-----AC_T_LEDGER（卖方） 
select f.* from AC_T_LEDGER@Dl.core f where f.id=2;
       
----AC_T_LEDGER_DETAIL（卖方应收逾期利息)  1007
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 1007 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);

----AC_T_LEDGER_DETAIL（卖方应收利息) 
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 1005 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);
       
----AC_T_LEDGER_DETAIL（卖方利息收入)   
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3001 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);
   
       
----AC_T_LEDGER_DETAIL（卖方误差处理) 
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3034 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);
   
       
----AC_T_LEDGER_DETAIL（卖方管理费支出)   
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3023 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);
   
       
----AC_T_LEDGER_DETAIL（卖方转让服务费支出) 
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3025 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);

----AC_T_LEDGER_DETAIL（卖方紧急转让服务费支出)     
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3030 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);  
       
----AC_T_LEDGER_DETAIL（公司现金账户预收款项) 
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 2008 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);        
       
----AC_T_LEDGER_DETAIL（管理费收入-公司账户)       
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3005 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);  
       
--AC_T_LEDGER_DETAIL（转让服务费收入-公司账户) 
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3007 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);        
       
-----AC_T_LEDGER_DETAIL（紧急转让服务费收入-公司账户)
select ac_t_ledger_detail.* from ac_t_ledger_detail@Dl.core left join ac_t_ledger@Dl.core  on ac_t_ledger_detail.ledger_id= ac_t_ledger.id
where ac_t_ledger_detail.type = 3008 and  ac_t_ledger.account in(
  select f.account from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
)
);    

------------AC_T_LEDGER_FINANCE（买方） 
select * from ac_t_ledger_finance@Dl.core t where t.ledger_id in(
 select f.id from AC_T_LEDGER@Dl.core f where f.account in (
  select t.acc_id from  FT_T_INVEST_INFO t where id in(541733661)
) );
     
---------AC_T_LEDGER_FINANCE（卖方）     
select * from ac_t_ledger_finance@Dl.core t where t.ledger_id in(2) and t.loan_id=485643058;