新系统bug.docx
- 文档编号:13629983
- 上传时间:2023-06-15
- 格式:DOCX
- 页数:18
- 大小:17.51KB
新系统bug.docx
《新系统bug.docx》由会员分享,可在线阅读,更多相关《新系统bug.docx(18页珍藏版)》请在冰点文库上搜索。
新系统bug
1.、营业收费清单融合报表:
逻辑实现及控制:
//页面初始化加载老工号所在本地网和所属市县
SELECTlanid,lanname,ppdomid,ppdomname,departid4,departname4,departid5,departname5,departid6,departname6,base_code,base_name,org_level
From
(Selectnvl(d.org_id2,-1)lanid,nvl(d.org_name2,-1)lanname,nvl(d.org_id3,-1)ppdomid,nvl(d.org_name3,-1)ppdomname,
nvl(d.org_id4,-1)departid4,nvl(d.org_name4,'全部')departname4,
nvl(d.org_id5,-1)departid5,nvl(d.org_name5,'全部')departname5,
nvl(d.org_id6,-1)departid6,nvl(d.org_name6,'全部')departname6,a.base_code,a.base_name,c.org_level
Fromdim_org_relata
LeftJoinstaff_mixbOnto_char(b.staff_id)=a.base_code
LeftJoinorganization_mixcOnc.party_id=b.org_id
LeftJoinvw_organization_mixdOnd.org_id4=c.party_idOrd.org_id5=c.party_idOrd.org_id6=c.party_id
Wherea.local_code_type=12Anda.local_code='155127')WhereRownum=1;
如果上面SQL查询出来的baseCode为空就提示“请联系管理员,未映射该工号的信息”。
根据选择的不同的级别组织进行查询:
如只选择了本地网,而且复选,其他市县,营业部等都是全部:
Selecta.bc_org_name_5,a.bc_org_name_6,a.bc_oper_name,a.accept_id,a.accept_time,
a.source_code,a.pay_type_code,a.service_code,b.prod_type_name,a.feeitem_code,
c.feeitem_name,a.pay_feefromdw_busi_fee_report_daya,DIM_FIX_PROD_TYPE
b,DIM_FIX_FEEITEMcwherea.service_code=b.prod_type_codeand
a.feeitem_code=c.feeitem_codeanda.source_code=b.source_codeand
a.source_code=c.source_codeanda.bc_org_code_2in
(6,1,2,8,5,9,16,17,11,10,13,18,7,4,12,3,14,0,376284)anda.month_no=to_number(201008);
如果要是选择了本地网,再选择某个市县,上面SQL依次拼接,若为日报,月报什么的改变报表的类型,费用来源、收费方式即可。
ANDa.bc_org_code_3in(……..)
ANDa.bc_org_code_4in(……..)……
2、营业员收费融合报表:
SELECTa.bc_org_name_6departname,a.bc_oper_code,a.bc_oper_nameopername,
a.service_code,
(SELECTb.prod_type_nameFROMdim_fix_prod_typeb
WHEREa.service_code=b.prod_type_codeanda.source_code=b.source_codeANDROWNUM=1)service_name,
a.feeitem_code,(SELECTb.feeitem_nameFROMdim_fix_feeitemb
WHEREa.feeitem_code=b.feeitem_codeanda.source_code=b.source_codeANDROWNUM=1)feeitem_name,
SUM(CASEa.oper_typeWHEN'0'THENa.pay_numELSE0END)chargenum,
SUM(CASEa.oper_typeWHEN'1'THENa.pay_numELSE0END)refundnum,
SUM(CASEa.oper_typeWHEN'0'THENa.pay_feeELSE0END)chargefee,
SUM(CASEa.oper_typeWHEN'1'THENa.pay_feeELSE0END)refundfee,
SUM(a.pay_fee)totalfeeFromst_busi_fee_mona
WHEREa.month_no=to_char(to_date('2010-08-0100:
00:
00','yyyy-mm-ddhh24:
mi:
ss'),'yyyymm')
anda.bc_org_code_2in
(1)
GroupBya.bc_org_name_6,a.bc_oper_code,a.bc_oper_name,
a.service_code,a.feeitem_code,a.source_code
UNIONALL
SELECT''departname,''bc_oper_code,'总计'opername,''service_code,''service_name,''feeitem_code,''feeitem_name,
SUM(CASEa.oper_typeWHEN'0'THENa.pay_numELSE0END)chargenum,
SUM(CASEa.oper_typeWHEN'1'THENa.pay_numELSE0END)refundnum,
SUM(CASEa.oper_typeWHEN'0'THENa.pay_feeELSE0END)chargefee,
SUM(CASEa.oper_typeWHEN'1'THENa.pay_feeELSE0END)refundfee,
SUM(a.pay_fee)totalfeeFROMst_busi_fee_mona
WHEREa.month_no=to_char(to_date('2010-08-0100:
00:
00','yyyy-mm-ddhh24:
mi:
ss'),'yyyymm')
anda.bc_org_code_2in
(1);
上述SQL也是根据选择的组织依次拼接。
3、营业厅收费融合报表:
SELECTA.BC_ORG_NAME_6DEPARTNAME,
A.BC_OPER_CODE,
A.BC_OPER_NAMEOPERNAME,
A.SERVICE_CODE,
(SELECTB.PROD_TYPE_NAME
FROMDIM_FIX_PROD_TYPEB
WHEREA.SERVICE_CODE=B.PROD_TYPE_CODE
ANDA.SOURCE_CODE=B.SOURCE_CODE
ANDROWNUM=1)SERVICE_NAME,
A.FEEITEM_CODE,
(SELECTB.FEEITEM_NAME
FROMDIM_FIX_FEEITEMB
WHEREA.FEEITEM_CODE=B.FEEITEM_CODE
ANDA.SOURCE_CODE=B.SOURCE_CODE
ANDROWNUM=1)FEEITEM_NAME,
SUM(CASEA.OPER_TYPE
WHEN'0'THEN
A.PAY_NUM
ELSE
0
END)CHARGENUM,
SUM(CASEA.OPER_TYPE
WHEN'1'THEN
A.PAY_NUM
ELSE
0
END)REFUNDNUM,
SUM(CASEA.OPER_TYPE
WHEN'0'THEN
A.PAY_FEE
ELSE
0
END)CHARGEFEE,
SUM(CASEA.OPER_TYPE
WHEN'1'THEN
A.PAY_FEE
ELSE
0
END)REFUNDFEE,
SUM(A.PAY_FEE)TOTALFEE
FROMST_BUSI_FEE_MONA
WHEREA.MONTH_NO=
TO_CHAR(TO_DATE('2010-08-0100:
00:
00','yyyy-mm-ddhh24:
mi:
ss'),
'yyyymm')
ANDA.BC_ORG_CODE_2IN
(1)
GROUPBYA.BC_ORG_NAME_6,
A.BC_OPER_CODE,
A.BC_OPER_NAME,
A.SERVICE_CODE,
A.FEEITEM_CODE,
A.SOURCE_CODE
UNIONALL
SELECT'总计'DEPARTNAME,
''BC_OPER_CODE,
''OPERNAME,
''SERVICE_CODE,
''SERVICE_NAME,
''FEEITEM_CODE,
''FEEITEM_NAME,
SUM(CASEA.OPER_TYPE
WHEN'0'THEN
A.PAY_NUM
ELSE
0
END)CHARGENUM,
SUM(CASEA.OPER_TYPE
WHEN'1'THEN
A.PAY_NUM
ELSE
0
END)REFUNDNUM,
SUM(CASEA.OPER_TYPE
WHEN'0'THEN
A.PAY_FEE
ELSE
0
END)CHARGEFEE,
SUM(CASEA.OPER_TYPE
WHEN'1'THEN
A.PAY_FEE
ELSE
0
END)REFUNDFEE,
SUM(A.PAY_FEE)TOTALFEE
FROMST_BUSI_FEE_MONA
WHEREA.MONTH_NO=
TO_CHAR(TO_DATE('2010-08-0100:
00:
00','yyyy-mm-ddhh24:
mi:
ss'),
'yyyymm')
ANDA.BC_ORG_CODE_2IN
(1);
4:
营业部收费汇总融合报表:
5:
融合组织管理,已经映射组织SQL:
SELECTC.LAN_NAMEASLANNME,
B.LAN_IDASLANID,
A.DEPART_IDASDEPARTID,
'固网营业:
'||A.DEPART_NAMEASDEPARTNAME,
1ASCODESOURCE
FROMMP_DEPARTMENTA,RR_PPDOMB,RR_LANC
WHEREA.REGION_ID=B.PPDOM_ID
ANDC.LAN_ID=B.LAN_ID
ANDB.LAN_ID=1
ANDA.VALID_FLAG='0'
ANDEXISTS(SELECT1
FROMDIM_ORG_RELAT
WHERELOCAL_CODE_TYPE=2
ANDCODE_SOURCE=1
ANDLOCAL_CODE=A.DEPART_ID||''
ANDBASE_CODE=50)
UNIONALL
SELECTC.LAN_NAMEASLANNME,
B.LAN_IDASLANID,
A.SITE_IDASDEPARTID,
'固网计费:
'||A.SITE_NAMEASDEPARTNAME,
2ASCODESOURCE
FROMDIM_ORG_BILLA,DIM_REGION_BILLB,RR_LANC
WHEREA.REGION_ID=B.REGION_ID
ANDC.LAN_ID=B.LAN_ID
ANDB.LAN_ID=1
ANDEXISTS(SELECT1
FROMDIM_ORG_RELAT
WHERELOCAL_CODE_TYPE=3
ANDCODE_SOURCE=2
ANDLOCAL_CODE=A.SITE_ID||''
ANDBASE_CODE=50)
UNIONALL
SELECTB.LAN_NAMEASLANNME,
(CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END)ASLANID,
DEALER_IDASDEPARTID,
'移网营业:
'||DEALER_NAMEASDEPARTNAME,
3ASCODESOURCE
FROMDIM_ORG_MOB,RR_LANB
WHERE1=1
ANDB.LAN_ID=(CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END)
AND(CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END)=1
ANDEXISTS(SELECT1
FROMDIM_ORG_RELAT
WHERELOCAL_CODE_TYPE=4
ANDCODE_SOURCE=3
ANDLOCAL_CODE=DEALER_ID||''
ANDBASE_CODE=50)
未映射的组织SQL:
SELECT(CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END)ASLANID,
DEALER_IDASDEPARTID,
'移网营业:
'||DEALER_NAMEASDEPARTNAME,
B.LAN_NAMEASLANNME,
3ASCODESOURCE
FROMDIM_ORG_MOB,RR_LANB
WHERE1=1
ANDB.LAN_ID=(CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END)
AND(CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END)=1
ANDDEALER_NAMELIKE'花园路夏自军%'
ANDDEALER_ID=3760004092
ANDNOTEXISTS(SELECT1
FROMDIM_ORG_RELAT
WHERELOCAL_CODE_TYPE=4
ANDCODE_SOURCE=3
ANDLOCAL_CODE=DEALER_ID||'')
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 系统 bug