APPS.PSP_XMLGEN SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 16

SELECT	set_of_books_id
FROM	psp_report_templates_h
WHERE	request_id = p_request_id;

Line: 21

SELECT	1
FROM	psp_report_errors
WHERE	request_id = p_request_id
AND	message_level = 'E'
AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
AND	pdf_request_id = g_request_id;

Line: 29

SELECT	user_name
FROM	fnd_user fu,
	fnd_concurrent_requests fcr
WHERE	fu.user_id = fcr.requested_by
AND	fcr.request_id = p_request_id;

Line: 52

	query1 := 'select xtt.template_name report_layout, prth.template_name, '
		|| 'TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_2)), ''' || l_icx_date_format
		|| ''') start_date, TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_3)), '''
		|| l_icx_date_format || ''') end_date, SUBSTR(prth.report_template_code, 6, 3) layout_type, flv1.meaning sort_option1, '
		|| 'flv2.meaning order_by1, flv3.meaning sort_option2, flv4.meaning order_by2, '
		|| 'flv5.meaning sort_option3, flv6.meaning order_by3, flv7.meaning sort_option4, flv8.meaning order_by4, '
		|| 'DECODE(prth.initiator_person_id, -1, ''' || l_user_name || ''', '
		|| 'psp_general.get_person_name(prth.initiator_person_id, TRUNC(SYSDATE))) initiated_by, '
		|| 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') run_date '
		|| 'FROM psp_report_templates_h prth, xdo_templates_tl xtt,  '
        	|| '(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, ' || TO_CHAR(l_gl_sob) || ')) flv1, '
		|| '(select * from fnd_lookup_values where language = ''' || l_language_code || ''' AND lookup_type = ''PSP_ORDERING_CRITERIA'') flv2, '
		|| '(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, ' || TO_CHAR(l_gl_sob) || ')) flv3, '
		|| '(select * from fnd_lookup_values where language = ''' || l_language_code || ''' AND lookup_type = ''PSP_ORDERING_CRITERIA'') flv4, '
        	|| '(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, ' || TO_CHAR(l_gl_sob) || ')) flv5, '
		|| '(select * from fnd_lookup_values where language = ''' || l_language_code || ''' AND lookup_type = ''PSP_ORDERING_CRITERIA'') flv6, '
		|| '(select * from psp_layout_lookup_code_v where set_of_books_id in (-1, ' || TO_CHAR(l_gl_sob) || ')) flv7, '
		|| '(select * from fnd_lookup_values where language = ''' || l_language_code || ''' AND lookup_type = ''PSP_ORDERING_CRITERIA'') flv8 WHERE '
		|| 'prth.request_id = ' || TO_CHAR(p_request_id)
		|| 'AND flv1.lookup_code = prth.parameter_value_5 '
		|| 'AND flv2.lookup_code = prth.parameter_value_6 '
		|| 'AND flv3.lookup_code = prth.parameter_value_7 '
		|| 'AND flv4.lookup_code = prth.parameter_value_8 '
		|| 'AND flv5.lookup_code (+) = prth.parameter_value_9 '
		|| 'AND flv6.lookup_code (+) = prth.parameter_value_10 '
		|| 'AND flv7.lookup_code (+) = prth.parameter_value_11 '
		|| 'AND flv8.lookup_code (+) = prth.parameter_value_12 '
		|| 'AND xtt.language = ''' || l_language_code || ''' '
		|| 'AND xtt.template_code = prth.report_template_code AND xtt.application_short_name = ''PSP''';

Line: 147

SELECT	DISTINCT prtdh.criteria_value1,
	plo.value1 || ' ' || DECODE(prtdh.criteria_value2, 'A', 'ASC', 'DESC') sort_option,
	' ''' || flv1.meaning || ''' ' sort_option_description,
	' ''' || flv2.meaning || ''' ' order_by_description
FROM	psp_report_template_details_h prtdh,
	psp_report_templates_h prth,
        fnd_lookup_values flv1,
        fnd_lookup_values flv2,
        psp_layout_options plo
WHERE	prtdh.request_id = p_request_id
AND	prth.request_id = p_request_id
AND	plo.report_template_code = prth.report_template_code
AND     flv1.lookup_code = prtdh.criteria_lookup_code
AND	flv1.lookup_type = 'PSP_SORTING_CRITERIA'
AND	prtdh.criteria_lookup_type = 'PSP_SORTING_CRITERIA'
AND	flv2.lookup_type = 'PSP_ORDERING_CRITERIA'
AND     flv2.lookup_code = prtdh.criteria_value2
ANd     prtdh.criteria_lookup_code = plo.layout_lookup_code
AND	plo.value1 LIKE 'per.%'
ORDER BY prtdh.criteria_value1;

Line: 169

SELECT	DISTINCT prtdh.criteria_value1,
	plo.value1 || ' ' || DECODE(prtdh.criteria_value2, 'A', 'ASC', 'DESC') sort_option,
	' ''' || flv1.meaning || ''' ' sort_option_description,
	' ''' || flv2.meaning || ''' ' order_by_description
FROM	psp_report_template_details_h prtdh,
	psp_report_templates_h prth,
        fnd_lookup_values flv1,
        fnd_lookup_values flv2,
        psp_layout_options plo
WHERE	prth.request_id = p_request_id
AND	prtdh.request_id = p_request_id
AND	plo.report_template_code = prth.report_template_code
AND     flv1.lookup_code = prtdh.criteria_lookup_code
AND	flv1.language = 'US'
AND	flv1.lookup_type = 'PSP_SORTING_CRITERIA'
AND	prtdh.criteria_lookup_type = 'PSP_SORTING_CRITERIA'
AND	plo.layout_lookup_type = 'PSP_SORTING_CRITERIA'
AND	flv2.language = 'US'
AND	flv2.lookup_type = 'PSP_ORDERING_CRITERIA'
AND     flv2.lookup_code = prtdh.criteria_value2
AND     prtdh.criteria_lookup_code = plo.layout_lookup_code --'PRINVESG'
--AND	plo.value1 LIKE 'perd.%'
AND	plo.layout_lookup_code IN ('PRINVESG', 'PIORG', 'PRJMGR', 'PMORG', 'TASKMGR', 'TMORG')
ORDER BY prtdh.criteria_value1;

Line: 195

SELECT	SUBSTR(report_template_code, 6, 3) layout_type,
	display_all_emp_distrib_flag display_flag
FROM	psp_report_templates_h prt
WHERE	request_id = l_request_id;

Line: 201

SELECT	value
FROM	nls_session_parameters
WHERE	parameter = 'NLS_DATE_FORMAT';

Line: 206

SELECT	DISTINCT pera.wf_role_display_name,
	haou.name
FROM	hr_all_organization_units haou,
	psp_eff_report_approvals pera,
	per_all_assignments_f paaf,
	fnd_user fu
WHERE	pera.wf_item_key = p_wf_item_key
AND	fu.employee_id = paaf.person_id
AND	fu.user_name = pera.wf_role_name
AND	paaf.primary_flag = 'Y'
AND	haou.organization_id = paaf.organization_id
AND	TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date;

Line: 220

SELECT	per.template_id,
	per.request_id,
	per.set_of_books_id
FROM	psp_eff_report_details perd,
	psp_eff_report_approvals pera,
	psp_eff_reports per
WHERE	perd.effort_report_detail_id = pera.effort_report_detail_id
AND	perd.effort_report_id = per.effort_report_id
AND	pera.wf_item_key = p_wf_item_key
AND	ROWNUM = 1
UNION ALL
SELECT	per2.template_id,
	p_request_id,
	per2.set_of_books_id
FROM	psp_eff_reports per2
WHERE	per2.request_id = p_request_id
AND	ROWNUM = 1;

Line: 239

SELECT	fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gsob.chart_of_accounts_id)
FROM	gl_sets_of_books gsob
WHERE	gsob.set_of_books_id = l_set_of_books_id;

Line: 244

SELECT	fifs.segment_name || l_segment_delimiter segment_header
FROM	fnd_id_flex_segments fifs,
	gl_sets_of_books gsob,
	fnd_application fa
WHERE	gsob.set_of_books_id = l_set_of_books_id
AND	fifs.id_flex_num = gsob.chart_of_accounts_id
AND	fifs.id_flex_code = 'GL#'
AND	fifs.application_id = fa.application_id
AND	fa.application_short_name = 'SQLGL'
AND	EXISTS	(SELECT	1
		FROM	psp_report_template_details_h prtdh
		WHERE	prtdh.REQUEST_ID= p_request_id
		AND	prtdh.criteria_lookup_type = 'PSP_SUMMARIZATION_CRITERIA'
		AND	prtdh.criteria_lookup_code = fifs.application_column_name)
ORDER BY fifs.segment_num;

Line: 267

SELECT	1
FROM	psp_report_errors
WHERE	request_id = p_request_id
AND	message_level = 'E'
AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
AND	pdf_request_id = g_request_id;

Line: 275

SELECT	fnd_currency.get_format_mask(currency_code, p_length)
FROM	psp_eff_reports
WHERE	request_id = l_request_id;

Line: 280

SELECT 'Y'
FROM  psp_eff_report_details perd,
psp_eff_reports per ,
psp_eff_report_details perd2
WHERE per.effort_report_id = perd.effort_report_id
AND per.status_code IN ('N', 'A')
AND per.request_id = NVL(p_request_id,per.request_id)
AND perd.investigator_person_id  =p_investigator_person_id
AND per.effort_report_id = perd2.effort_report_id
AND perd.investigator_person_id <>  perd2.investigator_person_id;

Line: 350

	query1 := 'SELECT TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_2)), ''' || l_icx_date_format
		|| ''') start_date, TO_CHAR(fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_3)), '''
		|| l_icx_date_format || ''') end_date, ''' || l_display_flag || ''' display_flag, ''' || l_gl_header || ''' gl_header, '
		|| 'TO_CHAR(SYSDATE, ''' || l_icx_date_format || ''') run_date '
		|| 'FROM psp_report_templates_h prth WHERE prth.request_id = ' || TO_CHAR(l_request_id);

Line: 371

			query1 := 'SELECT person_xml FROM psp_eff_reports per WHERE per.status_code IN (''A'', ''N'') '
				|| 'AND EXISTS (SELECT 1 FROM psp_eff_report_details perd, '
				|| 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id AND '
				|| 'perd.effort_report_id = per.effort_report_id AND pera.wf_item_key = ' || TO_CHAR(p_wf_item_key)
				|| ' AND pera.approval_status <> ''R'')' || l_sort_option1;

Line: 377

			query1 := 'SELECT person_xml FROM psp_eff_reports per WHERE status_code IN (''A'', ''N'') AND '
				|| 'request_id = ' || TO_CHAR(l_request_id) || l_sort_option1;

Line: 416

			query1 := 'SELECT investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id, '
				|| 'TRIM(TO_CHAR(SUM(proposed_salary_amt), ''' || l_num30_fmask || ''')) total_pi_proposed_salary, '
				|| 'TRIM(TO_CHAR(SUM(actual_salary_amt), ''' || l_num30_fmask || ''')) total_pi_actual_salary FROM '
				|| 'psp_eff_report_details perd, psp_eff_report_approvals pera WHERE pera.effort_report_detail_id = '
				|| 'perd.effort_report_detail_id AND pera.wf_item_key = ' || TO_CHAR(p_wf_item_key)
				|| ' AND EXISTS (SELECT 1 FROM psp_eff_reports per WHERE per.effort_report_id = perd.effort_report_id '
				|| 'AND per.status_code IN (''A'', ''N'')) AND perd.investigator_person_id IS NOT NULL'
				|| ' GROUP BY investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id'
				|| l_sort_option1;

Line: 426

			query1 := 'SELECT investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id, '
				|| 'TRIM(TO_CHAR(SUM(proposed_salary_amt), ''' || l_num30_fmask || ''')) total_pi_proposed_salary, '
				|| 'TRIM(TO_CHAR(SUM(actual_salary_amt), ''' || l_num30_fmask || ''')) total_pi_actual_salary FROM '
				|| 'psp_eff_report_details perd, psp_eff_reports per WHERE per.effort_report_id = '
				|| 'perd.effort_report_id AND per.request_id = ' || TO_CHAR(p_request_id)
				|| ' AND per.status_code IN (''A'', ''N'') AND perd.investigator_person_id IS NOT NULL'
				|| ' GROUP BY investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id'
				|| l_sort_option1;

Line: 474

			query2 := 'SELECT person_xml FROM psp_eff_reports per WHERE per.effort_report_id IN (SELECT perd.effort_report_id '
				|| 'FROM psp_eff_report_details perd WHERE perd.investigator_person_id = ' || TO_CHAR(l_investigator_id) || ')'
				||  '  AND request_id = ' || TO_CHAR(l_request_id) || ' AND status_code <> ''R''' || l_emp_sort_option;

Line: 491

/*			query2 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) '
				|| 'approver_name, TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
				|| ' approval_date FROM psp_eff_report_approvals pera, psp_eff_reports per'
				|| ',psp_eff_report_details perd WHERE perd.effort_report_detail_id=pera.effort_report_detail_id AND '
				|| 'per.effort_report_id = perd.effort_report_id AND per.request_id = ' || TO_CHAR(l_request_id)
				|| ' AND perd.investigator_person_id = ' || TO_CHAR(l_investigator_id)
				|| ' ORDER BY approver_order_num DESC';

Line: 500

			query2 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) '
				|| 'approver_name, TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
				|| ' approval_date, wfna.TEXT_VALUE note FROM psp_eff_report_approvals pera, psp_eff_reports per'
				|| ',psp_eff_report_details perd , WF_NOTIFICATION_ATTRIBUTES wfna WHERE perd.effort_report_detail_id=pera.effort_report_detail_id AND '
				|| 'per.effort_report_id = perd.effort_report_id AND per.request_id = ' || TO_CHAR(l_request_id)
				|| ' AND perd.investigator_person_id = ' || TO_CHAR(l_investigator_id)
		                ||' AND wfna.NAME(+) =''WF_NOTE'' AND wfna.NOTIFICATION_ID(+) = pera.NOTIFICATION_ID '
				|| ' ORDER BY approver_order_num DESC';

Line: 578

SELECT	SUBSTR(report_template_code, 6, 3) layout_type
FROM	psp_report_templates_h prt
WHERE	request_id = p_request_id;

Line: 584

SELECT	DISTINCT prtd.criteria_value1,
	plo.value1 || ' ' || DECODE(prtd.criteria_value2, 'A', 'ASC', 'DESC') sort_option
FROM	psp_report_template_details prtd,
        fnd_lookup_values flv1,
        fnd_lookup_values flv2,
        psp_layout_options plo
WHERE	prtd.template_id = p_template_id
AND     flv1.lookup_code = prtd.criteria_lookup_code
AND		flv1.lookup_type = 'PSP_SORTING_CRITERIA'
AND		prtd.criteria_lookup_type = 'PSP_SORTING_CRITERIA'
AND		flv2.lookup_type = 'PSP_ORDERING_CRITERIA'
AND     flv2.lookup_code = prtd.criteria_value2
ANd     prtd.criteria_lookup_code = plo.layout_lookup_code
AND		plo.value1 LIKE 'perd.%'
ORDER BY prtd.criteria_value1;

Line: 602

select  prtdh.criteria_value1,
decode (substr(prtdh.CRITERIA_LOOKUP_CODE,1,7),'SEGMENT','GL_'||prtdh.CRITERIA_LOOKUP_CODE,plo.VALUE1)
|| ' ' || DECODE(criteria_value2, 'A', 'ASC', 'DESC')   --decode plo.VALUE1
from psp_report_template_details_h prtdh ,
psp_report_templates_h prth
,psp_layout_options plo
where prth.request_id= p_request_id-- 125338 --125188
and prth.request_id = prtdh.request_id
and prtdh.CRITERIA_LOOKUP_TYPE ='PSP_SORTING_CRITERIA'
and prtdh.CRITERIA_LOOKUP_TYPE  = plo.LAYOUT_LOOKUP_TYPE
and prth.REPORT_TEMPLATE_CODE = plo.REPORT_TEMPLATE_CODE
and plo.PTAOE_STORED_IN_GL_FLAG = PSP_GENERAL.GET_CONFIGURATION_OPTION_VALUE(p_business_group_id,'PSP_USE_GL_PTAOE_MAPPING')
and (plo.LAYOUT_LOOKUP_CODE = prtdh.CRITERIA_LOOKUP_CODE
    OR plo.LAYOUT_LOOKUP_CODE = 'GL' and prtdh.CRITERIA_LOOKUP_CODE like 'SEGMENT%')
AND plo.value1 LIKE 'perd.%'
ORDER BY prtdh.criteria_value1;

Line: 621

SELECT	DISTINCT assignment_id,
		assignment_number
FROM	psp_eff_report_details
WHERE	effort_report_id = p_effort_report_id
ORDER BY assignment_number ASC;			-- Bug 4247734

Line: 628

SELECT	1
FROM	psp_report_errors
WHERE	request_id = p_request_id
AND	message_level = 'E'
AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
AND	pdf_request_id = g_request_id;

Line: 637

SELECT distinct perd.grouping_category, plo.layout_option_order
FROM   psp_eff_report_details perd,
psp_layout_options plo
WHERE  effort_report_id = p_effort_report_id
AND    NVL(assignment_id,-1) = NVL(p_assignment_id,-1)
AND    plo.layout_lookup_type ='PSP_EFFORT_CATEGORY'
AND    plo.LAYOUT_LOOKUP_CODE = perd.grouping_category
ORDER BY plo.layout_option_order;

Line: 648

SELECT	ppf.full_name
FROM	per_people_f ppf,
        fnd_user fu,
	fnd_concurrent_requests fcr
WHERE	fu.user_id = fcr.requested_by
AND     fu.employee_id = ppf.person_id
AND	fcr.request_id = p_request_id;

Line: 701

	l_er_check := ' AND NOT EXISTS (SELECT 1 FROM psp_eff_report_details perd1, psp_eff_report_approvals pera1 '
		|| 'WHERE perd.effort_report_id = perd1.effort_report_id AND perd1.effort_report_detail_id = pera1.effort_report_detail_id '
		|| 'AND pera1.approval_status = ''R'') '
		|| 'AND NVL(pera.approver_order_num, 1) = (SELECT NVL(MAX(pera1.approver_order_num), 1) FROM psp_eff_report_approvals pera1 '
		|| 'WHERE pera1.effort_report_detail_id = perd.effort_report_detail_id)';

Line: 718

	query1 := 'select distinct pera.eff_information1, pera.eff_information2, pera.eff_information3,pera.eff_information4, pera.eff_information5, '
                || ' pera.eff_information6,pera.eff_information7, pera.eff_information8 , pera.eff_information9,pera.eff_information10, '
                || ' pera.eff_information11, pera.eff_information12, pera.eff_information13, pera.eff_information14, pera.eff_information15 '
                || ' FROM   psp_eff_report_details perd, '
                || ' psp_eff_report_approvals pera '
                || ' WHERE  perd.effort_report_detail_id = pera.effort_report_detail_id '
                || ' AND    perd.effort_report_id = ' || p_effort_report_id
                || ' AND    APPROVER_ORDER_NUM = (SELECT max(APPROVER_ORDER_NUM) '
                || '                             FROM   psp_eff_report_approvals pera2, '
                || '                             psp_eff_report_details perd2 '
                || '                             WHERE  pera2.effort_report_detail_id = perd2.effort_report_detail_id '
                || '                             AND    perd2.effort_report_id = perd.effort_report_id) ';

Line: 743

	query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_emp_proposed_salary, '
		|| 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_emp_proposed_effort, '
		|| 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_emp_cost_share, '
		|| 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_emp_actual_salary, '
		|| 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_emp_actual_effort, '
		|| 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_emp_overwritten_effort, '
		|| 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_emp_actual_cost_share '
		|| 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '
		|| 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
		|| TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN ( ''P'', ''A'')' || l_er_check;

Line: 777

		query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_asg_proposed_salary, '
			|| 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_asg_proposed_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_asg_cost_share, '
			|| 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_asg_actual_salary, '
			|| 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_asg_actual_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_asg_overwritten_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_asg_actual_cost_share '
			|| 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '
			|| 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
			|| TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;

Line: 805

			query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_proposed_salary, '
				|| 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_proposed_effort, '
				|| 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_cost_share, '
				|| 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_actual_salary, '
				|| 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_actual_effort, '
				|| 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_overwritten_effort, '
				|| 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_actual_cost_share, '
				|| 'TRIM(MAX(lookup.lookup_code)) category_code, '
				|| 'TRIM(MAX(lookup.meaning)) category_desc '
				|| 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera, '
				|| 'psp_layout_options plo,  fnd_lookup_values_vl lookup, psp_report_templates_h prth '
				|| 'WHERE perd.grouping_category = plo.layout_lookup_code '
				|| 'AND plo.layout_lookup_code = lookup.lookup_code '
				|| 'AND plo.layout_lookup_type =''PSP_EFFORT_CATEGORY'' '
				|| 'AND lookup.lookup_type = ''PSP_EFFORT_CATEGORY'' '
				|| 'AND lookup.enabled_flag = ''Y'' '
				|| 'AND sysdate between NVL(lookup.start_date_active,to_date(''01/01/1951'',''DD/MM/RRRR'')) '
				|| 'AND NVL(lookup.end_date_active,to_date(''31/12/4712'',''DD/MM/RRRR'')) '
                                || 'AND perd.grouping_category = ''' || l_grouping_category || ''' '
				|| 'AND prth.report_template_code = plo.report_template_code '
				|| 'AND prth.request_id = ' || p_request_id
				|| ' AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
				|| TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;

Line: 839

		query1 := 'SELECT TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '
			|| 'TRIM(TO_CHAR(perd.PAYROLL_PERCENT, ''999G990D00'')) payroll_percent, '
			|| 'TRIM(TO_CHAR(perd.PROPOSED_SALARY_AMT, ''' || l_num25_fmask || ''')) proposed_salary_amt, '
			|| 'TRIM(TO_CHAR(perd.PROPOSED_EFFORT_PERCENT, ''999G990D00'')) proposed_effort_percent, '
			|| 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
			|| 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
			|| 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
			|| 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
			|| 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share, '
			|| 'pera.pera_information1 pera_information1, pera.pera_information2, pera.pera_information3, pera.pera_information4, pera.pera_information5, '
			|| 'pera.pera_information6, pera.pera_information7, pera.pera_information8, pera.pera_information9, pera.pera_information10, '
			|| 'pera.pera_information11, pera.pera_information12, pera.pera_information13, pera.pera_information14, pera.pera_information15, '
			|| 'pera.pera_information16, pera.pera_information17, pera.pera_information8, pera.pera_information19, pera.pera_information20 '
			|| 'FROM psp_eff_report_details perd, '
			|| 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
			|| TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
                        || 'AND perd.grouping_category = ''' || l_grouping_category || ''' '
                        || l_assignment_check || l_sort_option1;

Line: 876

		query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_spon_proposed_salary, '
			|| 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_spon_proposed_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_spon_cost_share, '
			|| 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_spon_actual_salary, '
			|| 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_spon_actual_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_spon_overwritten_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_spon_actual_cost_share '
			|| 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '
			|| 'WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
			|| 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
			|| '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type = ppa.project_type AND '
			|| 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
			|| '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
			|| TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;

Line: 898

		query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_nspon_proposed_salary, '
			|| 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_nspon_proposed_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_nspon_cost_share, '
			|| 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_nspon_actual_salary, '
			|| 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_nspon_actual_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_nspon_overwritten_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_nspon_actual_cost_share '
			|| 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '
			|| 'WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
			|| 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
			|| '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type (+) = ppa.project_type AND '
			|| 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
			|| '''N'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
			|| TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;

Line: 920

		query1 := 'select TRIM(TO_CHAR(SUM(NVL(proposed_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_gl_proposed_salary, '
			|| 'TRIM(TO_CHAR(SUM(NVL(proposed_effort_percent, 0)), ''999G990D00'')) total_gl_proposed_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(committed_cost_share, 0)), ''999G990D00'')) total_gl_cost_share, '
			|| 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_gl_actual_salary, '
			|| 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_gl_actual_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_gl_overwritten_effort, '
			|| 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_gl_actual_cost_share '
			|| 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '
			|| 'WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND '
			|| '(perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL OR perd.gl_segment3 IS NOT NULL OR perd.gl_segment4 '
			|| '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 '
			|| 'perd.gl_segment8 IS NOT NULL OR perd.gl_segment9 IS NOT NULL OR perd.gl_segment10 IS NOT NULL OR perd.gl_segment11'
			|| ' 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'
			|| ' perd.gl_segment15 IS NOT NULL OR perd.gl_segment16 IS NOT NULL OR perd.gl_segment17 IS NOT NULL OR '
			|| 'perd.gl_segment18 IS NOT NULL OR perd.gl_segment19 IS NOT NULL OR perd.gl_segment20 IS NOT NULL OR '
			|| 'perd.gl_segment21 IS NOT NULL OR perd.gl_segment22 IS NOT NULL OR perd.gl_segment23 IS NOT NULL OR '
			|| 'perd.gl_segment24 IS NOT NULL OR perd.gl_segment25 IS NOT NULL OR perd.gl_segment26 IS NOT NULL OR '
			|| 'perd.gl_segment27 IS NOT NULL OR perd.gl_segment28 IS NOT NULL OR perd.gl_segment29 IS NOT NULL OR '
			|| 'perd.gl_segment30 IS NOT NULL) AND effort_report_id = ' || TO_CHAR(p_effort_report_id)
			|| ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;

Line: 948

		query1 := 'SELECT TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '
			|| 'TRIM(TO_CHAR(perd.PAYROLL_PERCENT, ''999G990D00'')) payroll_percent, '
			|| 'TRIM(TO_CHAR(perd.PROPOSED_SALARY_AMT, ''' || l_num25_fmask || ''')) proposed_salary_amt, '
			|| 'TRIM(TO_CHAR(perd.PROPOSED_EFFORT_PERCENT, ''999G990D00'')) proposed_effort_percent, '
			|| 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
			|| 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
			|| 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
			|| 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
			|| 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
			|| 'psp_eff_report_approvals pera WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
			|| 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
			|| '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type = ppa.project_type AND '
			|| 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
			|| '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
			|| TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;

Line: 973

		query1 := 'SELECT TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '
			|| 'TRIM(TO_CHAR(perd.PAYROLL_PERCENT, ''999G990D00'')) payroll_percent, '
			|| 'TRIM(TO_CHAR(perd.PROPOSED_SALARY_AMT, ''' || l_num25_fmask || ''')) proposed_salary_amt, '
			|| 'TRIM(TO_CHAR(perd.PROPOSED_EFFORT_PERCENT, ''999G990D00'')) proposed_effort_percent, '
			|| 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
			|| 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
			|| 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
			|| 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
			|| 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
			|| 'psp_eff_report_approvals pera WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
			|| 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
			|| '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type(+) = ppa.project_type AND '
			|| 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
			|| '''N'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
			|| TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check || l_sort_option1;

Line: 998

		query1 := 'SELECT TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '
			|| 'TRIM(TO_CHAR(perd.PAYROLL_PERCENT, ''999G990D00'')) payroll_percent, '
			|| 'TRIM(TO_CHAR(perd.PROPOSED_SALARY_AMT, ''' || l_num25_fmask || ''')) proposed_salary_amt, '
			|| 'TRIM(TO_CHAR(perd.PROPOSED_EFFORT_PERCENT, ''999G990D00'')) proposed_effort_percent, '
			|| 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
			|| 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
			|| 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
			|| 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
			|| 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
			|| 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND '
			|| '(perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL OR perd.gl_segment3 IS NOT NULL OR '
			|| 'perd.gl_segment4 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 perd.gl_segment8 IS NOT NULL OR perd.gl_segment9 IS NOT NULL OR '
			|| 'perd.gl_segment10 IS NOT NULL OR perd.gl_segment11 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 perd.gl_segment15 IS NOT NULL OR '
			|| 'perd.gl_segment16 IS NOT NULL OR perd.gl_segment17 IS NOT NULL OR perd.gl_segment18 IS NOT NULL OR '
			|| 'perd.gl_segment19 IS NOT NULL OR perd.gl_segment20 IS NOT NULL OR perd.gl_segment21 IS NOT NULL OR '
			|| 'perd.gl_segment22 IS NOT NULL OR perd.gl_segment23 IS NOT NULL OR perd.gl_segment24 IS NOT NULL OR '
			|| 'perd.gl_segment25 IS NOT NULL OR perd.gl_segment26 IS NOT NULL OR perd.gl_segment27 IS NOT NULL OR '
			|| 'perd.gl_segment28 IS NOT NULL OR perd.gl_segment29 IS NOT NULL OR perd.gl_segment30 IS NOT NULL) '
			|| 'AND effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
			|| l_assignment_check || l_sort_option1;

Line: 1041

	query1 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) approver_name, '
		|| 'TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
		|| ' approval_date FROM psp_eff_report_approvals pera, psp_eff_report_details perd '
		|| 'WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
		|| TO_CHAR(p_effort_report_id) || ' ORDER BY approver_order_num DESC';

Line: 1051

	SELECT distinct prt.approval_type INTO l_approval_type
	FROM psp_report_templates prt,
	     psp_eff_reports per
	where  per.effort_report_id = p_effort_report_id
	and    per.template_id = prt.template_id;

Line: 1059

	  query1 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) approver_name, '
		|| 'TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
		|| ' approval_date,  wfna.TEXT_VALUE note FROM psp_eff_report_approvals pera, psp_eff_report_details perd, WF_NOTIFICATION_ATTRIBUTES wfna '
		|| ' WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
		|| TO_CHAR(p_effort_report_id) || ' AND wfna.NAME(+) =''WF_NOTE'' AND wfna.NOTIFICATION_ID(+) = pera.NOTIFICATION_ID '
		|| ' ORDER BY approver_order_num DESC';

Line: 1066

	  query1 := 'SELECT 1 approval_sequence, ''PRE-APPROVED'' approver_name, '
		|| 'TO_CHAR(sysdate, ''' || l_icx_date_format || ''')'
		|| 'approval_date,  ''Process Initiated by ''||''' || l_initiator_name || ''' note FROM DUAL';

Line: 1086

		query1 := 'SELECT ' || TO_CHAR(p_person_id) || ' person_id,  er.full_name employee_name, employee_number '
			|| ' employee_number, '|| to_char(p_emp_primary_org_id) || ' organization_id, emp_primary_org_name '
			|| ' organization_name, mailstop mailstop, '
			|| 'TRIM(TO_CHAR(perd.ACTUAL_SALARY_AMT, ''' || l_num25_fmask || ''')) actual_salary_amt, '
			|| 'TRIM(TO_CHAR(perd.PAYROLL_PERCENT, ''999G990D00'')) payroll_percent, '
			|| 'TRIM(TO_CHAR(perd.PROPOSED_SALARY_AMT, ''' || l_num25_fmask || ''')) proposed_salary_amt, '
			|| 'TRIM(TO_CHAR(perd.PROPOSED_EFFORT_PERCENT, ''999G990D00'')) proposed_effort_percent, '
			|| 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
			|| 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
			|| 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
			|| 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
			|| 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share, '
			|| 'pera.pera_information1, pera.pera_information2, pera.pera_information3, pera.pera_information4, pera.pera_information5, '
			|| 'pera.pera_information6, pera.pera_information7, pera.pera_information8, pera.pera_information9, pera.pera_information10, '
			|| 'pera.pera_information11, pera.pera_information12, pera.pera_information13, pera.pera_information14, pera.pera_information15, '
			|| 'pera.pera_information16, pera.pera_information17, pera.pera_information8, pera.pera_information19, pera.pera_information20 '
			|| 'FROM psp_eff_Reports er, psp_eff_report_details perd, '
			|| '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 '
			|| ' perd.effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
			|| l_er_check || l_sort_option1;

Line: 1150

SELECT	category_id
FROM	fnd_document_categories
WHERE	name = 'CUSTOM3788';

Line: 1155

SELECT	message_text
FROM	fnd_new_messages fnm
WHERe	fnm.message_name = 'PSP_ER_PDF_FILENAME'
AND	language_code = USERENV('LANG');

Line: 1160

SELECT	1
FROM	psp_report_errors
WHERE	request_id = l_request_id
AND	message_level = 'E'
AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
AND	pdf_request_id = g_request_id;

Line: 1168

SELECT	request_id
FROM	psp_eff_reports per
WHERE	EXISTS	(SELECT	1
		FROM	psp_eff_report_details perd,
			psp_eff_report_approvals pera
		WHERE	perd.effort_report_id = per.effort_report_id
		AND	perd.effort_report_detail_id = pera.effort_report_detail_id)
AND	ROWNUM = 1;

Line: 1178

SELECT	fl.file_id
FROM	fnd_lobs fl,
	fnd_attached_documents fad,
	fnd_documents_vl  fdl
WHERE	fad.pk1_value = TO_CHAR(p_wf_item_key)||p_wf_Role_Name
--AND     fad.pk3_value =  p_wf_role_name
AND	fdl.document_id = fad.document_id
AND	fdl.media_id = fl.file_id
AND	fad.entity_name = 'ERDETAILS'
AND	NVL(fad.pk2_value, 'AR') = NVL(p_receiver_flag, 'AR');

Line: 1211

		fnd_documents_pkg.insert_row
			(X_Rowid				=>	l_row_id_tmp,
			X_document_id				=>	l_document_id_tmp,
			X_creation_date				=>	SYSDATE,
			X_created_by				=>	1,
			X_last_update_date			=>	SYSDATE,
			X_last_updated_by			=>	1,
			X_last_update_login			=>	1,
			X_datatype_id				=>	6,
			X_category_id				=>	l_category_id,
			X_security_type				=>	1,
			X_security_id				=>	NULL,
			X_publish_flag				=>	'Y',
			X_image_type				=>	NULL,
			X_storage_type				=>	NULL,
			X_usage_type				=>	'O',
			X_start_date_active			=>	SYSDATE,
			X_end_date_active			=>	NULL,
			X_request_id				=>	NULL,
			X_program_application_id		=>	NULL,
			X_program_id				=>	NULL,
			X_program_update_date			=>	SYSDATE,
			X_language				=>	USERENV('LANG'),
			X_description				=>	NULL,
			X_file_name				=>	l_pdf_filename,
			X_media_id				=>	l_pdf_file_id);

Line: 1238

		INSERT INTO fnd_lobs
			(file_id,		File_name,		file_content_type,
			upload_date,		expiration_date,	program_name,
			program_tag,		file_data,		language,
			oracle_charset,		file_format)
		VALUES
			(l_pdf_file_id,		l_pdf_filename,		'application/pdf',
			SYSDATE,		NULL,			'PSPERPDF',
			NULL,			empty_blob(),		USERENV('LANG'),
			NULL,			'binary');

Line: 1249

		INSERT INTO fnd_attached_documents
			(attached_document_id,		document_id,		creation_date,
			created_by,			last_update_date,	last_updated_by,
			last_update_login,		seq_num,		entity_name,
			pk1_value,			pk2_value,		pk3_value,
			pk4_value,			pk5_value,		automatically_added_flag,
			program_application_id,		program_id,		program_update_date,
			request_id,			attribute_category,	attribute1,
			attribute2,			attribute3,		attribute4,
			attribute5,			attribute6,		attribute7,
			attribute8,			attribute9,		attribute10,
			attribute11,			attribute12,		attribute13,
			attribute14,			attribute15,		column1)
		VALUES	(fnd_attached_documents_s.nextval,	l_document_id_tmp,	SYSDATE,
			1,				SYSDATE,		1,
			NULL,				10,			'ERDETAILS',
			TO_CHAR(p_wf_item_key)||p_wf_Role_Name,		p_receiver_flag,	null,
			NULL,				NULL,			'N',
			NULL,				NULL,			SYSDATE,
			NULL,				NULL,			NULL,
			NULL,				NULL,			NULL,
			NULL,				NULL,			NULL,
			NULL,				NULL,			NULL,
			NULL,				NULL,			NULL,
			NULL,				NULL,			NULL);

Line: 1307

SELECT	1
FROM	psp_report_errors
WHERE	request_id = l_request_id
AND	message_level = 'E'
AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
AND	pdf_request_id = g_request_id;

Line: 1315

SELECT	request_id
FROM	psp_eff_reports per
WHERE	EXISTS	(SELECT	1
		FROM	psp_eff_report_details perd,
			psp_eff_report_approvals pera
		WHERE	perd.effort_report_id = per.effort_report_id
		AND	perd.effort_report_detail_id = pera.effort_report_detail_id)
AND	ROWNUM = 1;

Line: 1350

	SELECT	file_data,
	        file_id  -- Bug 7135471
	INTO	l_document,
	        l_pdf_file_id -- Bug 7135471
	FROM	fnd_lobs fl,
		fnd_attached_documents fad,
		fnd_documents_vl  fdl
	WHERE	fad.pk1_value = l_item_key || l_rname
	AND	fdl.document_id = fad.document_id
	AND	fdl.media_id = fl.file_id
	AND	fad.entity_name = 'ERDETAILS'
	AND     fl.file_id = (select max(file_id) FROM	fnd_lobs fl,  -- Bug 7135471
							fnd_attached_documents fad,
							fnd_documents_vl  fdl
						WHERE	fad.pk1_value = l_item_key || l_rname
						AND	fdl.document_id = fad.document_id
						AND	fdl.media_id = fl.file_id
						AND	fad.entity_name = 'ERDETAILS');

Line: 1397

PROCEDURE	update_er_person_xml	(p_start_person		IN		NUMBER,
					p_end_person		IN		NUMBER,
					p_request_id		IN		NUMBER,
					p_retry_request_id	IN		NUMBER	DEFAULT NULL,
					p_return_status		OUT	NOCOPY	VARCHAR2) IS
CURSOR	add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
SELECT	1
FROM	psp_report_errors
WHERE	request_id = p_request_id
AND	message_level = 'E'
AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
AND	pdf_request_id = g_request_id;

Line: 1411

cursor er_cur is select effort_Report_id,
person_id, template_id,  request_id, set_of_books_id, full_name,
employee_number, mailstop, emp_primary_org_name, emp_primary_org_id, currency_code
from psp_Eff_reports where request_id = p_request_id
	AND	person_id BETWEEN p_start_person AND p_end_person
	AND	status_code <> 'R';

Line: 1446

	UPDATE	psp_eff_reports
	SET	person_xml = x_lob
	WHERE	effort_report_id = rec.effort_report_id;

Line: 1454

		fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'UPDATE_ER_PERSON_XML', sqlerrm);

Line: 1470

END update_er_person_xml;

Line: 1472

PROCEDURE	update_er_person_xml	(p_request_id	IN		NUMBER,
					p_return_status	OUT NOCOPY	VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;

Line: 1476

SELECT	1
FROM	psp_report_errors
WHERE	request_id = p_request_id
AND	message_level = 'E'
AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
AND	pdf_request_id = g_request_id;

Line: 1487

	UPDATE	psp_eff_reports
	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)
	WHERE	request_id = p_request_id
	AND	status_code <> 'R';

Line: 1497

		fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'UPDATE_ER_PERSON_XML', sqlerrm);

Line: 1513

END update_er_person_xml;

Line: 1515

PROCEDURE	update_er_person_xml	(p_wf_item_key	IN		NUMBER,
					p_return_status	OUT	NOCOPY	VARCHAR2) IS
CURSOR	get_request_id IS
SELECT	request_id
FROM	psp_eff_reports per,
	psp_eff_report_details perd,
	psp_eff_report_approvals pera
WHERE	perd.effort_report_detail_id = pera.effort_report_detail_id
AND	per.effort_report_id = perd.effort_report_id
AND	pera.wf_item_key = p_wf_item_key;

Line: 1529

SELECT	1
FROM	psp_report_errors
WHERE	request_id = l_request_id
AND	message_level = 'E'
AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
AND	pdf_request_id = g_request_id;

Line: 1540

SELECT	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
FROM	psp_eff_reports
WHERE	effort_report_id IN	(SELECT	effort_report_id
				FROM	psp_eff_report_details perd,
					psp_eff_report_approvals pera
				WHERE	perd.effort_report_detail_id = pera.effort_report_detail_id
				AND	pera.wf_item_key = p_wf_item_key)
AND	status_code <> 'R';

Line: 1582

		UPDATE	psp_eff_reports
		SET	person_xml = l_xml
		WHERE	effort_report_id = person_rec.effort_report_id;

Line: 1588

/*****	Converted single xml clob update statement into row by row update
	UPDATE	psp_eff_reports
	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)
	WHERE	effort_report_id IN	(SELECT	effort_report_id
					FROM	psp_eff_report_details perd,
						psp_eff_report_approvals pera
					WHERE	perd.effort_report_detail_id = pera.effort_report_detail_id
					AND	pera.wf_item_key = p_wf_item_key)
	AND	status_code <> 'R';

Line: 1601

		fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'UPDATE_ER_PERSON_XML', sqlerrm);

Line: 1621

END update_er_person_xml;

Line: 1625

PROCEDURE update_er_details	(p_start_person		IN		NUMBER,
				p_end_person		IN		NUMBER,
				p_request_id		IN		NUMBER,
				p_retry_request_id	IN		NUMBER	DEFAULT NULL,
				p_return_status		OUT	NOCOPY	VARCHAR2) IS
TYPE t_num_15_type	IS TABLE OF NUMBER(15)	INDEX BY BINARY_INTEGER;

Line: 1648

SELECT	effort_report_id,
	start_date,
	end_date
FROM	psp_eff_reports per
WHERE	person_id BETWEEN p_start_person AND p_end_person
AND	status_code <> 'R'
AND	request_id = p_request_id;

Line: 1657

SELECT	SUBSTR(report_template_code, 6, 3) layout_type,
	per.template_id,
	per.set_of_books_id,
	prth.report_template_code
FROM	psp_eff_reports per,
	psp_report_templates_h prth
WHERE	per.request_id = p_request_id
AND	prth.request_id = per.request_id
AND	ROWNUM = 1;

Line: 1668

SELECT	fnd_flex_ext.get_delimiter('SQLGL', 'GL#', gsob.chart_of_accounts_id)
FROM	gl_sets_of_books gsob
WHERE	gsob.set_of_books_id = l_set_of_books_id;

Line: 1673

SELECT	'GL_' || fifs.application_column_name || ' || ''' || l_segment_delimiter || ''' || ' segment_name
FROM	fnd_id_flex_segments fifs,
	gl_sets_of_books gsob,
	fnd_application fa
WHERE	gsob.set_of_books_id = l_set_of_books_id
AND	fifs.id_flex_num = gsob.chart_of_accounts_id
AND	fifs.id_flex_code = 'GL#'
AND	fifs.application_id = fa.application_id
AND	fa.application_short_name = 'SQLGL'
AND	EXISTS	(SELECT	1
		FROM	psp_report_template_details_h prtdh
		WHERE	prtdh.REQUEST_ID= p_request_id
		AND	prtdh.criteria_lookup_type = 'PSP_SUMMARIZATION_CRITERIA'
		AND	prtdh.criteria_lookup_code = fifs.application_column_name)
ORDER BY fifs.segment_num;

Line: 1690

SELECT	1
FROM	psp_report_errors
WHERE	request_id = p_request_id
AND	message_level = 'E'
AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
AND	pdf_request_id = g_request_id;

Line: 1702

SELECT	layout_lookup_code
FROM	psp_layout_options
WHERE	report_template_code = TRIM(l_report_template_code)
AND	layout_lookup_type = 'PSP_PROJECT_MANAGER_ROLE';

Line: 1726

		UPDATE	psp_eff_report_details perd
		SET	assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
					WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
					AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
					AND paaf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
			(project_name, project_number) = (SELECT name, segment1 FROM pa_projects_all paa
							WHERE paa.project_id = perd.project_id),
			(award_short_name, award_number) = (SELECT award_short_name, award_number FROM gms_awards_all gaa
							WHERE gaa.award_id = perd.award_id),
			(task_name, task_number) = (SELECT task_name, task_number FROM pa_tasks pt WHERE pt.task_id = perd.task_id),
			exp_org_name = (SELECT name FROM hr_all_organization_units haou
					WHERE haou.organization_id = perd.expenditure_organization_id),
			actual_salary_amt = NVL(actual_salary_amt, 0),
			payroll_percent = NVL(payroll_percent, 0),
			proposed_salary_amt = NVL(proposed_salary_amt, 0),
			proposed_effort_percent = NVL(proposed_effort_percent, 0),
			committed_cost_share = NVL(committed_cost_share, 0)
		WHERE	perd.effort_report_id = r_effort_report.r_effort_report_id(I);

Line: 1761

			l_query := 'UPDATE psp_eff_report_details SET gl_sum_criteria_segment_name = ' || l_query
				|| ' WHERE effort_report_id IN (SELECT per.effort_report_id FROM psp_eff_reports per WHERE per.request_id = '
				|| TO_CHAR(p_request_id) || ' AND per.person_id BETWEEN '
				|| TO_CHAR(p_start_person) || ' AND ' || TO_CHAR(p_end_person) || ') AND '
				|| '(gl_segment1 IS NOT NULL OR gl_segment2 IS NOT NULL OR gl_segment3 IS NOT NULL OR '
				|| 'gl_segment4 IS NOT NULL OR gl_segment5 IS NOT NULL OR gl_segment6 IS NOT NULL OR '
				|| 'gl_segment7 IS NOT NULL OR gl_segment8 IS NOT NULL OR gl_segment9 IS NOT NULL OR '
				|| 'gl_segment10 IS NOT NULL OR gl_segment11 IS NOT NULL OR gl_segment12 IS NOT NULL OR '
				|| 'gl_segment13 IS NOT NULL OR gl_segment14 IS NOT NULL OR gl_segment15 IS NOT NULL OR '
				|| 'gl_segment16 IS NOT NULL OR gl_segment17 IS NOT NULL OR gl_segment18 IS NOT NULL OR '
				|| 'gl_segment19 IS NOT NULL OR gl_segment20 IS NOT NULL OR gl_segment21 IS NOT NULL OR '
				|| 'gl_segment22 IS NOT NULL OR gl_segment23 IS NOT NULL OR gl_segment24 IS NOT NULL OR '
				|| 'gl_segment25 IS NOT NULL OR gl_segment26 IS NOT NULL OR gl_segment27 IS NOT NULL OR '
				|| 'gl_segment28 IS NOT NULL OR gl_segment29 IS NOT NULL OR gl_segment30 IS NOT NULL)';

Line: 1783

		UPDATE	psp_eff_report_details perd
		SET	assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
				WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
				AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
				AND paaf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
			(project_name, project_number) = (SELECT name, segment1 FROM pa_projects_all paa
				WHERE paa.project_id = perd.project_id),
			(award_short_name, award_number) = (SELECT award_short_name, award_number FROM gms_awards_all gaa
				WHERE gaa.award_id = perd.award_id),
			(task_name, task_number) = (SELECT task_name, task_number FROM pa_tasks pt WHERE pt.task_id = perd.task_id),
			exp_org_name = (SELECT name FROM hr_all_organization_units haou
				WHERE haou.organization_id = perd.expenditure_organization_id),
			(investigator_name, investigator_person_id) = (SELECT full_name, person_id FROM per_all_people_f papf
				WHERE papf.person_id = (SELECT person_id FROM gms_personnel gp
						WHERE	gp.award_id = perd.award_id AND gp.award_role = 'PI'
						AND	gp.start_date_active = (SELECT	MAX(gp2.start_date_active)
									FROM	gms_personnel gp2
									WHERE	gp2.award_id = perd.award_id
                                                                        AND gp2.award_role = 'PI'  --- added for uva fix
                                                                        AND     nvl(gp2.end_date_active,to_date('31-12-4712','dd-mm-yyyy')) >= r_effort_report.r_start_date(I)    --- uva fix
									AND	gp2.start_date_active <= r_effort_report.r_end_date(I))
						AND	ROWNUM = 1)
				AND papf.effective_start_date <= r_effort_report.r_end_date(I)
				AND papf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
			actual_salary_amt = NVL(actual_salary_amt, 0),
			payroll_percent = NVL(payroll_percent, 0),
			proposed_salary_amt = NVL(proposed_salary_amt, 0),
			proposed_effort_percent = NVL(proposed_effort_percent, 0),
			committed_cost_share = NVL(committed_cost_share, 0)
		WHERE	perd.effort_report_id = r_effort_report.r_effort_report_id(I);

Line: 1815

		UPDATE	psp_eff_report_details perd
		SET	assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
				WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
				AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
				AND paaf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
			(project_name, project_number) = (SELECT name, segment1 FROM pa_projects_all paa
				WHERE paa.project_id = perd.project_id),
			(award_short_name, award_number) = (SELECT award_short_name, award_number FROM gms_awards_all gaa
				WHERE gaa.award_id = perd.award_id),
			(task_name, task_number) = (SELECT task_name, task_number FROM pa_tasks pt WHERE pt.task_id = perd.task_id),
			exp_org_name = (SELECT name FROM hr_all_organization_units haou
				WHERE haou.organization_id = perd.expenditure_organization_id),
			(investigator_name, investigator_person_id) = (SELECT full_name, person_id FROM per_all_people_f papf
   --- added max person_id for uva issue
				WHERE papf.person_id = (SELECT max(person_id) FROM pa_project_players pap
--						WHERE	pap.project_id = perd.project_id AND project_role_type = 'PROJECT MANAGER'
						WHERE	pap.project_id = perd.project_id AND project_role_type = l_project_manager_role
						AND	pap.start_date_active = (SELECT	MAX(pap2.start_date_active)
									FROM	pa_project_players pap2
									WHERE	pap2.project_id = perd.project_id
                                                                        AND     pap2.project_role_type = l_project_manager_role
                                                                        AND     nvl(pap2.end_date_active,to_date('31-12-4712','dd-mm-yyyy')) >= r_effort_report.r_start_date(I)    --- uva fix
									AND	pap2.start_date_active <= r_effort_report.r_end_date(I)))
				AND papf.effective_start_date <= r_effort_report.r_end_date(I)
				AND papf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
			actual_salary_amt = NVL(actual_salary_amt, 0),
			payroll_percent = NVL(payroll_percent, 0),
			proposed_salary_amt = NVL(proposed_salary_amt, 0),
			proposed_effort_percent = NVL(proposed_effort_percent, 0),
			committed_cost_share = NVL(committed_cost_share, 0)
		WHERE	perd.effort_report_id = r_effort_report.r_effort_report_id(I);

Line: 1848

		UPDATE	psp_eff_report_details perd
		SET	assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
				WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
				AND paaf.effective_start_date <= per.end_date
				AND paaf.effective_end_date >= per.start_date AND ROWNUM = 1),
			(project_name, project_number) = (SELECT name, segment1 FROM pa_projects_all paa
				WHERE paa.project_id = perd.project_id),
			(award_short_name, award_number) = (SELECT award_short_name, award_number FROM gms_awards_all gaa
				WHERE gaa.award_id = perd.award_id),
			(task_name, task_number) = (SELECT task_name, task_number FROM pa_tasks pt WHERE pt.task_id = perd.task_id),
			exp_org_name = (SELECT name FROM hr_all_organization_units haou
				WHERE haou.organization_id = perd.expenditure_organization_id),
			(investigator_name, investigator_person_id) = (SELECT full_name, person_id FROM per_all_people_f papf
				WHERE papf.person_id = (select task_manager_person_id from pa_tasks pt WHERE pt.task_id = perd.task_id)
				AND papf.effective_start_date <= r_effort_report.r_end_date(I)
				AND papf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),
			actual_salary_amt = NVL(actual_salary_amt, 0),
			payroll_percent = NVL(payroll_percent, 0),
			proposed_salary_amt = NVL(proposed_salary_amt, 0),
			proposed_effort_percent = NVL(proposed_effort_percent, 0),
			committed_cost_share = NVL(committed_cost_share, 0)
		WHERE	perd.effort_report_id = r_effort_report.r_effort_report_id(I);

Line: 1873

	UPDATE	psp_eff_report_details perd
	SET	(investigator_primary_org_id, investigator_org_name) = (SELECT haou.organization_id, haou.name
				FROM hr_all_organization_units haou, per_all_assignments_f paaf
				WHERE haou.organization_id = paaf.organization_id AND paaf.person_id = perd.investigator_person_id
				AND paaf.effective_start_date  <= r_effort_report.r_end_date(I)
				AND paaf.effective_end_date >= r_effort_report.r_start_date(I)
				AND paaf.primary_flag = 'Y' AND ROWNUM = 1)
	WHERE	perd.effort_report_id = r_effort_report.r_effort_report_id(I)
	AND	perd.investigator_person_id IS NOT NULL;

Line: 1884

	UPDATE	psp_eff_reports per
	SET	(employee_number, full_name, mailstop) = (SELECT papf.employee_number, papf.full_name, papf.mailstop FROM per_all_people_f papf
				WHERE papf.person_id = per.person_id AND papf.effective_start_date <= per.end_date
				AND papf.effective_end_date >= per.start_date AND ROWNUM = 1),
		(emp_primary_org_id, emp_primary_org_name) = (SELECT haou.organization_id, haou.name
				FROM hr_all_organization_units haou, per_all_assignments_f paaf
				WHERE haou.organization_id = paaf.organization_id AND paaf.person_id = per.person_id
				AND paaf.effective_start_date  <= per.end_date AND paaf.effective_end_date >= per.start_date
				AND paaf.primary_flag = 'Y' AND ROWNUM = 1)
	WHERE	per.effort_report_id = r_effort_report.r_effort_report_id(I);

Line: 1898

		fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'UPDATE_ER_DETAILS', sqlerrm);

Line: 1914

END update_er_details;

Line: 1924

    SELECT	effort_report_id
    FROM	psp_eff_reports per
    WHERE	person_id BETWEEN p_start_person AND p_end_person
    AND	status_code <> 'R'
    AND	request_id = p_request_id;

Line: 1931

    SELECT	1
    FROM	psp_report_errors
    WHERE	request_id = p_request_id
    AND	message_level = 'E'
    AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
    AND	pdf_request_id = g_request_id;

Line: 1957

 sql_stmt := ' Update psp_eff_report_details set project_id = GL_'||l_proj_segment ||
    ' , TASK_ID = GL_'|| l_tsk_segment || ' , AWARD_ID = GL_' || l_awd_sgement || ' , EXPENDITURE_ORGANIZATION_ID = GL_' || l_exp_org_segment
    || ' , EXPENDITURE_TYPE = GL_'|| l_exp_type_segment ||' WHERE effort_report_id in (select effort_report_id FROM psp_eff_reports per'
    || ' WHERE	person_id BETWEEN ' || p_start_person || ' AND ' || p_end_person
    || ' AND	status_code <> ''R'' AND	request_id = ' || p_request_id ||'  )';

Line: 1991

PROCEDURE	update_er_error_details	(p_request_id		IN		NUMBER,
					p_retry_request_id	IN		NUMBER,
					p_return_status		OUT	NOCOPY	VARCHAR2) IS

CURSOR	add_report_error_cur (p_sqlerrm IN VARCHAR2) IS
SELECT	1
FROM	psp_report_errors
WHERE	request_id = p_request_id
AND	message_level = 'E'
AND	error_message = SUBSTR(p_sqlerrm, 1, 2000)
AND	pdf_request_id = g_request_id;

Line: 2004

SELECT	fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_2)) start_date,
	fnd_date.canonical_to_date(fnd_date.date_to_canonical(prth.parameter_value_3)) end_date
FROM	psp_report_templates_h prth
WHERE	prth.request_id = p_request_id;

Line: 2019

	UPDATE	psp_report_errors pre
	SET	(source_name, parent_source_id, parent_source_name) =	(SELECT	full_name, emp_primary_org_id, emp_primary_org_name
							FROM	psp_eff_reports per
							WHERE	per.request_id = p_request_id
							AND	per.person_id = TO_NUMBER(pre.source_id))
	WHERE	pre.request_id = p_request_id
	AND	pre.source_id IS NOT NULL
	AND	pre.source_name IS NULL;

Line: 2028

	UPDATE	psp_report_errors pre
	SET	(source_name, parent_source_id, parent_source_name) =	(SELECT	papf.full_name, paaf.organization_id, haou.name
							FROM	per_all_assignments_f paaf,
								per_all_people_f papf,
								hr_all_organization_units haou
							WHERE	haou.organization_id = paaf.organization_id
							AND	papf.person_id = TO_NUMBER(pre.source_id)
							AND	paaf.person_id = TO_NUMBER(pre.source_id)
							AND	paaf.primary_flag = 'Y'
							AND	paaf.effective_start_date = (SELECT	MAX(paaf2.effective_start_date)
											FROM	per_all_assignments_f paaf2
											WHERE	paaf2.effective_start_date <= l_end_date
											AND	paaf2.effective_end_date >= l_start_date
											AND	paaf2.person_id = TO_NUMBER(pre.source_id))
							AND	papf.effective_start_date = (SELECT	MAX(papf2.effective_start_date)
											FROM	per_all_people_f papf2
											WHERE	papf2.effective_start_date <= l_end_date
											AND	papf2.effective_end_date >= l_start_date
											AND	papf2.person_id = TO_NUMBER(pre.source_id)))
	WHERE	pre.request_id = p_request_id
	AND	pre.source_id IS NOT NULL
	AND	pre.source_name IS NULL;

Line: 2051

	UPDATE	psp_report_errors pre
	SET	source_name =	(SELECT	papf.full_name
				FROM	per_all_people_f papf
				WHERE	papf.person_id = TO_NUMBER(pre.source_id)
				AND	papf.effective_start_date = (SELECT	MAX(papf2.effective_start_date)
									FROM	per_all_people_f papf2
									WHERE	papf2.effective_start_date <= l_end_date
									AND	papf2.effective_end_date >= l_start_date
									AND	papf2.person_id = TO_NUMBER(pre.source_id)))
	WHERE	pre.request_id = p_request_id
	AND	pre.source_id IS NOT NULL
	AND	pre.source_name IS NULL;

Line: 2064

	UPDATE	psp_report_errors pre
	SET	source_name =	(SELECT	papf.full_name
				FROM	per_all_people_f papf
				WHERE	papf.person_id = TO_NUMBER(pre.source_id)
				AND	papf.effective_start_date = (SELECT	MIN(papf2.effective_start_date)
									FROM	per_all_people_f papf2
									WHERE	papf2.person_id = TO_NUMBER(pre.source_id)))
	WHERE	pre.request_id = p_request_id
	AND	pre.source_id IS NOT NULL
	AND	pre.source_name IS NULL;

Line: 2078

		fnd_msg_pub.add_exc_msg('PSP_XMLGEN', 'UPDATE_ER_ERROR_DETAILS', sqlerrm);

Line: 2094

END update_er_error_details;

Line: 2099

PROCEDURE update_grouping_category (	p_start_person		IN		NUMBER,
					p_end_person		IN		NUMBER,
					p_request_id		IN		NUMBER,
					p_return_status		OUT	NOCOPY	VARCHAR2) IS

TYPE t_num_15_type	IS TABLE OF NUMBER(15)	INDEX BY BINARY_INTEGER;

Line: 2109

SELECT	perd.effort_report_detail_id
FROM	psp_eff_reports per,
        psp_eff_report_details perd
WHERE	per.effort_report_id = perd.effort_report_id
AND     per.person_id BETWEEN p_start_person AND p_end_person
AND	request_id = p_request_id
AND    (perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL
        OR perd.gl_segment3 IS NOT NULL OR perd.gl_segment4 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 perd.gl_segment8 IS NOT NULL
        OR perd.gl_segment9 IS NOT NULL OR perd.gl_segment10 IS NOT NULL
        OR perd.gl_segment11 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 perd.gl_segment15 IS NOT NULL OR perd.gl_segment16 IS NOT NULL
        OR perd.gl_segment17 IS NOT NULL OR perd.gl_segment18 IS NOT NULL
        OR perd.gl_segment19 IS NOT NULL OR perd.gl_segment20 IS NOT NULL
        OR perd.gl_segment21 IS NOT NULL OR perd.gl_segment22 IS NOT NULL
        OR perd.gl_segment23 IS NOT NULL OR perd.gl_segment24 IS NOT NULL
        OR perd.gl_segment25 IS NOT NULL OR perd.gl_segment26 IS NOT NULL
        OR perd.gl_segment27 IS NOT NULL OR perd.gl_segment28 IS NOT NULL
        OR perd.gl_segment29 IS NOT NULL OR perd.gl_segment30 IS NOT NULL);

Line: 2132

SELECT	perd.effort_report_detail_id
FROM	psp_eff_reports per,
        psp_eff_report_details perd
WHERE	per.effort_report_id = perd.effort_report_id
AND     per.person_id BETWEEN p_start_person AND p_end_person
AND	request_id = p_request_id
AND     (  perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL
        OR perd.award_id IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL
        OR perd.expenditure_type IS NOT NULL)
AND EXISTS (SELECT 1 FROM pa_projects_all ppa, gms_project_types gpta -- Changed from gms_project_types_all for bug 5503605
            WHERE gpta.project_type = ppa.project_type
	    AND ppa.project_id = perd.project_id
	    AND ppa.project_type <> 'AWARD_PROJECT' AND NVL(gpta.sponsored_flag, 'N') ='Y');

Line: 2147

SELECT	perd.effort_report_detail_id
FROM	psp_eff_reports per,
        psp_eff_report_details perd
WHERE	per.effort_report_id = perd.effort_report_id
AND     per.person_id BETWEEN p_start_person AND p_end_person
AND	request_id = p_request_id
AND     (  perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL
        OR perd.award_id IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL
        OR perd.expenditure_type IS NOT NULL)
AND EXISTS (SELECT 1 FROM pa_projects_all ppa, gms_project_types gpta -- Changed from gms_project_types_all for bug 5503605
            WHERE gpta.project_type(+) = ppa.project_type
	    AND ppa.project_id = perd.project_id
	    AND ppa.project_type <> 'AWARD_PROJECT' AND NVL(gpta.sponsored_flag, 'N') ='N');

Line: 2168

	UPDATE psp_eff_report_details perd SET grouping_category = 'GLA'
	WHERE  effort_report_detail_id = effort_report_detail_id_rec(I);

Line: 2171

	effort_report_detail_id_rec.delete;

Line: 2178

	UPDATE psp_eff_report_details perd SET grouping_category = 'SPO'
	WHERE  effort_report_detail_id = effort_report_detail_id_rec(I);

Line: 2181

	effort_report_detail_id_rec.delete;

Line: 2189

	UPDATE psp_eff_report_details perd SET grouping_category = 'NSP'
	WHERE  effort_report_detail_id = effort_report_detail_id_rec(I);

Line: 2192

	effort_report_detail_id_rec.delete;

Line: 2207

END update_grouping_category;