VIEW: APPS.CSR_RULES_TRANSLATED_V

Source

View Text - Preformatted

SELECT r.rule_id
       , r.object_version_number
       , r.rule_name
       , r.description
       , r.base_rule_id
       , base.rule_name base_rule_name
       , appl.application_name
       , resp.responsibility_name
       , u.user_name
       , terr.name territory_name
       , (SELECT o.name FROM jtf_objects_vl o WHERE o.object_code = r.resource_type) resource_type_name
       , res.resource_name
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = DECODE(r.enabled_flag, 'Y', 'Y', 'N')) enabled_flag
       , r.rule_rank
       , r.created_by
       , r.creation_date
       , r.last_updated_by
       , r.last_update_date
       , r.last_update_login
       , r.sp_plan_scope
       , r.sp_max_plan_options
       , r.sp_max_resources
       , r.sp_max_calc_time
       , r.sp_max_overtime
       , r.sp_wtp_threshold
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'CSR_ENFORCE_PLAN_WINDOW' and lookup_code = r.sp_enforce_plan_window) sp_enforce_plan_window
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'CSR_CONSIDER_STANDBY_SHIFTS' and lookup_code = r.sp_consider_standby_shifts) sp_consider_standby_shifts
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = r.sp_spares_mandatory) sp_spares_mandatory
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'CSR_SPARES_SOURCE' and lookup_code = r.sp_spares_source) sp_spares_source
       , r.sp_min_task_length
       , r.sp_default_shift_duration
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = r.sp_dist_last_child_effort) sp_dist_last_child_effort
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = r.sp_pick_contract_resources) sp_pick_contract_resources
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = r.sp_pick_ib_resources) sp_pick_ib_resources
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = r.sp_pick_territory_resources) sp_pick_territory_resources
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = r.sp_pick_skilled_resources) sp_pick_skilled_resources
       , (SELECT name FROM csf_dc_queries_vl WHERE query_id = r.sp_auto_sch_default_query) sp_auto_sch_default_query
       , (SELECT name FROM jtf_task_statuses_vl WHERE task_status_id = r.sp_auto_reject_sts_id_spares) sp_auto_reject_sts_id_spares
       , (SELECT name FROM jtf_task_statuses_vl WHERE task_status_id = r.sp_auto_reject_sts_id_others) sp_auto_reject_sts_id_others
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = r.sp_force_optimizer_to_group) sp_force_optimizer_to_group
       , r.sp_optimizer_success_perc
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'CSR_COMMUTES_POSITION_TYPE' and lookup_code = r.sp_commutes_position) sp_commutes_position
       , r.sp_commute_excluded_time
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = r.sp_commute_home_empty_trip) sp_commute_home_empty_trip
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'CSR_ROUTER_MODES' and lookup_code = r.sp_router_mode) sp_router_mode
       , r.sp_travel_time_extra
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = r.sp_default_router_enabled) sp_default_router_enabled
       , r.sp_default_travel_distance
       , r.sp_default_travel_duration
       , r.sp_max_distance_in_group
       , r.sp_max_dist_to_skip_actual
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'CSR_ROUTER_CALC_TYPES' and lookup_code = r.rc_router_calc_type) rc_router_calc_type
       , (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'YES_NO' and lookup_code = r.rc_consider_toll_roads) rc_consider_toll_roads
       , r.rc_route_func_delay_0
       , r.rc_route_func_delay_1
       , r.rc_route_func_delay_2
       , r.rc_route_func_delay_3
       , r.rc_route_func_delay_4
       , r.rc_estimate_first_boundary
       , r.rc_estimate_second_boundary
       , r.rc_estimate_first_avg_speed
       , r.rc_estimate_second_avg_speed
       , r.rc_estimate_third_avg_speed
       , r.cp_task_per_day_delayed
       , r.cp_task_per_min_early
       , r.cp_task_per_min_late
       , r.cp_tls_per_day_extra
       , r.cp_tls_per_child_extra
       , r.cp_parts_violation
       , r.cp_res_per_min_overtime
       , r.cp_res_assigned_not_pref
       , r.cp_res_skill_level
       , r.cp_standby_shift_usage
       , r.cp_travel_per_unit_distance
       , r.cp_travel_per_unit_duration
       , r.cp_defer_same_site
    FROM csr_rules_exploded_v r
       , fnd_application_tl appl
       , fnd_responsibility_tl resp
       , fnd_user u
       , jtf_terr_all terr
       , jtf_rs_all_resources_vl res
       , csr_rules_tl base
   WHERE appl.application_id(+) = r.appl_id
     AND appl.LANGUAGE(+) = USERENV( 'LANG' )
     AND resp.responsibility_id(+) = r.resp_id
     AND resp.LANGUAGE(+) = USERENV( 'LANG' )
     AND u.user_id(+) = r.user_id
     AND terr.terr_id(+) = r.terr_id
     AND res.resource_id(+) = r.resource_id
     AND res.resource_type(+) = r.resource_type
     AND base.rule_id(+) = r.base_rule_id
     AND base.LANGUAGE(+) = USERENV( 'LANG' )
   

View Text - HTML Formatted

SELECT R.RULE_ID
, R.OBJECT_VERSION_NUMBER
, R.RULE_NAME
, R.DESCRIPTION
, R.BASE_RULE_ID
, BASE.RULE_NAME BASE_RULE_NAME
, APPL.APPLICATION_NAME
, RESP.RESPONSIBILITY_NAME
, U.USER_NAME
, TERR.NAME TERRITORY_NAME
, (SELECT O.NAME
FROM JTF_OBJECTS_VL O
WHERE O.OBJECT_CODE = R.RESOURCE_TYPE) RESOURCE_TYPE_NAME
, RES.RESOURCE_NAME
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = DECODE(R.ENABLED_FLAG
, 'Y'
, 'Y'
, 'N')) ENABLED_FLAG
, R.RULE_RANK
, R.CREATED_BY
, R.CREATION_DATE
, R.LAST_UPDATED_BY
, R.LAST_UPDATE_DATE
, R.LAST_UPDATE_LOGIN
, R.SP_PLAN_SCOPE
, R.SP_MAX_PLAN_OPTIONS
, R.SP_MAX_RESOURCES
, R.SP_MAX_CALC_TIME
, R.SP_MAX_OVERTIME
, R.SP_WTP_THRESHOLD
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'CSR_ENFORCE_PLAN_WINDOW'
AND LOOKUP_CODE = R.SP_ENFORCE_PLAN_WINDOW) SP_ENFORCE_PLAN_WINDOW
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'CSR_CONSIDER_STANDBY_SHIFTS'
AND LOOKUP_CODE = R.SP_CONSIDER_STANDBY_SHIFTS) SP_CONSIDER_STANDBY_SHIFTS
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = R.SP_SPARES_MANDATORY) SP_SPARES_MANDATORY
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'CSR_SPARES_SOURCE'
AND LOOKUP_CODE = R.SP_SPARES_SOURCE) SP_SPARES_SOURCE
, R.SP_MIN_TASK_LENGTH
, R.SP_DEFAULT_SHIFT_DURATION
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = R.SP_DIST_LAST_CHILD_EFFORT) SP_DIST_LAST_CHILD_EFFORT
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = R.SP_PICK_CONTRACT_RESOURCES) SP_PICK_CONTRACT_RESOURCES
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = R.SP_PICK_IB_RESOURCES) SP_PICK_IB_RESOURCES
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = R.SP_PICK_TERRITORY_RESOURCES) SP_PICK_TERRITORY_RESOURCES
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = R.SP_PICK_SKILLED_RESOURCES) SP_PICK_SKILLED_RESOURCES
, (SELECT NAME
FROM CSF_DC_QUERIES_VL
WHERE QUERY_ID = R.SP_AUTO_SCH_DEFAULT_QUERY) SP_AUTO_SCH_DEFAULT_QUERY
, (SELECT NAME
FROM JTF_TASK_STATUSES_VL
WHERE TASK_STATUS_ID = R.SP_AUTO_REJECT_STS_ID_SPARES) SP_AUTO_REJECT_STS_ID_SPARES
, (SELECT NAME
FROM JTF_TASK_STATUSES_VL
WHERE TASK_STATUS_ID = R.SP_AUTO_REJECT_STS_ID_OTHERS) SP_AUTO_REJECT_STS_ID_OTHERS
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = R.SP_FORCE_OPTIMIZER_TO_GROUP) SP_FORCE_OPTIMIZER_TO_GROUP
, R.SP_OPTIMIZER_SUCCESS_PERC
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'CSR_COMMUTES_POSITION_TYPE'
AND LOOKUP_CODE = R.SP_COMMUTES_POSITION) SP_COMMUTES_POSITION
, R.SP_COMMUTE_EXCLUDED_TIME
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = R.SP_COMMUTE_HOME_EMPTY_TRIP) SP_COMMUTE_HOME_EMPTY_TRIP
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'CSR_ROUTER_MODES'
AND LOOKUP_CODE = R.SP_ROUTER_MODE) SP_ROUTER_MODE
, R.SP_TRAVEL_TIME_EXTRA
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = R.SP_DEFAULT_ROUTER_ENABLED) SP_DEFAULT_ROUTER_ENABLED
, R.SP_DEFAULT_TRAVEL_DISTANCE
, R.SP_DEFAULT_TRAVEL_DURATION
, R.SP_MAX_DISTANCE_IN_GROUP
, R.SP_MAX_DIST_TO_SKIP_ACTUAL
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'CSR_ROUTER_CALC_TYPES'
AND LOOKUP_CODE = R.RC_ROUTER_CALC_TYPE) RC_ROUTER_CALC_TYPE
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = R.RC_CONSIDER_TOLL_ROADS) RC_CONSIDER_TOLL_ROADS
, R.RC_ROUTE_FUNC_DELAY_0
, R.RC_ROUTE_FUNC_DELAY_1
, R.RC_ROUTE_FUNC_DELAY_2
, R.RC_ROUTE_FUNC_DELAY_3
, R.RC_ROUTE_FUNC_DELAY_4
, R.RC_ESTIMATE_FIRST_BOUNDARY
, R.RC_ESTIMATE_SECOND_BOUNDARY
, R.RC_ESTIMATE_FIRST_AVG_SPEED
, R.RC_ESTIMATE_SECOND_AVG_SPEED
, R.RC_ESTIMATE_THIRD_AVG_SPEED
, R.CP_TASK_PER_DAY_DELAYED
, R.CP_TASK_PER_MIN_EARLY
, R.CP_TASK_PER_MIN_LATE
, R.CP_TLS_PER_DAY_EXTRA
, R.CP_TLS_PER_CHILD_EXTRA
, R.CP_PARTS_VIOLATION
, R.CP_RES_PER_MIN_OVERTIME
, R.CP_RES_ASSIGNED_NOT_PREF
, R.CP_RES_SKILL_LEVEL
, R.CP_STANDBY_SHIFT_USAGE
, R.CP_TRAVEL_PER_UNIT_DISTANCE
, R.CP_TRAVEL_PER_UNIT_DURATION
, R.CP_DEFER_SAME_SITE
FROM CSR_RULES_EXPLODED_V R
, FND_APPLICATION_TL APPL
, FND_RESPONSIBILITY_TL RESP
, FND_USER U
, JTF_TERR_ALL TERR
, JTF_RS_ALL_RESOURCES_VL RES
, CSR_RULES_TL BASE
WHERE APPL.APPLICATION_ID(+) = R.APPL_ID
AND APPL.LANGUAGE(+) = USERENV( 'LANG' )
AND RESP.RESPONSIBILITY_ID(+) = R.RESP_ID
AND RESP.LANGUAGE(+) = USERENV( 'LANG' )
AND U.USER_ID(+) = R.USER_ID
AND TERR.TERR_ID(+) = R.TERR_ID
AND RES.RESOURCE_ID(+) = R.RESOURCE_ID
AND RES.RESOURCE_TYPE(+) = R.RESOURCE_TYPE
AND BASE.RULE_ID(+) = R.BASE_RULE_ID
AND BASE.LANGUAGE(+) = USERENV( 'LANG' )