Advertisement
psi_mmobile

Untitled

Mar 15th, 2021
251
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.32 KB | None | 0 0
  1. SELECT pd.person_id,
  2. p.vo_person_category_id,
  3. pd.is_locked,
  4. vo.contact_msisdn as "Nummer werkgever",
  5. replace(p.company_nr,'HBB') as "Nummer werknemer",
  6. vo.company_name as "Naam werkgever",
  7. p.last_name || ' ' || p.first_name as "Naam werknemer",
  8. decode(wc.short_code,'P',decode(pdd.day_journey_role,'P','P','D ','CH','DA','CH','P'),wc.short_code) as "Looncode",
  9. decode(wc.work_code_std_category_id,1,p_wbs.getlvl0WbsRefNumber(w.wbs_id),2,'AFWEZIG') as "Werf",
  10. pd.date_day as "Datum",
  11. replace(to_char(extract (hour from numtodsinterval(sum(pd.TRF_DHR), 'HOUR' )),'00'),' ','') as "Uren",
  12. replace(to_char(extract (minute from numtodsinterval(sum(pd.TRF_DHR), 'HOUR' )),'00'),' ','') as "Minuten",
  13. -- decode(wc.short_code,'P',nvl(pdd.declared_vehicle_km,0),'0') as "Gerapporteerde Km",
  14. case when wc.short_code = 'P' or wc.short_code = 'GWH' then nvl(pdd.declared_vehicle_km,0) else 0 end as "Gerapporteerde Km",
  15. case when wc.short_code = 'P' or wc.short_code = 'GWH' then nvl(pdd.declared_private_km,0) else 0 end as "Prive Km",
  16. pd.pd_trf_user_comment as "Commentaar"
  17. FROM pd_trf_day pd,
  18. person p,
  19. pd_day pdd,
  20. work_code wc,
  21. wbs w,
  22. vo_person_category vopc,
  23. vehicle_owner vo
  24. WHERE pd.person_id = pdd.person_id
  25. AND pd.date_day = pdd.date_day
  26. AND pdd.person_id = p.person_id
  27. and p.vo_person_category_id = vopc.vo_person_category_id
  28. and vopc.vehicle_owner_id = vo.vehicle_owner_id
  29. AND pd.work_code_id = wc.work_code_id
  30. and wc.vehicle_owner_id = vo.vehicle_owner_id
  31. and pd.wbs_id = w.wbs_id(+)
  32. and wc.vehicle_owner_id = 101404
  33. and wc.work_code_std_category_id in (1,2)
  34. and nvl(pd.TRF_DHR,0) > 0
  35. and p.person_status_id = 0
  36. group by pd.person_id,p.vo_person_category_id,pd.is_locked,pd.date_day,w.ref_number,p.company_nr,wc.short_code,vo.contact_msisdn,vo.company_name,p.last_name || ' ' || p.first_name,
  37. decode(wc.short_code,'P',decode(pdd.day_journey_role,'P','P','D ','CH','DA','CH','P'),wc.short_code),decode(wc.work_code_std_category_id,1,p_wbs.getlvl0WbsRefNumber(w.wbs_id),2,'AFWEZIG'),
  38. case when wc.short_code = 'P' or wc.short_code = 'GWH' then nvl(pdd.declared_vehicle_km,0) else 0 end,
  39. case when wc.short_code = 'P' or wc.short_code = 'GWH' then nvl(pdd.declared_private_km,0) else 0 end,
  40. pd.pd_trf_user_comment
  41. order by pd.date_day,"Nummer werknemer";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement