1 PACKAGE BODY SSP_SSPRPSPB_XMLP_PKG AS
2 /* $Header: SSPRPSPBB.pls 120.3 2010/12/24 14:54:19 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 = 'O' then
65 C_ELEMENT_NAME := 'Statutory Paternity Pay Birth';
66 C_LEAVE_TYPE :='PB';
67 C_VIEW_NAME := 'ssp_sppa_entries_v';
68 C_REPORT_TITLE := 'Ordinary Statutory Paternity Birth Pay Report';
69 elsif P_SPP_TYPE = 'A' then
70 C_ELEMENT_NAME :='Additional Statutory Paternity Pay Birth';
71 C_LEAVE_TYPE :='AB';
72 C_VIEW_NAME := 'ssp_asppa_entries_v';
73 C_REPORT_TITLE := 'Additional Statutory Paternity Birth 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_SPPBFORMULA(M_DUE_DATE IN DATE
84 ,M_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
85 BEGIN
86 DECLARE
87 EWC DATE;
88 QW DATE;
89 DUE_DATE DATE;
90 AVG_EARNINGS NUMBER(10,2);
91 BEGIN
92 DUE_DATE := M_DUE_DATE;
93 EWC := SSP_PAB_PKG.EXPECTED_WEEK_OF_CONFINEMENT(DUE_DATE);
94 QW := SSP_PAB_PKG.QUALIFYING_WEEK(DUE_DATE);
95 C_EWC := EWC;
96 C_WQ := QW;
97 SELECT
98 AVERAGE_EARNINGS_AMOUNT
99 INTO AVG_EARNINGS
100 FROM
101 SSP_EARNINGS_CALCULATIONS
102 WHERE EFFECTIVE_DATE = QW
103 AND PERSON_ID = M_PERSON_ID;
104 C_AVG_EARNINGS := AVG_EARNINGS;
105 RETURN NULL;
106 EXCEPTION
107 WHEN OTHERS THEN
108 NULL;
109 END;
110 RETURN NULL;
111 END C_SPPBFORMULA;
112
113 FUNCTION C_PROCESSEDFORMULA(E_ELEMENT_ENTRY_ID IN NUMBER) RETURN VARCHAR2 IS
114 BEGIN
115 BEGIN
116 IF SSP_SMP_SUPPORT_PKG.ENTRY_ALREADY_PROCESSED(E_ELEMENT_ENTRY_ID) THEN
117 RETURN ('Yes');
118 ELSE
119 RETURN (NULL);
120 END IF;
121 END;
122 RETURN NULL;
123 END C_PROCESSEDFORMULA;
124
125 FUNCTION C_AMOUNT_PROCESSEDFORMULA(C_PROCESSED IN VARCHAR2
126 ,E_AMOUNT IN NUMBER) RETURN NUMBER IS
127 BEGIN
128 BEGIN
129 IF C_PROCESSED = 'Yes' THEN
130 RETURN (E_AMOUNT);
131 ELSE
132 RETURN (0);
133 END IF;
134 END;
135 RETURN NULL;
136 END C_AMOUNT_PROCESSEDFORMULA;
137
138 FUNCTION C_RECOVERABLE_PROCESSEDFORMULA(C_PROCESSED IN VARCHAR2
139 ,E_RECOVERABLE IN NUMBER) RETURN NUMBER IS
140 BEGIN
141 BEGIN
142 IF C_PROCESSED = 'Yes' THEN
143 RETURN (E_RECOVERABLE);
144 ELSE
145 RETURN (0);
146 END IF;
147 END;
148 RETURN NULL;
149 END C_RECOVERABLE_PROCESSEDFORMULA;
150
151 FUNCTION AFTERREPORT RETURN BOOLEAN IS
152 BEGIN
153 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
154 RETURN (TRUE);
155 END AFTERREPORT;
156
157 FUNCTION C_EWC_P RETURN DATE IS
158 BEGIN
159 RETURN C_EWC;
160 END C_EWC_P;
161
162 FUNCTION C_WQ_P RETURN DATE IS
163 BEGIN
164 RETURN C_WQ;
165 END C_WQ_P;
166
167 FUNCTION C_AVG_EARNINGS_P RETURN NUMBER IS
168 BEGIN
169 RETURN C_AVG_EARNINGS;
170 END C_AVG_EARNINGS_P;
171
172 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
173 BEGIN
174 RETURN C_BUSINESS_GROUP_NAME;
175 END C_BUSINESS_GROUP_NAME_P;
176
177 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
178 BEGIN
179 RETURN C_REPORT_SUBTITLE;
180 END C_REPORT_SUBTITLE_P;
181
182 FUNCTION C_PAYROLL_NAME_P RETURN VARCHAR2 IS
183 BEGIN
184 RETURN C_PAYROLL_NAME;
185 END C_PAYROLL_NAME_P;
186
187 FUNCTION C_PERSON_NAME_P RETURN VARCHAR2 IS
188 BEGIN
189 RETURN C_PERSON_NAME;
190 END C_PERSON_NAME_P;
191
192 END SSP_SSPRPSPB_XMLP_PKG;
[Home] [Help]