TRUNCATE TABLE dwh_detailing_zak_general_2024;
INSERT INTO dwh_detailing_zak_general_2024
WITH
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
         ),
con AS (
            SELECT DISTINCT notificationnumber
            FROM dwh_contracts_delegated c
            WHERE (c.contract_regnum, c.versionnumber) IN (
                                                             SELECT contract_regnum, MAX(versionnumber)
                                                             FROM dwh_contracts_delegated
                                                             GROUP BY contract_regnum
                                                          )
        )
SELECT CASE WHEN pro.orgid='141053' THEN '146504' ELSE pro.orgid END orgid, --в связи с тем, что данный садик реорганзивался в садик с ИНН=7820076257 необходимо суммировать
       pro.ORGTITLE, org.short_name,
       CASE WHEN org.inn='7820075581' THEN '7820076257' ELSE org.inn END inn, --в связи с тем, что данный садик реорганзивался в садик с ИНН=7820076257 необходимо суммировать
       org.kpp,  pro.GRBSTITLE, org.grbs_inn, pro.grbsid, vg.vg_name vg,
       pro.pg_ikz,  pro.pg_n, pro.pg_rn, pro.ikz, --ИКЗ из извещения
       pro.reqnum,  COALESCE(pro.DATE_DEMAND_START,pro.first_notice_try) first_notice_try, --  Дата первой успешно отправленной версии извещения (xml-пакета)в ЕИС ( НЕ ВСЕГДА = Дате публикации извещения)
       CASE WHEN pro.joflag=1 THEN 1 ELSE 0 END sovm,
       cons.orgtitle c_org,
       CASE WHEN pro.placement='Централизованная закупка' THEN 1 ELSE 0 END centr,
       fin.flag_pp, --в рамках ч.6п.15 (переданные полномочия)
       pro.orgtitle org_pp, --организац, осуществляющая полномочия заказчика
       CASE WHEN (pro.sed_reg_number IS NOT NULL OR pro.request_order_number IS NOT NULL) THEN 1 ELSE 0 END UOPLACING,
       uo_org.FULL_NAME uo_org,
       fin.LOTUUID,
       fin.finyear, -- год финансирования
       fin.TARGETEXPENSEITEMCODE,
       t.NP_CODE_NAME,t.FP_CODE_NAME,
       CASE WHEN fin.targetexpenseitemcode IN ( SELECT targetexpenseitemcode FROM dwh_targetexpenseitemcode_aip ) AND fin.expensetypecode NOT IN '244'
       OR (fin.targetexpenseitemcode IN ('022E15305F','021E15305F') AND fin.expensetypecode='414' AND fin.kosgucode='310' AND pro.orgid='1506') -- правило по письму крицкой от 5.10.222
            THEN 'Да' ELSE 'Нет'
        END priznak,--Признак совпадения целевой статьи (АИП)
       pro.F_PRODUCTPRICE, fin.FINSUM, fin.budget_type, fin.expensetypecode, fin.kosgucode,fin.fundcode,
       pro.REQUESTID,
       pro.LOTSTAGE,
       pro.FORDERTYPENAME, pro.ONEEXEC,pro.SUBJECT, org.CONTRAGENT_ACCOUNT,
       pro.date_notice_published-- Дата размещения первой версии извещения на ЕИС
FROM FIN_TARG_ZAK_2024_table fin -- 28.09.22 создана таблица с инф-цией по финансированию закупок(с 3-мя union)
  Join dwh_procedures_kgntv pro ON NVL(pro.lot_cons_m,pro.lotuuid)=fin.lotuuid
                                   AND(( ((pro.lot_cons_m=pro.lotuuid AND pro.mord_flag='M') OR pro.lot_cons_m IS NULL) -- берем только мать pro.lot_cons_m=pro.lotuuid and pro.mord_flag='M'
                                   AND pro.reqnum NOT IN (SELECT reqnum FROM wrong_purchase)
                                   AND pro.reqnum IS NOT NULL
                                   AND ( pro.final_decision <>'Отмена закупки' OR pro.final_decision IS NULL)
                                   AND pro.lotstageid NOT IN (93,100,146,61) -- 93 Процедура отменена,
                                                                          -- 100 - обновление от 27.09.2019 корректировка Яковлева А.И. (не учитывать статус закупка не состоялась)
                                                                          -- 146 Закупка отменена от 03.12.2021 Крицкая Л.А.
                                                                          -- 61 Исполнение контракта
                                   AND pro.orgid IN (SELECT id FROM dwh_organization_not_zak ) --необходимо убрать учреждения законодательной власти
                                   AND pro.orgid<>1610 --16.11.2021 Письмо Крицкой Л.А.)
                                   )OR pro.lotuuid=4996697 -- Выключить закупку при переходе в стадию по заключению контракта
                                   )
  Join dwh_organization_kgntv   org ON org.id=pro.orgid
  Left Join dwh_targetexpenseitemcode_2020 t ON t.targetexpenseitemcode=fin.targetexpenseitemcode
  Left Join dwh_contract_kgntv con ON fin.lotuuid=con.lotid
  Left Join dwh_organization_kgntv uo_org ON uo_org.id = pro.uo_orgid
  Left Join dwh_procedures_cons_kgntv cons ON cons.lotuuid = pro.Joint_procedure_id
  Left Join dwh_vg_22 vg ON vg.grbs_id = pro.grbsid
  Left Join (
                SELECT DISTINCT purchasenumber
                FROM dwh_protocol_nrpz_acgz
                WHERE TYPE IN('fcsProtocolDeviation', 'fcsProtocolEvasion', 'epProtocolEvasion', 'epProtocolDeviation')
            ) prot ON prot.purchasenumber=pro.reqnum
  Left Join (
                SELECT DISTINCT purchasenumber
                FROM dwh_start_notice_canc_acgz
            ) canc ON canc.purchasenumber=pro.reqnum
  Left Join (SELECT DISTINCT purchasenumber FROM dwh_notice_prolong_nrpz) notice ON notice.purchasenumber=pro.reqnum
  WHERE   con.CONTRACTRNK IS NULL
        AND prot.purchasenumber IS NULL -- Крицкая Л.А. 19.04.2021 Убрать закупки, в которых есть "уклонение участника или отказ заказчика от заключения контракта"
        AND canc.purchasenumber IS NULL -- Крицкая Л.А. 19.04.2021 Убрать закупки, у которых есть протокол извещения об отмене
        AND pro.reqnum NOT IN (
                                SELECT DISTINCT reqnum
                                FROM dwh_procedures_kgntv pro
                                Join dwh_contract_kgntv con ON con.lotid=pro.lotuuid
                                WHERE pro.reqnum IS NOT NULL
                              ) -- Крицкая Л.А. 19.04.2021 Убрать совместные закупки, если по извещению есть хоть один контракт
        AND CASE WHEN pro.lotstage='Закупка не состоялась (продление сроков подачи заявок)' AND notice.purchasenumber IS NULL THEN 1 ELSE 0 END = 0 --Крицкая Л.А. письмо от 24.06.2021

UNION ALL

SELECT  TO_CHAR(org.id) orgid, proc.responsibleorg_fullname,org.short_name, org.inn, org.kpp,
        org.grbs_full_name, org.grbs_inn, TO_CHAR(org.parentid) grbsid, vg.vg_name vg,
        pos.pg_ikz pg_ikz, proc.plannumber,proc.positionnumber, proc.purchasecode, proc.purchasenumber,
        NULL first_notice_try,
        0 AS sovm,
        NULL AS c_org,
        0 AS centr,
        1 flag_pp,
        proc.responsibleorg_fullname org_pp,
        CASE WHEN maxprice>=40000000 THEN 1 ELSE 0 END, --uoplacing
        NULL uo_org,
        NULL lotuuid,
        2024 finyear, --год финансирования
        proc.targetexpenseitemcode,
        t.np_code_name, t.fp_code_name,
        CASE WHEN proc.targetexpenseitemcode IN ( SELECT targetexpenseitemcode FROM dwh_targetexpenseitemcode_aip ) AND TO_NUMBER(proc.kvr) NOT IN (244) THEN 'Да' ELSE 'Нет'
        END priznak,--Признак совпадения целевой статьи (АИП)
        proc.maxprice,
        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,
        NULL budjet_type,--тип финансирования
        TO_NUMBER(proc.kvr) kvr,
        NULL kosgu,--КОСГУ
        NULL fundcode, -- Код фонда
        NULL requestid,
        NULL status,
        proc.placingway_name,
        NULL onex,
        purchaseobjectinfo
        , org.CONTRAGENT_ACCOUNT,
        NULL date_notice_published
FROM proc
Left Join con ON proc.purchasenumber=con.notificationnumber
Left Join dwh_organization_kgntv org ON proc.responsibleorg_fullname=org.full_name
Left Join dwh_schedule_pos_2020_kgntv pos ON pos.pg_rn=proc.positionnumber
Left Join dwh_targetexpenseitemcode_2020 t ON t.targetexpenseitemcode=proc.targetexpenseitemcode
Left Join dwh_vg_22 vg ON vg.grbs_id=org.parentid
Left Join (
            SELECT DISTINCT purchasenumber
            FROM dwh_protocol_nrpz_acgz
            WHERE TYPE IN('fcsProtocolDeviation', 'fcsProtocolEvasion', 'epProtocolEvasion', 'epProtocolDeviation')
        ) prot ON prot.purchasenumber=proc.purchasenumber
Left Join (
            SELECT DISTINCT purchasenumber
            FROM dwh_start_notice_canc_acgz
        ) canc ON canc.purchasenumber=proc.purchasenumber
Left Join (SELECT DISTINCT purchasenumber FROM dwh_notice_prolong_nrpz) notice ON notice.purchasenumber=proc.purchasenumber
Left Join (SELECT DISTINCT reqnum FROM dwh_procedures_kgntv) p ON p.reqnum=proc.purchasenumber
WHERE proc.purchasenumber IS NOT NULL AND con.notificationnumber IS NULL
AND CASE
            WHEN currentyear IS NULL THEN 0
            WHEN 2024 = currentyear AND finsum_currentyear<>0 THEN 1
            WHEN 2024-1 = currentyear AND finsum_firstyear <>0 THEN 1
            WHEN 2024-2 = currentyear AND finsum_secondyear<>0 THEN 1
            ELSE 0
    END =1
AND prot.purchasenumber IS NULL -- Крицкая Л.А. 19.04.2021 Убрать закупки, в которых есть "уклонение участника или отказ заказчика от заключения контракта"
AND canc.purchasenumber IS NULL -- Крицкая Л.А. 19.04.2021 Убрать закупки, у которых есть протокол извещения об отмене
AND p.reqnum IS NULL --Необходимо убрать те извещения, которые уже есть в АИСГЗ
AND proc.responsibleorg_fullname<>'ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ КУЛЬТУРЫ "ГОСУДАРСТВЕННЫЙ МУЗЕЙ-ЗАПОВЕДНИК "ПАВЛОВСК"'
AND proc.purchasenumber NOT IN (SELECT reqnum FROM wrong_purchase);

-- Удаляем закупки у которых нет протокола подведения итогов определения поставщика и прошло более 50 рабочих дней с момента публикации первой версии извещения
DELETE FROM dwh_detailing_zak_general_2024
WHERE reqnum IN
(SELECT reqnum FROM dwh_detailing_zak_general_2024 z
  LEFT JOIN (
      SELECT purchasenumber,
             TYPE,
            MAX(protocoldate) protocolopr ,
            protocolnumber
        FROM dwh_protocol_nrpz_acgz
       WHERE TYPE IN (
                        'epProtocolEOK3',
                        'epProtocolEZK2020Final',
                        'epProtocolEOKOU3',
                        'epProtocolEOK2020Final',
                        'fcsProtocolEF3',
                        'epProtocolEF2020Final',
                        'epProtocolEZT2020FinalPart')
       GROUP BY purchasenumber, TYPE, protocolnumber) prot ON z.reqnum = prot.purchasenumber
       Left Join (
    SELECT DISTINCT
        protocolnumber,
        purchasenumber,
        protocoldate
    FROM dwh_protocol_nrpz_acgz
    WHERE TYPE IN ('ProtocolCancel','fcsProtocolCancel','epProtocolCancel')
          ) canc ON canc.protocolnumber = prot.protocolnumber AND canc.purchasenumber = prot.purchasenumber AND prot.protocolopr < canc.protocoldate
WHERE (CASE WHEN SYSDATE > (SELECT MIN(t.date_)
                              FROM work_Days_of_2019 t
                              JOIN work_Days_of_2019 t1 ON (t.date_>t1.date_ AND t.nm=t1.nm+50)
                             WHERE t1.date_ >= (SELECT MIN(date_)
                                                  FROM work_Days_all
                                                 WHERE DATE_ >= (TRUNC(z.first_notice_try)) AND TYPE=1)) AND (/*protocolopr is null or*/ canc.protocolnumber IS /*not*/ NULL) THEN 1 ELSE 0 END ) = 1)