psi_mmobile

Untitled

Jun 1st, 2026
77
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.87 KB | None | 0 0
  1. select ptd.pd_trf_day_id, nvl(ptd.wbs_id, vo.default_work_wbs_id) wbs_id,
  2. --of_owner.P_WBS.getWBSPath(nvl(ptd.wbs_id, vo.default_work_wbs_id)) wbs_full_path,
  3. wbs.name wbs_name,
  4. wbs.ref_number wbs_ref_number,
  5. ptd.person_id,
  6. of_owner.date_to_milliseconds(ptd.date_day) date_day,
  7. ptd.creation_date,
  8. of_owner.date_to_milliseconds(ptd.lm_date) lm_date,
  9. ptd.is_locked,
  10. ptd.is_locked_automatically,
  11. case when OF_OWNER.P_PD_TRF_HR.is_paid_per_hour(p.person_id) = 'Y' then ptd.trf_dhr else null end trf_dhr,
  12. ptd.quarter_one,ptd.quarter_two,ptd.quarter_three,ptd.quarter_four,
  13. ptd.start_time,
  14. ptd.stop_time,
  15. ptd.trf_dhr,
  16. ptd.planned_trf_dhr,
  17. ptd.planned_am,
  18. ptd.planned_pm,
  19. decode(nvl(wc.is_generic,'Y'), 'Y', ptd.work_code_id, wc.generic_work_code_id) work_code_id,
  20. ptd.cal_closing_period_id,
  21. ptd.erp_line_id,
  22. pd.meal_allowance, pd.vehicle_id, pd.traject, pd.transport, pd.long_distance, pd.day_journey_role
  23. , ptd.trf_comment, ptd.user_comment,ptd.PD_TRF_USER_COMMENT, ptd.TIME_SHEET_PAYROLL_COMMENT,
  24. case when ptd.is_locked = 'Y' then 46 --  ptd.validation_status_id --
  25. when (ptd.trf_comment like 'HRP_EMPTY_LINE%'
  26. or ptd.trf_comment like 'HRP_WORKING_SCHEDULE_LINE%'
  27. or ptd.trf_comment like 'HRP_PWC%') then 46
  28. else ptd.validation_status_id end validation_status_id,
  29. ptd.validation_status_id original_validation_status_id,
  30. to_char(ptd.day_json_dynamic_form) day_json_dynamic_form,
  31. to_char(ptd.line_json_dynamic_form) line_json_dynamic_form,
  32. wc.work_code_std_category_id
  33. from OF_OWNER.pd_trf_day ptd, OF_OWNER.pd_day pd, OF_OWNER.person p, OF_OWNER.vo_person_category vopc, OF_OWNER.vehicle_owner vo, OF_OWNER.work_code wc
  34. , OF_OWNER.operation_center oc, OF_OWNER.person user_p, OF_OWNER.vo_person_category user_vopc, OF_OWNER.wbs wbs
  35. where vo.vehicle_owner_id = vopc.vehicle_owner_id
  36. and oc.operation_center_id = vo.default_operation_center_id
  37. and vopc.vo_person_category_id = p.vo_person_category_id
  38. and p.person_id = ptd.person_id
  39. and ptd.wbs_id = wbs.wbs_id(+)
  40. --and ptd.date_day between nvl(to_date(:from_date, 'DD/MM/YYYY'), trunc(sysdate)) and nvl(to_date(:to_date, 'DD/MM/YYYY'), trunc(sysdate) + 1)
  41. and ptd.date_day = pd.date_day
  42. and ptd.person_id = pd.person_id
  43. and ptd.work_code_id = wc.work_code_id
  44. and :user_person_id = user_p.person_id
  45. and user_p.vo_person_category_id = user_vopc.vo_person_category_id
  46. and user_vopc.vehicle_owner_id = vo.vehicle_owner_id
  47. and ((:fetch_work_time = 'Y' AND wc.work_code_std_category_id = 1) OR (:fetch_absence = 'Y' AND wc.work_code_std_category_id = 2)
  48. )
  49. and (nvl(wc.is_generic,'Y') = 'Y' or wc.generic_work_code_id is not null)
  50. and ptd.PD_TRF_TYPE_ID = 1
  51. --and (p.person_id = :user_person_id or wc.work_code_std_category_id = 2)
  52. and (:wbs_ids is null
  53. or (:extended is null and ptd.wbs_id in (SELECT * FROM TABLE(of_owner.in_list(:wbs_ids))))
  54. or (:extended is not null and p.person_id in (
  55. select person_id from OF_OWNER.pd_trf_day
  56. where wbs_id in (SELECT * FROM TABLE(of_owner.in_list(:wbs_ids)))
  57. and date_day between of_owner.date_from_milliseconds(:from_date) and of_owner.date_from_milliseconds(:to_date)
  58. and PD_TRF_TYPE_ID = 1
  59. )
  60. )
  61. ) and (:person_ids is null or p.person_id in (SELECT * FROM TABLE(of_owner.in_list(:person_ids))))
  62. --and ptd.lm_date > of_owner.date_from_milliseconds(nvl(:lm_date, of_owner.date_to_milliseconds(trunc(sysdate,'YEAR'))))
  63. and (:lm_date is null or ptd.lm_date > of_owner.date_from_milliseconds(:lm_date))
  64. --and exists (select af.app_features_id from app_features af, mf_customer mfc where tag_name like '%prefill%' and af.mf_customer_id = mfc.mf_customer_id and mfc.erp_id = vo.vehicle_owner_id
  65. --            union select af.app_features_id from app_features af, mfc_mobile_worker mw where tag_name like '%prefill%' and af.mfc_mobile_worker_id = mw.mfc_mobile_worker_id and mw.erp_id = p.person_id)
  66. and ptd.date_day between of_owner.date_from_milliseconds(:from_date)
  67. and of_owner.date_from_milliseconds(:to_date)
Advertisement
Add Comment
Please, Sign In to add comment