CREATE TABLE ksp_15_04_24_fin_new AS
WITH fin AS (
SELECT
  lot_id,
  fin.budget_type,
  fin.targetexpenseitemcode,
  cs_name.name target_name,
  MIN(CASE WHEN fin.TYPE=1 THEN fin.YEAR ELSE NULL END) first_year   -- первый год планового фин
 ,MAX(CASE WHEN fin.TYPE=1 THEN fin.YEAR ELSE NULL END)  last_year-- последний год планового финас
 ,SUM(CASE WHEN fin.TYPE=1 THEN fin.finsum ELSE NULL END) finsum_plan  -- 44 плановое финансирования по контракту
 ,SUM(CASE WHEN fin.TYPE=2 THEN fin.finsum ELSE NULL END) finsum_fact -- 45 фактическое финансирования по контракту
 ,MAX(CASE WHEN fin.targetexpenseitemcode IN (SELECT targetexpenseitemcode FROM dwh_targetexpenseitemcode_aip) THEN 1 ELSE 0 END) aip_flag -- 50 признак АИП (проверить)
FROM dwh_contract_fin_kgntv_temp fin
Left join dwh_kf_cs cs_name ON cs_name.code=fin.targetexpenseitemcode AND fin.YEAR=cs_name.YEAR
WHERE fin.Targetexpenseitemcode IN (SELECT DISTINCT targetexpenseitemcode FROM dwh_targetexpenseitemcode_2020)
AND fin.YEAR>=2023
GROUP BY lot_id,fin.budget_type, fin.targetexpenseitemcode,cs_name.name
)


SELECT
    c.lotid,
    c.contractrnk, -- 1 рнк
    p.f_year YEAR, -- 2 год бюджета, к которому относятся ассигнования (вместо года размещения процедуры (requestyear) берем год финансирования контракта.
    fin.first_year first_fin_cont_year,-- 3 год первого финансирования по контракту
    fin.last_year, -- год последнего финансирования контракта
    c.requestyear f_year -- 5 год плана размещения
    ,c.finsource --6 источники финансирования
    ,fin.budget_type -- 7 тип бюджетных средств
    ,fin.targetexpenseitemcode -- 8 код целевой статьи
    ,fin.target_name -- 9 наименование целевой статьи
    ,CASE
        WHEN o_u.id IN (503,592,1407,2710,2229,2703,1409,2709,508,1898,2702,988,2711,504,509,500,1897,1411,1408,1412,1728) THEN o_u.full_name
        ELSE NULL
    END uo_org -- 10 уполномоченный орган
   ,COALESCE(cons.inn,o_u.inn,c.customerinn) AS inn -- 11 ИНН организации разместившей извещение
   ,COALESCE(cons.orgtitle, o_u.full_name,c.customername)AS c_org -- 12 наименование организации разместившей извещение*/
   ,c.customerinn -- 13 заказчик инн организации
   ,c.customername-- 14 заказчик наименование
   ,c.grbsname-- 15 ГРБС название
   ,gr.fshorttitle -- 16 сокращенное наименование грбс
   ,COALESCE ( p.reqnum,c.contractnoticenumber)contractnoticenumber  -- 17 номер извещения
   ,p.date_notice_published -- 18 дата размещения извещения о закупке
   ,CASE
        WHEN  p.fordertypename = 'Закупка у единственного поставщика' AND p.oneexec = 'Часть 1 пункт 25 статьи 93 -  Закупка вследствие признания несостоявшимся запроса котировок в соответствии частями 1 и 3 статьи 79 Федерального закона решения об осуществлении закупки у единственного поставщика (подрядчика, исполнителя)'
                                THEN 'Запрос котировок в электронной форме'
        WHEN p.fordertypename = 'Закупка у единственного поставщика' AND p.oneexec = 'Часть 1 пункт 25 статьи 93 -  Закупка вследствие признания несостоявшимся двухэтапного конкурса в соответствии с частями 1 и 7 статьи 55 Федерального закона решения об осуществлении закупки у единственного поставщика (подрядчика, исполнителя)'
                THEN 'Двухэтапный конкурс в электронной форме'
        WHEN p.fordertypename = 'Закупка у единственного поставщика' AND p.oneexec = 'Часть 1 пункт 25 статьи 93 -  Закупка вследствие признания несостоявшимися открытого конкурса в соответствии с частями 1 и 7 статьи 55 Федерального закона решения об осуществлении закупки у единственного поставщика (подрядчика, исполнителя)'
                THEN 'Открытый конкурс в электронной форме'
        WHEN p.fordertypename = 'Закупка у единственного поставщика' AND p.oneexec = 'Часть 1 пункт 25 статьи 93 -  Закупка вследствие признания несостоявшимся электронного аукциона в соответствии частями 1 – 3.1 статьи 71 Федерального закона решения об осуществлении закупки у единственного поставщика (подрядчика, исполнителя)'
                THEN 'Аукцион в электронной форме'
        WHEN p.fordertypename = 'Закупка у единственного поставщика' AND p.oneexec = 'Часть 1 пункт 25 статьи 93 -  Закупка вследствие признания несостоявшимися конкурса с ограниченным участием в соответствии с частями 1 и 7 статьи 55 Федерального закона решения об осуществлении закупки у единственного поставщика (подрядчика, исполнителя)'
                THEN 'Конкурс с ограниченным участием в электронной форме'
        WHEN p.fordertypename = 'Закупка у единственного поставщика' AND p.oneexec = 'Часть 1 пункт 25 статьи 93 -  Закупка вследствие признания несостоявшимся запроса предложений в соответствии с частью 18 статьи 83 Федерального закона решения об осуществлении закупки у единственного поставщика (подрядчика, исполнителя)'
                THEN 'Запрос предложений в электронной форме'
        ELSE p.fordertypename
    END   fordertypename -- 19 способо закупки на этапе размещения извещения
   ,c.contractsigndate -- 20 дата заключения контракта
   ,c.contractsubject -- 21 наименование объекта закупки
   ,c.contractrejectdate -- 22 дата расторжения контракта
   ,CASE WHEN c.contractoneexecreason  LIKE 'Часть 1 пункт 25 статьи 93%' THEN 'Закупка у единственного поставщика' ELSE c.ordertypename END ordertypename -- 23 соп
   ,p.f_productprice -- 24 нмцк
   ,c.contractsigningprice -- 25 первоначальная цена
   ,c.contractfullprice -- 26 текущая цена
   ,c.contractplaneexecdate -- 27 планируемая дата окончания исполнения контракта
   ,c.contractactualexecdate -- 28 фактическая дата исполнения контракта
   ,c.supplierinn -- 29 поставщик инн
   ,c.suppliername -- 30 поставщик наименование
   ,CASE WHEN c.contractoneexecreason IS NOT NULL THEN 1 ELSE 0 END onneexec_type-- 31 признак заключения контракта с единственным поставщиком
   ,c.contractoneexecreason -- 32 основание заключения контракта с ед поставщиком
   ,c.stagetitle -- 33 статус контракта
   ,c.contractrejectreason -- 34 основание расторжения контракта
   ,CASE WHEN p.placement ='Централизованная закупка' THEN 1 ELSE 0 END central -- 35 признак централизованной закупки
   ,p.joflag -- 36 признак совместных торгов
   ,c.supply_impossible -- 37 признак невозможно определить количество
   ,c.SUPPLIERCRIMINAL -- 38 учреждение уголовно-испытательной системы
   ,c.SUPPLIERINVALID -- 39 учреждение инвалидов
   ,c.SUPPLIERSMP -- 40 ПОСТАВЩИК ЯВЛЯЕТСЯ СМП ИЛИ СОНКО
   ,c.SUPPLIERSMPSONCO -- 41 СОНКО
   ,CASE WHEN penalty.sum_ IS NOT NULL THEN 1 ELSE 0 END penaty_flag-- 42 признак применения штрафных санкций
   ,penalty.sum_ -- 43 сумма начисленных санкций
   ,penalty.pen_pay_sum_ -- 44 сумма оплаченных санкций
    ,fin.finsum_plan -- 45 плановое финансирования по контракту
    ,fin.finsum_fact -- 46 фактическое финансирования по контракту
   , CASE WHEN c.is_concluded_in_e_shop IS NOT NULL THEN 1 ELSE 0 END eshop_flag-- 47 признак заключения контракта в ЭМ
   ,c.contract_modification_reasons   -- 48 причина изменения усвловий контракта/договора
   ,pea.nmck_type -- 49 метод определения и обоснования НМЦК
    ,fin.aip_flag -- 50 признак АИП (проверить)
    ,CASE WHEN c.perc_size IS NOT NULL OR c.perc_rub_size IS NOT NULL THEN 1 ELSE 0 END subpodr_flag -- 51 признак наличия субподрпяда
    ,c.perc_size -- 52 объем привлеченных к исполнению субподр %
    ,c.perc_rub_size -- 53 объем привлеченных к исполнению субподр руб
    ,p.ADVANCE_PAY_AMOUNT -- 54 размер аванса %
    ,p.advance_hand_amount -- 55 размер аванса руб
FROM dwh_contract_kgntv c
join dwh_procedures_kgntv p ON c.lotid=p.lotuuid
JOIN fin ON c.lotid=fin.lot_id
LEFT JOIN dwh_procedures_cons_kgntv cons ON cons.lotuuid = p.Joint_procedure_id
LEFT JOIN dwh_organization_kgntv o_u    ON o_u.id = p.uo_orgid
LEFT JOIN ksp_grbs_shotname gr  ON gr.KGNTVID = c.grbsid
LEFT join(SELECT
            contractid,
            listagg(list_, '; ') within GROUP (ORDER BY contractid) pen,
            SUM(NVL(amount,0))sum_
            ,SUM(NVL(penalty_pay_sum,0)) pen_pay_sum_
          FROM (SELECT
                     id contractid,
                    CASE
                        WHEN reason_id IN (1,3) THEN 'Исполнитель'  WHEN reason_id IN (2,4) THEN 'Заказчик'
                        ELSE NULL
                    END  list_
                    ,amount
                    ,penalty_pay_sum
                FROM dwh_contract_penalty_kgntv
                )GROUP BY contractid
        )penalty ON penalty.contractid = c.contractid
LEFT JOIN (
SELECT
COALESCE(l.lot_id,sovm.lot_id) lot_id,
COALESCE(l.plnm_name,l.plnm_name2,sovm.plnm_name) nmck_type
FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
LEFT JOIN (SELECT
            m.plnm_name,
            COALESCE(l.lot_id,m.lot_id,TO_NUMBER(p.lotuuid)) lot_id,
            COALESCE(p.MORD_FLAG,m.MORD_FLAG)
            FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
            LEFT JOIN dwh_procedures_kgntv p ON p.lotuuid = l.lot_id AND p.MORD_FLAG = 'D' --
            LEFT JOIN
               (SELECT
                COALESCE(gm.plnm_name,gm.plnm_name2) plnm_name,
                COALESCE(l.lot_id,gm.lot_id,TO_NUMBER(p.lotuuid)) lot_id, --p.lotuuid
                COALESCE(p.MORD_FLAG,gm.MORD_FLAG) MORD_FLAG
                FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
                    LEFT JOIN
                        (SELECT l.*,MORD_FLAG
                         FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
                            LEFT JOIN dwh_procedures_kgntv p ON p.lotuuid = l.lot_id
                         WHERE MORD_FLAG IS NULL
                        ) gm ON gm.lot_id = l.joint_procedure_id
                    LEFT JOIN dwh_procedures_kgntv p ON p.lotuuid = l.lot_id AND p.MORD_FLAG = 'M'
                    WHERE
                    l.joint_procedure_id IS NOT NULL
                  )m ON m.lot_id = p.LOT_CONS_M) sovm ON sovm.lot_id = l.lot_id) pea ON pea.lot_id = p.lotuuid
;