1 PACKAGE pqp_schedule_calculation_pkg AUTHID CURRENT_USER AS
2 /* $Header: pqschcal.pkh 120.3 2011/12/09 11:27:37 rajganga ship $ */
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
86 -- OVERLOADED get_days_worked
87 -- Returns the number of hours worked in the given date range
88 -- Uses Default Work Pattern if Assignment does not have a WP
89 FUNCTION get_hours_worked
90 (p_assignment_id IN NUMBER
91 ,p_business_group_id IN NUMBER
92 ,p_date_start IN DATE
93 ,p_date_end IN DATE
94 ,p_error_code OUT NOCOPY NUMBER
95 ,p_error_message OUT NOCOPY VARCHAR2
96 ,p_default_wp IN VARCHAR2 DEFAULT NULL
97 ,p_override_wp IN VARCHAR2 DEFAULT NULL
98 ,p_is_assignment_wp IN NUMBER DEFAULT 0
99 ) RETURN NUMBER;
100
101 -- Returns the number of days worked in the given date range
102 -- Also returns a Table of Working Dates
103 -- Uses Default Work Pattern if Assignment does not have a WP
104 FUNCTION get_days_worked
105 (p_assignment_id IN NUMBER
106 ,p_business_group_id IN NUMBER
107 ,p_date_start IN DATE
108 ,p_date_end IN DATE
109 ,p_working_dates OUT NOCOPY t_working_dates
110 ,p_error_code OUT NOCOPY NUMBER
111 ,p_error_message OUT NOCOPY VARCHAR2
112 ,p_default_wp IN VARCHAR2 DEFAULT NULL
113 ,p_override_wp IN VARCHAR2 DEFAULT NULL
114 ) RETURN NUMBER;
115
116 -- OVERLOADED get_days_worked
117 -- Returns the number of days worked in the given date range
118 -- Uses Default Work Pattern if Assignment does not have a WP
119 FUNCTION get_days_worked
120 (p_assignment_id IN NUMBER
121 ,p_business_group_id IN NUMBER
122 ,p_date_start IN DATE
123 ,p_date_end IN DATE
124 ,p_error_code OUT NOCOPY NUMBER
125 ,p_error_message OUT NOCOPY VARCHAR2
126 ,p_default_wp IN VARCHAR2 DEFAULT NULL
127 ,p_override_wp IN VARCHAR2 DEFAULT NULL
128 ) RETURN NUMBER;
129
130 -- Return Y if the given date is a working day, N if not a working day
131 FUNCTION is_working_day
132 (p_assignment_id IN NUMBER
133 ,p_business_group_id IN NUMBER
134 ,p_date IN DATE
135 ,p_error_code OUT NOCOPY NUMBER
136 ,p_error_message OUT NOCOPY VARCHAR2
137 ,p_default_wp IN VARCHAR2 DEFAULT NULL
138 ,p_override_wp IN VARCHAR2 DEFAULT NULL
139 ) RETURN VARCHAR2;
140
141 -- Returns the date prior to the next working day(p_days+1) after adding
142 -- working days to p_date_start
143 -- Uses default work pattern if Assignment does not have a WP
144 FUNCTION add_working_days
145 (p_assignment_id IN NUMBER
146 ,p_business_group_id IN NUMBER
147 ,p_date_start IN DATE
148 ,p_days IN NUMBER
149 ,p_error_code OUT NOCOPY NUMBER
150 ,p_error_message OUT NOCOPY VARCHAR2
151 ,p_default_wp IN VARCHAR2 DEFAULT NULL
152 ,p_override_wp IN VARCHAR2 DEFAULT NULL
153 ) RETURN DATE;
154
155 -----------------------------------------------------
156 -- Returns the number of Working Days in a Workpattern
157 -- as on the effective date
158 -- it takes 2 optional parameters p_override_wp, p_default_wp
159 -- Order of precedence is Override->Assignment->Default
160 FUNCTION get_working_days_in_week
161 (p_assignment_id IN NUMBER
162 ,p_business_group_id IN NUMBER
163 ,p_effective_date IN DATE
164 ,p_default_wp IN VARCHAR2 DEFAULT NULL
165 ,p_override_wp IN VARCHAR2 DEFAULT NULL
166 ) RETURN NUMBER;
167
168 FUNCTION get_day_index_for_date
169 (p_asg_work_pattern_start_date IN DATE
170 ,p_asg_work_pattern_start_day_n IN NUMBER
171 ,p_total_days_in_work_pattern IN NUMBER
172 ,p_date_to_index IN DATE
173 ) RETURN NUMBER;
174
175 PROCEDURE load_work_pattern_into_cache
176 (p_assignment_id IN NUMBER
177 ,p_business_group_id IN NUMBER
178 ,p_date_start IN DATE
179 ,p_default_wp IN VARCHAR2 DEFAULT NULL
180 ,p_override_wp IN VARCHAR2 DEFAULT NULL
181 ,p_work_pattern_used OUT NOCOPY VARCHAR2
182 ,p_asg_work_pattern_start_day_n OUT NOCOPY BINARY_INTEGER
183 ,p_asg_work_pattern_start_date OUT NOCOPY DATE
184 ,p_date_start_day_index OUT NOCOPY BINARY_INTEGER
185 );
186
187 FUNCTION add_working_days_using_one_wp
188 (p_assignment_id IN NUMBER
189 ,p_business_group_id IN NUMBER
190 ,p_date_start IN DATE
191 ,p_working_days_to_add IN NUMBER
192 ,p_default_wp IN VARCHAR2 DEFAULT NULL
193 ,p_override_wp IN VARCHAR2 DEFAULT NULL
194 ) RETURN DATE;
195
196
197 PROCEDURE clear_cache;
198
199
200 END pqp_schedule_calculation_pkg;
[Home] [Help]