Advertisement
GLASHATAY_007

Untitled

Sep 13th, 2023
681
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --truncate table aip_sgoz_detail_${import_year};
  2. --insert into aip_sgoz_detail_${import_year}
  3. SELECT s.cul_parent contragent_inn, org.kpp,UPPER(COALESCE(org.full_name, u.full_name)) full_namE,      
  4.       org.grbs_inn, org.parentid grbsid,
  5.       CASE WHEN s.cul_parent IN ('7838479881','7838489103') THEN 'КОМИТЕТ ПО ТРУДУ И ЗАНЯТОСТИ НАСЕЛЕНИЯ САНКТ-ПЕТЕРБУРГА'  ELSE org.grbs_full_name END grbs_name,
  6.        s.targetexpenseitemcode,  t.np_code_name,t.fp_code_name,s.contragent_account,
  7.      CASE
  8.         WHEN s.targetexpenseitemcode IN (SELECT * FROM dwh_targetexpenseitemcode_aip) AND s.cvr NOT IN ('244')
  9.         OR (s.targetexpenseitemcode='022E15305F' AND s.cvr='414' AND s.kosgu='310' AND s.cul_parent='7809025523')
  10.         THEN 'Да' ELSE 'Нет'              
  11.        END priznak,--Признак совпадения целевой статьи (АИП)
  12.         s.cvr,s.kosgu,
  13.         SUM fin_aip, --финансирование АИП из бюджета
  14.         S.FUND --Код фонда
  15.         ,s.aip_name
  16.         ,s.aip_code
  17. FROM (
  18.  
  19.         SELECT
  20.             b.*
  21.         FROM DWH_KF_LBO_AIP_KGNTV b
  22.         Join dwh_organization_kgntv o ON (b.cul_parent=o.inn)
  23.         WHERE b.YEAR=${import_year}
  24.               AND ((b.cvr BETWEEN 200 AND 247 )
  25.                     OR ((b.cvr = '414' OR b.cvr = '412' OR b.cvr = '466') AND b.kosgu NOT IN ('297','298','299','530'))
  26.                   )
  27.               AND (
  28.                     (b.targetexpenseitemcode NOT IN ('0210020830','0210020850','0220020820','0220020840','0510060500') AND o.inn='7832000076')
  29.                     OR
  30.                     o.inn<>'7832000076'
  31.                    )
  32.      )   s
  33. Join DWH_KF_UL u ON s.cul_parent=u.code AND u.YEAR=${import_year}
  34. left Join (SELECT targetexpenseitemcode, fp_code, fp_code_name, np_code_name FROM dwh_targetexpenseitemcode_2020 ) t ON s.targetexpenseitemcode=t.targetexpenseitemcode
  35. left Join (SELECT * FROM dwh_organization_kgntv /*Where org_actual =1*/) org ON s.cul_parent=org.inn AND s.YEAR=${import_year}
  36. WHERE s.YEAR=${import_year}  AND ( s.contragent_account NOT IN ('0171141','0295001'))
  37. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement