Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- TRUNCATE TABLE kgz_purchase_uo;
- INSERT INTO kgz_purchase_uo
- -- 1 block
- SELECT
- p.requestid,
- p.lotuuid,
- p.pg_ikz,
- p.pg_rn,
- p.reqnum,
- p.f_productprice,
- p.final_price,
- TRUNC(p.date_notice_published)date_notice_published,
- p.lotstage,
- p.reqstage,
- p.fordertypename,
- TO_CHAR(p.lotsubject)lotsubject,
- o.inn,
- o.full_name,
- p.grbstitle,
- NULL kgz_reqnum,
- o.inn AS c_inn -- 6 ИНН организации разместившей извещение
- ,o.full_name AS c_org -- 7 наименование организации разместившей извещение
- ,p.joflag
- ,pea.nmck_type -- метод определения и обоснования НМЦК
- ,get_pg_year(p.pg_ikz)pg_year
- ,1 TYPE
- FROM dwh_procedures_kgntv p
- Inner Join dwh_organization_kgntv o ON p.orgid=o.id
- Left Join ( SELECT
- COALESCE(l.lot_id,sovm.lot_id) lot_id,
- COALESCE(l.plnm_name,l.plnm_name2,sovm.plnm_name) nmck_type
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join (SELECT
- m.plnm_name,
- COALESCE(l.lot_id,m.lot_id,TO_NUMBER(p.lotuuid)) lot_id,
- COALESCE(p.MORD_FLAG,m.MORD_FLAG)
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join dwh_procedures_kgntv p ON p.lotuuid = l.lot_id AND p.MORD_FLAG = 'D' --
- Left Join(SELECT
- COALESCE(gm.plnm_name,gm.plnm_name2) plnm_name,
- COALESCE(l.lot_id,gm.lot_id,TO_NUMBER(p.lotuuid)) lot_id, --p.lotuuid
- COALESCE(p.MORD_FLAG,gm.MORD_FLAG) MORD_FLAG
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join( SELECT
- l.*,
- MORD_FLAG
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join dwh_procedures_kgntv p ON p.lotuuid = l.lot_id
- WHERE MORD_FLAG IS NULL
- ) gm ON gm.lot_id = l.joint_procedure_id
- Left Join dwh_procedures_kgntv p ON p.lotuuid = l.lot_id AND p.MORD_FLAG = 'M'
- WHERE l.joint_procedure_id IS NOT NULL
- )m ON m.lot_id = p.LOT_CONS_M
- ) sovm ON sovm.lot_id = l.lot_id
- ) pea ON pea.lot_id = p.lotuuid
- WHERE 1=1
- AND TRUNC(p.date_notice_published)>'01.01.2023'
- --and p.pg_ikz like '24%'
- AND p.reqnum IS NOT NULL
- AND p.mord_flag='D'
- AND p.uo_orgid IS NULL
- AND p.joflag <>1
- AND p.orgid NOT IN (1030,1894,2981,2167,1725,3097,1030)
- --order by p.f_productprice desc
- UNION
- -- 2 block
- SELECT
- DISTINCT
- TO_CHAR(cons.requestid)requestid,
- TO_CHAR(cons.lotuuid)lotuuid,
- cons.pg_ikz,
- cons.pg_rn,
- cons.reqnum,
- cons.f_productprice,
- cons.final_price,
- TRUNC(cons.date_notice_published)date_notice_published,
- cons.lotstage,
- cons.reqstage,
- cons.fordertypename,
- TO_CHAR(cons.lotsubject)lotsubject,
- o.inn,
- o.full_name,
- cons.grbstitle,
- CASE
- WHEN p.uo_orgid=1412 THEN cons.reqnum
- ELSE NULL
- END kgs_reqnum,
- o.inn AS inn -- 6 ИНН организации разместившей извещение
- ,o.full_name AS c_org -- 7 наименование организации разместившей извещение
- ,p.joflag
- ,pea.nmck_type -- метод определения и обоснования НМЦК
- ,get_pg_year(p.pg_ikz)pg_year
- ,2
- FROM dwh_procedures_cons_kgntv cons
- Inner Join dwh_procedures_kgntv p ON cons.lotuuid = p.Joint_procedure_id
- Inner Join dwh_organization_kgntv o ON cons.orgid=o.id
- Left Join dwh_organization_kgntv o_u ON o_u.id = p.uo_orgid
- Left Join (SELECT
- COALESCE(l.lot_id,sovm.lot_id) lot_id,
- COALESCE(l.plnm_name,l.plnm_name2,sovm.plnm_name) nmck_type
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join (SELECT
- m.plnm_name,
- COALESCE(l.lot_id,m.lot_id,TO_NUMBER(p.lotuuid)) lot_id,
- COALESCE(p.MORD_FLAG,m.MORD_FLAG)
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join dwh_procedures_kgntv p ON p.lotuuid = l.lot_id AND p.MORD_FLAG = 'D' --
- Left Join(SELECT
- COALESCE(gm.plnm_name,gm.plnm_name2) plnm_name,
- COALESCE(l.lot_id,gm.lot_id,TO_NUMBER(p.lotuuid)) lot_id, --p.lotuuid
- COALESCE(p.MORD_FLAG,gm.MORD_FLAG) MORD_FLAG
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join( SELECT
- l.*,
- MORD_FLAG
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join dwh_procedures_kgntv p ON p.lotuuid = l.lot_id
- WHERE MORD_FLAG IS NULL
- ) gm ON gm.lot_id = l.joint_procedure_id
- Left Join dwh_procedures_kgntv p ON p.lotuuid = l.lot_id AND p.MORD_FLAG = 'M'
- WHERE l.joint_procedure_id IS NOT NULL
- )m ON m.lot_id = p.LOT_CONS_M
- ) sovm ON sovm.lot_id = l.lot_id
- ) pea ON pea.lot_id = cons.lotuuid
- WHERE 1=1
- -- and coalesce(cons.pg_ikz, p.pg_ikz) like '24%'
- AND TRUNC(p.date_notice_published)>'01.01.2023'
- AND p.mord_flag='M'
- AND p.joflag=1
- AND p.orgid NOT IN (1030,1894,2981,2167,1725,3097,1030)
- UNION
- -- 3 block
- SELECT
- p.requestid,
- p.lotuuid,
- p.pg_ikz,
- p.pg_rn,
- p.reqnum,
- p.f_productprice,
- p.final_price,
- TRUNC(p.date_notice_published)date_notice_published,
- p.lotstage,
- p.reqstage,
- p.fordertypename,
- TO_CHAR(p.lotsubject)lotsubject,
- o.inn,
- o.full_name,
- p.grbstitle,
- CASE
- WHEN p.uo_orgid=1412 THEN p.reqnum
- ELSE NULL
- END kgs_reqnum,
- o.inn AS inn -- 6 ИНН организации разместившей извещение
- ,o.full_name AS c_org -- 7 наименование организации разместившей извещение
- ,p.joflag
- ,pea.nmck_type -- метод определения и обоснования НМЦК
- ,get_pg_year(p.pg_ikz)pg_year
- ,3
- FROM dwh_procedures_kgntv p
- Inner Join dwh_organization_kgntv o ON p.uo_orgid=o.id
- Left Join ( SELECT
- COALESCE(l.lot_id,sovm.lot_id) lot_id,
- COALESCE(l.plnm_name,l.plnm_name2,sovm.plnm_name) nmck_type
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join (SELECT
- m.plnm_name,
- COALESCE(l.lot_id,m.lot_id,TO_NUMBER(p.lotuuid)) lot_id,
- COALESCE(p.MORD_FLAG,m.MORD_FLAG)
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join dwh_procedures_kgntv p ON p.lotuuid = l.lot_id AND p.MORD_FLAG = 'D' --
- Left Join(SELECT
- COALESCE(gm.plnm_name,gm.plnm_name2) plnm_name,
- COALESCE(l.lot_id,gm.lot_id,TO_NUMBER(p.lotuuid)) lot_id, --p.lotuuid
- COALESCE(p.MORD_FLAG,gm.MORD_FLAG) MORD_FLAG
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join( SELECT
- l.*,
- MORD_FLAG
- FROM DWH_LOT_KGNTV_EXT_ATTR_1 l
- Left Join dwh_procedures_kgntv p ON p.lotuuid = l.lot_id
- WHERE MORD_FLAG IS NULL
- ) gm ON gm.lot_id = l.joint_procedure_id
- Left Join dwh_procedures_kgntv p ON p.lotuuid = l.lot_id AND p.MORD_FLAG = 'M'
- WHERE l.joint_procedure_id IS NOT NULL
- )m ON m.lot_id = p.LOT_CONS_M
- ) sovm ON sovm.lot_id = l.lot_id
- ) pea ON pea.lot_id = p.lotuuid
- WHERE 1=1
- --and p.pg_ikz like '24%'
- AND TRUNC(p.date_notice_published)>'01.01.2023'
- AND p.reqnum IS NOT NULL
- AND p.mord_flag='D'
- AND p.uo_orgid IS NOT NULL
- AND p.placement='Централизованная закупка'
- AND p.joflag<>1
- AND p.orgid NOT IN (1030,1894,2981,2167,1725,3097,1030)
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement