[Home] [Help]
PACKAGE BODY: APPS.PSP_PSPENEXR_XMLP_PKG
Source
1 PACKAGE BODY PSP_PSPENEXR_XMLP_PKG AS
2 /* $Header: PSPENEXRB.pls 120.6 2007/10/29 07:22:52 amakrish noship $ */
3 --FUNCTION BEFOREREPORT(ORIENTATION IN VARCHAR2) RETURN BOOLEAN IS
4 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
5 CURSOR GL_APPLICATION_ID_CUR IS
6 SELECT
7 APPLICATION_ID
8 FROM
9 FND_APPLICATION
10 WHERE APPLICATION_SHORT_NAME = 'SQLGL';
11 CURSOR GMS_APPLICATION_ID_CUR IS
12 SELECT
13 APPLICATION_ID
14 FROM
15 FND_APPLICATION
16 WHERE APPLICATION_SHORT_NAME = 'GMS';
17 BEGIN
18 --HR_STANDARD.EVENT('BEFORE REPORT');
19 IF P_SORT_BY IS NULL THEN
20 P_SORT_BY := 'O';
21 END IF;
22 P_ACTUAL_SELECT := 'UNION ALL
23 SELECT pesl.person_id,
24 pesl.assignment_id,
25 pelh.enc_element_type_id,';
26 IF P_SORT_BY = 'O' THEN
27 P_ACTUAL_SELECT := P_ACTUAL_SELECT || ' paf.organization_id, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), NULL,';
28 P_ACTUAL_SELECT := P_ACTUAL_SELECT || ' TO_NUMBER(NULL), pesl.gl_code_combination_id, pesl.project_id, pesl.task_id, pesl.award_id, pesl.expenditure_organization_id, pesl.expenditure_type, paf.organization_id,';
29 ELSE
30 P_ACTUAL_SELECT := P_ACTUAL_SELECT || ' TO_NUMBER(NULL), pesl.gl_code_combination_id, pesl.project_id, pesl.task_id, pesl.award_id, pesl.expenditure_organization_id, pesl.expenditure_type,';
31 P_ACTUAL_SELECT := P_ACTUAL_SELECT || ' paf.organization_id, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), NULL, -999,';
32 END IF;
33 P_ACTUAL_SELECT := P_ACTUAL_SELECT || ' interface_status,
34 MIN(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), pesl.EFFECTIVE_DATE)) min_start_date,
35 MAX(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), pesl.EFFECTIVE_DATE)) max_end_date,
36 1 row_num,
37 pesl.payroll_id,';
38 IF P_SORT_BY = 'O' THEN
39 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_SORT_DESCRIPTION1FORMULA(paf.organization_id, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), NULL) CF_sort_description1,';
40 ELSE
41 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_SORT_DESCRIPTION1FORMULA(TO_NUMBER(NULL), pesl.gl_code_combination_id, pesl.project_id, pesl.task_id, pesl.award_id, pesl.expenditure_organization_id, ';
42 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.expenditure_type) CF_sort_description1,';
43 END IF;
44 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_PERSON_NAMEFORMULA(pesl.person_id,MAX(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), ';
45 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.EFFECTIVE_DATE)),MIN(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), pesl.EFFECTIVE_DATE))) CF_person_name,';
46 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_ASSIGNMENT_NUMBERFORMULA(pesl.assignment_id,MAX(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), ';
47 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.EFFECTIVE_DATE)),MIN(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE,pesl.gl_posting_override_date),''DD/MM/RRRR''),pesl.EFFECTIVE_DATE))) CF_assignment_number,';
48 IF P_SORT_BY = 'O' THEN
49 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_ELEMENT_NAMEFORMULA(pelh.enc_element_type_id,MAX(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), ';
50 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.EFFECTIVE_DATE)),MIN(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), pesl.EFFECTIVE_DATE)),';
51 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.gl_code_combination_id, pesl.project_id, pesl.task_id, pesl.award_id, pesl.expenditure_organization_id,pesl.expenditure_type,TO_NUMBER(NULL)) CF_element_name,';
52 ELSE
53 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_ELEMENT_NAMEFORMULA(pelh.enc_element_type_id,MAX(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ';
54 P_ACTUAL_SELECT := P_ACTUAL_SELECT || '''DD/MM/RRRR''), pesl.EFFECTIVE_DATE)),MIN(NVL(TO_DATE(DECODE(pesl.gl_project_flag, ''P'', pesl.GMS_POSTING_OVERRIDE_DATE, pesl.gl_posting_override_date), ''DD/MM/RRRR''), ';
55 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'pesl.EFFECTIVE_DATE)),TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL),NULL,paf.organization_id) CF_element_name,';
56 END IF;
57 P_ACTUAL_SELECT := P_ACTUAL_SELECT || 'PSP_PSPENEXR_XMLP_PKG.CF_PAYROLL_NAMEFORMULA(pesl.payroll_id) CF_payroll_name,';
58 P_ACTUAL_SELECT := P_ACTUAL_SELECT ||'
59
60 PSP_PSPENEXR_XMLP_PKG.CP_SORT_DESCRIPTION2_P CP_sort_description2
61 FROM psp_enc_summary_lines pesl,
62 psp_enc_lines_history pelh,
63 per_assignments_f paf
64 WHERE pesl.payroll_action_id = ' || P_PAYROLL_ACTION_ID || '
65 AND paf.assignment_id = pesl.assignment_id
66 AND pelh.enc_summary_line_id = pesl.superceded_line_id
67 AND pesl.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
68 --AND pesl.status_code = ''R''
69 AND interface_status IS NOT NULL
70 AND superceded_line_id IS NOT NULL
71 AND EXISTS (SELECT 1
72 FROM psp_enc_summary_lines pesl2
73 WHERE pesl2.enc_summary_line_id = pesl.superceded_line_id
74 AND pesl2.status_code = ''A'')
75 GROUP BY paf.organization_id,
76 pesl.person_id,
77 pesl.assignment_id,
78 pelh.enc_element_type_id,
79 pesl.gl_code_combination_id,
80 pesl.project_id,
81 pesl.task_id,
82 pesl.award_id,
83 pesl.expenditure_type,
84 pesl.expenditure_organization_id,
85 pesl.interface_status,
86 pesl.payroll_id';
87 --ORIENTATION := 'LANDSCAPE';
88 OPEN GL_APPLICATION_ID_CUR;
89 FETCH GL_APPLICATION_ID_CUR
90 INTO P_GL_APPLICATION_ID;
91 CLOSE GL_APPLICATION_ID_CUR;
92 OPEN GMS_APPLICATION_ID_CUR;
93 FETCH GMS_APPLICATION_ID_CUR
94 INTO P_GMS_APPLICATION_ID;
95 CLOSE GMS_APPLICATION_ID_CUR;
96 RETURN (TRUE);
97 END BEFOREREPORT;
98
99 FUNCTION CF_ELEMENT_NAMEFORMULA(ELEMENT_TYPE_ID IN NUMBER
100 ,MAX_END_DATE IN DATE
101 ,MIN_START_DATE IN DATE
102 ,SORT_OPTION22 IN NUMBER
103 ,SORT_OPTION23 IN NUMBER
104 ,SORT_OPTION24 IN NUMBER
105 ,SORT_OPTION25 IN NUMBER
106 ,SORT_OPTION26 IN NUMBER
107 ,SORT_OPTION27 IN VARCHAR2
108 ,SORT_OPTION21 IN NUMBER) RETURN CHAR IS
109 V_RETCODE NUMBER;
110 L_CHART_OF_ACCTS VARCHAR2(20);
111 CURSOR ELEMENT_NAME_CUR IS
112 SELECT
113 ELEMENT_NAME,
114 1 ORDER_BY
115 FROM
116 PAY_ELEMENT_TYPES_F PETF
117 WHERE PETF.ELEMENT_TYPE_ID = CF_ELEMENT_NAMEFORMULA.ELEMENT_TYPE_ID
118 AND PETF.EFFECTIVE_START_DATE <= MAX_END_DATE
119 AND PETF.EFFECTIVE_END_DATE >= MIN_START_DATE
120 AND ROWNUM = 1
121 UNION ALL
122 SELECT
123 ELEMENT_NAME,
124 2 ORDER_BY
125 FROM
126 PAY_ELEMENT_TYPES_F PETF
127 WHERE PETF.ELEMENT_TYPE_ID = CF_ELEMENT_NAMEFORMULA.ELEMENT_TYPE_ID
128 AND PETF.EFFECTIVE_START_DATE = (
129 SELECT
130 MAX(PETF2.EFFECTIVE_START_DATE)
131 FROM
132 PAY_ELEMENT_TYPES_F PETF2
133 WHERE PETF2.ELEMENT_TYPE_ID = CF_ELEMENT_NAMEFORMULA.ELEMENT_TYPE_ID )
134 ORDER BY
135 2;
136 L_DUMMY NUMBER;
137 L_ELEMENT_NAME PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
138 BEGIN
139 OPEN ELEMENT_NAME_CUR;
140 FETCH ELEMENT_NAME_CUR
141 INTO L_ELEMENT_NAME,L_DUMMY;
142 CLOSE ELEMENT_NAME_CUR;
143 IF (P_SORT_BY = 'O') THEN
144 IF (SORT_OPTION22 IS NOT NULL) THEN
145 V_RETCODE := PSP_GENERAL.FIND_CHART_OF_ACCTS(P_SET_OF_BOOKS_ID
146 ,L_CHART_OF_ACCTS);
147 CP_SORT_DESCRIPTION2 := FND_FLEX_EXT.GET_SEGS(APPLICATION_SHORT_NAME => 'SQLGL'
148 ,KEY_FLEX_CODE => 'GL#'
149 ,STRUCTURE_NUMBER => TO_NUMBER(L_CHART_OF_ACCTS)
150 ,COMBINATION_ID => SORT_OPTION22);
151 ELSE
152 CP_SORT_DESCRIPTION2 := PSP_GENERAL.GET_PROJECT_NUMBER(SORT_OPTION23) || ' ' || PSP_GENERAL.GET_TASK_NUMBER(SORT_OPTION24) || ' ' || PSP_GENERAL.GET_AWARD_NUMBER(SORT_OPTION25)
153 || ' ' || PSP_GENERAL.GET_ORG_NAME(SORT_OPTION26) || ' ' || SORT_OPTION27;
154 END IF;
155 ELSE
156 CP_SORT_DESCRIPTION2 := PSP_GENERAL.GET_ORG_NAME(SORT_OPTION21);
157 END IF;
158 RETURN L_ELEMENT_NAME;
159 END CF_ELEMENT_NAMEFORMULA;
160
161 FUNCTION CF_SORT_DESCRIPTION1FORMULA(SORT_OPTION11 IN NUMBER
162 ,SORT_OPTION12 IN NUMBER
163 ,SORT_OPTION13 IN NUMBER
164 ,SORT_OPTION14 IN NUMBER
165 ,SORT_OPTION15 IN NUMBER
166 ,SORT_OPTION16 IN NUMBER
167 ,SORT_OPTION17 IN VARCHAR2) RETURN CHAR IS
168 V_RETCODE NUMBER;
169 L_CHART_OF_ACCTS VARCHAR2(20);
170 BEGIN
171 IF (P_SORT_BY = 'O') THEN
172 RETURN PSP_GENERAL.GET_ORG_NAME(SORT_OPTION11);
173 ELSE
174 IF (SORT_OPTION12 IS NOT NULL) THEN
175 V_RETCODE := PSP_GENERAL.FIND_CHART_OF_ACCTS(P_SET_OF_BOOKS_ID
176 ,L_CHART_OF_ACCTS);
177 RETURN FND_FLEX_EXT.GET_SEGS(APPLICATION_SHORT_NAME => 'SQLGL'
178 ,KEY_FLEX_CODE => 'GL#'
179 ,STRUCTURE_NUMBER => TO_NUMBER(L_CHART_OF_ACCTS)
180 ,COMBINATION_ID => SORT_OPTION12);
181 ELSE
182 RETURN PSP_GENERAL.GET_PROJECT_NUMBER(SORT_OPTION13) || ' ' || PSP_GENERAL.GET_TASK_NUMBER(SORT_OPTION14) || ' ' || PSP_GENERAL.GET_AWARD_NUMBER(SORT_OPTION15) || ' ' || PSP_GENERAL.GET_ORG_NAME(SORT_OPTION16) || ' ' || SORT_OPTION17;
183 END IF;
184 END IF;
185 END CF_SORT_DESCRIPTION1FORMULA;
186
187 FUNCTION CF_SORT_BY_HEADERFORMULA RETURN CHAR IS
188 CURSOR SORT_BY_HEADER_CUR IS
189 SELECT
190 MEANING || ':'
191 FROM
192 PSP_LOOKUPS PL
193 WHERE PL.LOOKUP_TYPE = 'PSP_ENC_EXC_REP_SORT_OPTIONS'
194 AND PL.LOOKUP_CODE = P_SORT_BY;
195 CURSOR SORT_BY_DETAIL_CUR IS
196 SELECT
197 MEANING
198 FROM
199 PSP_LOOKUPS PL
200 WHERE PL.LOOKUP_TYPE = 'PSP_ENC_EXC_REP_SORT_OPTIONS'
201 AND PL.LOOKUP_CODE <> P_SORT_BY;
202 L_SORT_BY_HEADER PSP_LOOKUPS.MEANING%TYPE;
203 BEGIN
204 OPEN SORT_BY_DETAIL_CUR;
205 FETCH SORT_BY_DETAIL_CUR
206 INTO CP_SORT_BY_DETAIL;
207 CLOSE SORT_BY_DETAIL_CUR;
208 OPEN SORT_BY_HEADER_CUR;
209 FETCH SORT_BY_HEADER_CUR
210 INTO L_SORT_BY_HEADER;
211 CLOSE SORT_BY_HEADER_CUR;
212 RETURN L_SORT_BY_HEADER;
213 END CF_SORT_BY_HEADERFORMULA;
214
215 FUNCTION AFTERREPORT RETURN BOOLEAN IS
216 BEGIN
217 --HR_STANDARD.EVENT('AFTER REPORT');
218 RETURN (TRUE);
219 END AFTERREPORT;
220
221 FUNCTION CF_PAYROLL_NAMEFORMULA(PAYROLL_ID IN NUMBER) RETURN CHAR IS
222 V_PAYROLL_NAME VARCHAR2(40);
223 BEGIN
224 SELECT
225 PAYROLL_NAME
226 INTO V_PAYROLL_NAME
227 FROM
228 PAY_PAYROLLS_F
229 WHERE PAYROLL_ID = CF_PAYROLL_NAMEFORMULA.PAYROLL_ID
230 AND ROWNUM = 1;
231 RETURN (V_PAYROLL_NAME);
232 RETURN NULL;
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN
235 RETURN ('no_data_found');
236 WHEN TOO_MANY_ROWS THEN
237 RETURN ('too many rows');
238 WHEN OTHERS THEN
239 RETURN ('error');
240 END CF_PAYROLL_NAMEFORMULA;
241
242 FUNCTION CF_PERSON_NAMEFORMULA(PERSON_ID IN NUMBER
243 ,MAX_END_DATE IN DATE
244 ,MIN_START_DATE IN DATE) RETURN CHAR IS
245 L_DUMMY NUMBER;
246 L_PERSON_NAME PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
247 L_EMPLOYEE_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE;
248 CURSOR EMPLOYEE_CUR IS
249 SELECT
250 FULL_NAME,
251 EMPLOYEE_NUMBER,
252 1 ORDER_BY
253 FROM
254 PER_ALL_PEOPLE_F PAPF
255 WHERE PAPF.PERSON_ID = CF_PERSON_NAMEFORMULA.PERSON_ID
256 AND PAPF.EFFECTIVE_START_DATE <= MAX_END_DATE
257 AND PAPF.EFFECTIVE_END_DATE >= MIN_START_DATE
258 AND ROWNUM = 1
259 UNION ALL
260 SELECT
261 FULL_NAME,
262 EMPLOYEE_NUMBER,
263 2 ORDER_BY
264 FROM
265 PER_ALL_PEOPLE_F PAPF
266 WHERE PAPF.PERSON_ID = CF_PERSON_NAMEFORMULA.PERSON_ID
267 AND PAPF.EFFECTIVE_START_DATE = (
268 SELECT
269 MAX(PAPF2.EFFECTIVE_START_DATE)
270 FROM
271 PER_ALL_PEOPLE_F PAPF2
272 WHERE PAPF2.PERSON_ID = CF_PERSON_NAMEFORMULA.PERSON_ID )
273 ORDER BY
274 3;
275 BEGIN
276 OPEN EMPLOYEE_CUR;
277 FETCH EMPLOYEE_CUR
278 INTO L_PERSON_NAME,L_EMPLOYEE_NUMBER,L_DUMMY;
279 CLOSE EMPLOYEE_CUR;
280 RETURN L_PERSON_NAME;
281 END CF_PERSON_NAMEFORMULA;
282
283 FUNCTION CF_ASSIGNMENT_NUMBERFORMULA(ASSIGNMENT_ID IN NUMBER
284 ,MAX_END_DATE IN DATE
285 ,MIN_START_DATE IN DATE) RETURN CHAR IS
286 CURSOR ASSIGNMENT_CUR IS
287 SELECT
288 ASSIGNMENT_NUMBER,
289 1 ORDER_BY
290 FROM
291 PER_ALL_ASSIGNMENTS_F PAAF
292 WHERE PAAF.ASSIGNMENT_ID = CF_ASSIGNMENT_NUMBERFORMULA.ASSIGNMENT_ID
293 AND PAAF.EFFECTIVE_START_DATE <= MAX_END_DATE
294 AND PAAF.EFFECTIVE_END_DATE >= MIN_START_DATE
295 AND ROWNUM = 1
296 UNION ALL
297 SELECT
298 ASSIGNMENT_NUMBER,
299 2 ORDER_BY
300 FROM
301 PER_ALL_ASSIGNMENTS_F PAAF
302 WHERE PAAF.ASSIGNMENT_ID = CF_ASSIGNMENT_NUMBERFORMULA.ASSIGNMENT_ID
303 AND PAAF.EFFECTIVE_START_DATE = (
304 SELECT
305 MAX(PAAF2.EFFECTIVE_START_DATE)
306 FROM
307 PER_ALL_ASSIGNMENTS_F PAAF2
308 WHERE PAAF2.ASSIGNMENT_ID = CF_ASSIGNMENT_NUMBERFORMULA.ASSIGNMENT_ID )
309 ORDER BY
310 2;
311 L_ASSIGNMENT_NUMBER PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;
312 L_DUMMY NUMBER;
313 BEGIN
314 OPEN ASSIGNMENT_CUR;
315 FETCH ASSIGNMENT_CUR
316 INTO L_ASSIGNMENT_NUMBER,L_DUMMY;
317 CLOSE ASSIGNMENT_CUR;
318 RETURN L_ASSIGNMENT_NUMBER;
319 END CF_ASSIGNMENT_NUMBERFORMULA;
320
321 FUNCTION CP_SORT_DESCRIPTION2_P RETURN VARCHAR2 IS
322 BEGIN
323 RETURN CP_SORT_DESCRIPTION2;
324 END CP_SORT_DESCRIPTION2_P;
325
326 FUNCTION CP_SORT_BY_DETAIL_P RETURN VARCHAR2 IS
327 BEGIN
328 RETURN CP_SORT_BY_DETAIL;
329 END CP_SORT_BY_DETAIL_P;
330
331 END PSP_PSPENEXR_XMLP_PKG;