CabinetContracts StageReceiptView

/* CabinetContracts StageReceiptView */

WITH div AS
(
SELECT sf.id_d
       , MAX(ac.d_start) d_start
FROM accounts_contract_ ac
     , stages_ st
     , contract_sources_ cs
     , source_financing_ sf
WHERE ac.id_stage = :Bind_IdStage
      AND ac.id_stage = st.id_stage
      AND st.id_contract = cs.id_contract
      AND ac.d_start BETWEEN cs.d_start AND nvl(cs.d_end, ac.d_start)
      AND cs.id_source = sf.id_source
GROUP BY sf.id_d
)
, sb AS(
SELECT s.idd_main AS id_d
FROM
(
SELECT s.idd_main, s.idd_subordinate
FROM subordination_ s
WHERE sysdate BETWEEN s.d_start AND nvl(s.d_end, sysdate)
) s
START WITH s.idd_subordinate
IN
(
SELECT div.id_d
FROM div
)
CONNECT BY prior decode(s.idd_main, s.idd_subordinate, NULL, s.idd_main) = s.idd_subordinate

UNION
SELECT id_d
FROM div
)
, org AS(
SELECT o.id_contractor
FROM organizations_ o
     , sb
     , division_ d
WHERE sb.id_d = d.id_d
      AND d.id_contractor = o.id_contractor
)

SELECT ac.d_start || '-' || ac.d_end AS DISPLAY
          , CASE
         WHEN EXISTS
-- свой счёт у филиалов - по старой форме!
(
SELECT 'x'
FROM accounts_ a
     , org
     , organizations_ o
WHERE a.id_contractor = org.id_contractor
      AND org.id_contractor = o.id_contractor
      AND o.id_organization <> p_environment_.get_ve_us_text(555)
      AND sysdate between a.d_start and nvl(a.d_end, sysdate)
)
THEN to_char(wpms_env_wp.get_property('common.org.app_server_url'))||'/report/generate?_id=receipt-education&'||'id_order='
 || p.id_order||'&'||'_filename='||'Направление на оплату договора ' ||REPLACE(ps.Status_Text, '/', '-')

            WHEN p.idk_order = 62 THEN
to_char(wpms_env_wp.get_property('common.org.app_server_url'))||'/report/generate?_id=receipt-education-moscow&'||'id_order='
 || p.id_order||'&'||'_filename='||'Направление на оплату договора ' ||REPLACE(ps.Status_Text, '/', '-')

            WHEN p.idk_order = 431 THEN
/*
to_char(wpms_env_wp.get_property('common.org.portal_url'))||'/rs/Направление на оплату договора ' || REPLACE(ps.Status_Text, '/', '-')
                 || '.pdf?cd=1&'||'id_list=' || p.id_order
*/
to_char(wpms_env_wp.get_property('common.org.app_server_url'))||'/report/generate?_id=receipt-education-tcb&'||'id_paragraph='
 || p.id_paragraph||'&'||'_filename='||'Направление на оплату договора ' ||REPLACE(ps.Status_Text, '/', '-')
            END AS LINK

          , st.id_stage

FROM accounts_contract_ ac
     , paragraphs_ p
     , stages_ st
     , contracts_ c
     , paragraphs_status_ ps

WHERE ac.id_stage = :Bind_IdStage AND
      st.id_stage = ac.id_stage
      AND ac.id_paragraph = p.id_paragraph
      AND p.idk_order IN (62, 431)
      AND ac.money > 0
      AND st.id_contract = c.id_contract
      AND c.id_paragraph = ps.id_paragraph