1 PACKAGE pqp_schedule_calculation_pkg AS
2 /* $Header: pqschcal.pkh 120.1.12000000.1 2007/01/16 04:34:10 appldev noship $ */
3
4 TYPE t_working_dates IS TABLE OF DATE
5 INDEX BY BINARY_INTEGER;
6
7 TYPE r_work_pattern IS RECORD
8 (hours NUMBER
9 ,next_working_day_index BINARY_INTEGER
10 ,days_to_next_working_day NUMBER
11 );
12
13 TYPE t_work_pattern_cache_type IS TABLE OF r_work_pattern
14 INDEX BY BINARY_INTEGER;
15
16
17
18 CURSOR c_wp_dets
19 (p_assignment_id NUMBER
20 ,p_start_date DATE
21 ,p_end_date DATE
22 )
23 IS
24 SELECT *
25 FROM pqp_assignment_attributes_f
26 WHERE assignment_id = p_assignment_id
27 AND (
28 (p_start_date BETWEEN effective_start_date
29 AND effective_end_date)
30 OR
31 (p_end_date BETWEEN effective_start_date
32 AND effective_end_date)
33 OR
34 (effective_start_date BETWEEN p_start_date
35 AND p_end_date)
36 OR
37 (effective_end_date BETWEEN p_start_date
38 AND p_end_date)
39 )
40 ORDER BY effective_start_date;
41
42
43 CURSOR c_wp_dets_up(p_assignment_id NUMBER
44 ,p_start_date DATE
45 ) IS
46 SELECT *
47 FROM pqp_assignment_attributes_f
48 WHERE assignment_id = p_assignment_id
49 AND (
50 (p_start_date BETWEEN effective_start_date
51 AND effective_end_date)
52 OR
53 (effective_start_date > p_start_date)
54 )
55 ORDER BY effective_start_date;
56
57 CURSOR c_get_legcode
58 (p_business_group_id NUMBER
59 )IS
60 SELECT legislation_code
61 FROM per_business_groups_perf
62 WHERE business_group_id = p_business_group_id;
63
64
65 -- Global variables
66 g_udt_name VARCHAR2(50) := 'PQP_COMPANY_WORK_PATTERNS';
67 g_default_start_day VARCHAR2(10) := 'sunday';
68 g_override_work_pattern pay_user_columns.user_column_name%TYPE;
69
70 PROCEDURE get_day_dets
71 (p_wp_dets IN c_wp_dets%ROWTYPE
72 ,p_calc_stdt IN DATE
73 ,p_calc_edt IN DATE
74 ,p_day_no OUT NOCOPY NUMBER
75 ,p_days_in_wp OUT NOCOPY NUMBER
76 );
77
78 -- Returns the number of hours worked in the given date range
79 FUNCTION calculate_time_worked
80 (p_assignment_id IN NUMBER
81 ,p_date_start IN DATE
82 ,p_date_end IN DATE
83 ) RETURN NUMBER;
84
85 -- Returns the number of hours worked in the given date range
86 -- Uses Default Work Pattern if Assignment does not have a WP
87 FUNCTION get_hours_worked
88 (p_assignment_id IN NUMBER
89 ,p_business_group_id IN NUMBER
90 ,p_date_start IN DATE
91 ,p_date_end IN DATE
92 ,p_error_code OUT NOCOPY NUMBER
93 ,p_error_message OUT NOCOPY VARCHAR2
94 ,p_default_wp IN VARCHAR2 DEFAULT NULL
95 ,p_override_wp IN VARCHAR2 DEFAULT NULL
96 ,p_is_assignment_wp IN BOOLEAN DEFAULT FALSE
97 ) RETURN NUMBER;
98
99 -- Returns the number of days worked in the given date range
100 -- Also returns a Table of Working Dates
101 -- Uses Default Work Pattern if Assignment does not have a WP
102 FUNCTION get_days_worked
103 (p_assignment_id IN NUMBER
104 ,p_business_group_id IN NUMBER
105 ,p_date_start IN DATE
106 ,p_date_end IN DATE
107 ,p_working_dates OUT NOCOPY t_working_dates
108 ,p_error_code OUT NOCOPY NUMBER
109 ,p_error_message OUT NOCOPY VARCHAR2
110 ,p_default_wp IN VARCHAR2 DEFAULT NULL
111 ,p_override_wp IN VARCHAR2 DEFAULT NULL
112 ) RETURN NUMBER;
113
114 -- OVERLOADED get_days_worked
115 -- Returns the number of days worked in the given date range
116 -- Uses Default Work Pattern if Assignment does not have a WP
117 FUNCTION get_days_worked
118 (p_assignment_id IN NUMBER
119 ,p_business_group_id IN NUMBER
120 ,p_date_start IN DATE
121 ,p_date_end IN DATE
122 ,p_error_code OUT NOCOPY NUMBER
123 ,p_error_message OUT NOCOPY VARCHAR2
124 ,p_default_wp IN VARCHAR2 DEFAULT NULL
125 ,p_override_wp IN VARCHAR2 DEFAULT NULL
126 ) RETURN NUMBER;
127
128 -- Return Y if the given date is a working day, N if not a working day
129 FUNCTION is_working_day
130 (p_assignment_id IN NUMBER
131 ,p_business_group_id IN NUMBER
132 ,p_date IN DATE
133 ,p_error_code OUT NOCOPY NUMBER
134 ,p_error_message OUT NOCOPY VARCHAR2
135 ,p_default_wp IN VARCHAR2 DEFAULT NULL
136 ,p_override_wp IN VARCHAR2 DEFAULT NULL
137 ) RETURN VARCHAR2;
138
139 -- Returns the date prior to the next working day(p_days+1) after adding
140 -- working days to p_date_start
141 -- Uses default work pattern if Assignment does not have a WP
142 FUNCTION add_working_days
143 (p_assignment_id IN NUMBER
144 ,p_business_group_id IN NUMBER
145 ,p_date_start IN DATE
146 ,p_days IN NUMBER
147 ,p_error_code OUT NOCOPY NUMBER
148 ,p_error_message OUT NOCOPY VARCHAR2
149 ,p_default_wp IN VARCHAR2 DEFAULT NULL
150 ,p_override_wp IN VARCHAR2 DEFAULT NULL
151 ) RETURN DATE;
152
153 -----------------------------------------------------
154 -- Returns the number of Working Days in a Workpattern
155 -- as on the effective date
156 -- it takes 2 optional parameters p_override_wp, p_default_wp
157 -- Order of precedence is Override->Assignment->Default
158 FUNCTION get_working_days_in_week
159 (p_assignment_id IN NUMBER
160 ,p_business_group_id IN NUMBER
161 ,p_effective_date IN DATE
162 ,p_default_wp IN VARCHAR2 DEFAULT NULL
163 ,p_override_wp IN VARCHAR2 DEFAULT NULL
164 ) RETURN NUMBER;
165
166 FUNCTION get_day_index_for_date
167 (p_asg_work_pattern_start_date IN DATE
168 ,p_asg_work_pattern_start_day_n IN NUMBER
169 ,p_total_days_in_work_pattern IN NUMBER
170 ,p_date_to_index IN DATE
171 ) RETURN NUMBER;
172
173 PROCEDURE load_work_pattern_into_cache
174 (p_assignment_id IN NUMBER
175 ,p_business_group_id IN NUMBER
176 ,p_date_start IN DATE
177 ,p_default_wp IN VARCHAR2 DEFAULT NULL
178 ,p_override_wp IN VARCHAR2 DEFAULT NULL
179 ,p_work_pattern_used OUT NOCOPY VARCHAR2
180 ,p_asg_work_pattern_start_day_n OUT NOCOPY BINARY_INTEGER
181 ,p_asg_work_pattern_start_date OUT NOCOPY DATE
182 ,p_date_start_day_index OUT NOCOPY BINARY_INTEGER
183 );
184
185 FUNCTION add_working_days_using_one_wp
186 (p_assignment_id IN NUMBER
187 ,p_business_group_id IN NUMBER
188 ,p_date_start IN DATE
189 ,p_working_days_to_add IN NUMBER
190 ,p_default_wp IN VARCHAR2 DEFAULT NULL
191 ,p_override_wp IN VARCHAR2 DEFAULT NULL
192 ) RETURN DATE;
193
194
195 PROCEDURE clear_cache;
196
197
198 END pqp_schedule_calculation_pkg;