[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_DB_SCHLINE_PKG
Source
1 PACKAGE BODY AP_WEB_DB_SCHLINE_PKG AS
2 /* $Header: apwdbscb.pls 120.2.12000000.3 2007/09/27 07:32:10 rveliche ship $ */
3
4 --------------------------------------------------------------------------------
5 FUNCTION GetScheduleLinesCursor(
6 p_policy_id IN NUMBER,
7 p_vehicle_category_code IN VARCHAR2,
8 p_vehicle_type IN VARCHAR2,
9 p_fuel_type IN VARCHAR2,
10 p_currency_code IN VARCHAR2,
11 p_employee_id IN NUMBER,
12 p_start_expense_date IN DATE,
13 p_schedule_lines_cursor OUT NOCOPY ScheduleLinesCursor)
14 RETURN BOOLEAN IS
15 --------------------------------------------------------------------------------
16 BEGIN
17 -- 3176205: This query includes all workers except for
18 -- terminated contingent workers and terminated employees who
19 -- are now active contingent workers.
20 -- Bug: 6448540, from OIE.K a schedule can have multiple passenger rates.
21 -- passenger_flag cannot be Y.
22 OPEN p_schedule_lines_cursor FOR
23 SELECT SL.RANGE_HIGH,
24 nvl(SL.RANGE_LOW, 0),
25 SP.START_DATE,
26 SP.END_DATE,
27 SL.rate,
28 decode(SH.passengers_flag,null,0,decode(calculation_method, 'AMOUNT', NVL(SL.rate_per_passenger,0), 'PERCENT', NVL(SL.rate_per_passenger,0)/100* SL.rate, 0)) rate_per_passenger
29 FROM AP_POL_LINES SL,
30 AP_POL_HEADERS SH,
31 AP_POL_SCHEDULE_PERIODS SP,
32 PER_ALL_ASSIGNMENTS_F PF
33 WHERE SH.POLICY_ID = p_policy_id
34 AND SH.POLICY_ID = SL.POLICY_ID
35 AND (nvl(SL.ROLE_ID,0) = decode(
36 SH.EMPLOYEE_ROLE_FLAG,'Y',(decode(SH.ROLE_CODE,'JOB_GROUP', PF.job_id,
37 'POSITION',PF.position_id,
38 'GRADE', PF.grade_id,
39 0)),0)
40 OR SL.role_id = -1)
41 AND nvl(SL.VEHICLE_CATEGORY,0) = decode(
42 SH.VEHICLE_CATEGORY_FLAG,'Y', p_vehicle_category_code, nvl(SL.VEHICLE_CATEGORY,0))
43 AND nvl(SL.VEHICLE_TYPE,0) = decode(
44 SH.VEHICLE_TYPE_FLAG, 'Y', p_vehicle_type, nvl(SL.VEHICLE_TYPE,0))
45 AND nvl(SL.FUEL_TYPE,0) = decode(
46 SH.FUEL_TYPE_FLAG, 'Y', p_fuel_type, nvl(SL.FUEL_TYPE,0))
47 AND SL.CURRENCY_CODE = decode(
48 SH.CURRENCY_PREFERENCE, 'MRC', p_currency_code, SL.CURRENCY_CODE)
49 AND SL.STATUS = 'ACTIVE'
50 AND SL.SCHEDULE_PERIOD_ID = SP.SCHEDULE_PERIOD_ID
51 AND SP.POLICY_ID = SH.POLICY_ID
52 AND PF.ASSIGNMENT_ID IN ( SELECT P.ASSIGNMENT_ID
53 FROM per_employees_x P
54 WHERE P.EMPLOYEE_ID = p_employee_id
55 AND NOT AP_WEB_DB_HR_INT_PKG.isPersonCwk(P.employee_id)='Y'
56 UNION ALL
57 SELECT P.ASSIGNMENT_ID
58 FROM per_cont_workers_current_x P
59 WHERE P.PERSON_ID = p_employee_id
60 )
61 AND PF.EFFECTIVE_START_DATE <= p_start_expense_date
62 AND PF.EFFECTIVE_END_DATE >= p_start_expense_date
63 AND SP.START_DATE <= p_start_expense_date
64 AND nvl(SP.END_DATE, p_start_expense_date) >= p_start_expense_date
65 AND SL.ADDON_MILEAGE_RATE_CODE is null
66 ;
67
68 return TRUE;
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 return FALSE;
72 WHEN OTHERS THEN
73 AP_WEB_DB_UTIL_PKG.RaiseException('GetScheduleLinesCursor');
74 APP_EXCEPTION.RAISE_EXCEPTION;
75 return FALSE;
76 END GetScheduleLinesCursor;
77
78
79 --------------------------------------------------------------------------------
80 PROCEDURE getSchHeaderInfo(
81 p_policy_id IN ap_pol_headers.policy_id%TYPE,
82 p_sh_distance_uom OUT NOCOPY ap_pol_headers.distance_uom%TYPE,
83 p_sh_currency_code OUT NOCOPY ap_pol_headers.currency_code%TYPE,
84 p_sh_distance_thresholds_flag OUT NOCOPY ap_pol_headers.distance_thresholds_flag%TYPE) IS
85 --------------------------------------------------------------------------------
86 BEGIN
87
88 SELECT distance_uom,
89 currency_code,
90 distance_thresholds_flag
91 INTO p_sh_distance_uom,
92 p_sh_currency_code,
93 p_sh_distance_thresholds_flag
94 FROM AP_POL_HEADERS
95 WHERE policy_id = p_policy_id;
96
97
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 AP_WEB_DB_UTIL_PKG.RaiseException('No data found: getSchHeaderInfo' );
101 APP_EXCEPTION.RAISE_EXCEPTION;
102 WHEN OTHERS THEN
103 AP_WEB_DB_UTIL_PKG.RaiseException('getSchHeaderInfo');
104 APP_EXCEPTION.RAISE_EXCEPTION;
105 END getSchHeaderInfo;
106
107 --------------------------------------------------------------------------------
108 END AP_WEB_DB_SCHLINE_PKG;