[Home] [Help]
160: -- before the definition.
161:
162: PROCEDURE permutatePolicyLines(p_user_id IN NUMBER,
163: p_policy_id IN ap_pol_headers.policy_id%TYPE,
164: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE);
165:
166: PROCEDURE permutateAddonRates( p_user_id IN NUMBER,
167: p_policy_id IN ap_pol_headers.policy_id%TYPE,
168: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
431: p_low_threshold IN NUMBER) RETURN NUMBER IS
432:
433: CURSOR threshold_c IS
434: SELECT threshold
435: FROM ap_pol_schedule_options
436: WHERE option_type = p_lookup_type
437: AND policy_id = p_policy_id
438: ORDER BY threshold;
439:
964: CURSOR policy_cur IS
965: select ph.role_code,
966: pso.role_id
967: from ap_pol_headers ph,
968: ap_pol_schedule_options pso
969: where ph.policy_id = pso.policy_id
970: and pso.schedule_option_id = p_policy_schedule_option_id;
971:
972: policy_rec policy_cur%ROWTYPE;
1291: | 16-May-2002 R Langi Created
1292: |
1293: *=======================================================================*/
1294: FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1295: p_threshold IN ap_pol_schedule_options.threshold%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE IS
1296: BEGIN
1297:
1298: RETURN getHighEndOfThreshold(p_policy_id, p_threshold, 'STANDARD');
1299:
1318: | 01-Nov-2005 krmenon Created
1319: |
1320: *=======================================================================*/
1321: FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1322: p_threshold IN ap_pol_schedule_options.threshold%TYPE,
1323: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE IS
1324:
1325: CURSOR c_threshold IS
1326: SELECT threshold
1319: |
1320: *=======================================================================*/
1321: FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1322: p_threshold IN ap_pol_schedule_options.threshold%TYPE,
1323: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE IS
1324:
1325: CURSOR c_threshold IS
1326: SELECT threshold
1327: FROM ap_pol_schedule_options
1323: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE IS
1324:
1325: CURSOR c_threshold IS
1326: SELECT threshold
1327: FROM ap_pol_schedule_options
1328: WHERE policy_id = p_policy_id
1329: AND (OPTION_TYPE = 'DISTANCE_THRESHOLD' or OPTION_TYPE = 'TIME_THRESHOLD')
1330: AND threshold is not null
1331: AND nvl(rate_type_code, 'STANDARD') = p_rate_type
1475: where policy_id = p_policy_id;
1476:
1477: select count(location_id)
1478: into l_location_count
1479: from ap_pol_schedule_options
1480: where policy_id = p_policy_id
1481: and option_type = c_LOCATION
1482: and location_id is not null;
1483:
1507: where policy_id = p_policy_id;
1508:
1509: select count(role_id)
1510: into l_role_count
1511: from ap_pol_schedule_options
1512: where policy_id = p_policy_id
1513: and option_type = c_EMPLOYEE_ROLE
1514: and role_id is not null;
1515:
1539: where policy_id = p_policy_id;
1540:
1541: select count(currency_code)
1542: into l_currency_count
1543: from ap_pol_schedule_options
1544: where policy_id = p_policy_id
1545: and option_type = c_CURRENCY
1546: and currency_code is not null;
1547:
1571: where policy_id = p_policy_id;
1572:
1573: select count(option_code)
1574: into l_vehicle_category_count
1575: from ap_pol_schedule_options
1576: where policy_id = p_policy_id
1577: and option_type = c_VEHICLE_CATEGORY
1578: and option_code is not null;
1579:
1603: where policy_id = p_policy_id;
1604:
1605: select count(option_code)
1606: into l_vehicle_type_count
1607: from ap_pol_schedule_options
1608: where policy_id = p_policy_id
1609: and option_type = c_VEHICLE_TYPE
1610: and option_code is not null;
1611:
1635: where policy_id = p_policy_id;
1636:
1637: select count(option_code)
1638: into l_fuel_type_count
1639: from ap_pol_schedule_options
1640: where policy_id = p_policy_id
1641: and option_type = c_FUEL_TYPE
1642: and option_code is not null;
1643:
1667: where policy_id = p_policy_id;
1668:
1669: select count(threshold)
1670: into l_thresholds_count
1671: from ap_pol_schedule_options
1672: where policy_id = p_policy_id
1673: and (option_type = c_TIME_THRESHOLD)
1674: and threshold is not null;
1675:
1699: where policy_id = p_policy_id;
1700:
1701: select count(threshold)
1702: into l_thresholds_count
1703: from ap_pol_schedule_options
1704: where policy_id = p_policy_id
1705: and (option_type = c_DISTANCE_THRESHOLD)
1706: and threshold is not null;
1707:
1755: where policy_id = p_policy_id;
1756:
1757: select count(1)
1758: into l_addon_rates_count
1759: from ap_pol_schedule_options
1760: where policy_id = p_policy_id
1761: and (option_type = c_ADDON_RATES)
1762: and option_code is not null;
1763:
1840: else
1841: if (p_rule = c_CURRENCY) then
1842: if (l_currency_preference = c_SRC) then
1843: /*
1844: if Single Rate Currency there will be no records in ap_pol_schedule_options
1845: we must still permutate using ap_pol_headers.currency_code
1846: */
1847: return l_src_stmt;
1848: else
1846: */
1847: return l_src_stmt;
1848: else
1849: /*
1850: if Location Currency Rate there will be no records in ap_pol_schedule_options
1851: if Airfare there will be no records in ap_pol_schedule_options
1852: */
1853: return l_varchar2_stmt;
1854: end if;
1847: return l_src_stmt;
1848: else
1849: /*
1850: if Location Currency Rate there will be no records in ap_pol_schedule_options
1851: if Airfare there will be no records in ap_pol_schedule_options
1852: */
1853: return l_varchar2_stmt;
1854: end if;
1855: elsif (p_rule = c_VEHICLE_CATEGORY) then
1976: l_thresholds_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_THRESHOLD);
1977: l_addon_rates_enabled VARCHAR2(1) := checkRuleOption(p_policy_id, c_ADDON_RATES);
1978: l_night_rates_enabled VARCHAR2(1) := isNightRatesEnabled(p_policy_id);
1979:
1980: l_schedule_option_rec ap_pol_schedule_options%ROWTYPE;
1981: l_zero_threshold_count NUMBER;
1982: l_category_code ap_pol_headers.category_code%TYPE;
1983: l_rate_type_code ap_pol_schedule_options.rate_type_code%TYPE;
1984: l_schedule_type ap_pol_headers.schedule_type_code%TYPE;
1979:
1980: l_schedule_option_rec ap_pol_schedule_options%ROWTYPE;
1981: l_zero_threshold_count NUMBER;
1982: l_category_code ap_pol_headers.category_code%TYPE;
1983: l_rate_type_code ap_pol_schedule_options.rate_type_code%TYPE;
1984: l_schedule_type ap_pol_headers.schedule_type_code%TYPE;
1985: l_source ap_pol_headers.source%TYPE;
1986:
1987: ---------------------------------------
1996: -- cursor for first last and same day rate periods
1997: --------------------------------------------------
1998: cursor c_rate_types is
1999: select distinct rate_type_code
2000: from ap_pol_schedule_options
2001: where policy_id = p_policy_id
2002: and rate_type_code in ('FIRST_PERIOD', 'LAST_PERIOD', 'SAME_DAY')
2003: and option_type = 'TIME_THRESHOLD';
2004:
2080: cursor l_l_cursor is
2081: select
2082: '
2083: (select LOCATION_ID
2084: from AP_POL_SCHEDULE_OPTIONS pso
2085: where
2086: POLICY_ID = :p_policy_id
2087: and OPTION_TYPE = :c_LOCATION
2088: and LOCATION_ID IS NOT NULL
2098: cursor l_r_cursor is
2099: select
2100: '
2101: (select ROLE_ID
2102: from AP_POL_SCHEDULE_OPTIONS pso
2103: where
2104: POLICY_ID = :p_policy_id
2105: and OPTION_TYPE = :c_EMPLOYEE_ROLE
2106: and ROLE_ID IS NOT NULL
2116: cursor l_c_cursor is
2117: select
2118: '
2119: (select CURRENCY_CODE
2120: from AP_POL_SCHEDULE_OPTIONS pso
2121: where
2122: POLICY_ID = :p_policy_id
2123: and OPTION_TYPE = :c_CURRENCY
2124: and CURRENCY_CODE IS NOT NULL
2134: cursor l_vc_cursor is
2135: select
2136: '
2137: (select OPTION_CODE, VEHICLE_TYPE_CODE, FUEL_TYPE_CODE
2138: from AP_POL_SCHEDULE_OPTIONS pso
2139: where
2140: POLICY_ID = :p_policy_id
2141: and OPTION_TYPE = :c_VEHICLE_CATEGORY
2142: and OPTION_CODE IS NOT NULL
2152: cursor l_vt_cursor is
2153: select
2154: '
2155: (select OPTION_CODE
2156: from AP_POL_SCHEDULE_OPTIONS pso
2157: where
2158: POLICY_ID = :p_policy_id
2159: and OPTION_TYPE = :c_VEHICLE_TYPE
2160: and OPTION_CODE IS NOT NULL
2170: cursor l_ft_cursor is
2171: select
2172: '
2173: (select OPTION_CODE
2174: from AP_POL_SCHEDULE_OPTIONS pso
2175: where
2176: POLICY_ID = :p_policy_id
2177: and OPTION_TYPE = :c_FUEL_TYPE
2178: and OPTION_CODE IS NOT NULL
2189: select
2190: '
2191: (select THRESHOLD,
2192: ap_web_policy_UTILS.getHighEndOfThreshold(:p_policy_id, THRESHOLD, nvl(:p_rate_type,''STANDARD'')) AS RANGE_HIGH
2193: from AP_POL_SCHEDULE_OPTIONS pso
2194: where
2195: POLICY_ID = :p_policy_id
2196: and (OPTION_TYPE = :c_DISTANCE_THRESHOLD or OPTION_TYPE = :c_TIME_THRESHOLD)
2197: and THRESHOLD IS NOT NULL
2284: -- ----------------------------------------------------------
2285: IF ( l_category_code = 'PER_DIEM' ) THEN
2286: BEGIN
2287: -- Delete zero threshold rows where not needed
2288: delete from ap_pol_schedule_options
2289: where policy_id = p_policy_id
2290: and option_type = 'TIME_THRESHOLD'
2291: and threshold = 0
2292: and rate_type_code not in
2290: and option_type = 'TIME_THRESHOLD'
2291: and threshold = 0
2292: and rate_type_code not in
2293: ( select distinct rate_type_code
2294: from ap_pol_schedule_options
2295: where policy_id = p_policy_id
2296: and rate_type_code in ('FIRST_PERIOD', 'LAST_PERIOD', 'SAME_DAY')
2297: and option_type = 'TIME_THRESHOLD'
2298: and threshold > 0 )
2301: FOR rate_type_cur in c_rate_types
2302: LOOP
2303: select count(1)
2304: into l_zero_threshold_count
2305: from ap_pol_schedule_options
2306: where policy_id = p_policy_id
2307: and rate_type_code = rate_type_cur.rate_type_code
2308: and threshold = 0;
2309:
2308: and threshold = 0;
2309:
2310: IF ( l_zero_threshold_count = 0 ) THEN
2311: BEGIN
2312: SELECT ap_pol_schedule_options_s.NEXTVAL
2313: INTO l_schedule_option_rec.schedule_option_id
2314: FROM DUAL;
2315:
2316: INSERT INTO ap_pol_schedule_options
2312: SELECT ap_pol_schedule_options_s.NEXTVAL
2313: INTO l_schedule_option_rec.schedule_option_id
2314: FROM DUAL;
2315:
2316: INSERT INTO ap_pol_schedule_options
2317: (
2318: policy_id,
2319: schedule_option_id,
2320: option_type,
2350: l_schedule_option_rec.last_update_date := sysdate;
2351: l_schedule_option_rec.last_updated_by := p_user_id;
2352: l_schedule_option_rec.rate_type_code := rate_type_cur.rate_type_code;
2353:
2354: INSERT INTO ap_pol_schedule_options values l_schedule_option_rec;
2355: ----------------------------------------------------------------------------------*/
2356: END;
2357: END IF;
2358: END LOOP;
2373: IF ( l_zero_threshold_count = 1 ) THEN
2374: BEGIN
2375: select count(1)
2376: into l_zero_threshold_count
2377: from ap_pol_schedule_options
2378: where policy_id = p_policy_id
2379: and rate_type_code = 'STANDARD'
2380: and threshold = 0;
2381:
2380: and threshold = 0;
2381:
2382: IF ( l_zero_threshold_count = 0 ) THEN
2383: BEGIN
2384: SELECT ap_pol_schedule_options_s.NEXTVAL
2385: INTO l_schedule_option_rec.schedule_option_id
2386: FROM DUAL;
2387:
2388: INSERT INTO ap_pol_schedule_options
2384: SELECT ap_pol_schedule_options_s.NEXTVAL
2385: INTO l_schedule_option_rec.schedule_option_id
2386: FROM DUAL;
2387:
2388: INSERT INTO ap_pol_schedule_options
2389: (
2390: policy_id,
2391: schedule_option_id,
2392: option_type,
2422: l_schedule_option_rec.last_update_date := sysdate;
2423: l_schedule_option_rec.last_updated_by := p_user_id;
2424: l_schedule_option_rec.rate_type_code := 'STANDARD';
2425:
2426: INSERT INTO ap_pol_schedule_options values l_schedule_option_rec;
2427: ---------------------------------------------------------------------------------*/
2428:
2429: END;
2430: END IF;
2434: -- ---------------------------------------------------------------------
2435: -- This means that we should not have a zero row for standard rate type
2436: -- So delete any such rows
2437: -- ---------------------------------------------------------------------
2438: delete from ap_pol_schedule_options
2439: where policy_id = p_policy_id
2440: and rate_type_code = 'STANDARD'
2441: and threshold = 0;
2442:
2676: where pl.POLICY_ID = :p_policy_id
2677: and ((pl.LOCATION_ID is not null
2678: and not exists
2679: (select pso.LOCATION_ID
2680: from AP_POL_SCHEDULE_OPTIONS pso
2681: where pso.POLICY_ID = pl.POLICY_ID
2682: and pso.OPTION_TYPE = :c_LOCATION
2683: and pso.LOCATION_ID is not null
2684: and pso.LOCATION_ID = pl.LOCATION_ID
2687: or
2688: (pl.LOCATION_ID is null
2689: and exists
2690: (select pso.LOCATION_ID
2691: from AP_POL_SCHEDULE_OPTIONS pso
2692: where pso.POLICY_ID = pl.POLICY_ID
2693: and pso.OPTION_TYPE = :c_LOCATION
2694: and pso.LOCATION_ID is not null
2695: )
2703: or
2704: (pl.ROLE_ID is not null
2705: and not exists
2706: (select pso.ROLE_ID
2707: from AP_POL_SCHEDULE_OPTIONS pso
2708: where pso.POLICY_ID = pl.POLICY_ID
2709: and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
2710: and pso.ROLE_ID is not null
2711: and pso.ROLE_ID = pl.ROLE_ID
2714: or
2715: (pl.ROLE_ID is null
2716: and exists
2717: (select pso.ROLE_ID
2718: from AP_POL_SCHEDULE_OPTIONS pso
2719: where pso.POLICY_ID = pl.POLICY_ID
2720: and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
2721: and pso.ROLE_ID is not null
2722: )
2731: (pl.CURRENCY_CODE is not null
2732: and
2733: (not exists
2734: (select pso.CURRENCY_CODE
2735: from AP_POL_SCHEDULE_OPTIONS pso
2736: where pso.POLICY_ID = pl.POLICY_ID
2737: and pso.OPTION_TYPE = :c_CURRENCY
2738: and pso.CURRENCY_CODE is not null
2739: and pso.CURRENCY_CODE = pl.CURRENCY_CODE
2776: or
2777: (pl.VEHICLE_CATEGORY is not null
2778: and not exists
2779: (select pso.OPTION_CODE
2780: from AP_POL_SCHEDULE_OPTIONS pso
2781: where pso.POLICY_ID = pl.POLICY_ID
2782: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2783: and pso.OPTION_CODE is not null
2784: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2787: or
2788: (pl.VEHICLE_CATEGORY is null
2789: and exists
2790: (select pso.OPTION_CODE
2791: from AP_POL_SCHEDULE_OPTIONS pso
2792: where pso.POLICY_ID = pl.POLICY_ID
2793: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2794: and pso.OPTION_CODE is not null
2795: )
2804: (pl.VEHICLE_TYPE is not null
2805: and
2806: (not exists
2807: (select pso.OPTION_CODE
2808: from AP_POL_SCHEDULE_OPTIONS pso
2809: where pso.POLICY_ID = pl.POLICY_ID
2810: and pso.OPTION_TYPE = :c_VEHICLE_TYPE
2811: and pso.OPTION_CODE is not null
2812: and pso.OPTION_CODE = pl.VEHICLE_TYPE
2812: and pso.OPTION_CODE = pl.VEHICLE_TYPE
2813: )
2814: or exists
2815: (select pso.OPTION_CODE
2816: from AP_POL_SCHEDULE_OPTIONS pso
2817: where pso.POLICY_ID = pl.POLICY_ID
2818: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2819: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2820: and pso.VEHICLE_TYPE_CODE <> ''R''
2823: or
2824: (pl.VEHICLE_TYPE is null
2825: and exists
2826: (select pso.OPTION_CODE
2827: from AP_POL_SCHEDULE_OPTIONS pso
2828: where pso.POLICY_ID = pl.POLICY_ID
2829: and pso.OPTION_TYPE = :c_VEHICLE_TYPE
2830: and pso.OPTION_CODE is not null
2831: )
2830: and pso.OPTION_CODE is not null
2831: )
2832: and not exists
2833: (select pso.OPTION_CODE
2834: from AP_POL_SCHEDULE_OPTIONS pso
2835: where pso.POLICY_ID = pl.POLICY_ID
2836: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2837: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2838: and pso.VEHICLE_TYPE_CODE <> ''R''
2848: (pl.FUEL_TYPE is not null
2849: and
2850: (not exists
2851: (select pso.OPTION_CODE
2852: from AP_POL_SCHEDULE_OPTIONS pso
2853: where pso.POLICY_ID = pl.POLICY_ID
2854: and pso.OPTION_TYPE = :c_FUEL_TYPE
2855: and pso.OPTION_CODE is not null
2856: and pso.OPTION_CODE = pl.FUEL_TYPE
2856: and pso.OPTION_CODE = pl.FUEL_TYPE
2857: )
2858: or exists
2859: (select pso.OPTION_CODE
2860: from AP_POL_SCHEDULE_OPTIONS pso
2861: where pso.POLICY_ID = pl.POLICY_ID
2862: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2863: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2864: and pso.FUEL_TYPE_CODE <> ''R''
2867: or
2868: (pl.FUEL_TYPE is null
2869: and exists
2870: (select pso.OPTION_CODE
2871: from AP_POL_SCHEDULE_OPTIONS pso
2872: where pso.POLICY_ID = pl.POLICY_ID
2873: and pso.OPTION_TYPE = :c_FUEL_TYPE
2874: and pso.OPTION_CODE is not null
2875: )
2874: and pso.OPTION_CODE is not null
2875: )
2876: and not exists
2877: (select pso.OPTION_CODE
2878: from AP_POL_SCHEDULE_OPTIONS pso
2879: where pso.POLICY_ID = pl.POLICY_ID
2880: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2881: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2882: and pso.FUEL_TYPE_CODE <> ''R''
2891: or
2892: (pl.RANGE_LOW is not null
2893: and not exists
2894: (select pso.THRESHOLD
2895: from AP_POL_SCHEDULE_OPTIONS pso
2896: where pso.POLICY_ID = pl.POLICY_ID
2897: and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
2898: and pso.THRESHOLD is not null
2899: and pso.THRESHOLD = pl.RANGE_LOW
2903: or
2904: (pl.RANGE_HIGH is not null
2905: and not exists
2906: (select pso.THRESHOLD
2907: from AP_POL_SCHEDULE_OPTIONS pso
2908: where pso.POLICY_ID = pl.POLICY_ID
2909: and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
2910: and pso.THRESHOLD is not null
2911: and pso.THRESHOLD = pl.RANGE_HIGH
2915: or
2916: (pl.RANGE_LOW is null
2917: and exists
2918: (select pso.THRESHOLD
2919: from AP_POL_SCHEDULE_OPTIONS pso
2920: where pso.POLICY_ID = pl.POLICY_ID
2921: and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
2922: and pso.THRESHOLD is not null
2923: and nvl(pso.rate_type_code, ''NULL'') = nvl(pl.rate_type_code, ''NULL'')
2945: ' or
2946: (pl.ADDON_MILEAGE_RATE_CODE is not null
2947: and not exists
2948: (select pso.OPTION_CODE
2949: from AP_POL_SCHEDULE_OPTIONS pso
2950: where pso.POLICY_ID = pl.POLICY_ID
2951: and pso.OPTION_TYPE = :c_ADDON_RATES
2952: and pso.OPTION_CODE is not null
2953: and pso.OPTION_CODE = pl.ADDON_MILEAGE_RATE_CODE
3082: and pl.STATUS = :c_ACTIVE
3083: and ((pl.LOCATION_ID is not null
3084: and exists
3085: (select pso.LOCATION_ID
3086: from AP_POL_SCHEDULE_OPTIONS pso
3087: where pso.POLICY_ID = pl.POLICY_ID
3088: and pso.OPTION_TYPE = :c_LOCATION
3089: and pso.LOCATION_ID is not null
3090: and pso.LOCATION_ID = pl.LOCATION_ID
3094: or
3095: (pl.ROLE_ID is not null
3096: and exists
3097: (select pso.ROLE_ID
3098: from AP_POL_SCHEDULE_OPTIONS pso
3099: where pso.POLICY_ID = pl.POLICY_ID
3100: and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
3101: and pso.ROLE_ID is not null
3102: and pso.ROLE_ID = pl.ROLE_ID
3106: or
3107: (pl.CURRENCY_CODE is not null
3108: and exists
3109: (select pso.CURRENCY_CODE
3110: from AP_POL_SCHEDULE_OPTIONS pso
3111: where pso.POLICY_ID = pl.POLICY_ID
3112: and pso.OPTION_TYPE = :c_CURRENCY
3113: and pso.CURRENCY_CODE is not null
3114: and pso.CURRENCY_CODE = pl.CURRENCY_CODE
3118: or
3119: (pl.VEHICLE_CATEGORY is not null
3120: and exists
3121: (select pso.OPTION_CODE
3122: from AP_POL_SCHEDULE_OPTIONS pso
3123: where pso.POLICY_ID = pl.POLICY_ID
3124: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
3125: and pso.OPTION_CODE is not null
3126: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
3130: or
3131: (pl.VEHICLE_TYPE is not null
3132: and exists
3133: (select pso.OPTION_CODE
3134: from AP_POL_SCHEDULE_OPTIONS pso
3135: where pso.POLICY_ID = pl.POLICY_ID
3136: and pso.OPTION_TYPE = :c_VEHICLE_TYPE
3137: and pso.OPTION_CODE is not null
3138: and pso.OPTION_CODE = pl.VEHICLE_TYPE
3142: or
3143: (pl.FUEL_TYPE is not null
3144: and exists
3145: (select pso.OPTION_CODE
3146: from AP_POL_SCHEDULE_OPTIONS pso
3147: where pso.POLICY_ID = pl.POLICY_ID
3148: and pso.OPTION_TYPE = :c_FUEL_TYPE
3149: and pso.OPTION_CODE is not null
3150: and pso.OPTION_CODE = pl.FUEL_TYPE
3154: or
3155: (pl.RANGE_LOW is not null
3156: and exists
3157: (select pso.THRESHOLD
3158: from AP_POL_SCHEDULE_OPTIONS pso
3159: where pso.POLICY_ID = pl.POLICY_ID
3160: and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
3161: and pso.THRESHOLD is not null
3162: and pso.THRESHOLD = pl.RANGE_LOW
3177: and pl.STATUS = :c_INACTIVE
3178: and (((pl.LOCATION_ID is not null
3179: and exists
3180: (select pso.LOCATION_ID
3181: from AP_POL_SCHEDULE_OPTIONS pso
3182: where pso.POLICY_ID = pl.POLICY_ID
3183: and pso.OPTION_TYPE = :c_LOCATION
3184: and pso.LOCATION_ID is not null
3185: and pso.LOCATION_ID = pl.LOCATION_ID
3190: and
3191: ((pl.ROLE_ID is not null
3192: and exists
3193: (select pso.ROLE_ID
3194: from AP_POL_SCHEDULE_OPTIONS pso
3195: where pso.POLICY_ID = pl.POLICY_ID
3196: and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
3197: and pso.ROLE_ID is not null
3198: and pso.ROLE_ID = pl.ROLE_ID
3204: ((pl.CURRENCY_CODE is not null
3205: and
3206: (exists
3207: (select pso.CURRENCY_CODE
3208: from AP_POL_SCHEDULE_OPTIONS pso
3209: where pso.POLICY_ID = pl.POLICY_ID
3210: and pso.OPTION_TYPE = :c_CURRENCY
3211: and pso.CURRENCY_CODE is not null
3212: and pso.CURRENCY_CODE = pl.CURRENCY_CODE
3226: and
3227: ((pl.VEHICLE_CATEGORY is not null
3228: and exists
3229: (select pso.OPTION_CODE
3230: from AP_POL_SCHEDULE_OPTIONS pso
3231: where pso.POLICY_ID = pl.POLICY_ID
3232: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
3233: and pso.OPTION_CODE is not null
3234: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
3239: and
3240: ((pl.VEHICLE_TYPE is not null
3241: and exists
3242: (select pso.OPTION_CODE
3243: from AP_POL_SCHEDULE_OPTIONS pso
3244: where pso.POLICY_ID = pl.POLICY_ID
3245: and pso.OPTION_TYPE = :c_VEHICLE_TYPE
3246: and pso.OPTION_CODE is not null
3247: and pso.OPTION_CODE = pl.VEHICLE_TYPE
3252: and
3253: ((pl.FUEL_TYPE is not null
3254: and exists
3255: (select pso.OPTION_CODE
3256: from AP_POL_SCHEDULE_OPTIONS pso
3257: where pso.POLICY_ID = pl.POLICY_ID
3258: and pso.OPTION_TYPE = :c_FUEL_TYPE
3259: and pso.OPTION_CODE is not null
3260: and pso.OPTION_CODE = pl.FUEL_TYPE
3265: and
3266: ((pl.RANGE_LOW is not null
3267: and exists
3268: (select pso.THRESHOLD
3269: from AP_POL_SCHEDULE_OPTIONS pso
3270: where pso.POLICY_ID = pl.POLICY_ID
3271: and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
3272: and pso.THRESHOLD is not null
3273: and pso.THRESHOLD = pl.RANGE_LOW
4213: p_to_policy_id IN ap_pol_headers.policy_id%TYPE) IS
4214:
4215: BEGIN
4216:
4217: insert into AP_POL_SCHEDULE_OPTIONS
4218: (
4219: SCHEDULE_OPTION_ID,
4220: POLICY_ID,
4221: OPTION_TYPE,
4235: LAST_UPDATE_DATE,
4236: LAST_UPDATED_BY
4237: )
4238: select
4239: AP_POL_SCHEDULE_OPTIONS_S.NEXTVAL AS SCHEDULE_OPTION_ID,
4240: p_to_policy_id AS POLICY_ID,
4241: OPTION_TYPE,
4242: OPTION_CODE,
4243: THRESHOLD,
4254: null AS LAST_UPDATE_LOGIN,
4255: sysdate AS LAST_UPDATE_DATE,
4256: p_user_id AS LAST_UPDATED_BY
4257: from
4258: AP_POL_SCHEDULE_OPTIONS
4259: where POLICY_ID = p_from_policy_id;
4260:
4261: EXCEPTION
4262: WHEN OTHERS THEN
4367: p_currency_code IN VARCHAR2,
4368: p_end_date IN DATE) RETURN VARCHAR2 IS
4369: CURSOR active_cur IS
4370: select schedule_option_id
4371: from ap_pol_schedule_options
4372: where option_type = p_option_type
4373: and NVL(option_code,9.99E125) = NVL(p_option_code,9.99E125)
4374: and NVL(threshold,9.99E125) = NVL(p_threshold,9.99E125)
4375: and NVL(role_id,9.99E125) = NVL(p_role_id,9.99E125)
4433:
4434: IF ( l_end_date is not null )
4435: THEN
4436:
4437: update ap_pol_schedule_options
4438: set end_date = l_end_date
4439: where option_type = 'LOCATION'
4440: and location_id = l_location_id
4441: and NVL(end_date,TO_DATE('1','j')) >=
4522: *=======================================================================*/
4523: PROCEDURE status_saved_sched_opts(p_policy_id IN NUMBER) IS
4524: BEGIN
4525: IF p_policy_id IS NOT NULL THEN
4526: update AP_POL_SCHEDULE_OPTIONS set STATUS = 'SAVED' where POLICY_ID = p_policy_id and nvl(STATUS, '~') <> 'ACTIVE';
4527: END IF;
4528: END status_saved_sched_opts;
4529:
4530: /*========================================================================
4576: p_status_code IN VARCHAR2) IS
4577: BEGIN
4578: IF p_policy_id IS NOT NULL AND
4579: p_status_code IS NOT NULL THEN
4580: update AP_POL_SCHEDULE_OPTIONS set STATUS = p_status_code where POLICY_ID = p_policy_id;
4581: END IF;
4582: END set_status_pol_sched_opts;
4583:
4584: /*========================================================================
4846: PROCEDURE deletePolicySchedule(p_policy_id IN NUMBER) IS
4847:
4848: BEGIN
4849:
4850: DELETE FROM AP_POL_SCHEDULE_OPTIONS WHERE POLICY_ID = p_policy_id;
4851:
4852: DELETE FROM AP_POL_SCHEDULE_PERIODS WHERE POLICY_ID = p_policy_id;
4853:
4854: DELETE FROM AP_POL_LINES WHERE POLICY_ID = p_policy_id;
4999: |
5000: *=======================================================================*/
5001: PROCEDURE permutatePolicyLines(p_user_id IN NUMBER,
5002: p_policy_id IN ap_pol_headers.policy_id%TYPE,
5003: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) IS
5004:
5005: l_schedule_period_id ap_pol_schedule_periods.schedule_period_id%TYPE;
5006: l_permutate_curref INTEGER;
5007: l_rows_permutated NUMBER := 0;
5095: cursor l_l_cursor is
5096: select
5097: '
5098: (select LOCATION_ID
5099: from AP_POL_SCHEDULE_OPTIONS pso
5100: where
5101: POLICY_ID = :p_policy_id
5102: and OPTION_TYPE = :c_LOCATION
5103: and LOCATION_ID IS NOT NULL
5113: cursor l_r_cursor is
5114: select
5115: '
5116: (select ROLE_ID
5117: from AP_POL_SCHEDULE_OPTIONS pso
5118: where
5119: POLICY_ID = :p_policy_id
5120: and OPTION_TYPE = :c_EMPLOYEE_ROLE
5121: and ROLE_ID IS NOT NULL
5131: cursor l_c_cursor is
5132: select
5133: '
5134: (select CURRENCY_CODE
5135: from AP_POL_SCHEDULE_OPTIONS pso
5136: where
5137: POLICY_ID = :p_policy_id
5138: and OPTION_TYPE = :c_CURRENCY
5139: and CURRENCY_CODE IS NOT NULL
5150: select
5151: '
5152: (select THRESHOLD,
5153: ap_web_policy_UTILS.getHighEndOfThreshold(:p_policy_id, THRESHOLD, nvl(:p_rate_type,''STANDARD'')) AS RANGE_HIGH
5154: from AP_POL_SCHEDULE_OPTIONS pso
5155: where
5156: POLICY_ID = :p_policy_id
5157: and (OPTION_TYPE = :c_DISTANCE_THRESHOLD or OPTION_TYPE = :c_TIME_THRESHOLD)
5158: and THRESHOLD IS NOT NULL
5417: apl.CREATED_BY,
5418: sysdate AS LAST_UPDATE_DATE,
5419: apl.LAST_UPDATED_BY
5420: from ap_pol_lines apl,
5421: ap_pol_schedule_options pso
5422: where apl.POLICY_ID = p_policy_id
5423: and apl.schedule_period_id = p_schedule_period_id -- Bug: 15996892
5424: and pso.policy_id = apl.policy_id
5425: and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'
5436: and addon_mileage_rate_code is not null
5437: and not exists
5438: (
5439: select 1
5440: from ap_pol_schedule_options pso
5441: where pso.policy_id = pl.policy_id
5442: and pso.option_code = pl.addon_mileage_rate_code
5443: );
5444: -- ---------------------------------------------------------
5483: apl.CREATED_BY,
5484: sysdate AS LAST_UPDATE_DATE,
5485: apl.LAST_UPDATED_BY
5486: from ap_pol_lines apl,
5487: ap_pol_schedule_options pso
5488: where apl.POLICY_ID = p_policy_id
5489: and pso.policy_id = apl.policy_id
5490: and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'
5491: and OPTION_CODE IS NOT NULL
5556: apl.currency_code,
5557: option_code as night_rate_type_code,
5558: 'NEW' AS STATUS
5559: from ap_pol_lines apl,
5560: ap_pol_schedule_options pso
5561: where apl.POLICY_ID = p_policy_id
5562: and apl.schedule_period_id = p_schedule_period_id -- Bug: 15996892
5563: and pso.policy_id(+) = apl.policy_id
5564: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5579: apl.currency_code,
5580: option_code as night_rate_type_code,
5581: 'NEW' AS STATUS
5582: from ap_pol_lines apl,
5583: ap_pol_schedule_options pso,
5584: ap_pol_lines epl
5585: where apl.POLICY_ID = p_policy_id
5586: and pso.policy_id(+) = apl.policy_id
5587: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5699: where policy_id = p_policy_id
5700: and rate_type_code = 'NIGHT_RATE'
5701: and (night_rate_type_code is NULL
5702: or night_rate_type_code not in(select option_code
5703: from ap_pol_schedule_options
5704: where policy_id = p_policy_id));
5705: END IF;
5706:
5707: -- ---------------------------------------------------------
5821: where policy_id = p_policy_id;
5822:
5823: select count(1)
5824: into l_first_period_count
5825: from ap_pol_schedule_options
5826: where policy_id = p_policy_id
5827: and rate_type_code= 'FIRST_PERIOD';
5828:
5829: if (l_rate_period_type_code = 'STANDARD_FIRST_LAST' and l_first_period_count > 0)
5872: where policy_id = p_policy_id;
5873:
5874: select count(1)
5875: into l_last_period_count
5876: from ap_pol_schedule_options
5877: where policy_id = p_policy_id
5878: and rate_type_code= 'LAST_PERIOD';
5879:
5880: if (l_rate_period_type_code = 'STANDARD_FIRST_LAST' and l_last_period_count > 0)
5922: where policy_id = p_policy_id;
5923:
5924: select count(1)
5925: into l_same_day_count
5926: from ap_pol_schedule_options
5927: where policy_id = p_policy_id
5928: and rate_type_code= 'SAME_DAY';
5929:
5930: if (l_same_day_rate_code = 'DEFINED' and l_same_day_count > 0)
5979: WHERE pl.policy_id = p_policy_id
5980: AND NVL(role_id, -1) <> -1
5981: AND NOT EXISTS
5982: ( SELECT 1
5983: FROM ap_pol_schedule_options pso
5984: WHERE pso.policy_id = pl.policy_id
5985: AND pso.option_type = 'EMPLOYEE_ROLE'
5986: AND pso.option_code = pl.role_id
5987: );
6078: sysdate as LAST_UPDATE_DATE,
6079: p_user_id as LAST_UPDATED_BY,
6080: apl.REIMBURSEMENT_PERCENTAGE
6081: FROM ap_pol_lines apl,
6082: ap_pol_schedule_options pso
6083: WHERE apl.policy_id = pso.policy_id
6084: AND apl.role_id = -1
6085: AND pso.option_type = 'EMPLOYEE_ROLE'
6086: AND pso.role_id IS NOT NULL
6200: FUNCTION getPolicyLocationId( p_expense_type_id IN NUMBER,
6201: p_expense_date IN DATE,
6202: p_location_id IN NUMBER ) RETURN NUMBER IS
6203:
6204: l_location_id AP_POL_SCHEDULE_OPTIONS.location_id%type;
6205:
6206: BEGIN
6207: SELECT LOCATION_ID
6208: INTO l_location_id
6208: INTO l_location_id
6209: FROM (
6210: -- This query verifies that a given location is active within a policy
6211: select location_id
6212: from AP_POL_SCHEDULE_OPTIONS opts, ap_expense_report_params_all p
6213: where p.parameter_id = p_expense_type_id
6214: AND policy_id = p.company_policy_id
6215: AND option_type = 'LOCATION'
6216: AND status = 'ACTIVE'
6217: AND (opts.end_date is null OR opts.end_date >= p_expense_date)
6218: AND LOCATION_ID = p_location_id
6219: UNION ALL
6220: select opts.location_id
6221: from AP_POL_SCHEDULE_OPTIONS opts, AP_POL_LOCATIONS_B loc1, AP_POL_LOCATIONS_B loc2, ap_expense_report_params_all p
6222: where p.parameter_id = p_expense_type_id
6223: AND policy_id = p.company_policy_id
6224: AND opts.option_type = 'LOCATION'
6225: AND opts.status = 'ACTIVE'
6231: AND loc2.location_id = p_location_id
6232: UNION ALL
6233: -- Will find the all other location for a given policy
6234: select loc.location_id
6235: from AP_POL_SCHEDULE_OPTIONS opts, AP_POL_LOCATIONS_B loc, ap_expense_report_params_all p
6236: where p.parameter_id = p_expense_type_id
6237: AND opts.policy_id = p.company_policy_id
6238: AND opts.option_type = 'LOCATION'
6239: AND opts.status = 'ACTIVE'
6314: AND p_expense_date between sp.start_date and nvl(sp.end_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'))
6315: AND sys.org_id = rate_opts.org_id(+)
6316: AND (nvl(h.employee_role_flag, 'N') = 'N' OR
6317: l.role_id = nvl((select ROLE_ID
6318: from AP_POL_SCHEDULE_OPTIONS
6319: where policy_id = h.policy_id
6320: AND option_type = 'EMPLOYEE_ROLE'
6321: AND status = 'ACTIVE'
6322: AND (end_date is null OR end_date >= p_expense_date)