np_aip_parametrs

TRUNCATE TABLE aip_zak_raz_detail_${import_year};
INSERT INTO aip_zak_raz_detail_${import_year}
SELECT
       pro.orgtitle,org.inn,org.kpp,  pro.grbstitle, pro.grbsid,
       pro.pg_ikz,  pro.pg_n, pro.pg_rn,
       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,
       pro.uoplacing, uo_org.full_name uo_org,
       fin.lotuuid,
       fin.targetexpenseitemcode,
       t.NP_CODE_NAME,t.FP_CODE_NAME,
       Case
            When  fin.targetexpenseitemcode in ( Select * From dwh_targetexpenseitemcode_aip ) and fin.expensetypecode not in ('244') Then 'Да' else 'Нет'
        End PRIZNAK_AIP,--Признак совпадения целевой статьи (АИП)
       pro.F_PRODUCTPRICE, fin.FINSUM, fin.budget_type, fin.expensetypecode, fin.kosgucode,
       pro.requestid,
       pro.lotstage,
       pro.fordertypename, pro.oneexec,pro.subject,
       fin.FUNDCODE, -- Код фонда
       fin.CONTRAGENT_ACCOUNT, -- Код заказчика
       pro.DATE_NOTICE_PUBLISHED -- Дата размещения первой версии извещения на ЕИС
FROM (
            SELECT fin.lotuuid,fin.targetexpenseitemcode, fin.fundkind budget_type, fin.expensetypecode, coalesce(fin.kosgucode,lsr.kosgu,'0') kosgucode, 0 flag_pp,
                   SUM(fin.finvalue) finsum,
                   fin.FUNDCODE,
                   org.CONTRAGENT_ACCOUNT
            FROM dwh_lot_fin_kgntv fin
            Join dwh_organization_kgntv org ON org.inn=fin.inn
            Left Join dwh_kf_lsr lsr ON lsr.exp_code=fin.expensesnumeration and lsr.year=${import_year}
            WHERE LOWER(fin.fundkind) IN ('субсидии на госзадания','субсидии на иные цели') AND fin.FINYEAR = ${import_year}
                AND org.role_code IN (10,3) --роль автономных и бюджетных учреждений
                AND targetexpenseitemcode IN ( SELECT targetexpenseitemcode FROM dwh_targetexpenseitemcode_aip)
            /*    And fin.expensetypecode not in ('415','452') -- письмо Л.А.Крицкой от 12.04.2021
                and nvl(fin.kosgucode,lsr.kosgu) not in ('298','299')*/
            GROUP BY fin.lotuuid,fin.targetexpenseitemcode, fin.fundkind, fin.expensetypecode, NVL(fin.kosgucode,lsr.kosgu), fin.FUNDCODE, org.CONTRAGENT_ACCOUNT, 0

            UNION

            SELECT  fin.lotuuid,fin.targetexpenseitemcode, fin.fundkind budget_type, fin.expensetypecode, NVL(fin.kosgucode,lsr.kosgu) kosgucode, 0 flag_pp,
                  SUM(fin.finvalue) fins,
                  fin.FUNDCODE,
                  org.CONTRAGENT_ACCOUNT
            FROM dwh_lot_fin_kgntv fin
            Join dwh_organization_kgntv org ON org.inn=fin.inn
            Left Join dwh_kf_lsr lsr ON lsr.exp_code=fin.expensesnumeration and lsr.year=${import_year}
            WHERE UPPER(fin.fundkind) NOT LIKE '%ОСТАТКИ%' AND fin.FINYEAR = ${import_year}  AND org.role_code IN (1,8) --ИОГВ+ГКУ
                AND ((fin.expensetypecode BETWEEN 200 AND 247 )
                      OR (fin.expensetypecode = 323 AND org.grbs_inn NOT IN ('7825675663','7840013199')) --комитет по социальной политике (+ жилищный комитет Белоусова А.А 21.12.2020)
                      OR ((fin.expensetypecode = 414 OR fin.expensetypecode = 412 OR fin.expensetypecode = 466) AND coalesdce(fin.kosgucode,lsr.kosgu) NOT IN (297,298,299,530)))
                AND targetexpenseitemcode IN ( SELECT targetexpenseitemcode FROM dwh_targetexpenseitemcode_aip)
                AND (
                            (fin.targetexpenseitemcode NOT IN ('0210020830','0210020850','0220020820','0220020840','0510060500') AND org.inn='7832000076')
                            OR
                            org.inn<>'7832000076'
                           )
            GROUP BY fin.lotuuid, fin.targetexpenseitemcode, fin.fundkind, fin.expensetypecode, NVL(fin.kosgucode,lsr.kosgu), fin.FUNDCODE, org.CONTRAGENT_ACCOUNT, 0

            UNION

            SELECT  fin.lotuuid,fin.targetexpenseitemcode, fin.fundkind budget_type, fin.expensetypecode, NVL(fin.kosgucode,lsr.kosgu) kosgucode, 1 flag_pp,
                  SUM(fin.finvalue) fins,
                  fin.FUNDCODE,
                  org.CONTRAGENT_ACCOUNT
            FROM dwh_lot_fin_kgntv fin
            Join dwh_organization_kgntv org ON org.inn=fin.inn
            Left Join dwh_kf_lsr lsr ON lsr.exp_code=fin.expensesnumeration and lsr.year=${import_year}
            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'
            WHERE UPPER(fin.fundkind) NOT LIKE '%ОСТАТКИ%' AND fin.FINYEAR = ${import_year}  AND org.role_code IN (5,3) --ГУП+Ленсвет
                AND ((fin.expensetypecode BETWEEN 200 AND 247 )
                      OR (fin.expensetypecode = 323 AND org.grbs_inn NOT IN ('7825675663','7840013199')) --комитет по социальной политике (+ жилищный комитет Белоусова А.А 21.12.2020)
                      OR ((fin.expensetypecode = 414 OR fin.expensetypecode = 412 OR fin.expensetypecode = 466) AND NVL(fin.kosgucode,lsr.kosgu) NOT IN (297,298,299,530)))
                AND targetexpenseitemcode IN ( SELECT targetexpenseitemcode FROM dwh_targetexpenseitemcode_aip)
                AND (
                            (fin.targetexpenseitemcode NOT IN ('0210020830','0210020850','0220020820','0220020840','0510060500') AND org.inn='7832000076')
                            OR
                            org.inn<>'7832000076'
                           )
                AND pro.pg_n IN (
                                        SELECT DISTINCT plannumber FROM dwh_tEnderplan_20_acgz
                                        WHERE customer_inn <> owner_inn --and owner_inn='7830000426'
                                )
                AND (fin.inn='7830000426' AND targetexpenseitemcode='0720080310' OR  fin.inn NOT IN '7830000426' )
            GROUP BY fin.lotuuid, fin.targetexpenseitemcode, fin.fundkind, fin.expensetypecode, NVL(fin.kosgucode,lsr.kosgu), fin.FUNDCODE, org.CONTRAGENT_ACCOUNT, 1
      )fin
Left Join DWH_PROCEDURES_KGNTV pro ON NVL(pro.lot_cons_m,pro.lotuuid)=fin.lotuuid AND (pro.lot_cons_m<>pro.lotuuid OR pro.lot_cons_m IS NULL)
                                      And pro.reqnum Not In (Select reqnum From wrong_purchase)
Left Join dwh_contract_kgntv con ON con.lotid=fin.lotuuid
Left Join dwh_targetexpenseitemcode_2020 t ON t.targetexpenseitemcode=fin.targetexpenseitemcode
Left Join dwh_organization_kgntv uo_org ON uo_org.id = pro.uo_orgid
Join dwh_organization_kgntv   org ON org.id=pro.orgid
Left Join dwh_procedures_cons_kgntv cons ON cons.lotuuid = pro.Joint_procedure_id
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
WHERE pro.reqnum IS NOT NULL
      AND con.contractrnk IS NULL
      AND (pro.final_decision <>'Отмена закупки' OR pro.final_decision IS NULL)
      AND pro.LOTSTAGE <> 'Процедура отменена'
            AND pro.lotstageid<>100
            AND pro.pg_rn LIKE '${import_year}%' --по запросу Яковлева А.И. 09 октября 2019
            AND PRO.first_fin_year=${import_year} --по запросу Яковлева А.И. 09 октября 2019
      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
                          );
--  Убираем закупки у которых нет протокола подведения итогов определения поставщика и прошло более 50 рабочих дней с момента публикации первой версии извещения 01.09.2022
DELETE FROM aip_zak_raz_detail_${import_year}
WHERE reqnum in
(SELECT reqnum FROM dwh_detailing_zak_general_${import_year} 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)