TRUNCATE TABLE dwh_detailing_con_general_2024;
 INSERT INTO dwh_detailing_con_general_2024

WITH zak AS
(SELECT
    fin.lotuuid ,
    SUM(fin.finvalue) fin_zak
  FROM dwh_lot_fin_kgntv fin
  Join (SELECT DISTINCT contragent_inn,targetexpenseitemcode FROM dwh_kf_sgoz_kgntv WHERE YEAR=2024 AND flag_budget_current=1) t ON t.contragent_inn=fin.inn AND t.targetexpenseitemcode=fin.targetexpenseitemcode
  -- изменение от 30.09.2020 сделано из-за раздвоения строк по косгу
  WHERE finyear=TO_NUMBER(2024)
  GROUP BY fin.lotuuid),
publ_full AS
(SELECT
    tru.lot_id,
    SUM(tru.fulfilled_cost_doc)fulfilled_cost_doc
FROM dwh_contract_tru_kgntv tru
Inner Join dwh_contract_kgntv c ON tru.lot_id=c.lotid AND LENGTH(c.contractrnk)=13 AND fulfilment_sum IS NULL
GROUP BY tru.lot_id)

SELECT CASE WHEN con.customerinn='7820075581' THEN '146504' --в связи с тем, что данный садик реорганзивался в садик с ИНН=7820076257 необходимо суммировать
            WHEN pro.delegated=1 THEN pro.orgid ELSE TO_CHAR(con.customerid) END customerid,
       CASE WHEN pro.delegated=1 THEN pro.orgtitle ELSE con.customername END customername,
       org.short_name,
       CASE WHEN con.customerinn='7820075581' THEN '7820076257' --в связи с тем, что данный садик реорганзивался в садик с ИНН=7820076257 необходимо суммировать
            WHEN pro.delegated=1 THEN org_pp.inn
            ELSE con.customerinn END customerinn,
       CASE WHEN pro.delegated=1 THEN org_pp.kpp ELSE org.kpp END customerkpp,
       CASE WHEN pro.delegated=1 THEN pro.grbstitle ELSE con.grbsname END grbsname,
       CASE WHEN pro.delegated=1 THEN org_pp.grbs_inn ELSE org.grbs_inn END grbs_inn,
       CASE WHEN pro.delegated=1 THEN pro.grbsid ELSE TO_CHAR(org.parentid) END grbsid,
       vg.vg_name vg, --вицегубернатор
       pro.pg_ikz, pro.pg_n, pro.pg_rn,pro.reqnum,
       pro.first_notice_try,--  Дата первой успешно отправленной версии извещения (xml-пакета)в ЕИС ( НЕ ВСЕГДА = Дате публикации извещения)
       pro.F_PRODUCTPRICE,
       NVL(pro.f_productprice,con.contractsigningprice) nmck_rasch,
       zak.fin_zak, --сумма финансирвоания 2021 по закупке
      CASE WHEN pro.joflag=1 THEN 1 ELSE 0 END sovm,
      cons.orgtitle cons_org,
      CASE WHEN pro.placement='Централизованная закупка' THEN 1 ELSE 0 END centr,
      tar.flag_pp, --в рамках ч.6п.15 (переданные полномочия)
      CASE WHEN pro.delegated=1 THEN pro.orgtitle ELSE NULL END org_pp, --организац, осуществляющая полномочия заказчика
      con.CONTRACTRNK, con.contractsigndate,
      con.contractsigningprice,
      con.contractfullprice, --добавлено 16.11.20211 по письму Крицкой Л.А.
      tar.finsum, --сумма финансирования 2021 по контракту
      tar.expensetypecode, tar.kosgucode,
      tar.budget_type, -- тип финансирования
      tar.fund_code,
      tar.req_code, --  код запроса ( добавлено 14.07.2023 по просьбе Крицкой Л.А.)
      con.ORDERTYPENAME,con.CONTRACTONEEXECREASON,
      con.stagetitle,
      con.contract_execution_start_date,--дата начала исполнения контракта
      con.contractplaneexecdate,
      con.CONTRACTREJECTDATE,
      tar.YEAR fin_year, --год финансирования
      tar.targetexpenseitemcode, --целевая статья из контракта
     -- zak.targetexpenseitemcode targ_zak, --целевая статья из закупки
      t.NP_CODE_NAME,
      t.FP_CODE_NAME,
   --   ROW_NUMBER() over (partition by 1 order by con.CUSTOMERNAME desc) rn,
      CASE
        WHEN (tar.targetexpenseitemcode IN ( SELECT targetexpenseitemcode FROM dwh_targetexpenseitemcode_aip ) AND tar.expensetypecode NOT IN ('244'))
        OR (tar.targetexpenseitemcode IN ('022E15305F','021E15305F') AND tar.expensetypecode='414' AND tar.kosgucode='310' AND con.customerinn='7809025523') -- правило по письму крицкой от 5.10.222
        THEN 'Да' ELSE 'Нет'
      END priznak_aip,--Признак совпадения целевой статьи (АИП)
      tar.finsum_f,
      tar.lot_id,
      CASE WHEN (pro.sed_reg_number IS NOT NULL OR pro.request_order_number IS NOT NULL) THEN 1 ELSE 0 END UOPLACING,
      org_uo.FULL_NAME uo_org,
      CON.CONTRACTSUBJECT,
      con.supplierinn,
      con.suppliername,
      TO_CHAR(con.supplierkpp)supplierkpp,
      CASE WHEN pro.delegated=1 THEN org_pp.contragent_account
      ELSE org.contragent_account END contragent_account, --код заказчика
      con.contractactualpaid, --фактически оплачено всего
      NVL(con.fulfilment_sum,publ_full.fulfilled_cost_doc)fulfilment_sum,--стоимость исполненных поставщиком обязательств в валюте исполнения (по письму Тереховой от 22.08.2022)
      pro.date_notice_published,-- Дата размещения первой версии извещения на ЕИС
      con.price_type, -- Способ указания цены контракта
      con.suppliertype,
      con.RTC_PRICE,
      con.supply_impossible

FROM  dwh_contract_kgntv con
Left Join dwh_organization_not_zak org ON org.id=con.customerid   -- 05.07.2023 вместо inner указан left, по причине не попадания контрактов у не актуальных орг-ций.
                                    AND contractrnk NOT IN ('2782534239015000190','2783845426019000015','7400319000003','7400319000002','2783845426019000014','2783845426019000021','2783845426019000018') --Контракты отключены по сообщению Рябовой 13 сентября
                                    AND con.customerid IN (SELECT id FROM dwh_organization_not_zak ) --необходимо убрать учреждения законодательной власти
                                    AND org.id<>1610--16.11.2021 Письмо Крицкой Л.А.
Inner Join
 (
    SELECT fin.lot_id,fin.YEAR,fin.finsum,fin.finsum_f,fin.targetexpenseitemcode, fin.expensetypecode, fin.kosgucode, fin.budget_type, fin.flag_pp ,fin.fund_code,req_code
        FROM FIN_TARG_CON_2024_VIEW fin   -- 28.09.22 создана View с инф-цией по финансированию по контрактам(с 3-мя union)
       WHERE fin.finsum IS NOT NULL
  )tar
ON con.LOTID = tar.LOT_ID
Left join zak ON zak.lotuuid=con.lot_cons_m
Left join zak ON tar.lot_id=zak.lotuuid
left join dwh_procedures_kgntv pro ON pro.LOTUUID = con.lotid
left join dwh_procedures_cons_kgntv cons ON cons.lotuuid=pro.joint_procedure_id
left join DWH_ORGANIZATION_KGNTV org_uo ON org_uo.ID = pro.UO_ORGID
left Join (SELECT DISTINCT targetexpenseitemcode,FP_CODE_NAME,NP_CODE_NAME FROM DWH_TARGETEXPENSEITEMCODE_2020) t ON t.targetexpenseitemcode=tar.targetexpenseitemcode
Left Join DWH_ORGANIZATION_KGNTV org_pp ON org_pp.id=pro.orgid AND pro.delegated=1
Left join dwh_vg_22 vg ON vg.grbs_id=con.grbsid
Left Join publ_full ON publ_full.lot_id=con.LOTID
WHERE ((con.CONTRACTREJECTDATE IS NOT NULL AND tar.finsum_f IS NOT NULL AND tar.finsum_f<>0) OR con.CONTRACTREJECTDATE IS NULL )
-- 05.07.2023 вместо inner указан left, по причине не попадания контрактов у не актуальных орг-ций.
AND org.inn NOT IN ('7812031703','7838468985','7812050079','7802215250','7804169391','7814143064','7802215268','7842000050','7804169401','7813188464','7801238167','7816226189','7811139084','7814143057','7816226502',
              '7842000068','7819029196','7819029206','7839000318','7843000046','7843000039','7810293894','7820038893','7817044400','7820038903','7806143737','7807053821','7810293904','7805283273','7806143720',
              '7811139119','7807053839','7805283280','7813585510','7840425330','7838389412','7825439419','7830001028','7830000426','7830001758'
             )
             AND org.full_name NOT LIKE 'ТЕРРИТОРИАЛЬНАЯ ИЗБИРАТЕЛЬНАЯ КОМИССИЯ%'
;

INSERT INTO dwh_detailing_con_general_2024
WITH
con AS (
            SELECT c.*,contract.suppliertype,contract.rtc_price, contract.supply_impossible,contract.supplierkpp, ROW_NUMBER() Over (PARTITION BY c.contract_regnum ORDER BY  c.contract_regnum DESC) rn
            FROM dwh_contracts_delegated c
            left join dwh_contract_kgntv contract ON contract.contractrnk=c.contract_regnum
            WHERE (c.contract_regnum, c.versionnumber) IN (
                                                             SELECT contract_regnum, MAX(versionnumber)
                                                             FROM dwh_contracts_delegated
                                                             GROUP BY contract_regnum
                                                          )
                  AND c.paymentyear=2024
                  AND contract.contractrnk IS NULL
                 --and c.exec_startyear=extract(year from sysdate)  -- правило добавлено т.к. записи дублируются из-за чего неправильно считается экономия пример контракт 2782536397817000078
       ),
proc AS (
            SELECT *
            FROM (
                    SELECT p.*, ROW_NUMBER() Over (PARTITION BY p.purchasenumber ORDER BY p.docpublishdate DESC) rn
                    FROM dwh_procedures_delegated p
                 ) p
            WHERE rn=1
         )

SELECT TO_CHAR(org.id) orgid, org.full_name,org.short_name,org.inn, org.kpp, org.grbs_full_name, grbs_inn, TO_CHAR(org.parentid) parentid,
       vg.vg_name vg, --вицегубрнатор
       COALESCE(pos.pg_ikz, con.purchasecode, notice.purchasecode) pg_ikz,
       COALESCE(pos.pg_n, notice.tenderplan) pg_n,
       CASE WHEN con.positionnumber IS NOT NULL THEN con.positionnumber ELSE proc.positionnumber END positionnumber,
       con.notificationnumber,
       NULL fitst_notice_try,--дата размещения первой версии извещения
       proc.maxprice,--нмцк
       COALESCE(proc.maxprice,con.contract_price) nmck_rasch, --нмцк расчетное поле
        CASE
            WHEN proc.currentyear IS NULL THEN NULL
            WHEN 2024 = proc.currentyear THEN proc.finsum_currentyear
            WHEN 2024-1 = proc.currentyear THEN proc.finsum_firstyear
            WHEN 2024-2 = proc.currentyear THEN proc.finsum_secondyear
        END finsum_zak,
        0 sovm, --совместная закупка
        NULL AS c_org, --Наименование организации, разместившей извещение
        0 centr, --централизовання закупка
        1 flag_pp,
        con.responsibleorg_fullname, -- Организация, осуществляющая полномочия заказчика
        con.contract_regnum, --РНК
        con.contract_signdate, --дата заключения контракта
        con.contract_price, --цена контракта на момент заключения
        con.contract_price contractfullprice, --текущая цена контракта
        con.paymentsum, --сумма финансирования
        con.kvr,
        NULL kosgu,--косгу
        NULL budjet_type,--тип финансирования
        NULL fund_code, -- код фонда
        NULL req_code, --код запроса
        CASE WHEN con.placing IN ('13013') THEN 'Запрос котировок в электронной форме'
             WHEN con.placing IN ('12011') THEN 'Аукцион в электронной форме'
             WHEN con.placing IN ('14013') THEN 'Запрос предложений в электронной форме'
             WHEN con.placing IN ('20000') OR con.placing IS NULL AND con.singlecustomer IS NOT NULL THEN 'Закупка у единственного поставщика (подрядчика, исполнителя)'
             WHEN con.placing IN ('11013') THEN 'Открытый конкурс в электронной форме'
             WHEN con.placing IN ('11031','11033') THEN 'Двухэтапный конкурс в электронной форме'
             WHEN con.placing IN ('13011') THEN 'Запрос котировок'
             WHEN con.placing IN ('11023') THEN 'Конкурс с ограниченным участием в электронной форме'
             WHEN con.placing IN ('30000') THEN 'Способ определения поставщика (подрядчика, исполнителя),  установленный правительством российской федерации в соответствии со статьей 111 федерального закона'
             WHEN con.placing IN ('14011') THEN 'Запрос предложений'
             WHEN con.placing IN ('11011') THEN 'Открытый конкурс'
             WHEN con.placing IN ('11042') THEN 'Закрытый конкурс'
             WHEN con.placing IN ('11021') THEN 'Конкурс с ограниченным участием'
             WHEN con.placing IN ('12022') THEN 'Закрытый аукцион'
             ELSE NULL
        END sop_name,
        rs.name,
        CASE
            WHEN LOWER(con.contractstage) = 'e' THEN 'Исполнение'
            WHEN LOWER(con.contractstage) = 'et' THEN 'Исполнение прекращено'
            WHEN LOWER(con.contractstage) = 'ec' THEN 'Исполнение завершено'
            WHEN LOWER(con.contractstage) = 'in' THEN 'Аннулировано'
        ELSE NULL END stage,
        con.startdate,
        con.enddate, --Плановая дата исполнения контракта
        con.rejectiondate, --дата расторжения контракта
        TO_NUMBER(2024) finyear, --год финансирования
        con.targetexpenseitemcode, --целевая статья
        t.np_code_name, t.fp_code_name,
        CASE WHEN proc.targetexpenseitemcode IN ( SELECT targetexpenseitemcode FROM dwh_targetexpenseitemcode_aip ) AND con.kvr NOT IN ('244') THEN 'Да' ELSE 'Нет'
        END priznak,--Признак совпадения целевой статьи (АИП)
        -- Case When con.exec_startyear=2022 Then exec_stageprice ELse null End fact,
        NULL fact,
        NULL lotid,
        CASE WHEN maxprice>=40000000 THEN 1 ELSE 0 END, --uoplacing
        NULL uo_org,
        con.contractsubject,
        con.supplier_inn,
        con.supplier_fullname,
        con.supplierkpp,
        org.contragent_account, --код заказчика
        NULL contractactualpaid, --фактически оплачено всего
        NULL fulfilment_sum,
        NULL date_notice_published,
        NULL price_type,
        con.suppliertype,
        con.rtc_price,
        con.supply_impossible

FROM con
join dwh_organization_kgntv org ON con.responsibleorg_fullname=org.full_name AND con.rn=1 --убрал правило из блока con не попадали нужные контракты пример 2782536397817000116
Left join proc ON con.notificationnumber=proc.purchasenumber
Left Join contract_single_supp_reasons rs ON rs.code_oos = con.singlecustomer AND rs.actual = 1
Left Join dwh_targetexpenseitemcode_2020 t ON t.targetexpenseitemcode=con.targetexpenseitemcode
Left Join dwh_schedule_pos_2020_kgntv pos ON pos.pg_rn=CASE WHEN con.positionnumber IS NOT NULL THEN con.positionnumber ELSE proc.positionnumber END
left join dwh_vg_22 vg ON vg.grbs_id=org.parentid
Left join (
            SELECT positionnumber, purchasecode, tenderplan
            FROM (
                    SELECT ROW_NUMBER() over (PARTITION BY positionnumber, docpublishdate ORDER BY positionnumber) rn, t.*
                    FROM dwh_start_notice_nrpz t
                    WHERE (t.positionnumber, t.docpublishdate) IN ( SELECT positionnumber, MAX(docpublishdate)docpublishdate FROM dwh_start_notice_nrpz GROUP BY positionnumber)
                 )
             WHERE rn =1
          ) notice ON notice.positionnumber= CASE WHEN con.positionnumber IS NOT NULL THEN con.positionnumber ELSE proc.positionnumber END
WHERE org.id<>1610 --16.11.2021 Письмо Крицкой Л.А.
;