10月份佣金维护的复制功能

该脚本为测试佣金维护的复制功能进行准备的：

SELECT
bmc.memberid ,
 case
   when bmc.Productid = 9 then
    decode(bmc.source, 3, 'IBL', 4, 'TOPfitIBL')
   ELSE
    decode(bmc.source, 1, 'NCD', 2, 'TOPfitNCD')
 END ||
 --bmc.source as 来源,
 decode(bmc.market, 1, '一级市场', 2, '二级市场', null) as 市场来源 ,
 BMC.FNCNAME functionName,

   to_char(bmc.effectdate,'yyyy/mm/dd')||' 至 ' ||to_char(bmc.enddate,'yyyy/mm/dd') as 起始结束日期,
   decode(bmc.signeddir,0,'all',1,'Buyer',2,'SELLER') as  签约方向,
   DECODE(BMC.CURRENCYID,1,'人民币',2,'美元',3,'日元',4,'港币',5,'欧元',6,'英镑',7,'新西兰元') as 币种,
 bmc.basicBP as 佣金基数,
 bmc.dealprdstart || '-' || bmc.dealprdend as 交易期限,
/* bmc.encashtlstartyear||'-'||bmc.encashtlendyear as 债券代偿期年,
 bmc.encashtlstartday || '-'|| bmc.encashtlendday as 债券代偿期天, */
 bmc.encashtlstartyear||'-'||bmc.encashtlendyear||' 年 '|| bmc.encashtlstartday ||'-'|| bmc.encashtlendday ||'天' as "债券代偿期年/天"   , 
 bmc.bondtype as 债券类型,
  (select b.formuladesc
    from BASE_COMMISSION_FORMULA b
   where b.fncname = BMC.FNCNAME) as 佣金公式描述,
 bmc.state

  FROM BASE_MEMBER_COMMISSION BMC
 WHERE BMC.MEMBERID =
       (select b.id
          from base_member b
         where b.cnfullname = &< NAME = "会员机构Name:" hint = "选择会员机构的全称 !"
         list =
               "select  b1.cnfullname from base_member b1 where b1.id in (1,3,4,5,23,113,120,557)"
         type = "String" >)
   AND BMC.PRODUCTID = (SELECT s.PRODUCTID
                          FROM SYS_PRODUCT S
                         WHERE S.PRODUCTCODE = &< NAME = "产品CODE"
                         LIST =
                               "SELECT productcode from sys_product  WHERE productid IN (13,9,1) order by productid "
                         TYPE = "String" >)
                    --    and BMC.Source= &<name="来源：" type="string" list="1,2,3,4"  > 
                    --    and bmc.market=&<name="市场：" type="String" list ="NULL,1,2" default=null>
                        
                         and bmc.state=0 ;
                         
                         
                         
                 --  select distinct(source) FROM BASE_MEMBER_COMMISSION BMC
                         
                         
/*        select * from (
select rownum  rn , b.code,b.encashdate, getyear_and_day(to_char(b.encashdate,'yyyy-mm-dd')) as yd,
getbondlivedays(getyear_and_day(to_char(b.encashdate,'yyyy-mm-dd'))) d
 from base_bond  b where rownum < 100 
 )  where d<>0 and rn>=1  order by d
        */