sql
2.23 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
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');