select  pr.id rid, --2388195
    l.id lid,
    pr.import_request_uuid,
    c.full_name      as orgname,
    c.id             as orgid,
    pp.upo_id as org_uo_id,
    gcc.code         as orgcode,
    c_grbs.full_name as grbsname,
    c_grbs.id        as grbsid,
    coalesce(pr.registry_number,join_lots.registry_number) reqnum,
        pp.order_number plannum,
    substring(pr.title, 0, 2000) reqsubject,
    case when pp_spec.detail_parent_id is not null then 'Особая закупка' when nkp.naumen_name is null then vpt.name else nkp.naumen_name end ot_naumen_name,
    case when pp_spec.detail_parent_id is not null then 101  when nkp.naumen_code is null then vpt.id+100 else nkp.naumen_code end ot_naumen_code,
    plc.name oneex_reason,
    pr.date_added,
    pr.date_published date_published,
    pp.registration_start_date date_demand_start,
    l.date_placed,
    l.date_fulfilled,
    first_notice_try.date first_something,
    nks.naumen_name,
    nks.naumen_id,
    vps.full_name lotstage,
    vps.id lotstageid,
    case when pr.oos_publish_status != 2 and nks.kgntv_name =
        'Ожидание публикации' then 'request' else nks.entity end entity,
    plptp.name changereason,
    plppg.short_name changereason_pg,
    plppz.short_name changereason_pz,
        (CASE WHEN pp.final_decision = 1 then 'Отмена закупки'
        WHEN pp.final_decision = 2 then 'Изменение закупки'
        else '' end) final_decision,
    case when vps.id = 93             then 1           else 0               end ignored,
    case when f_pvtp.name is not null then f_pvtp.name else first_fin.year_ end
    f_year,
    first_fin.year_ first_fin_year,
    l.number lotnum,
    l.plan_position_number lotoosnum,
    substring(l.subject, 0, 2000) lotsubject,
    case when pp.detail_parent_id is not null then l.scheduled_price_total else l.start_price end /* l.start_price*/ start_price,
    case when pp.detail_parent_id is not null then l.scheduled_price_total
        --when  pr.po_schedule_position_id is not null  and sch20.id is not null then l.final_price
        else l.start_price end /* l.start_price*/ final_price,
        --l.final_price,
    vpk."name" production_type,
    ve.description etp,
    substr(pl.min_reqs::text, 0, 2000) min_reqs,
    substring(pl.supply_place, 0, 2000) supply_place,
    pl.advance_pay_amount,
    l.guarantee_contract con_cover,
    case when pl.schedule_id is null then 0 else 1 end planexist,
    coalesce(GREATEST(preferences.smp,participants.smp,restriction.smp), 0) smp,
    coalesce(GREATEST(preferences.forsmp,participants.forsmp,restriction.forsmp), 0) forsmp,
    coalesce(preferences.invalid, 0) invalid,
    coalesce(preferences.criminal, 0) criminal,
    coalesce(preferences.ceoregion, 0) ceoregion,

    coalesce(preferences.smp, 0) smp_preference,
    coalesce(preferences.forsmp, 0) forsmp_preference,
    coalesce(preferences.invalid, 0) invalid_preference,
    coalesce(preferences.criminal, 0) criminal_preference,
    coalesce(preferences.ceoregion, 0) ceoregion_preference,
    coalesce(participants.smp, 0) smp_participant,
    coalesce(participants.forsmp, 0) forsmp_participant,
    coalesce(participants.invalid, 0) invalid_participant,
    coalesce(participants.criminal, 0) criminal_participant,
    coalesce(participants.ceoregion, 0) ceoregion_participant,
    coalesce(restriction.smp, 0) smp_restriction,
    coalesce(restriction.forsmp, 0) forsmp_restriction,
    substr(pl.restriction_fs, 0, 1000) restriction_fs , --ограничение участия в определении поставщика(п4 ст42)

    case when (pp.basis_for_single_supplier = 100004 or
        pp.basis_for_single_supplier = 100005 or (pp.basis_for_single_supplier =
        8361009 and l.plan_position_number is null) or pp.basis_for_single_supplier
        = 8361012 or pp.basis_for_single_supplier = 8724068 or
        pp.basis_for_single_supplier = 8395978 or pp.basis_for_single_supplier =
        8724074 or (pp.basis_for_single_supplier = 8724059 and
        l.plan_position_number is null)) then 1 else 0 end ispublic,
    case when l.start_price >= 40000000 or ( l.start_price < 40000000 and l.start_price >= 3000000 and lower(c_grbs.full_name)::text ~~* 'администрация% района%'::text and lower(c.full_name)::text !~~* 'администрация% района%'::text) then 1 else 0 end uoplacing,
    case when l.start_price >= 40000000 AND pr.procedure_type NOT IN (6, 10, 14, 15) then 'Подлежит размещению в УпО' when (l.start_price < 40000000 AND l.start_price >= 3000000 AND vc.org_is_placement_grbs_possible = true AND pr.procedure_type NOT IN (6, 10, 14, 15)) then 'Централизованная закупка' else 'Размещается Заказчиком' end placement,
/*    case when pl.consolidate_lot_id is not null or pp.joint_procedure_id is not null then 1 else 0 end can_be_joint, изменение от 2016-11-15*/
    case when pl.consolidate_lot_id is not null or pp.joint_procedure_id is not null or pp.can_be_joint = true then 1 else 0 end can_be_joint,
    coalesce(pl.consolidate_lot_id, join_lots.lot_id)   joint_procedure_id,
    cc.contract_amount,
    case when pp.detail_parent_id is not null then l.scheduled_price_total else l.start_price end /* l.start_price*/ - cc.contract_amount eco,
    pp.request_order_number, pp.sed_registration_number,pp.sed_registration_date ,
    l.date_end_registration,
    l.date_end_first_parts_review,
    COALESCE(l.date_end_second_parts_review, date_auction.date_end_second_parts_review) date_end_second_parts_review,
    pl.goods_ban,
    pl.public_discussion_num,
    pl.purchase_code ikz,
    coalesce(pl.schedule_id,l_big.schedule_id) schedule_id, -- ID Плана графика
    coalesce(pl.purchase_code,l_big.pg_ikz) pg_ikz,    --  ИКЗ Плана-графика
    pl.purchase_code_in_plan pz_ikz,    --  ИКЗ Плана закупок
    coalesce(l.plan_position_number,l_big.pg_rn) pg_rn, -- РН позиции Плана-графика
    '' pz_rn,
    coalesce(SCH.plan_number,l_big.pg_n) pg_n, -- РН Плана-графика
--    '' pz_n,  -- РН Плана закупок

    pl.contract_fulfill_date,  --Срок исполнения контракта
    plsp.special_type,--Тип особых закупок
    plsp.special_type_pz,--наименование типа для планов закупок
    plsp.special_type_pg,--наименование типа для планов-графиков

    pr.date_notice_planned_published,--Планируемая дата публикации извещения
    pr.date_notice_published, --Фактическая дата публикации извещения
    pr.date_notice_planned_change, --Планируемая дата публикации изменения в извещение
    pr.date_notice_change, --Фактическая дата публикации изменения в извещение
        CASE WHEN pr.send_to_oos = true then 1 else 0 end send_to_oos ,--Флаг отправки процедуры на ЕИС
    pl.advance_hand_amount,
    substring(pr.basis_proc_type, 0, 500) basis_proc_type, --Обоснование выбранного способа определения поставщика (подрядчика, исполнителя)
    substring(pvbfq.name,0,500)  basis_for_quotation_req --Основание для для проведения запроса предложений
    ,
        Case when pp.type_spec_purch is null then fin.fin_position_all Else fin_detailed.fin_position_all end fin_position_all, -- Финансирование из ПГ (общая сумма по позиции)
        Case when pp.type_spec_purch is null then fin.fin_position_cur Else fin_detailed_2020.fin_position_cur end fin_position_cur,-- Финансирование из ПГ по 2020 году (сумма по позиции)

    case when pr.po_schedule_position_id is null then 'M' else 'D' end MorD_flag --Флаг закупка-"Мать" или закупка-"Дочка"
  from procedures pr
  join po_procedures pp on pp.procedure_id = pr.id
  left join po_list_contract_single_supplier_reasons plc  on plc.code = pp.basis_for_single_supplier
  left join po_list_plan_change_reasons plptp on plptp.code = pp.basis_for_change

  left join po_list_tend_plan_2017_change_reasons plppg on plppg.code = pp.basis_schedule_plan
  left join po_list_plan_position_change_reasons plppz  on plppz.code = pp.basis_plan
  left join
    (select code special_type, max(tend_full_name) special_type_pg, max(prch_full_name) special_type_pz
      from po_list_special_purchases
      where actual = true
      group by code
    )plsp on plsp.special_type = pp.type_spec_purch
  left join po_vocab_time_periods f_pvtp  on f_pvtp.id = pp.finance_time_period_id
  left join po_vocab_time_periods pvtp  on pvtp.id = pp.time_period_id
  left join vocab_procedure_types vpt on vpt.id = pr.procedure_type
  left join site.naumen_kgntv_placingtypes nkp  on nkp.kgntv_id = vpt.id
  left join lots l  on l.procedure_id = pr.id
  left join lot_customers lc  on lc.lot_id = l.id and lc.actual = true
  left join contragents c on c.id = lc.customer_id  --on c.id = pr.organizer_contragent_id

  left join view_contragents vc on c.id = vc.id
  left join gpospb_contragents gcc  on gcc.contragent_id = c.id
  left join
    (select  lc.lot_id
      from lot_customers lc
      where lc.actual = true
      group by lc."lot_id"
      having count( * ) > 1
    ) cons_lot  on cons_lot.lot_id = l.id
  left join
    (select procedure_id, min(case when step_id = 'electronic_auction' then date_start else null end) date_end_second_parts_review
      from procedure_steps group by procedure_id
    ) date_auction on date_auction.procedure_id = pr.id
  left join procedure_steps ps  on ps.id = l.current_step --and ps.actual = true
  left join vocab_procedure_steps vps on vps.pseudo::text = ps.step_id::text --and vps.actual = true
  left join site.naumen_kgntv_stages nks  on nks.kgntv_id = vps.id
  left join po_list_org_hierarchy ploh  on ploh.contragent_id = c.id and ploh.actual = true and   ploh.parent_type = 1
  left join contragents c_grbs  on c_grbs.id = ploh.parent_contragent_id
  left join po_lots pl  on pl.lot_id = l.id
  left join
    (select  cc.lot_id,
        max(cc.contract_amount) contract_amount
      from cm_contracts cc
      group by cc.lot_id
    ) cc  on l.id = cc.lot_id
  left join
    (select
        pol.lot_id,
        l.procedure_id,
        p.registry_number registry_number
      from po_lots pol
      inner join lots l on l."id" = pol.consolidate_lot_id
      inner join procedures p on p."id" = l.procedure_id
      where pol.consolidate_lot_id is not null
    ) join_lots on join_lots.lot_id = l.id
  left join
    (select  l.id lot_id,
        min(pvtp.name) year_
      from lots l
      inner join po_finances pf     on pf.lot_id = l.id
      inner join po_account pa      on pa.id = pf.po_account_id
      inner join po_budget pb     on pb.id = pa.po_budget_id
      inner join po_vocab_time_periods pvtp     on pvtp.id = pb.po_period_id
      group by l.id
    ) first_fin on first_fin.lot_id = l.id
  left join
    (select  pp.lot_id,
        max(case when (plpp.short_name)::text in('MP44', 'MP') then 1 else 0 end) smp,
        max(case when (plpp.short_name)::text in('MPSP44') then 1 else 0 end) forsmp,
        max(case when (plpp.short_name)::text in('IN44') then 1 else 0 end) invalid,
        max(case when (plpp.short_name)::text in('UG44') then 1 else 0 end) criminal,
        max(case when (plpp.short_name)::text in('RM', 'RBK44') then 1 else 0 end)ceoregion
      from po_list_purchase_preferences plpp
      join po_preferences pp  on pp.preference_id = plpp.id
      where plpp.actual = true or (plpp.short_name)::text in('MP44', 'MP')
      group by pp.lot_id
    ) preferences on preferences.lot_id = l.id
  left join
    (select  pp.lot_id,
        max(case when (plpp.short_name)::text in('MP44', 'MP','MB44330') then 1 else 0 end) smp,
        max(case when (plpp.short_name)::text in('MPSP44','TS44530'/*,'NOSP44'*/) then 1 else 0 end) forsmp,
        max(case when (plpp.short_name)::text in('IN44') then 1 else 0 end) invalid,
        max(case when (plpp.short_name)::text in('UG44') then 1 else 0 end) criminal,
        max(case when (plpp.short_name)::text in('RM', 'RBK44') then 1 else 0 end)ceoregion
      from po_list_purchase_preferences plpp
      join po_participant_requirements pp on pp.requirement_id = plpp.id
      --where plpp.actual = true or (plpp.short_name)::text in('MP44', 'MP')
      group by pp.lot_id
    ) participants on participants.lot_id = l.id

  left join
    (select  pp.lot_id,
        max(case when (plpp.short_name)::text in('MP44', 'MP','MB44330') then 1 else 0 end) smp,
        max(case when (plpp.short_name)::text in('MPSP44','TS44530'/*,'NOSP44'*/) then 1 else 0 end) forsmp,
        max(case when (plpp.short_name)::text in('IN44') then 1 else 0 end) invalid,
        max(case when (plpp.short_name)::text in('UG44') then 1 else 0 end) criminal,
        max(case when (plpp.short_name)::text in('RM', 'RBK44') then 1 else 0 end) ceoregion
      from po_list_purchase_preferences plpp
      join po_restrictions pp on pp.restriction_id = plpp.id and pp.actual is true
      --where plpp.actual = true or (plpp.short_name)::text in('MP44', 'MP')
      group by pp.lot_id
    ) restriction on restriction.lot_id = l.id

  left join vocab_procedure_kind vpk  on vpk."id" = pp.production_type
  left join vocab_etplist ve  on ve.id::text = pp.etp_place
  left join
    (select min(last_attempt) date, procedure_id
        from oos_messages
        where "type" like 'notification%'
        group by procedure_id
    )first_notice_try on first_notice_try.procedure_id = pr.id
  left join
    (select id, plan_number
        from po_schedules
    )sch on sch.id = pl.schedule_id

  left join (select distinct detail_parent_id from po_procedures pp) pp_spec on pp_spec.detail_parent_id = pr.id
  left join po_vocab_bases_for_quotation pvbfq  on pvbfq.id = pp.basis_for_quotation_req
  left Join (
        select
              p.id,
              sum(f.amount) AS fin_position_all,
              -- sum( case when f.year=11/*11=2020*/ then f.amount else null end) fin_position_2020
                            sum( case when t.code='CUR' then f.amount else null end) fin_position_cur
        from procedures p
        join po_schedule_2020_position sp ON sp.procedure_id = p.id or sp.id = p.po_schedule_position_id
        join po_schedules_2020 s ON s.id = sp.schedule_id
        join po_schedule_2020_position_finances f ON sp.id = f.po_schedule_position_id
        join po_vocab_time_periods t ON t.id = f.year AND f.actual is true
             group by sp.schedule_id, p.id
        ) fin on fin.id = pr.id
   Left Join (
                            select  s.schedule_number,
                                            pp.type_spec_purch,
                                            sum(f.amount) AS fin_position_all,
                                            -- sum( case when f.year=11/*11=2020*/ then f.amount else null end) fin_position_2020
                                            sum( case when t.code='CUR' then f.amount else null end) fin_position_cur
                            from procedures p
                            join po_procedures pp   on pp.procedure_id = p.id
                            join po_schedule_2020_position sp ON sp.procedure_id = p.id --or sp.id = p.po_schedule_position_id
                            join po_schedules_2020 s ON s.id = sp.schedule_id
                            join po_schedule_2020_position_finances f ON sp.id = f.po_schedule_position_id
                            join po_vocab_time_periods t ON t.id = f.year AND f.actual is true
                            where sp.number_reg is not null and pp.type_spec_purch is not null
                            --order by pp.type_spec_purch,f.amount desc
                            group by s.schedule_number,sp.schedule_id, pp.type_spec_purch--p.id,
                         ) fin_detailed on fin_detailed.schedule_number = SCH.plan_number
                                                        and fin_detailed.type_spec_purch=pp.type_spec_purch
     Left join (
                            select  s.schedule_number,
                                            p.id,
                                            --sum( case when f.year=11/*11=2020*/ then f.amount else null end) fin_position_2020
                                            sum( case when t.code='CUR' then f.amount else null end) fin_position_cur
                            from procedures p
                            join po_procedures pp   on pp.procedure_id = p.id
                            join po_schedule_2020_position sp ON sp.procedure_id = p.id or sp.id = p.po_schedule_position_id
                            join po_schedules_2020 s ON s.id = sp.schedule_id
                            join po_schedule_2020_position_finances f ON sp.id = f.po_schedule_position_id
                            join po_vocab_time_periods t ON t.id = f.year AND f.actual is true
                            where sp.number_reg is not null and pp.type_spec_purch is not null

                            group by s.schedule_number,sp.schedule_id,p.id
                         ) fin_detailed_2020 on fin_detailed_2020.id = pr.id
        left join (
                                select  l.id,
                                                pl_big.schedule_id,
                                                sch.plan_id,SCH.plan_number pg_n, -- РН Плана-графика
                        (po_plan.plan_number::numeric) pz_n,
                        pl_big.purchase_code pg_ikz,    --  ИКЗ Плана-графика
                        pl_big.purchase_code_in_plan pz_ikz,    --  ИКЗ Плана закупок
                        l_big.plan_position_number pg_rn -- РН позиции Плана-графика
                                                ,pp.detail_parent_id procedure_id_parent
FROM contragents c
                JOIN po_list_org_hierarchy ploh on ploh.contragent_id = c.id and ploh.actual = true and         ploh.parent_type = 1
                JOIN po_plan on c.id = po_plan.customer_org_id  --
                JOIN po_schedules sch on po_plan.id = sch.plan_id--
                JOIN po_lots pl_big     on sch.id = pl_big.schedule_id  --
                JOIN lots l_big on pl_big.lot_id = l_big.id
                JOIN po_procedures pp on l_big.procedure_id = pp.detail_parent_id
                JOIN procedures pr on pp.procedure_id = pr.id
                JOIN lots l     on l.procedure_id = pr.id
                                ) l_big on l_big.id=l.id

  where /*pr.id='2419059' and */-- sch20.schedule_number='202003722001071001' and
 l.id is not null and cons_lot.lot_id is null and pp.joint_procedure_id is null --and c.actual = true
and vps.actual = true
and pr.id not in (Select p.id from procedures p
                                    join po_schedule_2020_position pos on (p.id=pos.procedure_id and p.po_schedule_position_id is null ) or p.po_schedule_position_id=pos.id
                                    )
                                    --and l.id='2621429'-- '2641226'