[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * INTO ret_val FROM (
SELECT p_person_id, effective_start_date, effective_end_date, grade_id, job_id, position_id
FROM per_all_assignments_f ass,
per_employees_x P
WHERE P.EMPLOYEE_ID = p_person_id
AND ass.person_id = P.employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.isPersonCwk(P.employee_id)='Y'
AND p_date >= effective_start_date and p_date <= effective_end_date
AND ass.assignment_type = 'E'
UNION ALL
SELECT p_person_id, effective_start_date, effective_end_date, grade_id, job_id, position_id
FROM per_all_assignments_f ass,
per_cont_workers_current_x P
WHERE ass.assignment_id = P.assignment_id
AND P.PERSON_ID = p_person_id
AND p_date >= effective_start_date and p_date <= effective_end_date
AND ass.assignment_type = 'C')
WHERE ROWNUM = 1;
SELECT end_date
INTO l_sch_end_date
FROM ap_pol_headers
WHERE policy_id = p_policy_id;
SELECT count(status)
INTO l_no__saved_or_duplicated
FROM ap_pol_lines
WHERE policy_id = p_policy_id
AND ( status = 'SAVED' OR status = 'DUPLICATED' or status ='INVALID' or status = 'NEW' or status = 'VALID');
SELECT count(status)
INTO l_no__active_or_inactive
FROM ap_pol_lines
WHERE policy_id = p_policy_id
AND ( status = 'ACTIVE' OR status = 'INACTIVE' );
SELECT meaning
INTO l_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code ;
SELECT description
INTO l_description
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code ;
SELECT threshold
FROM ap_pol_schedule_options
WHERE option_type = p_lookup_type
AND policy_id = p_policy_id
ORDER BY threshold;
SELECT count(1)
INTO l_count
FROM AP_POL_CONTEXT
WHERE user_id = p_user_id;
SELECT pco.CATEGORY_OPTION_ID,
pco.CATEGORY_CODE,
pco.ORG_ID,
pc.user_id,
pc.selected_org_id
FROM AP_POL_CAT_OPTIONS_ALL pco,
AP_POL_CONTEXT pc
WHERE pco.org_id(+) = pc.selected_org_id
AND pco.category_code(+) = p_category_code
AND pc.user_id = p_user_id;
INSERT INTO ap_pol_cat_options_all
(category_option_id,
category_code,
org_id,
distance_uom,
distance_field,
destination_field,
license_plate_field,
attendees_field,
attendees_number_field,
end_date_field,
merchant_field,
ticket_class_field,
ticket_number_field,
location_to_field,
location_from_field,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by)
VALUES (AP_POL_CAT_OPTIONS_S.nextval,
p_category_code,
user_cat_options_rec.selected_org_id,
DECODE(p_category_code,
'MILEAGE','KM',
NULL), --distance_uom
DECODE(p_category_code,
'MILEAGE','TRIP_DISTANCE',
NULL), --distance_field,
DECODE(p_category_code,
'MILEAGE','ENABLED',
NULL), --destination_field,
DECODE(p_category_code,
'MILEAGE','DISABLED',
NULL), --license_plate_field,
DECODE(p_category_code,
'MEALS','ENABLED',
NULL), --attendees_field,
DECODE(p_category_code,
'MEALS','ENABLED',
NULL), --attendees_number_field,
DECODE(p_category_code,
'ACCOMMODATIONS','ENABLED',
NULL), --end_date_field,
DECODE(p_category_code,
'ACCOMMODATIONS','ENABLED',
'AIRFARE','ENABLED',
'CAR_RENTAL','ENABLED',
NULL), --merchant_field,
DECODE(p_category_code,
'AIRFARE','ENABLED',
NULL), --ticket_class_field,
DECODE(p_category_code,
'AIRFARE','ENABLED',
NULL), --ticket_number_field,
DECODE(p_category_code,
'AIRFARE','ENABLED',
NULL), --location_to_field,
DECODE(p_category_code,
'AIRFARE','ENABLED',
NULL), --location_from_field,
SYSDATE,
p_user_id,
NULL,
SYSDATE,
p_user_id);
SELECT 'Y'
FROM dual
WHERE exists (select 'x' from ap_pol_locations_b);
SELECT count(1) missing_translation_count
FROM ap_pol_locations_tl
WHERE language = p_language_code
AND language <> source_lang;
select DECODE(per.PERSON_ID,
null, usr.USER_NAME,
per.full_name) employee_name
from fnd_user usr, per_people_x per
where usr.user_id = p_user_id
and usr.employee_id = per.person_id(+);
select location
from ap_pol_locations_vl
where location_id = p_location_id;
select name||' - '||currency_code currency_display
from fnd_currencies_vl
where currency_code = p_currency_code;
select ph.role_code,
pl.role_id
from ap_pol_headers ph,
ap_pol_lines pl
where pl.policy_id = ph.policy_id
and pl.policy_line_id = p_policy_line_id;
select ph.role_code,
pso.role_id
from ap_pol_headers ph,
ap_pol_schedule_options pso
where ph.policy_id = pso.policy_id
and pso.schedule_option_id = p_policy_schedule_option_id;
select name,
substrb(name,instrb(name,'.',-1)+1) parsed_name
from per_jobs
where job_id = p_role_id;
select name,
substrb(name,instrb(name,'.',-1)+1) parsed_name
from per_grades
where grade_id = p_role_id;
select name,
substrb(name,instrb(name,'.',-1)+1) parsed_name
from hr_all_positions_f
where position_id = p_role_id;
SELECT ex.EXCHANGE_RATE_ID,
ex.ORG_ID,
pc.user_id,
pc.selected_org_id
FROM AP_POL_EXRATE_OPTIONS_ALL ex,
AP_POL_CONTEXT pc
WHERE ex.org_id(+) = pc.selected_org_id
AND pc.user_id = p_user_id;
INSERT INTO ap_pol_exrate_options_all
(exchange_rate_id,
enabled,
default_exchange_rates,
exchange_rate_type,
exchange_rate_allowance,
overall_tolerance,
org_id,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by)
VALUES (AP_POL_EXRATE_OPTIONS_S.nextval,
'N',
'N',
'Corporate',
null,
null,
user_exrate_options_rec.selected_org_id,
SYSDATE,
p_user_id,
NULL,
SYSDATE,
p_user_id);
SELECT nvl(multi_org_flag, 'N') multi_org_flag
FROM fnd_product_groups;
SELECT threshold
FROM ap_pol_schedule_options
WHERE policy_id = p_policy_id
AND threshold is not null
AND nvl(rate_type_code, 'STANDARD') = p_rate_type
ORDER BY threshold;
select category_code
into l_category_code
from ap_pol_headers
where policy_id = p_policy_id;
select location_flag
into l_location_flag
from ap_pol_headers
where policy_id = p_policy_id;
select count(location_id)
into l_location_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and option_type = c_LOCATION
and location_id is not null;
select employee_role_flag
into l_employee_role_flag
from ap_pol_headers
where policy_id = p_policy_id;
select count(role_id)
into l_role_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and option_type = c_EMPLOYEE_ROLE
and role_id is not null;
select currency_preference
into l_currency_preference
from ap_pol_headers
where policy_id = p_policy_id;
select count(currency_code)
into l_currency_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and option_type = c_CURRENCY
and currency_code is not null;
select vehicle_category_flag
into l_vehicle_category_flag
from ap_pol_headers
where policy_id = p_policy_id;
select count(option_code)
into l_vehicle_category_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and option_type = c_VEHICLE_CATEGORY
and option_code is not null;
select vehicle_type_flag
into l_vehicle_type_flag
from ap_pol_headers
where policy_id = p_policy_id;
select count(option_code)
into l_vehicle_type_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and option_type = c_VEHICLE_TYPE
and option_code is not null;
select fuel_type_flag
into l_fuel_type_flag
from ap_pol_headers
where policy_id = p_policy_id;
select count(option_code)
into l_fuel_type_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and option_type = c_FUEL_TYPE
and option_code is not null;
select nvl(time_based_entry_flag, 'N')
into l_time_based_entry_flag
from ap_pol_headers
where policy_id = p_policy_id;
select count(threshold)
into l_thresholds_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and (option_type = c_TIME_THRESHOLD)
and threshold is not null;
select nvl2(distance_thresholds_flag, 'Y', 'N')
into l_distance_thresholds_flag
from ap_pol_headers
where policy_id = p_policy_id;
select count(threshold)
into l_thresholds_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and (option_type = c_DISTANCE_THRESHOLD)
and threshold is not null;
select nvl(addon_mileage_rates_flag, 'N')
into l_addon_mileage_rates_flag
from ap_pol_headers
where policy_id = p_policy_id;
select count(1)
into l_addon_rates_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and (option_type = c_ADDON_RATES)
and option_code is not null;
l_src_stmt VARCHAR2(160) := 'union all select CURRENCY_CODE from ap_pol_headers where POLICY_ID = :p_policy_id';
l_vc_stmt VARCHAR2(80) := 'union all select to_char(null), to_char(null), to_char(null) from sys.dual';
l_number_stmt VARCHAR2(80) := 'union all select to_number(null) from sys.dual';
l_varchar2_stmt VARCHAR2(80) := 'union all select to_char(null) from sys.dual';
select currency_preference
into l_currency_preference
from ap_pol_headers
where policy_id = p_policy_id;
if Vehicle Category is not selected
then return 3 nulls (option_code, vehicle_type_code, fuel_type_code)
*/
return l_vc_stmt;
update ap_pol_lines
set ticket_class_domestic = 'COACH'
where policy_id = p_policy_id
and ticket_class_domestic is null;
update ap_pol_lines
set ticket_class_international = 'COACH'
where policy_id = p_policy_id
and ticket_class_international is null;
l_insert_sql_stmt VARCHAR2(4000);
select schedule_period_id
from ap_pol_schedule_periods
where policy_id = p_policy_id;
select distinct rate_type_code
from ap_pol_schedule_options
where policy_id = p_policy_id
and rate_type_code in ('FIRST_PERIOD', 'LAST_PERIOD', 'SAME_DAY')
and option_type = 'TIME_THRESHOLD';
cursor l_insert_cursor is
select
'
insert into AP_POL_LINES
(
POLICY_LINE_ID,
POLICY_ID,
SCHEDULE_PERIOD_ID,
LOCATION_ID,
ROLE_ID,
CURRENCY_CODE,
VEHICLE_CATEGORY,
VEHICLE_TYPE,
FUEL_TYPE,
RANGE_LOW,
RANGE_HIGH,
RATE_TYPE_CODE,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
:p_policy_id AS POLICY_ID,
:p_schedule_period_id AS SCHEDULE_PERIOD_ID,
NEW_LOCATION_ID AS LOCATION_ID,
NEW_ROLE_ID AS ROLE_ID,
NEW_CURRENCY_CODE AS CURRENCY_CODE,
NEW_VEHICLE_CATEGORY AS VEHICLE_CATEGORY,
NEW_VEHICLE_TYPE AS VEHICLE_TYPE,
NEW_FUEL_TYPE AS FUEL_TYPE,
NEW_RANGE_LOW AS RANGE_LOW,
NEW_RANGE_HIGH AS RANGE_HIGH,
NEW_RATE_TYPE_CODE AS RATE_TYPE_CODE,
''NEW'' AS STATUS,
sysdate AS CREATION_DATE,
:p_user_id AS CREATED_BY,
sysdate AS LAST_UPDATE_DATE,
:p_user_id AS LAST_UPDATED_BY
from
(
select distinct
NEW_LOCATION_ID,
NEW_ROLE_ID,
NEW_CURRENCY_CODE,
NEW_VEHICLE_CATEGORY,
NEW_VEHICLE_TYPE,
NEW_FUEL_TYPE,
NEW_RANGE_LOW,
NEW_RANGE_HIGH,
NEW_RATE_TYPE_CODE
from
(
select
l.LOCATION_ID AS NEW_LOCATION_ID,
r.ROLE_ID AS NEW_ROLE_ID,
c.CURRENCY_CODE AS NEW_CURRENCY_CODE,
vc.OPTION_CODE AS NEW_VEHICLE_CATEGORY,
decode(vc.OPTION_CODE, null, vt.OPTION_CODE, decode(vc.VEHICLE_TYPE_CODE, ''R'', vt.OPTION_CODE, null)) AS NEW_VEHICLE_TYPE,
decode(vc.OPTION_CODE, null, ft.OPTION_CODE, decode(vc.FUEL_TYPE_CODE, ''R'', ft.OPTION_CODE, null)) AS NEW_FUEL_TYPE,
dt.THRESHOLD AS NEW_RANGE_LOW,
ap_web_policy_UTILS.getHighEndOfThreshold(:p_policy_id, dt.THRESHOLD) AS NEW_RANGE_HIGH,
:p_rate_type AS NEW_RATE_TYPE_CODE
from
'
from sys.dual; /* l_insert_cursor */
select
'
(select LOCATION_ID
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and OPTION_TYPE = :c_LOCATION
and LOCATION_ID IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
'||l_location_enabled||'
) l,
'
from sys.dual; /* l_l_cursor */
select
'
(select ROLE_ID
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and OPTION_TYPE = :c_EMPLOYEE_ROLE
and ROLE_ID IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
'||l_role_enabled||'
) r,
'
from sys.dual; /* l_r_cursor */
select
'
(select CURRENCY_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and OPTION_TYPE = :c_CURRENCY
and CURRENCY_CODE IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
'||l_currency_enabled||'
) c,
'
from sys.dual; /* l_c_cursor */
select
'
(select OPTION_CODE, VEHICLE_TYPE_CODE, FUEL_TYPE_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and OPTION_TYPE = :c_VEHICLE_CATEGORY
and OPTION_CODE IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
'||l_vehicle_category_enabled||'
) vc,
'
from sys.dual; /* l_vc_cursor */
select
'
(select OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and OPTION_TYPE = :c_VEHICLE_TYPE
and OPTION_CODE IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
'||l_vehicle_type_enabled||'
) vt,
'
from sys.dual; /* l_vt_cursor */
select
'
(select OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and OPTION_TYPE = :c_FUEL_TYPE
and OPTION_CODE IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
'||l_fuel_type_enabled||'
) ft,
'
from sys.dual; /* l_ft_cursor */
select
'
(select THRESHOLD
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and (OPTION_TYPE = :c_DISTANCE_THRESHOLD or OPTION_TYPE = :c_TIME_THRESHOLD)
and THRESHOLD IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
and nvl(rate_type_code, ''NULL'') = nvl(:p_rate_type, ''NULL'')
'||l_thresholds_enabled||'
) dt
'
from sys.dual; /* l_dt_cursor */
select
'
)
where
(
NEW_LOCATION_ID is not null
or NEW_ROLE_ID is not null
or NEW_CURRENCY_CODE is not null
or NEW_VEHICLE_CATEGORY is not null
or NEW_VEHICLE_TYPE is not null
or NEW_FUEL_TYPE is not null
or NEW_RANGE_LOW is not null
or NEW_RANGE_HIGH is not null
)
)
'
from sys.dual; /* l_where_cursor */
select
'
)
where
(
NEW_LOCATION_ID is not null
or NEW_ROLE_ID is not null
or NEW_CURRENCY_CODE is not null
or NEW_VEHICLE_CATEGORY is not null
or NEW_VEHICLE_TYPE is not null
or NEW_FUEL_TYPE is not null
or NEW_RANGE_LOW is not null
or NEW_RANGE_HIGH is not null
)
and
not exists
(
select epl.POLICY_LINE_ID
from AP_POL_LINES epl
where epl.POLICY_ID = :p_policy_id
and epl.SCHEDULE_PERIOD_ID = :p_schedule_period_id
and nvl(epl.LOCATION_ID, :dummy_number) = nvl(NEW_LOCATION_ID, :dummy_number)
and nvl(epl.ROLE_ID, :dummy_number) = nvl(NEW_ROLE_ID, :dummy_number)
and
(
(nvl(epl.CURRENCY_CODE, :dummy_varchar2) = nvl(NEW_CURRENCY_CODE, :dummy_varchar2))
or
(epl.CURRENCY_CODE is not null and NEW_CURRENCY_CODE is null)
)
and nvl(epl.VEHICLE_CATEGORY, :dummy_varchar2) = nvl(NEW_VEHICLE_CATEGORY, :dummy_varchar2)
and nvl(epl.VEHICLE_TYPE, :dummy_varchar2) = nvl(NEW_VEHICLE_TYPE, :dummy_varchar2)
and nvl(epl.FUEL_TYPE, :dummy_varchar2) = nvl(NEW_FUEL_TYPE, :dummy_varchar2)
and nvl(epl.RANGE_LOW, :dummy_number) = nvl(NEW_RANGE_LOW, :dummy_number)
and nvl(epl.RANGE_HIGH, :dummy_number) = nvl(NEW_RANGE_HIGH, :dummy_number)
and nvl(epl.RATE_TYPE_CODE, :dummy_varchar2) = nvl(NEW_RATE_TYPE_CODE, :dummy_varchar2)
)
)
'
from sys.dual; /* l_not_exists_cursor */
select category_code, schedule_type_code, source
into l_category_code, l_schedule_type, l_source
from ap_pol_headers
where policy_id = p_policy_id;
delete from ap_pol_schedule_options
where policy_id = p_policy_id
and option_type = 'TIME_THRESHOLD'
and threshold = 0
and rate_type_code not in
( select distinct rate_type_code
from ap_pol_schedule_options
where policy_id = p_policy_id
and rate_type_code in ('FIRST_PERIOD', 'LAST_PERIOD', 'SAME_DAY')
and option_type = 'TIME_THRESHOLD'
and threshold > 0 )
and rate_type_code <> 'STANDARD';
select count(1)
into l_zero_threshold_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and rate_type_code = rate_type_cur.rate_type_code
and threshold = 0;
SELECT ap_pol_schedule_options_s.NEXTVAL
INTO l_schedule_option_rec.schedule_option_id
FROM DUAL;
INSERT INTO ap_pol_schedule_options
(
policy_id,
schedule_option_id,
option_type,
threshold,
status,
creation_date,
created_by,
last_update_date,
last_updated_by,
rate_type_code
)
VALUES
(
p_policy_id,
l_schedule_option_rec.schedule_option_id,
'TIME_THRESHOLD',
0,
'SAVED',
sysdate,
p_user_id,
sysdate,
p_user_id,
rate_type_cur.rate_type_code
);
l_schedule_option_rec.last_update_date := sysdate;
l_schedule_option_rec.last_updated_by := p_user_id;
INSERT INTO ap_pol_schedule_options values l_schedule_option_rec;
select count(1)
into l_zero_threshold_count
from ap_pol_headers
where policy_id = p_policy_id
and time_based_entry_flag = 'Y'
and ( day_period_code <> 'MIDNIGHT' or
(nvl(rate_period_type_code, 'STANDARD') = 'STANDARD' and schedule_type_code = 'PER_DIEM') or
( schedule_type_code = 'ALLOWANCE' and allowance_time_rule_code = 'TIME_THRESHOLD' )
);
select count(1)
into l_zero_threshold_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and rate_type_code = 'STANDARD'
and threshold = 0;
SELECT ap_pol_schedule_options_s.NEXTVAL
INTO l_schedule_option_rec.schedule_option_id
FROM DUAL;
INSERT INTO ap_pol_schedule_options
(
policy_id,
schedule_option_id,
option_type,
threshold,
status,
creation_date,
created_by,
last_update_date,
last_updated_by,
rate_type_code
)
VALUES
(
p_policy_id,
l_schedule_option_rec.schedule_option_id,
'TIME_THRESHOLD',
0,
'SAVED',
sysdate,
p_user_id,
sysdate,
p_user_id,
'STANDARD'
);
l_schedule_option_rec.last_update_date := sysdate;
l_schedule_option_rec.last_updated_by := p_user_id;
INSERT INTO ap_pol_schedule_options values l_schedule_option_rec;
delete from ap_pol_schedule_options
where policy_id = p_policy_id
and rate_type_code = 'STANDARD'
and threshold = 0;
open l_insert_cursor;
fetch l_insert_cursor into l_insert_sql_stmt;
select count(policy_line_id)
into l_policy_line_count
from ap_pol_lines
where policy_id = p_policy_id
and schedule_period_id = l_schedule_period_id;
l_insert_sql_stmt||
l_l_sql_stmt||
l_r_sql_stmt||
l_c_sql_stmt||
l_vc_sql_stmt||
l_vt_sql_stmt||
l_ft_sql_stmt||
l_dt_sql_stmt||
l_where_sql_stmt, DBMS_SQL.NATIVE);
l_insert_sql_stmt||
l_l_sql_stmt||
l_r_sql_stmt||
l_c_sql_stmt||
l_vc_sql_stmt||
l_vt_sql_stmt||
l_ft_sql_stmt||
l_dt_sql_stmt||
l_not_exists_sql_stmt, DBMS_SQL.NATIVE);
close l_insert_cursor;
update ap_pol_lines
set calculation_method = 'AMOUNT',
accommodation_calc_method = 'AMOUNT'
where policy_id = p_policy_id
and nvl(calculation_method, 'X') <> 'AMOUNT';
updateInactivePolicyLines(p_policy_id);
select
'
delete
from AP_POL_LINES pl
where pl.POLICY_ID = :p_policy_id
and ((pl.LOCATION_ID is not null
and not exists
(select pso.LOCATION_ID
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_LOCATION
and pso.LOCATION_ID is not null
and pso.LOCATION_ID = pl.LOCATION_ID
)
)
or
(pl.LOCATION_ID is null
and exists
(select pso.LOCATION_ID
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_LOCATION
and pso.LOCATION_ID is not null
)
)
'
from sys.dual; /* l_l_cursor */
select
'
or
(pl.ROLE_ID is not null
and not exists
(select pso.ROLE_ID
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
and pso.ROLE_ID is not null
and pso.ROLE_ID = pl.ROLE_ID
)
)
or
(pl.ROLE_ID is null
and exists
(select pso.ROLE_ID
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
and pso.ROLE_ID is not null
)
)
'
from sys.dual; /* l_r_cursor */
select
'
or
(pl.CURRENCY_CODE is not null
and
(not exists
(select pso.CURRENCY_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_CURRENCY
and pso.CURRENCY_CODE is not null
and pso.CURRENCY_CODE = pl.CURRENCY_CODE
)
and
not exists
(select ph.CURRENCY_PREFERENCE
from AP_POL_HEADERS ph
where ph.POLICY_ID = pl.POLICY_ID
and (ph.CURRENCY_PREFERENCE = :c_SRC
or
ph.CURRENCY_PREFERENCE = :c_LCR
)
)
)
)
or
(pl.CURRENCY_CODE is not null
and
exists
(select ph.CURRENCY_PREFERENCE
from AP_POL_HEADERS ph
where ph.POLICY_ID = pl.POLICY_ID
and ph.CURRENCY_PREFERENCE = :c_SRC
)
and
not exists
(select ph.CURRENCY_CODE
from AP_POL_HEADERS ph
where ph.POLICY_ID = pl.POLICY_ID
and ph.CURRENCY_CODE = pl.CURRENCY_CODE
)
)
'
from sys.dual; /* l_c_cursor */
select
'
or
(pl.VEHICLE_CATEGORY is not null
and not exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
and pso.OPTION_CODE is not null
and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
)
)
or
(pl.VEHICLE_CATEGORY is null
and exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
and pso.OPTION_CODE is not null
)
)
'
from sys.dual; /* l_vc_cursor */
select
'
or
(pl.VEHICLE_TYPE is not null
and
(not exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_TYPE
and pso.OPTION_CODE is not null
and pso.OPTION_CODE = pl.VEHICLE_TYPE
)
or exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
and pso.VEHICLE_TYPE_CODE <> ''R''
))
)
or
(pl.VEHICLE_TYPE is null
and exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_TYPE
and pso.OPTION_CODE is not null
)
and not exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
and pso.VEHICLE_TYPE_CODE <> ''R''
)
)
'
from sys.dual; /* l_vt_cursor */
select
'
or
(pl.FUEL_TYPE is not null
and
(not exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_FUEL_TYPE
and pso.OPTION_CODE is not null
and pso.OPTION_CODE = pl.FUEL_TYPE
)
or exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
and pso.FUEL_TYPE_CODE <> ''R''
))
)
or
(pl.FUEL_TYPE is null
and exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_FUEL_TYPE
and pso.OPTION_CODE is not null
)
and not exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
and pso.FUEL_TYPE_CODE <> ''R''
)
)
'
from sys.dual; /* l_ft_cursor */
select
'
or
(pl.RANGE_LOW is not null
and not exists
(select pso.THRESHOLD
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
and pso.THRESHOLD is not null
and pso.THRESHOLD = pl.RANGE_LOW
and nvl(pso.rate_type_code, ''NULL'') = nvl(pl.rate_type_code, ''NULL'')
)
)
or
(pl.RANGE_HIGH is not null
and not exists
(select pso.THRESHOLD
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
and pso.THRESHOLD is not null
and pso.THRESHOLD = pl.RANGE_HIGH
and nvl(pso.rate_type_code, ''NULL'') = nvl(pl.rate_type_code, ''NULL'')
)
)
or
(pl.RANGE_LOW is null
and exists
(select pso.THRESHOLD
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
and pso.THRESHOLD is not null
and nvl(pso.rate_type_code, ''NULL'') = nvl(pl.rate_type_code, ''NULL'')
)
)
or
(pl.RANGE_LOW is not null
and
nvl(pl.RANGE_HIGH, :dummy_number) <>
nvl(AP_WEB_POLICY_UTILS.getHighEndOfThreshold(pl.POLICY_ID,
pl.RANGE_LOW,
nvl(pl.rate_type_code,''STANDARD'')), :dummy_number)
)
'
from sys.dual; /* l_dt_cursor */
select
' or
(pl.ADDON_MILEAGE_RATE_CODE is not null
and not exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_ADDON_RATES
and pso.OPTION_CODE is not null
and pso.OPTION_CODE = pl.ADDON_MILEAGE_RATE_CODE
)
)
)
'
from sys.dual; /* l_amr_cursor */
| PUBLIC PROCEDURE updateInactivePolicyLines
|
| DESCRIPTION
| - if option end dated then set Policy Line status to inactive
| - reactivate inactive lines if end date updated
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS
| p_policy_id IN Policy Identifier
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 16-May-2002 R Langi Created
|
*=======================================================================*/
PROCEDURE updateInactivePolicyLines(p_policy_id IN ap_pol_headers.policy_id%TYPE) IS
l_inactive_curref INTEGER;
select
'
update AP_POL_LINES pl
set pl.STATUS = :c_INACTIVE
where pl.POLICY_ID = :p_policy_id
and pl.STATUS = :c_ACTIVE
and ((pl.LOCATION_ID is not null
and exists
(select pso.LOCATION_ID
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_LOCATION
and pso.LOCATION_ID is not null
and pso.LOCATION_ID = pl.LOCATION_ID
and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
)
)
or
(pl.ROLE_ID is not null
and exists
(select pso.ROLE_ID
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
and pso.ROLE_ID is not null
and pso.ROLE_ID = pl.ROLE_ID
and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
)
)
or
(pl.CURRENCY_CODE is not null
and exists
(select pso.CURRENCY_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_CURRENCY
and pso.CURRENCY_CODE is not null
and pso.CURRENCY_CODE = pl.CURRENCY_CODE
and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
)
)
or
(pl.VEHICLE_CATEGORY is not null
and exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
and pso.OPTION_CODE is not null
and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
)
)
or
(pl.VEHICLE_TYPE is not null
and exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_TYPE
and pso.OPTION_CODE is not null
and pso.OPTION_CODE = pl.VEHICLE_TYPE
and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
)
)
or
(pl.FUEL_TYPE is not null
and exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_FUEL_TYPE
and pso.OPTION_CODE is not null
and pso.OPTION_CODE = pl.FUEL_TYPE
and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
)
)
or
(pl.RANGE_LOW is not null
and exists
(select pso.THRESHOLD
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
and pso.THRESHOLD is not null
and pso.THRESHOLD = pl.RANGE_LOW
and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
)
)
)
'
from sys.dual; /* l_inactive_cursor */
select
'
update AP_POL_LINES pl
set pl.STATUS = :c_ACTIVE
where pl.POLICY_ID = :p_policy_id
and pl.STATUS = :c_INACTIVE
and (((pl.LOCATION_ID is not null
and exists
(select pso.LOCATION_ID
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_LOCATION
and pso.LOCATION_ID is not null
and pso.LOCATION_ID = pl.LOCATION_ID
and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
)
) or pl.LOCATION_ID is null
)
and
((pl.ROLE_ID is not null
and exists
(select pso.ROLE_ID
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
and pso.ROLE_ID is not null
and pso.ROLE_ID = pl.ROLE_ID
and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
)
) or pl.ROLE_ID is null
)
and
((pl.CURRENCY_CODE is not null
and
(exists
(select pso.CURRENCY_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_CURRENCY
and pso.CURRENCY_CODE is not null
and pso.CURRENCY_CODE = pl.CURRENCY_CODE
and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
)
or exists
(select ph.CURRENCY_CODE
from AP_POL_HEADERS ph
where ph.POLICY_ID = pl.POLICY_ID
and ((ph.CURRENCY_CODE is not null and ph.CURRENCY_CODE = pl.CURRENCY_CODE)
or
(ph.CURRENCY_CODE is null and ph.CURRENCY_PREFERENCE <> :c_SRC)))
)
)
or pl.CURRENCY_CODE is null
)
and
((pl.VEHICLE_CATEGORY is not null
and exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
and pso.OPTION_CODE is not null
and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
)
) or pl.VEHICLE_CATEGORY is null
)
and
((pl.VEHICLE_TYPE is not null
and exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_VEHICLE_TYPE
and pso.OPTION_CODE is not null
and pso.OPTION_CODE = pl.VEHICLE_TYPE
and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
) or pl.VEHICLE_TYPE is null
)
)
and
((pl.FUEL_TYPE is not null
and exists
(select pso.OPTION_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and pso.OPTION_TYPE = :c_FUEL_TYPE
and pso.OPTION_CODE is not null
and pso.OPTION_CODE = pl.FUEL_TYPE
and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
)
) or pl.FUEL_TYPE is null
)
and
((pl.RANGE_LOW is not null
and exists
(select pso.THRESHOLD
from AP_POL_SCHEDULE_OPTIONS pso
where pso.POLICY_ID = pl.POLICY_ID
and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
and pso.THRESHOLD is not null
and pso.THRESHOLD = pl.RANGE_LOW
and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
)
) or pl.RANGE_LOW is null
)
)
'
from sys.dual; /* l_active_cursor */
END updateInactivePolicyLines;
select
'
insert into AP_POL_LINES
(
POLICY_LINE_ID,
POLICY_ID,
SCHEDULE_PERIOD_ID,
LOCATION_ID,
ROLE_ID,
CURRENCY_CODE,
MEAL_LIMIT,
RATE,
TOLERANCE,
TICKET_CLASS_DOMESTIC,
TICKET_CLASS_INTERNATIONAL,
VEHICLE_CATEGORY,
VEHICLE_TYPE,
FUEL_TYPE,
RANGE_LOW,
RANGE_HIGH,
CALCULATION_METHOD,
MEALS_DEDUCTION,
BREAKFAST_DEDUCTION,
LUNCH_DEDUCTION,
DINNER_DEDUCTION,
ACCOMMODATION_ADJUSTMENT,
ADDON_MILEAGE_RATE_CODE,
RATE_PER_PASSENGER,
RATE_TYPE_CODE,
ONE_MEAL_DEDUCTION_AMT,
TWO_MEALS_DEDUCTION_AMT,
THREE_MEALS_DEDUCTION_AMT,
NIGHT_RATE_TYPE_CODE,
ACCOMMODATION_CALC_METHOD,
START_OF_SEASON,
END_OF_SEASON,
MAX_LODGING_AMT,
NO_GOVT_MEALS_AMT,
PROP_MEALS_AMT,
OFF_BASE_INC_AMT,
FOOTNOTE_AMT,
FOOTNOTE_RATE_AMT,
MAX_PER_DIEM_AMT,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
:p_to_policy_id AS POLICY_ID,
:p_to_schedule_period_id AS SCHEDULE_PERIOD_ID,
LOCATION_ID,
ROLE_ID,
CURRENCY_CODE,
MEAL_LIMIT,
RATE,
TOLERANCE,
TICKET_CLASS_DOMESTIC,
TICKET_CLASS_INTERNATIONAL,
VEHICLE_CATEGORY,
VEHICLE_TYPE,
FUEL_TYPE,
RANGE_LOW,
RANGE_HIGH,
CALCULATION_METHOD,
MEALS_DEDUCTION,
BREAKFAST_DEDUCTION,
LUNCH_DEDUCTION,
DINNER_DEDUCTION,
ACCOMMODATION_ADJUSTMENT,
ADDON_MILEAGE_RATE_CODE,
RATE_PER_PASSENGER,
RATE_TYPE_CODE,
ONE_MEAL_DEDUCTION_AMT,
TWO_MEALS_DEDUCTION_AMT,
THREE_MEALS_DEDUCTION_AMT,
NIGHT_RATE_TYPE_CODE,
ACCOMMODATION_CALC_METHOD,
START_OF_SEASON,
END_OF_SEASON,
MAX_LODGING_AMT,
NO_GOVT_MEALS_AMT,
PROP_MEALS_AMT,
OFF_BASE_INC_AMT,
FOOTNOTE_AMT,
FOOTNOTE_RATE_AMT,
MAX_PER_DIEM_AMT,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
decode(status, ''ACTIVE'', ''VALID'', ''VALID'', ''VALID'', :c_DUPLICATED) AS STATUS,
sysdate AS CREATION_DATE,
:p_user_id AS CREATED_BY,
sysdate AS LAST_UPDATE_DATE,
:p_user_id AS LAST_UPDATED_BY
from
AP_POL_LINES
where POLICY_ID = :p_from_policy_id
and SCHEDULE_PERIOD_ID = :p_from_schedule_period_id
and PARENT_LINE_ID is null
'
from sys.dual; /* l_duplicate_cursor */
select count(*)
into l_preserve_count
from AP_POL_LINES
where parent_line_id = p_policy_line_id
and policy_id = p_policy_id
and schedule_period_id = p_schedule_period_id;
insert into AP_POL_LINES
(
PARENT_LINE_ID,
POLICY_LINE_ID,
POLICY_ID,
SCHEDULE_PERIOD_ID,
LOCATION_ID,
ROLE_ID,
CURRENCY_CODE,
MEAL_LIMIT,
RATE,
TOLERANCE,
TICKET_CLASS_DOMESTIC,
TICKET_CLASS_INTERNATIONAL,
VEHICLE_CATEGORY,
VEHICLE_TYPE,
FUEL_TYPE,
RANGE_LOW,
RANGE_HIGH,
CALCULATION_METHOD,
MEALS_DEDUCTION,
BREAKFAST_DEDUCTION,
LUNCH_DEDUCTION,
DINNER_DEDUCTION,
ACCOMMODATION_ADJUSTMENT,
ADDON_MILEAGE_RATE_CODE,
RATE_PER_PASSENGER,
RATE_TYPE_CODE,
ONE_MEAL_DEDUCTION_AMT,
TWO_MEALS_DEDUCTION_AMT,
THREE_MEALS_DEDUCTION_AMT,
NIGHT_RATE_TYPE_CODE,
ACCOMMODATION_CALC_METHOD,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
p_policy_line_id AS PARENT_LINE_ID,
AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
p_policy_id AS POLICY_ID,
p_schedule_period_id AS SCHEDULE_PERIOD_ID,
LOCATION_ID,
ROLE_ID,
CURRENCY_CODE,
MEAL_LIMIT,
RATE,
TOLERANCE,
TICKET_CLASS_DOMESTIC,
TICKET_CLASS_INTERNATIONAL,
VEHICLE_CATEGORY,
VEHICLE_TYPE,
FUEL_TYPE,
RANGE_LOW,
RANGE_HIGH,
CALCULATION_METHOD,
MEALS_DEDUCTION,
BREAKFAST_DEDUCTION,
LUNCH_DEDUCTION,
DINNER_DEDUCTION,
ACCOMMODATION_ADJUSTMENT,
ADDON_MILEAGE_RATE_CODE,
RATE_PER_PASSENGER,
RATE_TYPE_CODE,
ONE_MEAL_DEDUCTION_AMT,
TWO_MEALS_DEDUCTION_AMT,
THREE_MEALS_DEDUCTION_AMT,
NIGHT_RATE_TYPE_CODE,
ACCOMMODATION_CALC_METHOD,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
from
AP_POL_LINES
where POLICY_ID = p_policy_id
and SCHEDULE_PERIOD_ID = p_schedule_period_id
and POLICY_LINE_ID = p_policy_line_id;
insert into AP_POL_LINES_HISTORY
(
POLICY_LINE_HISTORY_ID,
POLICY_LINE_ID,
SCHEDULE_PERIOD_ID,
--CURRENCY_CODE, -- need to add CURRENCY_CODE to AP_POL_LINES_HISTORY because of LCR
MEAL_LIMIT,
RATE,
TOLERANCE,
TICKET_CLASS_DOMESTIC,
TICKET_CLASS_INTERNATIONAL,
CALCULATION_METHOD,
MEALS_DEDUCTION,
BREAKFAST_DEDUCTION,
LUNCH_DEDUCTION,
DINNER_DEDUCTION,
ACCOMMODATION_ADJUSTMENT,
ADDON_MILEAGE_RATE_CODE,
RATE_PER_PASSENGER,
RATE_TYPE_CODE,
ONE_MEAL_DEDUCTION_AMT,
TWO_MEALS_DEDUCTION_AMT,
THREE_MEALS_DEDUCTION_AMT,
NIGHT_RATE_TYPE_CODE,
ACCOMMODATION_CALC_METHOD,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
AP_POL_LINES_HISTORY_S.NEXTVAL AS POLICY_LINE_HISTORY_ID,
PARENT_LINE_ID AS POLICY_LINE_ID,
SCHEDULE_PERIOD_ID,
--CURRENCY_CODE,
MEAL_LIMIT,
RATE,
TOLERANCE,
TICKET_CLASS_DOMESTIC,
TICKET_CLASS_INTERNATIONAL,
CALCULATION_METHOD,
MEALS_DEDUCTION,
BREAKFAST_DEDUCTION,
LUNCH_DEDUCTION,
DINNER_DEDUCTION,
ACCOMMODATION_ADJUSTMENT,
ADDON_MILEAGE_RATE_CODE,
RATE_PER_PASSENGER,
RATE_TYPE_CODE,
ONE_MEAL_DEDUCTION_AMT,
TWO_MEALS_DEDUCTION_AMT,
THREE_MEALS_DEDUCTION_AMT,
NIGHT_RATE_TYPE_CODE,
ACCOMMODATION_CALC_METHOD,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
from
AP_POL_LINES
where POLICY_ID = p_policy_id
and PARENT_LINE_ID is not null;
delete
from
AP_POL_LINES
where POLICY_ID = p_policy_id
and PARENT_LINE_ID is not null;
select AP_POL_SCHEDULE_PERIODS_S.NEXTVAL
into l_schedule_period_id
from sys.dual;
insert into AP_POL_SCHEDULE_PERIODS
(
SCHEDULE_PERIOD_ID,
SCHEDULE_PERIOD_NAME,
POLICY_ID,
START_DATE,
END_DATE,
RATE_PER_PASSENGER,
MIN_DAYS,
TOLERANCE,
MIN_RATE_PER_PERIOD,
MAX_BREAKFAST_DEDUCTION_AMT,
MAX_LUNCH_DEDUCTION_AMT,
MAX_DINNER_DEDUCTION_AMT,
FIRST_DAY_RATE,
LAST_DAY_RATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
l_schedule_period_id AS SCHEDULE_PERIOD_ID,
decode(p_schedule_period_name, null, fnd_message.GET_STRING('SQLAP','OIE_POL_PERIODS_NEW_PERIOD'), substrb(fnd_message.GET_STRING('SQLAP','OIE_POL_COPY_OF')||' '||p_schedule_period_name, 1, C_PolicyNameMaxLength)) AS SCHEDULE_PERIOD_NAME,
p_policy_id AS POLICY_ID,
p_start_date AS START_DATE,
p_end_date AS END_DATE,
p_rate_per_passenger AS RATE_PER_PASSENGER,
p_min_days AS MIN_DAYS,
p_tolerance AS TOLERANCE,
p_min_rate_per_period as MIN_RATE_PER_PERIOD,
p_max_breakfast_deduction as MAX_BREAKFAST_DEDUCTION,
p_max_lunch_deduction as MAX_LUNCH_DEDUCTION,
p_max_dinner_deduction as MAX_DINNER_DEDUCTION,
p_first_day_rate as FIRST_DAY_RATE,
p_last_day_rate as LAST_DAY_RATE,
sysdate AS CREATION_DATE,
p_user_id AS CREATED_BY,
sysdate AS LAST_UPDATE_DATE,
p_user_id AS LAST_UPDATED_BY
from
sys.dual;
| PUBLIC FUNCTION massUpdateValue
|
| DESCRIPTION
| Using a rounding rule and percentage to update by, a value is returned
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS
| p_value IN Value to perform an update on
| p_update_by IN Percentage to update the value by
| p_rounding_rule IN Rounding rule to use after the value has been updated
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 16-May-2002 R Langi Created
|
*=======================================================================*/
FUNCTION massUpdateValue(p_value IN NUMBER,
p_update_by IN NUMBER,
p_rounding_rule IN VARCHAR2) RETURN NUMBER IS
l_new_value NUMBER := 0;
select decode(p_rounding_rule,
'1_DECIMALS', nvl(p_value, 0) + ROUND(nvl(p_value, 0) * (p_update_by/100), 1),
'2_DECIMALS', nvl(p_value, 0) + ROUND(nvl(p_value, 0) * (p_update_by/100), 2),
'3_DECIMALS', nvl(p_value, 0) + ROUND(nvl(p_value, 0) * (p_update_by/100), 3),
nvl(p_value, 0) + ROUND(nvl(p_value, 0) * (p_update_by/100)))
into l_new_value
from sys.dual;
END massUpdateValue;
select
SCHEDULE_PERIOD_ID,
SCHEDULE_PERIOD_NAME,
START_DATE,
END_DATE,
RATE_PER_PASSENGER,
MIN_DAYS,
TOLERANCE,
MIN_RATE_PER_PERIOD,
MAX_BREAKFAST_DEDUCTION_AMT,
MAX_LUNCH_DEDUCTION_AMT,
MAX_DINNER_DEDUCTION_AMT,
FIRST_DAY_RATE,
LAST_DAY_RATE
from AP_POL_SCHEDULE_PERIODS
where POLICY_ID = p_from_policy_id; /* l_duplicate_periods_cursor */
select AP_POL_HEADERS_S.NEXTVAL
into p_to_policy_id
from sys.dual;
insert into AP_POL_HEADERS
(
POLICY_ID,
CATEGORY_CODE,
POLICY_NAME,
DESCRIPTION,
CURRENCY_CODE,
BUSINESS_GROUP_ID,
JOB_GROUP_ID,
ROLE_CODE,
DISTANCE_UOM,
CURRENCY_PREFERENCE,
ALLOW_RATE_CONVERSION_CODE,
DAILY_LIMITS_CODE,
START_DATE,
END_DATE,
DISTANCE_THRESHOLDS_FLAG,
VEHICLE_CATEGORY_FLAG,
VEHICLE_TYPE_FLAG,
FUEL_TYPE_FLAG,
PASSENGERS_FLAG,
EMPLOYEE_ROLE_FLAG,
TIME_BASED_ENTRY_FLAG,
FREE_MEALS_FLAG,
FREE_ACCOMMODATIONS_FLAG,
TOLERANCE_LIMITS_FLAG,
DAILY_LIMITS_FLAG,
LOCATION_FLAG,
TOLERANCE_LIMIT_CODE,
FREE_MEALS_CODE,
FREE_ACCOMMODATIONS_CODE,
DAY_PERIOD_CODE,
ADDON_MILEAGE_RATES_FLAG,
SCHEDULE_TYPE_CODE,
MIN_TRIP_DURATION,
SAME_DAY_RATE_CODE,
NIGHT_RATES_CODE,
NIGHT_RATE_ELIGIBILITY,
NIGHT_RATE_START_TIME,
NIGHT_RATE_END_TIME,
MULTI_DEST_RULE_CODE,
MULTI_DEST_START_TIME,
MULTI_DEST_END_TIME,
PER_DIEM_TYPE_CODE,
SOURCE,
RATE_PERIOD_TYPE_CODE,
MEALS_TYPE_CODE,
ALLOWANCE_TIME_RULE_CODE,
BREAKFAST_START_TIME,
BREAKFAST_END_TIME,
LUNCH_START_TIME,
LUNCH_END_TIME,
DINNER_START_TIME,
DINNER_END_TIME,
USE_MAX_DEST_RATE_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
p_to_policy_id AS POLICY_ID,
CATEGORY_CODE,
substrb(fnd_message.GET_STRING('SQLAP','OIE_POL_COPY_OF')||' '||POLICY_NAME, 1, C_PolicyNameMaxLength) AS POLICY_NAME,
DESCRIPTION,
CURRENCY_CODE,
BUSINESS_GROUP_ID,
JOB_GROUP_ID,
ROLE_CODE,
DISTANCE_UOM,
CURRENCY_PREFERENCE,
ALLOW_RATE_CONVERSION_CODE,
DAILY_LIMITS_CODE,
START_DATE,
null, -- Bug 2847928
DISTANCE_THRESHOLDS_FLAG,
VEHICLE_CATEGORY_FLAG,
VEHICLE_TYPE_FLAG,
FUEL_TYPE_FLAG,
PASSENGERS_FLAG,
EMPLOYEE_ROLE_FLAG,
TIME_BASED_ENTRY_FLAG,
FREE_MEALS_FLAG,
FREE_ACCOMMODATIONS_FLAG,
TOLERANCE_LIMITS_FLAG,
DAILY_LIMITS_FLAG,
LOCATION_FLAG,
TOLERANCE_LIMIT_CODE,
FREE_MEALS_CODE,
FREE_ACCOMMODATIONS_CODE,
DAY_PERIOD_CODE,
ADDON_MILEAGE_RATES_FLAG,
SCHEDULE_TYPE_CODE,
MIN_TRIP_DURATION,
SAME_DAY_RATE_CODE,
NIGHT_RATES_CODE,
NIGHT_RATE_ELIGIBILITY,
NIGHT_RATE_START_TIME,
NIGHT_RATE_END_TIME,
MULTI_DEST_RULE_CODE,
MULTI_DEST_START_TIME,
MULTI_DEST_END_TIME,
PER_DIEM_TYPE_CODE,
SOURCE,
RATE_PERIOD_TYPE_CODE,
MEALS_TYPE_CODE,
ALLOWANCE_TIME_RULE_CODE,
BREAKFAST_START_TIME,
BREAKFAST_END_TIME,
LUNCH_START_TIME,
LUNCH_END_TIME,
DINNER_START_TIME,
DINNER_END_TIME,
USE_MAX_DEST_RATE_FLAG,
sysdate AS CREATION_DATE,
p_user_id AS CREATED_BY,
null AS LAST_UPDATE_LOGIN,
sysdate AS LAST_UPDATE_DATE,
p_user_id AS LAST_UPDATED_BY
from
AP_POL_HEADERS
where POLICY_ID = p_from_policy_id;
insert into AP_POL_SCHEDULE_OPTIONS
(
SCHEDULE_OPTION_ID,
POLICY_ID,
OPTION_TYPE,
OPTION_CODE,
THRESHOLD,
ROLE_ID,
LOCATION_ID,
CURRENCY_CODE,
END_DATE,
VEHICLE_TYPE_CODE,
FUEL_TYPE_CODE,
RATE_TYPE_CODE,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
AP_POL_SCHEDULE_OPTIONS_S.NEXTVAL AS SCHEDULE_OPTION_ID,
p_to_policy_id AS POLICY_ID,
OPTION_TYPE,
OPTION_CODE,
THRESHOLD,
ROLE_ID,
LOCATION_ID,
CURRENCY_CODE,
END_DATE,
VEHICLE_TYPE_CODE,
FUEL_TYPE_CODE,
RATE_TYPE_CODE,
STATUS,
sysdate AS CREATION_DATE,
p_user_id AS CREATED_BY,
null AS LAST_UPDATE_LOGIN,
sysdate AS LAST_UPDATE_DATE,
p_user_id AS LAST_UPDATED_BY
from
AP_POL_SCHEDULE_OPTIONS
where POLICY_ID = p_from_policy_id;
select schedule_option_id
from ap_pol_schedule_options
where option_type = p_option_type
and NVL(option_code,9.99E125) = NVL(p_option_code,9.99E125)
and NVL(threshold,9.99E125) = NVL(p_threshold,9.99E125)
and NVL(role_id,9.99E125) = NVL(p_role_id,9.99E125)
and NVL(location_id,9.99E125) = NVL(p_location_id,9.99E125)
and NVL(currency_code,chr(0)) = NVL(p_currency_code, chr(0))
and NVL(end_date,TO_DATE('1','j')) >= DECODE(p_end_date,
null, TO_DATE('1','j'),
DECODE(end_date,
null, TO_DATE('1','j'),
p_end_date));
select location_id, end_date
from ap_pol_locations_vl
where end_date is not null;
update ap_pol_schedule_options
set end_date = l_end_date
where option_type = 'LOCATION'
and location_id = l_location_id
and NVL(end_date,TO_DATE('1','j')) >=
DECODE(l_end_date,
null, TO_DATE('1','j'),
DECODE(end_date,
null, TO_DATE('1','j'),
l_end_date));
select 1 location_count
from dual
where exists
(select 1
from ap_pol_locations_b);
update AP_POL_SCHEDULE_OPTIONS set STATUS = 'SAVED' where POLICY_ID = p_policy_id and nvl(STATUS, '~') <> 'ACTIVE';
update AP_POL_SCHEDULE_OPTIONS set STATUS = p_status_code where POLICY_ID = p_policy_id;
SELECT 1 INTO l_count_rows
FROM dual
WHERE exists
(select 1
from ap_expense_report_params_all
where company_policy_id = p_policy_id and nvl(end_date,p_end_date+1) > p_end_date);
UPDATE ap_expense_report_params_all SET end_date = p_end_date
WHERE company_policy_id = p_policy_id and nvl(end_date,p_end_date+1) > p_end_date;
SELECT ep.ORG_ID,
pc.user_id,
pc.selected_org_id
FROM AP_EXPENSE_PARAMS_ALL ep,
AP_POL_CONTEXT pc
WHERE ep.org_id(+) = pc.selected_org_id
AND pc.user_id = p_user_id;
INSERT INTO AP_EXPENSE_PARAMS_ALL
(prevent_cash_cc_age_limit,
prevent_future_dated_day_limit,
enforce_cc_acc_limit,
enforce_cc_air_limit,
enforce_cc_car_limit,
enforce_cc_meal_limit,
enforce_cc_misc_limit,
org_id,
creation_date,
created_by,
last_update_login,
last_update_date,
last_updated_by)
VALUES (null,
null,
null,
null,
null,
null,
null,
user_exp_options_rec.selected_org_id,
SYSDATE,
p_user_id,
NULL,
SYSDATE,
p_user_id);
| PUBLIC PROCEDURE deletePolicySchedule
|
| DESCRIPTION
| This procedure deletes a Policy Schedule
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Called from BC4J
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS
| p_policy_id IN Policy ID
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 22-Jul-2005 Sameer Saxena Created
|
*=======================================================================*/
PROCEDURE deletePolicySchedule(p_policy_id IN NUMBER) IS
BEGIN
DELETE FROM AP_POL_SCHEDULE_OPTIONS WHERE POLICY_ID = p_policy_id;
DELETE FROM AP_POL_SCHEDULE_PERIODS WHERE POLICY_ID = p_policy_id;
DELETE FROM AP_POL_LINES WHERE POLICY_ID = p_policy_id;
END deletePolicySchedule;
SELECT count(1)
INTO l_count
FROM ap_pol_lines
WHERE schedule_period_id = p_schedule_period_id;
l_insert_sql_stmt VARCHAR2(4000);
select schedule_period_id
from ap_pol_schedule_periods
where policy_id = p_policy_id;
cursor l_insert_cursor is
select
'
insert into AP_POL_LINES
(
POLICY_LINE_ID,
POLICY_ID,
SCHEDULE_PERIOD_ID,
LOCATION_ID,
ROLE_ID,
CURRENCY_CODE,
RANGE_LOW,
RANGE_HIGH,
RATE_TYPE_CODE,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
:p_policy_id AS POLICY_ID,
:p_schedule_period_id AS SCHEDULE_PERIOD_ID,
NEW_LOCATION_ID AS LOCATION_ID,
NEW_ROLE_ID AS ROLE_ID,
NEW_CURRENCY_CODE AS CURRENCY_CODE,
NEW_RANGE_LOW AS RANGE_LOW,
NEW_RANGE_HIGH AS RANGE_HIGH,
NEW_RATE_TYPE_CODE AS RATE_TYPE_CODE,
''NEW'' AS STATUS,
sysdate AS CREATION_DATE,
:p_user_id AS CREATED_BY,
sysdate AS LAST_UPDATE_DATE,
:p_user_id AS LAST_UPDATED_BY
from
(
select distinct
NEW_LOCATION_ID,
NEW_ROLE_ID,
NEW_CURRENCY_CODE,
NEW_RANGE_LOW,
NEW_RANGE_HIGH,
NEW_RATE_TYPE_CODE
from
(
select
l.LOCATION_ID AS NEW_LOCATION_ID,
r.ROLE_ID AS NEW_ROLE_ID,
c.CURRENCY_CODE AS NEW_CURRENCY_CODE,
dt.THRESHOLD AS NEW_RANGE_LOW,
ap_web_policy_UTILS.getHighEndOfThreshold(:p_policy_id, dt.THRESHOLD, :p_rate_type) AS NEW_RANGE_HIGH,
:p_rate_type AS NEW_RATE_TYPE_CODE
from
'
from sys.dual; /* l_insert_cursor */
select
'
(select LOCATION_ID
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and OPTION_TYPE = :c_LOCATION
and LOCATION_ID IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
'||l_location_enabled||'
) l,
'
from sys.dual; /* l_l_cursor */
select
'
(select ROLE_ID
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and OPTION_TYPE = :c_EMPLOYEE_ROLE
and ROLE_ID IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
'||l_role_enabled||'
) r,
'
from sys.dual; /* l_r_cursor */
select
'
(select CURRENCY_CODE
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and OPTION_TYPE = :c_CURRENCY
and CURRENCY_CODE IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
'||l_currency_enabled||'
) c,
'
from sys.dual; /* l_c_cursor */
select
'
(select THRESHOLD
from AP_POL_SCHEDULE_OPTIONS pso
where
POLICY_ID = :p_policy_id
and (OPTION_TYPE = :c_DISTANCE_THRESHOLD or OPTION_TYPE = :c_TIME_THRESHOLD)
and THRESHOLD IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
and nvl(rate_type_code, :p_rate_type) = :p_rate_type
'||l_thresholds_enabled||'
) dt
'
from sys.dual; /* l_dt_cursor */
select
'
)
where
(
NEW_LOCATION_ID is not null
or NEW_ROLE_ID is not null
or NEW_CURRENCY_CODE is not null
or NEW_RANGE_LOW is not null
or NEW_RANGE_HIGH is not null
)
)
'
from sys.dual; /* l_where_cursor */
select
'
)
where
(
NEW_LOCATION_ID is not null
or NEW_ROLE_ID is not null
or NEW_CURRENCY_CODE is not null
or NEW_RANGE_LOW is not null
or NEW_RANGE_HIGH is not null
)
and
not exists
(
select epl.POLICY_LINE_ID
from AP_POL_LINES epl
where epl.POLICY_ID = :p_policy_id
and epl.SCHEDULE_PERIOD_ID = :p_schedule_period_id
and nvl(epl.LOCATION_ID, :dummy_number) = nvl(NEW_LOCATION_ID, :dummy_number)
and nvl(epl.ROLE_ID, :dummy_number) = nvl(NEW_ROLE_ID, :dummy_number)
and
(
(nvl(epl.CURRENCY_CODE, :dummy_varchar2) = nvl(NEW_CURRENCY_CODE, :dummy_varchar2))
or
(epl.CURRENCY_CODE is not null and NEW_CURRENCY_CODE is null)
)
and nvl(epl.RANGE_LOW, :dummy_number) = nvl(NEW_RANGE_LOW, :dummy_number)
and nvl(epl.RANGE_HIGH, :dummy_number) = nvl(NEW_RANGE_HIGH, :dummy_number)
and nvl(epl.RATE_TYPE_CODE, :dummy_varchar2) = nvl(NEW_RATE_TYPE_CODE, :dummy_varchar2)
)
)
'
from sys.dual; /* l_not_exists_cursor */
open l_insert_cursor;
fetch l_insert_cursor into l_insert_sql_stmt;
select count(policy_line_id)
into l_policy_line_count
from ap_pol_lines
where policy_id = p_policy_id
and schedule_period_id = l_schedule_period_id;
l_insert_sql_stmt||
l_l_sql_stmt||
l_r_sql_stmt||
l_c_sql_stmt||
l_dt_sql_stmt||
l_where_sql_stmt, DBMS_SQL.NATIVE);
l_insert_sql_stmt||
l_l_sql_stmt||
l_r_sql_stmt||
l_c_sql_stmt||
l_dt_sql_stmt||
l_not_exists_sql_stmt, DBMS_SQL.NATIVE);
close l_insert_cursor;
updateInactivePolicyLines(p_policy_id);
select count(policy_line_id)
into l_policy_line_count
from ap_pol_lines
where policy_id = p_policy_id
and schedule_period_id = p_schedule_period_id
and addon_mileage_rate_code is not null;
insert into AP_POL_LINES
(
POLICY_LINE_ID,
POLICY_ID,
SCHEDULE_PERIOD_ID,
LOCATION_ID,
ROLE_ID,
CURRENCY_CODE,
VEHICLE_CATEGORY,
VEHICLE_TYPE,
FUEL_TYPE,
RANGE_LOW,
RANGE_HIGH,
ADDON_MILEAGE_RATE_CODE,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
apl.POLICY_ID,
apl.SCHEDULE_PERIOD_ID,
apl.location_id,
apl.role_id,
apl.currency_code,
vehicle_category,
vehicle_type,
fuel_type,
range_low,
range_high,
option_code as addon_mileage_rate_code,
'NEW' AS STATUS,
sysdate AS CREATION_DATE,
apl.CREATED_BY,
sysdate AS LAST_UPDATE_DATE,
apl.LAST_UPDATED_BY
from ap_pol_lines apl,
ap_pol_schedule_options pso
where apl.POLICY_ID = p_policy_id
and pso.policy_id = apl.policy_id
and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'
and OPTION_CODE IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
and apl.addon_mileage_rate_code is null;
delete from ap_pol_lines pl
where policy_id = p_policy_id
and addon_mileage_rate_code is not null
and not exists
(
select 1
from ap_pol_schedule_options pso
where pso.policy_id = pl.policy_id
and pso.option_code = pl.addon_mileage_rate_code
);
insert into AP_POL_LINES
(
POLICY_LINE_ID,
POLICY_ID,
SCHEDULE_PERIOD_ID,
LOCATION_ID,
ROLE_ID,
CURRENCY_CODE,
VEHICLE_CATEGORY,
VEHICLE_TYPE,
FUEL_TYPE,
RANGE_LOW,
RANGE_HIGH,
ADDON_MILEAGE_RATE_CODE,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
select
AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
apl.POLICY_ID,
apl.SCHEDULE_PERIOD_ID,
apl.location_id,
apl.role_id,
apl.currency_code,
apl.vehicle_category,
apl.vehicle_type,
apl.fuel_type,
apl.range_low,
apl.range_high,
option_code as addon_mileage_rate_code,
'NEW' AS STATUS,
sysdate AS CREATION_DATE,
apl.CREATED_BY,
sysdate AS LAST_UPDATE_DATE,
apl.LAST_UPDATED_BY
from ap_pol_lines apl,
ap_pol_schedule_options pso
where apl.POLICY_ID = p_policy_id
and pso.policy_id = apl.policy_id
and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'
and OPTION_CODE IS NOT NULL
and nvl(END_DATE, SYSDATE+1) > SYSDATE
and apl.addon_mileage_rate_code is null
and not exists
( select 1
from ap_pol_lines epl
where epl.POLICY_ID = apl.policy_id
and epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
and nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
and nvl(epl.ROLE_ID, -1) = nvl(apl.role_id, -1)
and ((nvl(epl.CURRENCY_CODE, 'NULL') = nvl(apl.currency_code, 'NULL'))
or
(epl.CURRENCY_CODE is not null and apl.currency_code is null)
)
and nvl(epl.VEHICLE_CATEGORY, 'NULL') = nvl(apl.vehicle_category, 'NULL')
and nvl(epl.VEHICLE_TYPE, 'NULL') = nvl(apl.vehicle_type, 'NULL')
and nvl(epl.FUEL_TYPE, 'NULL') = nvl(apl.fuel_type, 'NULL')
and nvl(epl.RANGE_LOW, -1) = nvl(apl.range_low, -1)
and nvl(epl.RANGE_HIGH, -1) = nvl(apl.range_high, -1)
and epl.addon_mileage_rate_code = pso.option_code
);
cursor c_insert_nighrates is
select distinct
apl.POLICY_ID,
apl.SCHEDULE_PERIOD_ID,
apl.location_id,
apl.role_id,
apl.currency_code,
option_code as night_rate_type_code,
'NEW' AS STATUS
from ap_pol_lines apl,
ap_pol_schedule_options pso
where apl.POLICY_ID = p_policy_id
and pso.policy_id(+) = apl.policy_id
and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
and OPTION_CODE(+) IS NOT NULL
and nvl(END_DATE(+), SYSDATE+1) > SYSDATE
and nvl(apl.rate_type_code, 'STANDARD') = 'STANDARD';
cursor c_update_nightrates is
select
distinct
apl.POLICY_ID,
apl.SCHEDULE_PERIOD_ID,
apl.location_id,
apl.role_id,
apl.currency_code,
option_code as night_rate_type_code,
'NEW' AS STATUS
from ap_pol_lines apl,
ap_pol_schedule_options pso,
ap_pol_lines epl
where apl.POLICY_ID = p_policy_id
and pso.policy_id(+) = apl.policy_id
and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
and OPTION_CODE(+) IS NOT NULL
and nvl(END_DATE(+), SYSDATE+1) > SYSDATE
and nvl(apl.rate_type_code, 'STANDARD') = 'STANDARD'
and not exists
( select 1
from ap_pol_lines epl
where epl.POLICY_ID = apl.policy_id
and epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
and nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
and nvl(epl.ROLE_ID, -1) = nvl(apl.role_id, -1)
and ((nvl(epl.CURRENCY_CODE, 'NULL') = nvl(apl.currency_code, 'NULL'))
or
(epl.CURRENCY_CODE is not null and apl.currency_code is null)
)
and epl.rate_type_code = 'NIGHT_RATE'
and ( epl.night_rate_type_code is null or
( epl.night_rate_type_code is not null and epl.night_rate_type_code = pso.option_code )
)
);
select count(policy_line_id)
into l_policy_line_count
from ap_pol_lines
where policy_id = p_policy_id
and schedule_period_id = p_schedule_period_id
and rate_type_code = 'NIGHT_RATE';
FOR c_nightrate IN c_insert_nighrates
LOOP
insert into AP_POL_LINES
(
POLICY_LINE_ID,
POLICY_ID,
SCHEDULE_PERIOD_ID,
LOCATION_ID,
ROLE_ID,
CURRENCY_CODE,
RATE_TYPE_CODE,
NIGHT_RATE_TYPE_CODE,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
values
(
AP_POL_LINES_S.NEXTVAL,
c_nightrate.POLICY_ID,
c_nightrate.SCHEDULE_PERIOD_ID,
c_nightrate.LOCATION_ID,
c_nightrate.ROLE_ID,
c_nightrate.CURRENCY_CODE,
'NIGHT_RATE',
c_nightrate.NIGHT_RATE_TYPE_CODE,
c_nightrate.STATUS,
sysdate,
p_user_id,
sysdate,
p_user_id
);
select night_rates_code
into l_night_rates_code
from ap_pol_headers
where policy_id = p_policy_id;
delete from ap_pol_lines
where policy_id = p_policy_id
and rate_type_code = 'NIGHT_RATE'
and night_rate_type_code is not null;
/* delete from ap_pol_lines
where policy_id = p_policy_id
and rate_type_code = 'NIGHT_RATE'
and night_rate_type_code is NULL ; */
delete from ap_pol_lines
where policy_id = p_policy_id
and rate_type_code = 'NIGHT_RATE'
and (night_rate_type_code is NULL
or night_rate_type_code not in(select option_code
from ap_pol_schedule_options
where policy_id = p_policy_id));
FOR c_nightrate IN c_update_nightrates
LOOP
insert into AP_POL_LINES
(
POLICY_LINE_ID,
POLICY_ID,
SCHEDULE_PERIOD_ID,
LOCATION_ID,
ROLE_ID,
CURRENCY_CODE,
RATE_TYPE_CODE,
NIGHT_RATE_TYPE_CODE,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
values
(
AP_POL_LINES_S.NEXTVAL,
c_nightrate.POLICY_ID,
c_nightrate.SCHEDULE_PERIOD_ID,
c_nightrate.LOCATION_ID,
c_nightrate.ROLE_ID,
c_nightrate.CURRENCY_CODE,
'NIGHT_RATE',
c_nightrate.NIGHT_RATE_TYPE_CODE,
c_nightrate.STATUS,
sysdate,
p_user_id,
sysdate,
p_user_id
);
select nvl2(night_rates_code, 'Y', 'N' )
into l_night_rate_flag
from ap_pol_headers
where policy_id = p_policy_id;
select nvl(rate_period_type_code, 'STANDARD')
into l_rate_period_type_code
from ap_pol_headers
where policy_id = p_policy_id;
select count(1)
into l_first_period_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and rate_type_code= 'FIRST_PERIOD';
select nvl(rate_period_type_code, 'STANDARD')
into l_rate_period_type_code
from ap_pol_headers
where policy_id = p_policy_id;
select count(1)
into l_last_period_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and rate_type_code= 'LAST_PERIOD';
select nvl(same_day_rate_code, 'NULL')
into l_same_day_rate_code
from ap_pol_headers
where policy_id = p_policy_id;
select count(1)
into l_same_day_count
from ap_pol_schedule_options
where policy_id = p_policy_id
and rate_type_code= 'SAME_DAY';
DELETE FROM ap_pol_lines pl
WHERE pl.policy_id = p_policy_id
AND NVL(role_id, -1) <> -1
AND NOT EXISTS
( SELECT 1
FROM ap_pol_schedule_options pso
WHERE pso.policy_id = pl.policy_id
AND pso.option_type = 'EMPLOYEE_ROLE'
AND pso.option_code = pl.role_id
);
UPDATE ap_pol_lines
SET role_id = -1
WHERE policy_id = p_policy_id
AND role_id IS NULL;
INSERT INTO ap_pol_lines
( POLICY_LINE_ID,
POLICY_ID,
SCHEDULE_PERIOD_ID,
RATE_TYPE_CODE,
STATUS,
ROLE_ID,
LOCATION_ID,
CURRENCY_CODE,
RATE,
TOLERANCE,
CALCULATION_METHOD,
MEALS_DEDUCTION,
BREAKFAST_DEDUCTION,
LUNCH_DEDUCTION,
DINNER_DEDUCTION,
ONE_MEAL_DEDUCTION_AMT,
TWO_MEALS_DEDUCTION_AMT,
THREE_MEALS_DEDUCTION_AMT,
ACCOMMODATION_ADJUSTMENT,
ACCOMMODATION_CALC_METHOD,
NIGHT_RATE_TYPE_CODE,
START_OF_SEASON,
END_OF_SEASON,
MAX_LODGING_AMT,
NO_GOVT_MEALS_AMT,
PROP_MEALS_AMT,
OFF_BASE_INC_AMT,
FOOTNOTE_AMT,
FOOTNOTE_RATE_AMT,
MAX_PER_DIEM_AMT,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
SELECT AP_POL_LINES_S.NEXTVAL,
apl.POLICY_ID,
apl.SCHEDULE_PERIOD_ID,
apl.RATE_TYPE_CODE,
'NEW' as STATUS,
pso.option_code as ROLE_ID,
apl.LOCATION_ID,
apl.CURRENCY_CODE,
apl.RATE,
apl.TOLERANCE,
apl.CALCULATION_METHOD,
apl.MEALS_DEDUCTION,
apl.BREAKFAST_DEDUCTION,
apl.LUNCH_DEDUCTION,
apl.DINNER_DEDUCTION,
apl.ONE_MEAL_DEDUCTION_AMT,
apl.TWO_MEALS_DEDUCTION_AMT,
apl.THREE_MEALS_DEDUCTION_AMT,
apl.ACCOMMODATION_ADJUSTMENT,
apl.ACCOMMODATION_CALC_METHOD,
apl.NIGHT_RATE_TYPE_CODE,
apl.START_OF_SEASON,
apl.END_OF_SEASON,
apl.MAX_LODGING_AMT,
apl.NO_GOVT_MEALS_AMT,
apl.PROP_MEALS_AMT,
apl.OFF_BASE_INC_AMT,
apl.FOOTNOTE_AMT,
apl.FOOTNOTE_RATE_AMT,
apl.MAX_PER_DIEM_AMT,
apl.EFFECTIVE_START_DATE,
apl.EFFECTIVE_END_DATE,
sysdate as CREATION_DATE,
p_user_id as CREATED_BY,
p_user_id LAST_UPDATE_LOGIN,
sysdate as LAST_UPDATE_DATE,
p_user_id as LAST_UPDATED_BY
FROM ap_pol_lines apl,
ap_pol_schedule_options pso
WHERE apl.policy_id = pso.policy_id
AND apl.role_id = -1
AND pso.option_type = 'EMPLOYEE_ROLE'
AND pso.role_id IS NOT NULL
AND pso.role_id <> -1
AND nvl(pso.end_date, SYSDATE+1) > SYSDATE
AND NOT EXISTS
( SELECT 1
FROM ap_pol_lines epl
WHERE epl.POLICY_ID = apl.policy_id
AND epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
AND nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
AND nvl(epl.ROLE_ID, -1) = pso.option_code
AND nvl(epl.CURRENCY_CODE, 'NULL') = nvl(apl.currency_code, 'NULL')
);
UPDATE ap_pol_lines
SET role_id = NULL
WHERE policy_id = p_policy_id
AND role_id IS NOT NULL;
| Location Id -- Location ID to use when selecting the policy line.
| null -- otherwise.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 21-Feb-2006 albowicz Created
|
*=======================================================================*/
FUNCTION getPolicyLocationId( p_expense_type_id IN NUMBER,
p_expense_date IN DATE,
p_location_id IN NUMBER ) RETURN NUMBER IS
l_location_id AP_POL_SCHEDULE_OPTIONS.location_id%type;
SELECT LOCATION_ID
INTO l_location_id
FROM (
-- This query verifies that a given location is active within a policy
select location_id
from AP_POL_SCHEDULE_OPTIONS opts, ap_expense_report_params_all p
where p.parameter_id = p_expense_type_id
AND policy_id = p.company_policy_id
AND option_type = 'LOCATION'
AND status = 'ACTIVE'
AND (opts.end_date is null OR opts.end_date >= p_expense_date)
AND LOCATION_ID = p_location_id
UNION ALL
select opts.location_id
from AP_POL_SCHEDULE_OPTIONS opts, AP_POL_LOCATIONS_B loc1, AP_POL_LOCATIONS_B loc2, ap_expense_report_params_all p
where p.parameter_id = p_expense_type_id
AND policy_id = p.company_policy_id
AND opts.option_type = 'LOCATION'
AND opts.status = 'ACTIVE'
AND (opts.end_date is null OR opts.end_date >= p_expense_date)
AND loc1.location_id = opts.location_id
AND loc1.location_type = 'COUNTRY'
AND loc2.territory_code = loc1.territory_code
AND loc2.location_type <> 'COUNTRY'
AND loc2.location_id = p_location_id
UNION ALL
-- Will find the all other location for a given policy
select loc.location_id
from AP_POL_SCHEDULE_OPTIONS opts, AP_POL_LOCATIONS_B loc, ap_expense_report_params_all p
where p.parameter_id = p_expense_type_id
AND opts.policy_id = p.company_policy_id
AND opts.option_type = 'LOCATION'
AND opts.status = 'ACTIVE'
AND (opts.end_date is null OR opts.end_date >= p_expense_date)
AND loc.location_id = opts.location_id
AND loc.undefined_location_flag = 'Y'
)
WHERE ROWNUM = 1;
select l.POLICY_LINE_ID
INTO l_policy_line_id
from AP_POL_HEADERS h, AP_POL_LINES l, AP_POL_SCHEDULE_PERIODS sp, AP_SYSTEM_PARAMETERS sys, AP_POL_EXRATE_OPTIONS rate_opts, ap_expense_report_params_all p
where p.parameter_id = p_expense_type_id
AND h.policy_id = p.company_policy_id
AND h.category_code <> 'MILEAGE'
AND h.category_code <> 'PER_DIEM'
AND p_expense_date between h.start_date and nvl(h.end_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'))
AND l.policy_id = h.policy_id
AND l.status = 'ACTIVE'
AND l.SCHEDULE_PERIOD_ID = sp.SCHEDULE_PERIOD_ID
AND p_expense_date between sp.start_date and nvl(sp.end_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'))
AND sys.org_id = rate_opts.org_id(+)
AND (nvl(h.employee_role_flag, 'N') = 'N' OR
l.role_id = nvl((select ROLE_ID
from AP_POL_SCHEDULE_OPTIONS
where policy_id = h.policy_id
AND option_type = 'EMPLOYEE_ROLE'
AND status = 'ACTIVE'
AND (end_date is null OR end_date >= p_expense_date)
AND ROLE_ID = decode(h.role_code, 'JOB_GROUP', l_hr_assignment.job_id, 'POSITION', l_hr_assignment.position_id, 'GRADE', l_hr_assignment.grade_id, -1)), -1))
AND (nvl(h.location_flag, 'N') = 'N' OR
l.location_id = l_location_id)
AND ( (h.category_code = 'AIRFARE') OR
(l.currency_code = p_currency_code) OR
(nvl(h.allow_rate_conversion_code, 'NO_CONVERSION') = 'NO_CONVERSION' AND h.currency_code = p_currency_code) OR
(h.currency_preference = 'SRC' AND h.allow_rate_conversion_code = 'ALLOW_CONVERSION' AND
('Y' = GL_CURRENCY_API.rate_exists(p_currency_code, l.currency_code, p_expense_date, rate_opts.exchange_rate_type) OR
('Y' = GL_CURRENCY_API.rate_exists(p_currency_code, sys.base_currency_code, p_expense_date, rate_opts.exchange_rate_type) AND
'Y' = GL_CURRENCY_API.rate_exists(sys.base_currency_code, l.currency_code, p_expense_date, rate_opts.exchange_rate_type)))
) OR
(nvl(h.currency_preference, 'LCR') = 'LCR' AND ('Y' = GL_CURRENCY_API.rate_exists(p_currency_code, l.currency_code, p_expense_date, rate_opts.exchange_rate_type) OR
('Y' = GL_CURRENCY_API.rate_exists(p_currency_code, sys.base_currency_code, p_expense_date, rate_opts.exchange_rate_type) AND 'Y' = GL_CURRENCY_API.rate_exists(sys.base_currency_code, l.currency_code, p_expense_date, rate_opts.exchange_rate_type)))
)
)
-- ACC Seasonality condition.
AND (h.category_code <> 'ACCOMMODATIONS' OR l.start_of_season IS NULL OR l.end_of_season IS NULL OR
'Y' = AP_WEB_POLICY_UTILS.isDateInSeason(p_expense_date, l.start_of_season, l.end_of_season))
AND l.parent_line_id is null -- Bug: 6866388, Too Many rows fetched
AND p_expense_date between nvl(l.effective_start_date, p_expense_date) and nvl(l.effective_end_date, p_expense_date+1); -- 6994883
select nvl(rate_type_code, 'STANDARD') rate_type_code, count(1) as number_of_lines
from ap_pol_lines
where policy_id = p_policy_id
and schedule_period_id = p_schedule_id
and (status <> 'VALID' and status <> 'ACTIVE')
and addon_mileage_rate_code is null
group by rate_type_code
union all
select nvl(rate_type_code, 'ADDON') rate_type_code, count(1) as number_of_lines
from ap_pol_lines
where policy_id = p_policy_id
and schedule_period_id = p_schedule_id
and (status <> 'VALID' and status <> 'ACITVE')
and addon_mileage_rate_code is not null
group by rate_type_code;
UPDATE ap_pol_lines
SET status = 'ACTIVE'
WHERE policy_id = p_policy_id
AND schedule_period_id = p_schedule_id
AND status = 'VALID';