45: PROCEDURE getCumulativeMileage(
46: p_policy_id IN AP_POL_LINES.POLICY_ID%TYPE,
47: p_start_date IN AP_EXPENSE_REPORT_LINES.start_expense_date%TYPE,
48: p_end_date IN AP_EXPENSE_REPORT_LINES.end_expense_date%TYPE,
49: p_employee_id IN AP_WEB_EMPLOYEE_INFO.EMPLOYEE_ID%TYPE,
50: p_cumulative_mileage OUT NOCOPY AP_WEB_EMPLOYEE_INFO.NUMERIC_VALUE%TYPE,
51: p_period_id OUT NOCOPY AP_WEB_EMPLOYEE_INFO.PERIOD_ID%TYPE
52: )IS
53: --------------------------------------------------------------------------------
46: p_policy_id IN AP_POL_LINES.POLICY_ID%TYPE,
47: p_start_date IN AP_EXPENSE_REPORT_LINES.start_expense_date%TYPE,
48: p_end_date IN AP_EXPENSE_REPORT_LINES.end_expense_date%TYPE,
49: p_employee_id IN AP_WEB_EMPLOYEE_INFO.EMPLOYEE_ID%TYPE,
50: p_cumulative_mileage OUT NOCOPY AP_WEB_EMPLOYEE_INFO.NUMERIC_VALUE%TYPE,
51: p_period_id OUT NOCOPY AP_WEB_EMPLOYEE_INFO.PERIOD_ID%TYPE
52: )IS
53: --------------------------------------------------------------------------------
54: l_period_id AP_WEB_EMPLOYEE_INFO.PERIOD_ID%TYPE;
47: p_start_date IN AP_EXPENSE_REPORT_LINES.start_expense_date%TYPE,
48: p_end_date IN AP_EXPENSE_REPORT_LINES.end_expense_date%TYPE,
49: p_employee_id IN AP_WEB_EMPLOYEE_INFO.EMPLOYEE_ID%TYPE,
50: p_cumulative_mileage OUT NOCOPY AP_WEB_EMPLOYEE_INFO.NUMERIC_VALUE%TYPE,
51: p_period_id OUT NOCOPY AP_WEB_EMPLOYEE_INFO.PERIOD_ID%TYPE
52: )IS
53: --------------------------------------------------------------------------------
54: l_period_id AP_WEB_EMPLOYEE_INFO.PERIOD_ID%TYPE;
55:
50: p_cumulative_mileage OUT NOCOPY AP_WEB_EMPLOYEE_INFO.NUMERIC_VALUE%TYPE,
51: p_period_id OUT NOCOPY AP_WEB_EMPLOYEE_INFO.PERIOD_ID%TYPE
52: )IS
53: --------------------------------------------------------------------------------
54: l_period_id AP_WEB_EMPLOYEE_INFO.PERIOD_ID%TYPE;
55:
56: BEGIN
57:
58: SELECT ai.numeric_value, ai.period_id
56: BEGIN
57:
58: SELECT ai.numeric_value, ai.period_id
59: INTO p_cumulative_mileage, p_period_id
60: FROM ap_web_employee_info_all ai,
61: ap_pol_schedule_periods ap
62: WHERE ap.policy_id = p_policy_id
63: AND ap.start_date <= p_start_date
64: AND nvl(ap.end_date, p_end_date + 1) >= p_end_date