[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SL.RANGE_HIGH,
nvl(SL.RANGE_LOW, 0),
SP.START_DATE,
SP.END_DATE,
SL.rate,
decode(SH.passengers_flag,null,0,decode(calculation_method, 'AMOUNT', NVL(SL.rate_per_passenger,0), 'PERCENT', NVL(SL.rate_per_passenger,0)/100* SL.rate, 0)) rate_per_passenger
FROM AP_POL_LINES SL,
AP_POL_HEADERS SH,
AP_POL_SCHEDULE_PERIODS SP,
PER_ALL_ASSIGNMENTS_F PF
WHERE SH.POLICY_ID = p_policy_id
AND SH.POLICY_ID = SL.POLICY_ID
AND (nvl(SL.ROLE_ID,0) = decode(
SH.EMPLOYEE_ROLE_FLAG,'Y',(decode(SH.ROLE_CODE,'JOB_GROUP', PF.job_id,
'POSITION',PF.position_id,
'GRADE', PF.grade_id,
0)),0)
OR SL.role_id = -1)
AND nvl(SL.VEHICLE_CATEGORY,0) = decode(
SH.VEHICLE_CATEGORY_FLAG,'Y', p_vehicle_category_code, nvl(SL.VEHICLE_CATEGORY,0))
AND nvl(SL.VEHICLE_TYPE,0) = decode(
SH.VEHICLE_TYPE_FLAG, 'Y', p_vehicle_type, nvl(SL.VEHICLE_TYPE,0))
AND nvl(SL.FUEL_TYPE,0) = decode(
SH.FUEL_TYPE_FLAG, 'Y', p_fuel_type, nvl(SL.FUEL_TYPE,0))
AND SL.CURRENCY_CODE = decode(
SH.CURRENCY_PREFERENCE, 'MRC', p_currency_code, SL.CURRENCY_CODE)
AND SL.STATUS = 'ACTIVE'
AND SL.SCHEDULE_PERIOD_ID = SP.SCHEDULE_PERIOD_ID
AND SP.POLICY_ID = SH.POLICY_ID
AND PF.ASSIGNMENT_ID IN ( SELECT P.ASSIGNMENT_ID
FROM per_employees_x P
WHERE P.EMPLOYEE_ID = p_employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.isPersonCwk(P.employee_id)='Y'
UNION ALL
SELECT P.ASSIGNMENT_ID
FROM per_cont_workers_current_x P
WHERE P.PERSON_ID = p_employee_id
)
AND PF.EFFECTIVE_START_DATE <= p_start_expense_date
AND PF.EFFECTIVE_END_DATE >= p_start_expense_date
AND SP.START_DATE <= p_start_expense_date
AND nvl(SP.END_DATE, p_start_expense_date) >= p_start_expense_date
AND SL.ADDON_MILEAGE_RATE_CODE is null
;
SELECT distance_uom,
currency_code,
distance_thresholds_flag
INTO p_sh_distance_uom,
p_sh_currency_code,
p_sh_distance_thresholds_flag
FROM AP_POL_HEADERS
WHERE policy_id = p_policy_id;