sql
2.71 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
update GRAS_DEPT_SERVICER
set
SERVICER_NO=#{servicerNo,jdbcType=VARCHAR},
UPDATED_USER = #{updatedUser,jdbcType=VARCHAR},
UPDATED_DATE =sysdate
where
DEPT_NO =#{deptNo,jdbcType=VARCHAR}
and IS_VALID='Y'
select gbe.bank_code,di.dept_name,si.emp_no,si.emp_name,bi.branch_code,bi.branch_name,gbe.PK_SERIAL
from GRAS_BANK_EMP gbe,
staff_info si,
department_info di,
branch_info bi
where
gbe.emp_no=si.emp_no
and si.dept_no=di.dept_no
and di.branch_code=bi.branch_code
and gbe.END_DATE is null
SELECT ppi.policy_no
FROM policy_prem_info ppi,
policy p,
rn_policy_service_info rpsi,
branch_code_relation bcr,
policy_product e
WHERE ppi.prem_status = '1'
AND ppi.policy_no = p.policy_no
AND p.channel_type IN ('01', '02', '04', '05', '07')
AND ppi.prem_source = '1'
AND ppi.prem_due_date >= c_start_date
AND ppi.prem_due_date < c_end_date
AND ppi.policy_no = rpsi.policy_no
AND rpsi.orphan_mark = '1'
AND ppi.policy_no = p.policy_no
--and p.branch_code = bcr.relative_branch_code
--DMP-9274 保单分配记忆和标记功能 20150803
--调整为：只考虑正常分单（01、null）和跨机构分单（03）
AND (rpsi.assign_rule IN ('01', '02') OR rpsi.assign_rule IS NULL)
AND (CASE
WHEN rpsi.assign_rule = '01' OR rpsi.assign_rule IS NULL THEN
p.branch_code
WHEN rpsi.assign_rule = '02' THEN
rpsi.assign_branch_code
END) = bcr.relative_branch_code
AND bcr.branch_code = p_assign_branch_code
--排除归属于二级机构的保单
AND bcr.relative_grade < 1
--AND (select bi.branch_level from branch_info bi where bi.branch_code = bcr.relative_branch_code) <> '02'
AND p.policy_no = e.policy_no
AND e.prod_seq = 1
AND e.product_code not in('CBAN_TN1','CBAN_UN1','CBAN_VN1');
select di.dept_no,bcr.branch_code,gbe.bank_code from
department_info di
inner join branch_info bi on di.branch_code=bi.branch_code
inner join branch_code_relation bcr on bi.branch_code=bcr.branch_code
left join gras_bank_emp gbe on di.dept_no=gbe.bank_code
where
di.channel_type='02'
and bcr.relative_grade>-1
and gbe.bank_code is null
and bcr.relative_branch_code='865100';