Advertisement
psi_mmobile

Untitled

Sep 2nd, 2020
1,902
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 1.98 KB | None | 0 0
  1. SELECT ptd.person_id,
  2. ptd.date_day,
  3. ptd.work_code_id,
  4. p.vo_person_category_id,
  5. ptd.is_locked,
  6. '0201' AS company,
  7. TO_CHAR(pd.date_day,'MM') AS MONTH,
  8. '1' AS order_number,
  9. LPAD(p.company_nr,6,'0') AS person_number,
  10. '1' AS treatment_code,
  11. '3' AS TYPE,
  12. wc.short_code AS bonus_code,
  13. SUM(p.transport_allowance) AS euro_value,
  14. LPAD('0',42,'0') AS blank
  15. FROM pd_trf_day ptd, pd_day pd, person p, work_code wc
  16. WHERE ptd.date_day = pd.date_day
  17. AND ptd.person_id = pd.person_id
  18. AND pd.person_id = p.person_id
  19. AND ptd.work_code_id = wc.work_code_id
  20. AND wc.short_code = '043'
  21. AND wc.vehicle_owner_id = 101393
  22. --and wc.work_code_std_category_id = 1
  23. AND pd.day_journey_role IS NULL
  24. AND p.person_status_id = 0
  25. GROUP BY ptd.person_id,ptd.date_day,ptd.work_code_id,p.vo_person_category_id,ptd.is_locked,LPAD(p.company_nr,6,'0'),TO_CHAR(pd.date_day,'MM'),wc.short_code,'0201','1','1','3',LPAD('0',42,'0')
  26. HAVING SUM(p.transport_allowance) > 0
  27. UNION
  28. SELECT ptd.person_id,
  29. ptd.date_day,
  30. ptd.work_code_id,
  31. p.vo_person_category_id,
  32. ptd.is_locked,
  33. '0201' AS company,
  34. TO_CHAR(pd.date_day,'MM') AS MONTH,
  35. '1' AS order_number,
  36. LPAD(p.company_nr,6,'0') AS person_number,
  37. '1' AS treatment_code,
  38. '3' AS TYPE,
  39. wc.short_code AS bonus_code,
  40. 14 * COUNT(CASE WHEN (SELECT COUNT(*) FROM pd_trf_day WHERE date_day = ptd.date_day AND person_id = ptd.person_id AND work_code_id = 104519) > 0 THEN 1 END) AS euro_value,
  41. LPAD('0',42,'0') AS blank
  42. FROM pd_trf_day ptd, pd_day pd, person p, work_code wc
  43. WHERE ptd.date_day = pd.date_day
  44. AND ptd.person_id = pd.person_id
  45. AND pd.person_id = p.person_id
  46. AND ptd.work_code_id = wc.work_code_id
  47. AND wc.short_code = '030'
  48. --and wc.work_code_std_category_id = 1
  49. AND wc.vehicle_owner_id = 101393
  50. AND p.person_status_id = 0
  51. GROUP BY ptd.person_id, ptd.date_day, ptd.work_code_id,p.vo_person_category_id,ptd.is_locked,LPAD(p.company_nr,6,'0'),TO_CHAR(pd.date_day,'MM'),wc.short_code,'0201','1','1','3',LPAD('0',42,'0')
  52. HAVING COUNT(CASE WHEN ptd.work_code_id = 104519 THEN 1 END) > 0;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement