API tools faq
paste
Advertisement
psi_mmobile

Untitled

psi_mmobile
Nov 5th, 2019
300
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.65 KB | None | 0 0
raw download clone embed print report
  1. select p.person_id, p.company_nr, p.last_name || ' ' || p.first_name as name,
  2. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')-touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+1 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM'))) as d01,
  3. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 1 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 1), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+2 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +2)) as d02,
  4. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 2 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 2), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+3 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +3)) as d03,
  5. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 3 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 3), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+4 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +4)) as d04,
  6. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 4 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 4), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+5 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +5)) as d05,
  7. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 5 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 5), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+6 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +6)) as d06,
  8. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 6 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 6), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+7 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +7)) as d07,
  9. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 7 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 7), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+8 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +8)) as d08,
  10. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 8 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 8), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+9 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +9)) as d09,
  11. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 9 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 9), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+10 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +10)) as d10,
  12. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 10 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 10), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+11 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +11)) as d11,
  13. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 11 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 11), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+12 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +12)) as d12,
  14. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 12 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 12), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+13 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +13)) as d13,
  15. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 13 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 13), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+14 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +14)) as d14,
  16. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 14 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 14), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+15 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +15)) as d15,
  17. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 15 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 15), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+16 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +16)) as d16,
  18. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 16 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 16), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+17 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +17)) as d17,
  19. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 17 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 17), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+18 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +18)) as d18,
  20. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 18 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 18), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+19 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +19)) as d19,
  21. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 19 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 19), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+20 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +20)) as d20,
  22. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 20 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 20), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+21 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +21)) as d21,
  23. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 21 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 21), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+22 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +22)) as d22,
  24. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 22 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 22), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+23 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +23)) as d23,
  25. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 23 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 23), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+24 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +24)) as d24,
  26. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 24 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 24), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+25 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +25)) as d25,
  27. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 25 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 25), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+26 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +26)) as d26,
  28. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 26 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 26), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+27 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +27)) as d27,
  29. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 27 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 27), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+28 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +28)) as d28,
  30. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 28 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 28), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+29 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +29)) as d29,
  31. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 29 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 29), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+30 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +30)) as d30,
  32. p_person.getNbrBadges(gup.person_id, trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 30 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+ 30), trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM')+31 -touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') +31)) as d31,
  33. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY'),'MM') ))) as c01,
  34. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 1 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 1 ))) as c02,
  35. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 2 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 2 ))) as c03,
  36. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 3 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 3 ))) as c04,
  37. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 4 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 4 ))) as c05,
  38. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 5 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 5 ))) as c06,
  39. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 6 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 6 ))) as c07,
  40. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 7 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 7 ))) as c08,
  41. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 8 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 8 ))) as c09,
  42. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 9 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 9 ))) as c10,
  43. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 10 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 10 ))) as c11,
  44. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 11 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 11 ))) as c12,
  45. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 12 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 12 ))) as c13,
  46. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 13 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 13 ))) as c14,
  47. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 14 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 14 ))) as c15,
  48. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 15 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 15 ))) as c16,
  49. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 16 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 16 ))) as c17,
  50. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 17 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 17 ))) as c18,
  51. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 18 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 18 ))) as c19,
  52. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 19 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 19 ))) as c20,
  53. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 20 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 20 ))) as c21,
  54. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 21 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 21 ))) as c22,
  55. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 22 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 22 ))) as c23,
  56. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 23 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 23 ))) as c24,
  57. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 24 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 24 ))) as c25,
  58. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 25 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 25 ))) as c26,
  59. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 26 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 26 ))) as c27,
  60. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 27 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 27 ))) as c28,
  61. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 28 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 28 ))) as c29,
  62. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 29 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 29 ))) as c30,
  63. p_pd_day.getFlagColor(gup.person_id, trunc ( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 30 - touserdatediff( trunc( to_date('20/09/2019','DD/MM/YYYY') ,'MM') + 30 ))) as c31
  64. from gu_person gup, person p
  65. where p.person_id = gup.person_id
  66. and p.vo_person_category_id = decode (-1, -1, p.vo_person_category_id, -1)
  67. and gup.gui_user_id = 101853
  68. and p.person_status_id=0
  69. order by name;
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!