[Home] [Help]
PACKAGE: APPS.HXT_TIME_DETAIL
Source
1 PACKAGE hxt_time_detail AUTHID CURRENT_USER AS
2 /* $Header: hxttdet.pkh 120.3 2011/08/16 11:46:25 asrajago ship $ */
3
4 TYPE parent_to_rexplode_info IS RECORD (
5 parent_id NUMBER
6 );
7
8 TYPE parent_to_rexplode_table IS TABLE OF parent_to_rexplode_info
9 INDEX BY BINARY_INTEGER;
10
11 g_parent_to_re_explode parent_to_rexplode_table;
12
13
14
15 -- Bug 7359347
16 -- Global variable to hold session date.
17 g_det_session_date DATE;
18
19
20 -- Bug 10123886
21 g_holiday_sdp_paid BOOLEAN := FALSE;
22
23
24
25 TYPE re_explode_det_info IS RECORD (
26 earn_pol_id NUMBER,
27 parent_id NUMBER,
28 tim_id NUMBER,
29 date_worked DATE,
30 assignment_id NUMBER,
31 hours NUMBER,
32 time_in DATE,
33 time_out DATE,
34 element_type_id NUMBER,
35 fcl_earn_reason_code hxt_det_hours_worked.fcl_earn_reason_code%TYPE,
36 ffv_cost_center_id hxt_det_hours_worked.ffv_cost_center_id%TYPE,
37 tas_id hxt_det_hours_worked.tas_id%TYPE,
38 location_id hxt_det_hours_worked.location_id%TYPE,
39 sht_id hxt_det_hours_worked.sht_id%TYPE,
40 hrw_comment hxt_det_hours_worked.hrw_comment%TYPE,
41 ffv_rate_code_id hxt_det_hours_worked.ffv_rate_code_id%TYPE,
42 rate_multiple hxt_det_hours_worked.rate_multiple%TYPE,
43 hourly_rate hxt_det_hours_worked.hourly_rate%TYPE,
44 amount hxt_det_hours_worked.amount%TYPE,
45 fcl_tax_rule_code hxt_det_hours_worked.fcl_tax_rule_code%TYPE,
46 separate_check_flag hxt_det_hours_worked.separate_check_flag%TYPE,
47 seqno hxt_det_hours_worked.seqno%TYPE,
48 created_by hxt_det_hours_worked.created_by%TYPE,
49 creation_date hxt_det_hours_worked.creation_date%TYPE,
50 last_updated_by hxt_det_hours_worked.last_updated_by%TYPE,
51 last_update_date hxt_det_hours_worked.last_update_date%TYPE,
52 last_update_login hxt_det_hours_worked.last_update_login%TYPE,
53 effective_start_date hxt_det_hours_worked.effective_start_date%TYPE,
54 effective_end_date hxt_det_hours_worked.effective_end_date%TYPE,
55 project_id hxt_det_hours_worked.project_id%TYPE,
56 job_id hxt_det_hours_worked.job_id%TYPE,
57 STATE_NAME hxt_det_hours_worked_f.STATE_NAME%TYPE,
58 COUNTY_NAME hxt_det_hours_worked_f.COUNTY_NAME%TYPE,
59 CITY_NAME hxt_det_hours_worked_f.CITY_NAME%TYPE,
60 ZIP_CODE hxt_det_hours_worked_f.ZIP_CODE%TYPE
61 );
62
63 TYPE re_explode_det_table IS TABLE OF re_explode_det_info
64 INDEX BY BINARY_INTEGER;
65
66 g_re_explode_detail re_explode_det_table;
67
68 TYPE special_explosion IS RECORD (
69 element_type_id NUMBER,
70 hours_to_pay NUMBER
71 );
72
73 TYPE special_explosion_table IS TABLE OF special_explosion
74 INDEX BY BINARY_INTEGER;
75
76 g_special_explosion special_explosion_table;
77
78 TYPE daily_explosion IS RECORD (
79 element_type_id NUMBER,
80 hours_to_pay NUMBER,
81 earning_category hxt_pay_element_types_f_ddf_v.hxt_earning_category%TYPE
82 );
83
84 TYPE daily_explosion_table IS TABLE OF daily_explosion
85 INDEX BY BINARY_INTEGER;
86
87 g_daily_explosion daily_explosion_table;
88
89 TYPE weekly_explosion IS RECORD (
90 element_type_id NUMBER,
91 hours_to_pay NUMBER,
92 earning_category hxt_pay_element_types_f_ddf_v.hxt_earning_category%TYPE
93 );
94
95 TYPE weekly_explosion_table IS TABLE OF weekly_explosion
96 INDEX BY BINARY_INTEGER;
97
98 g_weekly_explosion weekly_explosion_table;
99
100 TYPE day_week_combo_explosion IS RECORD (
101 element_type_id NUMBER,
102 hours_to_pay NUMBER
103 );
104
105 TYPE day_week_combo_table IS TABLE OF weekly_explosion
106 INDEX BY BINARY_INTEGER;
107
108 g_dy_wk_combo_explosion day_week_combo_table;
109
110 TYPE daily_earn_category IS RECORD (
111 element_type_id NUMBER,
112 hours NUMBER,
113 earning_category hxt_pay_element_types_f_ddf_v.hxt_earning_category%TYPE
114 );
115
116 TYPE daily_earn_category_table IS TABLE OF daily_earn_category
117 INDEX BY BINARY_INTEGER;
118
119 g_daily_earn_category daily_earn_category_table;
120
121 TYPE weekly_earn_category IS RECORD (
122 element_type_id NUMBER,
123 hours NUMBER,
124 earning_category hxt_pay_element_types_f_ddf_v.hxt_earning_category%TYPE
125 );
126
127 TYPE weekly_earn_category_table IS TABLE OF weekly_earn_category
128 INDEX BY BINARY_INTEGER;
129
130 g_weekly_earn_category weekly_earn_category_table;
131 g_count NUMBER := 0;
132 g_explosion_to_use VARCHAR2 (9) := NULL;
133
134 CURSOR reg_cur (p_tim_id NUMBER)
135 IS
136 SELECT hrw.ROWID hrw_rowid, hrw.hours, hrw.date_worked, hrw.parent_id,
137 hrw.assignment_id, hrw.fcl_earn_reason_code,
138 hrw.ffv_cost_center_id, hrw.tas_id, hrw.location_id, hrw.sht_id,
139 hrw.hrw_comment, hrw.ffv_rate_code_id, hrw.rate_multiple,
140 hrw.hourly_rate, hrw.amount, hrw.fcl_tax_rule_code,
141 hrw.separate_check_flag, hrw.project_id, hrw.job_id,
142
143 -- hrw.GROUP_ID,
144 hrw.earn_pol_id
145 FROM hxt_pay_element_types_f_ddf_v eltv,
146 pay_element_types_f elt,
147 hxt_det_hours_worked hrw
148 WHERE hrw.tim_id = p_tim_id
149 AND hrw.date_worked =
150 (SELECT MAX (hrw.date_worked)
151 FROM hxt_pay_element_types_f_ddf_v eltv,
152 pay_element_types_f elt,
153 hxt_det_hours_worked hrw
154 WHERE hrw.tim_id = p_tim_id
155 AND NVL (hrw.hours, 0) > 0
156 AND elt.element_type_id = hrw.element_type_id
157 AND eltv.hxt_earning_category = 'REG'
158 AND hrw.date_worked BETWEEN elt.effective_start_date
159 AND elt.effective_end_date
160 AND eltv.element_type_id = elt.element_type_id
161 AND hrw.date_worked BETWEEN eltv.effective_start_date
162 AND eltv.effective_end_date)
163 AND NVL (hrw.hours, 0) > 0
164 AND elt.element_type_id = hrw.element_type_id
165 AND eltv.hxt_earning_category = 'REG'
166 AND hrw.date_worked BETWEEN elt.effective_start_date
167 AND elt.effective_end_date
168 AND eltv.element_type_id = elt.element_type_id
169 AND hrw.date_worked BETWEEN eltv.effective_start_date
170 AND eltv.effective_end_date;
171
172 FUNCTION generate_special (
173 p_ep_id IN NUMBER,
174 p_ep_type IN VARCHAR2,
175 p_egt_id IN NUMBER,
176 p_sdf_id IN NUMBER,
177 p_hdp_id IN NUMBER,
178 p_hol_id IN NUMBER,
179 p_sdp_id IN NUMBER,
180 p_pep_id IN NUMBER,
181 p_pip_id IN NUMBER,
182 p_sdovr_id IN NUMBER,
183 p_osp_id IN NUMBER,
184 p_hol_yn IN VARCHAR2,
185 p_person_id IN NUMBER,
186 p_location IN VARCHAR2,
187 p_id IN NUMBER,
188 p_tim_id IN NUMBER,
189 p_date_worked IN DATE,
190 p_assignment_id IN NUMBER,
191 p_hours IN NUMBER,
192 p_time_in IN DATE,
193 p_time_out IN DATE,
194 p_element_type_id IN NUMBER,
195 p_fcl_earn_reason_code IN VARCHAR2,
196 p_ffv_cost_center_id IN NUMBER,
197 p_ffv_labor_account_id IN NUMBER,
198 p_tas_id IN NUMBER,
199 p_location_id IN NUMBER,
200 p_sht_id IN NUMBER,
201 p_hrw_comment IN VARCHAR2,
202 p_ffv_rate_code_id IN NUMBER,
203 p_rate_multiple IN NUMBER,
204 p_hourly_rate IN NUMBER,
205 p_amount IN NUMBER,
206 p_fcl_tax_rule_code IN VARCHAR2,
207 p_separate_check_flag IN VARCHAR2,
208 p_seqno IN NUMBER,
209 p_created_by IN NUMBER,
210 p_creation_date IN DATE,
211 p_last_updated_by IN NUMBER,
212 p_last_update_date IN DATE,
213 p_last_update_login IN NUMBER,
214 p_start_day_of_week IN VARCHAR2,
215 p_effective_start_date IN DATE,
216 p_effective_end_date IN DATE,
217 p_project_id IN NUMBER,
218 p_job_id IN NUMBER,
219 p_pay_status IN VARCHAR2,
220 p_pa_status IN VARCHAR2,
221 p_retro_batch_id IN NUMBER,
222 p_period_start_date IN DATE,
223 p_call_adjust_abs IN VARCHAR2,
224 p_STATE_NAME IN VARCHAR2 DEFAULT NULL,
225 p_COUNTY_NAME IN VARCHAR2 DEFAULT NULL,
226 p_CITY_NAME IN VARCHAR2 DEFAULT NULL,
227 p_ZIP_CODE IN VARCHAR2 DEFAULT NULL
228 --p_GROUP_ID IN NUMBER
229 )
230 RETURN NUMBER;
231 END;