CabinetContracts - ContractStageView

/* CabinetContracts - ContractStageView */

Select st.id_stage
      ,st.id_contract
      ,st.identifier -- Номер этапа
      ,st.stage -- Этап (наименование)
      ,st.d_start -- Начало этапа
      ,st.d_end -- Конец этапа
      ,nvl((Select Sum(sa.money)
             From accounts_contract_ ac
                 ,paragraphs_        p
                 ,salary_account_    sa
            Where ac.id_stage = st.id_stage
              And ac.id_paragraph = p.id_paragraph
              And p.idk_order In (436, 444)
              And ac.id_basis_sal = sa.id_basis_sal_charge)
          ,st.money) money -- Стоимость этапа
      ,st.inst -- Институт
      ,nvl((Select Sum(ac.money)
             From accounts_contract_ ac
                 ,paragraphs_        p
                 ,dependences_lists_ dl
                 ,accounts_contract_ ac2
                 ,paragraphs_        p2
            Where ac.id_stage = st.id_stage
              And ac.id_paragraph = p.id_paragraph
              And p.id_order = dl.id_o_main
              And dl.id_o_dependent = p2.id_order
              And p2.id_paragraph = ac2.id_paragraph
              And ac2.id_stage = ac.id_stage
              And ac2.d_start = ac.d_start)
          ,0) pay -- Оплачено
/*
      ,Case
         When (Select Count(Distinct ac.id_paragraph || ac2.id_stage)
                 From accounts_contract_ ac
                     ,paragraphs_        p
                     ,accounts_contract_ ac2
                Where ac.id_stage = st.id_stage
                  And ac.id_paragraph = p.id_paragraph
                  And p.idk_order In (62, 431)
                  And ac.money > 0
                  And st.Status_order = 1
                  And ac2.id_paragraph = p.id_paragraph) = 1 Then
          'http://miit.ru/rs/Квитанция на оплату договора ' || st.Status_Text
           || '.pdf?cd=1&id_list=' ||
          (Select Distinct p.id_order
             From accounts_contract_ ac
                 ,paragraphs_        p
                 ,accounts_contract_ ac2
            Where ac.id_stage = st.id_stage
              And ac.id_paragraph = p.id_paragraph
              And p.idk_order In (62, 431)
              And ac.money > 0
              And ac2.id_paragraph = p.id_paragraph)
         Else
          Null
       End id_par -- Ссылка на печать документа
*/
  From (Select ps.Status_Text
              ,ps.Status_order
              ,nvl(st.subject, 'stage № ' || nvl(st.identifier, 1)) ||
               Decode(sn.id_stages_names
                     ,''
                     ,''
                     ,42478
                     ,''
                     ,42477
                     ,' (разница в планах)'
                     ,' (' || sn.Name || ')') stage
              ,c.d_start d_start_dog
              ,c.d_end d_end_dog
              ,c.id_contract
              ,st.d_start
              ,st.d_end
              ,st.identifier
              ,st.id_stage
              ,nvl((Select Sum(sa.money)
                     From accounts_contract_ ac
                         ,salary_account_    sa
                         ,paragraphs_        p
                    Where ac.id_stage = st.id_stage
                      And ac.id_basis_sal = sa.id_salary_account
                      And ac.id_paragraph = p.id_paragraph
                      And p.idk_order In (444, 436))
                  ,st.money) money
              ,ts_.Get_DivisionName(sd.id_d_institute
                                   ,st.d_start
                                   ,1
                                   ,1
                                   ,2
                                   ,2) inst

          From contracts_             c
              ,employees_             e
              ,paragraphs_status_     ps
              ,stages_                st
              ,stages_names_          sn
              ,web_user_wp            w
              ,specialities_contract_ sc
              ,specialities_division_ sd

         Where c.id_with_whom = e.id_contractor
           And w.id_web_user = :Bind_IdWebUser
           And e.id_e = w.id_e_kis
           And c.id_contract = st.id_contract
           And c.id_paragraph = ps.id_paragraph
           And c.id_contract = sc.id_contract(+)
           And sc.id_d_specialitie = sd.id_d_specialitie(+)
           And st.id_stages_names = sn.id_stages_names(+)) st

Order by st.d_start, st.identifier, st.stage