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