API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 7th, 2018
122
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.19 KB | None | 0 0
raw download clone embed print report
  1. INSERT INTO G2_SERVICE_DATA_TEMP ("SERVICE_ID", "CUSTOMER_SPP_ID", "CUSTOMER_NAME", "LOKACIJA_STORITVE", "TIP_STORITVE",
  2. "SERVICE_NAME", "PATH_ID", "PATH_NAME", "PATH_TYPE", "ACCESS_ID", "SERVICE_PROVIDER", "SLA_QOS_ID", "SLA_QOS_NAME", "ID_VODA",
  3. "SKRBNIK", "SKRBNIK_EMAIL", "USERNAME", "POINT_CODE", "A_SITE", "CI_NAME", "CI_DESCRIPTION", "CI_ID", "UPORABNIK",
  4. "NIVO1_PRIOR_ODPR_NAPAK", "NIVO2_CAS_ODPR_NAPAK", "ODZIVNI_CAS", "SLA_P1_ODZIVNI_CAS", "SERVICE_TARGET",
  5. "SERVICE_PROFILE", "TF_STEVILKA", "P1_ODZIVNI_CAS", "P1_PRIOR_ODPR_NAPAK", "P1_CAS_ODPR_NAPAK",
  6. "P2_ODZIVNI_CAS", "P2_PRIOR_ODPR_NAPAK", "P2_CAS_ODPR_NAPAK", "P3_ODZIVNI_CAS", "P3_PRIOR_ODPR_NAPAK",
  7. "P3_CAS_ODPR_NAPAK", "P4_ODZIVNI_CAS", "P4_PRIOR_ODPR_NAPAK", "P4_CAS_ODPR_NAPAK")
  8.  
  9. SELECT
  10. srv.resource_inst_id, g2.CUSTOMER_SPP_ID, g2.CUSTOMER_NAME, g2.LOKACIJA_STORITVE, g2.TIP_STORITVE, g2.SERVICE_NAME, g2.PATH_ID, g2.PATH_NAME,
  11. cpi.type, cpi.order_num, operater.attr_value, g2.SLA_QOS_ID, g2.SLA_QOS_NAME, ui.udc_name,
  12. g2.SKRBNIK, g2.SKRBNIK_EMAIL, ras.attr_value, pc.value, si3.site_hum_id, srv.name, srv.name, CAST(uda.attr_value AS VARCHAR2(10)), LTRIM(RTRIM(UPPER(upor.attr_value))),
  13. ras1.attr_value, ras2.attr_value, ras3.attr_value, p43.attr_value, g2.SERVICE_TARGET, g2.SERVICE_PROFILE, ua.udc_attr_value, p11.attr_value, p11.attr_value, p12.attr_value,
  14. p13.attr_value, p21.attr_value, p22.attr_value, p23.attr_value, p31.attr_value, p32.attr_value, p33.attr_value,
  15. p41.attr_value, p42.attr_value, p43.attr_value
  16.  
  17. FROM RESOURCE_INST@COPBTI.UP.TELEKOM.SI srv
  18. INNER JOIN G2_SERVICE_DATA g2
  19. INNER JOIN RESOURCE_DEFINITION_INST@COPBTI.UP.TELEKOM.SI srv_type ON srv_type.definition_inst_id = srv.definition_inst_id
  20. INNER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI uda ON srv.resource_inst_id = uda.resource_inst_id and uda.val_attr_inst_id = 1715
  21. LEFT OUTER JOIN resource_associations@COPBTI.UP.TELEKOM.SI ra on srv.resource_inst_id=ra.resource_inst_id and ra.target_type_id=10 --PATH
  22. LEFT OUTER JOIN resource_associations@COPBTI.UP.TELEKOM.SI ra_tf on srv.resource_inst_id=ra_tf.resource_inst_id and ra_tf.target_type_id=1762
  23. LEFT OUTER JOIN udc_attribute@COPBTI.UP.TELEKOM.SI ua on ra_tf.target_inst_id=ua.udc_inst_id and ua.attr_def_id=4300 and ua.udc_attr_value not like '%|_%' escape '|'
  24. LEFT OUTER JOIN circ_path_inst@COPBTI.UP.TELEKOM.SI cpi on ra.target_inst_id=cpi.circ_path_inst_id
  25. LEFT OUTER JOIN
  26. (CIRC_PATH_ELEMENT@COPBTI.UP.TELEKOM.SI cpe
  27. JOIN PATH_LEG_MEMBER@COPBTI.UP.TELEKOM.SI plm on cpe.element_inst_id=plm.element_inst_id
  28. JOIN EPA@COPBTI.UP.TELEKOM.SI tport on cpe.port_inst_id=tport.port_inst_id
  29. JOIN EQUIP_INST@COPBTI.UP.TELEKOM.SI ei on tport.equip_inst_id=ei.equip_inst_id
  30. JOIN G2_SDO_IGNORE gsi on ei.type=gsi.equip_type and sdo_ignore is null
  31. JOIN POINT_CODE@COPBTI.UP.TELEKOM.SI pc on ei.equip_inst_id=pc.equip_inst_id)
  32. on cpi.circ_path_inst_id=cpe.circ_path_inst_id
  33. and cpe.element_type='E' LEFT OUTER JOIN site_inst@COPBTI.UP.TELEKOM.SI si2 ON si2.site_inst_id= cpi.z_side_site_id
  34. LEFT OUTER JOIN site_inst@COPBTI.UP.TELEKOM.SI si3 ON si3.site_inst_id= cpi.a_side_site_id
  35. LEFT OUTER JOIN resource_associations@COPBTI.UP.TELEKOM.SI ra2 ON srv.resource_inst_id = ra2.resource_inst_id AND ra2.target_type_id = 16 --SITE
  36. LEFT OUTER JOIN site_inst@COPBTI.UP.TELEKOM.SI si ON si.site_inst_id= ra2.target_inst_id
  37. LEFT OUTER JOIN resource_associations@COPBTI.UP.TELEKOM.SI vod on srv.resource_inst_id=vod.resource_inst_id and vod.target_type_id=1384
  38. LEFT OUTER JOIN udc_instance@COPBTI.UP.TELEKOM.SI ui on vod.target_inst_id=ui.udc_inst_id
  39. LEFT OUTER JOIN resource_associations@COPBTI.UP.TELEKOM.SI ra_sla on srv.resource_inst_id=ra_sla.resource_inst_id and ra_sla.target_type_id=1342
  40. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI ras1 on ra_sla.target_inst_id=ras1.resource_inst_id and ras1.val_attr_inst_id=1840
  41. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI ras2 on ra_sla.target_inst_id=ras2.resource_inst_id and ras2.val_attr_inst_id=1841
  42. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI ras3 on ra_sla.target_inst_id=ras3.resource_inst_id and ras3.val_attr_inst_id=6475
  43. LEFT OUTER JOIN RESOURCE_ASSOCIATIONS@COPBTI.UP.TELEKOM.SI naroc ON srv.resource_inst_id = naroc.resource_inst_id AND naroc.target_type_id = 6 --NAROČNIK
  44. LEFT OUTER JOIN resource_attr_settings@copbti.up.telekom.si upor ON srv.resource_inst_id=upor.resource_inst_id and upor.val_attr_inst_id=1820 --UPORABNIK
  45. LEFT OUTER JOIN VAL_CUSTOMER@COPBTI.UP.TELEKOM.SI cust ON naroc.target_inst_id = cust.cust_inst_id -- DODATEK
  46. LEFT OUTER JOIN RESOURCE_ATTR_SETTINGS@COPBTI.UP.TELEKOM.SI operater on srv.resource_inst_id=operater.resource_inst_id and operater.val_attr_inst_id in
  47. (1410,1460,1502,1680,1685,1687,1898,4750,5335,6155,6195,6915,8195,8217,8617,8655,11140,11420,11699)
  48. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p11 ON ra_sla.target_inst_id=p11.resource_inst_id and p11.val_attr_inst_id=11478
  49. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p12 ON ra_sla.target_inst_id=p12.resource_inst_id and p12.val_attr_inst_id=11479
  50. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p13 ON ra_sla.target_inst_id=p13.resource_inst_id and p13.val_attr_inst_id=11480
  51. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p21 ON ra_sla.target_inst_id=p21.resource_inst_id and p21.val_attr_inst_id=11481
  52. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p22 ON ra_sla.target_inst_id=p22.resource_inst_id and p22.val_attr_inst_id=11482
  53. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p23 ON ra_sla.target_inst_id=p23.resource_inst_id and p23.val_attr_inst_id=11483
  54. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p31 ON ra_sla.target_inst_id=p31.resource_inst_id and p31.val_attr_inst_id=11484
  55. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p32 ON ra_sla.target_inst_id=p32.resource_inst_id and p32.val_attr_inst_id=11485
  56. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p33 ON ra_sla.target_inst_id=p33.resource_inst_id and p33.val_attr_inst_id=11486
  57. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p41 ON ra_sla.target_inst_id=p41.resource_inst_id and p41.val_attr_inst_id=11487
  58. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p42 ON ra_sla.target_inst_id=p42.resource_inst_id and p42.val_attr_inst_id=11488
  59. LEFT OUTER JOIN resource_attr_settings@COPBTI.UP.TELEKOM.SI p43 ON ra_sla.target_inst_id=p43.resource_inst_id and p43.val_attr_inst_id=11489
  60.  
  61. LEFT OUTER JOIN
  62. (resource_attr_settings@COPBTI.UP.TELEKOM.SI ras INNER JOIN val_attr_name@COPBTI.UP.TELEKOM.SI val ON ras.val_attr_inst_id = val.val_attr_inst_id AND val.attr_name like '%USERNAME%')
  63. ON ras.resource_inst_id = srv.resource_inst_id
  64. WHERE srv.last_mod_ts >= TO_DATE(SUBSTR(?, 1,19), 'YYYY-MM-DD HH24:MI:SS')
  65. AND ROWNUM <= 1000
  66. and
  67. (cpi.circ_path_inst_id,plm.sequence) in
  68. (select cpi.circ_path_inst_id,max(plm.sequence) from
  69. CIRC_PATH_INST@COPBTI.UP.TELEKOM.SI cpi
  70. LEFT OUTER JOIN
  71. (CIRC_PATH_ELEMENT@COPBTI.UP.TELEKOM.SI cpe
  72. JOIN PATH_LEG_MEMBER@COPBTI.UP.TELEKOM.SI plm on cpe.element_inst_id=plm.element_inst_id
  73. JOIN EPA@COPBTI.UP.TELEKOM.SI tport on cpe.port_inst_id=tport.port_inst_id
  74. JOIN EQUIP_INST@COPBTI.UP.TELEKOM.SI ei on tport.equip_inst_id=ei.equip_inst_id
  75. JOIN G2_SDO_IGNORE gsi on ei.type=gsi.equip_type and sdo_ignore is null
  76. JOIN POINT_CODE@COPBTI.UP.TELEKOM.SI pc on ei.equip_inst_id=pc.equip_inst_id)
  77. on cpi.circ_path_inst_id=cpe.circ_path_inst_id
  78. and cpe.element_type='E'
  79. group by cpi.circ_path_inst_id)
  80.  
  81. GROUP BY srv.resource_inst_id, g2.CUSTOMER_SPP_ID, g2.CUSTOMER_NAME, g2.LOKACIJA_STORITVE, g2.TIP_STORITVE, g2.SERVICE_NAME, g2.PATH_ID, g2.PATH_NAME,
  82. cpi.type, cpi.order_num, operater.attr_value, g2.SLA_QOS_ID, g2.SLA_QOS_NAME, ui.udc_name,
  83. g2.SKRBNIK, g2.SKRBNIK_EMAIL, ras.attr_value, pc.value, si3.site_hum_id, srv.name, srv.name, CAST(uda.attr_value AS VARCHAR2(10)), LTRIM(RTRIM(UPPER(upor.attr_value))),
  84. ras1.attr_value, ras2.attr_value, ras3.attr_value, p43.attr_value, g2.SERVICE_TARGET, g2.SERVICE_PROFILE, ua.udc_attr_value, p11.attr_value, p11.attr_value, p12.attr_value,
  85. p13.attr_value, p21.attr_value, p22.attr_value, p23.attr_value, p31.attr_value, p32.attr_value, p33.attr_value,
  86. p41.attr_value, p42.attr_value, p43.attr_value;
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!