Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select ptd.pd_trf_day_id, nvl(ptd.wbs_id, vo.default_work_wbs_id) wbs_id,
- --of_owner.P_WBS.getWBSPath(nvl(ptd.wbs_id, vo.default_work_wbs_id)) wbs_full_path,
- wbs.name wbs_name,
- wbs.ref_number wbs_ref_number,
- ptd.person_id,
- of_owner.date_to_milliseconds(ptd.date_day) date_day,
- ptd.creation_date,
- of_owner.date_to_milliseconds(ptd.lm_date) lm_date,
- ptd.is_locked,
- ptd.is_locked_automatically,
- 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,
- ptd.quarter_one,ptd.quarter_two,ptd.quarter_three,ptd.quarter_four,
- ptd.start_time,
- ptd.stop_time,
- ptd.trf_dhr,
- ptd.planned_trf_dhr,
- ptd.planned_am,
- ptd.planned_pm,
- decode(nvl(wc.is_generic,'Y'), 'Y', ptd.work_code_id, wc.generic_work_code_id) work_code_id,
- ptd.cal_closing_period_id,
- ptd.erp_line_id,
- pd.meal_allowance, pd.vehicle_id, pd.traject, pd.transport, pd.long_distance, pd.day_journey_role
- , ptd.trf_comment, ptd.user_comment,ptd.PD_TRF_USER_COMMENT, ptd.TIME_SHEET_PAYROLL_COMMENT,
- case when ptd.is_locked = 'Y' then 46 -- ptd.validation_status_id --
- when (ptd.trf_comment like 'HRP_EMPTY_LINE%'
- or ptd.trf_comment like 'HRP_WORKING_SCHEDULE_LINE%'
- or ptd.trf_comment like 'HRP_PWC%') then 46
- else ptd.validation_status_id end validation_status_id,
- ptd.validation_status_id original_validation_status_id,
- to_char(ptd.day_json_dynamic_form) day_json_dynamic_form,
- to_char(ptd.line_json_dynamic_form) line_json_dynamic_form,
- wc.work_code_std_category_id
- 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
- , OF_OWNER.operation_center oc, OF_OWNER.person user_p, OF_OWNER.vo_person_category user_vopc, OF_OWNER.wbs wbs
- where vo.vehicle_owner_id = vopc.vehicle_owner_id
- and oc.operation_center_id = vo.default_operation_center_id
- and vopc.vo_person_category_id = p.vo_person_category_id
- and p.person_id = ptd.person_id
- and ptd.wbs_id = wbs.wbs_id(+)
- --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)
- and ptd.date_day = pd.date_day
- and ptd.person_id = pd.person_id
- and ptd.work_code_id = wc.work_code_id
- and :user_person_id = user_p.person_id
- and user_p.vo_person_category_id = user_vopc.vo_person_category_id
- and user_vopc.vehicle_owner_id = vo.vehicle_owner_id
- 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)
- )
- and (nvl(wc.is_generic,'Y') = 'Y' or wc.generic_work_code_id is not null)
- and ptd.PD_TRF_TYPE_ID = 1
- --and (p.person_id = :user_person_id or wc.work_code_std_category_id = 2)
- and (:wbs_ids is null
- or (:extended is null and ptd.wbs_id in (SELECT * FROM TABLE(of_owner.in_list(:wbs_ids))))
- or (:extended is not null and p.person_id in (
- select person_id from OF_OWNER.pd_trf_day
- where wbs_id in (SELECT * FROM TABLE(of_owner.in_list(:wbs_ids)))
- and date_day between of_owner.date_from_milliseconds(:from_date) and of_owner.date_from_milliseconds(:to_date)
- and PD_TRF_TYPE_ID = 1
- )
- )
- ) and (:person_ids is null or p.person_id in (SELECT * FROM TABLE(of_owner.in_list(:person_ids))))
- --and ptd.lm_date > of_owner.date_from_milliseconds(nvl(:lm_date, of_owner.date_to_milliseconds(trunc(sysdate,'YEAR'))))
- and (:lm_date is null or ptd.lm_date > of_owner.date_from_milliseconds(:lm_date))
- --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
- -- 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)
- and ptd.date_day between of_owner.date_from_milliseconds(:from_date)
- and of_owner.date_from_milliseconds(:to_date)
Advertisement
Add Comment
Please, Sign In to add comment