API tools faq
paste
Advertisement
Calenria

Untitled

Calenria
May 25th, 2015
267
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 10.08 KB | None | 0 0
raw download clone embed print report
  1. SELECT DISTINCT SUBSTR(BASEKEY, 3, 2) AS BASEKEY_DO_TYP FROM (
  2. SELECT
  3.     DISTINCT fe.werk_id REGION_ID
  4.     , fe.basisschluessel BASEKEY
  5.     , fe_sto_kandidat.bs_sto SITE_BASEKEY
  6.     , fe.do_typ_id MODEL_TYPE_ID
  7.     , do_typ.do_typ_name MODEL_TYPE_NAME
  8.     , fe.hersteller MANUFACTURER_NAME
  9.     , fe.fe_name NE_NAME
  10.     , fe_sto_kandidat.status_nr NE_STATUS
  11.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',80,NULL),'DDMMYYYY') STATUS_80_DATE
  12.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',82,NULL),'DDMMYYYY') STATUS_82_DATE
  13.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',100,NULL),'DDMMYYYY') STATUS_100_DATE
  14.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',950,NULL),'DDMMYYYY') STATUS_950_DATE
  15.     , nvl (bs_typ.aufbauart, 'NichtGepflegt/ZENTIS') MODEL_ASSEMBLY
  16.     , nvl (bs_typ.ausfuehrung, 'NichtGepflegt/ZENTIS') MODEL_TECHNOLOGY
  17.     , bs_typ.generation MDOEL_GENERATION
  18.     , NULL ZENTRALSTAT_BS
  19.     , NULL "LINK ID"
  20.     , fe_sto_kandidat.fe_sto_kandidat_id
  21.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',fe_sto_kandidat.status_nr,fe_sto_kandidat.status_zeichen),'DDMMYYYY') DATUM_STATUS_AKT
  22.     , (SELECT
  23.     substr (MAX (betriebsortart), 1, 40) betriebsortart
  24. FROM
  25.     betriebsortart
  26. WHERE betriebsortart_id IN (
  27.     SELECT
  28.         betriebsortart_id
  29.     FROM
  30.         betriebsort bo
  31.         , fe_sto_kandidat festo
  32.     WHERE bo.betriebsort_id = festo.betriebsort_id
  33.         AND festo.fe_sto_kandidat_id = fe_sto_kandidat.fe_sto_kandidat_id
  34.     )) SITE_CATEGORY
  35.     , fe_sto_kandidat.mcm_ne_nummer
  36.     , fe_sto_kandidat.mcm_sto_nummer
  37.     , decode(pkg_baumassnahme.get_toc_status(pkg_baumassnahme.get_baumassnahme_id_nb(fe_sto_kandidat.fe_sto_kandidat_id)),'getoct',1,'enttoct',0,NULL) getoct
  38. FROM
  39.     fe
  40.     , fe_sto_kandidat
  41.     , do_typ
  42.     , bs
  43.     , bs_typ
  44.     , baumassnahme
  45.     , handlung
  46. WHERE fe.do_typ_id IN ('70')
  47.     AND fe.objekt_id = fe_sto_kandidat.objekt_id
  48.     AND fe_sto_kandidat.fe_sto_kandidat_id = baumassnahme.fe_sto_kandidat_id
  49.     AND baumassnahme.baumassnahme_id = handlung.baumassnahme_id
  50.     AND nvl(TO_CHAR(handlung.datum_ist),0) NOT LIKE 0
  51.     AND handlung.status_nr >= 21
  52.     AND fe_sto_kandidat.aktiv = 1
  53.     AND fe_sto_kandidat.ausgewaehlt = 1
  54.     AND do_typ.do_typ_id = fe.do_typ_id
  55.     AND bs.bs_typ_id = bs_typ.bs_typ_id(+)
  56.     AND bs.objekt_id = fe.objekt_id
  57. UNION ALL
  58. SELECT
  59.     DISTINCT fe.werk_id
  60.     , fe.basisschluessel
  61.     , fe_sto_kandidat.bs_sto
  62.     , fe.do_typ_id
  63.     , do_typ.do_typ_name
  64.     , fe.hersteller
  65.     , fe.fe_name fe_name
  66.     , fe_sto_kandidat.status_nr
  67.         , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',80,NULL),'DDMMYYYY') STATUS_80_DATE
  68.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',82,NULL),'DDMMYYYY') STATUS_82_DATE
  69.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',100,NULL),'DDMMYYYY') STATUS_100_DATE
  70.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',950,NULL),'DDMMYYYY') STATUS_950_DATE
  71.     , NULL aufbauart
  72.     , NULL ausfuehrung
  73.     , NULL generation
  74.         , NULL termial_out_bs
  75.     , NULL link_id
  76.         , fe_sto_kandidat.fe_sto_kandidat_id
  77.    
  78.    
  79.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',fe_sto_kandidat.status_nr,fe_sto_kandidat.status_zeichen),'DDMMYYYY') DATUM_STATUS_AKT
  80.     , (SELECT
  81.     substr (MAX (betriebsortart), 1, 40) betriebsortart
  82. FROM
  83.     betriebsortart
  84. WHERE betriebsortart_id IN (
  85.     SELECT
  86.         betriebsortart_id
  87.     FROM
  88.         betriebsort bo
  89.         , fe_sto_kandidat festo
  90.     WHERE bo.betriebsort_id = festo.betriebsort_id
  91.         AND festo.fe_sto_kandidat_id = fe_sto_kandidat.fe_sto_kandidat_id
  92.     )) SITE_CATEGORY
  93.     , fe_sto_kandidat.mcm_ne_nummer
  94.     , fe_sto_kandidat.mcm_sto_nummer
  95.     , decode(pkg_baumassnahme.get_toc_status(pkg_baumassnahme.get_baumassnahme_id_nb(fe_sto_kandidat.fe_sto_kandidat_id)),'getoct',1,'enttoct',0,NULL) getoct
  96. FROM
  97.     fe
  98.     , fe_sto_kandidat
  99.     , do_typ
  100.     , baumassnahme
  101.     , handlung
  102. WHERE fe.objekt_id = fe_sto_kandidat.objekt_id
  103.     AND fe_sto_kandidat.fe_sto_kandidat_id = baumassnahme.fe_sto_kandidat_id
  104.     AND baumassnahme.baumassnahme_id = handlung.baumassnahme_id
  105.     AND handlung.datum_ist IS
  106.     NOT NULL
  107.     AND handlung.status_nr >= 21
  108.     AND fe_sto_kandidat.aktiv = 1
  109.     AND fe_sto_kandidat.ausgewaehlt = 1
  110.     AND do_typ.do_typ_id = fe.do_typ_id
  111. UNION ALL
  112. SELECT
  113.     DISTINCT fe.werk_id
  114.     , fe.basisschluessel
  115.     , fe_sto_kandidat.bs_sto
  116.     , fe.do_typ_id
  117.     , do_typ.do_typ_name
  118.     , fe.hersteller
  119.     , fe.fe_name fe_name
  120.     , fe_sto_kandidat.status_nr
  121.         , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',80,NULL),'DDMMYYYY') STATUS_80_DATE
  122.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',82,NULL),'DDMMYYYY') STATUS_82_DATE
  123.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',100,NULL),'DDMMYYYY') STATUS_100_DATE
  124.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',950,NULL),'DDMMYYYY') STATUS_950_DATE
  125.     , NULL aufbauart
  126.     , NULL ausfuehrung
  127.     , NULL generation
  128.     , NULL termial_out_bs
  129.     , NULL link_id
  130.         , fe_sto_kandidat.fe_sto_kandidat_id
  131.    
  132.    
  133.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',fe_sto_kandidat.status_nr,fe_sto_kandidat.status_zeichen),'DDMMYYYY') DATUM_STATUS_AKT
  134.     , (SELECT
  135.     substr (MAX (betriebsortart), 1, 40) betriebsortart
  136. FROM
  137.     betriebsortart
  138. WHERE betriebsortart_id IN (
  139.     SELECT
  140.         betriebsortart_id
  141.     FROM
  142.         betriebsort bo
  143.         , fe_sto_kandidat festo
  144.     WHERE bo.betriebsort_id = festo.betriebsort_id
  145.         AND festo.fe_sto_kandidat_id = fe_sto_kandidat.fe_sto_kandidat_id
  146.     )) SITE_CATEGORY
  147.     , fe_sto_kandidat.mcm_ne_nummer
  148.     , fe_sto_kandidat.mcm_sto_nummer
  149.     , decode(pkg_baumassnahme.get_toc_status(pkg_baumassnahme.get_baumassnahme_id_nb(fe_sto_kandidat.fe_sto_kandidat_id)),'getoct',1,'enttoct',0,NULL) getoct
  150. FROM
  151.     fe
  152.     , fe_sto_kandidat
  153.     , do_typ
  154.     , baumassnahme
  155.     , handlung
  156. WHERE fe.objekt_id = fe_sto_kandidat.objekt_id
  157.     AND fe_sto_kandidat.fe_sto_kandidat_id = baumassnahme.fe_sto_kandidat_id
  158.     AND baumassnahme.baumassnahme_id = handlung.baumassnahme_id
  159.     AND handlung.datum_ist IS
  160.     NOT NULL
  161.     AND handlung.status_nr >= 21
  162.     AND fe_sto_kandidat.aktiv = 1
  163.     AND fe_sto_kandidat.ausgewaehlt = 1
  164.     AND do_typ.do_typ_id = fe.do_typ_id
  165. UNION ALL
  166. SELECT
  167.     DISTINCT fe.werk_id
  168.     , fe.basisschluessel
  169.     , fe_sto_kandidat.bs_sto
  170.     , fe.do_typ_id
  171.     , do_typ.do_typ_name
  172.     , fe.hersteller
  173.     , substr (fe.fe_name, 1, 40) fe_name
  174.     , fe_sto_kandidat.status_nr
  175.         , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',80,NULL),'DDMMYYYY') STATUS_80_DATE
  176.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',82,NULL),'DDMMYYYY') STATUS_82_DATE
  177.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',100,NULL),'DDMMYYYY') STATUS_100_DATE
  178.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',950,NULL),'DDMMYYYY') STATUS_950_DATE
  179.     , NULL aufbauart
  180.     , NULL ausfuehrung
  181.     , NULL generation
  182.     , verb.termial_out_bs
  183.     , verb.link_id
  184.         , fe_sto_kandidat.fe_sto_kandidat_id
  185.    
  186.    
  187.     , TO_CHAR(pkg_guest.get_handlung_datum_spi(fe_sto_kandidat.fe_sto_kandidat_id,1,'IST',fe_sto_kandidat.status_nr,fe_sto_kandidat.status_zeichen),'DDMMYYYY') DATUM_STATUS_AKT
  188.     , (SELECT
  189.     substr (MAX (betriebsortart), 1, 40) betriebsortart
  190. FROM
  191.     betriebsortart
  192. WHERE betriebsortart_id IN (
  193.     SELECT
  194.         betriebsortart_id
  195.     FROM
  196.         betriebsort bo
  197.         , fe_sto_kandidat festo
  198.     WHERE bo.betriebsort_id = festo.betriebsort_id
  199.         AND festo.fe_sto_kandidat_id = fe_sto_kandidat.fe_sto_kandidat_id
  200.     )) SITE_CATEGORY
  201.     , fe_sto_kandidat.mcm_ne_nummer
  202.     , fe_sto_kandidat.mcm_sto_nummer
  203.     , decode(pkg_baumassnahme.get_toc_status(pkg_baumassnahme.get_baumassnahme_id_nb(fe_sto_kandidat.fe_sto_kandidat_id)),'getoct',1,'enttoct',0,NULL) getoct
  204. FROM
  205.     fe
  206.     , fe_sto_kandidat
  207.     , do_typ
  208.     , (
  209.     SELECT
  210.         verbindung.start_id termial_in_id
  211.         , verbindung.basisschluessel_ziel termial_out_bs
  212.         , verbindung.basisschluessel link_id
  213.     FROM
  214.         verbindung
  215.         , fe term_out_fe
  216.         , objekt_zeit oz
  217.     WHERE verbindung.ebene_typ_id = 10
  218.         AND verbindung.ziel_id = term_out_fe.objekt_id
  219.         AND term_out_fe.do_typ_id = '26'
  220.         AND verbindung.objekt_id = oz.objekt_id
  221.         AND oz.abn IS
  222.         NULL
  223.     UNION
  224.     SELECT
  225.         verbindung.ziel_id termial_in_id
  226.         , verbindung.basisschluessel_start termial_out_bs
  227.         , verbindung.basisschluessel link_id
  228.     FROM
  229.         verbindung
  230.         , fe term_out_fe
  231.         , objekt_zeit oz
  232.     WHERE verbindung.ebene_typ_id = 10
  233.         AND verbindung.start_id = term_out_fe.objekt_id
  234.         AND term_out_fe.do_typ_id = '26'
  235.         AND verbindung.objekt_id = oz.objekt_id
  236.         AND oz.abn IS
  237.         NULL
  238.     ) verb
  239.     , baumassnahme
  240.     , handlung
  241. WHERE fe.do_typ_id = '64'
  242.     AND fe.objekt_id = fe_sto_kandidat.objekt_id
  243.     AND fe_sto_kandidat.fe_sto_kandidat_id = baumassnahme.fe_sto_kandidat_id
  244.     AND baumassnahme.baumassnahme_id = handlung.baumassnahme_id
  245.     AND nvl(TO_CHAR(handlung.datum_ist),0) NOT LIKE 0
  246.     AND handlung.status_nr >= 21
  247.     AND fe_sto_kandidat.aktiv = 1
  248.     AND fe_sto_kandidat.ausgewaehlt = 1
  249.     AND do_typ.do_typ_id = fe.do_typ_id
  250.     AND fe.objekt_id = verb.termial_in_id(+)
  251. ) ORDER BY bdo ASC
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!