[Home] [Help]
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:
1300: END getHighEndOfThreshold;
1301:
1302: /*========================================================================
1310: |
1311: | PARAMETERS
1312: | p_policy_id IN Policy Identifier
1313: | p_threshold IN Threshold
1314: | p_rate_type IN Rate Type (STANDARD, FIRST, LAST)
1315: |
1316: | MODIFICATION HISTORY
1317: | Date Author Description of Changes
1318: | 01-Nov-2005 krmenon Created
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
1332: ORDER BY threshold;
1333:
1334: previous_threshold NUMBER;
1335: counter NUMBER := 0;
2188: cursor l_dt_cursor is
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)
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 )
2299: and rate_type_code <> 'STANDARD';
2300:
2301: FOR rate_type_cur in c_rate_types
2302: LOOP
2303: select count(1)
2356: END;
2357: END IF;
2358: END LOOP;
2359:
2360: -- Standard rate is a special case. Should not insert 0 record
2361: -- for midnight to midnight schedules which have first and last rates
2362: -- or for allowance schedules with time rule of start and end times
2363: select count(1)
2364: into l_zero_threshold_count
2365: from ap_pol_headers
2366: where policy_id = p_policy_id
2367: and time_based_entry_flag = 'Y'
2368: and ( day_period_code <> 'MIDNIGHT' or
2369: (nvl(rate_period_type_code, 'STANDARD') = 'STANDARD' and schedule_type_code = 'PER_DIEM') or
2370: ( schedule_type_code = 'ALLOWANCE' and allowance_time_rule_code = 'TIME_THRESHOLD' )
2371: );
2372:
2373: IF ( l_zero_threshold_count = 1 ) THEN
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:
2382: IF ( l_zero_threshold_count = 0 ) THEN
2383: BEGIN
2408: sysdate,
2409: p_user_id,
2410: sysdate,
2411: p_user_id,
2412: 'STANDARD'
2413: );
2414:
2415: /*--------- COMMENTING THIS CODE SINCE GSCC NOT FIXED FOR REC TYPE --------------
2416: l_schedule_option_rec.policy_id := p_policy_id;
2420: l_schedule_option_rec.creation_date := sysdate;
2421: l_schedule_option_rec.created_by := p_user_id;
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:
2431:
2432: END;
2433: ELSE
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
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:
2443: END IF;
2444:
2441: and threshold = 0;
2442:
2443: END IF;
2444:
2445: l_rate_type_code := 'STANDARD';
2446: END;
2447: ELSE
2448: l_rate_type_code := null;
2449: END IF;
2928: and
2929: nvl(pl.RANGE_HIGH, :dummy_number) <>
2930: nvl(AP_WEB_POLICY_UTILS.getHighEndOfThreshold(pl.POLICY_ID,
2931: pl.RANGE_LOW,
2932: nvl(pl.rate_type_code,''STANDARD'')), :dummy_number)
2933: )
2934: '
2935: from sys.dual; /* l_dt_cursor */
2936:
5149: cursor l_dt_cursor is
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)
5563: and pso.policy_id(+) = apl.policy_id
5564: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5565: and OPTION_CODE(+) IS NOT NULL
5566: and nvl(END_DATE(+), SYSDATE+1) > SYSDATE
5567: and nvl(apl.rate_type_code, 'STANDARD') = 'STANDARD';
5568:
5569: -- -------------------------------------
5570: -- Cursor for updating permutations
5571: -- -------------------------------------
5586: and pso.policy_id(+) = apl.policy_id
5587: and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5588: and OPTION_CODE(+) IS NOT NULL
5589: and nvl(END_DATE(+), SYSDATE+1) > SYSDATE
5590: and nvl(apl.rate_type_code, 'STANDARD') = 'STANDARD'
5591: and not exists
5592: ( select 1
5593: from ap_pol_lines epl
5594: where epl.POLICY_ID = apl.policy_id
5814: l_rate_period_type_code ap_pol_headers.night_rates_code%TYPE;
5815: l_first_period_count number := 0;
5816: BEGIN
5817:
5818: select nvl(rate_period_type_code, 'STANDARD')
5819: into l_rate_period_type_code
5820: from ap_pol_headers
5821: where policy_id = p_policy_id;
5822:
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)
5830: then
5831: return 'Y';
5832: else
5833: return 'N';
5865: l_rate_period_type_code ap_pol_headers.night_rates_code%TYPE;
5866: l_last_period_count number := 0;
5867: BEGIN
5868:
5869: select nvl(rate_period_type_code, 'STANDARD')
5870: into l_rate_period_type_code
5871: from ap_pol_headers
5872: where policy_id = p_policy_id;
5873:
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)
5881: then
5882: return 'Y';
5883: else
5884: return 'N';
6361: |
6362: | PARAMETERS
6363: | p_policy_id IN Policy Id
6364: | p_schedule_id IN Policy Schedule Id.
6365: | p_rate_type IN Rate Type (STANDARD, SAME_DAY, FIRST_PERIOD,
6366: | LAST_PERIOD, NIGHT_RATE, ADDON)
6367: | p_std_invalid OUT Count of invalid standard lines
6368: | p_first_invalid OUT Count of invalid first period lines
6369: | p_last_invalid OUT Count of invalid last period lines
6363: | p_policy_id IN Policy Id
6364: | p_schedule_id IN Policy Schedule Id.
6365: | p_rate_type IN Rate Type (STANDARD, SAME_DAY, FIRST_PERIOD,
6366: | LAST_PERIOD, NIGHT_RATE, ADDON)
6367: | p_std_invalid OUT Count of invalid standard lines
6368: | p_first_invalid OUT Count of invalid first period lines
6369: | p_last_invalid OUT Count of invalid last period lines
6370: | p_same_invalid OUT Count of invalid same day rate lines
6371: | p_night_invalid OUT Count of invalid night rate lines
6387: p_addon_invalid OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
6388:
6389: -- Cursor to check for invalid lines based on status
6390: cursor c_invalid_policy_lines is
6391: select nvl(rate_type_code, 'STANDARD') rate_type_code, count(1) as number_of_lines
6392: from ap_pol_lines
6393: where policy_id = p_policy_id
6394: and schedule_period_id = p_schedule_id
6395: and (status <> 'VALID' and status <> 'ACTIVE' and status <> 'INACTIVE' and status <> 'DUPLICATED')--Bug #13390211,13908899
6423:
6424: -- Loop through the cursor
6425: FOR invalidLines in c_invalid_policy_lines
6426: LOOP
6427: IF ( invalidLines.rate_type_code = 'STANDARD' ) THEN
6428: p_std_invalid := invalidLines.number_of_lines;
6429: ELSIF ( invalidLines.rate_type_code = 'FIRST_PERIOD' ) THEN
6430: p_first_invalid := invalidLines.number_of_lines;
6431: ELSIF ( invalidLines.rate_type_code = 'LAST_PERIOD' ) THEN