[Home] [Help]
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:
1275: END getHighEndOfThreshold;
1276:
1277: /*========================================================================
1285: |
1286: | PARAMETERS
1287: | p_policy_id IN Policy Identifier
1288: | p_threshold IN Threshold
1289: | p_rate_type IN Rate Type (STANDARD, FIRST, LAST)
1290: |
1291: | MODIFICATION HISTORY
1292: | Date Author Description of Changes
1293: | 01-Nov-2005 krmenon Created
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;
1309:
1310: BEGIN
1311:
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 )
2250: and rate_type_code <> 'STANDARD';
2251:
2252: FOR rate_type_cur in c_rate_types
2253: LOOP
2254: select count(1)
2307: END;
2308: END IF;
2309: END LOOP;
2310:
2311: -- Standard rate is a special case. Should not insert 0 record
2312: -- for midnight to midnight schedules which have first and last rates
2313: -- or for allowance schedules with time rule of start and end times
2314: select count(1)
2315: into l_zero_threshold_count
2316: from ap_pol_headers
2317: where policy_id = p_policy_id
2318: and time_based_entry_flag = 'Y'
2319: and ( day_period_code <> 'MIDNIGHT' or
2320: (nvl(rate_period_type_code, 'STANDARD') = 'STANDARD' and schedule_type_code = 'PER_DIEM') or
2321: ( schedule_type_code = 'ALLOWANCE' and allowance_time_rule_code = 'TIME_THRESHOLD' )
2322: );
2323:
2324: IF ( l_zero_threshold_count = 1 ) THEN
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:
2333: IF ( l_zero_threshold_count = 0 ) THEN
2334: BEGIN
2359: sysdate,
2360: p_user_id,
2361: sysdate,
2362: p_user_id,
2363: 'STANDARD'
2364: );
2365:
2366: /*--------- COMMENTING THIS CODE SINCE GSCC NOT FIXED FOR REC TYPE --------------
2367: l_schedule_option_rec.policy_id := p_policy_id;
2371: l_schedule_option_rec.creation_date := sysdate;
2372: l_schedule_option_rec.created_by := p_user_id;
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:
2382:
2383: END;
2384: ELSE
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
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:
2394: END IF;
2395:
2392: and threshold = 0;
2393:
2394: END IF;
2395:
2396: l_rate_type_code := 'STANDARD';
2397: END;
2398: ELSE
2399: l_rate_type_code := null;
2400: END IF;
2880: and
2881: nvl(pl.RANGE_HIGH, :dummy_number) <>
2882: nvl(AP_WEB_POLICY_UTILS.getHighEndOfThreshold(pl.POLICY_ID,
2883: pl.RANGE_LOW,
2884: nvl(pl.rate_type_code,''STANDARD'')), :dummy_number)
2885: )
2886: '
2887: from sys.dual; /* l_dt_cursor */
2888:
5498: and pso.policy_id(+) = apl.policy_id
5499: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5500: and OPTION_CODE(+) IS NOT NULL
5501: and nvl(END_DATE(+), SYSDATE+1) > SYSDATE
5502: and nvl(apl.rate_type_code, 'STANDARD') = 'STANDARD';
5503:
5504: -- -------------------------------------
5505: -- Cursor for updating permutations
5506: -- -------------------------------------
5521: and pso.policy_id(+) = apl.policy_id
5522: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5523: and OPTION_CODE(+) IS NOT NULL
5524: and nvl(END_DATE(+), SYSDATE+1) > SYSDATE
5525: and nvl(apl.rate_type_code, 'STANDARD') = 'STANDARD'
5526: and not exists
5527: ( select 1
5528: from ap_pol_lines epl
5529: where epl.POLICY_ID = apl.policy_id
5749: l_rate_period_type_code ap_pol_headers.night_rates_code%TYPE;
5750: l_first_period_count number := 0;
5751: BEGIN
5752:
5753: select nvl(rate_period_type_code, 'STANDARD')
5754: into l_rate_period_type_code
5755: from ap_pol_headers
5756: where policy_id = p_policy_id;
5757:
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)
5765: then
5766: return 'Y';
5767: else
5768: return 'N';
5800: l_rate_period_type_code ap_pol_headers.night_rates_code%TYPE;
5801: l_last_period_count number := 0;
5802: BEGIN
5803:
5804: select nvl(rate_period_type_code, 'STANDARD')
5805: into l_rate_period_type_code
5806: from ap_pol_headers
5807: where policy_id = p_policy_id;
5808:
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)
5816: then
5817: return 'Y';
5818: else
5819: return 'N';
6283: |
6284: | PARAMETERS
6285: | p_policy_id IN Policy Id
6286: | p_schedule_id IN Policy Schedule Id.
6287: | p_rate_type IN Rate Type (STANDARD, SAME_DAY, FIRST_PERIOD,
6288: | LAST_PERIOD, NIGHT_RATE, ADDON)
6289: | p_std_invalid OUT Count of invalid standard lines
6290: | p_first_invalid OUT Count of invalid first period lines
6291: | p_last_invalid OUT Count of invalid last period lines
6285: | p_policy_id IN Policy Id
6286: | p_schedule_id IN Policy Schedule Id.
6287: | p_rate_type IN Rate Type (STANDARD, SAME_DAY, FIRST_PERIOD,
6288: | LAST_PERIOD, NIGHT_RATE, ADDON)
6289: | p_std_invalid OUT Count of invalid standard lines
6290: | p_first_invalid OUT Count of invalid first period lines
6291: | p_last_invalid OUT Count of invalid last period lines
6292: | p_same_invalid OUT Count of invalid same day rate lines
6293: | p_night_invalid OUT Count of invalid night rate lines
6309: p_addon_invalid OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
6310:
6311: -- Cursor to check for invalid lines based on status
6312: cursor c_invalid_policy_lines is
6313: select nvl(rate_type_code, 'STANDARD') rate_type_code, count(1) as number_of_lines
6314: from ap_pol_lines
6315: where policy_id = p_policy_id
6316: and schedule_period_id = p_schedule_id
6317: and (status <> 'VALID' and status <> 'ACTIVE')
6345:
6346: -- Loop through the cursor
6347: FOR invalidLines in c_invalid_policy_lines
6348: LOOP
6349: IF ( invalidLines.rate_type_code = 'STANDARD' ) THEN
6350: p_std_invalid := invalidLines.number_of_lines;
6351: ELSIF ( invalidLines.rate_type_code = 'FIRST_PERIOD' ) THEN
6352: p_first_invalid := invalidLines.number_of_lines;
6353: ELSIF ( invalidLines.rate_type_code = 'LAST_PERIOD' ) THEN