[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYKRSOE_XMLP_PKG
Source
4 function BeforeReport return boolean is
1 PACKAGE BODY PAY_PAYKRSOE_XMLP_PKG AS
2 /* $Header: PAYKRSOEB.pls 120.0 2007/12/13 12:20:41 amakrish noship $ */
3
5 begin
6
7 /*srw.user_exit('FND SRWINIT');*/null;
8
9
10 -- Pk_GlobalVariables.GlVar_Earnings := 0;
11 -- Pk_GlobalVariables.GlVar_Deductions := 0;
12 --Pk_GlobalVariables.GlVar_Hours := 0;
13 --Pk_GlobalVariables.GlVar_Earnings_Frame_Count := 0;
14 --Pk_GlobalVariables.GlVar_Deductions_Frame_Count := 0;
15
16 GlVar_Earnings := 0;
17 GlVar_Deductions := 0;
18 GlVar_Hours := 0;
19 GlVar_Earnings_Frame_Count := 0;
20 GlVar_Deductions_Frame_Count := 0;
21
22 return (TRUE);
23 end;
24
25 function AfterReport return boolean is
26 begin
27
28 /*srw.user_exit('FND SRWEXIT');*/null;
29
30
31 return (TRUE);
32 end;
33
34 function CF_business_groupFormula return VARCHAR2 is
35 v_business_group hr_organization_units.name%type;
36
37 begin
38 v_business_group := hr_reports.get_business_group(p_business_group_id);
39 return v_business_group;
40 end;
41
42 function CF_legislation_codeFormula return VARCHAR2 is
43
44 v_legislation_code hr_organization_information.org_information9%type := null;
45
46 cursor legislation_code
47 (c_business_group_id hr_organization_information.organization_id%type) is
48
49 select org_information9
50 from hr_organization_information
51 where organization_id = c_business_group_id
52 and org_information9 is not null
53 and org_information_context = 'Business Group Information';
54 begin
55 open legislation_code (p_business_group_id);
56 fetch legislation_code into v_legislation_code;
57 close legislation_code;
58
59 return v_legislation_code;
60 end;
61
62 function cf_currency_format_maskformula(cf_legislation_code in varchar2) return varchar2 is
63
64 v_currency_code fnd_currencies.currency_code%type;
65 v_format_mask varchar2(100) := null;
66 v_field_length number(3) := 14;
67
68 cursor currency_format_mask
69 (c_territory_code in fnd_currencies.issuing_territory_code%type) is
70 select currency_code
71 from fnd_currencies
72 where issuing_territory_code = c_territory_code;
73
74 begin
75 open currency_format_mask (cf_legislation_code);
76 fetch currency_format_mask into v_currency_code;
77 close currency_format_mask;
78
79 v_format_mask := fnd_currency.get_format_mask(v_currency_code, v_field_length);
80
81 return v_format_mask;
82 end;
83
84 PROCEDURE set_currency_format_mask IS
85 BEGIN
86
87 /*SRW.SET_FORMAT_MASK(CF_currency_format_mask);*/null;
88
89
90 END;
91
92 function P_BUSINESS_GROUP_IDValidTrigge return boolean is
93 begin
94 return (TRUE);
95 end;
96
97 function BetweenPage return boolean is
98 begin
99 GlVar_Earnings := 0;
100 GlVar_Deductions := 0;
101 GlVar_Hours := 0;
102 GlVar_Earnings_Frame_Count := 0;
103 GlVar_Deductions_Frame_Count := 0;
104
105 /* Pk_GlobalVariables.GlVar_Earnings := 0;
106 Pk_GlobalVariables.GlVar_Deductions := 0;
107 Pk_GlobalVariables.GlVar_Hours := 0;
108 Pk_GlobalVariables.GlVar_Earnings_Frame_Count := 0;
109 Pk_GlobalVariables.GlVar_Deductions_Frame_Count := 0; */
110 return (TRUE);
111 end;
112
113 function leavetakenformula(Leave_Taken_Dim_Bal in varchar2, End_Date_Bal in date, Assignment_Id_Bal in number, Accrual_Plan_Id_Bal in number,
114 Start_Date_Bal in date, Payroll_Id_Bal in number, Business_Group_Id_Bal in number, Assignment_Action_Id_Bal in number) return number is
115 lvFromDate DATE;
116 lvToDate DATE;
117 lvLeaveTaken NUMBER := 0;
118 lvAccrual NUMBER;
119 lvNetEntitlement NUMBER:= 0;
120 lvDate1 DATE;
121 lvDate2 DATE;
122 lvDate3 DATE;
123 begin
124 If Leave_Taken_Dim_Bal = 'KRCTD' Then
125 lvFromDate := to_date('01-01-'||to_char(End_Date_Bal,'RRRR'),'DD-MM-RRRR');
126 lvToDate := End_Date_Bal;
127 Elsif Leave_Taken_Dim_Bal = 'KRFTD' Then
128 lvFromDate := to_date('01-04-'||to_char(End_Date_Bal,'RRRR'),'DD-MM-RRRR');
129 lvToDate := End_Date_Bal;
130 End If;
131
132 If Leave_Taken_Dim_Bal IS NOT NULL Then
133 lvLeaveTaken := Per_Accrual_Calc_Functions.Get_Absence ( p_assignment_id => Assignment_Id_Bal
134 ,p_plan_id => Accrual_Plan_Id_Bal
135 ,p_calculation_date => lvToDate
136 ,p_Start_Date => Start_Date_Bal
137 );
138
139 Per_Accrual_Calc_Functions.Get_Net_Accrual ( P_Assignment_Id =>Assignment_Id_Bal
140 ,P_Plan_Id =>Accrual_Plan_Id_Bal
141 ,P_Payroll_Id =>Payroll_Id_Bal
142 ,P_Business_Group_Id =>Business_Group_Id_Bal
143 ,P_Assignment_Action_Id =>Assignment_Action_Id_Bal
144 ,P_Calculation_Date =>lvToDate
145 ,P_Accrual_Start_Date =>lvFromDate
146 ,P_START_DATE =>lvDate1
147 ,P_END_DATE =>lvDate2
148 ,P_ACCRUAL_END_DATE =>lvDate3
149 ,P_ACCRUAL =>lvAccrual
150 ,P_NET_ENTITLEMENT =>lvNetEntitlement
151 );
152 End If;
153
154 LeaveBalance := lvNetEntitlement ;
155 CP_PERIOD := '('||Start_Date_Bal||' - '||lvToDate||')';
156 Return ( lvLeaveTaken );
157 end;
158
159 function cf_miscearningsformula(Assignment_Action_Id in number) return number is
160
161 Cursor Cur_Pay_Kr_Asg_Elements_V_E is
162 Select Amount
163 From Pay_Kr_Asg_Elements_V
164 Where Classification_Name = 'EARNINGS'
165 And Assignment_Action_Id = Assignment_Action_Id
166 Order by Processing_Priority desc;
167 lvResult_Value Pay_Kr_Asg_Elements_V.Amount%TYPE := 0;
168 lvMisc_Earnings NUMBER := 0;
169 begin
170 Open Cur_Pay_Kr_Asg_Elements_V_E ;
171 Loop
172 Fetch Cur_Pay_Kr_Asg_Elements_V_E Into lvResult_Value ;
173 Exit When Cur_Pay_Kr_Asg_Elements_V_E%NOTFOUND;
174
175 If Cur_Pay_Kr_Asg_Elements_V_E%ROWCOUNT >10 Then
176 lvMisc_Earnings := lvMisc_Earnings + lvResult_Value;
177 End IF;
178 End Loop;
179 Close Cur_Pay_Kr_Asg_Elements_V_E ;
180
181 Return ( lvMisc_Earnings );
182 End;
183
184 function cf_mischoursformula(Assignment_Action_Id in number) return number is
185
186 Cursor Cur_Pay_Kr_Asg_Elements_V_E is
187 Select Hours
188 From Pay_Kr_Asg_Elements_V
189 Where Classification_Name = 'EARNINGS'
190 And Assignment_Action_Id = Assignment_Action_Id
191 Order by Processing_Priority desc;
192 lvResult_Value Pay_Kr_Asg_Elements_V.Amount%TYPE := 0;
193 lvMisc_Hours NUMBER := 0;
194 begin
195 Open Cur_Pay_Kr_Asg_Elements_V_E ;
196 Loop
197 Fetch Cur_Pay_Kr_Asg_Elements_V_E Into lvResult_Value ;
198 Exit When Cur_Pay_Kr_Asg_Elements_V_E%NOTFOUND;
199
200 If Cur_Pay_Kr_Asg_Elements_V_E%ROWCOUNT >10 Then
201 lvMisc_Hours := lvMisc_Hours + lvResult_Value;
202 End IF;
203 End Loop;
204 Close Cur_Pay_Kr_Asg_Elements_V_E ;
205
206 Return ( lvMisc_Hours );
207 End;
208
209 function cf_miscdeductionsformula(Assignment_Action_Id in number) return number is
210
211 Cursor Cur_Pay_Kr_Asg_Elements_V_D is
212 Select Amount
213 From Pay_Kr_Asg_Elements_V
214 Where Classification_Name = 'DEDUCTIONS'
215 And Assignment_Action_Id = Assignment_Action_Id
216 Order by Processing_Priority desc;
217 lvResult_Value Pay_Kr_Asg_Elements_V.Amount%TYPE := 0;
218 lvMisc_Deductions NUMBER := 0;
219 begin
220 Open Cur_Pay_Kr_Asg_Elements_V_D ;
221 Loop
222 Fetch Cur_Pay_Kr_Asg_Elements_V_D Into lvResult_Value ;
223 Exit When Cur_Pay_Kr_Asg_Elements_V_D%NOTFOUND;
224
225 If Cur_Pay_Kr_Asg_Elements_V_D%ROWCOUNT >10 Then
226 lvMisc_Deductions := lvMisc_Deductions + lvResult_Value;
227 End IF;
228 End Loop;
229 Close Cur_Pay_Kr_Asg_Elements_V_D ;
230
231 Return ( lvMisc_Deductions );
232 End;
233
234 function AfterPForm return boolean is
235 begin
236 return (TRUE);
237 end;
238
239 function CF_Effective_DateFormula return Date is
240
241 lv_Effective_Date Pay_Payroll_Actions.Effective_Date%TYPE;
242 lv_Payroll_Name Pay_PAyrolls_F.Payroll_Name%TYPE;
243 lv_RunType_Period VARCHAR2(100);
244 lv_Bus_Place hr_organization_information.Org_Information1%TYPE;
245 lv_Assignment_Number Per_Assignments_F.Assignment_Number%TYPE;
246 lvnum NUMBER;
247 lvmesg VARCHAR2(2000);
248 lvsort1 VARCHAR2(50);
249 lvsort2 VARCHAR2(50);
250 lvsort3 VARCHAR2(50);
251 lvsort4 VARCHAR2(50);
252 Cursor Cur_Pay_Payrolls_F is
253 Select Payroll_Name
254 From Pay_Payrolls_F
255 Where Payroll_Id = P_PAYROLL_ID;
256
257 Cursor Cur_Per_Assignments_F is
258 Select Assignment_Number
259 From Per_Assignments_F
260 Where Assignment_Id = P_ASSIGNMENT_ID;
261 begin
262
263
264 Begin
265 Open Cur_Pay_Payrolls_F;
266 Fetch Cur_Pay_Payrolls_F Into lv_Payroll_Name;
267 Close Cur_Pay_Payrolls_F;
268
269 CP_Payroll_Name := lv_Payroll_Name;
270 end;
271
272
273 Begin
274 Select distinct Run_Type_Name||'-'||Period_Name||'-'||ppa.Payroll_Action_Id
275 Into lv_RunType_Period
276 From Pay_Payroll_Actions ppa
277 ,Pay_Assignment_Actions paa
278 ,Pay_Run_Types_F prt
279 ,Per_Time_Periods ptp
280 ,Pay_Payroll_Actions rppa
281 ,Pay_Assignment_Actions rpaa
282 ,Pay_Action_Interlocks pai
283 Where ppa.Payroll_Action_Id = P_TIME_PERIOD_ID And rppa.Payroll_Id = P_PAYROLL_ID
284 And ppa.Payroll_Action_Id = paa.Payroll_Action_Id
285 And ppa.action_type IN ('U','P')
286 And ppa.action_status = 'C'
287 And rppa.Payroll_Action_Id = rpaa.Payroll_Action_Id
288 And rppa.action_type IN ('R','Q')
289 And rppa.action_status = 'C'
290 And pai.Locking_Action_Id = paa.Assignment_Action_Id
291 And pai.Locked_Action_Id = rpaa.Assignment_Action_Id
292 And rpaa.Run_Type_Id = prt.Run_Type_Id
293 And pai.Locked_Action_Id = ( Select max(Locked_Action_Id) Locked_Action_Id
294 From Pay_Action_Interlocks ai
295 where ai.Locking_Action_Id = pai.Locking_Action_Id )
296 And rppa.Payroll_Id = ptp.Payroll_Id
297 And rppa.Effective_Date Between ptp.Start_Date
298 And ptp.End_Date
299 And rppa.Effective_Date Between prt.Effective_Start_Date
300 And prt.Effective_End_Date ;
301
302
306 CP_RunType_Period := null;
303 CP_RunType_Period := lv_RunType_Period;
304 Exception
305 When Others Then
307 End;
308
309
310 Begin
311 Select Org_Information1
312 Into lv_Bus_Place
313 From hr_organization_information
314 Where org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
315 And Organization_Id = P_ESTABLISHMENT_ID;
316
317 CP_Bus_Place := lv_Bus_Place;
318 Exception
319 When Others Then
320 CP_Bus_Place := NULL;
321 End;
322
323
324 Begin
325 Open Cur_Per_Assignments_F;
326 Fetch Cur_Per_Assignments_F Into lv_Assignment_Number;
327 Close Cur_Per_Assignments_F;
328
329 CP_ASSIGN_NUM := lv_Assignment_Number;
330 End;
331
332
333
334 Begin
335 Select count(*)
336 Into lvnum
337 From ( Select distinct paa.Run_Type_Id
338 From Pay_Assignment_Actions paa
339 ,Pay_Action_Interlocks pai
340 ,Pay_Payroll_Actions pa
341 ,Pay_Assignment_Actions aa
342 Where pa.Payroll_Action_Id = aa.Payroll_Action_Id
343 And pa.Action_Type IN ('P','U')
344 And pai.Locked_Action_Id = paa.Assignment_Action_Id
345 And pai.Locking_Action_Id = aa.Assignment_Action_Id
346 And paa.Run_Type_Id IS NOT NULL
347 And pa.Payroll_Action_Id = P_TIME_PERIOD_ID );
348
349 If lvnum > 1 Then
350 FND_MESSAGE.SET_NAME('PAY','PAY_KR_RUN_TYPE_WARNING_MESG');
351 lvmesg := FND_MESSAGE.GET;
352 CP_Warning_Message := 'Warning :'||lvmesg;
353 Else
354 CP_Warning_Message := NULL ;
355 End If;
356 End;
357
358 Begin
359 If P_SORT_ORDER_1 = 'FULL_NAME' Then
363 End If;
360 lvsort1 := 'Full Name';
361 Elsif P_SORT_ORDER_1 = 'ESTABLISHMENT_ID' Then
362 lvsort1 := 'Business Place';
364
365 If P_SORT_ORDER_2 = 'FULL_NAME' Then
366 lvsort2 := ',Full Name';
367 Elsif P_SORT_ORDER_2 = 'ESTABLISHMENT_ID' Then
368 lvsort2 := ',Business Place';
369 End If;
370
371
372
373 CP_SORT_OPTION := lvsort1||lvsort2;
374
375
376 Exception
377 When Others Then
378 CP_SORT_OPTION := null;
379 End;
380
381
382
383 Begin
384 Select Effective_Date
385 Into lv_Effective_Date
386 From Pay_Payroll_Actions
387 Where Payroll_Action_Id = P_TIME_PERIOD_ID;
388 return ( lv_Effective_Date );
389 Exception
390 When Others Then
391 Return null;
392 End;
393
394 end;
395
396 function cf_messageflagformula(Payroll_Action_Id_Payroll in number) return number is
397
398 lvFlagNum NUMBER := 0;
399 begin
400 Select decode(Pay_Advice_Message,null,0,1)
401 Into lvFlagNum
402 From Pay_Payroll_Actions
403 Where Payroll_Action_Id = Payroll_Action_Id_Payroll;
404
405 return ( lvFlagNum );
406 exception
407 When Others Then
408 lvFlagNum := 0;
409 return ( lvFlagNum );
410 end;
411
412 --Functions to refer Oracle report placeholders--
413
414 Function LeaveBalance_p return number is
415 Begin
416 return LeaveBalance;
417 END;
418 Function CP_PERIOD_p return varchar2 is
419 Begin
420 return CP_PERIOD;
421 END;
422 Function CP_Payroll_Name_p return varchar2 is
423 Begin
424 return CP_Payroll_Name;
425 END;
426 Function CP_RunType_Period_p return varchar2 is
427 Begin
428 return CP_RunType_Period;
429 END;
430 Function CP_BUS_PLACE_p return varchar2 is
431 Begin
432 return CP_BUS_PLACE;
433 END;
434 Function CP_Assign_Num_p return varchar2 is
435 Begin
436 return CP_Assign_Num;
437 END;
438 Function CP_Warning_Message_p return varchar2 is
439 Begin
440 return CP_Warning_Message;
441 END;
442 Function CP_SORT_OPTION_p return varchar2 is
443 Begin
444 return CP_SORT_OPTION;
445 END;
446 END PAY_PAYKRSOE_XMLP_PKG ;