[Home] [Help]
PACKAGE BODY: APPS.PSP_XMLGEN
Source
1 PACKAGE BODY PSP_XMLGEN AS
2 /* $Header: PSPXMLGB.pls 120.29.12010000.5 2008/08/05 10:16:56 ubhat ship $ */
3
4 g_request_id NUMBER(15, 0);
5
6 FUNCTION generate_approver_header_xml (p_request_id IN NUMBER DEFAULT NULL) RETURN CLOB IS
7 qryCtx1 dbms_xmlgen.ctxType;
8 query1 varchar2(4000);
9 xmlresult1 CLOB;
10 l_xml CLOB DEFAULT empty_clob();
11 l_resultOffset int;
12 l_icx_date_format VARCHAR2(20);
13 l_gl_sob NUMBER;
14
15 CURSOR get_sob_cur IS
16 SELECT set_of_books_id
17 FROM psp_report_templates_h
18 WHERE request_id = p_request_id;
19
20 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
21 SELECT 1
22 FROM psp_report_errors
23 WHERE request_id = p_request_id
24 AND message_level = 'E'
25 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
26 AND pdf_request_id = g_request_id;
27
28 CURSOR user_name_cur IS
29 SELECT user_name
30 FROM fnd_user fu,
31 fnd_concurrent_requests fcr
32 WHERE fu.user_id = fcr.requested_by
33 AND fcr.request_id = p_request_id;
34
35 l_user_name fnd_user.user_name%TYPE;
36 l_error_count NUMBER;
37 l_return_status CHAR(1);
38 l_language_code VARCHAR2(30);
39 BEGIN
40 g_request_id := fnd_global.conc_request_id;
41 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_icx_date_format);
42 l_language_code := USERENV('LANG');
43
44 OPEN get_sob_cur;
45 FETCH get_sob_cur INTO l_gl_sob;
46 CLOSE get_sob_cur;
47
48 OPEN user_name_cur;
49 FETCH user_name_cur INTO l_user_name;
50 CLOSE user_name_cur;
51
52 query1 := 'select xtt.template_name report_layout, prth.template_name, '
53 || 'TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_2)), ''' || l_icx_date_format
54 || ''') start_date, TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_3)), '''
55 || l_icx_date_format || ''') end_date, SUBSTR(prth.report_template_code, 6, 3) layout_type, flv1.meaning sort_option1, '
56 || 'flv2.meaning order_by1, flv3.meaning sort_option2, flv4.meaning order_by2, '
57 || 'flv5.meaning sort_option3, flv6.meaning order_by3, flv7.meaning sort_option4, flv8.meaning order_by4, '
58 || 'DECODE(prth.initiator_person_id, -1, ''' || l_user_name || ''', '
59 || 'psp_general.get_person_name(prth.initiator_person_id, TRUNC(SYSDATE))) initiated_by, '
60 || 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') run_date '
61 || 'FROM psp_report_templates_h prth, xdo_templates_tl xtt, '
62 || '(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, ' || TO_CHAR(l_gl_sob) || ')) flv1, '
63 || '(select * from fnd_lookup_values where language = ''' || l_language_code || ''' AND lookup_type = ''PSP_ORDERING_CRITERIA'') flv2, '
64 || '(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, ' || TO_CHAR(l_gl_sob) || ')) flv3, '
65 || '(select * from fnd_lookup_values where language = ''' || l_language_code || ''' AND lookup_type = ''PSP_ORDERING_CRITERIA'') flv4, '
66 || '(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, ' || TO_CHAR(l_gl_sob) || ')) flv5, '
67 || '(select * from fnd_lookup_values where language = ''' || l_language_code || ''' AND lookup_type = ''PSP_ORDERING_CRITERIA'') flv6, '
68 || '(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, ' || TO_CHAR(l_gl_sob) || ')) flv7, '
69 || '(select * from fnd_lookup_values where language = ''' || l_language_code || ''' AND lookup_type = ''PSP_ORDERING_CRITERIA'') flv8 WHERE '
70 || 'prth.request_id = ' || TO_CHAR(p_request_id)
71 || 'AND flv1.lookup_code = prth.parameter_value_5 '
72 || 'AND flv2.lookup_code = prth.parameter_value_6 '
73 || 'AND flv3.lookup_code = prth.parameter_value_7 '
74 || 'AND flv4.lookup_code = prth.parameter_value_8 '
75 || 'AND flv5.lookup_code (+) = prth.parameter_value_9 '
76 || 'AND flv6.lookup_code (+) = prth.parameter_value_10 '
77 || 'AND flv7.lookup_code (+) = prth.parameter_value_11 '
78 || 'AND flv8.lookup_code (+) = prth.parameter_value_12 '
79 || 'AND xtt.language = ''' || l_language_code || ''' '
80 || 'AND xtt.template_code = prth.report_template_code AND xtt.application_short_name = ''PSP''';
81 qryCtx1 := dbms_xmlgen.newContext(query1);
82 dbms_xmlgen.setRowTag(qryCtx1, NULL);
83 dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
84 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
85 dbms_xmlgen.closecontext(qryctx1);
86 l_xml := xmlresult1;
87 dbms_lob.write(l_xml, length('<?xml version="1.0" ?> '), 1, '<?xml version="1.0" ?> ');
88 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
89 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, length('<?xml version="1.0" ?> '), l_resultOffset +1);
90 RETURN l_xml;
91 EXCEPTION
92 WHEN OTHERS THEN
93 OPEN add_report_error_cur(sqlerrm);
94 FETCH add_report_error_cur INTO l_error_count;
95 CLOSE add_report_error_cur;
96
97 IF (NVL(l_error_count, 0) = 0) THEN
98 psp_general.add_report_error
99 (p_request_id => p_request_id,
100 p_message_level => 'E',
101 p_source_id => NULL,
102 p_retry_request_id => NULL,
103 p_pdf_request_id => g_request_id,
104 p_error_message => sqlerrm,
105 p_return_status => l_return_status);
106 END IF;
107 RAISE;
108 END generate_approver_header_xml;
109
110 FUNCTION generate_approver_xml (p_wf_item_key IN NUMBER,
111 p_request_id IN NUMBER DEFAULT NULL) RETURN CLOB IS
112 qryCtx1 dbms_xmlgen.ctxType;
113 query1 varchar2(4000);
114 query2 varchar2(4000);
115 xmlresult1 CLOB;
116 l_xml CLOB DEFAULT empty_clob();
117 l_person_xml CLOB DEFAULT empty_clob();
118 l_resultOffset int;
119
120 l_organization_id NUMBER;
121 l_template_id NUMBER;
122 l_sort_option1 VARCHAR2(1000);
123 l_sort_option2 VARCHAR2(1000);
124 l_criteria_value1 VARCHAR2(30);
125 l_emp_sort_option VARCHAR2(1000);
126 l_sort_option_desc1 VARCHAR2(1000);
127 l_sort_option_desc2 VARCHAR2(1000);
128 l_order_by_desc1 VARCHAR2(100);
129 l_order_by_desc2 VARCHAR2(100);
130 l_layout_type VARCHAR2(100);
131 l_request_id NUMBER(15);
132 l_set_of_books_id NUMBER;
133 l_segment_delimiter CHAR(1);
134 l_gl_header VARCHAR2(1000);
135 l_segment_header VARCHAR2(200);
136 l_investigator_id NUMBER(15);
137 l_investigator_name psp_eff_report_details.investigator_name%TYPE;
138 l_investigator_org_name psp_eff_report_details.investigator_org_name%TYPE;
139 l_investigator_primary_org_id psp_eff_report_details.investigator_primary_org_id%TYPE;
140 l_total_pi_proposed_salary VARCHAR2(50);
141 l_total_pi_actual_salary VARCHAR2(50);
142 l_display_flag psp_report_templates_h.display_all_emp_distrib_flag%TYPE;
143 l_report_info VARCHAR2(4000);
144 l_icx_date_format VARCHAR2(20);
145
146 CURSOR sort_option_cur (p_request_id IN NUMBER) IS
147 SELECT DISTINCT prtdh.criteria_value1,
148 plo.value1 || ' ' || DECODE(prtdh.criteria_value2, 'A', 'ASC', 'DESC') sort_option,
149 ' ''' || flv1.meaning || ''' ' sort_option_description,
150 ' ''' || flv2.meaning || ''' ' order_by_description
151 FROM psp_report_template_details_h prtdh,
152 psp_report_templates_h prth,
153 fnd_lookup_values flv1,
154 fnd_lookup_values flv2,
155 psp_layout_options plo
156 WHERE prtdh.request_id = p_request_id
157 AND prth.request_id = p_request_id
158 AND plo.report_template_code = prth.report_template_code
159 AND flv1.lookup_code = prtdh.criteria_lookup_code
160 AND flv1.lookup_type = 'PSP_SORTING_CRITERIA'
161 AND prtdh.criteria_lookup_type = 'PSP_SORTING_CRITERIA'
162 AND flv2.lookup_type = 'PSP_ORDERING_CRITERIA'
163 AND flv2.lookup_code = prtdh.criteria_value2
164 ANd prtdh.criteria_lookup_code = plo.layout_lookup_code
165 AND plo.value1 LIKE 'per.%'
166 ORDER BY prtdh.criteria_value1;
167
168 CURSOR sort_option2_cur (p_request_id IN NUMBER) IS
169 SELECT DISTINCT prtdh.criteria_value1,
170 plo.value1 || ' ' || DECODE(prtdh.criteria_value2, 'A', 'ASC', 'DESC') sort_option,
171 ' ''' || flv1.meaning || ''' ' sort_option_description,
172 ' ''' || flv2.meaning || ''' ' order_by_description
173 FROM psp_report_template_details_h prtdh,
174 psp_report_templates_h prth,
175 fnd_lookup_values flv1,
176 fnd_lookup_values flv2,
177 psp_layout_options plo
178 WHERE prth.request_id = p_request_id
179 AND prtdh.request_id = p_request_id
180 AND plo.report_template_code = prth.report_template_code
181 AND flv1.lookup_code = prtdh.criteria_lookup_code
182 AND flv1.language = 'US'
183 AND flv1.lookup_type = 'PSP_SORTING_CRITERIA'
184 AND prtdh.criteria_lookup_type = 'PSP_SORTING_CRITERIA'
185 AND plo.layout_lookup_type = 'PSP_SORTING_CRITERIA'
186 AND flv2.language = 'US'
187 AND flv2.lookup_type = 'PSP_ORDERING_CRITERIA'
188 AND flv2.lookup_code = prtdh.criteria_value2
189 AND prtdh.criteria_lookup_code = plo.layout_lookup_code --'PRINVESG'
190 --AND plo.value1 LIKE 'perd.%'
191 AND plo.layout_lookup_code IN ('PRINVESG', 'PIORG', 'PRJMGR', 'PMORG', 'TASKMGR', 'TMORG')
192 ORDER BY prtdh.criteria_value1;
193
194 CURSOR layout_type_cur IS
195 SELECT SUBSTR(report_template_code, 6, 3) layout_type,
196 display_all_emp_distrib_flag display_flag
197 FROM psp_report_templates_h prt
198 WHERE request_id = l_request_id;
199
200 CURSOR nls_date_format_cur IS
201 SELECT value
202 FROM nls_session_parameters
203 WHERE parameter = 'NLS_DATE_FORMAT';
204
205 CURSOR approver_info_cur IS
206 SELECT DISTINCT pera.wf_role_display_name,
207 haou.name
208 FROM hr_all_organization_units haou,
209 psp_eff_report_approvals pera,
210 per_all_assignments_f paaf,
211 fnd_user fu
212 WHERE pera.wf_item_key = p_wf_item_key
213 AND fu.employee_id = paaf.person_id
214 AND fu.user_name = pera.wf_role_name
215 AND paaf.primary_flag = 'Y'
216 AND haou.organization_id = paaf.organization_id
217 AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
218
219 CURSOR template_id_cur IS
220 SELECT per.template_id,
221 per.request_id,
222 per.set_of_books_id
223 FROM psp_eff_report_details perd,
224 psp_eff_report_approvals pera,
225 psp_eff_reports per
226 WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
227 AND perd.effort_report_id = per.effort_report_id
228 AND pera.wf_item_key = p_wf_item_key
229 AND ROWNUM = 1
230 UNION ALL
231 SELECT per2.template_id,
232 p_request_id,
233 per2.set_of_books_id
234 FROM psp_eff_reports per2
235 WHERE per2.request_id = p_request_id
236 AND ROWNUM = 1;
237
238 CURSOR get_segment_delimeter_cur IS
239 SELECT fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gsob.chart_of_accounts_id)
240 FROM gl_sets_of_books gsob
241 WHERE gsob.set_of_books_id = l_set_of_books_id;
242
243 CURSOR get_segment_header_cur IS
244 SELECT fifs.segment_name || l_segment_delimiter segment_header
245 FROM fnd_id_flex_segments fifs,
246 gl_sets_of_books gsob,
247 fnd_application fa
248 WHERE gsob.set_of_books_id = l_set_of_books_id
249 AND fifs.id_flex_num = gsob.chart_of_accounts_id
250 AND fifs.id_flex_code = 'GL#'
251 AND fifs.application_id = fa.application_id
252 AND fa.application_short_name = 'SQLGL'
253 AND EXISTS (SELECT 1
254 FROM psp_report_template_details_h prtdh
255 WHERE prtdh.REQUEST_ID= p_request_id
256 AND prtdh.criteria_lookup_type = 'PSP_SUMMARIZATION_CRITERIA'
257 AND prtdh.criteria_lookup_code = fifs.application_column_name)
258 ORDER BY fifs.segment_num;
259
260 TYPE personxmlType IS REF CURSOR;
261 person_xml_cur personxmlType;
262
263 TYPE investigatorType IS REF CURSOR;
264 investigator_cur investigatorType;
265
266 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
267 SELECT 1
268 FROM psp_report_errors
269 WHERE request_id = p_request_id
270 AND message_level = 'E'
271 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
272 AND pdf_request_id = g_request_id;
273
274 CURSOR format_mask_cur(p_length IN NUMBER) IS
275 SELECT fnd_currency.get_format_mask(currency_code, p_length)
276 FROM psp_eff_reports
277 WHERE request_id = l_request_id;
278
279 CURSOR show_hide_FYI_lines_csr(p_request_id IN NUMBER,p_investigator_person_id IN NUMBER) IS
280 SELECT 'Y'
281 FROM psp_eff_report_details perd,
282 psp_eff_reports per ,
283 psp_eff_report_details perd2
284 WHERE per.effort_report_id = perd.effort_report_id
285 AND per.status_code IN ('N', 'A')
286 AND per.request_id = NVL(p_request_id,per.request_id)
287 AND perd.investigator_person_id =p_investigator_person_id
288 AND per.effort_report_id = perd2.effort_report_id
289 AND perd.investigator_person_id <> perd2.investigator_person_id;
290
291 l_show_hide_fyi_flag VARCHAR2(1);
292
293 l_num30_fmask VARCHAR2(35);
294 l_error_count NUMBER;
295 l_return_status CHAR(1);
296 BEGIN
297
298 g_request_id := fnd_global.conc_request_id;
299 OPEN template_id_cur;
300 FETCH template_id_cur INTO l_template_id, l_request_id, l_set_of_books_id;
301 CLOSE template_id_cur;
302
303 OPEN format_mask_cur(30);
304 FETCH format_mask_cur INTO l_num30_fmask;
305 CLOSE format_mask_cur;
306
307 OPEN sort_option_cur(l_request_id);
308 LOOP
309 IF (sort_option_cur%ROWCOUNT = 0) THEN
310 FETCH sort_option_cur INTO l_criteria_value1, l_sort_option1, l_sort_option_desc1, l_order_by_desc1;
311 ELSE
312 FETCH sort_option_cur INTO l_criteria_value1, l_sort_option2, l_sort_option_desc2, l_order_by_desc2;
313 END IF;
314 EXIT WHEN sort_option_cur%NOTFOUND;
315
316 IF (sort_option_cur%ROWCOUNT = 1) THEN
317 l_sort_option1 := ' ORDER BY ' || l_sort_option1;
318 END IF;
319
320 IF (l_sort_option2 IS NOT NULL) THEN
321 l_sort_option1 := l_sort_option1 || ', ' || l_sort_option2 || ' ';
322 END IF;
323 END LOOP;
324 CLOSE sort_option_cur;
325 l_emp_sort_option := l_sort_option1;
326
327 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_icx_date_format);
328
329 OPEN layout_type_cur;
330 FETCH layout_type_cur INTO l_layout_type, l_display_flag;
331 CLOSE layout_type_cur;
332
333 OPEN get_segment_delimeter_cur;
334 FETCH get_segment_delimeter_cur INTO l_segment_delimiter;
335 CLOSE get_segment_delimeter_cur;
336
337 OPEN get_segment_header_cur;
338 l_gl_header := '';
339 LOOP
340 FETCH get_segment_header_cur INTO l_segment_header;
341 EXIT WHEN get_segment_header_cur%NOTFOUND;
342
343 l_gl_header := l_gl_header || l_segment_header;
344 END LOOP;
345 l_gl_header := SUBSTR(l_gl_header, 1, LENGTH(l_gl_header) - 1);
346 CLOSE get_segment_header_cur;
347
348 l_report_info := '<?xml version="1.0" ?><PSPERREP> ';
349
350 query1 := 'SELECT TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_2)), ''' || l_icx_date_format
351 || ''') start_date, TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_3)), '''
352 || l_icx_date_format || ''') end_date, ''' || l_display_flag || ''' display_flag, ''' || l_gl_header || ''' gl_header, '
353 || 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') run_date '
354 || 'FROM psp_report_templates_h prth WHERE prth.request_id = ' || TO_CHAR(l_request_id);
355 qryCtx1 := dbms_xmlgen.newContext(query1);
356 dbms_xmlgen.setRowTag(qryCtx1, NULL);
357 dbms_xmlgen.setRowSetTag(qryCtx1, 'G_REPORT_INFO');
358 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
359 dbms_xmlgen.closecontext(qryctx1);
360 l_xml := xmlresult1;
361 dbms_lob.write(l_xml, length(l_report_info), 1, l_report_info);
362 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
363 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, length(l_report_info), l_resultOffset +1);
364
365 IF (l_layout_type = 'EMP') THEN
366
367 dbms_lob.writeappend(l_xml, length('<LIST_G_PERSON> '), '<LIST_G_PERSON> ');
368
369 IF (p_request_id IS NULL) THEN
370
371 query1 := 'SELECT person_xml FROM psp_eff_reports per WHERE per.status_code IN (''A'', ''N'') '
372 || 'AND EXISTS (SELECT 1 FROM psp_eff_report_details perd, '
373 || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id AND '
374 || 'perd.effort_report_id = per.effort_report_id AND pera.wf_item_key = ' || TO_CHAR(p_wf_item_key)
375 || ' AND pera.approval_status <> ''R'')' || l_sort_option1;
376 ELSE
377 query1 := 'SELECT person_xml FROM psp_eff_reports per WHERE status_code IN (''A'', ''N'') AND '
378 || 'request_id = ' || TO_CHAR(l_request_id) || l_sort_option1;
379 END IF;
380
381 OPEN person_xml_cur FOR query1;
382 LOOP
383 FETCH person_xml_cur INTO l_person_xml;
384 EXIT WHEN person_xml_cur%NOTFOUND;
385
386 dbms_lob.copy(l_xml, l_person_xml, dbms_lob.getlength(l_person_xml), dbms_lob.getlength(l_xml), 1);
387 END LOOP;
388 CLOSE person_xml_cur;
389
390 dbms_lob.writeappend(l_xml, length('</LIST_G_PERSON>'), '</LIST_G_PERSON>');
391 ELSE
392
393 OPEN sort_option2_cur(l_request_id);
394 l_sort_option1 := NULL;
395 l_sort_option2 := NULL;
396 LOOP
397 IF (sort_option2_cur%ROWCOUNT = 0) THEN
398 FETCH sort_option2_cur INTO l_criteria_value1, l_sort_option1, l_sort_option_desc1, l_order_by_desc1;
399 ELSE
400 FETCH sort_option2_cur INTO l_criteria_value1, l_sort_option2, l_sort_option_desc2, l_order_by_desc2;
401 END IF;
402 EXIT WHEN sort_option2_cur%NOTFOUND;
403
404 IF (sort_option2_cur%ROWCOUNT = 1) THEN
405 l_sort_option1 := ' ORDER BY ' || l_sort_option1;
406 END IF;
407
408 IF (l_sort_option2 IS NOT NULL) THEN
409 l_sort_option1 := l_sort_option1 || ', ' || l_sort_option2 || ' ';
410 END IF;
411 END LOOP;
412 CLOSE sort_option2_cur;
413
414 dbms_lob.writeappend(l_xml, length('<LIST_G_INVESTIGATOR> '), '<LIST_G_INVESTIGATOR> ');
415 IF (p_request_id IS NULL) THEN
416 query1 := 'SELECT investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id, '
417 || 'TRIM(TO_CHAR(SUM(proposed_salary_amt), ''' || l_num30_fmask || ''')) total_pi_proposed_salary, '
418 || 'TRIM(TO_CHAR(SUM(actual_salary_amt), ''' || l_num30_fmask || ''')) total_pi_actual_salary FROM '
419 || 'psp_eff_report_details perd, psp_eff_report_approvals pera WHERE pera.effort_report_detail_id = '
420 || 'perd.effort_report_detail_id AND pera.wf_item_key = ' || TO_CHAR(p_wf_item_key)
421 || ' AND EXISTS (SELECT 1 FROM psp_eff_reports per WHERE per.effort_report_id = perd.effort_report_id '
422 || 'AND per.status_code IN (''A'', ''N'')) AND perd.investigator_person_id IS NOT NULL'
423 || ' GROUP BY investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id'
424 || l_sort_option1;
425 ELSE
426 query1 := 'SELECT investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id, '
427 || 'TRIM(TO_CHAR(SUM(proposed_salary_amt), ''' || l_num30_fmask || ''')) total_pi_proposed_salary, '
428 || 'TRIM(TO_CHAR(SUM(actual_salary_amt), ''' || l_num30_fmask || ''')) total_pi_actual_salary FROM '
429 || 'psp_eff_report_details perd, psp_eff_reports per WHERE per.effort_report_id = '
430 || 'perd.effort_report_id AND per.request_id = ' || TO_CHAR(p_request_id)
431 || ' AND per.status_code IN (''A'', ''N'') AND perd.investigator_person_id IS NOT NULL'
432 || ' GROUP BY investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id'
433 || l_sort_option1;
434 END IF;
435
436 OPEN investigator_cur FOR query1;
437 LOOP
438 FETCH investigator_cur INTO l_investigator_id, l_investigator_name, l_investigator_org_name,
439 l_investigator_primary_org_id, l_total_pi_proposed_salary, l_total_pi_actual_salary;
440 EXIT WHEN investigator_cur%NOTFOUND;
441
442 OPEN show_hide_FYI_lines_csr(p_request_id, l_investigator_id);
443 FETCH show_hide_FYI_lines_csr INTO l_show_hide_fyi_flag ;
444 IF (show_hide_FYI_lines_csr%NOTFOUND) THEN
445 l_show_hide_fyi_flag := 'N';
446 END IF;
447 CLOSE show_hide_FYI_lines_csr;
448
449
450 ---- 4429787
451 l_investigator_name := convert_xml_controls(l_investigator_name);
452 l_investigator_org_name := convert_xml_controls(l_investigator_org_name);
453
454 dbms_lob.writeappend(l_xml, length('<G_INVESTIGATOR>
455 <INVESTIGATOR_PERSON_ID>' || TO_CHAR(l_investigator_id) || '</INVESTIGATOR_PERSON_ID>
456 <INVESTIGATOR_NAME>' || l_investigator_name || '</INVESTIGATOR_NAME>
457 <INVESTIGATOR_ORG_NAME>' || l_investigator_org_name || '</INVESTIGATOR_ORG_NAME>
458 <INVESTIGATOR_PRIMARY_ORG_ID>' || TO_CHAR(l_investigator_primary_org_id) || '</INVESTIGATOR_PRIMARY_ORG_ID>
459 <TOTAL_PI_PROPOSED_SALARY>' || l_total_pi_proposed_salary || '</TOTAL_PI_PROPOSED_SALARY>
460 <TOTAL_PI_ACTUAL_SALARY>' || l_total_pi_actual_salary || '</TOTAL_PI_ACTUAL_SALARY>
461 <SHOW_HIDE_FYI_LINES>' || l_show_hide_fyi_flag || '</SHOW_HIDE_FYI_LINES>
462 '), '<G_INVESTIGATOR>
463 <INVESTIGATOR_PERSON_ID>' || TO_CHAR(l_investigator_id) || '</INVESTIGATOR_PERSON_ID>
464 <INVESTIGATOR_NAME>' || l_investigator_name || '</INVESTIGATOR_NAME>
465 <INVESTIGATOR_ORG_NAME>' || l_investigator_org_name || '</INVESTIGATOR_ORG_NAME>
466 <INVESTIGATOR_PRIMARY_ORG_ID>' || TO_CHAR(l_investigator_primary_org_id) || '</INVESTIGATOR_PRIMARY_ORG_ID>
467 <TOTAL_PI_PROPOSED_SALARY>' || l_total_pi_proposed_salary || '</TOTAL_PI_PROPOSED_SALARY>
468 <TOTAL_PI_ACTUAL_SALARY>' || l_total_pi_actual_salary || '</TOTAL_PI_ACTUAL_SALARY>
469 <SHOW_HIDE_FYI_LINES>' || l_show_hide_fyi_flag || '</SHOW_HIDE_FYI_LINES>
470 ');
471
472 -- dbms_lob.writeappend(l_xml, length('<LIST_G_PERSON> '), '<LIST_G_PERSON> ');
473
474 query2 := 'SELECT person_xml FROM psp_eff_reports per WHERE per.effort_report_id IN (SELECT perd.effort_report_id '
475 || 'FROM psp_eff_report_details perd WHERE perd.investigator_person_id = ' || TO_CHAR(l_investigator_id) || ')'
476 || ' AND request_id = ' || TO_CHAR(l_request_id) || ' AND status_code <> ''R''' || l_emp_sort_option;
477 OPEN person_xml_cur FOR query2;
478 LOOP
479 FETCH person_xml_cur INTO l_person_xml;
480 EXIT WHEN person_xml_cur%NOTFOUND;
481 l_resultOffset := DBMS_LOB.INSTR(l_person_xml,'>');
482 dbms_lob.copy(l_xml, l_person_xml, dbms_lob.getlength(l_person_xml), dbms_lob.getlength(l_xml), 1);
483 END LOOP;
484 CLOSE person_xml_cur;
485
486 -- dbms_lob.writeappend(l_xml, length('</LIST_G_PERSON> '), '</LIST_G_PERSON> ');
487
488 --Bug 4334816: START
489 -- Including the WorkFlow Note in Pdf
490
491 /* query2 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) '
492 || 'approver_name, TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
493 || ' approval_date FROM psp_eff_report_approvals pera, psp_eff_reports per'
494 || ',psp_eff_report_details perd WHERE perd.effort_report_detail_id=pera.effort_report_detail_id AND '
495 || 'per.effort_report_id = perd.effort_report_id AND per.request_id = ' || TO_CHAR(l_request_id)
496 || ' AND perd.investigator_person_id = ' || TO_CHAR(l_investigator_id)
497 || ' ORDER BY approver_order_num DESC';
498 */
499
500 query2 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) '
501 || 'approver_name, TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
502 || ' approval_date, wfna.TEXT_VALUE note FROM psp_eff_report_approvals pera, psp_eff_reports per'
503 || ',psp_eff_report_details perd , WF_NOTIFICATION_ATTRIBUTES wfna WHERE perd.effort_report_detail_id=pera.effort_report_detail_id AND '
504 || 'per.effort_report_id = perd.effort_report_id AND per.request_id = ' || TO_CHAR(l_request_id)
505 || ' AND perd.investigator_person_id = ' || TO_CHAR(l_investigator_id)
506 ||' AND wfna.NAME(+) =''WF_NOTE'' AND wfna.NOTIFICATION_ID(+) = pera.NOTIFICATION_ID '
507 || ' ORDER BY approver_order_num DESC';
508 --Bug 4334816: END
509
510 qryCtx1 := dbms_xmlgen.newContext(query2);
511 dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
512 dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
513 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
514 dbms_xmlgen.closecontext(qryctx1);
515 IF (dbms_lob.getlength(xmlresult1) > 0) THEN
516 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
517 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
518 END IF;
519 dbms_lob.writeappend(l_xml, length('</G_INVESTIGATOR> '), '</G_INVESTIGATOR> ');
520
521 END LOOP;
522 CLOSE investigator_cur;
523
524 dbms_lob.writeappend(l_xml, length('</LIST_G_INVESTIGATOR> '), '</LIST_G_INVESTIGATOR> ');
525 END IF;
526
527 dbms_lob.writeappend(l_xml, length('</PSPERREP>'), '</PSPERREP>');
528
529 RETURN l_xml;
530 EXCEPTION
531 WHEN OTHERS THEN
532 OPEN add_report_error_cur(sqlerrm);
533 FETCH add_report_error_cur INTO l_error_count;
534 CLOSE add_report_error_cur;
535
536 IF (NVL(l_error_count, 0) = 0) THEN
537 psp_general.add_report_error
538 (p_request_id => l_request_id,
539 p_message_level => 'E',
540 p_source_id => NULL,
541 p_retry_request_id => NULL,
542 p_pdf_request_id => g_request_id,
543 p_error_message => sqlerrm,
544 p_return_status => l_return_status);
545 END IF;
546 RAISE;
547 END generate_approver_xml;
548
549 FUNCTION generate_person_xml (p_person_id IN NUMBER,
550 p_template_id IN NUMBER,
551 p_effort_report_id IN NUMBER,
552 p_request_id IN NUMBER,
553 p_set_of_books_id IN NUMBER,
554 p_full_name IN VARCHAR2,
555 p_employee_number IN VARCHAR2,
556 p_mailstop IN VARCHAR2,
557 p_emp_primary_org_name IN VARCHAR2,
558 p_emp_primary_org_id IN NUMBER,
559 p_currency_code IN VARCHAR2
560 ) RETURN CLOB IS
561 qryCtx1 dbms_xmlgen.ctxType;
562 qryCtx2 dbms_xmlquery.ctxType;
563 query1 VARCHAR2(8000);
564 xmlresult1 CLOB;
565 l_xml CLOB;
566 l_resultOffset INT;
567 l_assignment_id NUMBER;
568 l_assignment_number VARCHAR2(30);
569 l_er_check VARCHAR2(1000);
570 l_assignment_check VARCHAR2(1000);
571 l_sort_option1 VARCHAR2(200);
572 l_sort_option2 VARCHAR2(200);
573 l_criteria_value1 VARCHAR2(30);
574 l_employee_info VARCHAR2(2000);
575 l_layout_type CHAR(3);
576
577 CURSOR layout_type_cur IS
578 SELECT SUBSTR(report_template_code, 6, 3) layout_type
579 FROM psp_report_templates_h prt
580 WHERE request_id = p_request_id;
581
582 /*
583 CURSOR sort_option_cur (p_template_id IN NUMBER) IS
584 SELECT DISTINCT prtd.criteria_value1,
585 plo.value1 || ' ' || DECODE(prtd.criteria_value2, 'A', 'ASC', 'DESC') sort_option
586 FROM psp_report_template_details prtd,
587 fnd_lookup_values flv1,
588 fnd_lookup_values flv2,
589 psp_layout_options plo
590 WHERE prtd.template_id = p_template_id
591 AND flv1.lookup_code = prtd.criteria_lookup_code
592 AND flv1.lookup_type = 'PSP_SORTING_CRITERIA'
593 AND prtd.criteria_lookup_type = 'PSP_SORTING_CRITERIA'
594 AND flv2.lookup_type = 'PSP_ORDERING_CRITERIA'
595 AND flv2.lookup_code = prtd.criteria_value2
596 ANd prtd.criteria_lookup_code = plo.layout_lookup_code
597 AND plo.value1 LIKE 'perd.%'
598 ORDER BY prtd.criteria_value1;
599 */
600 -- Bug 4244924 YALE ENHANCEMENTS
601 CURSOR sort_option_cur (p_request_id IN NUMBER, p_business_group_id IN NUMBER) IS
602 select prtdh.criteria_value1,
603 decode (substr(prtdh.CRITERIA_LOOKUP_CODE,1,7),'SEGMENT','GL_'||prtdh.CRITERIA_LOOKUP_CODE,plo.VALUE1)
604 || ' ' || DECODE(criteria_value2, 'A', 'ASC', 'DESC') --decode plo.VALUE1
605 from psp_report_template_details_h prtdh ,
606 psp_report_templates_h prth
607 ,psp_layout_options plo
608 where prth.request_id= p_request_id-- 125338 --125188
609 and prth.request_id = prtdh.request_id
610 and prtdh.CRITERIA_LOOKUP_TYPE ='PSP_SORTING_CRITERIA'
611 and prtdh.CRITERIA_LOOKUP_TYPE = plo.LAYOUT_LOOKUP_TYPE
612 and prth.REPORT_TEMPLATE_CODE = plo.REPORT_TEMPLATE_CODE
613 and plo.PTAOE_STORED_IN_GL_FLAG = PSP_GENERAL.GET_CONFIGURATION_OPTION_VALUE(p_business_group_id,'PSP_USE_GL_PTAOE_MAPPING')
614 and (plo.LAYOUT_LOOKUP_CODE = prtdh.CRITERIA_LOOKUP_CODE
615 OR plo.LAYOUT_LOOKUP_CODE = 'GL' and prtdh.CRITERIA_LOOKUP_CODE like 'SEGMENT%')
616 AND plo.value1 LIKE 'perd.%'
617 ORDER BY prtdh.criteria_value1;
618
619
620 CURSOR assign_cur (p_effort_report_id IN NUMBER) IS
621 SELECT DISTINCT assignment_id,
622 assignment_number
623 FROM psp_eff_report_details
624 WHERE effort_report_id = p_effort_report_id
625 ORDER BY assignment_number ASC; -- Bug 4247734
626
627 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
628 SELECT 1
629 FROM psp_report_errors
630 WHERE request_id = p_request_id
631 AND message_level = 'E'
632 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
633 AND pdf_request_id = g_request_id;
634
635 /* Added for Hospital effort report */
636 CURSOR grouping_category_csr(p_assignment_id IN NUMBER) IS
637 SELECT distinct perd.grouping_category, plo.layout_option_order
638 FROM psp_eff_report_details perd,
639 psp_layout_options plo
640 WHERE effort_report_id = p_effort_report_id
641 AND NVL(assignment_id,-1) = NVL(p_assignment_id,-1)
642 AND plo.layout_lookup_type ='PSP_EFFORT_CATEGORY'
643 AND plo.LAYOUT_LOOKUP_CODE = perd.grouping_category
644 ORDER BY plo.layout_option_order;
645
646 /* Added for TGEN Bug 6864426 for pre-approved effort report*/
647 CURSOR initiator_name_cur IS
648 SELECT ppf.full_name
649 FROM per_people_f ppf,
650 fnd_user fu,
651 fnd_concurrent_requests fcr
652 WHERE fu.user_id = fcr.requested_by
653 AND fu.employee_id = ppf.person_id
654 AND fcr.request_id = p_request_id;
655
656
657 l_num25_fmask VARCHAR2(30);
658 l_icx_date_format VARCHAR2(20);
659 l_error_count NUMBER;
660 l_return_status CHAR(1);
661 l_business_group_id Number := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
662 l_grouping_category varchar2(30); -- Added for Hospital effort report
663 l_layout_option_order Number;
664 l_approval_type psp_report_templates.approval_type%TYPE; --Bug 6864426
665 l_initiator_name per_people_f.full_name%TYPE; --Bug 6864426
666
667 BEGIN
668 g_request_id := fnd_global.conc_request_id;
669 OPEN layout_type_cur;
670 FETCH layout_type_cur INTO l_layout_type;
671 CLOSE layout_type_cur;
672
673 --Bug 6864426
674 OPEN initiator_name_cur;
675 FETCH initiator_name_cur INTO l_initiator_name;
676 CLOSE initiator_name_cur;
677
678
679 l_num25_fmask := fnd_currency.get_format_mask(p_currency_code, 25);
680 fnd_profile.get('ICX_DATE_FORMAT_MASK', l_icx_date_format);
681
682 OPEN sort_option_cur(p_template_id, l_business_group_id);
683 LOOP
684 IF (sort_option_cur%ROWCOUNT = 0) THEN
685 FETCH sort_option_cur INTO l_criteria_value1, l_sort_option1;
686 ELSE
687 FETCH sort_option_cur INTO l_criteria_value1, l_sort_option2;
688 END IF;
689 EXIT WHEN sort_option_cur%NOTFOUND;
690
691 IF (sort_option_cur%ROWCOUNT = 1) THEN
692 l_sort_option1 := ' ORDER BY ' || l_sort_option1;
693 END IF;
694
695 IF (l_sort_option2 IS NOT NULL) THEN
696 l_sort_option1 := l_sort_option1 || ', ' || l_sort_option2;
697 END IF;
698 END LOOP;
699 CLOSE sort_option_cur;
700
701 l_er_check := ' AND NOT EXISTS (SELECT 1 FROM psp_eff_report_details perd1, psp_eff_report_approvals pera1 '
702 || 'WHERE perd.effort_report_id = perd1.effort_report_id AND perd1.effort_report_detail_id = pera1.effort_report_detail_id '
703 || 'AND pera1.approval_status = ''R'') '
704 || 'AND NVL(pera.approver_order_num, 1) = (SELECT NVL(MAX(pera1.approver_order_num), 1) FROM psp_eff_report_approvals pera1 '
705 || 'WHERE pera1.effort_report_detail_id = perd.effort_report_detail_id)';
706
707 IF (l_layout_type = 'EMP') THEN
708 l_employee_info := '
709 <G_PERSON>
710 <PERSON_ID>' || TO_CHAR(p_person_id) || '</PERSON_ID>
711 <EMPLOYEE_NAME>' || convert_xml_controls(p_full_name) || '</EMPLOYEE_NAME>
712 <EMPLOYEE_NUMBER>' || convert_xml_controls(p_employee_number) || '</EMPLOYEE_NUMBER>
713 <MAILSTOP>' || convert_xml_controls(p_mailstop) || '</MAILSTOP>
714 <ORGANIZATION_NAME>' || convert_xml_controls(p_emp_primary_org_name) || '</ORGANIZATION_NAME>
715 <ORGANIZATION_ID>' || p_emp_primary_org_id || '</ORGANIZATION_ID>
716 ';
717
718 query1 := 'select distinct pera.eff_information1, pera.eff_information2, pera.eff_information3,pera.eff_information4, pera.eff_information5, '
719 || ' pera.eff_information6,pera.eff_information7, pera.eff_information8 , pera.eff_information9,pera.eff_information10, '
720 || ' pera.eff_information11, pera.eff_information12, pera.eff_information13, pera.eff_information14, pera.eff_information15 '
721 || ' FROM psp_eff_report_details perd, '
722 || ' psp_eff_report_approvals pera '
723 || ' WHERE perd.effort_report_detail_id = pera.effort_report_detail_id '
724 || ' AND perd.effort_report_id = ' || p_effort_report_id
725 || ' AND APPROVER_ORDER_NUM = (SELECT max(APPROVER_ORDER_NUM) '
726 || ' FROM psp_eff_report_approvals pera2, '
727 || ' psp_eff_report_details perd2 '
728 || ' WHERE pera2.effort_report_detail_id = perd2.effort_report_detail_id '
729 || ' AND perd2.effort_report_id = perd.effort_report_id) ';
730
731 qryCtx2 := dbms_xmlquery.newContext(query1);
732 dbms_xmlquery.setRowTag(qryCtx2, NULL);
733 dbms_xmlquery.setRowSetTag(qryCtx2, 'EMP_DFF');
734 xmlresult1 := dbms_xmlquery.getXML(qryCtx2, dbms_xmlgen.NONE);
735 dbms_xmlquery.closecontext(qryctx2);
736 l_xml := xmlresult1;
737 dbms_lob.write(l_xml, length(l_employee_info), 1, l_employee_info);
738 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
739 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, length(l_employee_info), l_resultOffset +1);
740
741
742
743 query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_emp_proposed_salary, '
744 || 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_emp_proposed_effort, '
745 || 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_emp_cost_share, '
746 || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_emp_actual_salary, '
747 || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_emp_actual_effort, '
748 || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_emp_overwritten_effort, '
749 || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_emp_actual_cost_share '
750 || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '
751 || 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
752 || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN ( ''P'', ''A'')' || l_er_check;
753 qryCtx1 := dbms_xmlgen.newContext(query1);
754 dbms_xmlgen.setRowTag(qryCtx1, NULL);
755 dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_EMP');
756 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
757 dbms_xmlgen.closecontext(qryctx1);
758 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
759 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
760 dbms_lob.writeappend(l_xml, length('<LIST_G_ASSIGNMENT> '), '<LIST_G_ASSIGNMENT> ');
761
762 OPEN assign_cur(p_effort_report_id);
763 LOOP
764 FETCH assign_cur INTO l_assignment_id, l_assignment_number;
765 EXIT WHEN assign_cur%NOTFOUND;
766
767 l_assignment_check := l_er_check;
768 IF (l_assignment_id IS NOT NULL) THEN
769 l_assignment_check := l_assignment_check || ' AND assignment_id = ' || TO_CHAR(l_assignment_id);
770 END IF;
771 --- applied strip controls for 4429787
772 dbms_lob.writeappend(l_xml, length('<G_ASSIGNMENT><ASSIGNMENT_ID>' || TO_CHAR(l_assignment_id) ||
773 '</ASSIGNMENT_ID><ASSIGNMENT_NUMBER>' || convert_xml_controls(l_assignment_number) || '</ASSIGNMENT_NUMBER> '),
774 '<G_ASSIGNMENT><ASSIGNMENT_ID>' || TO_CHAR(l_assignment_id) || '</ASSIGNMENT_ID><ASSIGNMENT_NUMBER>' ||
775 convert_xml_controls(l_assignment_number) || '</ASSIGNMENT_NUMBER> ');
776
777 query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_asg_proposed_salary, '
778 || 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_asg_proposed_effort, '
779 || 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_asg_cost_share, '
780 || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_asg_actual_salary, '
781 || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_asg_actual_effort, '
782 || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_asg_overwritten_effort, '
783 || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_asg_actual_cost_share '
784 || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '
785 || 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
786 || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
787 qryCtx1 := dbms_xmlgen.newContext(query1);
788 dbms_xmlgen.setRowTag(qryCtx1, NULL);
789 dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_ASG');
790 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
791 dbms_xmlgen.closecontext(qryctx1);
792 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
793 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
794
795
796 /* Changes for Hospital effort report :START */
797 dbms_lob.writeappend(l_xml, length('<LIST_G_CATEGORY> '), '<LIST_G_CATEGORY> ');
798
799 OPEN grouping_category_csr(l_assignment_id);
800 LOOP
801 FETCH grouping_category_csr INTO l_grouping_category, l_layout_option_order;
802 EXIT WHEN grouping_category_csr%NOTFOUND;
803 --dbms_lob.writeappend(l_xml, length('<G_CATEGORY> '), '<G_CATEGORY> ');
804
805 query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_proposed_salary, '
806 || 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_proposed_effort, '
807 || 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_cost_share, '
808 || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_actual_salary, '
809 || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_actual_effort, '
810 || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_overwritten_effort, '
811 || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_actual_cost_share, '
812 || 'TRIM(MAX(lookup.lookup_code)) category_code, '
813 || 'TRIM(MAX(lookup.meaning)) category_desc '
814 || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera, '
815 || 'psp_layout_options plo, fnd_lookup_values_vl lookup, psp_report_templates_h prth '
816 || 'WHERE perd.grouping_category = plo.layout_lookup_code '
817 || 'AND plo.layout_lookup_code = lookup.lookup_code '
818 || 'AND plo.layout_lookup_type =''PSP_EFFORT_CATEGORY'' '
819 || 'AND lookup.lookup_type = ''PSP_EFFORT_CATEGORY'' '
820 || 'AND lookup.enabled_flag = ''Y'' '
821 || 'AND sysdate between NVL(lookup.start_date_active,to_date(''01/01/1951'',''DD/MM/RRRR'')) '
822 || 'AND NVL(lookup.end_date_active,to_date(''31/12/4712'',''DD/MM/RRRR'')) '
823 || 'AND perd.grouping_category = ''' || l_grouping_category || ''' '
824 || 'AND prth.report_template_code = plo.report_template_code '
825 || 'AND prth.request_id = ' || p_request_id
826 || ' AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
827 || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
828
829 qryCtx1 := dbms_xmlgen.newContext(query1);
830 dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORY');
831 dbms_xmlgen.setRowSetTag(qryCtx1, NULL);
832 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
833 dbms_xmlgen.closecontext(qryctx1);
834 xmlresult1 := SUBSTR(xmlresult1, 1, LENGTH(xmlresult1) - 15);
835 -- l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
836 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
837
838
839 query1 := 'SELECT TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '
840 || 'TRIM(TO_CHAR(perd.PAYROLL_PERCENT, ''999G990D00'')) payroll_percent, '
841 || 'TRIM(TO_CHAR(perd.PROPOSED_SALARY_AMT, ''' || l_num25_fmask || ''')) proposed_salary_amt, '
842 || 'TRIM(TO_CHAR(perd.PROPOSED_EFFORT_PERCENT, ''999G990D00'')) proposed_effort_percent, '
843 || 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
844 || 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
845 || 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
846 || 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
847 || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share, '
848 || 'pera.pera_information1 pera_information1, pera.pera_information2, pera.pera_information3, pera.pera_information4, pera.pera_information5, '
849 || 'pera.pera_information6, pera.pera_information7, pera.pera_information8, pera.pera_information9, pera.pera_information10, '
850 || 'pera.pera_information11, pera.pera_information12, pera.pera_information13, pera.pera_information14, pera.pera_information15, '
851 || 'pera.pera_information16, pera.pera_information17, pera.pera_information8, pera.pera_information19, pera.pera_information20 '
852 || 'FROM psp_eff_report_details perd, '
853 || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
854 || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
855 || 'AND perd.grouping_category = ''' || l_grouping_category || ''' '
856 || l_assignment_check || l_sort_option1;
857 qryCtx1 := dbms_xmlgen.newContext(query1);
858 dbms_xmlgen.setRowTag(qryCtx1, 'G_CATEGORYDETAILS');
859 dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_CATEGORYDETAILS');
860 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
861 dbms_xmlgen.closecontext(qryctx1);
862 IF (dbms_lob.getlength(xmlresult1) > 0) THEN
863 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
864 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
865 END IF;
866
867
868 dbms_lob.writeappend(l_xml, length('</G_CATEGORY> '), '</G_CATEGORY> ');
869
870 END LOOP;
871 CLOSE grouping_category_csr;
872 dbms_lob.writeappend(l_xml, length('</LIST_G_CATEGORY> '), '</LIST_G_CATEGORY> ');
873
874 /*
875
876 query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_spon_proposed_salary, '
877 || 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_spon_proposed_effort, '
878 || 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_spon_cost_share, '
879 || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_spon_actual_salary, '
880 || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_spon_actual_effort, '
881 || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_spon_overwritten_effort, '
882 || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_spon_actual_cost_share '
883 || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '
884 || 'WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
885 || 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
886 || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type = ppa.project_type AND '
887 || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
888 || '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
889 || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
890 qryCtx1 := dbms_xmlgen.newContext(query1);
891 dbms_xmlgen.setRowTag(qryCtx1, NULL);
892 dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_SPONSORED');
893 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
894 dbms_xmlgen.closecontext(qryctx1);
895 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
896 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
897
898 query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_nspon_proposed_salary, '
899 || 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_nspon_proposed_effort, '
900 || 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_nspon_cost_share, '
901 || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_nspon_actual_salary, '
902 || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_nspon_actual_effort, '
903 || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_nspon_overwritten_effort, '
904 || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_nspon_actual_cost_share '
905 || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '
906 || 'WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
907 || 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
908 || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type (+) = ppa.project_type AND '
909 || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
910 || '''N'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
911 || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
912 qryCtx1 := dbms_xmlgen.newContext(query1);
913 dbms_xmlgen.setRowTag(qryCtx1, NULL);
914 dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_NON_SPONSORED');
915 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
916 dbms_xmlgen.closecontext(qryctx1);
917 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
918 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
919
920 query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_gl_proposed_salary, '
921 || 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_gl_proposed_effort, '
922 || 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_gl_cost_share, '
923 || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_gl_actual_salary, '
924 || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_gl_actual_effort, '
925 || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_gl_overwritten_effort, '
926 || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_gl_actual_cost_share '
927 || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '
928 || 'WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND '
929 || '(perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL OR perd.gl_segment3 IS NOT NULL OR perd.gl_segment4 '
930 || 'IS NOT NULL OR perd.gl_segment5 IS NOT NULL OR perd.gl_segment6 IS NOT NULL OR perd.gl_segment7 IS NOT NULL OR '
931 || 'perd.gl_segment8 IS NOT NULL OR perd.gl_segment9 IS NOT NULL OR perd.gl_segment10 IS NOT NULL OR perd.gl_segment11'
932 || ' IS NOT NULL OR perd.gl_segment12 IS NOT NULL OR perd.gl_segment13 IS NOT NULL OR perd.gl_segment14 IS NOT NULL OR'
933 || ' perd.gl_segment15 IS NOT NULL OR perd.gl_segment16 IS NOT NULL OR perd.gl_segment17 IS NOT NULL OR '
934 || 'perd.gl_segment18 IS NOT NULL OR perd.gl_segment19 IS NOT NULL OR perd.gl_segment20 IS NOT NULL OR '
935 || 'perd.gl_segment21 IS NOT NULL OR perd.gl_segment22 IS NOT NULL OR perd.gl_segment23 IS NOT NULL OR '
936 || 'perd.gl_segment24 IS NOT NULL OR perd.gl_segment25 IS NOT NULL OR perd.gl_segment26 IS NOT NULL OR '
937 || 'perd.gl_segment27 IS NOT NULL OR perd.gl_segment28 IS NOT NULL OR perd.gl_segment29 IS NOT NULL OR '
938 || 'perd.gl_segment30 IS NOT NULL) AND effort_report_id = ' || TO_CHAR(p_effort_report_id)
939 || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
940 qryCtx1 := dbms_xmlgen.newContext(query1);
941 dbms_xmlgen.setRowTag(qryCtx1, NULL);
942 dbms_xmlgen.setRowSetTag(qryCtx1, 'TOTAL_GL');
943 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
944 dbms_xmlgen.closecontext(qryctx1);
945 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
946 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
947
948 query1 := 'SELECT TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '
949 || 'TRIM(TO_CHAR(perd.PAYROLL_PERCENT, ''999G990D00'')) payroll_percent, '
950 || 'TRIM(TO_CHAR(perd.PROPOSED_SALARY_AMT, ''' || l_num25_fmask || ''')) proposed_salary_amt, '
951 || 'TRIM(TO_CHAR(perd.PROPOSED_EFFORT_PERCENT, ''999G990D00'')) proposed_effort_percent, '
952 || 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
953 || 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
954 || 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
955 || 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
956 || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
957 || 'psp_eff_report_approvals pera WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
958 || 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
959 || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type = ppa.project_type AND '
960 || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
961 || '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
962 || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;
963 qryCtx1 := dbms_xmlgen.newContext(query1);
964 dbms_xmlgen.setRowTag(qryCtx1, 'G_SPONSORED');
965 dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_SPONSORED');
966 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
967 dbms_xmlgen.closecontext(qryctx1);
968 IF (dbms_lob.getlength(xmlresult1) > 0) THEN
969 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
970 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
971 END IF;
972
973 query1 := 'SELECT TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '
974 || 'TRIM(TO_CHAR(perd.PAYROLL_PERCENT, ''999G990D00'')) payroll_percent, '
975 || 'TRIM(TO_CHAR(perd.PROPOSED_SALARY_AMT, ''' || l_num25_fmask || ''')) proposed_salary_amt, '
976 || 'TRIM(TO_CHAR(perd.PROPOSED_EFFORT_PERCENT, ''999G990D00'')) proposed_effort_percent, '
977 || 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
978 || 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
979 || 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
980 || 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
981 || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
982 || 'psp_eff_report_approvals pera WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
983 || 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
984 || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type(+) = ppa.project_type AND '
985 || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
986 || '''N'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
987 || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;
988 qryCtx1 := dbms_xmlgen.newContext(query1);
989 dbms_xmlgen.setRowTag(qryCtx1, 'G_NON_SPONSORED');
990 dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_NON_SPONSORED');
991 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
992 dbms_xmlgen.closecontext(qryctx1);
993 IF (dbms_lob.getlength(xmlresult1) > 0) THEN
994 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
995 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
996 END IF;
997
998 query1 := 'SELECT TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '
999 || 'TRIM(TO_CHAR(perd.PAYROLL_PERCENT, ''999G990D00'')) payroll_percent, '
1000 || 'TRIM(TO_CHAR(perd.PROPOSED_SALARY_AMT, ''' || l_num25_fmask || ''')) proposed_salary_amt, '
1001 || 'TRIM(TO_CHAR(perd.PROPOSED_EFFORT_PERCENT, ''999G990D00'')) proposed_effort_percent, '
1002 || 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
1003 || 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
1004 || 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
1005 || 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
1006 || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
1007 || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND '
1008 || '(perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL OR perd.gl_segment3 IS NOT NULL OR '
1009 || 'perd.gl_segment4 IS NOT NULL OR perd.gl_segment5 IS NOT NULL OR perd.gl_segment6 IS NOT NULL OR '
1010 || 'perd.gl_segment7 IS NOT NULL OR perd.gl_segment8 IS NOT NULL OR perd.gl_segment9 IS NOT NULL OR '
1011 || 'perd.gl_segment10 IS NOT NULL OR perd.gl_segment11 IS NOT NULL OR perd.gl_segment12 IS NOT NULL OR '
1012 || 'perd.gl_segment13 IS NOT NULL OR perd.gl_segment14 IS NOT NULL OR perd.gl_segment15 IS NOT NULL OR '
1013 || 'perd.gl_segment16 IS NOT NULL OR perd.gl_segment17 IS NOT NULL OR perd.gl_segment18 IS NOT NULL OR '
1014 || 'perd.gl_segment19 IS NOT NULL OR perd.gl_segment20 IS NOT NULL OR perd.gl_segment21 IS NOT NULL OR '
1015 || 'perd.gl_segment22 IS NOT NULL OR perd.gl_segment23 IS NOT NULL OR perd.gl_segment24 IS NOT NULL OR '
1016 || 'perd.gl_segment25 IS NOT NULL OR perd.gl_segment26 IS NOT NULL OR perd.gl_segment27 IS NOT NULL OR '
1017 || 'perd.gl_segment28 IS NOT NULL OR perd.gl_segment29 IS NOT NULL OR perd.gl_segment30 IS NOT NULL) '
1018 || 'AND effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
1019 || l_assignment_check || l_sort_option1;
1020 qryCtx1 := dbms_xmlgen.newContext(query1);
1021 dbms_xmlgen.setRowTag(qryCtx1, 'G_GL');
1022 dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_GL');
1023 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1024 dbms_xmlgen.closecontext(qryctx1);
1025 IF (dbms_lob.getlength(xmlresult1) > 0) THEN
1026 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
1027 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
1028 END IF;
1029 */
1030 /* Changes for Hospital effort report : END */
1031
1032 dbms_lob.writeappend(l_xml, length('</G_ASSIGNMENT> '), '</G_ASSIGNMENT> ');
1033 END LOOP;
1034 CLOSE assign_cur;
1035 dbms_lob.writeappend(l_xml, length('</LIST_G_ASSIGNMENT> '), '</LIST_G_ASSIGNMENT> ');
1036
1037 --Bug 4334816: START
1038 -- Including the WorkFlow Note in Pdf
1039 /*
1040
1041 query1 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) approver_name, '
1042 || 'TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
1043 || ' approval_date FROM psp_eff_report_approvals pera, psp_eff_report_details perd '
1044 || 'WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
1045 || TO_CHAR(p_effort_report_id) || ' ORDER BY approver_order_num DESC';
1046 */
1047
1048
1049 /*TGEN bug 6864426*/
1050
1051 SELECT distinct prt.approval_type INTO l_approval_type
1052 FROM psp_report_templates prt,
1053 psp_eff_reports per
1054 where per.effort_report_id = p_effort_report_id
1055 and per.template_id = prt.template_id;
1056
1057
1058 IF (l_approval_type <> 'PRE') THEN
1059 query1 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) approver_name, '
1060 || 'TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
1061 || ' approval_date, wfna.TEXT_VALUE note FROM psp_eff_report_approvals pera, psp_eff_report_details perd, WF_NOTIFICATION_ATTRIBUTES wfna '
1062 || ' WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
1063 || TO_CHAR(p_effort_report_id) || ' AND wfna.NAME(+) =''WF_NOTE'' AND wfna.NOTIFICATION_ID(+) = pera.NOTIFICATION_ID '
1064 || ' ORDER BY approver_order_num DESC';
1065 ELSE -- Added this ELSE for TGEN bug 6864426
1066 query1 := 'SELECT 1 approval_sequence, ''PRE-APPROVED'' approver_name, '
1067 || 'TO_CHAR(sysdate, ''' || l_icx_date_format || ''')'
1068 || 'approval_date, ''Process Initiated by ''||''' || l_initiator_name || ''' note FROM DUAL';
1069
1070 END IF;
1071 --Bug 4334816: END
1072
1073 qryCtx1 := dbms_xmlgen.newContext(query1);
1074 dbms_xmlgen.setRowTag(qryCtx1, 'G_APPROVER');
1075 dbms_xmlgen.setRowSetTag(qryCtx1, 'LIST_G_APPROVER');
1076 xmlresult1 := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1077 dbms_xmlgen.closecontext(qryctx1);
1078 IF (dbms_lob.getlength(xmlresult1) > 0) THEN
1079 l_resultOffset := DBMS_LOB.INSTR(xmlresult1,'>');
1080 dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, dbms_lob.getlength(l_xml), l_resultOffset +1);
1081 END IF;
1082
1083 dbms_lob.writeappend(l_xml, length('</G_PERSON> '), '</G_PERSON> ');
1084 ELSE
1085 ---- 4429787 : replaced variables with col names
1086 query1 := 'SELECT ' || TO_CHAR(p_person_id) || ' person_id, er.full_name employee_name, employee_number '
1087 || ' employee_number, '|| to_char(p_emp_primary_org_id) || ' organization_id, emp_primary_org_name '
1088 || ' organization_name, mailstop mailstop, '
1089 || 'TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '
1090 || 'TRIM(TO_CHAR(perd.PAYROLL_PERCENT, ''999G990D00'')) payroll_percent, '
1091 || 'TRIM(TO_CHAR(perd.PROPOSED_SALARY_AMT, ''' || l_num25_fmask || ''')) proposed_salary_amt, '
1092 || 'TRIM(TO_CHAR(perd.PROPOSED_EFFORT_PERCENT, ''999G990D00'')) proposed_effort_percent, '
1093 || 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
1094 || 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
1095 || 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
1096 || 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
1097 || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share, '
1098 || 'pera.pera_information1, pera.pera_information2, pera.pera_information3, pera.pera_information4, pera.pera_information5, '
1099 || 'pera.pera_information6, pera.pera_information7, pera.pera_information8, pera.pera_information9, pera.pera_information10, '
1100 || 'pera.pera_information11, pera.pera_information12, pera.pera_information13, pera.pera_information14, pera.pera_information15, '
1101 || 'pera.pera_information16, pera.pera_information17, pera.pera_information8, pera.pera_information19, pera.pera_information20 '
1102 || 'FROM psp_eff_Reports er, psp_eff_report_details perd, '
1103 || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND er.effort_report_id = perd.effort_Report_id and '
1104 || ' perd.effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
1105 || l_er_check || l_sort_option1;
1106
1107 qryCtx1 := dbms_xmlgen.newContext(query1);
1108 dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1109 dbms_xmlgen.setRowTag(qryCtx1, 'G_PERSON');
1110 --dbms_xmlgen.setRowsetTag(qryCtx1, 'LIST_G_PERSON');
1111 l_xml := dbms_xmlgen.getXML(qryCtx1, dbms_xmlgen.NONE);
1112 l_resultOffset := DBMS_LOB.INSTR(l_xml,'>');
1113 dbms_lob.write(l_xml, LENGTH(RPAD(' ', l_resultOffset, ' ')), 1, RPAD(' ', l_resultOffset, ' '));
1114 -- dbms_lob.copy(l_xml, xmlresult1, dbms_lob.getlength(xmlresult1) - l_resultOffset, 1, l_resultOffset +1);
1115 dbms_xmlgen.closecontext(qryctx1);
1116 END IF;
1117
1118 RETURN l_xml;
1119 EXCEPTION
1120 WHEN OTHERS THEN
1121 OPEN add_report_error_cur(sqlerrm);
1122 FETCH add_report_error_cur INTO l_error_count;
1123 CLOSE add_report_error_cur;
1124
1125 IF (NVL(l_error_count, 0) = 0) THEN
1126 psp_general.add_report_error
1127 (p_request_id => p_request_id,
1128 p_message_level => 'E',
1129 p_source_id => p_person_id, --- person_id replaces NULL-- 4429787
1130 p_retry_request_id => NULL,
1131 p_pdf_request_id => g_request_id,
1132 p_error_message => sqlerrm,
1133 p_return_status => l_return_status);
1134 END IF;
1135 RAISE;
1136 END generate_person_xml;
1137
1138 PROCEDURE store_pdf (p_wf_item_key IN NUMBER,
1139 p_receiver_flag IN VARCHAR2,
1140 p_file_id OUT NOCOPY NUMBER,
1141 p_wf_Role_Name IN VARCHAR2) IS
1142 l_category_id NUMBER;
1143 l_pdf_file_id NUMBER;
1144 l_request_id NUMBER;
1145 l_pdf_filename VARCHAR2(100);
1146 l_row_id_tmp VARCHAR2(100);
1147 l_document_id_tmp NUMBER;
1148
1149 CURSOR document_category_cur IS
1150 SELECT category_id
1151 FROM fnd_document_categories
1152 WHERE name = 'CUSTOM3788';
1153
1154 CURSOR pdf_filename_cur IS
1155 SELECT message_text
1156 FROM fnd_new_messages fnm
1157 WHERe fnm.message_name = 'PSP_ER_PDF_FILENAME'
1158 AND language_code = USERENV('LANG');
1159 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
1160 SELECT 1
1161 FROM psp_report_errors
1162 WHERE request_id = l_request_id
1163 AND message_level = 'E'
1164 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
1165 AND pdf_request_id = g_request_id;
1166
1167 CURSOR get_request_id_cur IS
1168 SELECT request_id
1169 FROM psp_eff_reports per
1170 WHERE EXISTS (SELECT 1
1171 FROM psp_eff_report_details perd,
1172 psp_eff_report_approvals pera
1173 WHERE perd.effort_report_id = per.effort_report_id
1174 AND perd.effort_report_detail_id = pera.effort_report_detail_id)
1175 AND ROWNUM = 1;
1176
1177 CURSOR get_file_id_cur IS
1178 SELECT fl.file_id
1179 FROM fnd_lobs fl,
1180 fnd_attached_documents fad,
1181 fnd_documents_vl fdl
1182 WHERE fad.pk1_value = TO_CHAR(p_wf_item_key)||p_wf_Role_Name
1183 --AND fad.pk3_value = p_wf_role_name
1184 AND fdl.document_id = fad.document_id
1185 AND fdl.media_id = fl.file_id
1186 AND fad.entity_name = 'ERDETAILS'
1187 AND NVL(fad.pk2_value, 'AR') = NVL(p_receiver_flag, 'AR');
1188
1189 l_error_count NUMBER;
1190 l_return_status CHAR(1);
1191 BEGIN
1192 g_request_id := fnd_global.conc_request_id;
1193
1194 OPEN get_request_id_cur;
1195 FETCH get_request_id_cur INTO l_request_id;
1196 CLOSE get_request_id_cur;
1197
1198 OPEN document_category_cur;
1199 FETCH document_category_cur INTO l_category_id;
1200 CLOSE document_category_cur;
1201
1202 OPEN pdf_filename_cur;
1203 FETCH pdf_filename_cur INTO l_pdf_filename;
1204 CLOSE pdf_filename_cur;
1205
1206 OPEN get_file_id_cur;
1207 FETCH get_file_id_cur INTO l_pdf_file_id;
1208 CLOSE get_file_id_cur;
1209
1210 IF (l_pdf_file_id IS NULL) THEN
1211 fnd_documents_pkg.insert_row
1212 (X_Rowid => l_row_id_tmp,
1213 X_document_id => l_document_id_tmp,
1214 X_creation_date => SYSDATE,
1215 X_created_by => 1,
1216 X_last_update_date => SYSDATE,
1217 X_last_updated_by => 1,
1218 X_last_update_login => 1,
1219 X_datatype_id => 6,
1220 X_category_id => l_category_id,
1221 X_security_type => 1,
1222 X_security_id => NULL,
1223 X_publish_flag => 'Y',
1224 X_image_type => NULL,
1225 X_storage_type => NULL,
1226 X_usage_type => 'O',
1227 X_start_date_active => SYSDATE,
1228 X_end_date_active => NULL,
1229 X_request_id => NULL,
1230 X_program_application_id => NULL,
1231 X_program_id => NULL,
1232 X_program_update_date => SYSDATE,
1233 X_language => USERENV('LANG'),
1234 X_description => NULL,
1235 X_file_name => l_pdf_filename,
1236 X_media_id => l_pdf_file_id);
1237
1238 INSERT INTO fnd_lobs
1239 (file_id, File_name, file_content_type,
1240 upload_date, expiration_date, program_name,
1241 program_tag, file_data, language,
1242 oracle_charset, file_format)
1243 VALUES
1244 (l_pdf_file_id, l_pdf_filename, 'application/pdf',
1245 SYSDATE, NULL, 'PSPERPDF',
1246 NULL, empty_blob(), USERENV('LANG'),
1247 NULL, 'binary');
1248
1249 INSERT INTO fnd_attached_documents
1250 (attached_document_id, document_id, creation_date,
1251 created_by, last_update_date, last_updated_by,
1252 last_update_login, seq_num, entity_name,
1253 pk1_value, pk2_value, pk3_value,
1254 pk4_value, pk5_value, automatically_added_flag,
1255 program_application_id, program_id, program_update_date,
1256 request_id, attribute_category, attribute1,
1257 attribute2, attribute3, attribute4,
1258 attribute5, attribute6, attribute7,
1259 attribute8, attribute9, attribute10,
1260 attribute11, attribute12, attribute13,
1261 attribute14, attribute15, column1)
1262 VALUES (fnd_attached_documents_s.nextval, l_document_id_tmp, SYSDATE,
1263 1, SYSDATE, 1,
1264 NULL, 10, 'ERDETAILS',
1265 TO_CHAR(p_wf_item_key)||p_wf_Role_Name, p_receiver_flag, null,
1266 NULL, NULL, 'N',
1267 NULL, NULL, SYSDATE,
1268 NULL, NULL, NULL,
1269 NULL, NULL, NULL,
1270 NULL, NULL, NULL,
1271 NULL, NULL, NULL,
1272 NULL, NULL, NULL,
1273 NULL, NULL, NULL);
1274 END IF;
1275
1276 p_file_id := l_pdf_file_id;
1277 EXCEPTION
1278 WHEN OTHERS THEN
1279 OPEN add_report_error_cur(sqlerrm);
1280 FETCH add_report_error_cur INTO l_error_count;
1281 CLOSE add_report_error_cur;
1282
1283 IF (NVL(l_error_count, 0) = 0) THEN
1284 psp_general.add_report_error
1285 (p_request_id => l_request_id,
1286 p_message_level => 'E',
1287 p_source_id => NULL,
1288 p_retry_request_id => NULL,
1289 p_pdf_request_id => g_request_id,
1290 p_error_message => sqlerrm,
1291 p_return_status => l_return_status);
1292 END IF;
1293 RAISE;
1294 END store_pdf;
1295
1296 PROCEDURE attach_pdf (p_item_type_key IN VARCHAR2,
1297 content_type IN VARCHAR2,
1298 p_document IN OUT NOCOPY BLOB,
1299 p_document_type IN OUT NOCOPY VARCHAR2) IS
1300 l_item_type VARCHAR2(100);
1301 l_item_key VARCHAR2(100);
1302 l_document_length NUMBER;
1303 l_pdf_file_id NUMBER;
1304 l_request_id NUMBER;
1305 l_document BLOB;
1306 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
1307 SELECT 1
1308 FROM psp_report_errors
1309 WHERE request_id = l_request_id
1310 AND message_level = 'E'
1311 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
1312 AND pdf_request_id = g_request_id;
1313
1314 CURSOR get_request_id_cur IS
1315 SELECT request_id
1316 FROM psp_eff_reports per
1317 WHERE EXISTS (SELECT 1
1318 FROM psp_eff_report_details perd,
1319 psp_eff_report_approvals pera
1320 WHERE perd.effort_report_id = per.effort_report_id
1321 AND perd.effort_report_detail_id = pera.effort_report_detail_id)
1322 AND ROWNUM = 1;
1323
1324 l_error_count NUMBER;
1325 l_return_status CHAR(1);
1326 l_rname wf_roles.name%type; -- Bug 7135471
1327 l_file_name fnd_lobs.file_name%type; -- Bug 7229792
1328
1329 BEGIN
1330 OPEN get_request_id_cur;
1331 FETCH get_request_id_cur INTO l_request_id;
1332 CLOSE get_request_id_cur;
1333
1334 hr_utility.trace('LD Debug p_item_type_key = '||p_item_type_key);
1335
1336 g_request_id := fnd_global.conc_request_id;
1337 l_item_type := SUBSTR(p_item_type_key, 1, INSTR(p_item_type_key, ':') - 1);
1338 l_item_key := SUBSTR(p_item_type_key, INSTR(p_item_type_key, ':') + 1, length(p_item_type_key) - 2);
1339
1340 hr_utility.trace('LD Debug l_item_type = '||l_item_type);
1341 hr_utility.trace('LD Debug l_item_key = '||l_item_key);
1342
1343 l_rname := NVL(
1344 wf_engine.GetItemAttrText(itemtype => l_item_type,
1345 itemkey => l_item_key,
1346 aname => 'APPROVER_ROLE_NAME'), null); -- Bug 7135471
1347
1348 hr_utility.trace('LD Debug l_rname = '||l_rname);
1349
1350 SELECT file_data,
1351 file_id -- Bug 7135471
1352 INTO l_document,
1353 l_pdf_file_id -- Bug 7135471
1354 FROM fnd_lobs fl,
1355 fnd_attached_documents fad,
1356 fnd_documents_vl fdl
1357 WHERE fad.pk1_value = l_item_key || l_rname
1358 AND fdl.document_id = fad.document_id
1359 AND fdl.media_id = fl.file_id
1360 AND fad.entity_name = 'ERDETAILS'
1361 AND fl.file_id = (select max(file_id) FROM fnd_lobs fl, -- Bug 7135471
1362 fnd_attached_documents fad,
1363 fnd_documents_vl fdl
1364 WHERE fad.pk1_value = l_item_key || l_rname
1365 AND fdl.document_id = fad.document_id
1366 AND fdl.media_id = fl.file_id
1367 AND fad.entity_name = 'ERDETAILS');
1368
1369 l_file_name := 'PSP_ER_' || l_item_key || '.pdf' ; -- Bug 7229792
1370
1371 hr_utility.trace('LD Debug l_pdf_file_id = '||l_pdf_file_id);
1372 l_document_length := DBMS_LOB.getlength(l_document);
1373 DBMS_LOB.copy(p_document, l_document, l_document_length, 1, 1);
1374 p_document_type:='application/pdf; name='|| l_file_name; -- Bug 7229792
1375
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 WF_CORE.Context('PSP_ER_XML','ATTACH_PDF', l_item_key,
1379 content_type, p_document_type, sqlerrm);
1380 OPEN add_report_error_cur(sqlerrm);
1381 FETCH add_report_error_cur INTO l_error_count;
1382 CLOSE add_report_error_cur;
1383
1384 IF (NVL(l_error_count, 0) = 0) THEN
1385 psp_general.add_report_error
1386 (p_request_id => l_request_id,
1387 p_message_level => 'E',
1388 p_source_id => NULL,
1389 p_retry_request_id => NULL,
1390 p_pdf_request_id => g_request_id,
1391 p_error_message => sqlerrm,
1392 p_return_status => l_return_status);
1393 END IF;
1394 RAISE;
1395 END attach_pdf;
1396
1397 PROCEDURE update_er_person_xml (p_start_person IN NUMBER,
1398 p_end_person IN NUMBER,
1399 p_request_id IN NUMBER,
1400 p_retry_request_id IN NUMBER DEFAULT NULL,
1401 p_return_status OUT NOCOPY VARCHAR2) IS
1402 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
1403 SELECT 1
1404 FROM psp_report_errors
1405 WHERE request_id = p_request_id
1406 AND message_level = 'E'
1407 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
1408 AND pdf_request_id = g_request_id;
1409 --- added er_cur for 4429787
1410 rec psp_Eff_reports%rowtype;
1411 cursor er_cur is select effort_Report_id,
1412 person_id, template_id, request_id, set_of_books_id, full_name,
1413 employee_number, mailstop, emp_primary_org_name, emp_primary_org_id, currency_code
1414 from psp_Eff_reports where request_id = p_request_id
1415 AND person_id BETWEEN p_start_person AND p_end_person
1416 AND status_code <> 'R';
1417 x_lob clob;
1418 l_error_count NUMBER;
1419 l_return_status CHAR(1);
1420
1421
1422 BEGIN
1423 g_request_id := fnd_global.conc_request_id;
1424 --- replaced single bulk update with a loop b'cos of xml error
1425 ---ORA-29532, ORA-6512 for bug fix 4429787
1426 open er_cur;
1427 loop
1428 fetch er_cur into rec.effort_Report_id,
1429 rec.person_id, rec.template_id, rec.request_id,
1430 rec.set_of_books_id, rec.full_name, rec.employee_number, rec.mailstop, rec.emp_primary_org_name,
1431 rec.emp_primary_org_id, rec.currency_code;
1432
1433 if er_cur%notfound then
1434 close er_cur;
1435 exit;
1436 end if;
1437
1438 x_lob := psp_xmlgen.generate_person_xml(rec.person_id, rec.template_id,
1439 rec.effort_report_id, rec.request_id,
1440 rec.set_of_books_id, rec.full_name,
1441 rec.employee_number, rec.mailstop,
1442 rec.emp_primary_org_name, rec.emp_primary_org_id,
1443 rec.currency_code );
1444
1445
1446 UPDATE psp_eff_reports
1447 SET person_xml = x_lob
1448 WHERE effort_report_id = rec.effort_report_id;
1449
1450 end loop;
1451 p_return_status := 'S';
1452 EXCEPTION
1453 WHEN OTHERS THEN
1454 fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'UPDATE_ER_PERSON_XML', sqlerrm);
1455 OPEN add_report_error_cur(sqlerrm);
1456 FETCH add_report_error_cur INTO l_error_count;
1457 CLOSE add_report_error_cur;
1458
1459 IF (NVL(l_error_count, 0) = 0) THEN
1460 psp_general.add_report_error
1461 (p_request_id => p_request_id,
1462 p_message_level => 'E',
1463 p_source_id => rec.person_id,
1464 p_retry_request_id => p_retry_request_id,
1465 p_pdf_request_id => g_request_id,
1466 p_error_message => sqlerrm,
1467 p_return_status => l_return_status);
1468 END IF;
1469 p_return_status := 'E';
1470 END update_er_person_xml;
1471
1472 PROCEDURE update_er_person_xml (p_request_id IN NUMBER,
1473 p_return_status OUT NOCOPY VARCHAR2) IS
1474 PRAGMA AUTONOMOUS_TRANSACTION;
1475 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
1476 SELECT 1
1477 FROM psp_report_errors
1478 WHERE request_id = p_request_id
1479 AND message_level = 'E'
1480 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
1481 AND pdf_request_id = g_request_id;
1482
1483 l_error_count NUMBER;
1484 l_return_status CHAR(1);
1485 BEGIN
1486 g_request_id := fnd_global.conc_request_id;
1487 UPDATE psp_eff_reports
1488 SET person_xml = generate_person_xml(person_id, template_id, effort_report_id, request_id, set_of_books_id, full_name, employee_number, mailstop, emp_primary_org_name, emp_primary_org_id, currency_code)
1489 WHERE request_id = p_request_id
1490 AND status_code <> 'R';
1491
1492 COMMIT;
1493
1494 p_return_status := 'S';
1495 EXCEPTION
1496 WHEN OTHERS THEN
1497 fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'UPDATE_ER_PERSON_XML', sqlerrm);
1498 OPEN add_report_error_cur(sqlerrm);
1499 FETCH add_report_error_cur INTO l_error_count;
1500 CLOSE add_report_error_cur;
1501
1502 IF (NVL(l_error_count, 0) = 0) THEN
1503 psp_general.add_report_error
1504 (p_request_id => p_request_id,
1505 p_message_level => 'E',
1506 p_source_id => NULL,
1507 p_retry_request_id => NULL,
1508 p_pdf_request_id => g_request_id,
1509 p_error_message => sqlerrm,
1510 p_return_status => l_return_status);
1511 END IF;
1512 p_return_status := 'E';
1513 END update_er_person_xml;
1514
1515 PROCEDURE update_er_person_xml (p_wf_item_key IN NUMBER,
1516 p_return_status OUT NOCOPY VARCHAR2) IS
1517 CURSOR get_request_id IS
1518 SELECT request_id
1519 FROM psp_eff_reports per,
1520 psp_eff_report_details perd,
1521 psp_eff_report_approvals pera
1522 WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
1523 AND per.effort_report_id = perd.effort_report_id
1524 AND pera.wf_item_key = p_wf_item_key;
1525
1526 l_request_id NUMBER;
1527
1528 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
1529 SELECT 1
1530 FROM psp_report_errors
1531 WHERE request_id = l_request_id
1532 AND message_level = 'E'
1533 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
1534 AND pdf_request_id = g_request_id;
1535
1536 l_error_count NUMBER;
1537 l_return_status CHAR(1);
1538
1539 CURSOR person_cur IS
1540 SELECT person_id,
1541 template_id,
1542 effort_report_id,
1543 request_id,
1544 set_of_books_id,
1545 full_name,
1546 employee_number,
1547 mailstop,
1548 emp_primary_org_name,
1549 emp_primary_org_id,
1550 currency_code
1551 FROM psp_eff_reports
1552 WHERE effort_report_id IN (SELECT effort_report_id
1553 FROM psp_eff_report_details perd,
1554 psp_eff_report_approvals pera
1555 WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
1556 AND pera.wf_item_key = p_wf_item_key)
1557 AND status_code <> 'R';
1558
1559 person_rec person_cur%ROWTYPE;
1560 l_xml CLOB;
1561 BEGIN
1562 g_request_id := fnd_global.conc_request_id;
1563
1564 OPEN person_cur;
1565 l_xml := empty_clob();
1566 LOOP
1567 FETCH person_cur INTO person_rec;
1568 EXIT WHEN person_cur%NOTFOUND;
1569
1570 l_xml := generate_person_xml(person_rec.person_id,
1571 person_rec.template_id,
1572 person_rec.effort_report_id,
1573 person_rec.request_id,
1574 person_rec.set_of_books_id,
1575 person_rec.full_name,
1576 person_rec.employee_number,
1577 person_rec.mailstop,
1578 person_rec.emp_primary_org_name,
1579 person_rec.emp_primary_org_id,
1580 person_rec.currency_code );
1581
1582 UPDATE psp_eff_reports
1583 SET person_xml = l_xml
1584 WHERE effort_report_id = person_rec.effort_report_id;
1585 END LOOP;
1586 CLOSE person_cur;
1587
1588 /***** Converted single xml clob update statement into row by row update
1589 UPDATE psp_eff_reports
1590 SET person_xml = generate_person_xml(person_id, template_id, effort_report_id, request_id, set_of_books_id, full_name, employee_number, mailstop, emp_primary_org_name, emp_primary_org_id, currency_code)
1591 WHERE effort_report_id IN (SELECT effort_report_id
1592 FROM psp_eff_report_details perd,
1593 psp_eff_report_approvals pera
1594 WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
1595 AND pera.wf_item_key = p_wf_item_key)
1596 AND status_code <> 'R';
1597 End of changes for bug fix 4429787 *****/
1598 p_return_status := 'S';
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'UPDATE_ER_PERSON_XML', sqlerrm);
1602 OPEN get_request_id;
1603 FETCH get_request_id INTO l_request_id;
1604 CLOSE get_request_id;
1605
1606 OPEN add_report_error_cur(sqlerrm);
1607 FETCH add_report_error_cur INTO l_error_count;
1608 CLOSE add_report_error_cur;
1609
1610 IF (NVL(l_error_count, 0) = 0) THEN
1611 psp_general.add_report_error
1612 (p_request_id => l_request_id,
1613 p_message_level => 'E',
1614 p_source_id => NULL,
1615 p_retry_request_id => NULL,
1616 p_pdf_request_id => g_request_id,
1617 p_error_message => sqlerrm,
1618 p_return_status => l_return_status);
1619 END IF;
1620 p_return_status := 'E';
1621 END update_er_person_xml;
1622
1623
1624
1625 PROCEDURE update_er_details (p_start_person IN NUMBER,
1626 p_end_person IN NUMBER,
1627 p_request_id IN NUMBER,
1628 p_retry_request_id IN NUMBER DEFAULT NULL,
1629 p_return_status OUT NOCOPY VARCHAR2) IS
1630 TYPE t_num_15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
1631 TYPE t_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1632 TYPE effort_report_id_type IS RECORD
1633 (r_effort_report_id t_num_15_type,
1634 r_start_date t_date_type,
1635 r_end_date t_date_type);
1636
1637 r_effort_report effort_report_id_type;
1638
1639 l_layout_type CHAR(3);
1640 l_report_template_code CHAR(80);
1641 l_set_of_books_id NUMBER;
1642 l_segment_delimiter CHAR(1);
1643 l_query VARCHAR2(4000);
1644 l_segment_name VARCHAR2(50);
1645 l_template_id NUMBER(15);
1646
1647 CURSOR effort_report_id_cur IS
1648 SELECT effort_report_id,
1649 start_date,
1650 end_date
1651 FROM psp_eff_reports per
1652 WHERE person_id BETWEEN p_start_person AND p_end_person
1653 AND status_code <> 'R'
1654 AND request_id = p_request_id;
1655
1656 CURSOR layout_type_cur IS
1657 SELECT SUBSTR(report_template_code, 6, 3) layout_type,
1658 per.template_id,
1659 per.set_of_books_id,
1660 prth.report_template_code
1661 FROM psp_eff_reports per,
1662 psp_report_templates_h prth
1663 WHERE per.request_id = p_request_id
1664 AND prth.request_id = per.request_id
1665 AND ROWNUM = 1;
1666
1667 CURSOR get_segment_delimeter_cur IS
1668 SELECT fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gsob.chart_of_accounts_id)
1669 FROM gl_sets_of_books gsob
1670 WHERE gsob.set_of_books_id = l_set_of_books_id;
1671
1672 CURSOR get_segment_name_cur IS
1673 SELECT 'GL_' || fifs.application_column_name || ' || ''' || l_segment_delimiter || ''' || ' segment_name
1674 FROM fnd_id_flex_segments fifs,
1675 gl_sets_of_books gsob,
1676 fnd_application fa
1677 WHERE gsob.set_of_books_id = l_set_of_books_id
1678 AND fifs.id_flex_num = gsob.chart_of_accounts_id
1679 AND fifs.id_flex_code = 'GL#'
1680 AND fifs.application_id = fa.application_id
1681 AND fa.application_short_name = 'SQLGL'
1682 AND EXISTS (SELECT 1
1683 FROM psp_report_template_details_h prtdh
1684 WHERE prtdh.REQUEST_ID= p_request_id
1685 AND prtdh.criteria_lookup_type = 'PSP_SUMMARIZATION_CRITERIA'
1686 AND prtdh.criteria_lookup_code = fifs.application_column_name)
1687 ORDER BY fifs.segment_num;
1688
1689 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
1690 SELECT 1
1691 FROM psp_report_errors
1692 WHERE request_id = p_request_id
1693 AND message_level = 'E'
1694 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
1695 AND pdf_request_id = g_request_id;
1696
1697 l_error_count NUMBER;
1698 l_return_status CHAR(1);
1699 l_project_manager_role VARCHAR2(30);
1700
1701 CURSOR project_manager_role_cur IS
1702 SELECT layout_lookup_code
1703 FROM psp_layout_options
1704 WHERE report_template_code = TRIM(l_report_template_code)
1705 AND layout_lookup_type = 'PSP_PROJECT_MANAGER_ROLE';
1706
1707
1708 BEGIN
1709 g_request_id := fnd_global.conc_request_id;
1710 OPEN effort_report_id_cur;
1711 FETCH effort_report_id_cur BULK COLLECT INTO r_effort_report.r_effort_report_id, r_effort_report.r_start_date, r_effort_report.r_end_date;
1712 CLOSE effort_report_id_cur;
1713
1714 OPEN layout_type_cur;
1715 FETCH layout_type_cur INTO l_layout_type, l_template_id, l_set_of_books_id, l_report_template_code;
1716 CLOSE layout_type_cur;
1717
1718 OPEN project_manager_role_cur;
1719 FETCH project_manager_role_cur INTO l_project_manager_role;
1720 CLOSE project_manager_role_cur;
1721
1722 l_project_manager_role := NVL(l_project_manager_role, 'PROJECT MANAGER');
1723
1724 IF (l_layout_type = 'EMP') THEN
1725 FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1726 UPDATE psp_eff_report_details perd
1727 SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1728 WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1729 AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
1730 AND paaf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
1731 (project_name, project_number) = (SELECT name, segment1 FROM pa_projects_all paa
1732 WHERE paa.project_id = perd.project_id),
1733 (award_short_name, award_number) = (SELECT award_short_name, award_number FROM gms_awards_all gaa
1734 WHERE gaa.award_id = perd.award_id),
1735 (task_name, task_number) = (SELECT task_name, task_number FROM pa_tasks pt WHERE pt.task_id = perd.task_id),
1736 exp_org_name = (SELECT name FROM hr_all_organization_units haou
1737 WHERE haou.organization_id = perd.expenditure_organization_id),
1738 actual_salary_amt = NVL(actual_salary_amt, 0),
1739 payroll_percent = NVL(payroll_percent, 0),
1740 proposed_salary_amt = NVL(proposed_salary_amt, 0),
1741 proposed_effort_percent = NVL(proposed_effort_percent, 0),
1742 committed_cost_share = NVL(committed_cost_share, 0)
1743 WHERE perd.effort_report_id = r_effort_report.r_effort_report_id(I);
1744
1745 OPEN get_segment_delimeter_cur;
1746 FETCH get_segment_delimeter_cur INTO l_segment_delimiter;
1747 CLOSE get_segment_delimeter_cur;
1748
1749 OPEN get_segment_name_cur;
1750 l_query := '';
1751 LOOP
1752 FETCH get_segment_name_cur INTO l_segment_name;
1753 EXIT WHEN get_segment_name_cur%NOTFOUND;
1754
1755 l_query := l_query || l_segment_name;
1756 END LOOP;
1757 l_query := SUBSTR(l_query, 1, LENGTH(l_query) - 11);
1758 CLOSE get_segment_name_cur;
1759
1760 IF (l_query IS NOT NULL) THEN
1761 l_query := 'UPDATE psp_eff_report_details SET gl_sum_criteria_segment_name = ' || l_query
1762 || ' WHERE effort_report_id IN (SELECT per.effort_report_id FROM psp_eff_reports per WHERE per.request_id = '
1763 || TO_CHAR(p_request_id) || ' AND per.person_id BETWEEN '
1764 || TO_CHAR(p_start_person) || ' AND ' || TO_CHAR(p_end_person) || ') AND '
1765 || '(gl_segment1 IS NOT NULL OR gl_segment2 IS NOT NULL OR gl_segment3 IS NOT NULL OR '
1766 || 'gl_segment4 IS NOT NULL OR gl_segment5 IS NOT NULL OR gl_segment6 IS NOT NULL OR '
1767 || 'gl_segment7 IS NOT NULL OR gl_segment8 IS NOT NULL OR gl_segment9 IS NOT NULL OR '
1768 || 'gl_segment10 IS NOT NULL OR gl_segment11 IS NOT NULL OR gl_segment12 IS NOT NULL OR '
1769 || 'gl_segment13 IS NOT NULL OR gl_segment14 IS NOT NULL OR gl_segment15 IS NOT NULL OR '
1770 || 'gl_segment16 IS NOT NULL OR gl_segment17 IS NOT NULL OR gl_segment18 IS NOT NULL OR '
1771 || 'gl_segment19 IS NOT NULL OR gl_segment20 IS NOT NULL OR gl_segment21 IS NOT NULL OR '
1772 || 'gl_segment22 IS NOT NULL OR gl_segment23 IS NOT NULL OR gl_segment24 IS NOT NULL OR '
1773 || 'gl_segment25 IS NOT NULL OR gl_segment26 IS NOT NULL OR gl_segment27 IS NOT NULL OR '
1774 || 'gl_segment28 IS NOT NULL OR gl_segment29 IS NOT NULL OR gl_segment30 IS NOT NULL)';
1775
1776 EXECUTE IMMEDIATE l_query;
1777
1778 -- l_query := REPLACE(l_query, 'segment', 'description');
1779 -- EXECUTE IMMEDIATE l_query;
1780 END IF;
1781 ELSIF (l_layout_type = 'PIV') THEN
1782 FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1783 UPDATE psp_eff_report_details perd
1784 SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1785 WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1786 AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
1787 AND paaf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
1788 (project_name, project_number) = (SELECT name, segment1 FROM pa_projects_all paa
1789 WHERE paa.project_id = perd.project_id),
1790 (award_short_name, award_number) = (SELECT award_short_name, award_number FROM gms_awards_all gaa
1791 WHERE gaa.award_id = perd.award_id),
1792 (task_name, task_number) = (SELECT task_name, task_number FROM pa_tasks pt WHERE pt.task_id = perd.task_id),
1793 exp_org_name = (SELECT name FROM hr_all_organization_units haou
1794 WHERE haou.organization_id = perd.expenditure_organization_id),
1795 (investigator_name, investigator_person_id) = (SELECT full_name, person_id FROM per_all_people_f papf
1796 WHERE papf.person_id = (SELECT person_id FROM gms_personnel gp
1797 WHERE gp.award_id = perd.award_id AND gp.award_role = 'PI'
1798 AND gp.start_date_active = (SELECT MAX(gp2.start_date_active)
1799 FROM gms_personnel gp2
1800 WHERE gp2.award_id = perd.award_id
1801 AND gp2.award_role = 'PI' --- added for uva fix
1802 AND nvl(gp2.end_date_active,to_date('31-12-4712','dd-mm-yyyy')) >= r_effort_report.r_start_date(I) --- uva fix
1803 AND gp2.start_date_active <= r_effort_report.r_end_date(I))
1804 AND ROWNUM = 1)
1805 AND papf.effective_start_date <= r_effort_report.r_end_date(I)
1806 AND papf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
1807 actual_salary_amt = NVL(actual_salary_amt, 0),
1808 payroll_percent = NVL(payroll_percent, 0),
1809 proposed_salary_amt = NVL(proposed_salary_amt, 0),
1810 proposed_effort_percent = NVL(proposed_effort_percent, 0),
1811 committed_cost_share = NVL(committed_cost_share, 0)
1812 WHERE perd.effort_report_id = r_effort_report.r_effort_report_id(I);
1813 ELSIF (l_layout_type = 'PMG') THEN
1814 FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1815 UPDATE psp_eff_report_details perd
1816 SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1817 WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1818 AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
1819 AND paaf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
1820 (project_name, project_number) = (SELECT name, segment1 FROM pa_projects_all paa
1821 WHERE paa.project_id = perd.project_id),
1822 (award_short_name, award_number) = (SELECT award_short_name, award_number FROM gms_awards_all gaa
1823 WHERE gaa.award_id = perd.award_id),
1824 (task_name, task_number) = (SELECT task_name, task_number FROM pa_tasks pt WHERE pt.task_id = perd.task_id),
1825 exp_org_name = (SELECT name FROM hr_all_organization_units haou
1826 WHERE haou.organization_id = perd.expenditure_organization_id),
1827 (investigator_name, investigator_person_id) = (SELECT full_name, person_id FROM per_all_people_f papf
1828 --- added max person_id for uva issue
1829 WHERE papf.person_id = (SELECT max(person_id) FROM pa_project_players pap
1830 -- WHERE pap.project_id = perd.project_id AND project_role_type = 'PROJECT MANAGER'
1831 WHERE pap.project_id = perd.project_id AND project_role_type = l_project_manager_role
1832 AND pap.start_date_active = (SELECT MAX(pap2.start_date_active)
1833 FROM pa_project_players pap2
1834 WHERE pap2.project_id = perd.project_id
1835 AND pap2.project_role_type = l_project_manager_role
1836 AND nvl(pap2.end_date_active,to_date('31-12-4712','dd-mm-yyyy')) >= r_effort_report.r_start_date(I) --- uva fix
1837 AND pap2.start_date_active <= r_effort_report.r_end_date(I)))
1838 AND papf.effective_start_date <= r_effort_report.r_end_date(I)
1839 AND papf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
1840 actual_salary_amt = NVL(actual_salary_amt, 0),
1841 payroll_percent = NVL(payroll_percent, 0),
1842 proposed_salary_amt = NVL(proposed_salary_amt, 0),
1843 proposed_effort_percent = NVL(proposed_effort_percent, 0),
1844 committed_cost_share = NVL(committed_cost_share, 0)
1845 WHERE perd.effort_report_id = r_effort_report.r_effort_report_id(I);
1846 ELSIF (l_layout_type = 'TMG') THEN
1847 FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1848 UPDATE psp_eff_report_details perd
1849 SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1850 WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1851 AND paaf.effective_start_date <= per.end_date
1852 AND paaf.effective_end_date >= per.start_date AND ROWNUM = 1),
1853 (project_name, project_number) = (SELECT name, segment1 FROM pa_projects_all paa
1854 WHERE paa.project_id = perd.project_id),
1855 (award_short_name, award_number) = (SELECT award_short_name, award_number FROM gms_awards_all gaa
1856 WHERE gaa.award_id = perd.award_id),
1857 (task_name, task_number) = (SELECT task_name, task_number FROM pa_tasks pt WHERE pt.task_id = perd.task_id),
1858 exp_org_name = (SELECT name FROM hr_all_organization_units haou
1859 WHERE haou.organization_id = perd.expenditure_organization_id),
1860 (investigator_name, investigator_person_id) = (SELECT full_name, person_id FROM per_all_people_f papf
1861 WHERE papf.person_id = (select task_manager_person_id from pa_tasks pt WHERE pt.task_id = perd.task_id)
1862 AND papf.effective_start_date <= r_effort_report.r_end_date(I)
1863 AND papf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
1864 actual_salary_amt = NVL(actual_salary_amt, 0),
1865 payroll_percent = NVL(payroll_percent, 0),
1866 proposed_salary_amt = NVL(proposed_salary_amt, 0),
1867 proposed_effort_percent = NVL(proposed_effort_percent, 0),
1868 committed_cost_share = NVL(committed_cost_share, 0)
1869 WHERE perd.effort_report_id = r_effort_report.r_effort_report_id(I);
1870 END IF;
1871
1872 FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1873 UPDATE psp_eff_report_details perd
1874 SET (investigator_primary_org_id, investigator_org_name) = (SELECT haou.organization_id, haou.name
1875 FROM hr_all_organization_units haou, per_all_assignments_f paaf
1876 WHERE haou.organization_id = paaf.organization_id AND paaf.person_id = perd.investigator_person_id
1877 AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
1878 AND paaf.effective_end_date >= r_effort_report.r_start_date(I)
1879 AND paaf.primary_flag = 'Y' AND ROWNUM = 1)
1880 WHERE perd.effort_report_id = r_effort_report.r_effort_report_id(I)
1881 AND perd.investigator_person_id IS NOT NULL;
1882
1883 FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1884 UPDATE psp_eff_reports per
1885 SET (employee_number, full_name, mailstop) = (SELECT papf.employee_number, papf.full_name, papf.mailstop FROM per_all_people_f papf
1886 WHERE papf.person_id = per.person_id AND papf.effective_start_date <= per.end_date
1887 AND papf.effective_end_date >= per.start_date AND ROWNUM = 1),
1888 (emp_primary_org_id, emp_primary_org_name) = (SELECT haou.organization_id, haou.name
1889 FROM hr_all_organization_units haou, per_all_assignments_f paaf
1890 WHERE haou.organization_id = paaf.organization_id AND paaf.person_id = per.person_id
1891 AND paaf.effective_start_date <= per.end_date AND paaf.effective_end_date >= per.start_date
1892 AND paaf.primary_flag = 'Y' AND ROWNUM = 1)
1893 WHERE per.effort_report_id = r_effort_report.r_effort_report_id(I);
1894
1895 p_return_status := 'S';
1896 EXCEPTION
1897 WHEN OTHERS THEN
1898 fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'UPDATE_ER_DETAILS', sqlerrm);
1899 OPEN add_report_error_cur(sqlerrm);
1900 FETCH add_report_error_cur INTO l_error_count;
1901 CLOSE add_report_error_cur;
1902
1903 IF (NVL(l_error_count, 0) = 0) THEN
1904 psp_general.add_report_error
1905 (p_request_id => p_request_id,
1906 p_message_level => 'E',
1907 p_source_id => NULL,
1908 p_retry_request_id => p_retry_request_id,
1909 p_pdf_request_id => g_request_id,
1910 p_error_message => sqlerrm,
1911 p_return_status => l_return_status);
1912 END IF;
1913 p_return_status := 'E';
1914 END update_er_details;
1915
1916 PROCEDURE COPY_PTAOE_FROM_GL_SEGMENTS (p_start_person IN NUMBER,
1917 p_end_person IN NUMBER,
1918 p_request_id IN NUMBER,
1919 p_retry_request_id IN NUMBER DEFAULT NULL,
1920 p_business_group_id IN NUMBER,
1921 p_return_status OUT NOCOPY VARCHAR2) IS
1922
1923 CURSOR effort_report_id_cur IS
1924 SELECT effort_report_id
1925 FROM psp_eff_reports per
1926 WHERE person_id BETWEEN p_start_person AND p_end_person
1927 AND status_code <> 'R'
1928 AND request_id = p_request_id;
1929
1930 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
1931 SELECT 1
1932 FROM psp_report_errors
1933 WHERE request_id = p_request_id
1934 AND message_level = 'E'
1935 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
1936 AND pdf_request_id = g_request_id;
1937
1938 l_proj_segment varchar2(30);
1939 l_tsk_segment varchar2(30);
1940 l_awd_sgement varchar2(30);
1941 l_exp_org_segment varchar2(30);
1942 l_exp_type_segment varchar2(30);
1943 sql_stmt varchar2(4000);
1944 l_error_count NUMBER;
1945 l_return_status CHAR(1);
1946
1947 BEGIN
1948
1949 PSP_GENERAL.GET_GL_PTAOE_MAPPING(p_business_group_id => p_business_group_id,
1950 -- p_set_of_books_id => p_set_of_books_id,
1951 p_proj_segment => l_proj_segment,
1952 p_tsk_segment => l_tsk_segment,
1953 p_awd_sgement => l_awd_sgement,
1954 p_exp_org_segment=> l_exp_org_segment,
1955 p_exp_type_segment => l_exp_type_segment);
1956
1957 sql_stmt := ' Update psp_eff_report_details set project_id = GL_'||l_proj_segment ||
1958 ' , TASK_ID = GL_'|| l_tsk_segment || ' , AWARD_ID = GL_' || l_awd_sgement || ' , EXPENDITURE_ORGANIZATION_ID = GL_' || l_exp_org_segment
1959 || ' , EXPENDITURE_TYPE = GL_'|| l_exp_type_segment ||' WHERE effort_report_id in (select effort_report_id FROM psp_eff_reports per'
1960 || ' WHERE person_id BETWEEN ' || p_start_person || ' AND ' || p_end_person
1961 || ' AND status_code <> ''R'' AND request_id = ' || p_request_id ||' )';
1962
1963 EXECUTE IMMEDIATE sql_stmt ;
1964 p_return_status := fnd_api.g_ret_sts_success;
1965 EXCEPTION
1966 WHEN OTHERS THEN
1967 fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'COPY_PTAOE_FROM_GL_SEGMENTS', sqlerrm);
1968 OPEN add_report_error_cur(sqlerrm);
1969 FETCH add_report_error_cur INTO l_error_count;
1970 CLOSE add_report_error_cur;
1971
1972 IF (NVL(l_error_count, 0) = 0) THEN
1973 psp_general.add_report_error
1974 (p_request_id => p_request_id,
1975 p_message_level => 'E',
1976 p_source_id => NULL,
1977 p_retry_request_id => p_retry_request_id,
1978 p_pdf_request_id => g_request_id,
1979 p_error_message => sqlerrm,
1980 p_return_status => l_return_status);
1981 END IF;
1982 p_return_status := fnd_api.g_ret_sts_error;
1983 END COPY_PTAOE_FROM_GL_SEGMENTS;
1984 --- function for uva issues.. this can be replace dbms_xmlgen.convert in future.
1985 ---- fix for bug 4429787
1986 function convert_xml_controls(p_string varchar2) return varchar2 is
1987 begin
1988 return replace(replace(replace(replace(replace(p_string, '&', '&'),'''','''),'"','"'),'<','<'),'>','>');
1989 end;
1990
1991 PROCEDURE update_er_error_details (p_request_id IN NUMBER,
1992 p_retry_request_id IN NUMBER,
1993 p_return_status OUT NOCOPY VARCHAR2) IS
1994
1995 CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
1996 SELECT 1
1997 FROM psp_report_errors
1998 WHERE request_id = p_request_id
1999 AND message_level = 'E'
2000 AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
2001 AND pdf_request_id = g_request_id;
2002
2003 CURSOR er_dates_cur IS
2004 SELECT fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_2)) start_date,
2005 fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_3)) end_date
2006 FROM psp_report_templates_h prth
2007 WHERE prth.request_id = p_request_id;
2008
2009 l_start_date DATE;
2010 l_end_date DATE;
2011 l_return_status CHAR(1);
2012 l_error_count NUMBER;
2013
2014 BEGIN
2015 OPEN er_dates_cur;
2016 FETCH er_dates_cur INTO l_start_date, l_end_date;
2017 CLOSE er_dates_cur;
2018
2019 UPDATE psp_report_errors pre
2020 SET (source_name, parent_source_id, parent_source_name) = (SELECT full_name, emp_primary_org_id, emp_primary_org_name
2021 FROM psp_eff_reports per
2022 WHERE per.request_id = p_request_id
2023 AND per.person_id = TO_NUMBER(pre.source_id))
2024 WHERE pre.request_id = p_request_id
2025 AND pre.source_id IS NOT NULL
2026 AND pre.source_name IS NULL;
2027
2028 UPDATE psp_report_errors pre
2029 SET (source_name, parent_source_id, parent_source_name) = (SELECT papf.full_name, paaf.organization_id, haou.name
2030 FROM per_all_assignments_f paaf,
2031 per_all_people_f papf,
2032 hr_all_organization_units haou
2033 WHERE haou.organization_id = paaf.organization_id
2034 AND papf.person_id = TO_NUMBER(pre.source_id)
2035 AND paaf.person_id = TO_NUMBER(pre.source_id)
2036 AND paaf.primary_flag = 'Y'
2037 AND paaf.effective_start_date = (SELECT MAX(paaf2.effective_start_date)
2038 FROM per_all_assignments_f paaf2
2039 WHERE paaf2.effective_start_date <= l_end_date
2040 AND paaf2.effective_end_date >= l_start_date
2041 AND paaf2.person_id = TO_NUMBER(pre.source_id))
2042 AND papf.effective_start_date = (SELECT MAX(papf2.effective_start_date)
2043 FROM per_all_people_f papf2
2044 WHERE papf2.effective_start_date <= l_end_date
2045 AND papf2.effective_end_date >= l_start_date
2046 AND papf2.person_id = TO_NUMBER(pre.source_id)))
2047 WHERE pre.request_id = p_request_id
2048 AND pre.source_id IS NOT NULL
2049 AND pre.source_name IS NULL;
2050
2051 UPDATE psp_report_errors pre
2052 SET source_name = (SELECT papf.full_name
2053 FROM per_all_people_f papf
2054 WHERE papf.person_id = TO_NUMBER(pre.source_id)
2055 AND papf.effective_start_date = (SELECT MAX(papf2.effective_start_date)
2056 FROM per_all_people_f papf2
2057 WHERE papf2.effective_start_date <= l_end_date
2058 AND papf2.effective_end_date >= l_start_date
2059 AND papf2.person_id = TO_NUMBER(pre.source_id)))
2060 WHERE pre.request_id = p_request_id
2061 AND pre.source_id IS NOT NULL
2062 AND pre.source_name IS NULL;
2063
2064 UPDATE psp_report_errors pre
2065 SET source_name = (SELECT papf.full_name
2066 FROM per_all_people_f papf
2067 WHERE papf.person_id = TO_NUMBER(pre.source_id)
2068 AND papf.effective_start_date = (SELECT MIN(papf2.effective_start_date)
2069 FROM per_all_people_f papf2
2070 WHERE papf2.person_id = TO_NUMBER(pre.source_id)))
2071 WHERE pre.request_id = p_request_id
2072 AND pre.source_id IS NOT NULL
2073 AND pre.source_name IS NULL;
2074
2075 p_return_status := fnd_api.g_ret_sts_success;
2076 EXCEPTION
2077 WHEN OTHERS THEN
2078 fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'UPDATE_ER_ERROR_DETAILS', sqlerrm);
2079 OPEN add_report_error_cur(sqlerrm);
2080 FETCH add_report_error_cur INTO l_error_count;
2081 CLOSE add_report_error_cur;
2082
2083 IF (NVL(l_error_count, 0) = 0) THEN
2084 psp_general.add_report_error
2085 (p_request_id => p_request_id,
2086 p_message_level => 'E',
2087 p_source_id => NULL,
2088 p_retry_request_id => p_retry_request_id,
2089 p_pdf_request_id => NULL,
2090 p_error_message => sqlerrm,
2091 p_return_status => l_return_status);
2092 END IF;
2093 p_return_status := fnd_api.g_ret_sts_error;
2094 END update_er_error_details;
2095
2096
2097 /* Procedure Added for Hospital effort report */
2098
2099 PROCEDURE update_grouping_category ( p_start_person IN NUMBER,
2100 p_end_person IN NUMBER,
2101 p_request_id IN NUMBER,
2102 p_return_status OUT NOCOPY VARCHAR2) IS
2103
2104 TYPE t_num_15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
2105 effort_report_detail_id_rec t_num_15_type;
2106 l_return_status CHAR(1);
2107
2108 CURSOR GLA_effort_report_detail_cur IS
2109 SELECT perd.effort_report_detail_id
2110 FROM psp_eff_reports per,
2111 psp_eff_report_details perd
2112 WHERE per.effort_report_id = perd.effort_report_id
2113 AND per.person_id BETWEEN p_start_person AND p_end_person
2114 AND request_id = p_request_id
2115 AND (perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL
2116 OR perd.gl_segment3 IS NOT NULL OR perd.gl_segment4 IS NOT NULL
2117 OR perd.gl_segment5 IS NOT NULL OR perd.gl_segment6 IS NOT NULL
2118 OR perd.gl_segment7 IS NOT NULL OR perd.gl_segment8 IS NOT NULL
2119 OR perd.gl_segment9 IS NOT NULL OR perd.gl_segment10 IS NOT NULL
2120 OR perd.gl_segment11 IS NOT NULL OR perd.gl_segment12 IS NOT NULL
2121 OR perd.gl_segment13 IS NOT NULL OR perd.gl_segment14 IS NOT NULL
2122 OR perd.gl_segment15 IS NOT NULL OR perd.gl_segment16 IS NOT NULL
2123 OR perd.gl_segment17 IS NOT NULL OR perd.gl_segment18 IS NOT NULL
2124 OR perd.gl_segment19 IS NOT NULL OR perd.gl_segment20 IS NOT NULL
2125 OR perd.gl_segment21 IS NOT NULL OR perd.gl_segment22 IS NOT NULL
2126 OR perd.gl_segment23 IS NOT NULL OR perd.gl_segment24 IS NOT NULL
2127 OR perd.gl_segment25 IS NOT NULL OR perd.gl_segment26 IS NOT NULL
2128 OR perd.gl_segment27 IS NOT NULL OR perd.gl_segment28 IS NOT NULL
2129 OR perd.gl_segment29 IS NOT NULL OR perd.gl_segment30 IS NOT NULL);
2130
2131 CURSOR SPO_effort_report_detail_cur IS
2132 SELECT perd.effort_report_detail_id
2133 FROM psp_eff_reports per,
2134 psp_eff_report_details perd
2135 WHERE per.effort_report_id = perd.effort_report_id
2136 AND per.person_id BETWEEN p_start_person AND p_end_person
2137 AND request_id = p_request_id
2138 AND ( perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL
2139 OR perd.award_id IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL
2140 OR perd.expenditure_type IS NOT NULL)
2141 AND EXISTS (SELECT 1 FROM pa_projects_all ppa, gms_project_types gpta -- Changed from gms_project_types_all for bug 5503605
2142 WHERE gpta.project_type = ppa.project_type
2143 AND ppa.project_id = perd.project_id
2144 AND ppa.project_type <> 'AWARD_PROJECT' AND NVL(gpta.sponsored_flag, 'N') ='Y');
2145
2146 CURSOR NSP_effort_report_detail_cur IS
2147 SELECT perd.effort_report_detail_id
2148 FROM psp_eff_reports per,
2149 psp_eff_report_details perd
2150 WHERE per.effort_report_id = perd.effort_report_id
2151 AND per.person_id BETWEEN p_start_person AND p_end_person
2152 AND request_id = p_request_id
2153 AND ( perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL
2154 OR perd.award_id IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL
2155 OR perd.expenditure_type IS NOT NULL)
2156 AND EXISTS (SELECT 1 FROM pa_projects_all ppa, gms_project_types gpta -- Changed from gms_project_types_all for bug 5503605
2157 WHERE gpta.project_type(+) = ppa.project_type
2158 AND ppa.project_id = perd.project_id
2159 AND ppa.project_type <> 'AWARD_PROJECT' AND NVL(gpta.sponsored_flag, 'N') ='N');
2160
2161
2162 BEGIN
2163 OPEN GLA_effort_report_detail_cur;
2164 FETCH GLA_effort_report_detail_cur BULK COLLECT INTO effort_report_detail_id_rec;
2165 CLOSE GLA_effort_report_detail_cur;
2166
2167 FORALL I IN 1..effort_report_detail_id_rec.COUNT
2168 UPDATE psp_eff_report_details perd SET grouping_category = 'GLA'
2169 WHERE effort_report_detail_id = effort_report_detail_id_rec(I);
2170
2171 effort_report_detail_id_rec.delete;
2172
2173 OPEN SPO_effort_report_detail_cur;
2174 FETCH SPO_effort_report_detail_cur BULK COLLECT INTO effort_report_detail_id_rec;
2175 CLOSE SPO_effort_report_detail_cur;
2176
2177 FORALL I IN 1..effort_report_detail_id_rec.COUNT
2178 UPDATE psp_eff_report_details perd SET grouping_category = 'SPO'
2179 WHERE effort_report_detail_id = effort_report_detail_id_rec(I);
2180
2181 effort_report_detail_id_rec.delete;
2182
2183
2184 OPEN NSP_effort_report_detail_cur;
2185 FETCH NSP_effort_report_detail_cur BULK COLLECT INTO effort_report_detail_id_rec;
2186 CLOSE NSP_effort_report_detail_cur;
2187
2188 FORALL I IN 1..effort_report_detail_id_rec.COUNT
2189 UPDATE psp_eff_report_details perd SET grouping_category = 'NSP'
2190 WHERE effort_report_detail_id = effort_report_detail_id_rec(I);
2191
2192 effort_report_detail_id_rec.delete;
2193
2194 p_return_status := fnd_api.g_ret_sts_success;
2195
2196 EXCEPTION
2197 WHEN OTHERS THEN
2198 psp_general.add_report_error
2199 (p_request_id => p_request_id,
2200 p_message_level => 'E',
2201 p_source_id => NULL,
2202 p_error_message => sqlerrm,
2203 p_return_status => l_return_status);
2204
2205 p_return_status := fnd_api.g_ret_sts_unexp_error;
2206
2207 END update_grouping_category;
2208
2209
2210 END PSP_XMLGEN;