1 PACKAGE BODY SSP_SSPRPSAP_XMLP_PKG AS
2 /* $Header: SSPRPSAPB.pls 120.1 2007/12/24 14:06:48 amakrish noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 declare
8 l_test number;
9 begin
10 /*srw.user_exit('FND SRWINIT');*/null;
11
12 insert into fnd_sessions
13 (session_id, effective_date)
14 select userenv('sessionid'),
15 trunc(sysdate)
16 from sys.dual
17 where not exists
18 (select 1
19 from fnd_sessions fs
20 where fs.session_id = userenv('sessionid'));
21 c_business_group_name :=
22 hr_reports.get_business_group(p_business_group_id);
23
24 if p_payroll_id is not null then
25 Select distinct payroll_name into C_payroll_name
26 from pay_payrolls
27 where business_group_id = p_business_group_id
28 and payroll_id = p_payroll_id;
29 end if;
30
31 if P_person_id is not null then
32 Select distinct substr(full_name,1,80) into C_person_name
33 from per_people_f ppf
34 where person_id = P_person_id
35 and business_group_id = P_business_group_id
36 and (
37 (p_session_date between ppf.effective_start_date and ppf.effective_end_date)
38 or
39 (p_session_date > ppf.effective_end_date
40 and ppf.effective_end_date = (select max(p1.effective_end_date)
41 from per_all_people_f p1
42 where p1.person_id = ppf.person_id)
43 )
44 or
45 (p_session_date < ppf.effective_start_date
46 and ppf.effective_start_date = ppf.start_date
47 )
48 );
49
50 end if;
51 fnd_date.initialize('YYYY/MM/DD',null);
52 exception
53 when NO_DATA_FOUND then
54 null;
55 end;
56
57 return (TRUE);
58 end;
59
60 function c_sapformula(M_due_date in date, M_matching_date in date, M_PERSON_ID in number) return varchar2 is
61 begin
62
63 Declare
64 Ewc date;
65 Qw date;
66 Due_date date;
67 Avg_earnings number(10,2);
68 Begin
69 Due_date := M_due_date;
70 Ewc := M_due_date;
71 Qw := ssp_sap_pkg.MATCHING_WEEK_OF_ADOPTION(M_matching_date);
72
73 C_EWC := Ewc;
74 C_WQ := Qw;
75
76 Select average_earnings_amount into Avg_earnings
77 from ssp_earnings_calculations
78 where effective_date = Qw
79 and person_id = M_PERSON_ID;
80
81 C_AVG_EARNINGS := Avg_earnings;
82
83 return NULL;
84
85 Exception
86 When Others then null;
87
88
89
90 End;
91
92 RETURN NULL; end;
93
94 function c_processedformula(E_Element_entry_id in number) return varchar2 is
95 begin
96
97 Begin
98 if ssp_smp_support_pkg.entry_already_processed(E_Element_entry_id) then
99 return('Yes');
100 else
101 return(null);
102 end if;
103 End;
104 RETURN NULL; end;
105
106 function c_amount_processedformula(C_Processed in varchar2, E_amount in number) return number is
107 begin
108
109 Begin
110
111 If C_Processed = 'Yes' then
112 return(E_amount);
113 else
114 return(0);
115 end if;
116
117 End;
118 RETURN NULL; end;
119
120 function c_recoverable_processedformula(C_Processed in varchar2, E_recoverable in number) return number is
121 begin
122
123 Begin
124
125 If C_Processed = 'Yes' then
126 return(E_recoverable);
127 else
128 return(0);
129 End if;
130
131 End;
132 RETURN NULL; end;
133
134 function AfterReport return boolean is
135 begin
136 /*srw.user_exit('FND SRWEXIT');*/null;
137
138 return (TRUE);
139 end;
140
141 --Functions to refer Oracle report placeholders--
142
143 Function C_EWC_p return date is
144 Begin
145 return C_EWC;
146 END;
147 Function C_WQ_p return date is
148 Begin
149 return C_WQ;
150 END;
151 Function C_AVG_EARNINGS_p return number is
152 Begin
153 return C_AVG_EARNINGS;
154 END;
155 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
156 Begin
157 return C_BUSINESS_GROUP_NAME;
158 END;
159 Function C_REPORT_SUBTITLE_p return varchar2 is
160 Begin
161 return C_REPORT_SUBTITLE;
162 END;
163 Function C_PAYROLL_NAME_p return varchar2 is
164 Begin
165 return C_PAYROLL_NAME;
166 END;
167 Function C_PERSON_NAME_p return varchar2 is
168 Begin
169 return C_PERSON_NAME;
170 END;
171 END SSP_SSPRPSAP_XMLP_PKG ;