佣金维护测试sql 

 1 SELECT
 2 bmc.memberid ,
 3  case
 4    when bmc.Productid = 9 then
 5     decode(bmc.source, 3, 'IBL', 4, 'TOPfitIBL')
 6    ELSE
 7     decode(bmc.source, 1, 'NCD', 2, 'TOPfitNCD')
 8  END ||
 9  --bmc.source as 来源,
10  decode(bmc.market, 1, '一级市场', 2, '二级市场', null) as 市场来源 ,
11  BMC.FNCNAME functionName,
12 
13    to_char(bmc.effectdate,'yyyy/mm/dd')||' 至 ' ||to_char(bmc.enddate,'yyyy/mm/dd') as 起始结束日期,
14    decode(bmc.signeddir,0,'all',1,'Buyer',2,'SELLER') as  签约方向,
15    DECODE(BMC.CURRENCYID,1,'人民币',2,'美元',3,'日元',4,'港币',5,'欧元',6,'英镑',7,'新西兰元') as 币种,
16  bmc.basicBP as 佣金基数,
17  bmc.dealprdstart || '-' || bmc.dealprdend as 交易期限,
18 /* bmc.encashtlstartyear||'-'||bmc.encashtlendyear as 债券代偿期年,
19  bmc.encashtlstartday || '-'|| bmc.encashtlendday as 债券代偿期天, */
20  bmc.encashtlstartyear||'-'||bmc.encashtlendyear||' 年 '|| bmc.encashtlstartday ||'-'|| bmc.encashtlendday ||'天' as "债券代偿期年/天"   , 
21  bmc.bondtype as 债券类型,
22   (select b.formuladesc
23     from BASE_COMMISSION_FORMULA b
24    where b.fncname = BMC.FNCNAME) as 佣金公式描述,
25  bmc.state
26 
27   FROM BASE_MEMBER_COMMISSION BMC
28  WHERE BMC.MEMBERID =
29        (select b.id
30           from base_member b
31          where b.cnfullname = &< NAME = "会员机构Name:" hint = "选择会员机构的全称 !"
32          list =
33                "select  b1.cnfullname from base_member b1 where b1.id in (1,3,4,5,23,113,120,557)"
34          type = "String" >)
35    AND BMC.PRODUCTID = (SELECT s.PRODUCTID
36                           FROM SYS_PRODUCT S
37                          WHERE S.PRODUCTCODE = &< NAME = "产品CODE"
38                          LIST =
39                                "SELECT productcode from sys_product  WHERE productid IN (13,9,1) order by productid "
40                          TYPE = "String" >)
41                     --    and BMC.Source= &<name="来源：" type="string" list="1,2,3,4"  > 
42                     --    and bmc.market=&<name="市场：" type="String" list ="NULL,1,2" default=null>
43                         
44                          and bmc.state=0 ;
45                          
46                          
47                          
48                  --  select distinct(source) FROM BASE_MEMBER_COMMISSION BMC
49                          
50                          
51 /*        select * from (
52 select rownum  rn , b.code,b.encashdate, getyear_and_day(to_char(b.encashdate,'yyyy-mm-dd')) as yd,
53 getbondlivedays(getyear_and_day(to_char(b.encashdate,'yyyy-mm-dd'))) d
54  from base_bond  b where rownum < 100 
55  )  where d<>0 and rn>=1  order by d
56         */

 

posted @ 2017-10-24 11:16  linbo.yang  阅读(257)  评论(0)    收藏  举报