psi_mmobile

Untitled

Feb 23rd, 2021
512
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT /*+ FIRST_ROWS */
  2.        PdTrfDay.DATE_DAY,
  3.        PdTrfDay.PD_TRF_DAY_ID,
  4.        PdTrfDay.DESTINATION_POI_ID,
  5.        PdTrfDay.IS_LOCKED,
  6.        PdTrfDay.IS_MODIFIED_BY_USER,
  7.        PdTrfDay.OC_AGGREG_ADMIN_STATUS_ID,
  8.        PdTrfDay.OC_DAY_AGGREG_ID,
  9.        PdTrfDay.OPERATION_MODE_ID,
  10.        PdTrfDay.PD_TRF_TYPE_ID,
  11.        PdTrfDay.PD_TRF_USER_COMMENT,
  12.        PdTrfDay.PERSON_ID,
  13.        PdTrfDay.REF_NUMBER,
  14.        CAST(PdTrfDay.START_TIME AS TIMESTAMP) AS STARTTIME,
  15.        CAST(PdTrfDay.STOP_TIME AS TIMESTAMP) AS STOPTIME,
  16.        PdTrfDay.TRF_DHR,
  17.        PdTrfDay.TRF_PHR,
  18.        PdTrfDay.WBS_ID,
  19.        PdTrfDay.WORK_CODE_ID,
  20.        PdTrfDay.VEHICLE_ID,
  21.        WorkCode.WORK_CODE_ID AS WORK_CODE_ID1,
  22.        GuPerson.GUI_USER_ID,
  23.        GuPerson.PERSON_ID AS PERSON_ID1,
  24.        OperationMode.NAME,
  25.        OperationMode.OPERATION_MODE_ID AS OPERATION_MODE_ID1,
  26.        OperationMode.SHORT_NAME,
  27.        OcAggregAdminStatus.OC_AGGREG_ADMIN_STATUS_ID AS OC_AGGREG_ADMIN_STATUS_ID1,
  28.        OcDayAggreg.OC_DAY_AGGREG_ID AS OC_DAY_AGGREG_ID1,
  29.        OcPoi.POI_ID,
  30.        VoPersonCategory.VO_PERSON_CATEGORY_ID,
  31.        Person.PERSON_ID AS PERSON_ID2,
  32.        (Person.Last_name || ' ' || Person.First_Name) AS PERSON_NAME,
  33.        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 DAY_STR,
  34.        TO_CHAR( PdTrfDay.date_day,'dd') AS DAY_NR,
  35.        TO_CHAR ( PdTrfDay.DATE_DAY , 'dd/MM/yyyy' ) AS DATE_DAY_STR,
  36.        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 WBS_NAME,
  37.        MOD(DENSE_RANK () OVER (ORDER BY Person.company_nr, person.last_name, person.first_name,PdTrfDay.date_day), 2) AS COLOR1,
  38.        PdTrfDay.START_POI_ID,
  39.        PdTrfDay.TOT_PRIVATE_KM,
  40.        PdTrfDay.TOT_DRIVER_WITH_P_KM,
  41.        PdTrfDay.TOT_PASSENGER_KM,
  42.        OcAggregAdminStatus.FLAG_COLOUR,
  43.        VoPersonCategory.VEHICLE_OWNER_ID,
  44.        DECODE(OperationMode.short_name,'NW','#223c4a','CO','#f75414','') AS OM_SHORT_NAME1,
  45.        PdTrfDay.USER_COMMENT,
  46.        (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 DEFAULT_WORK_CODE_ID,
  47.        (VoPersonCategory.VEHICLE_OWNER_ID) AS UPDATABLE_VEHICLE_OWNER_ID,
  48.        (Person.Company_nr) AS COMPANY_NR,
  49.        (VoPersonCategory.Name) AS CATEGORY_NAME,
  50.        (VehicleOwner.Company_name) AS COMPANY_NAME,
  51.        PdTrfDay.ACTIVITY_ID,
  52.        (PdDay.TRF_DHR_SUPP_TO_STORE) AS TRF_DHR_SUPP_TO_STORE,
  53.        ROW_NUMBER () over (PARTITION BY pdday.pd_day_id ORDER BY PdTrfDay.order_num, PdTrfDay.START_TIME) AS ROW_NUM,
  54.        (PdDay.TRF_DHR_SUPP_TO_PAY) AS TRF_DHR_SUPP_TO_PAY,
  55.        (PdDay.MOBILITY_ZONE_ID) AS MOBILITY_ZONE_ID,
  56.        (Pdday.MOBILITY_COMPENSATION) AS MOBILITY_COMPENSATION,
  57.        (PdDay.MEAL_ALLOWANCE) AS MEAL_ALLOWANCE,
  58.        (Vehicle.COMPANY_NR || ' ' || Vehicle.NUMBERPLATE) AS VEHICLE_NAME,
  59.        PdTrfDay.START_LOCATION_ADDRESS,
  60.        (Vehicle.NUMBERPLATE) AS VEHICLE_NUMBERPLATE,
  61.        (Vehicle.COMPANY_NR) AS VEHICLE_COMPANY_NR,
  62.        PdTrfDay.TRF_IHR,
  63.        (PDDAY.Declared_Vehicle_Km) AS DECLARED_VEHICLE_KM,
  64.        (PDDAY.Declared_Vehicle_Ref) AS DECLARED_VEHICLE_REF,
  65.        (PDDAY.pause) AS PAUSE,
  66.        (PDDAY.Declared_Private_Km) AS DECLARED_PRIVATE_KM,
  67.        PdTrfDay.START_LOCATION_NAME,
  68.        PdTrfDay.ERP_SIGN_ATTACHED_DOC_ID,
  69.        PdTrfDay.VALIDATED_BY_EMAIL,
  70.        PdTrfDay.VALIDATED_BY_NAME,
  71.        PdTrfDay.VALIDATION_COMMENT,
  72.        (PDDAY.Declared_On_Foot_Km) AS DECLARED_ON_FOOT_KM,
  73.        (PDDAY.Declared_Bicycle_Km) AS DECLARED_BICYCLE_KM,
  74.        (PDDAY.public_transport) AS PUBLIC_TRANSPORT,
  75.        (Private_Vehicle) AS PRIVATE_VEHICLE,
  76.        (PDDAY.Company_Vehicle) AS COMPANY_VEHICLE,
  77.        (PDDAY.long_distance) AS LONG_DISTANCE,
  78.        (PDDAY.TRAJECT) AS TRAJECT,
  79.        (PDDAY.Transport) AS TRANSPORT,
  80.        (PDDAY.DECLARED_JOURNEY_TYPE_ID) AS DECLARED_JOURNEY_TYPE_ID,
  81.        (PDDAY.WORK_LOCATION_CATEGORY) AS WORK_LOCATION_CATEGORY,
  82.        (PDDAY.WORK_LOCATION_POST_CODE) AS WORK_LOCATION_POST_CODE,
  83.        (PDDAY.MEETING_POINT_POST_CODE) AS MEETING_POINT_POST_CODE,
  84.        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 SUM_TRF_DHR_PER_DAY,
  85.        GraphicalSymbol.GRAPHICAL_SYMBOL_ID,
  86.        (GraphicalSymbol.NAME) AS GRAPHICAL_SYMBOL_NAME,
  87.        P_WBS.getWBSPathWithNames(PdTrfDay.wbs_Id) AS WBS_PATH_NAME,
  88.        P_WBS.getWBSPathWithREF_number(PdTrfDay.WBS_ID) AS WBS_PATH_WITH_REF_NUMBER,
  89.        P_WBS.getWBSPathWithShortName(PdTrfDay.WBS_ID) AS WBS_PATH_WITH_SHORT_NAME,
  90.        (PDDAY.WORK_LOCATION_CITY) AS WORK_LOCATION_CITY,
  91.        (PDDAY.MEETING_POINT_CITY) AS MEETING_POINT_CITY,
  92.        (PDDAY.PLANNED_WORK_DURATION) AS PLANNED_WORK_DURATION,
  93.        WorkCode.WORK_CODE_STD_TYPE_ID,
  94.        PdTrfDay.HEAVY_WORK,
  95.        NVL(PdTrfDay.is_exported, 'N') AS Is_Exported,
  96.        P_WBS.getlvl0WbsRefNumber(PdTrfDay.WBS_ID) AS LVL0_WBS_REF_NUMBER,
  97.        P_WBS.getlvl1WbsRefNumber(PdTrfDay.WBS_ID) AS LVL1_WBS_REF_NUMBER,
  98.        P_WBS.getlvl2WbsRefNumber(PdTrfDay.WBS_ID) AS LVL2_WBS_REF_NUMBER,
  99.        (OcAggregAdminStatus.NAME) AS OC_AGGREG_ADMIN_STATUS_NAME,
  100.        (WorkCode.short_code) AS WORK_CODE_SHORT,
  101.        PdTrfDay.REF_NUMBER_TASK_CODE,
  102.        (Vehicle1.COMPANY_NR || ' ' || Vehicle1.NUMBERPLATE) AS VEHICLE2_NAME,
  103.        (PdTrfDay.Person_id) AS UPDATEABLE_PERSON_ID,
  104.        PdTrfDay.ORDER_NUM,
  105.        PdTrfDay.EXPORT_FILE_NAME,
  106.        PdTrfDay.TRF_PRICE,
  107.        (WorkCode.WORK_CODE_NAME) AS WORK_CODE_NAME,
  108.        PdTrfDay.TRF_KM_ROUTE,
  109.        PdTrfDay.TRF_KM_ESTIMATED,
  110.        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 VEHICLE_I_NAME,
  111.        (PDDAY.MAINTENANCE) AS MAINTENANCE,
  112.        (PDDAY.LAUNDRY) AS LAUNDRY,
  113.        (PDDAY.DAY_JOURNEY_ROLE) AS DAY_JOURNEY_ROLE,
  114.        (OcPoi.name) AS POI_NAME,
  115.        PdTrfDay.SUBMITTED_BY_PERSON_ID,
  116.        PdTrfDay.SUBMITTED_BY_DATE_DAY,
  117.        PdTrfDay.VALIDATION_STATUS_ID,
  118.        (OperationMode.SHORT_NAME) AS OM_SHORT_NAME1,
  119.        (OperationMode.NAME) AS OM_NAME1,
  120.        (PDDAY.WORK_DURATION) AS WORK_DURATION,
  121.        (PDDAY.DRIVER_KM) AS DRIVER_KM,
  122.        (PDDAY.PASSENGER_KM) AS PASSENGER_KM,
  123.        PdTrfDay.COST_PER_HOUR,
  124.        PdTrfDay.COST_PER_HOUR_COEFICIENT,
  125.        PdTrfDay.DECLARED_VEHICLE_ID,
  126.        p_vehicle.getName(PdTrfDay.DECLARED_VEHICLE_ID) AS DECLARED_VEHICLE_NAME,
  127.        (PDDAY.IS_USING_BIKE) AS IS_USING_BIKE,
  128.        (PDDAY.DRIVER_ALONE_KM) AS DRIVER_ALONE_KM,
  129.        CAST(PDDAY.START_TIME AS TIMESTAMP) AS START_TIME,
  130.        CAST(PDDAY.STOP_TIME AS TIMESTAMP) AS STOP_TIME,
  131.        (PDDAY.PAUSE_DURATION) AS PAUSE_DURATION,
  132.        (PDDAY.LOADING_DURATION) AS LOADING_DURATION,
  133.        (PDDAY.DHRP) AS DHRP,
  134.        (PDDAY.TRF_DHR) AS DAY_TRF_DHR,
  135.        (PDDAY.reported_Work_Duration) AS REPORTED_WORK_DURATION,
  136.        P_WBS.getlvl3WbsRefNumber(PdTrfDay.WBS_ID) AS LVL3_WBS_REF_NUMBER,
  137.        P_PERSON.list_person_group_name(Person.PERSON_ID) AS PERSON_GROUPS,
  138.        Person.REAL_EMPLOYER_NAME,
  139.        (SubmittedByPerson.LAST_NAME || ' ' || SubmittedByPerson.FIRST_NAME) AS SUBMITTED_BY_PERSON_NAME,
  140.        MOD(DENSE_RANK () OVER (ORDER BY person.last_name, person.first_name,Person.company_nr, PdTrfDay.date_day), 2) AS COLOR2,
  141.        MOD(DENSE_RANK () OVER (ORDER BY PdTrfDay.date_day, Person.company_nr, person.last_name, person.first_name), 2) AS COLOR3,
  142.        (PDDAY.TRANSPORT_DURATION) AS TRANSPORT_DURATION,
  143.        (PDDAY.PD_DAY_ID) AS PD_DAY_ID,
  144.        Person.RANK1_SC_NAME,
  145.        Person.RANK1_SC_TIN,
  146.        (PdDay.TOT_DRIVER_WITH_P_KM) AS TOT_DRIVER_WITH_P_KM_PDD,
  147.        (PdDay.TOT_PASSENGER_KM) AS TOT_PASSENGER_KM_PDD,
  148.        (PdDay.TOT_PRIVATE_KM) AS TOT_PRIVATE_KM_PDD,
  149.        (PdDay.WE_BLOCKED) AS WE_BLOCKED,
  150.        (PdDay.EMPLOYEE_CONTRACT_TYPE_ID) AS EMPLOYEE_CONTRACT_TYPE_ID
  151. 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, PERSON SubmittedByPerson
  152. WHERE PdTrfDay.start_poi_id = OcPoi.poi_id (+)
  153. AND PdTrfDay.oc_day_aggreg_id = OcDayAggreg.oc_day_aggreg_id (+)
  154. AND PdTrfDay.OC_AGGREG_ADMIN_STATUS_ID = OcAggregAdminStatus.OC_AGGREG_ADMIN_STATUS_ID (+)
  155. AND PdTrfDay.Operation_mode_id = OperationMode.Operation_mode_id (+)
  156. AND PdTrfDay.work_code_id = WorkCode.work_code_id (+)
  157. AND PdTrfDay.SUBMITTED_BY_PERSON_ID = SubmittedByPerson.PERSON_ID(+)
  158. AND WorkCode.GRAPHICAL_SYMBOL_ID = GraphicalSymbol.GRAPHICAL_SYMBOL_ID (+)
  159. AND PdTrfDay.person_id = Person.person_id
  160. AND Person.Vo_Person_Category_id = VoPersonCategory.Vo_Person_Category_id
  161. AND VoPersonCategory.Vehicle_owner_id = VehicleOwner.vehicle_owner_id
  162. AND Person.PERSON_ID = GuPerson.PERSON_ID
  163. AND PdTrfDay.date_day = PdDay.date_day
  164. AND PdTrfDay.person_id = PdDay.person_id
  165. AND PdTrfDay.activity_id = activity.activity_id(+)
  166. AND pdday.vehicle_id = vehicle.vehicle_id (+)
  167. AND pdday.vehicle2_id = vehicle1.vehicle_id (+)
  168. AND PdTrfDay.TRF_I_VEHICLE_ID = IVehicle.vehicle_id (+)
  169. AND PdTrfDay.VEHICLE_ID = PdTrfVehicle.VEHICLE_ID (+)
  170. AND NVL (GuPerson.caw_limited, 'N') != 'Y'
  171. AND GuPerson.gui_user_id = ?
  172. AND (Person.vo_person_category_id = ? OR ? = -1)
  173. AND PdTrfDay.date_day >= ? AND  PdTrfDay.date_day <= ?
  174. AND (Person.PERSON_ID = ? OR ? = 0)
  175. AND ((NVL (PdTrfDay.TRF_DHR, 0) + NVL (PdTrfDay.TRF_PHR, 0)) > 0 OR ? = 'true')  
  176. AND (?='true' OR TO_CHAR (PdTrfDay.date_day, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')  NOT IN ('SAT', 'SUN')
  177.     OR (NVL (PdTrfDay.TRF_DHR, 0) + NVL (PdTrfDay.TRF_PHR, 0)) > 0)
  178. AND (? = 'false' OR OcAggregAdminStatus.name != 'Normal')
  179. AND ((? = 'false') OR (NVL(PdTrfDay.is_locked,'N')='N'))
  180. AND ((? = 'false') OR (OperationMode.OPERATION_MODE_ID=2))
  181. AND (? = '-1' OR Person.Person_ID IN (SELECT person_id FROM vo_group_person WHERE vo_group_id IN ( SELECT regexp_substr( ? ,'[^,]+', 1, LEVEL) FROM dual
  182.     CONNECT BY regexp_substr( ?, '[^,]+', 1, LEVEL) IS NOT NULL )))
  183. AND  (? = '-1'  OR P_WBS.getlvl0WbsRefNumber(PdTrfDay.WBS_ID) = ? )
  184. AND Person.Vo_Person_category_id IN (
  185. SELECT VFmxPc.VO_PERSON_CATEGORY_ID FROM V_FMX_PC VFmxPc
  186. WHERE VFmxPc.OPERATION_CENTER_ID IN
  187. (
  188.  SELECT OPERATION_CENTER_ID FROM GUI_USERS WHERE GUI_USER_ID = ?
  189. )
  190. 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 = ?))
  191. AND INSTR(?,TO_CHAR(WorkCode.WORK_CODE_STD_TYPE_ID)) > 0
  192. AND INSTR(?,TO_CHAR(WorkCode.WORK_CODE_STD_CATEGORY_ID)) > 0
  193. AND ((? = 'false') OR (NVL(PdTrfDay.is_exported,'N')='N'))
  194. AND (? = 'false' OR NVL(VoPersonCategory.IS_EXPORTED_TO_PAYROLL,'Y')='Y')
  195. AND (
  196. UPPER (Person.REAL_EMPLOYER_NAME || ' ' || PdTrfDay.PD_TRF_USER_COMMENT   || ' ' || UPPER(NVL(PdTrfDay.EXPORT_FILE_NAME,'')) || ' ' || PdTrfDay.ref_number  || ' '  || OcPoi.name || ' ' || USER_COMMENT  || ' '  || OperationMode.SHORT_NAME  || ' '  || OcAggregAdminStatus.NAME  || ' '  || Person.First_Name  || ' '  || Person.Last_name  || ' '  || Person.First_Name  || ' '  || 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(NULL, OcPoi.poi_id)))  || ' '  || Person.COMPANY_NR  || ' '  || WorkCode.SHORT_CODE || ' ' || ACTIVITY.ACTIVITY_CODE  || ' ' || ACTIVITY.ACTIVITY_NAME || ' ' || VEHICLE.COMPANY_NR || ' ' || VEHICLE.NUMBERPLATE || ' ' || P_WBS.getWBSPathWithNames(PdTrfDay.wbs_Id) || ' ' || P_WBS.getWBSPathWithREF_number(PdTrfDay.WBS_ID)  || ' ' || P_PERSON.getNameForFiltering(PdTrfDay.SUBMITTED_BY_PERSON_ID) || ' ' || P_WBS.getWBSPathWithShortName(PdTrfDay.WBS_ID)) LIKE '%' || UPPER (?) || '%')
  197. AND (? = -1 OR PdTrfDay.START_POI_ID = ?)
  198. AND (? = 'false' OR PdTrfDay.USER_COMMENT IS NOT NULL)
  199. ORDER BY Person.COMPANY_NR, Person.Last_name, Person.FIRST_NAME, PdTrfDay.date_day, PdTrfDay.order_num, PdTrfDay.START_TIME, row_num;
RAW Paste Data