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