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