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 */
