[Home] [Help]
159: -- before the definition.
160:
161: PROCEDURE permutatePolicyLines(p_user_id IN NUMBER,
162: p_policy_id IN ap_pol_headers.policy_id%TYPE,
163: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE);
164:
165: PROCEDURE permutateAddonRates( p_user_id IN NUMBER,
166: p_policy_id IN ap_pol_headers.policy_id%TYPE,
167: p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
420: p_low_threshold IN NUMBER) RETURN NUMBER IS
421:
422: CURSOR threshold_c IS
423: SELECT threshold
424: FROM ap_pol_schedule_options
425: WHERE option_type = p_lookup_type
426: AND policy_id = p_policy_id
427: ORDER BY threshold;
428:
939: CURSOR policy_cur IS
940: select ph.role_code,
941: pso.role_id
942: from ap_pol_headers ph,
943: ap_pol_schedule_options pso
944: where ph.policy_id = pso.policy_id
945: and pso.schedule_option_id = p_policy_schedule_option_id;
946:
947: policy_rec policy_cur%ROWTYPE;
1266: | 16-May-2002 R Langi Created
1267: |
1268: *=======================================================================*/
1269: FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1270: p_threshold IN ap_pol_schedule_options.threshold%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE IS
1271: BEGIN
1272:
1273: RETURN getHighEndOfThreshold(p_policy_id, p_threshold, 'STANDARD');
1274:
1293: | 01-Nov-2005 krmenon Created
1294: |
1295: *=======================================================================*/
1296: FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1297: p_threshold IN ap_pol_schedule_options.threshold%TYPE,
1298: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE IS
1299:
1300: l_high_end_of_threshold ap_pol_schedule_options.threshold%TYPE;
1301:
1294: |
1295: *=======================================================================*/
1296: FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1297: p_threshold IN ap_pol_schedule_options.threshold%TYPE,
1298: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE IS
1299:
1300: l_high_end_of_threshold ap_pol_schedule_options.threshold%TYPE;
1301:
1302: CURSOR c_threshold IS
1296: FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1297: p_threshold IN ap_pol_schedule_options.threshold%TYPE,
1298: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE IS
1299:
1300: l_high_end_of_threshold ap_pol_schedule_options.threshold%TYPE;
1301:
1302: CURSOR c_threshold IS
1303: SELECT threshold
1304: FROM ap_pol_schedule_options
1300: l_high_end_of_threshold ap_pol_schedule_options.threshold%TYPE;
1301:
1302: CURSOR c_threshold IS
1303: SELECT threshold
1304: FROM ap_pol_schedule_options
1305: WHERE policy_id = p_policy_id
1306: AND threshold is not null
1307: AND nvl(rate_type_code, 'STANDARD') = p_rate_type
1308: ORDER BY threshold;
1419: where policy_id = p_policy_id;
1420:
1421: select count(location_id)
1422: into l_location_count
1423: from ap_pol_schedule_options
1424: where policy_id = p_policy_id
1425: and option_type = c_LOCATION
1426: and location_id is not null;
1427:
1451: where policy_id = p_policy_id;
1452:
1453: select count(role_id)
1454: into l_role_count
1455: from ap_pol_schedule_options
1456: where policy_id = p_policy_id
1457: and option_type = c_EMPLOYEE_ROLE
1458: and role_id is not null;
1459:
1483: where policy_id = p_policy_id;
1484:
1485: select count(currency_code)
1486: into l_currency_count
1487: from ap_pol_schedule_options
1488: where policy_id = p_policy_id
1489: and option_type = c_CURRENCY
1490: and currency_code is not null;
1491:
1515: where policy_id = p_policy_id;
1516:
1517: select count(option_code)
1518: into l_vehicle_category_count
1519: from ap_pol_schedule_options
1520: where policy_id = p_policy_id
1521: and option_type = c_VEHICLE_CATEGORY
1522: and option_code is not null;
1523:
1547: where policy_id = p_policy_id;
1548:
1549: select count(option_code)
1550: into l_vehicle_type_count
1551: from ap_pol_schedule_options
1552: where policy_id = p_policy_id
1553: and option_type = c_VEHICLE_TYPE
1554: and option_code is not null;
1555:
1579: where policy_id = p_policy_id;
1580:
1581: select count(option_code)
1582: into l_fuel_type_count
1583: from ap_pol_schedule_options
1584: where policy_id = p_policy_id
1585: and option_type = c_FUEL_TYPE
1586: and option_code is not null;
1587:
1611: where policy_id = p_policy_id;
1612:
1613: select count(threshold)
1614: into l_thresholds_count
1615: from ap_pol_schedule_options
1616: where policy_id = p_policy_id
1617: and (option_type = c_TIME_THRESHOLD)
1618: and threshold is not null;
1619:
1643: where policy_id = p_policy_id;
1644:
1645: select count(threshold)
1646: into l_thresholds_count
1647: from ap_pol_schedule_options
1648: where policy_id = p_policy_id
1649: and (option_type = c_DISTANCE_THRESHOLD)
1650: and threshold is not null;
1651:
1699: where policy_id = p_policy_id;
1700:
1701: select count(1)
1702: into l_addon_rates_count
1703: from ap_pol_schedule_options
1704: where policy_id = p_policy_id
1705: and (option_type = c_ADDON_RATES)
1706: and option_code is not null;
1707:
1783: else
1784: if (p_rule = c_CURRENCY) then
1785: if (l_currency_preference = c_SRC) then
1786: /*
1787: if Single Rate Currency there will be no records in ap_pol_schedule_options
1788: we must still permutate using ap_pol_headers.currency_code
1789: */
1790: return l_src_stmt;
1791: else
1789: */
1790: return l_src_stmt;
1791: else
1792: /*
1793: if Location Currency Rate there will be no records in ap_pol_schedule_options
1794: if Airfare there will be no records in ap_pol_schedule_options
1795: */
1796: return l_varchar2_stmt;
1797: end if;
1790: return l_src_stmt;
1791: else
1792: /*
1793: if Location Currency Rate there will be no records in ap_pol_schedule_options
1794: if Airfare there will be no records in ap_pol_schedule_options
1795: */
1796: return l_varchar2_stmt;
1797: end if;
1798: elsif (p_rule = c_VEHICLE_CATEGORY) then
1917: l_thresholds_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_THRESHOLD);
1918: l_addon_rates_enabled VARCHAR2(1) := checkRuleOption(p_policy_id, c_ADDON_RATES);
1919: l_night_rates_enabled VARCHAR2(1) := isNightRatesEnabled(p_policy_id);
1920:
1921: l_schedule_option_rec ap_pol_schedule_options%ROWTYPE;
1922: l_zero_threshold_count NUMBER;
1923: l_category_code ap_pol_headers.category_code%TYPE;
1924: l_rate_type_code ap_pol_schedule_options.rate_type_code%TYPE;
1925: l_schedule_type ap_pol_headers.schedule_type_code%TYPE;
1920:
1921: l_schedule_option_rec ap_pol_schedule_options%ROWTYPE;
1922: l_zero_threshold_count NUMBER;
1923: l_category_code ap_pol_headers.category_code%TYPE;
1924: l_rate_type_code ap_pol_schedule_options.rate_type_code%TYPE;
1925: l_schedule_type ap_pol_headers.schedule_type_code%TYPE;
1926: l_source ap_pol_headers.source%TYPE;
1927:
1928: ---------------------------------------
1937: -- cursor for first last and same day rate periods
1938: --------------------------------------------------
1939: cursor c_rate_types is
1940: select distinct rate_type_code
1941: from ap_pol_schedule_options
1942: where policy_id = p_policy_id
1943: and rate_type_code in ('FIRST_PERIOD', 'LAST_PERIOD', 'SAME_DAY')
1944: and option_type = 'TIME_THRESHOLD';
1945:
2021: cursor l_l_cursor is
2022: select
2023: '
2024: (select LOCATION_ID
2025: from AP_POL_SCHEDULE_OPTIONS pso
2026: where
2027: POLICY_ID = :p_policy_id
2028: and OPTION_TYPE = :c_LOCATION
2029: and LOCATION_ID IS NOT NULL
2039: cursor l_r_cursor is
2040: select
2041: '
2042: (select ROLE_ID
2043: from AP_POL_SCHEDULE_OPTIONS pso
2044: where
2045: POLICY_ID = :p_policy_id
2046: and OPTION_TYPE = :c_EMPLOYEE_ROLE
2047: and ROLE_ID IS NOT NULL
2057: cursor l_c_cursor is
2058: select
2059: '
2060: (select CURRENCY_CODE
2061: from AP_POL_SCHEDULE_OPTIONS pso
2062: where
2063: POLICY_ID = :p_policy_id
2064: and OPTION_TYPE = :c_CURRENCY
2065: and CURRENCY_CODE IS NOT NULL
2075: cursor l_vc_cursor is
2076: select
2077: '
2078: (select OPTION_CODE, VEHICLE_TYPE_CODE, FUEL_TYPE_CODE
2079: from AP_POL_SCHEDULE_OPTIONS pso
2080: where
2081: POLICY_ID = :p_policy_id
2082: and OPTION_TYPE = :c_VEHICLE_CATEGORY
2083: and OPTION_CODE IS NOT NULL
2093: cursor l_vt_cursor is
2094: select
2095: '
2096: (select OPTION_CODE
2097: from AP_POL_SCHEDULE_OPTIONS pso
2098: where
2099: POLICY_ID = :p_policy_id
2100: and OPTION_TYPE = :c_VEHICLE_TYPE
2101: and OPTION_CODE IS NOT NULL
2111: cursor l_ft_cursor is
2112: select
2113: '
2114: (select OPTION_CODE
2115: from AP_POL_SCHEDULE_OPTIONS pso
2116: where
2117: POLICY_ID = :p_policy_id
2118: and OPTION_TYPE = :c_FUEL_TYPE
2119: and OPTION_CODE IS NOT NULL
2129: cursor l_dt_cursor is
2130: select
2131: '
2132: (select THRESHOLD
2133: from AP_POL_SCHEDULE_OPTIONS pso
2134: where
2135: POLICY_ID = :p_policy_id
2136: and (OPTION_TYPE = :c_DISTANCE_THRESHOLD or OPTION_TYPE = :c_TIME_THRESHOLD)
2137: and THRESHOLD IS NOT NULL
2235: -- ----------------------------------------------------------
2236: IF ( l_category_code = 'PER_DIEM' ) THEN
2237: BEGIN
2238: -- Delete zero threshold rows where not needed
2239: delete from ap_pol_schedule_options
2240: where policy_id = p_policy_id
2241: and option_type = 'TIME_THRESHOLD'
2242: and threshold = 0
2243: and rate_type_code not in
2241: and option_type = 'TIME_THRESHOLD'
2242: and threshold = 0
2243: and rate_type_code not in
2244: ( select distinct rate_type_code
2245: from ap_pol_schedule_options
2246: where policy_id = p_policy_id
2247: and rate_type_code in ('FIRST_PERIOD', 'LAST_PERIOD', 'SAME_DAY')
2248: and option_type = 'TIME_THRESHOLD'
2249: and threshold > 0 )
2252: FOR rate_type_cur in c_rate_types
2253: LOOP
2254: select count(1)
2255: into l_zero_threshold_count
2256: from ap_pol_schedule_options
2257: where policy_id = p_policy_id
2258: and rate_type_code = rate_type_cur.rate_type_code
2259: and threshold = 0;
2260:
2259: and threshold = 0;
2260:
2261: IF ( l_zero_threshold_count = 0 ) THEN
2262: BEGIN
2263: SELECT ap_pol_schedule_options_s.NEXTVAL
2264: INTO l_schedule_option_rec.schedule_option_id
2265: FROM DUAL;
2266:
2267: INSERT INTO ap_pol_schedule_options
2263: SELECT ap_pol_schedule_options_s.NEXTVAL
2264: INTO l_schedule_option_rec.schedule_option_id
2265: FROM DUAL;
2266:
2267: INSERT INTO ap_pol_schedule_options
2268: (
2269: policy_id,
2270: schedule_option_id,
2271: option_type,
2301: l_schedule_option_rec.last_update_date := sysdate;
2302: l_schedule_option_rec.last_updated_by := p_user_id;
2303: l_schedule_option_rec.rate_type_code := rate_type_cur.rate_type_code;
2304:
2305: INSERT INTO ap_pol_schedule_options values l_schedule_option_rec;
2306: ----------------------------------------------------------------------------------*/
2307: END;
2308: END IF;
2309: END LOOP;
2324: IF ( l_zero_threshold_count = 1 ) THEN
2325: BEGIN
2326: select count(1)
2327: into l_zero_threshold_count
2328: from ap_pol_schedule_options
2329: where policy_id = p_policy_id
2330: and rate_type_code = 'STANDARD'
2331: and threshold = 0;
2332:
2331: and threshold = 0;
2332:
2333: IF ( l_zero_threshold_count = 0 ) THEN
2334: BEGIN
2335: SELECT ap_pol_schedule_options_s.NEXTVAL
2336: INTO l_schedule_option_rec.schedule_option_id
2337: FROM DUAL;
2338:
2339: INSERT INTO ap_pol_schedule_options
2335: SELECT ap_pol_schedule_options_s.NEXTVAL
2336: INTO l_schedule_option_rec.schedule_option_id
2337: FROM DUAL;
2338:
2339: INSERT INTO ap_pol_schedule_options
2340: (
2341: policy_id,
2342: schedule_option_id,
2343: option_type,
2373: l_schedule_option_rec.last_update_date := sysdate;
2374: l_schedule_option_rec.last_updated_by := p_user_id;
2375: l_schedule_option_rec.rate_type_code := 'STANDARD';
2376:
2377: INSERT INTO ap_pol_schedule_options values l_schedule_option_rec;
2378: ---------------------------------------------------------------------------------*/
2379:
2380: END;
2381: END IF;
2385: -- ---------------------------------------------------------------------
2386: -- This means that we should not have a zero row for standard rate type
2387: -- So delete any such rows
2388: -- ---------------------------------------------------------------------
2389: delete from ap_pol_schedule_options
2390: where policy_id = p_policy_id
2391: and rate_type_code = 'STANDARD'
2392: and threshold = 0;
2393:
2628: where pl.POLICY_ID = :p_policy_id
2629: and ((pl.LOCATION_ID is not null
2630: and not exists
2631: (select pso.LOCATION_ID
2632: from AP_POL_SCHEDULE_OPTIONS pso
2633: where pso.POLICY_ID = pl.POLICY_ID
2634: and pso.OPTION_TYPE = :c_LOCATION
2635: and pso.LOCATION_ID is not null
2636: and pso.LOCATION_ID = pl.LOCATION_ID
2639: or
2640: (pl.LOCATION_ID is null
2641: and exists
2642: (select pso.LOCATION_ID
2643: from AP_POL_SCHEDULE_OPTIONS pso
2644: where pso.POLICY_ID = pl.POLICY_ID
2645: and pso.OPTION_TYPE = :c_LOCATION
2646: and pso.LOCATION_ID is not null
2647: )
2655: or
2656: (pl.ROLE_ID is not null
2657: and not exists
2658: (select pso.ROLE_ID
2659: from AP_POL_SCHEDULE_OPTIONS pso
2660: where pso.POLICY_ID = pl.POLICY_ID
2661: and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
2662: and pso.ROLE_ID is not null
2663: and pso.ROLE_ID = pl.ROLE_ID
2666: or
2667: (pl.ROLE_ID is null
2668: and exists
2669: (select pso.ROLE_ID
2670: from AP_POL_SCHEDULE_OPTIONS pso
2671: where pso.POLICY_ID = pl.POLICY_ID
2672: and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
2673: and pso.ROLE_ID is not null
2674: )
2683: (pl.CURRENCY_CODE is not null
2684: and
2685: (not exists
2686: (select pso.CURRENCY_CODE
2687: from AP_POL_SCHEDULE_OPTIONS pso
2688: where pso.POLICY_ID = pl.POLICY_ID
2689: and pso.OPTION_TYPE = :c_CURRENCY
2690: and pso.CURRENCY_CODE is not null
2691: and pso.CURRENCY_CODE = pl.CURRENCY_CODE
2728: or
2729: (pl.VEHICLE_CATEGORY is not null
2730: and not exists
2731: (select pso.OPTION_CODE
2732: from AP_POL_SCHEDULE_OPTIONS pso
2733: where pso.POLICY_ID = pl.POLICY_ID
2734: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2735: and pso.OPTION_CODE is not null
2736: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2739: or
2740: (pl.VEHICLE_CATEGORY is null
2741: and exists
2742: (select pso.OPTION_CODE
2743: from AP_POL_SCHEDULE_OPTIONS pso
2744: where pso.POLICY_ID = pl.POLICY_ID
2745: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2746: and pso.OPTION_CODE is not null
2747: )
2756: (pl.VEHICLE_TYPE is not null
2757: and
2758: (not exists
2759: (select pso.OPTION_CODE
2760: from AP_POL_SCHEDULE_OPTIONS pso
2761: where pso.POLICY_ID = pl.POLICY_ID
2762: and pso.OPTION_TYPE = :c_VEHICLE_TYPE
2763: and pso.OPTION_CODE is not null
2764: and pso.OPTION_CODE = pl.VEHICLE_TYPE
2764: and pso.OPTION_CODE = pl.VEHICLE_TYPE
2765: )
2766: or exists
2767: (select pso.OPTION_CODE
2768: from AP_POL_SCHEDULE_OPTIONS pso
2769: where pso.POLICY_ID = pl.POLICY_ID
2770: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2771: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2772: and pso.VEHICLE_TYPE_CODE <> ''R''
2775: or
2776: (pl.VEHICLE_TYPE is null
2777: and exists
2778: (select pso.OPTION_CODE
2779: from AP_POL_SCHEDULE_OPTIONS pso
2780: where pso.POLICY_ID = pl.POLICY_ID
2781: and pso.OPTION_TYPE = :c_VEHICLE_TYPE
2782: and pso.OPTION_CODE is not null
2783: )
2782: and pso.OPTION_CODE is not null
2783: )
2784: and not exists
2785: (select pso.OPTION_CODE
2786: from AP_POL_SCHEDULE_OPTIONS pso
2787: where pso.POLICY_ID = pl.POLICY_ID
2788: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2789: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2790: and pso.VEHICLE_TYPE_CODE <> ''R''
2800: (pl.FUEL_TYPE is not null
2801: and
2802: (not exists
2803: (select pso.OPTION_CODE
2804: from AP_POL_SCHEDULE_OPTIONS pso
2805: where pso.POLICY_ID = pl.POLICY_ID
2806: and pso.OPTION_TYPE = :c_FUEL_TYPE
2807: and pso.OPTION_CODE is not null
2808: and pso.OPTION_CODE = pl.FUEL_TYPE
2808: and pso.OPTION_CODE = pl.FUEL_TYPE
2809: )
2810: or exists
2811: (select pso.OPTION_CODE
2812: from AP_POL_SCHEDULE_OPTIONS pso
2813: where pso.POLICY_ID = pl.POLICY_ID
2814: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2815: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2816: and pso.FUEL_TYPE_CODE <> ''R''
2819: or
2820: (pl.FUEL_TYPE is null
2821: and exists
2822: (select pso.OPTION_CODE
2823: from AP_POL_SCHEDULE_OPTIONS pso
2824: where pso.POLICY_ID = pl.POLICY_ID
2825: and pso.OPTION_TYPE = :c_FUEL_TYPE
2826: and pso.OPTION_CODE is not null
2827: )
2826: and pso.OPTION_CODE is not null
2827: )
2828: and not exists
2829: (select pso.OPTION_CODE
2830: from AP_POL_SCHEDULE_OPTIONS pso
2831: where pso.POLICY_ID = pl.POLICY_ID
2832: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2833: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2834: and pso.FUEL_TYPE_CODE <> ''R''
2843: or
2844: (pl.RANGE_LOW is not null
2845: and not exists
2846: (select pso.THRESHOLD
2847: from AP_POL_SCHEDULE_OPTIONS pso
2848: where pso.POLICY_ID = pl.POLICY_ID
2849: and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
2850: and pso.THRESHOLD is not null
2851: and pso.THRESHOLD = pl.RANGE_LOW
2855: or
2856: (pl.RANGE_HIGH is not null
2857: and not exists
2858: (select pso.THRESHOLD
2859: from AP_POL_SCHEDULE_OPTIONS pso
2860: where pso.POLICY_ID = pl.POLICY_ID
2861: and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
2862: and pso.THRESHOLD is not null
2863: and pso.THRESHOLD = pl.RANGE_HIGH
2867: or
2868: (pl.RANGE_LOW is null
2869: and exists
2870: (select pso.THRESHOLD
2871: from AP_POL_SCHEDULE_OPTIONS pso
2872: where pso.POLICY_ID = pl.POLICY_ID
2873: and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
2874: and pso.THRESHOLD is not null
2875: and nvl(pso.rate_type_code, ''NULL'') = nvl(pl.rate_type_code, ''NULL'')
2897: ' or
2898: (pl.ADDON_MILEAGE_RATE_CODE is not null
2899: and not exists
2900: (select pso.OPTION_CODE
2901: from AP_POL_SCHEDULE_OPTIONS pso
2902: where pso.POLICY_ID = pl.POLICY_ID
2903: and pso.OPTION_TYPE = :c_ADDON_RATES
2904: and pso.OPTION_CODE is not null
2905: and pso.OPTION_CODE = pl.ADDON_MILEAGE_RATE_CODE
3034: and pl.STATUS = :c_ACTIVE
3035: and ((pl.LOCATION_ID is not null
3036: and exists
3037: (select pso.LOCATION_ID
3038: from AP_POL_SCHEDULE_OPTIONS pso
3039: where pso.POLICY_ID = pl.POLICY_ID
3040: and pso.OPTION_TYPE = :c_LOCATION
3041: and pso.LOCATION_ID is not null
3042: and pso.LOCATION_ID = pl.LOCATION_ID
3046: or
3047: (pl.ROLE_ID is not null
3048: and exists
3049: (select pso.ROLE_ID
3050: from AP_POL_SCHEDULE_OPTIONS pso
3051: where pso.POLICY_ID = pl.POLICY_ID
3052: and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
3053: and pso.ROLE_ID is not null
3054: and pso.ROLE_ID = pl.ROLE_ID
3058: or
3059: (pl.CURRENCY_CODE is not null
3060: and exists
3061: (select pso.CURRENCY_CODE
3062: from AP_POL_SCHEDULE_OPTIONS pso
3063: where pso.POLICY_ID = pl.POLICY_ID
3064: and pso.OPTION_TYPE = :c_CURRENCY
3065: and pso.CURRENCY_CODE is not null
3066: and pso.CURRENCY_CODE = pl.CURRENCY_CODE
3070: or
3071: (pl.VEHICLE_CATEGORY is not null
3072: and exists
3073: (select pso.OPTION_CODE
3074: from AP_POL_SCHEDULE_OPTIONS pso
3075: where pso.POLICY_ID = pl.POLICY_ID
3076: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
3077: and pso.OPTION_CODE is not null
3078: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
3082: or
3083: (pl.VEHICLE_TYPE is not null
3084: and exists
3085: (select pso.OPTION_CODE
3086: from AP_POL_SCHEDULE_OPTIONS pso
3087: where pso.POLICY_ID = pl.POLICY_ID
3088: and pso.OPTION_TYPE = :c_VEHICLE_TYPE
3089: and pso.OPTION_CODE is not null
3090: and pso.OPTION_CODE = pl.VEHICLE_TYPE
3094: or
3095: (pl.FUEL_TYPE is not null
3096: and exists
3097: (select pso.OPTION_CODE
3098: from AP_POL_SCHEDULE_OPTIONS pso
3099: where pso.POLICY_ID = pl.POLICY_ID
3100: and pso.OPTION_TYPE = :c_FUEL_TYPE
3101: and pso.OPTION_CODE is not null
3102: and pso.OPTION_CODE = pl.FUEL_TYPE
3106: or
3107: (pl.RANGE_LOW is not null
3108: and exists
3109: (select pso.THRESHOLD
3110: from AP_POL_SCHEDULE_OPTIONS pso
3111: where pso.POLICY_ID = pl.POLICY_ID
3112: and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
3113: and pso.THRESHOLD is not null
3114: and pso.THRESHOLD = pl.RANGE_LOW
3129: and pl.STATUS = :c_INACTIVE
3130: and (((pl.LOCATION_ID is not null
3131: and exists
3132: (select pso.LOCATION_ID
3133: from AP_POL_SCHEDULE_OPTIONS pso
3134: where pso.POLICY_ID = pl.POLICY_ID
3135: and pso.OPTION_TYPE = :c_LOCATION
3136: and pso.LOCATION_ID is not null
3137: and pso.LOCATION_ID = pl.LOCATION_ID
3142: and
3143: ((pl.ROLE_ID is not null
3144: and exists
3145: (select pso.ROLE_ID
3146: from AP_POL_SCHEDULE_OPTIONS pso
3147: where pso.POLICY_ID = pl.POLICY_ID
3148: and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
3149: and pso.ROLE_ID is not null
3150: and pso.ROLE_ID = pl.ROLE_ID
3156: ((pl.CURRENCY_CODE is not null
3157: and
3158: (exists
3159: (select pso.CURRENCY_CODE
3160: from AP_POL_SCHEDULE_OPTIONS pso
3161: where pso.POLICY_ID = pl.POLICY_ID
3162: and pso.OPTION_TYPE = :c_CURRENCY
3163: and pso.CURRENCY_CODE is not null
3164: and pso.CURRENCY_CODE = pl.CURRENCY_CODE
3178: and
3179: ((pl.VEHICLE_CATEGORY is not null
3180: and exists
3181: (select pso.OPTION_CODE
3182: from AP_POL_SCHEDULE_OPTIONS pso
3183: where pso.POLICY_ID = pl.POLICY_ID
3184: and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
3185: and pso.OPTION_CODE is not null
3186: and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
3191: and
3192: ((pl.VEHICLE_TYPE is not null
3193: and exists
3194: (select pso.OPTION_CODE
3195: from AP_POL_SCHEDULE_OPTIONS pso
3196: where pso.POLICY_ID = pl.POLICY_ID
3197: and pso.OPTION_TYPE = :c_VEHICLE_TYPE
3198: and pso.OPTION_CODE is not null
3199: and pso.OPTION_CODE = pl.VEHICLE_TYPE
3204: and
3205: ((pl.FUEL_TYPE is not null
3206: and exists
3207: (select pso.OPTION_CODE
3208: from AP_POL_SCHEDULE_OPTIONS pso
3209: where pso.POLICY_ID = pl.POLICY_ID
3210: and pso.OPTION_TYPE = :c_FUEL_TYPE
3211: and pso.OPTION_CODE is not null
3212: and pso.OPTION_CODE = pl.FUEL_TYPE
3217: and
3218: ((pl.RANGE_LOW is not null
3219: and exists
3220: (select pso.THRESHOLD
3221: from AP_POL_SCHEDULE_OPTIONS pso
3222: where pso.POLICY_ID = pl.POLICY_ID
3223: and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
3224: and pso.THRESHOLD is not null
3225: and pso.THRESHOLD = pl.RANGE_LOW
4144: p_to_policy_id IN ap_pol_headers.policy_id%TYPE) IS
4145:
4146: BEGIN
4147:
4148: insert into AP_POL_SCHEDULE_OPTIONS
4149: (
4150: SCHEDULE_OPTION_ID,
4151: POLICY_ID,
4152: OPTION_TYPE,
4166: LAST_UPDATE_DATE,
4167: LAST_UPDATED_BY
4168: )
4169: select
4170: AP_POL_SCHEDULE_OPTIONS_S.NEXTVAL AS SCHEDULE_OPTION_ID,
4171: p_to_policy_id AS POLICY_ID,
4172: OPTION_TYPE,
4173: OPTION_CODE,
4174: THRESHOLD,
4185: null AS LAST_UPDATE_LOGIN,
4186: sysdate AS LAST_UPDATE_DATE,
4187: p_user_id AS LAST_UPDATED_BY
4188: from
4189: AP_POL_SCHEDULE_OPTIONS
4190: where POLICY_ID = p_from_policy_id;
4191:
4192: EXCEPTION
4193: WHEN OTHERS THEN
4296: p_currency_code IN VARCHAR2,
4297: p_end_date IN DATE) RETURN VARCHAR2 IS
4298: CURSOR active_cur IS
4299: select schedule_option_id
4300: from ap_pol_schedule_options
4301: where option_type = p_option_type
4302: and NVL(option_code,9.99E125) = NVL(p_option_code,9.99E125)
4303: and NVL(threshold,9.99E125) = NVL(p_threshold,9.99E125)
4304: and NVL(role_id,9.99E125) = NVL(p_role_id,9.99E125)
4362:
4363: IF ( l_end_date is not null )
4364: THEN
4365:
4366: update ap_pol_schedule_options
4367: set end_date = l_end_date
4368: where option_type = 'LOCATION'
4369: and location_id = l_location_id
4370: and NVL(end_date,TO_DATE('1','j')) >=
4451: *=======================================================================*/
4452: PROCEDURE status_saved_sched_opts(p_policy_id IN NUMBER) IS
4453: BEGIN
4454: IF p_policy_id IS NOT NULL THEN
4455: update AP_POL_SCHEDULE_OPTIONS set STATUS = 'SAVED' where POLICY_ID = p_policy_id and nvl(STATUS, '~') <> 'ACTIVE';
4456: END IF;
4457: END status_saved_sched_opts;
4458:
4459: /*========================================================================
4505: p_status_code IN VARCHAR2) IS
4506: BEGIN
4507: IF p_policy_id IS NOT NULL AND
4508: p_status_code IS NOT NULL THEN
4509: update AP_POL_SCHEDULE_OPTIONS set STATUS = p_status_code where POLICY_ID = p_policy_id;
4510: END IF;
4511: END set_status_pol_sched_opts;
4512:
4513: /*========================================================================
4775: PROCEDURE deletePolicySchedule(p_policy_id IN NUMBER) IS
4776:
4777: BEGIN
4778:
4779: DELETE FROM AP_POL_SCHEDULE_OPTIONS WHERE POLICY_ID = p_policy_id;
4780:
4781: DELETE FROM AP_POL_SCHEDULE_PERIODS WHERE POLICY_ID = p_policy_id;
4782:
4783: DELETE FROM AP_POL_LINES WHERE POLICY_ID = p_policy_id;
4928: |
4929: *=======================================================================*/
4930: PROCEDURE permutatePolicyLines(p_user_id IN NUMBER,
4931: p_policy_id IN ap_pol_headers.policy_id%TYPE,
4932: p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) IS
4933:
4934: l_schedule_period_id ap_pol_schedule_periods.schedule_period_id%TYPE;
4935: l_permutate_curref INTEGER;
4936: l_rows_permutated NUMBER := 0;
5024: cursor l_l_cursor is
5025: select
5026: '
5027: (select LOCATION_ID
5028: from AP_POL_SCHEDULE_OPTIONS pso
5029: where
5030: POLICY_ID = :p_policy_id
5031: and OPTION_TYPE = :c_LOCATION
5032: and LOCATION_ID IS NOT NULL
5042: cursor l_r_cursor is
5043: select
5044: '
5045: (select ROLE_ID
5046: from AP_POL_SCHEDULE_OPTIONS pso
5047: where
5048: POLICY_ID = :p_policy_id
5049: and OPTION_TYPE = :c_EMPLOYEE_ROLE
5050: and ROLE_ID IS NOT NULL
5060: cursor l_c_cursor is
5061: select
5062: '
5063: (select CURRENCY_CODE
5064: from AP_POL_SCHEDULE_OPTIONS pso
5065: where
5066: POLICY_ID = :p_policy_id
5067: and OPTION_TYPE = :c_CURRENCY
5068: and CURRENCY_CODE IS NOT NULL
5078: cursor l_dt_cursor is
5079: select
5080: '
5081: (select THRESHOLD
5082: from AP_POL_SCHEDULE_OPTIONS pso
5083: where
5084: POLICY_ID = :p_policy_id
5085: and (OPTION_TYPE = :c_DISTANCE_THRESHOLD or OPTION_TYPE = :c_TIME_THRESHOLD)
5086: and THRESHOLD IS NOT NULL
5354: apl.CREATED_BY,
5355: sysdate AS LAST_UPDATE_DATE,
5356: apl.LAST_UPDATED_BY
5357: from ap_pol_lines apl,
5358: ap_pol_schedule_options pso
5359: where apl.POLICY_ID = p_policy_id
5360: and pso.policy_id = apl.policy_id
5361: and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'
5362: and OPTION_CODE IS NOT NULL
5372: and addon_mileage_rate_code is not null
5373: and not exists
5374: (
5375: select 1
5376: from ap_pol_schedule_options pso
5377: where pso.policy_id = pl.policy_id
5378: and pso.option_code = pl.addon_mileage_rate_code
5379: );
5380: -- ---------------------------------------------------------
5419: apl.CREATED_BY,
5420: sysdate AS LAST_UPDATE_DATE,
5421: apl.LAST_UPDATED_BY
5422: from ap_pol_lines apl,
5423: ap_pol_schedule_options pso
5424: where apl.POLICY_ID = p_policy_id
5425: and pso.policy_id = apl.policy_id
5426: and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'
5427: and OPTION_CODE IS NOT NULL
5492: apl.currency_code,
5493: option_code as night_rate_type_code,
5494: 'NEW' AS STATUS
5495: from ap_pol_lines apl,
5496: ap_pol_schedule_options pso
5497: where apl.POLICY_ID = p_policy_id
5498: and pso.policy_id(+) = apl.policy_id
5499: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5500: and OPTION_CODE(+) IS NOT NULL
5514: apl.currency_code,
5515: option_code as night_rate_type_code,
5516: 'NEW' AS STATUS
5517: from ap_pol_lines apl,
5518: ap_pol_schedule_options pso,
5519: ap_pol_lines epl
5520: where apl.POLICY_ID = p_policy_id
5521: and pso.policy_id(+) = apl.policy_id
5522: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5634: where policy_id = p_policy_id
5635: and rate_type_code = 'NIGHT_RATE'
5636: and (night_rate_type_code is NULL
5637: or night_rate_type_code not in(select option_code
5638: from ap_pol_schedule_options
5639: where policy_id = p_policy_id));
5640: END IF;
5641:
5642: -- ---------------------------------------------------------
5756: where policy_id = p_policy_id;
5757:
5758: select count(1)
5759: into l_first_period_count
5760: from ap_pol_schedule_options
5761: where policy_id = p_policy_id
5762: and rate_type_code= 'FIRST_PERIOD';
5763:
5764: if (l_rate_period_type_code = 'STANDARD_FIRST_LAST' and l_first_period_count > 0)
5807: where policy_id = p_policy_id;
5808:
5809: select count(1)
5810: into l_last_period_count
5811: from ap_pol_schedule_options
5812: where policy_id = p_policy_id
5813: and rate_type_code= 'LAST_PERIOD';
5814:
5815: if (l_rate_period_type_code = 'STANDARD_FIRST_LAST' and l_last_period_count > 0)
5857: where policy_id = p_policy_id;
5858:
5859: select count(1)
5860: into l_same_day_count
5861: from ap_pol_schedule_options
5862: where policy_id = p_policy_id
5863: and rate_type_code= 'SAME_DAY';
5864:
5865: if (l_same_day_rate_code = 'DEFINED' and l_same_day_count > 0)
5914: WHERE pl.policy_id = p_policy_id
5915: AND NVL(role_id, -1) <> -1
5916: AND NOT EXISTS
5917: ( SELECT 1
5918: FROM ap_pol_schedule_options pso
5919: WHERE pso.policy_id = pl.policy_id
5920: AND pso.option_type = 'EMPLOYEE_ROLE'
5921: AND pso.option_code = pl.role_id
5922: );
6011: p_user_id LAST_UPDATE_LOGIN,
6012: sysdate as LAST_UPDATE_DATE,
6013: p_user_id as LAST_UPDATED_BY
6014: FROM ap_pol_lines apl,
6015: ap_pol_schedule_options pso
6016: WHERE apl.policy_id = pso.policy_id
6017: AND apl.role_id = -1
6018: AND pso.option_type = 'EMPLOYEE_ROLE'
6019: AND pso.role_id IS NOT NULL
6133: FUNCTION getPolicyLocationId( p_expense_type_id IN NUMBER,
6134: p_expense_date IN DATE,
6135: p_location_id IN NUMBER ) RETURN NUMBER IS
6136:
6137: l_location_id AP_POL_SCHEDULE_OPTIONS.location_id%type;
6138:
6139: BEGIN
6140: SELECT LOCATION_ID
6141: INTO l_location_id
6141: INTO l_location_id
6142: FROM (
6143: -- This query verifies that a given location is active within a policy
6144: select location_id
6145: from AP_POL_SCHEDULE_OPTIONS opts, ap_expense_report_params_all p
6146: where p.parameter_id = p_expense_type_id
6147: AND policy_id = p.company_policy_id
6148: AND option_type = 'LOCATION'
6149: AND status = 'ACTIVE'
6150: AND (opts.end_date is null OR opts.end_date >= p_expense_date)
6151: AND LOCATION_ID = p_location_id
6152: UNION ALL
6153: select opts.location_id
6154: from AP_POL_SCHEDULE_OPTIONS opts, AP_POL_LOCATIONS_B loc1, AP_POL_LOCATIONS_B loc2, ap_expense_report_params_all p
6155: where p.parameter_id = p_expense_type_id
6156: AND policy_id = p.company_policy_id
6157: AND opts.option_type = 'LOCATION'
6158: AND opts.status = 'ACTIVE'
6164: AND loc2.location_id = p_location_id
6165: UNION ALL
6166: -- Will find the all other location for a given policy
6167: select loc.location_id
6168: from AP_POL_SCHEDULE_OPTIONS opts, AP_POL_LOCATIONS_B loc, ap_expense_report_params_all p
6169: where p.parameter_id = p_expense_type_id
6170: AND opts.policy_id = p.company_policy_id
6171: AND opts.option_type = 'LOCATION'
6172: AND opts.status = 'ACTIVE'
6236: AND p_expense_date between sp.start_date and nvl(sp.end_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'))
6237: AND sys.org_id = rate_opts.org_id(+)
6238: AND (nvl(h.employee_role_flag, 'N') = 'N' OR
6239: l.role_id = nvl((select ROLE_ID
6240: from AP_POL_SCHEDULE_OPTIONS
6241: where policy_id = h.policy_id
6242: AND option_type = 'EMPLOYEE_ROLE'
6243: AND status = 'ACTIVE'
6244: AND (end_date is null OR end_date >= p_expense_date)