Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT PdTrfDay.DATE_DAY,
- PdTrfDay.PD_TRF_DAY_ID,
- PdTrfDay.DESTINATION_POI_ID,
- PdTrfDay.IS_LOCKED,
- PdTrfDay.IS_MODIFIED_BY_USER,
- PdTrfDay.OC_AGGREG_ADMIN_STATUS_ID,
- PdTrfDay.OC_DAY_AGGREG_ID,
- PdTrfDay.OPERATION_MODE_ID,
- PdTrfDay.PD_TRF_TYPE_ID,
- PdTrfDay.PD_TRF_USER_COMMENT,
- PdTrfDay.PERSON_ID,
- PdTrfDay.REF_NUMBER,
- CAST(PdTrfDay.START_TIME AS TIMESTAMP) AS STARTTIME,
- CAST(PdTrfDay.STOP_TIME AS TIMESTAMP) AS STOPTIME,
- PdTrfDay.TRF_DHR,
- PdTrfDay.TRF_PHR,
- PdTrfDay.WBS_ID,
- PdTrfDay.WORK_CODE_ID,
- PdTrfDay.VEHICLE_ID,
- WorkCode.WORK_CODE_ID AS WORK_CODE_ID1,
- GuPerson.GUI_USER_ID,
- GuPerson.PERSON_ID AS PERSON_ID1,
- OperationMode.NAME,
- OperationMode.OPERATION_MODE_ID AS OPERATION_MODE_ID1,
- OperationMode.SHORT_NAME,
- OcAggregAdminStatus.OC_AGGREG_ADMIN_STATUS_ID AS OC_AGGREG_ADMIN_STATUS_ID1,
- OcDayAggreg.OC_DAY_AGGREG_ID AS OC_DAY_AGGREG_ID1,
- OcPoi.POI_ID,
- VoPersonCategory.VO_PERSON_CATEGORY_ID,
- Person.PERSON_ID AS PERSON_ID2,
- (Person.Last_name || ' ' || Person.First_Name) AS VIEW_ATTR,
- TO_CHAR(PdTrfDay.date_day, 'Dy', 'nls_date_language=' || (select case when language = 'DU' then 'Dutch' when language = 'FR' then 'French' else 'English' end from gui_users gu where gu.gui_user_id = GuPerson.gui_user_id)) AS VIEW_ATTR,
- TO_CHAR( PdTrfDay.date_day,'dd') AS VIEW_ATTR,
- to_char ( PdTrfDay.DATE_DAY , 'dd/MM/yyyy' ) AS VIEW_ATTR,
- DECODE(PdTrfDay.start_poi_id,NULL,PdTrfDay.start_location_name, DECODE(OcPoi.oc_poi_status_id,4, '[' || OcPoi.short_name || '].'|| p_vd_poi.getVehicleDataAddress(PdTrfDay.start_vehicle_data_id), p_wbs.getWbsName(PdTrfDay.wbs_Id, PdTrfDay.start_poi_id))) AS VIEW_ATTR,
- mod(DENSE_RANK () OVER (order by Person.company_nr, person.last_name, person.person_id, PdTrfDay.date_day), 2) AS VIEW_ATTR,
- PdTrfDay.START_POI_ID,
- PdTrfDay.TOT_PRIVATE_KM,
- PdTrfDay.TOT_DRIVER_WITH_P_KM,
- PdTrfDay.TOT_PASSENGER_KM,
- OcAggregAdminStatus.FLAG_COLOUR,
- VoPersonCategory.VEHICLE_OWNER_ID,
- decode(OperationMode.short_name,'NW','#223c4a','CO','#f75414','') AS VIEW_ATTR,
- PdTrfDay.USER_COMMENT,
- (Select work_code_id From work_code Where vehicle_owner_id = VoPersonCategory.vehicle_owner_id And work_code_std_id=110 And order_num = ( select min(order_num) From work_code Where vehicle_owner_id = VoPersonCategory.vehicle_owner_id And work_code_std_id=110)) AS VIEW_ATTR,
- (VoPersonCategory.VEHICLE_OWNER_ID) AS VIEW_ATTR,
- (Person.Company_nr) AS VIEW_ATTR,
- (VoPersonCategory.Name) AS VIEW_ATTR,
- (VehicleOwner.Company_name) AS VIEW_ATTR,
- PdTrfDay.ACTIVITY_ID,
- PdDay.PD_DAY_ID,
- (PdDay.TRF_DHR_SUPP_TO_STORE) AS VIEW_ATTR,
- row_number () over (PARTITION by pdday.pd_day_id order by PdTrfDay.order_num, PdTrfDay.START_TIME) AS ROW_NUM,
- (PdDay.TRF_DHR_SUPP_TO_PAY) AS VIEW_ATTR,
- (PdDay.MOBILITY_ZONE_ID) AS VIEW_ATTR,
- (Pdday.MOBILITY_COMPENSATION) AS VIEW_ATTR,
- (PdDay.MEAL_ALLOWANCE) AS VIEW_ATTR,
- (Vehicle.COMPANY_NR || ' ' || Vehicle.NUMBERPLATE) AS VIEW_ATTR,
- PdTrfDay.START_LOCATION_ADDRESS,
- (Vehicle.NUMBERPLATE) AS VIEW_ATTR,
- (Vehicle.COMPANY_NR) AS VIEW_ATTR,
- PdTrfDay.TRF_IHR,
- (PDDAY.Declared_Vehicle_Km) AS VIEW_ATTR,
- (PDDAY.Declared_Vehicle_Ref) AS VIEW_ATTR,
- (PDDAY.pause) AS VIEW_ATTR,
- (PDDAY.Declared_Private_Km) AS VIEW_ATTR,
- PdTrfDay.START_LOCATION_NAME,
- PdTrfDay.ERP_SIGN_ATTACHED_DOC_ID,
- PdTrfDay.VALIDATED_BY_EMAIL,
- PdTrfDay.VALIDATED_BY_NAME,
- PdTrfDay.VALIDATION_COMMENT,
- (PDDAY.Declared_On_Foot_Km) AS VIEW_ATTR,
- (PDDAY.Declared_Bicycle_Km) AS VIEW_ATTR,
- (PDDAY.public_transport) AS VIEW_ATTR,
- (Private_Vehicle) AS VIEW_ATTR,
- (PDDAY.Company_Vehicle) AS VIEW_ATTR,
- (PDDAY.long_distance) AS VIEW_ATTR,
- (PDDAY.TRAJECT) AS VIEW_ATTR,
- (PDDAY.Transport) AS VIEW_ATTR,
- (PDDAY.DECLARED_JOURNEY_TYPE_ID) AS VIEW_ATTR,
- (PDDAY.WORK_LOCATION_CATEGORY) AS VIEW_ATTR,
- (PDDAY.WORK_LOCATION_POST_CODE) AS VIEW_ATTR,
- (PDDAY.MEETING_POINT_POST_CODE) AS VIEW_ATTR,
- SUM( case when WorkCode.work_code_std_type_id = 1 then PdTrfDay.TRF_DHR else 0 end) OVER (PARTITION BY PdTrfDay.date_day,PdTrfDay.person_id) AS VIEW_ATTR,
- GraphicalSymbol.GRAPHICAL_SYMBOL_ID,
- (GraphicalSymbol.NAME) AS VIEW_ATTR,
- P_WBS.getWBSPathWithNames(PdTrfDay.wbs_Id) AS VIEW_ATTR,
- P_WBS.getWBSPathWithREF_number(PdTrfDay.WBS_ID) AS VIEW_ATTR,
- P_WBS.getWBSPathWithShortName(PdTrfDay.WBS_ID) AS VIEW_ATTR,
- (PDDAY.WORK_LOCATION_CITY) AS VIEW_ATTR,
- (PDDAY.MEETING_POINT_CITY) AS VIEW_ATTR,
- (PDDAY.PLANNED_WORK_DURATION) AS VIEW_ATTR,
- WorkCode.WORK_CODE_STD_TYPE_ID,
- PdTrfDay.HEAVY_WORK,
- nvl(PdTrfDay.is_exported, 'N') AS Is_Exported,
- P_WBS.getlvl0WbsRefNumber(PdTrfDay.WBS_ID) AS VIEW_ATTR,
- P_WBS.getlvl1WbsRefNumber(PdTrfDay.WBS_ID) AS VIEW_ATTR,
- P_WBS.getlvl2WbsRefNumber(PdTrfDay.WBS_ID) AS VIEW_ATTR,
- (OcAggregAdminStatus.NAME) AS VIEW_ATTR,
- (WorkCode.short_code) AS VIEW_ATTR,
- PdTrfDay.REF_NUMBER_TASK_CODE,
- (Vehicle1.COMPANY_NR || ' ' || Vehicle1.NUMBERPLATE) AS VIEW_ATTR,
- (PdTrfDay.Person_id) AS VIEW_ATTR,
- PdTrfDay.ORDER_NUM,
- PdTrfDay.EXPORT_FILE_NAME,
- PdTrfDay.TRF_PRICE,
- (WorkCode.WORK_CODE_NAME) AS VIEW_ATTR,
- PdTrfDay.TRF_KM_ROUTE,
- PdTrfDay.TRF_KM_ESTIMATED,
- DECODE (IVehicle.vehicle_id, NULL, DECODE (PdTrfVehicle.vehicle_id, NULL, NULL, trim(DECODE(PdTrfVehicle.company_nr, NULL, '', PdTrfVehicle.company_nr || ' - ') || PdTrfVehicle.numberplate)), trim(DECODE(IVehicle.company_nr, NULL, '', IVehicle.company_nr || ' - ') || IVehicle.numberplate)) AS VIEW_ATTR,
- (PDDAY.MAINTENANCE) AS VIEW_ATTR,
- (PDDAY.LAUNDRY) AS VIEW_ATTR,
- (PDDAY.DAY_JOURNEY_ROLE) AS VIEW_ATTR,
- (OcPoi.name) AS POI_NAME,
- PdTrfDay.SUBMITTED_BY_PERSON_ID,
- PdTrfDay.SUBMITTED_BY_DATE_DAY,
- PdTrfDay.VALIDATION_STATUS_ID,
- (OperationMode.SHORT_NAME) AS VIEW_ATTR,
- (OperationMode.NAME) AS VIEW_ATTR,
- (PDDAY.WORK_DURATION) AS VIEW_ATTR,
- (PDDAY.DRIVER_KM) AS VIEW_ATTR,
- (PDDAY.PASSENGER_KM) AS VIEW_ATTR
- FROM PD_TRF_DAY PdTrfDay, PERSON Person, VO_PERSON_CATEGORY VoPersonCategory, OC_POI OcPoi, OC_DAY_AGGREG OcDayAggreg, OC_AGGREG_ADMIN_STATUS OcAggregAdminStatus, OPERATION_MODE OperationMode, GU_PERSON GuPerson, WORK_CODE WorkCode, VEHICLE_OWNER VehicleOwner, PD_DAY PdDay, VEHICLE Vehicle, OF_OWNER.ACTIVITY Activity, OF_OWNER.GRAPHICAL_SYMBOL GraphicalSymbol, VEHICLE Vehicle1, VEHICLE IVehicle, VEHICLE PdTrfVehicle
- WHERE PdTrfDay.start_poi_id = OcPoi.poi_id (+)
- and PdTrfDay.oc_day_aggreg_id = OcDayAggreg.oc_day_aggreg_id (+)
- and PdTrfDay.OC_AGGREG_ADMIN_STATUS_ID = OcAggregAdminStatus.OC_AGGREG_ADMIN_STATUS_ID (+)
- and PdTrfDay.Operation_mode_id = OperationMode.Operation_mode_id (+)
- and PdTrfDay.work_code_id = WorkCode.work_code_id (+)
- and WorkCode.GRAPHICAL_SYMBOL_ID = GraphicalSymbol.GRAPHICAL_SYMBOL_ID (+)
- and PdTrfDay.person_id = Person.person_id
- and Person.Vo_Person_Category_id = VoPersonCategory.Vo_Person_Category_id
- and VoPersonCategory.Vehicle_owner_id = VehicleOwner.vehicle_owner_id
- and Person.PERSON_ID = GuPerson.PERSON_ID
- and PdTrfDay.date_day = PdDay.date_day
- and PdTrfDay.person_id = PdDay.person_id
- and PdTrfDay.activity_id = activity.activity_id(+)
- and pdday.vehicle_id = vehicle.vehicle_id (+)
- and pdday.vehicle2_id = vehicle1.vehicle_id (+)
- and PdTrfDay.TRF_I_VEHICLE_ID = IVehicle.vehicle_id (+)
- and PdTrfDay.VEHICLE_ID = PdTrfVehicle.VEHICLE_ID (+)
- and nvl (GuPerson.caw_limited, 'N') <> 'Y'
- and GuPerson.gui_user_id = 102594
- and (Person.vo_person_category_id = -1 or -1 = -1)
- and PdTrfDay.date_day >= to_date('08/10/2019','DD/MM/YYYY') and PdTrfDay.date_day < to_date('09/10/2019','DD/MM/YYYY')
- and (Person.PERSON_ID = 0 or 0 = 0)
- and ((nvl (PdTrfDay.TRF_DHR, 0) + nvl (PdTrfDay.TRF_PHR, 0)) > 0 or 'true' = 'true')
- and ('true'='true' OR TO_CHAR (PdTrfDay.date_day, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') not IN ('SAT', 'SUN')
- OR (nvl (PdTrfDay.TRF_DHR, 0) + nvl (PdTrfDay.TRF_PHR, 0)) > 0)
- and ('false' = 'false' OR OcAggregAdminStatus.name != 'Normal')
- and (('false' = 'false') OR (nvl(PdTrfDay.is_locked,'N')='N'))
- and (('false' = 'false') OR (OperationMode.OPERATION_MODE_ID=2))
- and ('-1' = '-1' or Person.Person_ID in (select person_id from vo_group_person where vo_group_id in ( select regexp_substr( '-1' ,'[^,]+', 1, level) from dual
- connect by regexp_substr( '-1', '[^,]+', 1, level) is not null )))
- and ('-1' = '-1' or P_WBS.getlvl0WbsRefNumber(PdTrfDay.WBS_ID) = '-1' )
- and Person.Vo_Person_category_id in (
- select VFmxPc.VO_PERSON_CATEGORY_ID from V_FMX_PC VFmxPc
- WHERE VFmxPc.OPERATION_CENTER_ID in
- (
- select OPERATION_CENTER_ID from GUI_USERS where GUI_USER_ID = 102594
- )
- and VFmxPc.VO_PERSON_CATEGORY_ID in (select distinct VO_PERSON_CATEGORY_ID from GU_PERSON gu, PERSON p where gu.PERSON_ID = p.PERSON_ID and gu.GUI_USER_ID = 102594)
- )
- and INSTR('0,1,2,3,4',TO_CHAR(WorkCode.WORK_CODE_STD_TYPE_ID)) > 0
- and INSTR('1,2,3,4,5',TO_CHAR(WorkCode.WORK_CODE_STD_CATEGORY_ID)) > 0
- and (('false' = 'false') OR (nvl(PdTrfDay.is_exported,'N')='N'))
- and ('false' = 'false' OR NVL(VoPersonCategory.IS_EXPORTED_TO_PAYROLL,'Y')='Y')
- and PdTrfDay.PD_TRF_DAY_ID IN (142954867,142954858,142954859,142954862,142954863,142954864,142954865)
- ORDER BY Person.COMPANY_NR, Person.Last_name, PdTrfDay.date_day, PdTrfDay.START_TIME, row_num;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement