1 PACKAGE BODY SSP_SSPRPSPA_XMLP_PKG AS
2 /* $Header: SSPRPSPAB.pls 120.1 2008/01/07 14:42:30 srikrish noship $ */
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 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 NULL;
66 END;
67 RETURN (TRUE);
68 END BEFOREREPORT;
69
70 FUNCTION C_SPPAFORMULA(M_DUE_DATE IN DATE
71 ,M_MATCHING_DATE IN DATE
72 ,M_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
73 BEGIN
74 DECLARE
75 EWC DATE;
76 QW DATE;
77 DUE_DATE DATE;
78 AVG_EARNINGS NUMBER(10,2);
79 BEGIN
80 DUE_DATE := M_DUE_DATE;
81 EWC := DUE_DATE;
82 QW := SSP_PAD_PKG.MATCHING_WEEK_OF_ADOPTION(M_MATCHING_DATE);
83 C_EWC := EWC;
84 C_WQ := QW;
85 SELECT
86 AVERAGE_EARNINGS_AMOUNT
87 INTO AVG_EARNINGS
88 FROM
89 SSP_EARNINGS_CALCULATIONS
90 WHERE EFFECTIVE_DATE = QW
91 AND PERSON_ID = M_PERSON_ID;
92 C_AVG_EARNINGS := AVG_EARNINGS;
93 RETURN NULL;
94 EXCEPTION
95 WHEN OTHERS THEN
96 NULL;
97 END;
98 RETURN NULL;
99 END C_SPPAFORMULA;
100
101 FUNCTION C_PROCESSEDFORMULA(E_ELEMENT_ENTRY_ID IN NUMBER) RETURN VARCHAR2 IS
102 BEGIN
103 BEGIN
104 IF SSP_SMP_SUPPORT_PKG.ENTRY_ALREADY_PROCESSED(E_ELEMENT_ENTRY_ID) THEN
105 RETURN ('Yes');
106 ELSE
107 RETURN (NULL);
108 END IF;
109 END;
110 RETURN NULL;
111 END C_PROCESSEDFORMULA;
112
113 FUNCTION C_AMOUNT_PROCESSEDFORMULA(C_PROCESSED IN VARCHAR2
114 ,E_AMOUNT IN NUMBER) RETURN NUMBER IS
115 BEGIN
116 BEGIN
117 IF C_PROCESSED = 'Yes' THEN
118 RETURN (E_AMOUNT);
119 ELSE
120 RETURN (0);
121 END IF;
122 END;
123 RETURN NULL;
124 END C_AMOUNT_PROCESSEDFORMULA;
125
126 FUNCTION C_RECOVERABLE_PROCESSEDFORMULA(C_PROCESSED IN VARCHAR2
127 ,E_RECOVERABLE IN NUMBER) RETURN NUMBER IS
128 BEGIN
129 BEGIN
130 IF C_PROCESSED = 'Yes' THEN
131 RETURN (E_RECOVERABLE);
132 ELSE
133 RETURN (0);
134 END IF;
135 END;
136 RETURN NULL;
137 END C_RECOVERABLE_PROCESSEDFORMULA;
138
139 FUNCTION AFTERREPORT RETURN BOOLEAN IS
140 BEGIN
141 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
142 RETURN (TRUE);
143 END AFTERREPORT;
144
145 FUNCTION C_EWC_P RETURN DATE IS
146 BEGIN
147 RETURN C_EWC;
148 END C_EWC_P;
149
150 FUNCTION C_WQ_P RETURN DATE IS
151 BEGIN
152 RETURN C_WQ;
153 END C_WQ_P;
154
155 FUNCTION C_AVG_EARNINGS_P RETURN NUMBER IS
156 BEGIN
157 RETURN C_AVG_EARNINGS;
158 END C_AVG_EARNINGS_P;
159
160 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
161 BEGIN
162 RETURN C_BUSINESS_GROUP_NAME;
163 END C_BUSINESS_GROUP_NAME_P;
164
165 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
166 BEGIN
167 RETURN C_REPORT_SUBTITLE;
168 END C_REPORT_SUBTITLE_P;
169
170 FUNCTION C_PAYROLL_NAME_P RETURN VARCHAR2 IS
171 BEGIN
172 RETURN C_PAYROLL_NAME;
173 END C_PAYROLL_NAME_P;
174
175 FUNCTION C_PERSON_NAME_P RETURN VARCHAR2 IS
176 BEGIN
177 RETURN C_PERSON_NAME;
178 END C_PERSON_NAME_P;
179
180 END SSP_SSPRPSPA_XMLP_PKG;
181