ksp

CREATE TABLE ksp_01_08_23 AS
SELECT
    fin.lot_id,
    c.contractrnk,-- 1 рнк
    CASE
        WHEN fin.TYPE=1 THEN fin.YEAR ELSE NULL END first_fin_cont_year,-- 2 год планового финансирования по контракту
    fin.finsource --4 источники финансирования
    ,CASE
        WHEN o_u.id IN (503,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 -- 5 уполномоченный орган
    ,COALESCE(cons.inn,o.inn) AS inn -- 6 ИНН организации разместившей извещение
    ,COALESCE(cons.orgtitle, o.full_name)AS c_org -- 7 наименование организации разместившей извещение
    ,c.customerinn -- 8 заказчик инн организации
    ,c.customername-- 9 заказчик наименование
    ,c.grbsname-- 10 ГРБС название
    ,COALESCE ( p.reqnum,c.contractnoticenumber)contractnoticenumber  -- 11 номер извещения
    ,p.date_notice_published -- 12 дата размещения извещения о закупке
    ,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 -- 13 способо закупки на этапе размещения извещения
    ,TRUNC(c.contractsigndate)contractsigndate -- 14 дата заключения контракта
    ,c.contractsubject -- 15 наименование объекта закупки
    ,CASE
        WHEN c.contractoneexecreason  LIKE 'Часть 1 пункт 25 статьи 93%' THEN 'Закупка у единственного поставщика'
        ELSE c.ordertypename
    END ordertypename -- 16 соп
    ,p.f_productprice -- 17 нмцк
    ,c.contractsigningprice -- 18 первоначальная цена
    ,c.contractfullprice -- 19 текущая цена
    ,c.contractplaneexecdate -- 20 планируемая дата окончания исполнения контракта
    ,c.contractactualexecdate -- 21 фактическая дата исполнения контракта
    ,c.supplierinn -- 22 поставщик инн
    ,c.suppliername -- 23 поставщик наименование
    ,c.contractoneexecreason -- 24 основание заключения контракта с ед поставщиком
    ,c.contractrejectdate -- 25 дата расторжения контракта
    ,c.contractrejectbase -- 26 основание расторжения контракта
    ,CASE
        WHEN p.placement ='Централизованная закупка' THEN 1
        ELSE 0
    END central -- 27 признак централизованной закупки
    ,p.joflag -- 28 признак совместных торгов
    ,c.supply_impossible -- 29 признак невозможно определить количество
    ,penalty.sum_ -- 30 сумма начисленных санкций
    ,penalty.pen_pay_sum_ -- 31 сумма оплаченных санкций
    ,CASE
        WHEN fin.TYPE=1 THEN fin.finsum
        ELSE NULL
    END finsum_plan -- 32 плановое финансирования по контракту
    ,CASE
        WHEN fin.TYPE=2 THEN fin.finsum
        ELSE NULL
    END finsum_fact -- 33 фактическое финансирования по контракту
    ,pea.nmck_type -- 34 метод определения и обоснования НМЦК
    ,c.perc_size -- 35 объем привлеченных к исполнению субподр %
    ,c.perc_rub_size -- 36 объем привлеченных к исполнению субподр руб
    ,p.ADVANCE_PAY_AMOUNT -- 37 размер аванса %
    ,p.advance_hand_amount -- 38 размер аванса руб
     FROM dwh_contract_kgntv c
Inner Join dwh_procedures_kgntv p ON p.lotuuid = c.lotid AND c.contractsigndate BETWEEN TO_DATE ('01.01.22', 'dd.mm.yy') AND TO_DATE ('30.06.23','dd.mm.yy')
Inner Join dwh_organization_kgntv o ON o.id = c.customerid
Inner Join dwh_contract_fin_kgntv 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 (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
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 DISTINCT
                    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
 ;