API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 26th, 2020
118
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 130.69 KB | None | 0 0
raw download clone embed print report
  1.  
  2. drop table dwh_tenderPlan_19_opn_d;
  3. create table dwh_tenderPlan_19_opn_d as
  4. select * from dwh_tenderPlan_19_opn_d_v;
  5.  
  6. drop table dwh_tenderPlan_19_pos_opn_d;
  7. create table dwh_tenderPlan_19_pos_opn_d as
  8. select * from dwh_tenderplan_19_pos_opn_d_v;
  9.  
  10. --1 шаг генерации рейтинга эффективности
  11. ---ERC_19_SCHEDULE---DWH_TENDERPLAN_19_OPN_D DWH_TENDERPLAN_19_POS_OPN_D
  12. --drop table ERC_19_SCHEDULE;
  13. --create table ERC_19_SCHEDULE as
  14. select org_name, org_inn, org_spz,org_kgntv, plannumber, versionnumber, publishdate
  15. from (select
  16. sh.customer_fullname org_name,
  17. sh.customer_inn org_inn,
  18. sh.customer_regnum org_spz,
  19. org.id org_kgntv,
  20. sh.plannumber,
  21. sh.versionnumber,
  22. sh.publishdate,
  23. row_number() over (partition by sh.customer_regnum order by to_number(sh.versionnumber) desc, sh.publishdate desc) rn
  24. from dwh_tenderPlan_19_pos_opn_d sh
  25. join erc_dwh_organization_kgntv org on sh.customer_regnum = org.spz
  26. where sh.year = 2019
  27. and nvl(placingway_code,0) <>all('ZK44','ZKK44') --выключить все бумажные
  28. --and placingway_code <>all('ZK504','ZP504','OK504','OKU504')--не учитывать Электронные процедуры (открытый конкурс в электронной форме, конкурс с ограниченным участием в электронной форме, двухэтапный конкурс в электронной форме, запрос котировок в электронной форме, запрос предложений в электронной форме)
  29. and sh.publishdate< to_date('01.01.2020','DD.MM.YYYY')
  30. and org.id not in (3039,2913,31344,2998,3020,2901,3024,2994,7817,3774,29714, /*проверять новые ГУПЫ перед генерацией*/ /*select distinct org.full_name, org.id from dwh_tenderPlan_19_pos_opn_d sh join erc_dwh_organization_kgntv org on sh.customer_regnum = org.spz where lower(org.full_name) like '%гуп%' or lower(org.full_name) like '%унитар%';*/ --Прошу исключить 4 УМВД у администраций Красносельского, Кронштадтского, Петроградского, Петродворцового районов. Исключить Автономное учреждение Поликлиника № 40.
  31. 3127,3128,3132,3133,2147,3011,1556 )--Прошу исключить 4 УМВД у администраций Красносельского, Кронштадтского, Петроградского, Петродворцового районов. Исключить Автономное учреждение Поликлиника № 40. Детский Авангард
  32. and sh.plannumber not in ( '2019017220000180070001','2019017220000180030001','2019017220000180010001') /*select distinct org_name, plannumber, org_name_g from kris_19_list where org_type_ = 2;*/ -- решить вопрос с правилом запроса
  33. --по переданным полномочиям не учитываем 23.10.2019
  34. -- and org.inn not in (select * from inn0108)
  35. --or sh.plannumber='2019037220027060010001'
  36. )plan_
  37. where plan_.rn = 1;
  38.  
  39. -----
  40. 2019017220000180060001-План График самого КОМИТЕТ ПО ЭНЕРГЕТИКЕ И ИНЖЕНЕРНОМУ ОБЕСПЕЧЕНИЮ ( без передачи полномочий)
  41. 2019017220000180030001 -переданы полномочия ГОСУДАРСТВЕННОЕ УНИТАРНОЕ ПРЕДПРИЯТИЕ "ТОПЛИВНО-ЭНЕРГЕТИЧЕСКИЙ КОМПЛЕКС САНКТ-ПЕТЕРБУРГА"
  42. 2019017220000180070001 -переданы полномочия САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ УНИТАРНОЕ ПРЕДПРИЯТИЕ "ЛЕНСВЕТ"
  43. 2019017220000180010001 -переданы полномочия ГОСУДАРСТВЕННОЕ УНИТАРНОЕ ПРЕДПРИЯТИЕ "ВОДОКАНАЛ САНКТ-ПЕТЕРБУРГА"
  44.  
  45.  
  46. 3039- id Комитета по развитию предпринимательства и потребительского рынка Санкт-Петербурга ( В связи с ликвидацией с 01.08.2019 исключен из расчета по письму от Тереховой 07.02.2020)
  47.  
  48.  
  49.  
  50. --drop table ERC_19_SCHEDULE_POS_modif;
  51. --create table ERC_19_SCHEDULE_POS_modif as
  52. with notice as
  53. (
  54. select p.ikz, p.versionnumber, p.placingway_name, p.plan_placement_date, p.purchasecanceled, rn
  55. from
  56. (select
  57. ikz,
  58. to_number(versionnumber) versionnumber,
  59. placingway_name,
  60. case when type_position = 'specialPurchase' then null
  61. else to_date('01'||case when length(pos.placing_month) = 1 then '0'||pos.placing_month else pos.placing_month end||'.'||pos.placing_year,'DD.MM.YYYY')
  62. end plan_placement_date,
  63. case when purchasecanceled is null then 0 else 1 end purchasecanceled,
  64. row_number() over (partition by ikz order by to_number(versionnumber)) rn
  65. from dwh_tenderPlan_19_pos_opn_d pos
  66. where pos.year = 2019 and pos.publishdate< to_date('01.01.2020','DD.MM.YYYY')
  67. --and placingway_code <>all('ZK504','ZP504','OK504','OKU504','ZK504','ZP504','OK504','OKU504')
  68. and type_position <> 'specialPurchase'
  69. )p
  70. )
  71. select n_f.ikz, n_f.versionnumber ver_from,n_s.versionnumber ver_to,
  72. case when n_f.placingway_name <> n_s.placingway_name then 1
  73. when n_f.plan_placement_date <> n_s.plan_placement_date then 1
  74. when n_f.purchasecanceled <> n_s.purchasecanceled then 1
  75. else 0 end modiff_all,
  76. case when n_f.placingway_name <> n_s.placingway_name then 1
  77. else 0 end modiff_sop,
  78. case when n_f.plan_placement_date <> n_s.plan_placement_date then 1
  79. else 0 end modiff_plan_date,
  80. case when n_f.purchasecanceled <> n_s.purchasecanceled then 1
  81. else 0 end modiff_canceled
  82. from notice n_f
  83. join notice n_s on n_f.ikz = n_s.ikz and n_f.rn+1 = n_s.rn;
  84.  
  85. --2 шаг генерации рейтинга эффективности
  86. ---ERC_19_SCHEDULE_POS---
  87. --drop table ERC_19_SCHEDULE_POS;
  88. create table ERC_19_SCHEDULE_POS as
  89. select ORG_NAME,ORG_INN,ORG_SPZ,ORG_KGNTV,PLANNUMBER,VERSIONNUMBER,PUBLISHDATE,flah_act_version,TYPE_POSITION,POSITIONNUMBER,IKZ,IKZ_PP,PLAN_PLACEMENT_DATE,PLAN_EXECUTION_DATE,
  90. PURCHASEOBJECTINFO,PLACINGWAY_CODE,PLACINGWAY_NAME,FINANCE_TOTAL,PURCHASECANCELED,SPECIALPURCHASE_TYPE,FLAG_COMP,MODIFF_ALL
  91. from
  92. (select plan_.org_name, plan_.org_inn, plan_.org_spz,plan_.org_kgntv, plan_.plannumber,
  93. pos.versionnumber , pos.publishdate,
  94. case when plan_.versionnumber = pos.versionnumber and plan_.publishdate =pos.publishdate
  95. then 1 else 0 end flah_act_version, --plan_.versionnumber, plan_.publishdate,
  96. pos.type_position,
  97. pos.positionnumber,
  98. pos.ikz,
  99. pos.purchase_purchaseplanikz ikz_pp,
  100. case when type_position = 'specialPurchase' then null
  101. else to_date('01'||case when length(pos.placing_month) = 1 then '0'||pos.placing_month else pos.placing_month end||'.'||pos.placing_year,'DD.MM.YYYY') end plan_placement_date,
  102. case when type_position = 'specialPurchase' then null
  103. else to_date('01'||case when length(pos.execution_month) = 1 then '0'||pos.execution_month else pos.execution_month end||'.'||pos.execution_year,'DD.MM.YYYY') end plan_execution_date,
  104. pos.purchaseobjectinfo ,
  105. case when pos.placingway_code is null then 'EP44_Sp' else pos.placingway_code end placingway_code,
  106. case when pos.placingway_name is null then 'Особая закупка' else pos.placingway_name end placingway_name,
  107. to_number(replace (pos.finance_total,'.',','))finance_total,
  108. pos.purchasecanceled,
  109. pos.specialpurchase_type,
  110. case when pos.placingway_name not in ('Закупка у единственного поставщика (подрядчика, исполнителя)') then 1 else 0 end flag_comp,
  111. nvl(modiff_all,0) modiff_all,
  112. row_number() over (partition by pos.ikz order by to_number(pos.versionnumber) desc, pos.publishdate desc) rn
  113. from dwh_tenderPlan_19_pos_opn_d pos
  114. join erc_19_schedule plan_
  115. on plan_.org_name =pos.customer_fullname
  116. and plan_.org_inn =pos.customer_inn
  117. and plan_.org_spz =pos.customer_regnum
  118. and plan_.plannumber =pos.plannumber --and plan_.versionnumber =pos.versionnumber --and plan_.publishdate =pos.publishdate
  119. left join (select ikz, sum(modiff_all) modiff_all from ERC_19_SCHEDULE_POS_modif group by ikz)m on m.ikz = pos.ikz
  120. where pos.publishdate< to_date('01.01.2020','DD.MM.YYYY')
  121. /*where (pos.placingway_code not in ('ZA44','ZKKD44','ZKKU44','ZKB44','INM111') or pos.placingway_code is null)
  122. and (case when type_position = 'specialPurchase' then null
  123. else to_date('01'||case when length(pos.placing_month) = 1 then '0'||pos.placing_month else pos.placing_month end||'.'||pos.placing_year,'DD.MM.YYYY') end < to_date('01012020','DDMMYYYY')
  124. or type_position = 'specialPurchase')*/
  125.  
  126. )p
  127. where p.rn = 1
  128. and p.flah_act_version = 1
  129. and p.placingway_code not in ('ZA44','ZKKD44','ZKKU44','ZKB44','INM111','ZKK44')
  130. and (p.plan_placement_date < to_date('01012020','DDMMYYYY') or type_position = 'specialPurchase');
  131.  
  132.  
  133.  
  134. --2 шаг генерации рейтинга эффективности
  135. --3 шаг генерации рейтинга эффективности
  136. --ERC_START_NOTICE
  137. --*совместную закупку 0172200002517000165 учесть УО - не КГЗ, а Курортному району. Проверить наличие аналогов в 1 квартале*/
  138. --0172200002518000037
  139. /*
  140. select purchasenumber, max(docpublishdate), min(docpublishdate)
  141. from DWH_START_NOTICE_nrpz_cons
  142. where
  143. responsibleRole in ('OAU','OCC','OCS','OOA','OAI','ORA','OCU')
  144. and purchasecode is not null
  145. --and purchasenumber = '0172200002517000165'
  146. and regnum = '01722000025'
  147. and docpublishdate>=to_date('01012020','DDMMYYYY')
  148. group by purchasenumber;
  149.  
  150. select * from dwh_procedures_cons_kgntv
  151. where reqnum in ('0172200002518000097','0172200002518000037');
  152.  
  153. select lc.actual, lc.lot_id, c.id, c.inn, c.full_name, c.oos_id
  154. from lot_customers lc
  155. join contragents c on c.id = lc.customer_id
  156. where lot_id in (1865554,1859552);
  157. */
  158. --drop table ERC_19_START_NOTICE;
  159. CREATE table ERC_19_START_NOTICE as
  160. select s.org_kgntv,
  161. s.org_kgntv_SCHEDULE,
  162. s.org_kgntv_NOTICE,
  163. case when st_hand.reqnum is not null then st_hand.org_kgntv_joflag else s.org_kgntv_joflag end org_kgntv_joflag,
  164. s.reqnum,
  165. s.PUBLISHDATE,
  166. s.sop_code,
  167. s.sop_name,
  168. s.org_spz,
  169. s.org_name,
  170. --s.uo_kgz,
  171. case when st_hand.reqnum is not null then st_hand.joflag_org_name else s.joflag_org_name end joflag_org_name,
  172. case when st_hand.reqnum is not null then st_hand.joflag_org_spz else s.joflag_org_spz end joflag_org_spz,
  173. s.joflag,
  174. s.STARTDATE,
  175. s.ENDDATE,
  176. s.MAXPRICE,
  177. s.MAXPRICE_all,
  178. s.CURRENCY,
  179. s.BIDDINGDATE,--Дата и время проведения аукциона в электронной форме
  180. s.OPENINGDATE,--Дата и время вскрытия конвертов, открытии доступа к электронным документам заявок участников
  181. s.SCORINGDATE,--Дата рассмотрения первых частей заявок учасников или Дата рассмотрения и оценки заявок на участие в конкурсе
  182. s.PREQUALIFICATION,--Дата и время предквалификационного отбора
  183. s.LOTNUMBER,
  184. s.pg_pos,
  185. s.flag_comp,
  186. s.ikz,
  187. s.pg,
  188. s.object_name,
  189. s.flag_cans,
  190. GREATEST(gr.cnt - 1, 0) cnt_modif,
  191. gr.date_max,
  192. case when st_hand.reqnum is not null then 1 else s.uo_kgz end flag_st_hand,
  193. row_number() over (partition by s.ikz,s.org_kgntv order by s.flag_cans, case when c.purchasenumber is not null then 1 else 0 end desc,case when p.purchasenumber is not null then 1 else 0 end desc,
  194. s.publishdate desc) purchasenumber_rn
  195. from
  196. (select sch.org_kgntv org_kgntv_SCHEDULE,
  197. org.id org_kgntv_NOTICE,
  198. org_joflag.id org_kgntv_joflag,
  199. nvl(org.id,sch.org_kgntv ) org_kgntv,
  200. st.PURCHASENUMBER reqnum,
  201. st.DOCPUBLISHDATE PUBLISHDATE,
  202. case when responsibleRole in ('OAU','OCC','OCS','OOA','OAI','ORA','OCU','RA') then 1 else 0 end joflag,
  203. case when responsibleRole in ('OAU','OCC','OCS','OOA','OAI','ORA','OCU','RA') then st.REGNUM else null end joflag_org_spz,
  204. case when responsibleRole in ('OAU','OCC','OCS','OOA','OAI','ORA','OCU','RA') then st.FULLNAME else null end joflag_org_name,
  205. case when responsibleRole in ('OAU','OCC','OCS','OOA','OAI','ORA','OCU','RA') then st.customer_regNum else st.REGNUM end org_spz,
  206. case when responsibleRole in ('OAU','OCC','OCS','OOA','OAI','ORA','OCU','RA') then st.customer_FULLNAME else st.FULLNAME end org_name,
  207. st.CODEPLACINGWAY sop_code,
  208. st.NAMEPLACINGWAY sop_name,
  209. case when st.NAMEPLACINGWAY not in ('Закупка у единственного поставщика (подрядчика, исполнителя)') then 1 else 0 end flag_comp,
  210. st.STARTDATE,--Дата и время начала подачи заявок
  211. st.ENDDATE,--Дата и время окончания подачи заявок
  212. st.MAXPRICE,
  213. st.MAXPRICE_all,
  214. st.CURRENCY,
  215. st.BIDDINGDATE,--Дата и время проведения аукциона в электронной форме
  216. st.OPENINGDATE,--Дата и время вскрытия конвертов, открытии доступа к электронным документам заявок участников
  217. st.SCORINGDATE,--Дата рассмотрения первых частей заявок учасников или Дата рассмотрения и оценки заявок на участие в конкурсе
  218. st.PREQUALIFICATION,--Дата и время предквалификационного отбора
  219. nvl(st.LOTNUMBER,1) LOTNUMBER,
  220. st.POSITIONNUMBER pg_pos,
  221. st.PURCHASECODE ikz,
  222. st.TENDERPLAN pg,
  223. st.PURCHASEOBJECTINFO object_name,
  224. case when canc.purchasenumber is not null then 1 else 0 end flag_cans,
  225. case when responsibleRole in ('RA','ORA')and st.REGNUM = '01722000025'/*КГЗ*/ then 1 else null end uo_kgz,
  226. row_number() over
  227. (partition by
  228. st.PURCHASENUMBER,
  229. nvl(st.LOTNUMBER,1),
  230. case when responsibleRole in ('OAU','OCC','RA','OCS','OOA','OAI','ORA','OCU') then st.customer_regNum else st.REGNUM end -- берётся не СПЗ организатора а СПЗ участника
  231. order by st.DOCPUBLISHDATE desc) rn
  232. from DWH_START_NOTICE_nrpz_cons st
  233. left join erc_dwh_organization_kgntv org on case when responsibleRole in ('OAU','OCC','OCS','OOA','OAI','ORA','OCU','RA') then st.customer_regNum else st.REGNUM end = org.spz --07.11.2019 добавлено 'RA'
  234. left join erc_dwh_organization_kgntv org_joflag on case when responsibleRole in ('OAU','OCC','OCS','OOA','OAI','ORA','OCU','RA') then st.REGNUM else null end = org_joflag.spz --07.11.2019 добавлено 'RA'
  235. join (select distinct ikz,ikz_pp, org_kgntv from ERC_19_SCHEDULE_POS /*where purchasecanceled is null*/ /*убрали 27/07/2017*/) sch on sch.ikz = st.purchasecode or sch.ikz_pp = st.purchasecode
  236. left join
  237. (select distinct purchasenumber
  238. from DWH_START_NOTICE_canc_nrpz
  239. )canc on canc.purchasenumber = st.PURCHASENUMBER
  240. where /*canc.purchasenumber is null *//*and st.purchasecode like '19%' and*/ st.DOCPUBLISHDATE <to_date('01012020','DDMMYYYY')
  241. ) s
  242. join
  243. (
  244. select purchasenumber reqnum,
  245. count ( distinct trunc(docpublishdate) ) cnt,
  246. max(docpublishdate) date_max
  247. from DWH_START_NOTICE_nrpz_cons
  248. where /*purchasecode like '19%' and*/ docpublishdate <to_date('01012020','DDMMYYYY')
  249. group by purchasenumber
  250. ) gr on gr.reqnum = s.reqnum
  251. left join
  252. ( select '0172200002518000037' reqnum, 2618 org_kgntv_joflag, 'ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБЩЕОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ШКОЛА- ИНТЕРНАТ №16 ПУШКИНСКОГО РАЙОНА САНКТ-ПЕТЕРБУРГА' joflag_org_name, '03722001835'joflag_org_spz from dual
  253. union all
  254. select '0172200002519000147',1846,'САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ "ГОРОДСКАЯ БОЛЬНИЦА № 40 КУРОРТНОГО РАЙОНА"','01722000025' from dual
  255. union all
  256. select '0172200002519000148',1412,'САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ "ГОРОДСКАЯ БОЛЬНИЦА СВЯТОГО ВЕЛИКОМУЧЕНИКА ГЕОРГИЯ"','01722000025' from dual
  257. union all
  258. select '0172200002519000149',1412,'КОМИТЕТ ПО ГОСУДАРСТВЕННОМУ ЗАКАЗУ САНКТ-ПЕТЕРБУРГА','01722000025' from dual
  259. union all
  260. select '0172200002519000261',1412,'КОМИТЕТ ПО ГОСУДАРСТВЕННОМУ ЗАКАЗУ САНКТ-ПЕТЕРБУРГА','01722000025' from dual
  261. union all
  262. select '0372200000119000166',1769,'САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ "ГОРОДСКАЯ МАРИИНСКАЯ БОЛЬНИЦА','03722000001' from dual
  263. union all
  264. select '0372200037619000099',1292,'САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ "РОДИЛЬНЫЙ ДОМ №17','03722000376' from dual
  265. union all
  266. select '0372200037619000112',1292,'САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ "РОДИЛЬНЫЙ ДОМ №17','03722000376' from dual
  267. union all
  268. select '0372200075119000101',933,'САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ "ГОРОДСКАЯ СТОМАТОЛОГИЧЕСКАЯ ПОЛИКЛИНИКА № 33','03722000751' from dual
  269. union all
  270. select '0372200075119000093',933,'САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ "ГОРОДСКАЯ СТОМАТОЛОГИЧЕСКАЯ ПОЛИКЛИНИКА № 33','03722000751' from dual
  271. union all
  272. select '0372200168119000307',345,'САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ "КЛИНИЧЕСКАЯ ИНФЕКЦИОННАЯ БОЛЬНИЦА ИМ. С.П. БОТКИНА','03722001681' from dual
  273. union all
  274. select '0372200257619000068',1491,'САНКТ-ПЕТЕРБУРГСКОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ЗДРАВООХРАНЕНИЯ "БЮРО СУДЕБНО-МЕДИЦИНСКОЙ ЭКСПЕРТИЗЫ','03722002576' from dual
  275. )st_hand on st_hand.reqnum = s.reqnum
  276. left join (select distinct purchasenumber from DWH_PROTOCOL_nrpz) p on p.purchasenumber = s.reqnum
  277. left join (select distinct NOTIFICATIONNUMBER purchasenumber from dwh_contract_notice_nrpz) c on c.purchasenumber = s.reqnum
  278. where s.rn = 1;
  279.  
  280. --delete from erc_19_start_notice where ikz= '172780514677478050100100190018425244' and org_kgntv = 2590;
  281. --3 шаг генерации рейтинга эффективности
  282.  
  283. --4 шаг генерации рейтинга эффективности
  284. --ERC_19_LIST_NOTI
  285. --drop table erc_19_list;
  286. create table erc_19_list as
  287. select
  288. sch.ORG_NAME,
  289. sch.ORG_INN,
  290. sch.ORG_SPZ,
  291. sch.ORG_KGNTV,
  292. sch.PLANNUMBER,
  293. sch.VERSIONNUMBER,
  294. sch.PUBLISHDATE,
  295. sch.POSITIONNUMBER,
  296. sch.IKZ,
  297. sch.IKZ_PP,
  298. sch.PLAN_PLACEMENT_DATE,
  299. sch.PLAN_EXECUTION_DATE,
  300. sch.PURCHASEOBJECTINFO OBJECT_NAME,
  301. sch.PLACINGWAY_CODE SOP_CODE,
  302. sch.PLACINGWAY_NAME SOP_NAME,
  303. sch.FINANCE_TOTAL nmc_SCHEDULE,
  304. sch.PURCHASECANCELED,
  305. sch.SPECIALPURCHASE_TYPE,
  306. sch.FLAG_COMP,
  307. sch.MODIFF_ALL CNT_MODIF_PG,
  308. sch.flah_act_version,
  309. st.REQNUM,
  310. st.PUBLISHDATE PUBLISHDATE_REQNUM,
  311. st.SOP_CODE SOP_CODE_REQNUM,
  312. st.SOP_NAME SOP_NAME_REQNUM,
  313. st.JOFLAG_ORG_NAME,
  314. st.JOFLAG_ORG_SPZ,
  315. st.ORG_KGNTV_JOFLAG,
  316. st.JOFLAG,
  317. st.STARTDATE,
  318. st.ENDDATE,
  319. st.MAXPRICE nmc_REQNUM,
  320. st.MAXPRICE_ALL nmc_joflag,
  321. st.BIDDINGDATE,
  322. st.OPENINGDATE,
  323. st.SCORINGDATE,
  324. st.PREQUALIFICATION,
  325. st.LOTNUMBER,
  326. st.PG_POS POSITIONNUMBER_REQNUM,
  327. st.FLAG_COMP FLAG_COMP_REQNUM,
  328. st.IKZ IKZ_REQNUM,
  329. st.PG PLANNUMBER_REQNUM,
  330. st.OBJECT_NAME OBJECT_NAME_REQNUM,
  331. st.CNT_MODIF CNT_MODIF_REQNUM,
  332. st.flag_cans flag_cans_REQNUM,
  333. st.purchasenumber_rn,
  334. st.flag_st_hand
  335. from ERC_19_SCHEDULE_POS sch
  336. /*left*/ join ERC_19_START_NOTICE st on (sch.ikz = st.ikz/* or sch.ikz_pp = st.ikz*/)
  337. union all
  338. select
  339. sch.ORG_NAME,
  340. sch.ORG_INN,
  341. sch.ORG_SPZ,
  342. sch.ORG_KGNTV,
  343. sch.PLANNUMBER,
  344. sch.VERSIONNUMBER,
  345. sch.PUBLISHDATE,
  346. sch.POSITIONNUMBER,
  347. sch.IKZ,
  348. sch.IKZ_PP,
  349. sch.PLAN_PLACEMENT_DATE,
  350. sch.PLAN_EXECUTION_DATE,
  351. sch.PURCHASEOBJECTINFO OBJECT_NAME,
  352. sch.PLACINGWAY_CODE SOP_CODE,
  353. sch.PLACINGWAY_NAME SOP_NAME,
  354. sch.FINANCE_TOTAL nmc_SCHEDULE,
  355. sch.PURCHASECANCELED,
  356. sch.SPECIALPURCHASE_TYPE,
  357. sch.FLAG_COMP,
  358. sch.MODIFF_ALL CNT_MODIF_PG,
  359. sch.flah_act_version,
  360. st.REQNUM,
  361. st.PUBLISHDATE PUBLISHDATE_REQNUM,
  362. st.SOP_CODE SOP_CODE_REQNUM,
  363. st.SOP_NAME SOP_NAME_REQNUM,
  364. st.JOFLAG_ORG_NAME,
  365. st.JOFLAG_ORG_SPZ,
  366. st.ORG_KGNTV_JOFLAG,
  367. st.JOFLAG,
  368. st.STARTDATE, st.ENDDATE,
  369. st.MAXPRICE nmc_REQNUM,
  370. st.MAXPRICE_ALL nmc_joflag,
  371. st.BIDDINGDATE,
  372. st.OPENINGDATE,
  373. st.SCORINGDATE,
  374. st.PREQUALIFICATION,
  375. st.LOTNUMBER,
  376. st.PG_POS POSITIONNUMBER_REQNUM,
  377. st.FLAG_COMP FLAG_COMP_REQNUM,
  378. st.IKZ IKZ_REQNUM,
  379. st.PG PLANNUMBER_REQNUM,
  380. st.OBJECT_NAME OBJECT_NAME_REQNUM,
  381. st.CNT_MODIF CNT_MODIF_REQNUM,
  382. st.flag_cans flag_cans_REQNUM,
  383. st.purchasenumber_rn,
  384. st.flag_st_hand
  385. from ERC_19_SCHEDULE_POS sch
  386. /*left */join ERC_19_START_NOTICE st on (/*sch.ikz = st.ikz or */sch.ikz_pp = st.ikz and sch.ikz_pp <> sch.ikz)
  387. --where not (sch.PLANNUMBER = 2017037220021190010001 and sch.flah_act_version = 0 and sch.ikz_pp = '172781344635578130100100090008110244')
  388. union all
  389. select
  390. sch.ORG_NAME,
  391. sch.ORG_INN,
  392. sch.ORG_SPZ,
  393. sch.ORG_KGNTV,
  394. sch.PLANNUMBER,
  395. sch.VERSIONNUMBER,
  396. sch.PUBLISHDATE,
  397. sch.POSITIONNUMBER,
  398. sch.IKZ,
  399. sch.IKZ_PP,
  400. sch.PLAN_PLACEMENT_DATE,
  401. sch.PLAN_EXECUTION_DATE,
  402. sch.PURCHASEOBJECTINFO OBJECT_NAME,
  403. sch.PLACINGWAY_CODE SOP_CODE,
  404. sch.PLACINGWAY_NAME SOP_NAME,
  405. sch.FINANCE_TOTAL nmc_SCHEDULE,
  406. sch.PURCHASECANCELED,
  407. sch.SPECIALPURCHASE_TYPE,
  408. sch.FLAG_COMP,
  409. sch.MODIFF_ALL CNT_MODIF_PG,
  410. sch.flah_act_version,
  411. null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
  412. from ERC_19_SCHEDULE_POS sch
  413. left join (select distinct ikz from ERC_19_START_NOTICE)st on st.ikz = sch.IKZ
  414. left join (select distinct ikz from ERC_19_START_NOTICE)st_p on st_p.ikz = sch.IKZ_PP
  415. where st.ikz is null and st_p.ikz is null and sch.flah_act_version = 1
  416. --4 шаг генерации рейтинга эффективности
  417.  
  418. --5 шаг генерации рейтинга эффективности
  419. --ERC_19_CONTRACT_MESS
  420. -- ITOGOVAIA TABLICA !!!!!
  421. CREATE table ERC_19_CONTRACT_MESS as
  422. select cc.id,
  423. cc.contract_rnk,
  424. min(o.last_attempt) date_,
  425. min(case when o.success = true then o.last_attempt end) successdate
  426. from oos_messages o
  427. inner join cm_contracts cc on cc.lot_id = o.lot_id and cc.id = o.contract_id
  428. where o.type = 'contract' and cc.import_uuid is null --and cc.id>699751
  429. group by cc.id, cc.contract_rnk
  430. --5 шаг генерации рейтинга эффективности
  431.  
  432. --6 шаг генерации рейтинга эффективности
  433. --ERC_19_CONTRACT
  434. --drop table ERC_19_CONTRACT;
  435. CREATE table ERC_19_CONTRACT as
  436. with notice as
  437. (
  438. select
  439. c.regnum rnk,
  440. c.NUMBER_ rnk_number,
  441. c.purchasecode ikz,
  442. c.signdate,
  443. c.publishdate,
  444. c.VERSIONNUMBER,
  445. c.NOTIFICATIONNUMBER,
  446. nvl(c.LOTNUMBER,1)LOTNUMBER,
  447. c.PLACING sop_code,
  448. case
  449. when c.PLACING in ('13013') then 'Запрос котировок в электронной форме'
  450. when c.PLACING in ('12011') then 'Аукцион в электронной форме'
  451. when c.PLACING in ('14013') then 'Запрос предложений в электронной форме'
  452. when c.PLACING in ('20000') or c.PLACING is null and c.SINGLECUSTOMER is not null then 'Закупка у единственного поставщика (подрядчика, исполнителя)'
  453. when c.PLACING in ('11013') then 'Открытый конкурс в электронной форме'
  454. when c.PLACING in ('11031') then 'Двухэтапный конкурс в электронной форме'
  455. when c.PLACING in ('13011') then 'Запрос котировок'
  456. when c.PLACING in ('11023') then 'Конкурс с ограниченным участием в электронной форме'
  457. when c.PLACING in ('30000') then 'Способ определения поставщика (подрядчика, исполнителя), установленный Правительством Российской Федерации в соответствии со статьей 111 Федерального закона'
  458. when c.PLACING in ('14011') then 'Запрос предложений'
  459. when c.PLACING in ('11011') then 'Открытый конкурс'
  460. when c.PLACING in ('11042') then 'Закрытый конкурс'
  461. when c.PLACING in ('11021') then 'Конкурс с ограниченным участием'
  462. when c.PLACING in ('12022') then 'закрытый аукцион'
  463. else null end sop_name,
  464. c.SINGLECUSTOMER,
  465. rs.name SINGLECUSTOMER_name,
  466. c.PROTOCOLDATE,
  467. c.PRICE,
  468. c.CURRENCY,
  469. case when c.CHANGETYPE_TAG = 'contractChange' then 1 else 0 end modif,
  470. c.changereason modif_reason,
  471. case when c.CURRENTCONTRACTSTAGE = 'E' then 'Исполнение'
  472. when c.CURRENTCONTRACTSTAGE = 'ET' then 'Исполнение прекращено'
  473. when c.CURRENTCONTRACTSTAGE = 'EC' then 'Исполнение завершено'
  474. when c.CURRENTCONTRACTSTAGE = 'IN' then 'Aннулировано'
  475. else null end stage,
  476. c.EXECUTIONPERIOD_START,
  477. c.EXECUTIONPERIOD_END,
  478. c.SUPPLIER_TYPE,
  479. c.SUPPLIER_FULLNAME,
  480. c.SUPPLIER_INN,
  481. c.SUPPLIER_KPP,
  482. c.PRODUCT_NAME object_name,
  483. c.CUSTOMER_FULLNAME org_name,
  484. c.CUSTOMER_INN org_inn,
  485. c.CUSTOMER_REGNUM org_spz,
  486. org.id org_kgntv_contract,
  487. n.org_kgntv org_kgntv_notice,
  488. nvl(n.joflag,0) joflag,
  489. nvl(s.org_kgntv, s_pp.org_kgntv) org_kgntv_schedule,
  490. nvl(s.placingway_name, s_pp.placingway_name) placingway_name,
  491. c.ID,
  492. c_ais.ikz ikz_ais,
  493. row_number() over (partition by c.regnum order by c.VERSIONNUMBER, c.publishdate) rn_first,
  494. row_number() over (partition by c.regnum order by c.VERSIONNUMBER desc, c.publishdate desc) rn_last
  495. from dwh_contract_notice_nrpz c
  496. /*left*/ join erc_dwh_organization_kgntv org on c.customer_regnum = org.spz
  497. left join contract_single_supp_reasons rs on rs.code_oos = c.SINGLECUSTOMER and rs.actual = 't'
  498. left join
  499. (select c.contractrnk rnk, min(p.pg_ikz ) ikz
  500. from erc_dwh_contract_kgntv_194_6 c
  501. join erc_dwh_procedures_kgntv_194_6 p on c.lotid = p.lotuuid
  502. group by c.contractrnk
  503. )c_ais on c_ais.rnk = c.regnum
  504. left join
  505. (select reqnum,
  506. case when joflag = 0 then org_kgntv else org_kgntv_joflag end org_kgntv,
  507. max(joflag) joflag
  508. from erc_19_start_notice
  509. group by reqnum, case when joflag = 0 then org_kgntv else org_kgntv_joflag end
  510. ) n on n.reqnum = c.notificationnumber
  511. left join (select distinct ikz, org_kgntv, placingway_name from erc_19_schedule_pos /*where purchasecanceled is null*/) s on s.ikz = c.purchasecode
  512. left join (select distinct ikz_pp ikz, org_kgntv, placingway_name from erc_19_schedule_pos /*where purchasecanceled is null*/) s_pp on s_pp.ikz = c.purchasecode
  513. where c.name like '%contract'
  514. and --c.publishdate< to_date('17-01-2018','DD-MM-YYYY') and
  515. c.signdate<to_date('01012020','DDMMYYYY') and c.signdate>=to_date('01012019','DDMMYYYY') and coalesce(n.reqnum,s.ikz,s_pp.ikz) is not null)
  516.  
  517. select
  518. n_first.rnk,
  519. n_first.rnk_number,
  520. n_first.ikz,
  521. n_first.ikz_ais,
  522. n_first.signdate,
  523. n_first.publishdate publishdate_first,
  524.  
  525. n_last.publishdate publishdate_last,
  526.  
  527. n_first.VERSIONNUMBER VERSIONNUMBER_first,
  528.  
  529. n_last.VERSIONNUMBER VERSIONNUMBER_last,
  530.  
  531. n_first.NOTIFICATIONNUMBER,
  532. n_first.LOTNUMBER,
  533. n_first.sop_name,
  534. case when n_first.SINGLECUSTOMER_name is null then n_last.SINGLECUSTOMER_name else n_first.SINGLECUSTOMER_name end SINGLECUSTOMER_name,
  535. n_first.PROTOCOLDATE,
  536. n_first.PRICE,
  537.  
  538.  
  539.  
  540. n_last.PRICE PRICE_cur,
  541. n_first.CURRENCY,
  542.  
  543. n_last.modif,
  544. n_last.modif_reason,
  545. nvl(c_proc.stage, n_last.stage) stage,
  546.  
  547. --c_proc-(от стадии исполнения контракта)
  548. c_proc.executions_date,
  549. c_proc.rejected_date,
  550. c_proc.rejected_paid,
  551. c_proc.rejected_reason,
  552. c_proc.rejected_reason_name,
  553. c_proc.penalties_type,
  554. c_proc.penalties_party,
  555. c_proc.penalties_amount,
  556.  
  557. n_first.EXECUTIONPERIOD_START,
  558.  
  559. n_last.EXECUTIONPERIOD_END,
  560.  
  561. n_first.SUPPLIER_TYPE,
  562. n_first.SUPPLIER_FULLNAME,
  563. n_first.SUPPLIER_INN,
  564. n_first.SUPPLIER_KPP,
  565. n_first.object_name,
  566. n_first.org_name,
  567. n_first.org_inn,
  568. n_first.org_spz,
  569. n_first.org_kgntv_contract,
  570. n_first.org_kgntv_notice,
  571. n_first.org_kgntv_schedule,
  572. coalesce(n_first.org_kgntv_contract,n_first.org_kgntv_schedule, n_first.org_kgntv_notice) org_kgntv,
  573. n_first.joflag,
  574. n_first.placingway_name,
  575. prot.protocoldate_publ,
  576. prot.protocoldate_SIGN,
  577. prot_one.protocoldate_publ protocoldate_one_publ,
  578. prot_one.protocoldate_SIGN protocoldate_one_SIGN,
  579. mess.successdate FIRSTNOTICEsuccesDATE,
  580. case when regexp_like(case when n_first.SINGLECUSTOMER_name is null then n_last.SINGLECUSTOMER_name else n_first.SINGLECUSTOMER_name end, '(^Часть 1 пункт (4|5|23|42|44|45|46) статьи 93)') then 0 else 1 end flag_16 ,
  581. nvl(cn.cnt_modif,0) cnt_modif,
  582. case when peva.PURCHASENUMBER is not null then 1 else 0 end flag_evasion
  583. from notice n_first
  584. join notice n_last on n_last.rn_last = 1 and n_first.rnk = n_last.rnk
  585.  
  586. left join
  587. (select rnk, count(*) cnt_modif from
  588. (select distinct regnum rnk, VERSIONNUMBER , PUBLISHDATE
  589. from dwh_contract_notice_nrpz cn
  590. where cn.name like '%contract' and cn.publishdate< to_date('21-01-2020','DD-MM-YYYY') and cn.CHANGETYPE_TAG = 'contractChange' -- зачем мы делаем срез на 11 число = потому что контракт может быть подписан в нужной нам для отчета квартале а вот публикаци может выходить за эту дату для этого мы и берем 11.10.2018
  591. )
  592. group by rnk
  593. )cn on n_first.rnk = cn.rnk
  594. left join
  595. (select
  596. cp.regnum rnk,
  597. cp.publishdate,
  598. case
  599. when cp.rejected_date is not null then 'Исполнение прекращено'
  600. --when cp.executions_date is not null then 'Исполнение завершено'
  601. when cp.CURRENTCONTRACTSTAGE = 'E' then 'Исполнение'
  602. when cp.CURRENTCONTRACTSTAGE = 'ET' then 'Исполнение прекращено'
  603. when cp.CURRENTCONTRACTSTAGE = 'EC' then 'Исполнение завершено'
  604. when cp.CURRENTCONTRACTSTAGE = 'IN' then 'Aннулировано'
  605. else null end stage,
  606. case when cp.CURRENTCONTRACTSTAGE = 'E' then null else cp.executions_date end executions_date,
  607. cp.rejected_date,
  608. cp.rejected_paid,
  609. cp.rejected_reason,
  610. case when cp.rejected_reason = '1' then 'Соглашение сторон'
  611. when cp.rejected_reason = '2' then 'Судебный акт'
  612. when cp.rejected_reason = '3' then 'Односторонний отказ заказчика от исполнения контракта в соответствии с гражданским законодательством'
  613. when cp.rejected_reason = '4' then 'Односторонний отказ поставщика (подрядчика, исполнителя) от исполнения контракта в соответствии с гражданским законодательством'
  614. else null end rejected_reason_name,
  615. case when cp.penalties_type = 'F' then 'Штраф'
  616. when cp.penalties_type = 'I' then 'Пени'
  617. else null end penalties_type,
  618. case when cp.penalties_party = 'C' then 'Заказчик'
  619. when cp.penalties_party = 'S' then 'Поставщик'
  620. else null end penalties_party,
  621. cp.penalties_amount,
  622. row_number() over (partition by cp.regnum order by cp.publishdate desc) rn
  623. from dwh_contract_notice_nrpz cp
  624. left join (select distinct regnum, id from dwh_contract_notice_nrpz where name like '%contractProcedureCancel') canc_ on cp.id = canc_.id and cp.regnum = canc_.regnum
  625. where cp.name like '%contractProcedure' and canc_.id is null --and cp.publishdate<to_date('01012020','DDMMYYYY')
  626. )c_proc on c_proc.rn = 1 and c_proc.rnk = n_first.rnk
  627.  
  628. left join
  629. (
  630. select to_char ( p.purchasenumber ) numb,
  631. max (case when pc.reqnum is not null then pc.publishdate else p.publishdate end) protocoldate_publ,
  632. max(p.SIGNDATE) protocoldate_SIGN
  633. from DWH_PROTOCOL_NRPZ p
  634. left join (select distinct purchasenumber, PROTOCOLNUMBER from DWH_PROTOCOL_NRPZ where type in ('ProtocolCancel','rotocolCancel'))canc on canc.protocolnumber = p.protocolnumber and canc.PURCHASENUMBER = p.PURCHASENUMBER
  635. left join erc_19_protocol_ch pc on p.purchasenumber = pc.reqnum and p.TYPE = pc.type
  636. where p.publishdate < to_date ( '01.01.2020', 'dd.MM.YYYY' ) and p.type in ('ProtocolEFSingleApp','ProtocolEFSinglePart','ProtocolZP','ProtocolZPFinal','ProtocolEF3','ProtocolZK','ProtocolOK2','ProtocolOKD5','ProtocolOKOU3','ProtocolZKAfterProlong') and canc.protocolnumber is null
  637. group by to_char ( p.purchasenumber )
  638. ) prot on prot.numb = n_first.NOTIFICATIONNUMBER
  639.  
  640. left join
  641. (
  642. select to_char ( p.purchasenumber ) numb,
  643. max (case when pc.reqnum is not null then pc.publishdate else p.publishdate end) protocoldate_publ,
  644. max(p.SIGNDATE) protocoldate_SIGN
  645. from DWH_PROTOCOL_NRPZ p
  646. left join (select distinct purchasenumber, PROTOCOLNUMBER from DWH_PROTOCOL_NRPZ where type in ('ProtocolCancel','rotocolCancel'))canc on canc.protocolnumber = p.protocolnumber and canc.PURCHASENUMBER = p.PURCHASENUMBER
  647. left join erc_19_protocol_ch pc on p.purchasenumber = pc.reqnum and p.TYPE = pc.type
  648. where p.publishdate < to_date ( '01.01.2020', 'dd.MM.YYYY' ) and p.type in ('ProtocolOKOUSingleApp','ProtocolOKDSingleApp','ProtocolOKSingleApp','ProtocolZP','ProtocolZPFinal') and canc.protocolnumber is null
  649. group by to_char ( p.purchasenumber )
  650. ) prot_one on prot_one.numb = n_first.NOTIFICATIONNUMBER
  651.  
  652. left join
  653. (
  654. select contract_rnk rnk,successdate --изменение с successdate на date_ от 14/08/2017 Терехова -- от 14.11 считать от успешной даты первой отправки сведений на ЕИС
  655. from erc_19_contract_mess
  656. where date_ < to_date('2020-01-01', 'YYYY-MM-DD')
  657. )mess on mess.rnk = n_first.rnk
  658. left join
  659. (select distinct PURCHASENUMBER
  660. from DWH_PROTOCOL_NRPZ
  661. where TYPE in('ProtocolDeviation', 'ProtocolEvasion') and PUBLISHDATE < TO_DATE('2020-01-01', 'YYYY-MM-DD')
  662. )peva on peva.PURCHASENUMBER = n_first.NOTIFICATIONNUMBER
  663. where n_first.rn_first = 1;
  664.  
  665.  
  666. --drop table erc_19_contract_all;
  667. create table erc_19_contract_all
  668. as select * from erc_19_contract;
  669.  
  670. -- drop table erc_19_contract_del;
  671. create table erc_19_contract_del as
  672. select
  673. st.rnk,
  674. st.publishdate_first, st.ikz,ikz_ais, NOTIFICATIONNUMBER,
  675. row_number() over (partition by st.ikz order by st.publishdate_first desc) rn
  676. from erc_19_contract st
  677. join
  678. (select
  679. ikz,
  680. count(distinct rnk)
  681. from erc_19_contract
  682. where ikz is not null and nvl(placingway_name,' ') <>'Особая закупка'
  683. group by ikz
  684. having count(distinct rnk)>1
  685. )f on f.ikz = st.ikz;
  686. --6 шаг генерации рейтинга эффективности
  687. --ERC_19_CONTRACT
  688.  
  689. -- !!!!! СНАЧАЛА изменить view : ERC_19_CONTRACT_add (даты и наименование таблиц на нужный квартал)
  690.  
  691. --7 шаг генерации рейтинга эффективности
  692. --ERC_19_CONTRACT_1
  693. /*удалить все без связи в АИС ГЗ по ИКЗ*/
  694. delete from erc_19_contract where rnk in
  695. (select rnk from erc_19_contract_del where ikz_ais is null
  696. );
  697.  
  698. /*Заменить ИКЗ контракта на ИКЗ из АИС ГЗ*/
  699. UPDATE erc_19_contract t set t.ikz = t.ikz_ais
  700. where t.rnk in
  701. (select cd.rnk --, cd.ikz ikz_from, cd.ikz_ais ikz_to, a.lotuuid, a.requestid --,a.ikz ikz_ais
  702. from erc_19_contract_del cd
  703. left join erc_19_contract_all ca on ca.ikz = cd.ikz_ais and cd.ikz<>cd.ikz_ais
  704. left join
  705. (select c.contractrnk rnk, min(p.lotuuid) lotuuid, min(p.requestid)requestid, min(p.pg_ikz ) ikz
  706. from erc_dwh_contract_kgntv_194_6 c
  707. join erc_dwh_procedures_kgntv_194_6 p on c.lotid = p.lotuuid
  708. group by c.contractrnk
  709. )a on a.rnk = cd.rnk
  710. where ca.ikz is null and cd.ikz_ais is not null and cd.ikz_ais<>cd.ikz
  711. );-- заменяются икз контрактов на икз из АИС ГЗ
  712.  
  713. /*удаляем остальное*/
  714. delete from erc_19_contract where rnk in
  715. (select distinct
  716. st.rnk
  717. from erc_19_contract st
  718. join
  719. (select
  720. ikz,
  721. count(distinct rnk)
  722. from erc_19_contract
  723. where ikz is not null and nvl(placingway_name,' ') <>'Особая закупка'
  724. group by ikz
  725. having count(distinct rnk)>1
  726. )f on f.ikz = st.ikz
  727. );
  728.  
  729. --drop table ERC_19_CONTRACT_plus;
  730. create table ERC_19_CONTRACT_plus as
  731. select * from ERC_19_CONTRACT;
  732.  
  733. drop table ERC_19_CONTRACT;
  734. create table ERC_19_CONTRACT as
  735. select
  736. RNK,
  737. RNK_NUMBER,
  738. IKZ,
  739. SIGNDATE,
  740. PUBLISHDATE_FIRST,
  741. PUBLISHDATE_LAST,
  742. VERSIONNUMBER_FIRST,
  743. VERSIONNUMBER_LAST,
  744. NOTIFICATIONNUMBER,
  745. LOTNUMBER,
  746. SOP_NAME,
  747. to_char(SINGLECUSTOMER_NAME) SINGLECUSTOMER_NAME,
  748. PROTOCOLDATE,
  749. PRICE,
  750. PRICE_CUR,
  751. CURRENCY,
  752. MODIF,
  753. MODIF_REASON,
  754. STAGE,
  755. EXECUTIONS_DATE,
  756. REJECTED_DATE,
  757. REJECTED_PAID,
  758. REJECTED_REASON,
  759. REJECTED_REASON_NAME,
  760. PENALTIES_TYPE,
  761. PENALTIES_PARTY,
  762. PENALTIES_AMOUNT,
  763. EXECUTIONPERIOD_START,
  764. EXECUTIONPERIOD_END,
  765. SUPPLIER_TYPE,
  766. SUPPLIER_FULLNAME,
  767. SUPPLIER_INN,
  768. SUPPLIER_KPP,
  769. OBJECT_NAME,
  770. ORG_NAME,
  771. ORG_INN,
  772. ORG_SPZ,
  773. ORG_KGNTV_CONTRACT,
  774. ORG_KGNTV_NOTICE,
  775. ORG_KGNTV_SCHEDULE,
  776. ORG_KGNTV,
  777. JOFLAG,
  778. PLACINGWAY_NAME,
  779. PROTOCOLDATE_PUBL,
  780. PROTOCOLDATE_SIGN,
  781. PROTOCOLDATE_ONE_PUBL,
  782. PROTOCOLDATE_ONE_SIGN,
  783. FIRSTNOTICESUCCESDATE,
  784. FLAG_16,
  785. CNT_MODIF,
  786. flag_evasion,
  787. 'eis' type_
  788. from ERC_19_CONTRACT_plus
  789. union all
  790. select
  791. RNK,
  792. RNK_NUMBER,
  793. IKZ,
  794. SIGNDATE,
  795. null PUBLISHDATE_FIRST,
  796. null PUBLISHDATE_LAST,
  797. null VERSIONNUMBER_FIRST,
  798. null VERSIONNUMBER_LAST,
  799. NOTIFICATIONNUMBER,
  800. LOTNUMBER,
  801. SOP_NAME,
  802. SINGLECUSTOMER_NAME,
  803. PROTOCOLDATE,
  804. PRICE,
  805. PRICE_CUR,
  806. CURRENCY,
  807. null MODIF,
  808. MODIF_REASON,
  809. STAGE,
  810. EXECUTIONS_DATE,
  811. REJECTED_DATE,
  812. REJECTED_PAID,
  813. REJECTED_REASON,
  814. REJECTED_REASON_NAME,
  815. PENALTIES_TYPE,
  816. PENALTIES_PARTY,
  817. null PENALTIES_AMOUNT,
  818. null EXECUTIONPERIOD_START,
  819. EXECUTIONPERIOD_END,
  820. SUPPLIER_TYPE,
  821. SUPPLIER_FULLNAME,
  822. SUPPLIER_INN,
  823. SUPPLIER_KPP,
  824. OBJECT_NAME,
  825. ORG_NAME,
  826. ORG_INN,
  827. ORG_SPZ,
  828. ORG_KGNTV_CONTRACT,
  829. ORG_KGNTV_NOTICE,
  830. ORG_KGNTV_SCHEDULE,
  831. ORG_KGNTV,
  832. 0 JOFLAG,
  833. PLACINGWAY_NAME,
  834. PROTOCOLDATE_PUBL,
  835. PROTOCOLDATE_SIGN,
  836. PROTOCOLDATE_ONE_PUBL,
  837. PROTOCOLDATE_ONE_SIGN,
  838. FIRSTNOTICESUCCESDATE,
  839. FLAG_16,
  840. CNT_MODIF,
  841. flag_evasion,
  842. 'kg' type_
  843. from ERC_19_CONTRACT_add;
  844. --7 шаг генерации рейтинга эффективности
  845. --ERC_19_CONTRACT_1
  846.  
  847. --8 шаг генерации рейтинга эффективности
  848. --ERC_19_LIST_CONTRACT
  849. --drop table erc_19_list_contract;
  850. create table erc_19_list_contract as
  851. select
  852. sch.ORG_NAME,sch.ORG_INN,sch.ORG_SPZ,sch.ORG_KGNTV,sch.PLANNUMBER,sch.VERSIONNUMBER,sch.PUBLISHDATE,sch.POSITIONNUMBER,sch.IKZ,sch.IKZ_PP,sch.PLAN_PLACEMENT_DATE,sch.PLAN_EXECUTION_DATE,sch.OBJECT_NAME,sch.SOP_CODE,sch.SOP_NAME,sch.nmc_SCHEDULE,sch.PURCHASECANCELED,sch.SPECIALPURCHASE_TYPE,sch.FLAG_COMP,sch.CNT_MODIF_PG,sch.REQNUM,sch.PUBLISHDATE_REQNUM,sch.SOP_CODE_REQNUM,sch.SOP_NAME_REQNUM,sch.JOFLAG_ORG_NAME,sch.JOFLAG_ORG_SPZ,sch.ORG_KGNTV_JOFLAG,sch.JOFLAG,sch.STARTDATE,sch.ENDDATE,sch.nmc_REQNUM,sch.nmc_joflag,sch.BIDDINGDATE,sch.OPENINGDATE,sch.SCORINGDATE,sch.PREQUALIFICATION,sch.LOTNUMBER,sch.POSITIONNUMBER_REQNUM,sch.FLAG_COMP_REQNUM,sch.IKZ_REQNUM,sch.PLANNUMBER_REQNUM,sch.OBJECT_NAME_REQNUM,sch.CNT_MODIF_REQNUM,sch.flag_cans_REQNUM,sch.purchasenumber_rn,sch.flag_st_hand,sch.flah_act_version,
  853. con.RNK,con.RNK_NUMBER,con.IKZ IKZ_con,con.SIGNDATE,con.PUBLISHDATE_FIRST PUBLISHDATE_con,con.PUBLISHDATE_LAST PUBLISHDATE_con_last,con.VERSIONNUMBER_FIRST VERSIONNUMBER_con,con.VERSIONNUMBER_LAST VERSIONNUMBER_con_last,con.NOTIFICATIONNUMBER reqnum_con,con.LOTNUMBER LOTNUMBER_con,con.SOP_NAME SOP_NAME_con,con.SINGLECUSTOMER_NAME oneex_con,con.PROTOCOLDATE PROTOCOLDATE_con,con.PRICE ck_first,con.PRICE_CUR ck_last,con.STAGE STAGE_con,con.EXECUTIONS_DATE,con.REJECTED_DATE,con.REJECTED_PAID,con.REJECTED_REASON,con.REJECTED_REASON_NAME,con.PENALTIES_TYPE,con.PENALTIES_PARTY,con.PENALTIES_AMOUNT,con.EXECUTIONPERIOD_END PLAN_EXECUTION_DATe_con,con.SUPPLIER_TYPE,con.SUPPLIER_FULLNAME,con.SUPPLIER_INN,con.SUPPLIER_KPP,con.OBJECT_NAME OBJECT_NAME_con,con.ORG_NAME ORG_NAME_con,con.ORG_INN ORG_INN_con,con.ORG_SPZ ORG_SPZ_con,con.ORG_KGNTV_CONTRACT,con.protocoldate_publ,con.protocoldate_SIGN,con.protocoldate_one_publ,con.protocoldate_one_SIGN,con.FIRSTNOTICEsuccesDATE,con.flag_16,con.CNT_MODIF CNT_MODIF_con,con.type_,con.flag_evasion
  854. from erc_19_list sch
  855. join
  856. (select
  857. con.RNK,con.RNK_NUMBER,con.IKZ,con.SIGNDATE,con.PUBLISHDATE_FIRST ,con.PUBLISHDATE_LAST ,con.VERSIONNUMBER_FIRST ,con.VERSIONNUMBER_LAST ,con.NOTIFICATIONNUMBER ,con.LOTNUMBER ,con.SOP_NAME ,to_char(con.SINGLECUSTOMER_NAME) SINGLECUSTOMER_NAME,con.PROTOCOLDATE ,con.PRICE ,con.PRICE_CUR ,con.STAGE ,con.EXECUTIONS_DATE,con.REJECTED_DATE,con.REJECTED_PAID,con.REJECTED_REASON,con.REJECTED_REASON_NAME,con.PENALTIES_TYPE,con.PENALTIES_PARTY,con.PENALTIES_AMOUNT,con.EXECUTIONPERIOD_END ,con.SUPPLIER_TYPE,con.SUPPLIER_FULLNAME,con.SUPPLIER_INN,con.SUPPLIER_KPP,con.OBJECT_NAME ,con.ORG_NAME ,con.ORG_INN ,con.ORG_SPZ ,con.ORG_KGNTV_CONTRACT,con.protocoldate_publ,con.protocoldate_SIGN,con.protocoldate_one_publ,con.protocoldate_one_SIGN,con.FIRSTNOTICEsuccesDATE,con.flag_16,con.CNT_MODIF, con.type_,con.flag_evasion
  858. from erc_19_contract con
  859. ) con
  860. on sch.joflag = 0
  861. and con.NOTIFICATIONNUMBER = sch.reqnum
  862. and con.lotnumber = sch.lotnumber
  863. union all
  864. select
  865. sch.ORG_NAME,sch.ORG_INN,sch.ORG_SPZ,sch.ORG_KGNTV,sch.PLANNUMBER,sch.VERSIONNUMBER,sch.PUBLISHDATE,sch.POSITIONNUMBER,sch.IKZ,sch.IKZ_PP,sch.PLAN_PLACEMENT_DATE,sch.PLAN_EXECUTION_DATE,sch.OBJECT_NAME,sch.SOP_CODE,sch.SOP_NAME,sch.nmc_SCHEDULE,sch.PURCHASECANCELED,sch.SPECIALPURCHASE_TYPE,sch.FLAG_COMP,sch.CNT_MODIF_PG,sch.REQNUM,sch.PUBLISHDATE_REQNUM,sch.SOP_CODE_REQNUM,sch.SOP_NAME_REQNUM,sch.JOFLAG_ORG_NAME,sch.JOFLAG_ORG_SPZ,sch.ORG_KGNTV_JOFLAG,sch.JOFLAG,sch.STARTDATE,sch.ENDDATE,sch.nmc_REQNUM,sch.nmc_joflag,sch.BIDDINGDATE,sch.OPENINGDATE,sch.SCORINGDATE,sch.PREQUALIFICATION,sch.LOTNUMBER,sch.POSITIONNUMBER_REQNUM,sch.FLAG_COMP_REQNUM,sch.IKZ_REQNUM,sch.PLANNUMBER_REQNUM,sch.OBJECT_NAME_REQNUM,sch.CNT_MODIF_REQNUM,sch.flag_cans_REQNUM,sch.purchasenumber_rn,sch.flag_st_hand,sch.flah_act_version,
  866. con.RNK,con.RNK_NUMBER,con.IKZ IKZ_con,con.SIGNDATE,con.PUBLISHDATE_FIRST PUBLISHDATE_con,con.PUBLISHDATE_LAST PUBLISHDATE_con_last,con.VERSIONNUMBER_FIRST VERSIONNUMBER_con,con.VERSIONNUMBER_LAST VERSIONNUMBER_con_last,con.NOTIFICATIONNUMBER reqnum_con,con.LOTNUMBER LOTNUMBER_con,con.SOP_NAME SOP_NAME_con,con.SINGLECUSTOMER_NAME oneex_con,con.PROTOCOLDATE PROTOCOLDATE_con,con.PRICE ck_first,con.PRICE_CUR ck_last,con.STAGE STAGE_con,con.EXECUTIONS_DATE,con.REJECTED_DATE,con.REJECTED_PAID,con.REJECTED_REASON,con.REJECTED_REASON_NAME,con.PENALTIES_TYPE,con.PENALTIES_PARTY,con.PENALTIES_AMOUNT,con.EXECUTIONPERIOD_END PLAN_EXECUTION_DATe_con,con.SUPPLIER_TYPE,con.SUPPLIER_FULLNAME,con.SUPPLIER_INN,con.SUPPLIER_KPP,con.OBJECT_NAME OBJECT_NAME_con,con.ORG_NAME ORG_NAME_con,con.ORG_INN ORG_INN_con,con.ORG_SPZ ORG_SPZ_con,con.ORG_KGNTV_CONTRACT,con.protocoldate_publ,con.protocoldate_SIGN,con.protocoldate_one_publ,con.protocoldate_one_SIGN,con.FIRSTNOTICEsuccesDATE,con.flag_16,con.CNT_MODIF CNT_MODIF_con,con.type_,con.flag_evasion
  867. from erc_19_list sch
  868. join
  869. (select
  870. con.RNK,con.RNK_NUMBER,con.IKZ,con.SIGNDATE,con.PUBLISHDATE_FIRST ,con.PUBLISHDATE_LAST ,con.VERSIONNUMBER_FIRST ,con.VERSIONNUMBER_LAST ,con.NOTIFICATIONNUMBER ,con.LOTNUMBER ,con.SOP_NAME ,to_char(con.SINGLECUSTOMER_NAME) SINGLECUSTOMER_NAME,con.PROTOCOLDATE ,con.PRICE ,con.PRICE_CUR ,con.STAGE ,con.EXECUTIONS_DATE,con.REJECTED_DATE,con.REJECTED_PAID,con.REJECTED_REASON,con.REJECTED_REASON_NAME,con.PENALTIES_TYPE,con.PENALTIES_PARTY,con.PENALTIES_AMOUNT,con.EXECUTIONPERIOD_END ,con.SUPPLIER_TYPE,con.SUPPLIER_FULLNAME,con.SUPPLIER_INN,con.SUPPLIER_KPP,con.OBJECT_NAME ,con.ORG_NAME ,con.ORG_INN ,con.ORG_SPZ ,con.ORG_KGNTV_CONTRACT,con.protocoldate_publ,con.protocoldate_SIGN,con.protocoldate_one_publ,con.protocoldate_one_SIGN,con.FIRSTNOTICEsuccesDATE,con.flag_16,con.CNT_MODIF, con.type_,con.flag_evasion
  871. from erc_19_contract con
  872. ) con
  873. on con.ikz = sch.ikz
  874. and sch.reqnum is null
  875. union all
  876. select
  877. sch.ORG_NAME,sch.ORG_INN,sch.ORG_SPZ,sch.ORG_KGNTV,sch.PLANNUMBER,sch.VERSIONNUMBER,sch.PUBLISHDATE,sch.POSITIONNUMBER,sch.IKZ,sch.IKZ_PP,sch.PLAN_PLACEMENT_DATE,sch.PLAN_EXECUTION_DATE,sch.OBJECT_NAME,sch.SOP_CODE,sch.SOP_NAME,sch.nmc_SCHEDULE,sch.PURCHASECANCELED,sch.SPECIALPURCHASE_TYPE,sch.FLAG_COMP,sch.CNT_MODIF_PG,sch.REQNUM,sch.PUBLISHDATE_REQNUM,sch.SOP_CODE_REQNUM,sch.SOP_NAME_REQNUM,sch.JOFLAG_ORG_NAME,sch.JOFLAG_ORG_SPZ,sch.ORG_KGNTV_JOFLAG,sch.JOFLAG,sch.STARTDATE,sch.ENDDATE,sch.nmc_REQNUM,sch.nmc_joflag,sch.BIDDINGDATE,sch.OPENINGDATE,sch.SCORINGDATE,sch.PREQUALIFICATION,sch.LOTNUMBER,sch.POSITIONNUMBER_REQNUM,sch.FLAG_COMP_REQNUM,sch.IKZ_REQNUM,sch.PLANNUMBER_REQNUM,sch.OBJECT_NAME_REQNUM,sch.CNT_MODIF_REQNUM,sch.flag_cans_REQNUM,sch.purchasenumber_rn,sch.flag_st_hand,sch.flah_act_version,
  878. con.RNK,con.RNK_NUMBER,con.IKZ IKZ_con,con.SIGNDATE,con.PUBLISHDATE_FIRST PUBLISHDATE_con,con.PUBLISHDATE_LAST PUBLISHDATE_con_last,con.VERSIONNUMBER_FIRST VERSIONNUMBER_con,con.VERSIONNUMBER_LAST VERSIONNUMBER_con_last,con.NOTIFICATIONNUMBER reqnum_con,con.LOTNUMBER LOTNUMBER_con,con.SOP_NAME SOP_NAME_con,con.SINGLECUSTOMER_NAME oneex_con,con.PROTOCOLDATE PROTOCOLDATE_con,con.PRICE ck_first,con.PRICE_CUR ck_last,con.STAGE STAGE_con,con.EXECUTIONS_DATE,con.REJECTED_DATE,con.REJECTED_PAID,con.REJECTED_REASON,con.REJECTED_REASON_NAME,con.PENALTIES_TYPE,con.PENALTIES_PARTY,con.PENALTIES_AMOUNT,con.EXECUTIONPERIOD_END PLAN_EXECUTION_DATe_con,con.SUPPLIER_TYPE,con.SUPPLIER_FULLNAME,con.SUPPLIER_INN,con.SUPPLIER_KPP,con.OBJECT_NAME OBJECT_NAME_con,con.ORG_NAME ORG_NAME_con,con.ORG_INN ORG_INN_con,con.ORG_SPZ ORG_SPZ_con,con.ORG_KGNTV_CONTRACT,con.protocoldate_publ,con.protocoldate_SIGN,con.protocoldate_one_publ,con.protocoldate_one_SIGN,con.FIRSTNOTICEsuccesDATE,con.flag_16,con.CNT_MODIF CNT_MODIF_con,con.type_,con.flag_evasion
  879. from erc_19_list sch
  880. join
  881. (select
  882. con.RNK,con.RNK_NUMBER,con.IKZ,con.SIGNDATE,con.PUBLISHDATE_FIRST ,con.PUBLISHDATE_LAST ,con.VERSIONNUMBER_FIRST ,con.VERSIONNUMBER_LAST ,con.NOTIFICATIONNUMBER ,con.LOTNUMBER ,con.SOP_NAME ,to_char(con.SINGLECUSTOMER_NAME) SINGLECUSTOMER_NAME,con.PROTOCOLDATE ,con.PRICE ,con.PRICE_CUR ,con.STAGE ,con.EXECUTIONS_DATE,con.REJECTED_DATE,con.REJECTED_PAID,con.REJECTED_REASON,con.REJECTED_REASON_NAME,con.PENALTIES_TYPE,con.PENALTIES_PARTY,con.PENALTIES_AMOUNT,con.EXECUTIONPERIOD_END ,con.SUPPLIER_TYPE,con.SUPPLIER_FULLNAME,con.SUPPLIER_INN,con.SUPPLIER_KPP,con.OBJECT_NAME ,con.ORG_NAME ,con.ORG_INN ,con.ORG_SPZ ,con.ORG_KGNTV_CONTRACT,con.protocoldate_publ,con.protocoldate_SIGN,con.protocoldate_one_publ,con.protocoldate_one_SIGN,con.FIRSTNOTICEsuccesDATE,con.flag_16,con.CNT_MODIF, con.type_,con.flag_evasion
  883. from erc_19_contract con
  884. ) con
  885. on sch.joflag = 1
  886. and con.NOTIFICATIONNUMBER = sch.reqnum
  887. and con.lotnumber = sch.lotnumber
  888. and con.ikz = sch.ikz
  889. union all
  890. select
  891. sch.ORG_NAME,sch.ORG_INN,sch.ORG_SPZ,sch.ORG_KGNTV,sch.PLANNUMBER,sch.VERSIONNUMBER,sch.PUBLISHDATE,sch.POSITIONNUMBER,sch.IKZ,sch.IKZ_PP,sch.PLAN_PLACEMENT_DATE,sch.PLAN_EXECUTION_DATE,sch.OBJECT_NAME,sch.SOP_CODE,sch.SOP_NAME,sch.nmc_SCHEDULE,sch.PURCHASECANCELED,sch.SPECIALPURCHASE_TYPE,sch.FLAG_COMP,sch.CNT_MODIF_PG,sch.REQNUM,sch.PUBLISHDATE_REQNUM,sch.SOP_CODE_REQNUM,sch.SOP_NAME_REQNUM,sch.JOFLAG_ORG_NAME,sch.JOFLAG_ORG_SPZ,sch.ORG_KGNTV_JOFLAG,sch.JOFLAG,sch.STARTDATE,sch.ENDDATE,sch.nmc_REQNUM,sch.nmc_joflag,sch.BIDDINGDATE,sch.OPENINGDATE,sch.SCORINGDATE,sch.PREQUALIFICATION,sch.LOTNUMBER,sch.POSITIONNUMBER_REQNUM,sch.FLAG_COMP_REQNUM,sch.IKZ_REQNUM,sch.PLANNUMBER_REQNUM,sch.OBJECT_NAME_REQNUM,sch.CNT_MODIF_REQNUM,sch.flag_cans_REQNUM,sch.purchasenumber_rn,sch.flag_st_hand,sch.flah_act_version,
  892. null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null
  893. from erc_19_list sch
  894. left join
  895. (select distinct con.RNK, con.IKZ, con.NOTIFICATIONNUMBER, con.LOTNUMBER
  896. from erc_19_contract con
  897. )con_1 on sch.joflag = 1 and con_1.NOTIFICATIONNUMBER = sch.reqnum and con_1.lotnumber = sch.lotnumber and con_1.ikz = sch.ikz
  898. left join
  899. (select distinct con.RNK, con.IKZ, con.NOTIFICATIONNUMBER, con.LOTNUMBER
  900. from erc_19_contract con
  901. )con_2 on sch.joflag = 0 and con_2.NOTIFICATIONNUMBER = sch.reqnum and con_2.lotnumber = sch.lotnumber
  902. left join
  903. (select distinct con.RNK, con.IKZ, con.NOTIFICATIONNUMBER, con.LOTNUMBER
  904. from erc_19_contract con
  905. )con_3 on con_3.ikz = sch.ikz and sch.reqnum is null
  906. where con_1.RNK is null and con_2.RNK is null and con_3.RNK is null;
  907.  
  908.  
  909. DROP TABLE erc_19_list_contract_l;
  910. CREATE TABLE erc_19_list_contract_l AS
  911. SELECT
  912. c.parentid AS grbsid,
  913. s.*
  914. FROM
  915. erc_19_list_contract s
  916. JOIN erc_dwh_organization_kgntv c ON s.org_kgntv = c.id;
  917.  
  918. DROP TABLE erc_19_list_contract;
  919. CREATE TABLE erc_19_list_contract AS
  920. SELECT * FROM erc_19_list_contract_l;
  921. --8 шаг генерации рейтинга эффективности
  922. --ERC_19_LIST_CONTRACT
  923.  
  924. --9 шаг генерации рейтинга эффективности
  925. --ERC_19_CONTRACT_KG
  926. --drop table erc_19_contract_kg;
  927. create table erc_19_contract_kg as
  928. select
  929. c.contractrnk RNK,
  930. d.ikz IKZ,to_number(d.ORGid) org_kgntv,
  931. c.contractsigndate SIGNDATE,
  932. c.contractoneexecreason SINGLECUSTOMER_NAME,
  933. nvl(c.contractsigningprice, case when c.contractfullprice>0 then c.contractfullprice else 0 end) price,
  934. case when c.contractfullprice>0 then c.contractfullprice else 0 end price_cur,
  935. case when c.contractrejectdate is not null then 'Исполнение прекращено'
  936. when c.contractactualexecdate is not null then 'Исполнение завершено'
  937. else 'Исполнение'
  938. end STAGE,
  939. c.contractactualexecdate EXECUTIONS_DATE,
  940. c.contractrejectdate REJECTED_DATE,
  941. case when pen.id is not null then 'Пени' else null end PENALTIES_TYPE,
  942. case when pen.id is not null then 'Поставщик' else null end PENALTIES_party,
  943. c.contractplaneexecdate EXECUTIONPERIOD_END,
  944. c.contractsubject OBJECT_NAME,
  945. c.customername ORG_NAME,
  946. c.customerinn ORG_INN,
  947. -- c.customerid ORG_KGNTV,
  948. c.ordertypename PLACINGWAY_NAME,
  949. c.is_concluded_in_e_shop,
  950. c.lotid,
  951. d.requestid
  952. from dwh_contract_kgntv c
  953. join dwh_procedures_detailed_kgntv d on d.lotuuid=c.lotid
  954. join erc_dwh_procedures_kgntv_194_6 p on d.DETAIL_PARENT_ID = p.REQUESTID
  955. --join dwh_procedures_detailed_kgntv d on d.REQUESTID=c.REQUESTID
  956. join erc_19_schedule_pos sch on sch.ikz = p.pg_ikz --or sch.ikz_pp = p.pz_ikz
  957. left join (select distinct id from erc_dwh_contract_pen_kg_194_6 where reason_id in (1,3)) pen on pen.id = c.contractid
  958. left join erc_19_contract cc on cc.rnk = c.contractrnk
  959. where cc.rnk is null
  960. and regexp_like(contractoneexecreason, '(^Часть 1 пункт (4|5|23|33|42|44|45|46) статьи 93)')
  961. and c.contractsigndate < to_date('01-01-2020','DD-MM-YYYY') and c.contractsigndate >= to_date('23-11-2018','DD-MM-YYYY') ;
  962.  
  963.  
  964. --9 шаг генерации рейтинга эффективнос ти
  965. --ERC_19_CONTRACT_KG
  966.  
  967. --10 шаг генерации рейтинга эффективности
  968. --ERC_19_CONTRACT_ONEEX
  969. --drop table erc_19_contract_oneex;
  970. create table erc_19_contract_oneex as
  971. select distinct kk.rnk
  972. from erc_19_contract kk
  973. where regexp_like(kk.singlecustomer_name, '(^Часть 1 пункт (1|2|3|6|8|9|22|23|29|31) статьи 93)')
  974. union all
  975. select distinct kk.rnk
  976. from erc_19_contract_kg kk
  977. where regexp_like(kk.singlecustomer_name, '(^Часть 1 пункт (1|2|3|6|8|9|22|23|29|31) статьи 93)');
  978. --10 шаг генерации рейтинга эффективности
  979. --ERC_19_CONTRACT_ONEEX
  980.  
  981. --11 шаг генерации рейтинга эффективности
  982. --ERC_19_CHECKRESULT_BASE
  983. --drop table ERC_19_CHECKRESULT_BASE;
  984. CREATE table ERC_19_CHECKRESULT_BASE as
  985. select ch.COM_NUMBER,ch.CHECK_NUMBER,ch.reqnum, ch.chec_date,ch.chec_PRES_DATE, ch.RESULT,ch.PRESCRIPTIONNUMBER,complaint_publishdate,org_name,org_inn,org_spz,checksubjects_type
  986. from (select
  987. case when ch.COMPLAINT_REGNUMBER is not null then ch.COMPLAINT_REGNUMBER else ch.COMPLAINTNUMBER end COM_NUMBER,
  988. ch.purchasenumber reqnum,
  989. case when ch.regnumber is not null then ch.regnumber when length(ch.CHECKRESULTNUMBER)>=3 then ch.CHECKRESULTNUMBER else null end CHECK_NUMBER,
  990. ch.PUBLISHDATE chec_date,
  991. to_date(ch.PRESCRIPTIONDATE,'YYYY-MM-DD') chec_PRES_DATE, --дата предписания
  992. ch.RESULT,
  993. ch.PRESCRIPTIONNUMBER,
  994. ch.complaint_publishdate,
  995. ch.checksubjects_fullname org_name,
  996. ch.checksubjects_inn org_inn, /*не всегда есть*/
  997. nvl(ch.checksubjects_regnum,org.spz) org_spz, /*не всегда есть*/
  998. ch.checksubjects_type,
  999. ROW_NUMBER() OVER (PARTITION BY
  1000. case when ch.COMPLAINT_REGNUMBER is not null then ch.COMPLAINT_REGNUMBER else ch.COMPLAINTNUMBER end,ch.purchasenumber, ch.checksubjects_regnum, ch.checksubjects_inn,
  1001. case when ch.regnumber is not null then ch.regnumber when length(ch.CHECKRESULTNUMBER)>=3 then ch.CHECKRESULTNUMBER else null end
  1002. ORDER BY ch.PUBLISHDATE desc,ch.RESULT
  1003. ) rn
  1004. from DWH_checkResult_nrpz ch
  1005. join (select distinct reqnum from erc_19_start_notice) st on st.reqnum = ch.purchasenumber
  1006. left join erc_dwh_organization_kgntv org on org.inn = ch.checksubjects_inn
  1007. where ch.NAME not in('ns2:checkResultCancel', 'checkResultCancel')
  1008. and (ch.PUBLISHDATE>=TO_DATE('2018-11-23', 'YYYY-MM-DD') --дата одобрения бюджета Советом федерации на 2019 год
  1009. and (ch.PUBLISHDATE<TO_DATE('2020-01-01', 'YYYY-MM-DD')
  1010. or to_date(ch.decisiondate,'YYYY-MM-DD')<TO_DATE('2020-01-01', 'YYYY-MM-DD')))
  1011. and ch.flag_erc is null
  1012. and nvl(ch.checksubjects_type,'1') <> 'EPNew'
  1013.  
  1014. )ch
  1015. left join
  1016. (select distinct case when regnumber is not null then regnumber when length(CHECKRESULTNUMBER)>=3 then CHECKRESULTNUMBER else null end CHECK_NUMBER
  1017. from DWH_checkResult_nrpz
  1018. where PUBLISHDATE>= TO_DATE('2018-11-23', 'YYYY-MM-DD') and PUBLISHDATE< TO_DATE('2020-01-01', 'YYYY-MM-DD')
  1019. and NAME in ('checkResultCancel','ns2:checkResultCancel')
  1020. )ch_canc on ch_canc.CHECK_NUMBER = ch.CHECK_NUMBER
  1021. where ch.rn = 1 and nvl(ch.RESULT,0) <> 'COMPLAINT_NO_VIOLATIONS' and ch_canc.CHECK_NUMBER is null
  1022. order by ch.COM_NUMBER,ch.CHECK_NUMBER
  1023. --11 шаг генерации рейтинга эффективности
  1024. --ERC_19_CHECKRESULT_BASE
  1025. ---------------Изменение 19.11.2019
  1026. --12 шаг генерации рейтинга эффективности
  1027. --ERC_19_DEMAND
  1028. --drop table erc_19_demand;
  1029. create table erc_19_demand as
  1030. select dem.purchasenumber,
  1031. max ( dem.cnt_all ) cnt_all,
  1032. max ( dem.cnt_adm ) cnt_adm,
  1033. max(dem.max_PUBL)max_PUBL,
  1034. min(dem.min_PUBL)min_PUBL,
  1035. dem.flag_protocol,
  1036. max(prolonflag) prolonflag
  1037. from
  1038. ( select
  1039. p.purchasenumber,
  1040. p.protocolnumber,
  1041. p.type,
  1042. sum (case when d.PROTOCOLNUMBER is not null then 1 else 0 end) cnt_all,
  1043. sum (case when d.admitted is not null then 1 else 0 end ) cnt_adm,
  1044. max(p.max_PUBL)max_PUBL,
  1045. min(p.min_PUBL)min_PUBL,
  1046. case
  1047. when p.type in ('rotocolEOK1','rotocolEOKOU1','rotocolEOKD2','ProtocolEF1') then 1
  1048. when p.type in ('rotocolEOKSingleApp','rotocolEOKOUSingleApp','rotocolEOKDSingleApp','rotocolEFSingleApp')then 2
  1049. when p.type in ('ProtocolEF1') then 4 --- сделано для показателя 6 и 8
  1050. when p.type in ('ProtocolEFInvalidation') then 6 --- сделано для показателя 7 и 8
  1051. when p.type in ('ProtocolEF2') then 5 --- сделано для показателя 7 и 8
  1052. when p.type in ('ProtocolEFSingleApp') then 0 --- сделано для показателя 7 и 8
  1053. else 0 end flag_protocol,
  1054. prolonflag
  1055.  
  1056. from (
  1057.  
  1058. select p.PURCHASENUMBER, p.PROTOCOLNUMBER, p.type, max(p.PUBLISHDATE) max_PUBL, min(p.PUBLISHDATE) min_PUBL ,max(case when p.PUBLISHDATE < prolong.prolongdate then 1 else null end) prolonflag
  1059. from DWH_PROTOCOL_NRPZ p
  1060. left join (SELECT max((docpublishdate))prolongdate, purchasenumber FROM dwh_start_notice_prolong_nrpz group by purchasenumber)prolong on prolong.purchasenumber=p.PURCHASENUMBER
  1061. where p.PUBLISHDATE < to_date('01/01/20', 'DD/MM/YY') and p.PUBLISHDATE > to_date('23/11/18', 'DD/MM/YY') and p.type in ('rotocolEOK1','rotocolEOKOU1','rotocolEOKD2','rotocolEOKSingleApp','rotocolEOKOUSingleApp','rotocolEOKDSingleApp','rotocolEFSingleApp','ProtocolEFSingleApp','ProtocolEF1','ProtocolEF2','ProtocolEFInvalidation')
  1062. --and p.purchasenumber='0372200038319000049'
  1063. Group by p.PURCHASENUMBER, p.PROTOCOLNUMBER, p.type) p
  1064. left join (
  1065. select distinct PURCHASENUMBER, PROTOCOLNUMBER,admitted,JOURNALNUMBER, max(PUBLISHDATE)
  1066. from DWH_PROTOCOL_DEMAND_NRPZ
  1067. where (PURCHASENUMBER, PROTOCOLNUMBER,PUBLISHDATE) in
  1068. (
  1069. select PURCHASENUMBER, PROTOCOLNUMBER, max(PUBLISHDATE)
  1070. from DWH_PROTOCOL_DEMAND_NRPZ
  1071. group by PURCHASENUMBER, PROTOCOLNUMBER
  1072. ) group by
  1073. PURCHASENUMBER, PROTOCOLNUMBER,admitted,JOURNALNUMBER
  1074. ) d
  1075. on p.PROTOCOLNUMBER = d.PROTOCOLNUMBER and p.PURCHASENUMBER = d.PURCHASENUMBER
  1076. group by p.purchasenumber, p.protocolnumber, p.type,prolonflag
  1077. )
  1078. dem
  1079. left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL_NRPZ where type in ('ProtocolCancel','rotocolCancel')) canc on canc.protocolnumber = dem.protocolnumber and canc.PURCHASENUMBER = dem.PURCHASENUMBER
  1080. where canc.protocolnumber is null
  1081. group by dem.purchasenumber,dem.flag_protocol,prolonflag
  1082. union all
  1083. select dem.purchasenumber,
  1084. sum ( dem.cnt_all ) cnt_all,
  1085. sum ( dem.cnt_adm ) cnt_adm,
  1086. max(dem.max_PUBL)max_PUBL,
  1087. min(dem.min_PUBL)min_PUBL,
  1088. dem.flag_protocol,
  1089. prolonflag
  1090. from
  1091. (select
  1092. p.purchasenumber,
  1093. p.protocolnumber,
  1094. p.type,
  1095. sum (case when d.PROTOCOLNUMBER is not null then 1 else 0 end) cnt_all,
  1096. sum (case when d.admitted is not null then 1 else 0 end ) cnt_adm,
  1097. max(max_PUBL)max_PUBL,
  1098. min(min_PUBL)min_PUBL,
  1099. case when p.type in ('rotocolEZP1','rotocolEZK1') then 3 else 0 end flag_protocol,
  1100. prolonflag
  1101. from (
  1102. select p.PURCHASENUMBER, p.PROTOCOLNUMBER, p.type, max(p.PUBLISHDATE) max_PUBL, min(p.PUBLISHDATE) min_PUBL, max(case when p.PUBLISHDATE < prolong.prolongdate then 1 else null end) prolonflag
  1103. from DWH_PROTOCOL_NRPZ p
  1104. left join (SELECT max((docpublishdate))prolongdate, purchasenumber FROM dwh_start_notice_prolong_nrpz group by purchasenumber)prolong on prolong.purchasenumber=p.PURCHASENUMBER
  1105. where p.PUBLISHDATE < to_date('01/01/20', 'DD/MM/YY') and p.PUBLISHDATE > to_date('23/11/18', 'DD/MM/YY') and p.type in ('rotocolEZP1','rotocolEZK1')
  1106. -- and p.purchasenumber='0372200139819000031'
  1107. group by p.purchasenumber, p.protocolnumber, p.type) p
  1108. left join (select distinct PURCHASENUMBER, PROTOCOLNUMBER,admitted, max(PUBLISHDATE), JOURNALNUMBER
  1109. from DWH_PROTOCOL_DEMAND_NRPZ group by PURCHASENUMBER, PROTOCOLNUMBER,admitted,JOURNALNUMBER) d on p.PROTOCOLNUMBER = d.PROTOCOLNUMBER and p.PURCHASENUMBER = d.PURCHASENUMBER
  1110.  
  1111. --where p.purchasenumber='0372200139819000031'
  1112. group by p.purchasenumber, p.protocolnumber, p.type,prolonflag
  1113.  
  1114. )dem
  1115. group by dem.purchasenumber,dem.flag_protocol,prolonflag
  1116. ;
  1117.  
  1118. --12 шаг генерации рейтинга эффективности
  1119. --ERC_19_DEMAND
  1120. drop table erc_19_protocol_194;
  1121. create table erc_19_protocol_194 as
  1122. select ORG_KGNTV, ORG_NAME, PUBLISHDATE_NOTICE, PROTNAME, PROTTYPE, PROTOCOLDATE, SIGNDATE, PUBLISHDATE,PURCHASENUMBER, PROTOCOLNUMBER from erc_19_protocol
  1123. ;
  1124. --13 шаг генерации рейтинга эффективности
  1125. --ERC_19_PROTOCOL
  1126. --drop table erc_19_protocol;
  1127. create table erc_19_protocol as
  1128. select distinct
  1129. filtr.org_kgntv,
  1130. filtr.org_name,
  1131. filtr.PUBLISHDATE PUBLISHDATE_notice,
  1132. p.TITLE protname,
  1133. p.TYPE prottype,
  1134. p.PROTOCOLDATE,
  1135. p.SIGNDATE ,
  1136. p.PUBLISHDATE,
  1137. p.protocolid,
  1138. p.PURCHASENUMBER,
  1139. p.protocolnumber,
  1140. --"0" flag_preg,
  1141. case when pred_prot.purchasenumber is not null then 1 else 0 end flag_preg,
  1142. filtr.scoringdate,
  1143. filtr.enddate
  1144. from
  1145. (select reqnum,
  1146. case when joflag = 0 then org_kgntv else org_kgntv_joflag end org_kgntv,
  1147. case when joflag = 0 then org_name else joflag_org_name end org_name ,
  1148. max(PUBLISHDATE)PUBLISHDATE,
  1149. max(scoringdate) scoringdate,
  1150. max(enddate) enddate
  1151. from erc_19_start_notice
  1152. where flag_st_hand is null
  1153. group by reqnum, case when joflag = 0 then org_kgntv else org_kgntv_joflag end, case when joflag = 0 then org_name else joflag_org_name end
  1154. ) filtr
  1155. join (select pr.TYPE,
  1156. pr.title,
  1157. pr.PURCHASENUMBER,
  1158. pr.protocolnumber,
  1159. pr.SIGNDATE,
  1160. pr.PUBLISHDATE,
  1161. pr.PROTOCOLDATE,
  1162. pr.id protocolid,
  1163. row_number () over (partition by pr.TYPE, pr.PURCHASENUMBER,pr.protocolnumber,pr.id order by pr.PUBLISHDATE desc)rn
  1164. from (select distinct TYPE,
  1165. title,
  1166. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1167. protocolnumber,
  1168. SIGNDATE,
  1169. PUBLISHDATE, id,
  1170. PROTOCOLDATE
  1171. from DWH_PROTOCOL_nrpz
  1172. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY')
  1173. )pr
  1174. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc on canc.protocolnumber = pr.protocolnumber and canc.PURCHASENUMBER = pr.PURCHASENUMBER
  1175. where --canc.protocolnumber is null and
  1176. pr.TYPE in ('ProtocolZPFinal','ProtocolOK1','ProtocolOKOU1','ProtocolOKD4',
  1177. 'ProtocolOKD1',/*'ProtocolZPExtract',*/'ProtocolOKD3','ProtocolEF3',
  1178. 'ProtocolOKOU2','ProtocolOKSingleApp','ProtocolOKDSingleApp',
  1179. 'ProtocolOKOUSingleApp','ProtocolEF1', 'ProtocolZK','ProtocolOK2',
  1180. 'ProtocolOKD5','ProtocolZKAfterProlong','ProtocolEFSingleApp'/*,'ProtocolEFSinglePart'*/,'rotocolEZP2','rotocolEOK1','rotocolEOK2','rotocolEOK3','rotocolEOKOU1','rotocolEOKOU2','rotocolEOKOU3','rotocolEOKD1','rotocolEZP1Extract','rotocolEOKD2','rotocolEOKD3','rotocolEOKSingleApp','rotocolEOKOUSingleApp','rotocolEOKDSingleApp','rotocolEFSingleApp','rotocolEZK1')
  1181. )p on filtr.reqnum = p.PURCHASENUMBER and rn = 1
  1182. left join (select distinct purchasenumber, protocolnumber from erc_19_protocol_194)pred_prot on pred_prot.purchasenumber = p.purchasenumber and pred_prot.protocolnumber = p.protocolnumber
  1183.  
  1184.  
  1185.  
  1186. union all---1
  1187.  
  1188. select distinct
  1189. filtr.org_kgntv,
  1190. filtr.org_name,
  1191. filtr.PUBLISHDATE PUBLISHDATE_notice,
  1192. p.TITLE protname,
  1193. p.TYPE prottype,
  1194. p.PROTOCOLDATE,
  1195. p.SIGNDATE ,
  1196. p.PUBLISHDATE,
  1197. p.protocolid,
  1198. p.PURCHASENUMBER,
  1199. p.protocolnumber,
  1200. --"0" flag_preg,
  1201. case when pred_prot.purchasenumber is not null then 1 else 0 end flag_preg,
  1202. filtr.scoringdate,
  1203. filtr.enddate
  1204. from (select reqnum,
  1205. case when joflag = 0 then org_kgntv else org_kgntv_joflag end org_kgntv,
  1206. case when joflag = 0 then org_name else joflag_org_name end org_name ,
  1207. max(PUBLISHDATE)PUBLISHDATE,
  1208. max(scoringdate) scoringdate,
  1209. max(enddate) enddate
  1210. from erc_19_start_notice
  1211. where flag_st_hand is null
  1212. group by reqnum, case when joflag = 0 then org_kgntv else org_kgntv_joflag end, case when joflag = 0 then org_name else joflag_org_name end
  1213. ) filtr
  1214. join (select pr.TYPE,
  1215. pr.title,
  1216. pr.PURCHASENUMBER,
  1217. pr.protocolnumber,
  1218. pr.SIGNDATE,
  1219. pr.PUBLISHDATE,
  1220. pr.PROTOCOLDATE,
  1221. pr.id protocolid,
  1222. row_number () over (partition by pr.TYPE, pr.PURCHASENUMBER,pr.protocolnumber,pr.id order by pr.PUBLISHDATE desc)rn
  1223. from (select distinct TYPE,
  1224. title,
  1225. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1226. protocolnumber,
  1227. SIGNDATE,
  1228. PUBLISHDATE,
  1229. PROTOCOLDATE,id,
  1230. row_number() over (partition by to_char(PURCHASENUMBER),TYPE order by PUBLISHDATE) rn
  1231. from DWH_PROTOCOL_nrpz
  1232. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('rotocolEZP1Extract','protocolEZPExtract')
  1233. )pr
  1234. where pr.rn = 1
  1235. )p on filtr.reqnum = p.PURCHASENUMBER and rn = 1
  1236. left join (select distinct purchasenumber, protocolnumber from erc_19_protocol_194)pred_prot on pred_prot.purchasenumber = p.purchasenumber and pred_prot.protocolnumber = p.protocolnumber
  1237.  
  1238. union all ---2
  1239. select distinct
  1240. filtr.org_kgntv,
  1241. filtr.org_name,
  1242. filtr.PUBLISHDATE PUBLISHDATE_notice,
  1243. p.TITLE protname,
  1244. p.TYPE prottype,
  1245. p.PROTOCOLDATE,
  1246. p.SIGNDATE ,
  1247. p.PUBLISHDATE,
  1248. p.protocolid,
  1249. p.PURCHASENUMBER,
  1250. p.protocolnumber,
  1251. --"0" flag_preg,
  1252. case when pred_prot.purchasenumber is not null then 1 else 0 end flag_preg,
  1253. filtr.scoringdate,
  1254. filtr.enddate
  1255. from (select reqnum,
  1256. case when joflag = 0 then org_kgntv else org_kgntv_joflag end org_kgntv,
  1257. case when joflag = 0 then org_name else joflag_org_name end org_name ,
  1258. max(PUBLISHDATE)PUBLISHDATE,
  1259. max(scoringdate) scoringdate,
  1260. max(enddate) enddate
  1261. from erc_19_start_notice
  1262. where flag_st_hand is null
  1263. group by reqnum, case when joflag = 0 then org_kgntv else org_kgntv_joflag end, case when joflag = 0 then org_name else joflag_org_name end
  1264. ) filtr
  1265. join (select pr_3.TYPE,
  1266. pr_3.title,
  1267. pr_3.PURCHASENUMBER,
  1268. pr_3.protocolnumber,pr_3.id protocolid,
  1269. pr_3.SIGNDATE,
  1270. pr_3.PUBLISHDATE,
  1271. pr_2.PROTOCOLDATE,
  1272. row_number () over (partition by pr_3.TYPE, pr_3.PURCHASENUMBER,pr_3.protocolnumber,pr_3.id order by pr_3.PUBLISHDATE desc,pr_2.PROTOCOLDATE desc)rn
  1273. from (select distinct TYPE,
  1274. title,
  1275. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1276. protocolnumber,
  1277. SIGNDATE,
  1278. PUBLISHDATE,
  1279. PROTOCOLDATE,id,
  1280. row_number() over (partition by to_char(PURCHASENUMBER),TYPE order by PUBLISHDATE) rn
  1281. from DWH_PROTOCOL_nrpz
  1282. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('ProtocolOKOU3','rotocolOKOU1')
  1283. )pr_3
  1284. left join
  1285. (select distinct TYPE,
  1286. title,
  1287. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1288. protocolnumber,
  1289. SIGNDATE,
  1290. PUBLISHDATE,
  1291. PROTOCOLDATE
  1292. from DWH_PROTOCOL_nrpz
  1293. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('ProtocolOKOU2''rotocolOKOU2')
  1294. )pr_2 on pr_3.PURCHASENUMBER = pr_2.PURCHASENUMBER and pr_3.PUBLISHDATE>=pr_2.PROTOCOLDATE
  1295. where pr_3.rn = 1
  1296. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_3 on canc_3.protocolnumber = pr_3.protocolnumber and canc_3.PURCHASENUMBER = pr_3.PURCHASENUMBER
  1297. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_2 on canc_2.protocolnumber = pr_2.protocolnumber and canc_2.PURCHASENUMBER = pr_2.PURCHASENUMBER
  1298. --where canc_2.protocolnumber is null and canc_3.protocolnumber is null
  1299. )p on filtr.reqnum = p.PURCHASENUMBER and rn = 1
  1300. left join (select distinct purchasenumber, protocolnumber from erc_19_protocol_194)pred_prot on pred_prot.purchasenumber = p.purchasenumber and pred_prot.protocolnumber = p.protocolnumber
  1301.  
  1302.  
  1303. union all --3
  1304. select distinct
  1305. filtr.org_kgntv,
  1306. filtr.org_name,
  1307. filtr.PUBLISHDATE PUBLISHDATE_notice,
  1308. p.TITLE protname,
  1309. p.TYPE prottype,
  1310. p.PROTOCOLDATE,
  1311. p.SIGNDATE ,
  1312. p.PUBLISHDATE,
  1313. p.protocolid,
  1314. p.PURCHASENUMBER,
  1315. p.protocolnumber,
  1316. --"0" flag_preg,
  1317. case when pred_prot.purchasenumber is not null then 1 else 0 end flag_preg,
  1318. filtr.scoringdate,
  1319. filtr.enddate
  1320. from (select reqnum,
  1321. case when joflag = 0 then org_kgntv else org_kgntv_joflag end org_kgntv,
  1322. case when joflag = 0 then org_name else joflag_org_name end org_name ,
  1323. max(PUBLISHDATE)PUBLISHDATE,
  1324. max(scoringdate) scoringdate,
  1325. max(enddate) enddate
  1326. from erc_19_start_notice
  1327. where flag_st_hand is null
  1328. group by reqnum, case when joflag = 0 then org_kgntv else org_kgntv_joflag end, case when joflag = 0 then org_name else joflag_org_name end
  1329. ) filtr
  1330. join (select pr_3.TYPE,
  1331. pr_3.title,
  1332. pr_3.PURCHASENUMBER,
  1333. pr_3.protocolnumber,
  1334. pr_3.id protocolid,
  1335. pr_3.PROTOCOLDATE,
  1336. pr_3.PUBLISHDATE,
  1337. pr_2.SIGNDATE,
  1338. row_number () over (partition by pr_3.TYPE, pr_3.PURCHASENUMBER,pr_3.protocolnumber,pr_3.id order by pr_3.PUBLISHDATE desc,pr_2.SIGNDATE desc)rn
  1339. from (select distinct TYPE,
  1340. title,
  1341. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1342. protocolnumber,
  1343. SIGNDATE,
  1344. PUBLISHDATE,id,
  1345. PROTOCOLDATE
  1346. from DWH_PROTOCOL_nrpz
  1347. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('ProtocolZP','rotocolEZP1')
  1348. )pr_3
  1349. left join
  1350. (select distinct TYPE,
  1351. title,
  1352. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1353. protocolnumber,
  1354. SIGNDATE,
  1355. PUBLISHDATE,
  1356. PROTOCOLDATE
  1357. from DWH_PROTOCOL_nrpz
  1358. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('ProtocolZPFinal','rotocolEZP2')
  1359. )pr_2 on pr_3.PURCHASENUMBER = pr_2.PURCHASENUMBER and pr_3.PUBLISHDATE>=pr_2.SIGNDATE
  1360. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_3 on canc_3.protocolnumber = pr_3.protocolnumber and canc_3.PURCHASENUMBER = pr_3.PURCHASENUMBER
  1361. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_2 on canc_2.protocolnumber = pr_2.protocolnumber and canc_2.PURCHASENUMBER = pr_2.PURCHASENUMBER
  1362. --where canc_2.protocolnumber is null and canc_3.protocolnumber is null
  1363. )p on filtr.reqnum = p.PURCHASENUMBER and rn = 1
  1364. left join (select distinct purchasenumber, protocolnumber from erc_19_protocol_194)pred_prot on pred_prot.purchasenumber = p.purchasenumber and pred_prot.protocolnumber = p.protocolnumber
  1365.  
  1366. union all --4
  1367. select distinct
  1368. filtr.org_kgntv,
  1369. filtr.org_name,
  1370. filtr.PUBLISHDATE PUBLISHDATE_notice,
  1371. p.TITLE protname,
  1372. p.TYPE prottype,
  1373. p.PROTOCOLDATE,
  1374. p.SIGNDATE ,
  1375. p.PUBLISHDATE,
  1376. p.protocolid,
  1377. p.PURCHASENUMBER,
  1378. p.protocolnumber,
  1379. --"0" flag_preg,
  1380. case when pred_prot.purchasenumber is not null then 1 else 0 end flag_preg,
  1381. filtr.scoringdate,
  1382. filtr.enddate
  1383. from (select reqnum,
  1384. case when joflag = 0 then org_kgntv else org_kgntv_joflag end org_kgntv,
  1385. case when joflag = 0 then org_name else joflag_org_name end org_name ,
  1386. max(PUBLISHDATE)PUBLISHDATE,
  1387. max(scoringdate) scoringdate,
  1388. max(enddate) enddate
  1389. from erc_19_start_notice
  1390. where flag_st_hand is null
  1391. group by reqnum, case when joflag = 0 then org_kgntv else org_kgntv_joflag end, case when joflag = 0 then org_name else joflag_org_name end
  1392. ) filtr
  1393. join (select pr_3.TYPE,
  1394. pr_3.title,
  1395. pr_3.PURCHASENUMBER,
  1396. pr_3.protocolnumber,
  1397. pr_3.PROTOCOLDATE,
  1398. pr_3.PUBLISHDATE,pr_3.id protocolid,
  1399. pr_2.PUBLISHDATE SIGNDATE, ---дата рассмотрения заявок на участие в ЭА
  1400. row_number () over (partition by pr_3.TYPE, pr_3.PURCHASENUMBER,pr_3.protocolnumber,pr_3.id order by pr_3.PUBLISHDATE desc,pr_2.PUBLISHDATE desc)rn
  1401. from (select distinct TYPE,
  1402. title,
  1403. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1404. protocolnumber,
  1405. SIGNDATE,
  1406. PUBLISHDATE,id,
  1407. PROTOCOLDATE
  1408. from DWH_PROTOCOL_nrpz
  1409. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('ProtocolEFSinglePart')
  1410. )pr_3
  1411. left join
  1412. (select distinct TYPE,
  1413. title,
  1414. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1415. protocolnumber,
  1416. SIGNDATE,
  1417. PUBLISHDATE,
  1418. PROTOCOLDATE
  1419. from DWH_PROTOCOL_nrpz
  1420. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('ProtocolEF1')
  1421. )pr_2 on pr_3.PURCHASENUMBER = pr_2.PURCHASENUMBER and pr_3.PUBLISHDATE>=pr_2.SIGNDATE
  1422. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_3 on canc_3.protocolnumber = pr_3.protocolnumber and canc_3.PURCHASENUMBER = pr_3.PURCHASENUMBER
  1423. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_2 on canc_2.protocolnumber = pr_2.protocolnumber and canc_2.PURCHASENUMBER = pr_2.PURCHASENUMBER
  1424. --where canc_2.protocolnumber is null and canc_3.protocolnumber is null
  1425. )p on filtr.reqnum = p.PURCHASENUMBER and rn = 1
  1426. left join (select distinct purchasenumber, protocolnumber from erc_19_protocol_194)pred_prot on pred_prot.purchasenumber = p.purchasenumber and pred_prot.protocolnumber = p.protocolnumber
  1427.  
  1428. union all --5
  1429. select distinct
  1430. filtr.org_kgntv,
  1431. filtr.org_name,
  1432. filtr.PUBLISHDATE PUBLISHDATE_notice,
  1433. p.TITLE protname,
  1434. p.TYPE prottype,
  1435. p.PROTOCOLDATE,
  1436. p.SIGNDATE ,
  1437. p.PUBLISHDATE,
  1438. p.protocolid,
  1439. p.PURCHASENUMBER,
  1440. p.protocolnumber,
  1441. --null flag_preg,
  1442. case when pred_prot.purchasenumber is not null then 1 else 0 end flag_preg,
  1443. filtr.scoringdate,
  1444. filtr.enddate
  1445. from (select reqnum,
  1446. case when joflag = 0 then org_kgntv else org_kgntv_joflag end org_kgntv,
  1447. case when joflag = 0 then org_name else joflag_org_name end org_name ,
  1448. max(PUBLISHDATE)PUBLISHDATE,
  1449. max(scoringdate) scoringdate,
  1450. max(enddate) enddate
  1451. from erc_19_start_notice
  1452. where flag_st_hand is null
  1453. group by reqnum, case when joflag = 0 then org_kgntv else org_kgntv_joflag end, case when joflag = 0 then org_name else joflag_org_name end
  1454. ) filtr
  1455. join (select pr_3.TYPE,
  1456. pr_3.title,
  1457. pr_3.PURCHASENUMBER,
  1458. pr_3.protocolnumber,
  1459. pr_3.PROTOCOLDATE,
  1460. pr_3.PUBLISHDATE,pr_3.id protocolid,
  1461. pr_2.PUBLISHDATE SIGNDATE, ---дата рассмотрения заявок на участие в ОКЭФ
  1462. row_number () over (partition by pr_3.TYPE, pr_3.PURCHASENUMBER,pr_3.protocolnumber,pr_3.id order by pr_3.PUBLISHDATE desc,pr_2.PUBLISHDATE desc)rn
  1463. from (select distinct TYPE,
  1464. title,
  1465. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1466. protocolnumber,
  1467. SIGNDATE,
  1468. PUBLISHDATE,id,
  1469. PROTOCOLDATE
  1470. from DWH_PROTOCOL_nrpz
  1471. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('rotocolEOKSinglePart')
  1472. )pr_3
  1473. left join
  1474. (select distinct TYPE,
  1475. title,
  1476. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1477. protocolnumber,
  1478. SIGNDATE,
  1479. PUBLISHDATE,
  1480. PROTOCOLDATE
  1481. from DWH_PROTOCOL_nrpz
  1482. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('rotocolEOK1')
  1483. )pr_2 on pr_3.PURCHASENUMBER = pr_2.PURCHASENUMBER and pr_3.PUBLISHDATE>=pr_2.SIGNDATE
  1484. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_3 on canc_3.protocolnumber = pr_3.protocolnumber and canc_3.PURCHASENUMBER = pr_3.PURCHASENUMBER
  1485. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_2 on canc_2.protocolnumber = pr_2.protocolnumber and canc_2.PURCHASENUMBER = pr_2.PURCHASENUMBER
  1486. --where canc_2.protocolnumber is null and canc_3.protocolnumber is null
  1487. )p on filtr.reqnum = p.PURCHASENUMBER and rn = 1
  1488.  
  1489. left join (select distinct purchasenumber, protocolnumber from erc_19_protocol_194)pred_prot on pred_prot.purchasenumber = p.purchasenumber and pred_prot.protocolnumber = p.protocolnumber
  1490.  
  1491. union all --6
  1492. select distinct
  1493. filtr.org_kgntv,
  1494. filtr.org_name,
  1495. filtr.PUBLISHDATE PUBLISHDATE_notice,
  1496. p.TITLE protname,
  1497. p.TYPE prottype,
  1498. p.PROTOCOLDATE,
  1499. p.SIGNDATE ,
  1500. p.PUBLISHDATE,
  1501. p.protocolid,
  1502. p.PURCHASENUMBER,
  1503. p.protocolnumber,
  1504. --null flag_preg,
  1505. case when pred_prot.purchasenumber is not null then 1 else 0 end flag_preg,
  1506. filtr.scoringdate,
  1507. filtr.enddate
  1508. from (select reqnum,
  1509. case when joflag = 0 then org_kgntv else org_kgntv_joflag end org_kgntv,
  1510. case when joflag = 0 then org_name else joflag_org_name end org_name ,
  1511. max(PUBLISHDATE)PUBLISHDATE,
  1512. max(scoringdate) scoringdate,
  1513. max(enddate) enddate
  1514. from erc_19_start_notice
  1515. where flag_st_hand is null
  1516. group by reqnum, case when joflag = 0 then org_kgntv else org_kgntv_joflag end, case when joflag = 0 then org_name else joflag_org_name end
  1517. ) filtr
  1518. join (select pr_3.TYPE,
  1519. pr_3.title,
  1520. pr_3.PURCHASENUMBER,
  1521. pr_3.protocolnumber,
  1522. pr_3.PROTOCOLDATE,pr_3.id protocolid,
  1523. pr_3.PUBLISHDATE,
  1524. pr_2.PUBLISHDATE SIGNDATE, ---дата рассмотрения заявок на участие в ОКЭФ с ограниченным участием
  1525. row_number () over (partition by pr_3.TYPE, pr_3.PURCHASENUMBER,pr_3.protocolnumber,pr_3.id order by pr_3.PUBLISHDATE desc,pr_2.PUBLISHDATE desc)rn
  1526. from (select distinct TYPE,
  1527. title,
  1528. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1529. protocolnumber,
  1530. SIGNDATE,
  1531. PUBLISHDATE,id,
  1532. PROTOCOLDATE
  1533. from DWH_PROTOCOL_nrpz
  1534. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('rotocolEOKOUSinglePart')
  1535. )pr_3
  1536. left join
  1537. (select distinct TYPE,
  1538. title,
  1539. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1540. protocolnumber,
  1541. SIGNDATE,
  1542. PUBLISHDATE,
  1543. PROTOCOLDATE
  1544. from DWH_PROTOCOL_nrpz
  1545. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('rotocolEOKOU1')
  1546. )pr_2 on pr_3.PURCHASENUMBER = pr_2.PURCHASENUMBER and pr_3.PUBLISHDATE>=pr_2.SIGNDATE
  1547. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_3 on canc_3.protocolnumber = pr_3.protocolnumber and canc_3.PURCHASENUMBER = pr_3.PURCHASENUMBER
  1548. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_2 on canc_2.protocolnumber = pr_2.protocolnumber and canc_2.PURCHASENUMBER = pr_2.PURCHASENUMBER
  1549. --where canc_2.protocolnumber is null and canc_3.protocolnumber is null
  1550. )p on filtr.reqnum = p.PURCHASENUMBER and rn = 1
  1551. left join (select distinct purchasenumber, protocolnumber from erc_19_protocol_194)pred_prot on pred_prot.purchasenumber = p.purchasenumber and pred_prot.protocolnumber = p.protocolnumber
  1552. union all --7
  1553. select distinct
  1554. filtr.org_kgntv,
  1555. filtr.org_name,
  1556. filtr.PUBLISHDATE PUBLISHDATE_notice,
  1557. p.TITLE protname,
  1558. p.TYPE prottype,
  1559. p.PROTOCOLDATE,
  1560. p.SIGNDATE ,
  1561. p.PUBLISHDATE,
  1562. p.protocolid,
  1563. p.PURCHASENUMBER,
  1564. p.protocolnumber,
  1565. --null flag_preg,
  1566. case when pred_prot.purchasenumber is not null then 1 else 0 end flag_preg,
  1567. filtr.scoringdate,
  1568. filtr.enddate
  1569. from (select reqnum,
  1570. case when joflag = 0 then org_kgntv else org_kgntv_joflag end org_kgntv,
  1571. case when joflag = 0 then org_name else joflag_org_name end org_name ,
  1572. max(PUBLISHDATE)PUBLISHDATE,
  1573. max(scoringdate) scoringdate,
  1574. max(enddate) enddate
  1575. from erc_19_start_notice
  1576. where flag_st_hand is null
  1577. group by reqnum, case when joflag = 0 then org_kgntv else org_kgntv_joflag end, case when joflag = 0 then org_name else joflag_org_name end
  1578. ) filtr
  1579. join (select pr_3.TYPE,
  1580. pr_3.title,
  1581. pr_3.PURCHASENUMBER,
  1582. pr_3.protocolnumber,pr_3.id protocolid,
  1583. pr_3.PROTOCOLDATE,
  1584. pr_3.PUBLISHDATE,
  1585. pr_2.PUBLISHDATE SIGNDATE, ---дата рассмотрения заявок на участие в ОКДЭФ (двухэтапный конкурс в электронной форме)
  1586. row_number () over (partition by pr_3.TYPE, pr_3.PURCHASENUMBER,pr_3.protocolnumber,pr_3.id order by pr_3.PUBLISHDATE desc,pr_2.PUBLISHDATE desc)rn
  1587. from (select distinct TYPE,
  1588. title,
  1589. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1590. protocolnumber,
  1591. SIGNDATE,
  1592. PUBLISHDATE,id,
  1593. PROTOCOLDATE
  1594. from DWH_PROTOCOL_nrpz
  1595. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('rotocolEOKDSinglePart')
  1596. )pr_3
  1597. left join
  1598. (select distinct TYPE,
  1599. title,
  1600. to_char(PURCHASENUMBER)PURCHASENUMBER,
  1601. protocolnumber,
  1602. SIGNDATE,
  1603. PUBLISHDATE,
  1604. PROTOCOLDATE
  1605. from DWH_PROTOCOL_nrpz
  1606. where PUBLISHDATE < to_date('01.01.2020', 'dd.MM.YYYY') and pUBLISHDATE >= to_date('23.11.2018', 'dd.MM.YYYY') and TYPE in ('rotocolEOKD1')
  1607. )pr_2 on pr_3.PURCHASENUMBER = pr_2.PURCHASENUMBER and pr_3.PUBLISHDATE>=pr_2.SIGNDATE
  1608. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_3 on canc_3.protocolnumber = pr_3.protocolnumber and canc_3.PURCHASENUMBER = pr_3.PURCHASENUMBER
  1609. --left join (select distinct protocolnumber, PURCHASENUMBER from DWH_PROTOCOL where type in ('ProtocolCancel')) canc_2 on canc_2.protocolnumber = pr_2.protocolnumber and canc_2.PURCHASENUMBER = pr_2.PURCHASENUMBER
  1610. --where canc_2.protocolnumber is null and canc_3.protocolnumber is null
  1611. )p on filtr.reqnum = p.PURCHASENUMBER and rn = 1
  1612. left join (select distinct purchasenumber, protocolnumber from erc_19_protocol_194)pred_prot on pred_prot.purchasenumber = p.purchasenumber and pred_prot.protocolnumber = p.protocolnumber;
  1613. --
  1614. drop table erc_19_protocol_prim;
  1615. create table erc_19_protocol_prim as
  1616. select * from erc_19_protocol;
  1617.  
  1618. drop table erc_19_protocol;
  1619. create table erc_19_protocol as
  1620. select distinct pp.ORG_KGNTV,
  1621. pp.ORG_NAME,
  1622. pp.PUBLISHDATE_NOTICE,
  1623. pp.PROTNAME,
  1624. pp.PROTTYPE,
  1625. case when pp.PROTTYPE like 'Protocol%' then pp.PUBLISHDATE else pp.PROTOCOLDATE end PROTOCOLDATE,
  1626. pp.SIGNDATE,
  1627. case when pp.PROTTYPE like 'Protocol%' then pp.PROTOCOLDATE else pp.PUBLISHDATE end PUBLISHDATE,
  1628. pp.PURCHASENUMBER,
  1629. pp.PROTOCOLNUMBER ,
  1630. pp.flag_preg,
  1631. pp.scoringdate,
  1632. pp.enddate,
  1633. pp.protocolid
  1634. from erc_19_protocol_prim pp;
  1635.  
  1636. drop table erc_19_protocol_prim;
  1637. create table erc_19_protocol_prim as
  1638. select * from erc_19_protocol;
  1639.  
  1640. drop table erc_19_protocol;
  1641. create table erc_19_protocol as
  1642. select distinct pp.ORG_KGNTV,
  1643. pp.ORG_NAME,
  1644. pp.PUBLISHDATE_NOTICE,
  1645. pp.PROTNAME,
  1646. pp.PROTTYPE,
  1647. pp.PROTOCOLDATE,
  1648. pp.SIGNDATE,
  1649. case when pc.reqnum is not null then pc.PUBLISHDATE else pp.PUBLISHDATE end PUBLISHDATE,
  1650. pp.PURCHASENUMBER,
  1651. pp.PROTOCOLNUMBER ,
  1652. pp.flag_preg,
  1653. pp.scoringdate,
  1654. pp.enddate,
  1655. pp.protocolid
  1656. from erc_19_protocol_prim pp
  1657. left join erc_19_protocol_ch pc on pp.protocolid = pc.reqnum and pp.prottype = pc.type;
  1658.  
  1659. drop table erc_19_protocol_prim;
  1660. create table erc_19_protocol_prim as
  1661. select * from erc_19_protocol;
  1662.  
  1663. drop table erc_19_protocol;
  1664. create table erc_19_protocol as
  1665. select distinct pp.ORG_KGNTV,
  1666. pp.ORG_NAME,
  1667. pp.PUBLISHDATE_NOTICE,
  1668. pp.PROTNAME,
  1669. pp.PROTTYPE,
  1670. case when pc.reqnum is not null then pc.PROTOCOLDATE else pp.PROTOCOLDATE end PROTOCOLDATE,
  1671. pp.SIGNDATE,
  1672. pp.PUBLISHDATE,
  1673. pp.PURCHASENUMBER,
  1674. pp.PROTOCOLNUMBER ,
  1675. pp.flag_preg,
  1676. pp.scoringdate,
  1677. pp.enddate,
  1678. pp.protocolid
  1679. from erc_19_protocol_prim pp
  1680. left join erc_19_protocol_hc pc on pp.protocolid = pc.reqnum and pp.prottype = pc.type
  1681. --13 шаг генерации рейтинга эффективности
  1682. --ERC_19_PROTOCOL
  1683.  
  1684. --14 шаг генерации рейтинга эффективности
  1685. --ERC_19_F13
  1686. --drop table erc_19_f13;
  1687. create table erc_19_f13 as select * from erc_19_f13_view;
  1688.  
  1689. drop table erc_19_f15;
  1690. create table erc_19_f15 as select * from erc_19_f15_view;
  1691. --14 шаг генерации рейтинга эффективности
  1692. --ERC_19_F13
  1693.  
  1694. --15 шаг генерации рейтинга эффективности
  1695. --ERC_19_RESULT
  1696. --drop table erc_19_result;
  1697. create table erc_19_result as
  1698. select eo.orgtitle fgrbsname, eo.ORGID_KGNTV grbsid,eo.ISREGADM,
  1699. sum(f1.n1) f1n1, sum(f1.n2) f1n2,
  1700. sum(f2.n1) f2n1, sum(f2.n2) f2n2,
  1701. sum(f3.n1) f3n1, sum(f3.n2) f3n2,
  1702. sum(f4.n1) f4n1, sum(f4.n2) f4n2,
  1703. sum(f5.n1) f5n1, sum(f5.n2) f5n2,
  1704. sum(f6.n1) f6n1, sum(f6.n2) f6n2,
  1705. sum(f7.n1) f7n1, sum(f7.n2) f7n2,
  1706. sum(f8.n1) f8n1, sum(f8.n2) f8n2,
  1707. sum(f9.n1) f9n1, sum(f9.n2) f9n2,
  1708. sum(f10.n1) f10n1, sum(f10.n2) f10n2,
  1709. sum(f11.n1) f11n1, sum(f11.n2) f11n2,
  1710. sum(f12.n1) f12n1, sum(f12.n2) f12n2,
  1711. sum(f13.n1) f13n1, sum(f13.n2) f13n2,
  1712. sum(f14.n1) f14n1, sum(f14.n2) f14n2,
  1713. sum(f15.n1) f15n1, sum(f15.n2) f15n2,
  1714. sum(f16.n1) f16n1, sum(f16.n2) f16n2,
  1715. sum(f17.n1) f17n1, sum(f17.n2) f17n2,
  1716. sum(f18.n1) f18n1, sum(f18.n2) f18n2,
  1717. sum(f19.n1) f19n1, sum(f19.n2) f19n2
  1718. from erc_dwh_organization_kgntv dok
  1719. inner join erc_dwh_organization_kgntv dokgrbs on dokgrbs.id = dok.parentid and dokgrbs.id <> 3039
  1720. inner join ERC_ORGANIZATION eo on eo.spz = dokgrbs.spz
  1721. left join ERC_19_f1 f1 on f1.org = to_char(dok.id)
  1722. left join ERC_19_f2 f2 on f2.org = to_char(dok.id)
  1723. left join ERC_19_f3 f3 on f3.org = to_char(dok.id)
  1724. left join ERC_19_f4 f4 on f4.org = to_char(dok.id)
  1725. left join ERC_19_f5 f5 on f5.org = to_char(dok.id)
  1726. left join ERC_19_f6 f6 on f6.org = to_char(dok.id)
  1727. left join ERC_19_f7 f7 on f7.org = to_char(dok.id)
  1728. left join ERC_19_f8 f8 on f8.org = to_char(dok.id)
  1729. left join ERC_19_f9 f9 on f9.org = to_char(dok.id)
  1730. left join ERC_19_f10 f10 on f10.org = to_char(dok.id)
  1731. left join ERC_19_f11 f11 on f11.org = to_char(dok.id)
  1732. left join ERC_19_f12 f12 on f12.org = to_char(dok.id)
  1733. left join ERC_19_f13 f13 on f13.org = to_char(dok.id)
  1734. left join ERC_19_f14 f14 on f14.org = to_char(dok.id)
  1735. left join ERC_19_f15 f15 on f15.org = to_char(dok.id)
  1736. left join ERC_19_f16 f16 on f16.org = to_char(dok.id)
  1737. left join ERC_19_f17 f17 on f17.org = to_char(dok.id)
  1738. left join ERC_19_f18 f18 on f18.org = to_char(dok.id)
  1739. left join ERC_19_f19 f19 on f19.org = to_char(dok.id)
  1740. group by eo.ORGID_KGNTV, eo.orgtitle ,eo.ISREGADM
  1741. order by eo.ISREGADM desc, eo.orgtitle;
  1742.  
  1743. --yne zabudi zdelati!!!!!!!!!
  1744. drop table erc_19_result_org;
  1745. create table erc_19_result_org as
  1746. select eo.orgtitle fgrbsname, eo.ORGID_KGNTV grbsid,eo.ISREGADM,
  1747. dok.full_name org_name,dok.id org_id, case when dok.parentid = dok.id then 1 else 0 end grbs_flag,
  1748. sum(f1.n1) f1n1, sum(f1.n2) f1n2,
  1749. sum(f2.n1) f2n1, sum(f2.n2) f2n2,
  1750. sum(f3.n1) f3n1, sum(f3.n2) f3n2,
  1751. sum(f4.n1) f4n1, sum(f4.n2) f4n2,
  1752. sum(f5.n1) f5n1, sum(f5.n2) f5n2,
  1753. sum(f6.n1) f6n1, sum(f6.n2) f6n2,
  1754. sum(f7.n1) f7n1, sum(f7.n2) f7n2,
  1755. sum(f8.n1) f8n1, sum(f8.n2) f8n2,
  1756. sum(f9.n1) f9n1, sum(f9.n2) f9n2,
  1757. sum(f10.n1) f10n1, sum(f10.n2) f10n2,
  1758. sum(f11.n1) f11n1, sum(f11.n2) f11n2,
  1759. sum(f12.n1) f12n1, sum(f12.n2) f12n2,
  1760. sum(f13.n1) f13n1, sum(f13.n2) f13n2,
  1761. sum(f14.n1) f14n1, sum(f14.n2) f14n2,
  1762. sum(f15.n1) f15n1, sum(f15.n2) f15n2,
  1763. sum(f16.n1) f16n1, sum(f16.n2) f16n2,
  1764. sum(f17.n1) f17n1, sum(f17.n2) f17n2
  1765. from erc_dwh_organization_kgntv dok
  1766. inner join erc_dwh_organization_kgntv dokgrbs on dokgrbs.id = dok.parentid
  1767. inner join ERC_ORGANIZATION eo on eo.spz = dokgrbs.spz
  1768. left join ERC_19_f1 f1 on f1.org = to_char(dok.id)
  1769. left join ERC_19_f2 f2 on f2.org = to_char(dok.id)
  1770. left join ERC_19_f3 f3 on f3.org = to_char(dok.id)
  1771. left join ERC_19_f4 f4 on f4.org = to_char(dok.id)
  1772. left join ERC_19_f5 f5 on f5.org = to_char(dok.id)
  1773. left join ERC_19_f6 f6 on f6.org = to_char(dok.id)
  1774. left join ERC_19_f7 f7 on f7.org = to_char(dok.id)
  1775. left join ERC_19_f8 f8 on f8.org = to_char(dok.id)
  1776. left join ERC_19_f9 f9 on f9.org = to_char(dok.id)
  1777. left join ERC_19_f10 f10 on f10.org = to_char(dok.id)
  1778. left join ERC_19_f11 f11 on f11.org = to_char(dok.id)
  1779. left join ERC_19_f12 f12 on f12.org = to_char(dok.id)
  1780. left join ERC_19_f13 f13 on f13.org = to_char(dok.id)
  1781. left join ERC_19_f14 f14 on f14.org = to_char(dok.id)
  1782. left join ERC_19_f15 f15 on f15.org = to_char(dok.id)
  1783. left join ERC_19_f16 f16 on f16.org = to_char(dok.id)
  1784. left join ERC_19_f17 f17 on f17.org = to_char(dok.id)
  1785. group by eo.ORGID_KGNTV, eo.orgtitle ,eo.ISREGADM , dok.full_name ,case when dok.parentid = dok.id then 1 else 0 end,dok.id
  1786. order by eo.ISREGADM desc, eo.orgtitle,dok.full_name;
  1787. --15 шаг генерации рейтинга эффективности
  1788. --ERC_19_RESULT
  1789.  
  1790. --16 шаг генерации рейтинга эффективности
  1791. --ERC_19_DATA_LIST
  1792. --drop table erc_19_data_list;
  1793. create table erc_19_data_list as
  1794. select eo.orgtitle grbstitle, --ГРБС
  1795. rl.ORG_NAME, --Заказчик
  1796. rl.ORG_KGNTV, --Заказчик ID
  1797. rl.PLANNUMBER, --Реестровый номер ПГ
  1798. rl.VERSIONNUMBER, --Версия ПГ
  1799. rl.PUBLISHDATE, --Дата публикации ПГ
  1800. rl.POSITIONNUMBER, --Реестровый номер позиции ПГ
  1801. rl.IKZ, --ИКЗ из ПГ
  1802. rl.IKZ_PP,--ИКЗ из ПЗ
  1803. rl.PLAN_PLACEMENT_DATE, --Плановая дата размещения ПГ
  1804. rl.PLAN_EXECUTION_DATE, --Плановая дата исполнения ПГ
  1805. rl.OBJECT_NAME,--объект закупки ПГ
  1806. rl.SOP_NAME, --Способ определения поставщика в ПГ
  1807. rl.NMC_SCHEDULE, --НМЦ ПГ
  1808. rl.PURCHASECANCELED, --позиция отменена в ПГ
  1809. --rl.FLAG_COMP, --Конкурентный СОП в ПГ
  1810. rl.CNT_MODIF_PG, --Число изменений позиции в ПГ
  1811. rl.REQNUM, --Реестровый номер извещения
  1812. rl.PUBLISHDATE_REQNUM,--Дата публикации извещения
  1813. rl.SOP_NAME_REQNUM,--Способ определения поставщика в извещении
  1814. rl.JOFLAG_ORG_NAME, --Организатор совместной закупки
  1815. rl.JOFLAG, --Часть совместной закупки
  1816. --rl.STARTDATE,
  1817. --rl.ENDDATE,
  1818. rl.NMC_REQNUM, --НМЦ в извещении
  1819. rl.NMC_JOFLAG, --НМЦ совместной закупки
  1820. --rl.BIDDINGDATE,
  1821. --rl.OPENINGDATE,
  1822. --rl.SCORINGDATE,
  1823. --rl.PREQUALIFICATION,
  1824. rl.LOTNUMBER, --Номер лота
  1825. --rl.POSITIONNUMBER_REQNUM,
  1826. rl.FLAG_COMP_REQNUM, --Конкурентный СОП
  1827. --rl.IKZ_REQNUM,
  1828. --rl.PLANNUMBER_REQNUM,
  1829. rl.OBJECT_NAME_REQNUM, --Объект закупки в извещении
  1830. rl.CNT_MODIF_REQNUM, --Число изменений извещения
  1831. rl.RNK, --Реестровый номер контракта
  1832. --rl.RNK_NUMBER,
  1833. --rl.IKZ_CON,
  1834. rl.SIGNDATE, --Дата подписания контракта
  1835. rl.PUBLISHDATE_CON, --Дата публикации контракта
  1836. --rl.PUBLISHDATE_CON_LAST,
  1837. --rl.VERSIONNUMBER_CON,
  1838. --rl.VERSIONNUMBER_CON_LAST,
  1839. --rl.REQNUM_CON,
  1840. --rl.LOTNUMBER_CON,
  1841. rl.SOP_NAME_CON, --Способ определения поставщика в контракте
  1842. rl.ONEEX_CON, --Обоснование у ед.поставщика
  1843. rl.PROTOCOLDATE_CON, --Дата протокола основания
  1844. rl.CK_FIRST, --Цена контракта
  1845. rl.CK_LAST, --Цена контракта на отчетную дату
  1846. rl.STAGE_CON, --Состояние контракта
  1847. rl.PLAN_EXECUTION_DATE_CON, --Плановая дата исполнения контракта из контракта
  1848. rl.EXECUTIONS_DATE, --Дата исполнения
  1849. rl.REJECTED_DATE,--Дата растордения
  1850. --rl.REJECTED_PAID,
  1851. --rl.REJECTED_REASON,
  1852. --rl.REJECTED_REASON_NAME,
  1853. --rl.PENALTIES_TYPE,
  1854. --rl.PENALTIES_PARTY,
  1855. --rl.PENALTIES_AMOUNT,
  1856. rl.PENALTIES_PARTY, --Штраф наличие
  1857. --rl.SUPPLIER_TYPE,
  1858. rl.SUPPLIER_FULLNAME, --Поставщик
  1859. rl.SUPPLIER_INN, --Поставщик ИНН
  1860. --rl.SUPPLIER_KPP,
  1861. rl.OBJECT_NAME_CON, --Объект закупки в контракте
  1862. --rl.ORG_NAME_CON,
  1863. --rl.ORG_INN_CON,
  1864. --rl.ORG_SPZ_CON,
  1865. --rl.ORG_KGNTV_CONTRACT,
  1866. rl.PROTOCOLDATE_PUBL,
  1867. rl.PROTOCOLDATE_SIGN,
  1868. rl.PROTOCOLDATE_ONE_PUBL,
  1869. rl.PROTOCOLDATE_ONE_SIGN,
  1870. rl.FIRSTNOTICESUCCESDATE,
  1871. case when rl.purchasenumber_rn = 1 then 1 else 0 end purchasenumber_rn,
  1872. ais.lotuuid, ais.requestid,
  1873. --rl.FLAG_16, --Флаг СОП на 16 показатель
  1874. rl.CNT_MODIF_CON, --Число изменений по контракту
  1875. rl.flag_evasion,
  1876. case when rl.flag_comp_reqnum = 1 and rl.purchasenumber_rn = 1 then rl.nmc_reqnum else 0 end f1n1,
  1877. case when nvl(rl.flag_comp_reqnum, rl.flag_comp) = 1 and (rl.flag_comp_reqnum = 1 or rl.purchasecanceled is null) and (rl.purchasenumber_rn = 1 or rl.purchasenumber_rn is null) then nvl(rl.nmc_reqnum, rl.nmc_schedule) else 0 end f1n2,
  1878. case when rl.flag_comp = 1 then rl.cnt_modif_pg else 0 end f2n1,
  1879. case when rl.flag_comp = 1 then 1 else 0 end f2n2,
  1880. case when rl.flag_comp_reqnum = 1 and rl.rnk is not null then 1 else 0 end f3n1,
  1881. case when rl.rnk is not null and co.rnk is null then 1 else 0 end f3n2,
  1882.  
  1883. case when rl.flag_comp_reqnum = 1 and rl.rnk is not null and (lower(rl.sop_name_reqnum) like '%аукцион%' or lower(rl.sop_name_reqnum) like '%конкурс%') then ck_first else 0 end f5n1,
  1884. case when rl.flag_comp_reqnum = 1
  1885. and (lower(rl.sop_name_reqnum) like '%аукцион%' or lower(rl.sop_name_reqnum) like '%конкурс%' or lower(rl.sop_name_reqnum) like '%котировок в электронной%')
  1886. and (rl.flag_st_hand is null or rl.cnt_modif_reqnum = 0) then 1 else 0 end f9n2,
  1887.  
  1888. case when rl.flag_comp_reqnum = 1 then 1 else 0 end f10n2, /*не учитывается*/
  1889. case when rl.flag_comp_reqnum = 1 and rl.rnk is not null and oneex_con is not null then 1 else 0 end f11n1,
  1890. case when rl.flag_comp_reqnum = 1 and rl.rnk is not null then 1 else 0 end f11n2,
  1891. case when rl.flag_comp_reqnum = 1 and rl.rnk is not null then rl.nmc_reqnum else 0 end f14n1,
  1892. case when rl.flag_comp_reqnum = 1 and rl.rnk is not null then ck_first else 0 end f14n2,
  1893. ---------------------------------------------------------------------------------------------------
  1894. case when rl.flag_comp_reqnum = 1 and rl.rnk is not null and
  1895. case when rl.protocoldate_publ is null and rl.protocoldate_sign is null and rl.protocoldate_one_publ is null and rl.protocoldate_one_SIGN is null then 0
  1896. --добавить обработку протокола ProtocolEvasion как в тестовом рейтинге 16 года
  1897. when (rl.sop_name_reqnum like '%лектронный аукцион%' or
  1898. rl.sop_name_reqnum like '%Открытый конкурс%' or
  1899. rl.sop_name_reqnum like '%Конкурс с ограниченным участием%' or
  1900. rl.sop_name_reqnum like '%Двухэтапный конкурс%' )
  1901. and (trunc( rl.SIGNDATE) - trunc(rl.protocoldate_publ) >= 10 )
  1902. and
  1903. trunc(rl.SIGNDATE) <=
  1904. (
  1905. Select min(t.date_)--, min(t1.date_)
  1906. From work_days_of_2019 t Inner Join work_days_of_2019 t1 On (t.date_>t1.date_ And t.nm=t1.nm+9)
  1907. Where t1.date_>= (select min(date_) + interval '5' day
  1908. from work_days_all
  1909. where DATE_ >= (trunc(rl.protocoldate_publ) + interval '5' day) and type=1)
  1910. )
  1911. then 0
  1912. when (rl.sop_name_reqnum like 'Запрос котировок в электронной форме%' or
  1913. rl.sop_name_reqnum like 'Запрос предложений в электронной форме%')
  1914. and (trunc( rl.SIGNDATE) - trunc(rl.protocoldate_publ) >= 7 )
  1915. and
  1916. trunc(rl.SIGNDATE) <=
  1917. (
  1918. Select min(t.date_)--, min(t1.date_)
  1919. From work_days_of_2019 t Inner Join work_days_of_2019 t1 On (t.date_>t1.date_ And t.nm=t1.nm+9)
  1920. Where t1.date_>= (select min(date_) + interval '5' day
  1921. from work_days_all
  1922. where DATE_ >= (trunc(rl.protocoldate_publ) + interval '5' day) and type=1)
  1923. )
  1924.  
  1925. then 0
  1926.  
  1927. else 1 end = 1
  1928. then 1 else 0 end f15n1,
  1929. ------------------------------
  1930. case when rl.flag_comp_reqnum = 1 and rl.rnk is not null and rl.flag_evasion = 0 then 1 else 0 end f15n2,
  1931. case when ( select sum ( days.type )
  1932. from work_days_all days
  1933. where days.date_ > trunc(rl.SIGNDATE)
  1934. and days.date_ < trunc(rl.FIRSTNOTICEsuccesDATE) ) >= 3
  1935. and ( rl.flag_16 = 1)
  1936. and rl.rnk is not null
  1937. and trunc(rl.SIGNDATE)<'27.06.2018'
  1938. or
  1939. ( select sum ( days.type )
  1940. from work_days_all days
  1941. where days.date_ > trunc(rl.SIGNDATE)
  1942. and days.date_ < trunc(rl.FIRSTNOTICEsuccesDATE) ) >= 5
  1943. and ( rl.flag_16 = 1)
  1944. and rl.rnk is not null
  1945. and trunc(rl.SIGNDATE)>='27.06.2018'
  1946. then 1 else 0 end f16n1,
  1947. case when ( rl.flag_16 = 1) and rl.rnk is not null then 1 else 0 end f16n2 ,
  1948. rl.type_
  1949. from erc_19_list_contract rl
  1950. join erc_dwh_organization_kgntv dok
  1951. on to_char(dok.id) = rl.org_kgntv
  1952. join erc_dwh_organization_kgntv dokgrbs
  1953. on dokgrbs.id = dok.parentid
  1954. join ERC_ORGANIZATION eo
  1955. on eo.spz = dokgrbs.spz
  1956. left join erc_19_contract_oneex co on co.rnk = rl.rnk
  1957. left join
  1958. (select ikz,
  1959. listagg(lotuuid, '; ') within group (order by ikz) lotuuid,
  1960. listagg(requestid, '; ') within group (order by ikz) requestid
  1961. from
  1962. (select ikz,lotuuid,requestid, row_number () over (partition by ikz order by 1) rn
  1963. from (select distinct ikz,lotuuid,requestid from erc_dwh_procedures_kgntv_194_6 where ikz is not null) p
  1964. )
  1965. where rn < 100
  1966. group by ikz
  1967. )ais on ais.ikz = rl.IKZ
  1968. where rl.SOP_NAME <> 'Запрос котировок'
  1969. ;
  1970.  
  1971. --16 шаг генерации рейтинга эффективности
  1972. --16 шаг генерации рейтинга эффективности
  1973. --ERC_19_DATA_LIST
  1974.  
  1975. --17 шаг генерации рейтинга эффективности
  1976. --ERC_19_DATA_CONTRACT_19
  1977. --drop table erc_19_data_contract_19;
  1978. create table erc_19_data_contract_19 as
  1979. select
  1980. eo.orgtitle grbstitle,
  1981. sc.org_kgntv,
  1982. org_name,
  1983. sc.rnk,
  1984. plan_execution_date_con,
  1985. executions_date,
  1986. rejected_date,
  1987. penalties_party,
  1988. ck_first,
  1989. flag_comp_reqnum flag_3_n1,
  1990. /*case when trunc ( sc.plan_execution_date_con ) < to_date('01-04-2018','DD-MM-YYYY')
  1991. and( case
  1992. when sc.executions_date is not null and trunc ( sc.executions_date ) > trunc ( sc.work_days ) --and trunc ( sc.executions_date ) < to_date('01-01-2018','DD-MM-YYYY')
  1993. then 1
  1994. when sc.executions_date is null and trunc (sc.work_days) < trunc ( sc.rejected_date ) --and trunc ( sc.rejected_date ) < to_date('01-01-2018','DD-MM-YYYY')
  1995. then 1
  1996. when sc.executions_date is null and sc.rejected_date is null and nvl(pay_.pay_,-100)<sc.ck_last
  1997. then 1
  1998. else 0 end = 1
  1999. )
  2000. and (nvl(penalties_party,' ')<>'Поставщик') then 1 else 0 end*/
  2001. case when trunc ( sc.plan_execution_date_con /*work_days*/ ) < to_date('01-01-2020','DD-MM-YYYY')
  2002. and (nvl(penalties_party,' ')<>'Поставщик')
  2003. and( case
  2004. when sc.executions_date is not null and trunc ( sc.executions_date ) > trunc ( sc.work_days )
  2005. /*and (nvl(penalties_party,' ')<>'Поставщик')*/ and nvl(pay_.pay_,-100)<sc.ck_last--and trunc ( sc.executions_date ) < to_date('01-01-2018','DD-MM-YYYY')
  2006. then 1
  2007. when sc.executions_date is null and trunc (sc.work_days) < trunc ( sc.rejected_date ) --and trunc ( sc.rejected_date ) < to_date('01-01-2018','DD-MM-YYYY')
  2008. then 1
  2009. when sc.executions_date is null and sc.rejected_date is null and nvl(pay_.pay_,-100)<sc.ck_last
  2010. then 1
  2011. else 0 end = 1
  2012. ) then 1 else 0 end flag_n1,
  2013.  
  2014.  
  2015. case when trunc ( sc.plan_execution_date_con ) < to_date('01-01-2020','DD-MM-YYYY') then 1 else 0 end flag_n2,
  2016. case when co.rnk is not null then 1 else 0 end flag_oneex,ck_last,pay_.pay_,pay_.execdocdate, sc.type_,
  2017. sc.oneex_con, sc.object_name,sc.sop_name,
  2018. ais.lotuuid, ais.requestid, ais.contractnoticenumber,
  2019. sc.signdate,
  2020. sc.work_days
  2021. from
  2022. (select rejected_date, executions_date,plan_execution_date_con, rnk , org_kgntv, penalties_party, org_name, ck_first,nvl(flag_comp_reqnum,0) flag_comp_reqnum,ck_last,type_,oneex_con, object_name,sop_name,signdate,
  2023. nvl(w.work_days,c.plan_execution_date_con) work_days
  2024. from erc_19_list_contract c
  2025. left join erc_work_days w on w.date_ = c.plan_execution_date_con
  2026. union all
  2027. select rejected_date, executions_date,EXECUTIONPERIOD_END, rnk ,to_number( ORG_KGNTV)ORG_KGNTV, penalties_party, c.org_name, price,0, price_cur, 'aaa', singlecustomer_name, object_name,placingway_name, signdate,
  2028. nvl(w.work_days,c.EXECUTIONPERIOD_END) work_days
  2029. from erc_19_contract_kg c
  2030. left join erc_work_days w on w.date_ = c.EXECUTIONPERIOD_END
  2031. ) sc
  2032. join erc_dwh_organization_kgntv dok
  2033. on to_char(dok.id) = sc.org_kgntv
  2034. join erc_dwh_organization_kgntv dokgrbs
  2035. on dokgrbs.id = dok.parentid
  2036. join ERC_ORGANIZATION eo
  2037. on eo.spz = dokgrbs.spz
  2038. left join erc_19_contract_oneex co on co.rnk = sc.rnk
  2039. left join
  2040. (select p.rnk,
  2041. sum(p.sum_) pay_,
  2042. max(p.execdocdate) execdocdate
  2043. from
  2044. (select case when substr(p.rnk,1,6) = '000000' then substr(p.rnk,7) else p.rnk end rnk, p.sum_, p.execdocdate from dwh_payments p) p
  2045. join
  2046. (select c.rnk , nvl(w.work_days,c.plan_execution_date_con) work_days from erc_19_list_contract c left join erc_work_days w on w.date_ = c.plan_execution_date_con
  2047. union all
  2048. select c.rnk ,nvl(w.work_days,c.EXECUTIONPERIOD_END) work_days from erc_19_contract_kg c left join erc_work_days w on w.date_ = c.EXECUTIONPERIOD_END
  2049. )c on c.rnk = p.rnk
  2050. where trunc(p.execdocdate)<= c.work_days --to_date('01012018','DDMMYYYY')
  2051. group by p.rnk
  2052. )pay_ on pay_.rnk = sc.rnk
  2053. left join
  2054. (select contractrnk,
  2055. listagg(lotid, '; ') within group (order by contractrnk) lotuuid,
  2056. listagg(requestid, '; ') within group (order by contractrnk) requestid,
  2057. listagg(contractnoticenumber, '; ') within group (order by contractrnk) contractnoticenumber
  2058. from
  2059. (select contractrnk,lotid,requestid, contractnoticenumber,row_number () over (partition by contractrnk order by 1) rn
  2060. from (select distinct contractrnk,lotid,requestid,contractnoticenumber from erc_dwh_contract_kgntv_194_6 where contractrnk is not null) p
  2061. )
  2062. where rn < 100
  2063. group by contractrnk
  2064. )ais on ais.contractrnk = sc.rnk
  2065. where sc.rnk is not null
  2066. and sc.sop_name <> 'Запрос котировок';
  2067. 0172200004719000006
  2068. 0372200173419000001
  2069.  
  2070. --17 шаг генерации рейтинга эффективности
  2071. --ERC_19_DATA_CONTRACT_19
  2072.  
  2073. ---изменние 19.11.2019
  2074. --18 шаг генерации рейтинга эффективности
  2075. --ERC_19_DATA_DEMAND
  2076. --drop table erc_19_data_demand;
  2077. create table erc_19_data_demand as
  2078. select srl.org_kgntv,
  2079. eo.orgtitle grbstitle,
  2080. srl.REQNUM,
  2081. srl.publishdate_reqnum,
  2082. sd.max_PUBL,
  2083. sd.min_PUBL,
  2084. sd.cnt_all,
  2085. sd.cnt_adm,
  2086. sd.flag_protocol,
  2087. srl.sop_name_reqnum,
  2088. ais.lotuuid, ais.requestid
  2089. from (select REQNUM, max(publishdate_reqnum) publishdate_reqnum, sop_name_reqnum,joflag,
  2090. Case when joflag = 1 and org_kgntv_joflag <> 1412 then org_kgntv_joflag
  2091. -- when joflag = 1 and org_kgntv_joflag = 1412 then grbsid
  2092. else org_kgntv end org_kgntv
  2093. from erc_19_list_contract
  2094. where flag_comp_reqnum = 1 AND sop_code_reqnum<>'EAB44'
  2095. group by REQNUM, sop_name_reqnum, joflag,
  2096. Case when joflag = 1 and org_kgntv_joflag <> 1412 then org_kgntv_joflag
  2097. --when joflag = 1 and org_kgntv_joflag = 1412 then grbsid
  2098. else org_kgntv END
  2099. )srl
  2100. join erc_19_demand sd on srl.REQNUM = sd.purchasenumber And sd.flag_protocol in ('2','3','0','4','1') and sd.prolonflag is null
  2101. join erc_dwh_organization_kgntv dok on to_char(dok.id) = srl.org_kgntv
  2102. join erc_dwh_organization_kgntv dokgrbs on dokgrbs.id = dok.parentid
  2103. join ERC_ORGANIZATION eo on eo.spz = dokgrbs.spz
  2104. left join
  2105. (select reqnum,
  2106. listagg(lotuuid, '; ') within group (order by reqnum) lotuuid,
  2107. listagg(requestid, '; ') within group (order by reqnum) requestid
  2108. from
  2109. (select reqnum,lotuuid,requestid, row_number () over (partition by reqnum order by 1) rn
  2110. from (select distinct reqnum,lotuuid,requestid from erc_dwh_procedures_kgntv_194_6 where reqnum is not null) p
  2111. )
  2112. where rn < 100
  2113. group by reqnum
  2114. )ais on ais.reqnum = srl.REQNUM
  2115. UNION ALL
  2116.  
  2117.  
  2118. select srl.org_kgntv,
  2119. eo.orgtitle grbstitle,
  2120. srl.REQNUM,
  2121. srl.publishdate_reqnum,
  2122. sd.max_PUBL,
  2123. sd.min_PUBL,
  2124. sd.cnt_all,
  2125. sd.cnt_adm,
  2126. sd.flag_protocol,
  2127. srl.sop_name_reqnum,
  2128. ais.lotuuid, ais.requestid
  2129. from (select REQNUM, max(publishdate_reqnum) publishdate_reqnum, sop_name_reqnum,joflag,
  2130. Case when joflag = 1 and org_kgntv_joflag <> 1412 then org_kgntv_joflag
  2131. -- when joflag = 1 and org_kgntv_joflag = 1412 then grbsid
  2132. else org_kgntv end org_kgntv
  2133. from erc_19_list_contract
  2134. where flag_comp_reqnum = 1 AND sop_code_reqnum='EAB44'
  2135. group by REQNUM, sop_name_reqnum, joflag,
  2136. Case when joflag = 1 and org_kgntv_joflag <> 1412 then org_kgntv_joflag
  2137. -- when joflag = 1 and org_kgntv_joflag = 1412 then grbsid
  2138. else org_kgntv END
  2139.  
  2140. )srl
  2141. join erc_19_demand sd on srl.REQNUM = sd.purchasenumber And sd.flag_protocol in ('5','4','0','6') and sd.prolonflag is null
  2142. join erc_dwh_organization_kgntv dok on to_char(dok.id) = srl.org_kgntv
  2143. join erc_dwh_organization_kgntv dokgrbs on dokgrbs.id = dok.parentid
  2144. join ERC_ORGANIZATION eo on eo.spz = dokgrbs.spz
  2145. left join
  2146. (select reqnum,
  2147. listagg(lotuuid, '; ') within group (order by reqnum) lotuuid,
  2148. listagg(requestid, '; ') within group (order by reqnum) requestid
  2149. from
  2150. (select reqnum,lotuuid,requestid, row_number () over (partition by reqnum order by 1) rn
  2151. from (select distinct reqnum,lotuuid,requestid from erc_dwh_procedures_kgntv_194_6 where reqnum is not null) p
  2152. )
  2153. where rn < 100
  2154. group by reqnum
  2155. )ais on ais.reqnum = srl.REQNUM
  2156. ;
  2157. --18 шаг генерации рейтинга эффективности
  2158. --ERC_19_DATA_DEMAND
  2159.  
  2160. erc_19_protocol
  2161. --19 шаг генерации рейтинга эффективности
  2162. --ERC_19_DATA_PROTOCOL
  2163. --drop table erc_19_data_protocol;
  2164. create table erc_19_data_protocol as
  2165. select pp.org_kgntv,
  2166. pp.org_name,
  2167. pp.publishdate_notice,
  2168. eo.orgtitle grbstitle,
  2169. pp.PURCHASENUMBER,
  2170. pp.protname protocolname,
  2171. pp.protocolnumber,
  2172. case
  2173. when
  2174. pp.prottype in ('ProtocolEF1','ProtocolEFSinglePart','ProtocolEFSinglePart','ProtocolEF1') then pp.PUBLISHDATE else pp.PROTOCOLDATE end PROTOCOLDATE,--дата публикации на EIS,
  2175. case
  2176. when pp.prottype = 'ProtocolEFSinglePart' then prz.SIGNDATE else pp.SIGNDATE end SIGNDATE,
  2177. case
  2178. when
  2179. pp.prottype in ('ProtocolEF1','ProtocolEFSinglePart') then pp.PUBLISHDATE else null end ProtocolEFSinglePart_Date,
  2180. case
  2181. when
  2182. pp.prottype in ('ProtocolEF1','ProtocolEFSinglePart','ProtocolEFSinglePart','ProtocolEF1') then pp.PROTOCOLDATE else pp.PUBLISHDATE end PUBLISHDATE,--дата публикации на ЭТП
  2183. case
  2184. when pp.prottype not in ('ProtocolZPFinal','ProtocolOK1','ProtocolOKOU1','ProtocolOKD4',
  2185. 'ProtocolOKD1',/*'ProtocolZPExtract',*/'ProtocolOKD3','ProtocolEF3',
  2186. 'ProtocolOKOU2','ProtocolOKSingleApp','ProtocolOKDSingleApp',
  2187. 'ProtocolOKOUSingleApp','ProtocolEF1','ProtocolZK','ProtocolOK2','ProtocolOKOU3','ProtocolZP',
  2188. 'ProtocolOKD5','ProtocolZKAfterProlong','ProtocolEFSingleApp'/*,'ProtocolEFSinglePart'*/,
  2189. 'rotocolEOK1','rotocolEOK2','rotocolEOK3','rotocolEOKOU1','rotocolEOKOU2','rotocolEOKOU3',
  2190. 'rotocolEOKD1','rotocolEOKD2','rotocolEOKD3','rotocolEOKSingleAppType','rotocolEOKOUSingleAppType','rotocolEZP2','rotocolEOKD4',
  2191. 'rotocolEOKDSingleAppType','rotocolEZK1','rotocolEZK2','rotocolEZP1Extract','rotocolEOKSingleAppType','rotocolEOKD4Type') then 0
  2192. when pp.publishdate is null
  2193. or pp.signdate is null and pp.prottype not in ('ProtocolOKOU2','ProtocolOKOU3','ProtocolZPExtract','rotocolEOKOU2','rotocolEOKOU3','rotocolEZP1Extract','rotocolEOK3','ProtocolEF3','rotocolEZK2')
  2194. or pp.PROTOCOLDATE is null and pp.prottype in ('ProtocolOKOU2','ProtocolOKOU3','ProtocolZPExtract','rotocolEOKOU2','rotocolEOKOU3','rotocolEZP1Extract','rotocolEOK3','ProtocolEF3','rotocolEZK2') then 0
  2195. -- две строки выше - это условие по ряду итоговых протоколов и выписке в случае наличия первых частей протоколов и отсутствия последних
  2196.  
  2197. when pp.prottype in ( 'ProtocolEF1') and
  2198. (( trunc ( pp.PROTOCOLDATE ) - trunc ( pp.scoringdate ) < 1 ) or
  2199. --( trunc ( pp.publishdate ) - trunc ( pp.scoringdate ) < 1 )
  2200. nvl((
  2201. select sum ( days.type ) sss--,'1' as code
  2202. from work_days_all days
  2203. where days.date_ BETWEEN Trunc(pp.scoringdate) and Trunc(pp.publishdate)- INTERVAL '1' DAY
  2204. )
  2205. ,0 ) =0 )
  2206. then 0
  2207. when pp.prottype in ('ProtocolEFSinglePart') --+
  2208. and nvl((
  2209. select sum ( days.type )
  2210. from work_days_all days
  2211. where days.date_ <= trunc ( pp.publishdate ) and days.date_ > trunc (pp.signdate) ),0) <= 3 then 0
  2212. when pp.prottype in ( 'ProtocolEFSingleApp','rotocolEOKSingleApp','rotocolEOKOUSingleApp' )--+
  2213. and nvl((
  2214. select sum ( days.type )
  2215. from work_days_all days
  2216. where days.date_ <= trunc ( pp.publishdate ) and days.date_ > trunc (pp.enddate)),0 ) <= 4 then 0
  2217. when pp.prottype in ( 'ProtocolOK2', 'ProtocolOKSingleApp','ProtocolOKOU1', 'ProtocolOKOUSingleApp', 'ProtocolOK1',
  2218. /**/ 'ProtocolOKD4','ProtocolOKDSingleApp','rotocolEOKDSingleApp','ProtocolOKD1','ProtocolOKD3','rotocolEOKD3','ProtocolOKD5') --+
  2219. and nvl((
  2220. select sum ( days.type )
  2221. from work_days_all days
  2222. where days.date_ <= trunc ( pp.publishdate ) and days.date_ > trunc (pp.signdate) ),0) <= 1 then 0
  2223.  
  2224. when pp.prottype in ('ProtocolEF3') --+
  2225. and nvl((
  2226. select sum ( days.type )
  2227. from work_days_all days
  2228. where days.date_ <= trunc ( pp.publishdate ) and days.date_ > trunc (pp.signdate) ),0) <= 1 then 0
  2229.  
  2230. when pp.prottype in ( 'ProtocolOKOU2','rotocolEOKOU2','rotocolEOK2' ) --+--не позднее даты окончания подписания заявок ОКЭФ, ОКОУЭФ
  2231. and
  2232. ( pp.publishdate ) <= trunc (pp.scoringdate) then 0
  2233.  
  2234. when pp.prottype in ( 'ProtocolOKOU3','rotocolEOKOU3','rotocolEOK3','rotocolEOKD4','rotocolEZP1','rotocolEZP2')--протокол подведения итогов по ( открытый конкурс в ЭФ, ОКОУ в ЭФ и ОКДЭФ) и ЗП в ЭФ
  2235. and ( pp.publishdate ) = trunc (pp.signdate)
  2236. and ( trunc ( pp.publishdate ) < nvl(w.work_days,interval '10' day + trunc ( pp.protocoldate ))) then 0 --+
  2237.  
  2238. when pp.prottype in ('rotocolEOKD1') --+-протокол первого этапа двухэтапного конкурса в ЭФ
  2239. and nvl((
  2240. select sum ( days.type )
  2241. from work_days_all days
  2242. where days.date_ <= trunc ( pp.publishdate ) and days.date_ > trunc (pp.signdate) ),0) <= 1 then 0
  2243. /*when pp.prottype in ( 'ProtocolEF3' )
  2244. and (
  2245. select sum ( days.type )
  2246. from work_days_all days
  2247. where days.date_ <= trunc ( pp.publishdate ) and days.date_ >= trunc (pp.signdate) ) <= 2 then 0*/
  2248. /* when pp.prottype in ( 'ProtocolOKD4', 'ProtocolOKDSingleApp', 'ProtocolOKD1', 'ProtocolOKD3', 'ProtocolOKD5' ) and nvl((
  2249. select sum ( days.type )
  2250. from work_days_all days
  2251. where days.date_ <= trunc ( pp.publishdate ) and days.date_ >= trunc (pp.signdate) ),0) <= 2 then 0 */
  2252. /*when pp.prottype in ( 'ProtocolEF1', 'ProtocolEFSinglePart', 'ProtocolEFSingleApp', 'ProtocolEF3' ) --протокол ProtocolEF1 ProtocolEFSingleApp (публикация) сравнивается с датой scoringdate из извещения (в тот же день)
  2253. and (
  2254. select sum ( days.type )
  2255. from work_days_all days
  2256. where days.date_ <= trunc ( pp.publishdate ) and days.date_ >= trunc (pp.signdate) ) <= 2 then 0 */
  2257. when pp.prottype in ( 'ProtocolZKAfterProlong', 'ProtocolZK' ,'ProtocolZP','ProtocolZPFinal') and nvl((
  2258. select sum ( days.type )
  2259. from work_days_all days
  2260. where days.date_ <= trunc ( pp.publishdate ) and days.date_ >= trunc (pp.signdate)),0 ) <= 1 then 0
  2261. when pp.prottype in ('rotocolEZK1') --+
  2262. and nvl((
  2263. select sum ( days.type )
  2264. from work_days_all days
  2265. where days.date_ <= trunc ( pp.publishdate ) and days.date_ > trunc (pp.enddate)),0 ) <= 1 then 0
  2266. when pp.prottype in ('ProtocolZPExtract','rotocolEZP1Extract') -- Выписка из протокола проведения ЗП не позднее даты окончания срока рассмотрения заявок
  2267. and ( pp.publishdate ) <= trunc (pp.scoringdate) then 0
  2268. /*when pp.prottype in ( 'ProtocolZP', 'ProtocolZPFinal' ) and nvl((
  2269. select sum ( days.type )
  2270. from work_days_all days
  2271. where days.date_ <= trunc ( pp.publishdate ) and days.date_ >= trunc (pp.signdate)),0 ) <= 1 then 0 */
  2272. else 1
  2273. end delay,
  2274. pp.flag_preg,
  2275. ais.lotuuid, ais.requestid,
  2276. pp.scoringdate,
  2277. pp.enddate,
  2278. pc.PUBLISHDATE gz_epdate,
  2279. pp.protocolid
  2280. from erc_19_protocol pp
  2281. left join erc_work_days w on w.date_ = interval '10' day + trunc ( pp.protocoldate )
  2282. left join (SELECT DISTINCT reqnum,type,SIGNDATE,PUBLISHDATE FROM erc_19_protocol_ch) pc on pp.purchasenumber = pc.reqnum and pp.prottype = pc.type and pc.SIGNDATE=pp.SIGNDATE
  2283. left join (select distinct protocolnumber, PURCHASENUMBER, SIGNDATE, TITLE, id from DWH_PROTOCOL_nrpz)prz on pp.PURCHASENUMBER=prz.PURCHASENUMBER and pp.protname=prz.title and prz.protocolnumber = pp.protocolnumber and prz.id = pp.protocolid
  2284. join erc_dwh_organization_kgntv dok on to_char(dok.id) = pp.org_kgntv
  2285. join erc_dwh_organization_kgntv dokgrbs on dokgrbs.id = dok.parentid
  2286. join ERC_ORGANIZATION eo on eo.spz = dokgrbs.spz
  2287. --left join (select distinct REQNUM from erc_19_list_contract where flag_st_hand =1 )p on p.reqnum = pp.purchasenumber
  2288. left join
  2289. (select reqnum,
  2290. listagg(lotuuid, '; ') within group (order by reqnum) lotuuid,
  2291. listagg(requestid, '; ') within group (order by reqnum) requestid
  2292. from
  2293. (select reqnum,lotuuid,requestid, row_number () over (partition by reqnum order by 1) rn
  2294. from (select distinct reqnum,lotuuid,requestid from erc_dwh_procedures_kgntv_194_6 where reqnum is not null) p
  2295. )
  2296. where rn < 100
  2297. group by reqnum
  2298. )ais on ais.reqnum = pp.PURCHASENUMBER
  2299. where pp.prottype not in ('rotocolEOK1','rotocolEOKOU1','rotocolEZK2','ProtocolZK');
  2300.  
  2301. 0172200004719000006
  2302. 0372200173419000001
  2303.  
  2304.  
  2305.  
  2306. --19 шаг генерации рейтинга эффективности
  2307. --ERC_19_DATA_PROTOCOL
  2308. 4719008550
  2309. erc_dwh_organization_kgntv
  2310.  
  2311. --20 шаг генерации рейтинга эффективности
  2312. --ERC_19_DATA_COMPLAINT
  2313. --drop table erc_19_data_complaint;
  2314. create table erc_19_data_complaint as
  2315. select eo.orgtitle fgrbsname,sc.org_kgntv, sc.org_name, c.com_number, c.check_number, c.reqnum, c.chec_date, c.chec_pres_date,
  2316. case when c.result = 'COMPLAINT_VIOLATIONS' then 'Обоснована' when c.result = 'COMPLAINT_PARTLY_VALID' then 'Обоснована частично' else null end result,
  2317. c.prescriptionnumber, c.complaint_publishdate,
  2318. ais.lotuuid, ais.requestid
  2319. from
  2320. (select distinct REQNUM,
  2321. case when joflag = 1 then org_kgntv_joflag else org_kgntv end org_kgntv,
  2322. case when joflag = 1 then to_char(joflag_org_name) else org_name end org_name
  2323. from erc_19_list_contract
  2324. where flag_comp_reqnum = 1
  2325. ) sc
  2326. left join ERC_19_CHECKRESULT_BASE c on c.REQNUM = sc.REQNUM
  2327. join erc_dwh_organization_kgntv dok on dok.id = sc.org_kgntv
  2328. inner join erc_dwh_organization_kgntv dokgrbs on dokgrbs.id = dok.parentid
  2329. inner join ERC_ORGANIZATION eo on eo.spz = dokgrbs.spz
  2330. left join
  2331. (select reqnum,
  2332. listagg(lotuuid, '; ') within group (order by reqnum) lotuuid,
  2333. listagg(requestid, '; ') within group (order by reqnum) requestid
  2334. from
  2335. (select reqnum,lotuuid,requestid, row_number () over (partition by reqnum order by 1) rn
  2336. from (select distinct reqnum,lotuuid,requestid from erc_dwh_procedures_kgntv_194_6 where reqnum is not null) p
  2337. )
  2338. where rn < 100
  2339. group by reqnum
  2340. )ais on ais.reqnum = sc.reqnum
  2341. --20 шаг генерации рейтинга эффективности
  2342. --ERC_19_DATA_COMPLAINT
  2343.  
  2344. --21 шаг генерации рейтинга эффективности
  2345. -- детализация по малым закупкам
  2346. --ERC_19_DATA_EM_purchase
  2347. drop table ERC_19_DATA_EM_purchase;
  2348. create table ERC_19_DATA_EM_purchase as
  2349. select
  2350. o.grbs_full_name,k.org_kgntv,k.org_name,
  2351. k.rnk,k.signdate,k.EXECUTIONPERIOD_END,
  2352. k.REJECTED_DATE,k.EXECUTIONS_DATE,
  2353. k.PENALTIES_TYPE,k.price,k.price_cur,
  2354. case when trunc(signdate)>='01.07.2019' and k.is_concluded_in_e_shop is not null then 1 else null end bra1,
  2355. 1 bra2,
  2356. k.OBJECT_NAME,
  2357. k.PLACINGWAY_NAME,
  2358. k.SINGLECUSTOMER_NAME,
  2359. k.lotid,
  2360. k.requestid
  2361. from erc_19_contract_kg k
  2362. join erc_dwh_organization_kgntv o on o.id=k.org_kgntv
  2363. where (singlecustomer_name like('%пункт 4%')or singlecustomer_name like('%пункт 5%'))
  2364. and trunc(signdate)>='01.07.2019'
  2365. --where org_kgntv in (910,1515,1001)
  2366. --SUCCESS!
  2367.  
  2368. select dpk.grbstitle,
  2369. sum(finprice) nmc,
  2370. count( *) count_,
  2371. sum(finprice_con) consum,
  2372. sum(is_con) concount_pur,
  2373. sum(cnt_con) concount,
  2374. sum(finprice_c25) oneexsum,
  2375. sum(cnt_m25) oneexcount_pur,
  2376. sum(cnt_c25) oneexcount,
  2377. sum(is_failed_sop)is_failed_sop,
  2378. sum(finprice_failed)finprice_failed,
  2379. sum(is_cancel)is_cancel,
  2380. sum(finprice_cancel)finprice_cancel
  2381. from
  2382. (select dpk.reqnum,
  2383. sum(dpk.STARTPRICE) finprice,
  2384. max(case when dck.lotid is not null then 1 else 0 end) is_con,
  2385. sum(case when dck.lotid is not null then dpk.STARTPRICE else 0 end)finprice_con,
  2386. sum(case when dck.lotid is not null then 1 else 0 end) cnt_con,
  2387. sum(case when dck.lotid is not null and dck.flag_25 = 1 then dpk.STARTPRICE else 0 end) finprice_c25,
  2388. sum(case when dck.lotid is not null and dck.flag_25 = 1 then 1 else 0 end) cnt_c25,
  2389. max(case when dck.lotid is not null and dck.flag_25 = 1 then 1 else 0 end) cnt_m25,
  2390. max(dpk.failed_sop) is_failed_sop,
  2391. sum(case when dpk.failed_sop = 1 then dpk.STARTPRICE else 0 end) finprice_failed,
  2392. max(dpk.flag_publ_date_cancel) is_cancel,
  2393. sum(case when dpk.flag_publ_date_cancel = 1 then dpk.STARTPRICE else 0 end) finprice_cancel,
  2394. dpk.grbstitle --dpk.FORDERTYPENAME
  2395. from dwh_governor_lot_all dpk
  2396. left join DWH_GOVERNOR_CONTRACT_all dck on dpk.lotuuid = dck.lotid and dck.FLAG_SIGN_DATE = 1
  2397. where flag_publ_date = 1 and dpk.flag_special = 0
  2398. group by dpk.reqnum, dpk.grbstitle--, dpk.FORDERTYPENAME
  2399. ) dpk
  2400. group by dpk.grbstitle
  2401. order by dpk.grbstitle
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!