[Home] [Help]
PACKAGE BODY: APPS.HXT_HXT007A_XMLP_PKG
Source
1 PACKAGE BODY HXT_HXT007A_XMLP_PKG AS
2 /* $Header: HXT007AB.pls 120.0 2007/12/03 10:34:58 amakrish noship $ */
3
4 FUNCTION CF_PAYROLLFormula return VARCHAR2 is
5 BEGIN
6 DECLARE
7 Cursor Dates_Cur Is
8 Select Effective_Start_Date, Effective_End_Date
9 From Pay_Payrolls_X
10 Where Payroll_Id = P_Payroll_Id;
11
12 CURSOR payroll_cur(ES_Date In Date, EE_Date In Date)IS
13 SELECT pay.payroll_name
14 FROM pay_payrolls_x pay
15 WHERE pay.business_group_id = (Select Business_Group_Id
16 From Pay_Payrolls_X
17 Where Payroll_Id = P_Payroll_Id
18 And Effective_Start_Date = ES_Date
19 And Effective_End_Date = EE_Date)
20 AND pay.payroll_id = p_payroll_id
21 And Effective_Start_Date = ES_Date
22 And Effective_End_Date = EE_Date;
23
24
25 l_payroll_name pay_payrolls_f.payroll_name%TYPE;
26 EStart_Date pay_payrolls_f.Effective_Start_Date%Type;
27 EEnd_Date pay_payrolls_f.Effective_Start_Date%Type;
28
29 BEGIN
30 Open Dates_Cur;
31 If Dates_Cur%NotFound Then
32 Close Dates_Cur;
33 Return Null;
34 End If;
35 Fetch Dates_Cur Into EStart_Date, EEnd_Date;
36 Close Dates_Cur;
37
38 OPEN payroll_cur(EStart_Date, EEnd_Date);
39 If Payroll_Cur%NotFound Then
40 Close Payroll_Cur;
41 Return Null;
42 End If;
43 FETCH payroll_cur INTO l_payroll_name;
44 CLOSE payroll_cur;
45
46 RETURN l_payroll_name;
47
48 EXCEPTION when no_data_found then
49 RETURN null;
50 END;
51 END;
52
53 function cf_detail_hoursformula(P_BATCH_ID in number) return number is
54
55 CURSOR details_hours_cur IS
56 SELECT SUM(det.hours)
57 FROM pay_element_types_f elt,
58 hxt_pay_element_types_f_ddf_v eltv,
59 hxt_det_hours_worked_f det,
60 hxt_timecards_f tim
61 WHERE det.element_type_id = elt.element_type_id
62 AND det.date_worked BETWEEN elt.effective_start_date AND elt.effective_end_date
63 AND elt.element_type_id = eltv.element_type_id
64 AND det.date_worked BETWEEN eltv.effective_start_date AND eltv.effective_end_date
65 AND eltv.hxt_earning_category IN ('REG','OVT','ABS')
66 AND det.retro_batch_id IS NULL
67 AND det.tim_id = tim.id
68 AND tim.batch_id = P_BATCH_ID;
69
70 l_details_hours hxt_det_hours_worked_f.hours%TYPE := NULL;
71
72 begin
73 OPEN details_hours_cur;
74 FETCH details_hours_cur INTO l_details_hours;
75 IF details_hours_cur%NOTFOUND THEN
76 CLOSE details_hours_cur;
77 RETURN 0;
78 END IF;
79 CLOSE details_hours_cur;
80
81 IF l_details_hours IS NULL THEN
82 RETURN 0;
83 ELSE
84 RETURN l_details_hours;
85 END IF;
86 end;
87
88 function cf_bee_line_hoursformula(ELEMENT_TYPE_ID in number, VALUE_1 in varchar2, VALUE_2 in varchar2, VALUE_3 in varchar2, VALUE_4 in varchar2, VALUE_5 in varchar2, VALUE_6 in varchar2, VALUE_7 in varchar2,
89 VALUE_8 in varchar2, VALUE_9 in varchar2, VALUE_10 in varchar2, VALUE_11 in varchar2, VALUE_12 in varchar2, VALUE_13 in varchar2, VALUE_14 in varchar2, VALUE_15 in varchar2) return number is
90 seq1 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
91 seq2 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
92 seq3 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
93 seq4 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
94 seq5 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
95 seq6 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
96 seq7 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
97 seq8 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
98 seq9 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
99 seq10 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
100 seq11 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
101 seq12 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
102 seq13 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
103 seq14 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
104 seq15 PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE := NULL;
105 name1 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
106 name2 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
107 name3 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
108 name4 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
109 name5 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
110 name6 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
111 name7 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
112 name8 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
113 name9 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
114 name10 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
115 name11 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
116 name12 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
117 name13 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
118 name14 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
119 name15 PAY_INPUT_VALUES_F_TL.NAME%TYPE := NULL;
120 lookup1 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
121 lookup2 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
122 lookup3 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
123 lookup4 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
124 lookup5 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
125 lookup6 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
126 lookup7 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
127 lookup8 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
128 lookup9 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
129 lookup10 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
130 lookup11 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
131 lookup12 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
132 lookup13 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
133 lookup14 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
134 lookup15 PAY_INPUT_VALUES_F.LOOKUP_TYPE%TYPE := NULL;
135 l_meaning HR_LOOKUPS.MEANING%TYPE;
136 l_value PAY_BATCH_LINES.VALUE_1%TYPE;
137 l_hours HXT_DET_HOURS_WORKED_F.HOURS%TYPE := 0;
138 l_amount VARCHAR2(80) := NULL;
139 l_dummy NUMBER;
140
141 CURSOR hours_value(p_meaning VARCHAR2, p_date DATE) IS
142 SELECT 1
143 FROM hr_lookups
144 WHERE lookup_code = 'HOURS'
145 AND meaning = p_meaning
146 AND lookup_type = 'NAME_TRANSLATIONS'
147 AND enabled_flag = 'Y'
148 AND p_date BETWEEN nvl(start_date_active, p_date) AND nvl(end_date_active, p_date);
149
150 CURSOR amount_value(p_meaning VARCHAR2, p_date DATE) IS
151 SELECT 1
152 FROM hr_lookups
153 WHERE lookup_code = 'PAY VALUE'
154 AND meaning = p_meaning
155 AND lookup_type = 'NAME_TRANSLATIONS'
156 AND enabled_flag = 'Y'
157 AND p_date BETWEEN nvl(start_date_active, p_date) AND nvl(end_date_active, p_date);
158
159 begin
160 pay_paywsqee_pkg.GET_INPUT_VALUE_DETAILS(ELEMENT_TYPE_ID,
161 P_DATE_EARNED,
162 seq1,
163 seq2,
164 seq3,
165 seq4,
166 seq5,
167 seq6,
168 seq7,
169 seq8,
170 seq9,
171 seq10,
172 seq11,
173 seq12,
174 seq13,
175 seq14,
176 seq15,
177 name1,
178 name2,
179 name3,
180 name4,
181 name5,
182 name6,
183 name7,
184 name8,
185 name9,
186 name10,
187 name11,
188 name12,
189 name13,
190 name14,
191 name15,
192 lookup1,
193 lookup2,
194 lookup3,
195 lookup4,
196 lookup5,
197 lookup6,
198 lookup7,
199 lookup8,
200 lookup9,
201 lookup10,
202 lookup11,
203 lookup12,
204 lookup13,
205 lookup14,
206 lookup15);
207
208 FOR i in 1..15 LOOP
209 IF i = 1 THEN
210 l_meaning := name1;
211 l_value := VALUE_1;
212 ELSIF i = 2 THEN
213 l_meaning := name2;
214 l_value := VALUE_2;
215 ELSIF i = 3 THEN
216 l_meaning := name3;
217 l_value := VALUE_3;
218 ELSIF i = 4 THEN
219 l_meaning := name4;
220 l_value := VALUE_4;
221 ELSIF i = 5 THEN
222 l_meaning := name5;
223 l_value := VALUE_5;
224 ELSIF i = 6 THEN
225 l_meaning := name6;
226 l_value := VALUE_6;
227 ELSIF i = 7 THEN
228 l_meaning := name7;
229 l_value := VALUE_7;
230 ELSIF i = 8 THEN
231 l_meaning := name8;
232 l_value := VALUE_8;
233 ELSIF i = 9 THEN
234 l_meaning := name9;
235 l_value := VALUE_9;
236 ELSIF i = 10 THEN
237 l_meaning := name10;
238 l_value := VALUE_10;
239 ELSIF i = 11 THEN
240 l_meaning := name11;
241 l_value := VALUE_11;
242 ELSIF i = 12 THEN
243 l_meaning := name12;
244 l_value := VALUE_12;
245 ELSIF i = 13 THEN
246 l_meaning := name13;
247 l_value := VALUE_13;
248 ELSIF i = 14 THEN
249 l_meaning := name14;
250 l_value := VALUE_14;
251 ELSIF i = 15 THEN
252 l_meaning := name15;
253 l_value := VALUE_15;
254 END IF;
255
256 OPEN hours_value(l_meaning, P_DATE_EARNED);
257 FETCH hours_value INTO l_dummy;
258 IF hours_value%FOUND THEN
259 l_hours := to_number(l_value);
260 ELSE
261 OPEN amount_value(l_meaning, P_DATE_EARNED);
262 FETCH amount_value INTO l_dummy;
263 IF amount_value%FOUND THEN
264 l_amount := l_value;
265 END IF;
266 CLOSE amount_value;
267 END IF;
268 CLOSE hours_value;
269 END LOOP;
270
271 IF l_amount IS NULL THEN
272 RETURN l_hours;
273 ELSE
274 RETURN 0;
275 END IF;
276 end;
277
278 function BeforeReport return boolean is
279 begin
280 /*SRW.USER_EXIT('FND SRWINIT');*/null;
281
282 return (TRUE);
283 end;
284
285 function AfterReport return boolean is
286 begin
287 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
288
289 return (TRUE);
290 end;
291
292 --Functions to refer Oracle report placeholders--
293
294 Function C_REPORT_SUBTITLE_p return varchar2 is
295 Begin
296 return C_REPORT_SUBTITLE;
297 END;
298 END HXT_HXT007A_XMLP_PKG ;