create or replace view fj_online as
select rx.task_oid, --ID
       null stage_task_oid,
       t.task_data_oid,
       t.to_node_id,
       s.stage_id||'-'||t.task_data_oid reqid, --Ψһж
       (case t.to_node_id
         when 'node15' then
          1 --
         when 'node36' then
          2 --һ
         when 'node38' then
          3 --
       end) onlinetype,
       rx.FLD_SOFTWARE_REQ_NUM FLD_SOFTWARE_REQ_NUM, --
       rx.TASK_TITLE reqdesc, --
       s.stage_name reqphase, --׶
       FUNC_ITSM_CODE_TO_NAME(76, rx.FLD_TYPE) FLD_TYPE, --
       FUNC_ITSM_CODE_TO_NAME(71, rx.FLD_INTEGRATOR) reqINTEGRATOR, --
       (case when rx.fld_m_system is null then
       FUNC_ITSM_CODE_TO_NAME(72, rx.FLD_SYSTEM)
       else FUNC_ITSM_CODE_TO_NAME(141, rx.fld_m_system) end) reqSYSTEM, -- ϵͳ
       nvl(rx.fld_m_system,rx.FLD_SYSTEM) FLD_SYSTEM,
       rx.FLD_INTEGRATOR,
       rx.fld_nf_req_person,
       to_node_name, -- node_name׶
       BOMC_DEPT(FLD_DEMAND_SOURCES_DEP_ID) DEPARTMENT, --
       BOMC_USER_NO(yx.FLD_DEMAND_SOURCES_PERSON) INTRODUCER, --Ա
       responsible_person reqmanager, --Ա
       rx.FLD_DEV_APPROVALS_PERSON devmanager, --Ҿ
       rx.FLD_MAINTAIN_PERSON mainmanager, --άԱ
       FUNC_ITSM_CODE_TO_NAME(75, nvl(s.IS_GRAY_RELEASE, '')) reqgray, --reqgrayǷҶȷ
       nvl(
       (case t.to_node_id
         when 'node15' then
          s.fld_online_result --
         when 'node36' then
          s.fld_one_onl_app_result --һ
         when 'node38' then
          s.fld_two_onl_app_result --
       end),'0') reqresult, --߽
       (case t.to_node_id
         when 'node15' then
          s.fld_online_failure_cause --
         when 'node36' then
          s.fld_one_onl_app_cont --һ
         when 'node38' then
          s.fld_two_onl_app_cont --
       end) reqreason -- ʧԭ
       --,requirement_leader
       --,developer_approver devmanager
       --,maintain_approver mainmanager
       --,h.release_manager relmanager
  from itsm_task_stat_2111 rx,
       ITSM_TASK_STAT_2105 yx,
       fj_req_split,
       itsm_task_stat_2111_data t,
       fj_software_dev_stage s
       --,fj_system_hander h
 where fj_req_split.TASK_OID = yx.TASK_OID --һҵ
   and s.task_oid = rx.task_oid --һ׶
   and s.task_oid = t.task_oid
   and s.stage_id = t.fld_stage_id --׶
   and fj_req_split.SOFTWARE_REQ_STASK_OID = rx.task_oid --
   --and h.system_name(+) = rx.FLD_SYSTEM
      --(0,δߣ1,ɹ2ʧܣ3ǳɹ4,ȡ5)
   and t.to_node_id in ('node15','node36','node38') --ҪߵͼУвܹѾ߳ɹļ¼
   /**
   (case
       --Ҷȷ,һ߻һߣδߵ,ȥnode38
       when s.is_gray_release = '0' and nvl(s.fld_one_onl_app_result, 0) not in (0, 2, 3, 4)
            and nvl(s.fld_two_onl_app_result, 0) in (0, 2, 3, 4) then 'node38'
       --Ҷȷδһߵģnode36ڵ
       when s.is_gray_release = '0' and nvl(s.fld_one_onl_app_result, 0) in (0, 2, 3, 4) then 'node36'
       --ǻҶȷδߵģnode15ڵ
       when s.is_gray_release = '1' and nvl(s.fld_online_result, 0) in (0, 2, 3, 4) then 'node15'
       --
       else ''
   end)
   **/
   --Ŀҵ贴Ŀ
   union all
   select rx.task_oid, --ID
       null stage_task_oid,
       t.task_data_oid,
       t.to_node_id,
       s.stage_id||'-'||t.task_data_oid reqid, --Ψһж
       (case t.to_node_id
         when 'node15' then
          1 --
         when 'node36' then
          2 --һ
         when 'node38' then
          3 --
       end) onlinetype,
       rx.FLD_SOFTWARE_REQ_NUM FLD_SOFTWARE_REQ_NUM, --
       rx.TASK_TITLE reqdesc, --
       s.stage_name reqphase, --׶
       FUNC_ITSM_CODE_TO_NAME(76, rx.FLD_TYPE) FLD_TYPE, --
       FUNC_ITSM_CODE_TO_NAME(71, rx.FLD_INTEGRATOR) reqINTEGRATOR, --
       (case when rx.fld_m_system is null then
       FUNC_ITSM_CODE_TO_NAME(72, rx.FLD_SYSTEM)
       else FUNC_ITSM_CODE_TO_NAME(141, rx.fld_m_system) end) reqSYSTEM, -- ϵͳ
       nvl(rx.fld_m_system,rx.FLD_SYSTEM) FLD_SYSTEM,
       rx.FLD_INTEGRATOR,
       rx.fld_nf_req_person,
       to_node_name, -- node_name׶
       BOMC_DEPT(FLD_DEMAND_SOURCES_DEP_ID) DEPARTMENT, --
       BOMC_USER_NO(yx.FLD_DEMAND_SOURCES_PERSON) INTRODUCER, --Ա
       responsible_person reqmanager, --Ա
       rx.FLD_DEV_APPROVALS_PERSON devmanager, --Ҿ
       rx.FLD_MAINTAIN_PERSON mainmanager, --άԱ
       FUNC_ITSM_CODE_TO_NAME(75, nvl(s.IS_GRAY_RELEASE, '')) reqgray, --reqgrayǷҶȷ
       nvl(
       (case t.to_node_id
         when 'node15' then
          s.fld_online_result --
         when 'node36' then
          s.fld_one_onl_app_result --һ
         when 'node38' then
          s.fld_two_onl_app_result --
       end),'0') reqresult, --߽
       (case t.to_node_id
         when 'node15' then
          s.fld_online_failure_cause --
         when 'node36' then
          s.fld_one_onl_app_cont --һ
         when 'node38' then
          s.fld_two_onl_app_cont --
       end) reqreason -- ʧԭ
  from itsm_task_stat_2127 rx,
       ITSM_TASK_STAT_2126 yx,
       fj_req_split,
       itsm_task_stat_2127_data t,
       fj_software_dev_stage s
 where fj_req_split.TASK_OID = yx.TASK_OID --һҵ
   and s.task_oid = rx.task_oid --һ׶
   and s.task_oid = t.task_oid
   and s.stage_id = t.fld_stage_id --׶
   and fj_req_split.SOFTWARE_REQ_STASK_OID = rx.task_oid --
   and t.to_node_id in ('node15','node36','node38') --ҪߵͼУвܹѾ߳ɹļ¼;
   --ҵ贴Ŀ
   union all
select rx.task_oid, --ID
       null stage_task_oid,
       t.task_data_oid,
       t.to_node_id,
       s.stage_id||'-'||t.task_data_oid reqid, --Ψһж
       (case t.to_node_id
         when 'node15' then
          1 --
         when 'node36' then
          2 --һ
         when 'node38' then
          3 --
       end) onlinetype,
       rx.FLD_SOFTWARE_REQ_NUM FLD_SOFTWARE_REQ_NUM, --
       rx.TASK_TITLE reqdesc, --
       s.stage_name reqphase, --׶
       FUNC_ITSM_CODE_TO_NAME(76, rx.FLD_TYPE) FLD_TYPE, --
       FUNC_ITSM_CODE_TO_NAME(71, rx.FLD_INTEGRATOR) reqINTEGRATOR, --
       (case when rx.fld_m_system is null then
       FUNC_ITSM_CODE_TO_NAME(72, rx.FLD_SYSTEM)
       else FUNC_ITSM_CODE_TO_NAME(141, rx.fld_m_system) end) reqSYSTEM, -- ϵͳ
       nvl(rx.fld_m_system,rx.FLD_SYSTEM) FLD_SYSTEM,
       rx.FLD_INTEGRATOR,
       rx.fld_nf_req_person,
       to_node_name, -- node_name׶
       BOMC_DEPT(FLD_DEMAND_SOURCES_DEP_ID) DEPARTMENT, --
       BOMC_USER_NO(yx.FLD_DEMAND_SOURCES_PERSON) INTRODUCER, --Ա
       responsible_person reqmanager, --Ա
       rx.FLD_DEV_APPROVALS_PERSON devmanager, --Ҿ
       rx.FLD_MAINTAIN_PERSON mainmanager, --άԱ
       FUNC_ITSM_CODE_TO_NAME(75, nvl(s.IS_GRAY_RELEASE, '')) reqgray, --reqgrayǷҶȷ
       nvl(
       (case t.to_node_id
         when 'node15' then
          s.fld_online_result --
         when 'node36' then
          s.fld_one_onl_app_result --һ
         when 'node38' then
          s.fld_two_onl_app_result --
       end),'0') reqresult, --߽
       (case t.to_node_id
         when 'node15' then
          s.fld_online_failure_cause --
         when 'node36' then
          s.fld_one_onl_app_cont --һ
         when 'node38' then
          s.fld_two_onl_app_cont --
       end) reqreason -- ʧԭ
  from itsm_task_stat_2127 rx,
       ITSM_TASK_STAT_2105 yx,
       fj_req_split,
       itsm_task_stat_2127_data t,
       fj_software_dev_stage s
 where fj_req_split.TASK_OID = yx.TASK_OID --һҵ
   and s.task_oid = rx.task_oid --һ׶
   and s.task_oid = t.task_oid
   and s.stage_id = t.fld_stage_id --׶
   and fj_req_split.SOFTWARE_REQ_STASK_OID = rx.task_oid --
   and t.to_node_id in ('node15','node36','node38') --ҪߵͼУвܹѾ߳ɹļ¼;
union
select r.task_oid,
       j.task_oid stage_task_oid,
       d.task_data_id task_data_oid,d.wf_node_id to_node_id,
         s.stage_id||'-'||d.task_data_id reqid,1 onlinetype,r.fld_software_req_num,
         r.TASK_TITLE reqdesc,s.stage_name reqphase,FUNC_ITSM_CODE_TO_NAME(76, r.FLD_TYPE) FLD_TYPE,
         FUNC_ITSM_CODE_TO_NAME(71, r.FLD_INTEGRATOR) reqINTEGRATOR,
         (case when r.fld_m_system is null then FUNC_ITSM_CODE_TO_NAME(72, r.FLD_SYSTEM) else FUNC_ITSM_CODE_TO_NAME(141, r.fld_m_system) end) reqSYSTEM,
         (case when r.fld_m_system is null then r.FLD_SYSTEM else r.fld_m_system end) FLD_SYSTEM,
         r.FLD_INTEGRATOR,r.fld_nf_req_person,d.wf_node_name to_node_name,
         BOMC_DEPT(y.FLD_DEMAND_SOURCES_DEP_ID) DEPARTMENT,
         BOMC_USER_NO(y.FLD_DEMAND_SOURCES_PERSON) INTRODUCER,
         r.fld_responsible_person reqmanager,r.FLD_DEV_APPROVALS_PERSON devmanager,r.FLD_MAINTAIN_PERSON mainmanager,
         FUNC_ITSM_CODE_TO_NAME(75, nvl(s.IS_GRAY_RELEASE, '')) reqgray,
         (case when s.fld_online_result is null then '0' else s.fld_online_result end) reqresult,
         s.fld_online_failure_cause reqreason
  from itsm_task_stat_2111 r,itsm_task_stat_2128 j, fj_software_dev_stage s,itsm_task_data d,itsm_task_stat_2105 y
  where r.task_oid = s.task_oid
   and j.task_oid = s.stage_task_oid
   and d.task_oid = j.task_oid
   and y.task_oid = r.fld_business_req_task_oid
   --and d.data_status=0
   and d.wf_node_id='node4'
   and s.stage_task_oid is not null
;
