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
CREATE OR REPLACE VIEW CON_CONTRACT_REPO_MODIFY_LV AS
SELECT t1.contract_id,
t1.data_class,
t1.calc_session_id,
t1.project_id,
t1.business_type,
t1.division,
t1.lease_organization,
t1.lease_channel,
t1.document_type,
t1.repurchase_contract_number,
t1.contract_number,
t1.contract_name,
t1.bp_id_tenant,
(SELECT m.bp_name
FROM hls_bp_master m
WHERE m.bp_id = t1.bp_id_tenant) AS bp_id_tenant_n,
t1.bp_id_agent_level1,
(SELECT hbm.bp_name
FROM hls_bp_master hbm
WHERE hbm.enabled_flag = 'Y'
AND hbm.bp_category = 'AGENT'
AND hbm.bp_id = t1.bp_id_agent_level1) bp_id_agent_level1_n, --代理店
(SELECT d.description
FROM hls_division d
WHERE d.division = t1.division) AS division_n,
t1.employee_id,
(SELECT e.name
FROM exp_employees e
WHERE e.employee_id = t1.employee_id) AS employee_id_n,
t1.REPURCHASE_TYPES,
(SELECT v.code_value_name
FROM sys_code_values_v v
WHERE v.code = 'REPURCHASE_TYPES'
AND v.code_value = t1.REPURCHASE_TYPES) AS REPURCHASE_TYPES_n,
cr.created_by,
(SELECT su.description
FROM sys_user su
WHERE su.user_id = cr.created_by) change_created_by_n,
t1.unit_id,
(SELECT u.description
FROM exp_org_unit_vl u
WHERE u.unit_id = t1.unit_id) AS unit_id_n,
t1.contract_status,
(SELECT v.code_value_name
FROM sys_code_values_v v
WHERE v.code = 'CON500_CONTRACT_STATUS'
AND v.code_value = t1.contract_status) AS contract_status_n,
cr.req_status,
(SELECT v.code_value_name
FROM sys_code_values_v v
WHERE v.code = 'CON_CHANGE_REQ_STATUS'
AND v.code_value = cr.req_status) AS req_status_n,
cr.repo_change_flag
FROM con_contract t1,
con_contract_change_req cr
WHERE t1.contract_id = cr.change_req_id
AND t1.data_class = 'CHANGE_REQ'
AND cr.repo_change_flag = 'Y'
;