[Home] [Help]
PACKAGE: APPS.HXT_BATCH_VAL
Source
1 PACKAGE hxt_batch_val AUTHID CURRENT_USER AS
2 /* $Header: hxtbtval.pkh 120.5.12020000.1 2012/06/27 09:54:16 appldev ship $ */
3 c_39315_max_hrs_exceeded CONSTANT fnd_lookup_values.lookup_code%TYPE
4 := '24_HOUR_EDIT';
5 c_39260_abs_not_asg_to_acrl CONSTANT fnd_lookup_values.lookup_code%TYPE
6 := 'ABS_NOT_ASG_TO_ACRL';
7 c_39509_accrual_exceeded CONSTANT fnd_lookup_values.lookup_code%TYPE
8 := 'ACCRUAL_EXCEEDED';
9 c_39332_day_on_hol_cal CONSTANT fnd_lookup_values.lookup_code%TYPE
10 := 'DAY_IS_HOLIDAY';
11 c_39333_hol_not_valid_on_cal CONSTANT fnd_lookup_values.lookup_code%TYPE
12 := 'HOLIDAY_NOT_VAL';
13 c_39334_hrs_chged_ne_cal_hrs CONSTANT fnd_lookup_values.lookup_code%TYPE
14 := 'HOURS_NOT_EQUAL';
15 c_39338_ins_error_occ CONSTANT fnd_lookup_values.lookup_code%TYPE
16 := 'INSERT_ERRORS';
17 c_39316_person_nf CONSTANT fnd_lookup_values.lookup_code%TYPE
18 := 'PERSON_NOT_IN_DB';
19 c_39335_exceeded_accrued_hrs CONSTANT fnd_lookup_values.lookup_code%TYPE
20 := 'PTO_CHG_ACCRUED';
21 c_39336_errs_in_batch CONSTANT fnd_lookup_values.lookup_code%TYPE
22 := 'TIMECARD_ERRORS';
23 c_39317_empl_inactive CONSTANT fnd_lookup_values.lookup_code%TYPE
24 := 'TIMECARD_INACTIVE';
25 c_39337_timecard_not_apprved CONSTANT fnd_lookup_values.lookup_code%TYPE
26 := 'TIMECARD_NOT_APPROVED';
27
28
29 -- Bug 8584436
30 -- Added the following data structures to hold token values and error messages
31 -- Values once obtained for the tokens are saved in the Assoc Arrays, so there is
32 -- no repeated querying on the tables.
33
34 TYPE ERRORREC IS RECORD
35 ( errmsg VARCHAR2(1000),
36 errtype VARCHAR2(20));
37
38 TYPE ERRORTAB IS TABLE OF ERRORREC INDEX BY VARCHAR2(50);
39
40 g_errtab ERRORTAB;
41
42
43 TYPE VARCHARASSOCARRAY IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
44
45 g_name_tab VARCHARASSOCARRAY;
46 g_accrual_plan VARCHARASSOCARRAY;
47 g_holiday_calendar VARCHARASSOCARRAY;
48 g_period_end_date VARCHARASSOCARRAY;
49 g_assig_tab VARCHARASSOCARRAY;
50
51
52 FUNCTION error_level
53 RETURN NUMBER;
54
55 PROCEDURE set_error_level (p_valid IN VARCHAR, p_msg_level IN VARCHAR2);
56
57 PROCEDURE reset_error_level;
58
59 PROCEDURE delete_prev_val_errors (p_tim_id IN hxt_timecards_f.id%TYPE);
60
61 FUNCTION errors_exist (p_tim_id IN hxt_timecards.id%TYPE)
62 RETURN BOOLEAN;
63
64 FUNCTION timecard_end_date (p_tim_id IN hxt_timecards_f.id%TYPE)
65 RETURN per_time_periods.end_date%TYPE;
66
67 FUNCTION person_effective_at_tc_end (
68 p_person_id IN per_people_f.person_id%TYPE,
69 p_tim_id IN hxt_timecards.id%TYPE
70 )
71 RETURN BOOLEAN;
72
73 PROCEDURE record_error (
74 p_batch_id IN NUMBER,
75 p_period_id IN hxt_timecards.time_period_id%TYPE,
76 p_tim_id IN hxt_timecards.id%TYPE,
77 p_error_code IN VARCHAR2
78 );
79
80 PROCEDURE person_validation (
81 p_batch_id IN NUMBER,
82 p_person_id IN hxt_timecards.for_person_id%TYPE,
83 p_period_id IN hxt_timecards.time_period_id%TYPE,
84 p_tim_id IN hxt_timecards.id%TYPE
85 );
86
87 PROCEDURE excess_pto (
88 p_batch_id IN NUMBER,
89 p_calculation_date IN hxt_sum_hours_worked_x.date_worked%TYPE,
90 p_person_id IN hxt_timecards.for_person_id%TYPE,
91 p_period_id IN hxt_timecards.time_period_id%TYPE,
92 p_tim_id IN hxt_timecards.id%TYPE
93 );
94
95 FUNCTION primary_assignment_id (
96 p_person_id IN per_people_f.person_id%TYPE,
97 p_effective_date IN DATE
98 )
99 RETURN per_all_assignments_f.assignment_id%TYPE;
100
101 FUNCTION holiday_calendar_id (
102 p_person_id IN per_people_f.person_id%TYPE,
103 p_effective_date IN DATE
104 )
105 RETURN hxt_holiday_calendars.id%TYPE;
106
107 FUNCTION holiday_calendar_id (
108 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
109 p_effective_date IN DATE
110 )
111 RETURN hxt_holiday_calendars.id%TYPE;
112
113 FUNCTION holiday_element_id (p_hol_cal_id IN hxt_holiday_calendars.id%TYPE)
114 RETURN hxt_holiday_calendars.element_type_id%TYPE;
115
116 FUNCTION day_is_holiday (
117 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
118 p_day IN DATE
119 )
120 RETURN BOOLEAN;
121
122 FUNCTION timecard_approved (
123 p_tim_id IN hxt_holiday_calendars.id%TYPE,
124 p_approver_id IN hxt_timecards_f.approv_person_id%TYPE,
125 p_source_flag IN hxt_timecards_f.auto_gen_flag%TYPE
126 )
127 RETURN BOOLEAN;
128
129 PROCEDURE tcard_approved (
130 p_batch_id IN NUMBER,
131 p_person_id IN hxt_timecards.for_person_id%TYPE,
132 p_period_id IN hxt_timecards.time_period_id%TYPE,
133 p_tim_id IN hxt_timecards.id%TYPE,
134 p_approver_id IN hxt_timecards_f.approv_person_id%TYPE,
135 p_source_flag IN hxt_timecards_f.auto_gen_flag%TYPE
136 );
137
138 FUNCTION legislation_code (
139 p_bg_id IN per_business_groups.business_group_id%TYPE
140 )
141 RETURN VARCHAR2; -- per_business_groups.legislation_code%TYPE;
142
143 FUNCTION legislation_code (
144 p_asg_id IN per_all_assignments_f.assignment_id%TYPE,
145 p_effective_date IN DATE
146 )
147 RETURN per_business_groups.legislation_code%TYPE;
148
149 FUNCTION assignment_is_active (
150 p_asg_id IN per_all_assignments_f.assignment_id%TYPE,
151 p_effective_date IN DATE
152 )
153 RETURN BOOLEAN;
154
155 PROCEDURE inactive_emp_tcard (
156 p_batch_id IN NUMBER,
157 p_person_id IN hxt_timecards.for_person_id%TYPE,
158 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
159 p_period_id IN hxt_timecards.time_period_id%TYPE,
160 p_tim_id IN hxt_timecards.id%TYPE,
161 p_day IN DATE
162 );
163
164 PROCEDURE get_holiday_info (
165 p_day IN DATE,
166 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
167 p_hol_hours OUT NOCOPY hxt_holiday_days.hours%TYPE,
168 p_hol_element_type_id OUT NOCOPY hxt_holiday_calendars.element_type_id%TYPE
169 );
170
171 PROCEDURE get_holiday_info (
172 p_person_id IN per_people_f.person_id%TYPE,
173 p_day IN DATE,
174 p_effective_date IN DATE,
175 p_hol_hours OUT NOCOPY hxt_holiday_days.hours%TYPE,
176 p_hol_element_type_id OUT NOCOPY hxt_holiday_calendars.element_type_id%TYPE
177 );
178
179 FUNCTION sum_unexploded_hours (
180 p_tim_id IN hxt_timecards.id%TYPE,
181 p_day IN hxt_det_hours_worked_f.date_worked%TYPE,
182 p_hours_type IN hxt_det_hours_worked_f.hours%TYPE DEFAULT NULL
183 )
184 RETURN NUMBER;
185
186 PROCEDURE holiday_mismatch (
187 p_batch_id IN NUMBER,
188 p_person_id IN hxt_timecards.for_person_id%TYPE,
189 p_period_id IN hxt_timecards.time_period_id%TYPE,
190 p_tim_id IN hxt_timecards.id%TYPE,
191 p_day IN DATE,
192 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
193 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
194 );
195
196 PROCEDURE holiday_valid (
197 p_batch_id IN NUMBER,
198 p_person_id IN hxt_timecards.for_person_id%TYPE,
199 p_period_id IN hxt_timecards.time_period_id%TYPE,
200 p_tim_id IN hxt_timecards.id%TYPE,
201 p_day IN DATE,
202 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
203 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
204 );
205
206 FUNCTION element_link (
207 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
208 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
209 p_effective_date IN DATE
210 )
211 RETURN pay_element_links_f.element_link_id%TYPE;
212
213 FUNCTION element_linked (
214 p_element_type_id IN pay_element_types_f.element_type_id%TYPE,
215 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
216 p_effective_date IN DATE
217 )
218 RETURN BOOLEAN;
219
220 FUNCTION valid_for_summing (
221 p_element_id IN pay_element_types_f.element_type_id%TYPE,
222 p_earnings_category IN hxt_add_elem_info_f.earning_category%TYPE,
223 p_day IN DATE,
224 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
225 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE,
226 p_valid_earn_cat1 IN hxt_add_elem_info_f.earning_category%TYPE
227 DEFAULT NULL,
228 p_valid_earn_cat2 IN hxt_add_elem_info_f.earning_category%TYPE
229 DEFAULT NULL,
230 p_valid_earn_cat3 IN hxt_add_elem_info_f.earning_category%TYPE
231 DEFAULT NULL,
232 p_valid_earn_cat4 IN hxt_add_elem_info_f.earning_category%TYPE
233 DEFAULT NULL,
234 p_valid_earn_cat5 IN hxt_add_elem_info_f.earning_category%TYPE
235 DEFAULT NULL,
236 p_valid_earn_cat6 IN hxt_add_elem_info_f.earning_category%TYPE
237 DEFAULT NULL,
238 p_valid_earn_cat7 IN hxt_add_elem_info_f.earning_category%TYPE
239 DEFAULT NULL
240 )
241 RETURN BOOLEAN;
242
243 FUNCTION sum_valid_det_hours (
244 p_tim_id IN hxt_timecards.id%TYPE,
245 p_day IN DATE,
246 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
247 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
248 )
249 RETURN NUMBER;
250
251 PROCEDURE day_over_24 (
252 p_batch_id IN NUMBER,
253 p_person_id IN hxt_timecards.for_person_id%TYPE,
254 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
255 p_period_id IN hxt_timecards.time_period_id%TYPE,
256 p_tim_id IN hxt_timecards.id%TYPE,
257 p_day IN DATE,
258 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
259 );
260
261 PROCEDURE holiday_as_reg (
262 p_batch_id IN NUMBER,
263 p_person_id IN hxt_timecards.for_person_id%TYPE,
264 p_period_id IN hxt_timecards.time_period_id%TYPE,
265 p_tim_id IN hxt_timecards.id%TYPE,
266 p_day IN DATE,
267 p_hours_type IN hxt_sum_hours_worked_f.element_type_id%TYPE,
268 p_hol_cal_id IN hxt_holiday_calendars.id%TYPE
269 );
270
271 PROCEDURE validate_tc (
272 p_batch_id IN NUMBER,
273 p_tim_id IN hxt_timecards.id%TYPE,
274 p_person_id IN hxt_timecards.for_person_id%TYPE,
275 p_period_id IN hxt_timecards.time_period_id%TYPE,
276 p_approv_person_id IN hxt_timecards.approv_person_id%TYPE,
277 p_auto_gen_flag IN hxt_timecards.auto_gen_flag%TYPE,
278 p_error_level IN OUT NOCOPY NUMBER
279 );
280
281 PROCEDURE val_batch (
282 p_batch_id IN NUMBER,
283 p_time_period_id IN NUMBER,
284 p_valid_retcode IN OUT NOCOPY NUMBER,
285 p_merge_flag IN VARCHAR2 DEFAULT '0',
286 p_merge_batches OUT NOCOPY HXT_BATCH_PROCESS.MERGE_BATCHES_TYPE_TABLE
287 );
288
289
290 -- Bug 8584436
291 -- Added the following functions to return required values for the respective token ids.
292
293 FUNCTION person_name ( p_person_id IN NUMBER)
294 RETURN VARCHAR2;
295
296 FUNCTION holiday_calendar_name ( p_hcl_id IN NUMBER)
297 RETURN VARCHAR2;
298
299 FUNCTION assignment(p_person_id IN NUMBER)
300 RETURN VARCHAR2;
301
302 FUNCTION period_end_date(p_period_id IN NUMBER)
303 RETURN VARCHAR2;
304
305
306 END hxt_batch_val;