SELECT a.opp_province,
a.area_type,
a.opp_local,
a.cust_manager_name,
(CASE
WHEN length(a.cust_man_dept) != lengthb(a.cust_man_dept) THEN
a.cust_man_dept
ELSE
(SELECT d.dept_name
FROM th_int_dept d
WHERE d.row_id = a.cust_man_dept)
END) AS cust_man_dept_name,
a.cust_name,
a.cust_id,
a.cust_indu,
(CASE
WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
(SELECT t.contact_name
FROM th_contact t
LEFT JOIN th_customer cust ON (cust.row_id = t.work_unit_id)
LEFT JOIN th_party pt ON (pt.row_id = cust.party_id)
WHERE pt.party_code = a.cust_id
AND ROWNUM = 1)
ELSE
(SELECT t.cust_contact
FROM tm_province_cust_temp t
WHERE t.cust_id = a.cust_id
AND t.opp_id = a.opp_id)
END) AS cust_link_man,
(CASE
WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
(SELECT t.office_phone
FROM th_contact t
LEFT JOIN th_customer cust ON (cust.row_id = t.work_unit_id)
LEFT JOIN th_party pt ON (pt.row_id = cust.party_id)
WHERE pt.party_code = a.cust_id
AND ROWNUM = 1)
ELSE
(SELECT t.cust_tel
FROM tm_province_cust_temp t
WHERE t.cust_id = a.cust_id
AND t.opp_id = a.opp_id)
END) AS cust_link_phone,
(CASE
WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
(SELECT t.email_addr
FROM th_contact t
LEFT JOIN th_customer cust ON (cust.row_id = t.work_unit_id)
LEFT JOIN th_party pt ON (pt.row_id = cust.party_id)
WHERE pt.party_code = a.cust_id
AND ROWNUM = 1)
ELSE
(SELECT t.cust_email
FROM tm_province_cust_temp t
WHERE t.cust_id = a.cust_id
AND t.opp_id = a.opp_id)
END) AS cust_link_email,
(CASE
WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
''
ELSE
(SELECT t.cust_property
FROM tm_province_cust_temp t
WHERE t.cust_id = a.cust_id
AND t.opp_id = a.opp_id)
END) AS cust_attr,
a.opp_id,
a.opp_name,
(SELECT p.display_value
FROM pb_list_of_value p
WHERE p.TYPE = 'OPP_DEGREE'
AND p.language_flag = 'CHS'
AND p.stand_code = a.opp_imp) AS opp_imp,
(SELECT p.display_value
FROM pb_list_of_value p
WHERE p.TYPE = 'OPP_PHASE'
AND p.language_flag = 'CHS'
AND p.stand_code = a.opp_phase) AS opp_phase,
a.opp_forsign_time,
(SELECT p.display_value
FROM pb_list_of_value p
WHERE p.TYPE = 'OPP_PROJ_TYPE'
AND p.language_flag = 'CHS'
AND p.stand_code = a.project_type) AS project_type,
(SELECT p.display_value