1 PACKAGE BODY SSP_SSPRPSPA_XMLP_PKG AS
2 /* $Header: SSPRPSPAB.pls 120.3 2010/12/24 14:53:01 pprvenka ship $ */
3
4 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
5 BEGIN
6 DECLARE
7 L_TEST NUMBER;
8 BEGIN
9 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
10 AA_P_DATE_FROM:=to_char(P_DATE_FROM,'DD-MON-YYYY');
11 AA_P_DATE_TO:=to_char(P_DATE_TO,'DD-MON-YYYY');
12 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
13 INSERT INTO FND_SESSIONS
14 (SESSION_ID
15 ,EFFECTIVE_DATE)
16 SELECT
17 USERENV('sessionid'),
18 TRUNC(SYSDATE)
19 FROM
20 SYS.DUAL
21 WHERE not exists (
22 SELECT
23 1
24 FROM
25 FND_SESSIONS FS
26 WHERE FS.SESSION_ID = USERENV('sessionid') );
27 C_BUSINESS_GROUP_NAME := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
28 IF P_PAYROLL_ID IS NOT NULL THEN
29 SELECT
30 DISTINCT
31 PAYROLL_NAME
32 INTO C_PAYROLL_NAME
33 FROM
34 PAY_PAYROLLS
35 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
36 AND PAYROLL_ID = P_PAYROLL_ID;
37 END IF;
38 IF P_PERSON_ID IS NOT NULL THEN
39 SELECT
40 DISTINCT
41 SUBSTR(FULL_NAME
42 ,1
43 ,80)
44 INTO C_PERSON_NAME
45 FROM
46 PER_PEOPLE_F PPF
47 WHERE PERSON_ID = P_PERSON_ID
48 AND BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
49 AND ( ( P_SESSION_DATE between PPF.EFFECTIVE_START_DATE
50 AND PPF.EFFECTIVE_END_DATE )
51 OR ( P_SESSION_DATE > PPF.EFFECTIVE_END_DATE
52 AND PPF.EFFECTIVE_END_DATE = (
53 SELECT
54 MAX(P1.EFFECTIVE_END_DATE)
55 FROM
56 PER_ALL_PEOPLE_F P1
57 WHERE P1.PERSON_ID = PPF.PERSON_ID ) )
58 OR ( P_SESSION_DATE < PPF.EFFECTIVE_START_DATE
59 AND PPF.EFFECTIVE_START_DATE = PPF.START_DATE ) );
60 END IF;
61 FND_DATE.INITIALIZE('YYYY/MM/DD'
62 ,NULL);
63
64 IF P_SPP_TYPE = 'A' THEN
65 C_ELEMENT_NAME := 'Additional Statutory Paternity Pay Adoption';
66 C_LEAVE_TYPE := 'AA';
67 C_VIEW_NAME := 'ssp_asppa_entries_v';
68 C_REPORT_TITLE := 'Additional Statutory Paternity Adoption Pay Report';
69 ELSIF P_SPP_TYPE = 'O' THEN
70 C_ELEMENT_NAME := 'Statutory Paternity Pay Adoption';
71 C_LEAVE_TYPE := 'PA';
72 C_VIEW_NAME := 'ssp_sppa_entries_v';
73 C_REPORT_TITLE := 'Ordinary Statutory Paternity Adoption Pay Report';
74 END IF;
75
76 EXCEPTION
77 WHEN NO_DATA_FOUND THEN
78 NULL;
79 END;
80 RETURN (TRUE);
81 END BEFOREREPORT;
82
83 FUNCTION C_SPPAFORMULA(M_DUE_DATE IN DATE
84 ,M_MATCHING_DATE IN DATE
85 ,M_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
86 BEGIN
87 DECLARE
88 EWC DATE;
89 QW DATE;
90 DUE_DATE DATE;
91 AVG_EARNINGS NUMBER(10,2);
92 BEGIN
93 DUE_DATE := M_DUE_DATE;
94 EWC := DUE_DATE;
95 QW := SSP_PAD_PKG.MATCHING_WEEK_OF_ADOPTION(M_MATCHING_DATE);
96 C_EWC := EWC;
97 C_WQ := QW;
98 SELECT
99 AVERAGE_EARNINGS_AMOUNT
100 INTO AVG_EARNINGS
101 FROM
102 SSP_EARNINGS_CALCULATIONS
103 WHERE EFFECTIVE_DATE = QW
104 AND PERSON_ID = M_PERSON_ID;
105 C_AVG_EARNINGS := AVG_EARNINGS;
106 RETURN NULL;
107 EXCEPTION
108 WHEN OTHERS THEN
109 NULL;
110 END;
111 RETURN NULL;
112 END C_SPPAFORMULA;
113
114 FUNCTION C_PROCESSEDFORMULA(E_ELEMENT_ENTRY_ID IN NUMBER) RETURN VARCHAR2 IS
115 BEGIN
116 BEGIN
117 IF SSP_SMP_SUPPORT_PKG.ENTRY_ALREADY_PROCESSED(E_ELEMENT_ENTRY_ID) THEN
118 RETURN ('Yes');
119 ELSE
120 RETURN (NULL);
121 END IF;
122 END;
123 RETURN NULL;
124 END C_PROCESSEDFORMULA;
125
126 FUNCTION C_AMOUNT_PROCESSEDFORMULA(C_PROCESSED IN VARCHAR2
127 ,E_AMOUNT IN NUMBER) RETURN NUMBER IS
128 BEGIN
129 BEGIN
130 IF C_PROCESSED = 'Yes' THEN
131 RETURN (E_AMOUNT);
132 ELSE
133 RETURN (0);
134 END IF;
135 END;
136 RETURN NULL;
137 END C_AMOUNT_PROCESSEDFORMULA;
138
139 FUNCTION C_RECOVERABLE_PROCESSEDFORMULA(C_PROCESSED IN VARCHAR2
140 ,E_RECOVERABLE IN NUMBER) RETURN NUMBER IS
141 BEGIN
142 BEGIN
143 IF C_PROCESSED = 'Yes' THEN
144 RETURN (E_RECOVERABLE);
145 ELSE
146 RETURN (0);
147 END IF;
148 END;
149 RETURN NULL;
150 END C_RECOVERABLE_PROCESSEDFORMULA;
151
152 FUNCTION AFTERREPORT RETURN BOOLEAN IS
153 BEGIN
154 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
155 RETURN (TRUE);
156 END AFTERREPORT;
157
158 FUNCTION C_EWC_P RETURN DATE IS
159 BEGIN
160 RETURN C_EWC;
161 END C_EWC_P;
162
163 FUNCTION C_WQ_P RETURN DATE IS
164 BEGIN
165 RETURN C_WQ;
166 END C_WQ_P;
167
168 FUNCTION C_AVG_EARNINGS_P RETURN NUMBER IS
169 BEGIN
170 RETURN C_AVG_EARNINGS;
171 END C_AVG_EARNINGS_P;
172
173 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
174 BEGIN
175 RETURN C_BUSINESS_GROUP_NAME;
176 END C_BUSINESS_GROUP_NAME_P;
177
178 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
179 BEGIN
180 RETURN C_REPORT_SUBTITLE;
181 END C_REPORT_SUBTITLE_P;
182
183 FUNCTION C_PAYROLL_NAME_P RETURN VARCHAR2 IS
184 BEGIN
185 RETURN C_PAYROLL_NAME;
186 END C_PAYROLL_NAME_P;
187
188 FUNCTION C_PERSON_NAME_P RETURN VARCHAR2 IS
189 BEGIN
190 RETURN C_PERSON_NAME;
191 END C_PERSON_NAME_P;
192
193 END SSP_SSPRPSPA_XMLP_PKG;
194
[Home] [Help]