[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT person_id from psp_selected_persons_t where
person_id between stperson and endperson and request_id in
(select request_id from pay_payroll_actions where payroll_action_id= p_pactid);
select pay_assignment_actions_s.nextval into l_asgactid from dual;
select count(object_id) into l_cnt from pay_temp_object_actions where
payroll_action_id = p_pactid;
SELECT request_id, legislative_parameters into g_psp_request_id, param_string from pay_payroll_actions where payroll_action_id = p_payroll_action_id;
g_psp_template_id := psp_template_selection.get_parameter_value('TEMPLATE_ID', param_string);
g_psp_effort_start:= trunc(fnd_date.canonical_to_date(psp_template_selection.get_parameter_value('START_DATE', param_string)));
g_psp_effort_end:= trunc(fnd_date.canonical_to_date(psp_template_selection.get_parameter_value('END_DATE', param_string)));
/* insert record in psp_report_templates_h */
END;
p_eff_report_details_api.update_eff_report_details(p_validate , p_request_id )
*/
end;
CURSOR get_summarization_criteria(p_request_id IN NUMBER) is Select criteria_lookup_code, criteria_value1
from psp_report_template_details_h where request_id = p_request_id and
criteria_lookup_type='PSP_SUMMARIZATION_CRITERIA' order by
to_number(criteria_value1);
l_select_string varchar2(4000);
select distinct person_id from psp_selected_persons_t where request_id = p_request_id and person_id between
l_start_person and l_end_person;
select DISTINCT person_id -- Introduced DISTINCT for bug fix 4429787/4506505
from psp_supercede_persons_gt
where person_id > 0;
SELECT fcp.concurrent_program_name
FROM fnd_concurrent_programs fcp,
fnd_concurrent_requests fcr
WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcr.request_id = l_request_id;
SELECT HUNDRED_PCENT_EFF_AT_PER_ASG, selection_match_level
FROM psp_report_templates_h
WHERE request_id = p_request_id;
l_selection_match_level varchar2(10);
Select nvl(max(request_id),p_request_id) into l_request_id from psp_report_templates_h prth where request_id < p_request_id
and payroll_action_id = p_pactid;
select min(object_id), max(object_id) into l_start_person, l_end_person from pay_temp_object_actions
where payroll_action_id =p_pactid and chunk_number = p_chunk_num;
l_selection_match_level);
fetch HUNDRED_PCENT_EFF_CSR into l_HUNDRED_PCENT_EFF_AT_PER_ASG, l_selection_match_level;
l_selection_match_level);
select parameter_value_2, parameter_value_3 into p_effort_start, p_effort_end from
psp_report_templates_h where request_id = l_request_id;
select criteria_value1 into l_element_set_id from psp_report_template_details_h where
criteria_lookup_code='EST' and request_id = l_request_id;
SELECT
person_id , ASSIGNMENT_ID,
PROJECT_ID, TASK_ID,
AWARD_ID, EXPENDITURE_ORGANIZATION_ID, EXPENDITURE_TYPE,
SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10,
SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15,
SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20,
SEGMENT21, SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25,
SEGMENT26, SEGMENT27, SEGMENT28, SEGMENT29, SEGMENT30,
SUM(distribution_amount) distribution_amount, MAX(distribution_date1) , max(distribution_date2)
bulk collect into
:det_person_id, :det_assignment_id, :det_project_id, :det_task_id , :det_award_id,
:det_exp_org_id,
:det_exp_type,
:det_segment1, :det_segment2, :det_segment3, :det_segment4, :det_segment5, :det_segment6,
:det_segment7, :det_segment8, :det_segment9,
:det_segment10, :det_segment11, :det_segment12,
:det_segment13, :det_segment14, :det_segment15,
:det_segment16, :det_segment17, :det_segment18,
:det_segment19, :det_segment20, :det_segment21,
:det_segment22 , :det_segment23, :det_segment24,
:det_segment25, :det_segment26, :det_segment27,
:det_segment28, :det_segment29, :det_segment30 ,
:det_dist_amount, :det_sch_st_date,
:det_sch_end_date
from (
SELECT
psl.person_id person_id, null ASSIGNMENT_ID,
null PROJECT_ID, null TASK_ID,
null AWARD_ID, null EXPENDITURE_ORGANIZATION_ID, null EXPENDITURE_TYPE, null SEGMENT1, null SEGMENT2, null SEGMENT3, null SEGMENT4,
null SEGMENT5, null SEGMENT6, null SEGMENT7, null SEGMENT8, null SEGMENT9,
null SEGMENT10, null SEGMENT11, null SEGMENT12, null SEGMENT13, null SEGMENT14,
Null SEGMENT15, null SEGMENT16, null SEGMENT17, null SEGMENT18, null SEGMENT19, null SEGMENT20,
Null SEGMENT21, null SEGMENT22, null SEGMENT23, null SEGMENT24, null SEGMENT25, null SEGMENT26, null SEGMENT27, null SEGMENT28, null SEGMENT29, null SEGMENT30,
/*
sum(decode(psl.dr_cr_flag,'||''''||'D'||''''||',nvl(pdnh.distribution_amount,nvl(ppg.distribution_amount, pal.distribution_amount)), nvl(-pdnh.distribution_amount, nvl(-ppg.distribution_amount,
-pal.distribution_amount)))) distribution_amount,
*/
decode(psl.dr_cr_flag,'||''''||'D'||''''||',nvl(pdnh.distribution_amount,nvl(ppg.distribution_amount, pal.distribution_amount)), nvl(-pdnh.distribution_amount, nvl(-ppg.distribution_amount,
-pal.distribution_amount))) distribution_amount,
null distribution_date1, null distribution_date2
FROM
psp_distribution_lines_history pdnh,
psp_summary_lines psl,
psp_Selected_persons_t pspt,
psp_pre_gen_dist_lines_history ppg,
psp_adjustment_lineS_history pal,
psp_payroll_sub_lines ppsl,
psp_payroll_lines ppl
WHERE psl.person_id = pspt.person_id
and pspt.request_id = :l_request_id
and pspt.person_id between :l_start_person and :l_end_person
and psl.summary_line_id = pdnh.summary_line_id(+)
and pdnh.payroll_sub_line_id= ppsl.payroll_sub_line_id(+) and
ppsl.payroll_line_id = ppl.payroll_line_id(+)
and psl.summary_line_id = ppg.summary_line_id(+)
and psl.summary_line_id = pal.summary_line_id(+)
AND psl.status_code = '||''''||'A'||''''||'
AND
( EXISTS
(select 1 from psp_element_set_members_v pesr where pesr.element_set_id = :l_element_set_id and
pesr.element_type_id = ppl.element_type_id )
OR EXISTS
(select 1 from psp_element_set_members_v pesr where pesr.element_set_id =
:l_element_set_id and pesr.element_type_id = pal.element_type_id)
OR EXISTS
(select 1 from psp_element_set_members_v pesr where pesr.element_set_id =
:l_element_set_id and pesr.element_type_id = ppg.element_type_id )
) AND
/* pspt.person_id not in (select person_id from psp_eff_reports pea where
pea.end_date >= :effort_start and pea.start_date <= :effort_end and
status_code in ('||''''||'N'||''''||','||''''||'A'||''''||')) AND */
((psl.source_type in ('||''''||'N'||''''||' , '|| ''''|| 'O' ||'''' ||' ) and
psl.summary_line_id = pdnh.summary_line_id
and pdnh.distribution_date between :effort_start and :effort_end
AND pdnh.reversal_entry_flag is NULL
AND pdnh.adjustment_batch_name is null
)
OR (psl.source_type=' ||''''|| 'P'||'''' || '
AND ppg.status_code ='||''''||'A' || ''''||'
and ppg.distribution_date between :effort_start and :effort_end
AND ppg.reversal_entry_flag is NULL
AND ppg.summary_line_id = psl.summary_line_id
AND ppg.adjustment_batch_name is null
)
OR (psl.source_type='||'''' || 'A' || ''''||'
and pal.distribution_date between :effort_start and :effort_end
AND pal.status_code = ' || ''''|| 'A' || ''''|| '
AND NVL(pal.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
|| ' AND pal.reversal_entry_flag is NULL
AND pal.summary_line_id = psl.summary_line_id
AND pal.adjustment_batch_name is null
))
/* Added for hospital effort report Change*/
UNION ALL
SELECT
psl.person_id person_id, null ASSIGNMENT_ID,
null PROJECT_ID, null TASK_ID,
null AWARD_ID, null EXPENDITURE_ORGANIZATION_ID, null EXPENDITURE_TYPE,
null SEGMENT1, null SEGMENT2, null SEGMENT3, null SEGMENT4, null SEGMENT5,
null SEGMENT6, null SEGMENT7, null SEGMENT8, null SEGMENT9, null SEGMENT10,
null SEGMENT11, null SEGMENT12, null SEGMENT13, null SEGMENT14, Null SEGMENT15,
null SEGMENT16, null SEGMENT17, null SEGMENT18, null SEGMENT19, null SEGMENT20,
Null SEGMENT21, null SEGMENT22, null SEGMENT23, null SEGMENT24, null SEGMENT25,
null SEGMENT26, null SEGMENT27, null SEGMENT28, null SEGMENT29, null SEGMENT30,
psl.distribution_amount distribution_amount, null , null
FROM psp_external_effort_lines psl,
psp_Selected_persons_t pspt
WHERE psl.person_id = pspt.person_id
and pspt.request_id = :l_request_id
AND pspt.person_id between :l_start_person and :l_end_person
/*
AND EXISTS (select 1
from psp_element_set_members_v pesr
where pesr.element_set_id = :l_element_set_id
and pesr.element_type_id = psl.element_type_id)
*/
AND psl.distribution_date between :effort_start and :effort_end
)
group by
person_id , ASSIGNMENT_ID,
PROJECT_ID, TASK_ID,
AWARD_ID, EXPENDITURE_ORGANIZATION_ID, EXPENDITURE_TYPE,
SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10,
SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15,
SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20,
SEGMENT21, SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25,
SEGMENT26, SEGMENT27, SEGMENT28, SEGMENT29, SEGMENT30
/*psl.person_id */
';
if l_selection_match_level = 'EMP' then
insert into psp_selected_persons_t (request_id, person_id)
select -1 * l_request_id, gt.person_id from psp_supercede_persons_gt gt
where gt.person_id > 0 ;
insert into psp_selected_persons_t (request_id, person_id, assignment_id)
select -1 * l_request_id, person_id, assignment_id
from psp_Eff_reports er,
psp_eff_report_details erd
where er.effort_Report_id = erd.effort_Report_id
and er.request_id = l_request_id
and er.status_code in ('N','A')
and er.person_id in (select person_id from psp_supercede_persons_gt where person_id > 0) ;
g_exec_string := replace(g_exec_string, 'pspt.person_id not in (select person_id from psp_eff_reports pea where',null);
' and pspt.person_id in (select person_id from psp_Eff_reports where request_id ='|| p_request_id||' and status_code in ( '||''''||'A'||''''||','||''''||'N'||''''||')) group by psl.person_id');
l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
g_exec_string:= replace(g_exec_string, ' psl.person_id = pspt.person_id', ' psl.person_id = pspt.person_id '||l_select_string);
SELECT NVL(max(effort_report_id),0) into min_effort_report_id from psp_eff_reports;
insert into psp_eff_reports(
effort_report_id,
status_code,
person_id,
object_version_number,
start_date,
end_date,
template_id,
request_id,
currency_code,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
business_group_id,
set_of_books_id
)
values
(
psp_effort_reports_s.nextval,
decode(p_supercede_mode , null, 'N', 'T'),
person_rec.array_person_id(i),
1,
p_effort_Start,
p_effort_end,
decode(p_supercede_mode, null, g_psp_template_id, -999),
l_request_id,
l_bg_currency_code,
sysdate,
fnd_global.user_id ,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
l_profile_bg_id,
l_profile_sob_id) returning effort_report_id bulk collect into person_rec.array_effort_report_id;
hr_utility.trace('psp_create_eff_reports--> After insert into eff '||person_rec.array_effort_report_id.count);
hr_utility.trace('psp_create_eff_reports--> before inserting into details ');
insert into psp_eff_report_details(
effort_report_detail_id,
effort_report_id,
object_version_number,
assignment_id,
GL_SEGMENT1,
GL_SEGMENT2,
GL_SEGMENT3,
GL_SEGMENT4,
GL_SEGMENT5,
GL_SEGMENT6,
GL_SEGMENT7,
GL_SEGMENT8,
GL_SEGMENT9,
GL_SEGMENT10,
GL_SEGMENT11,
GL_SEGMENT12,
GL_SEGMENT13,
GL_SEGMENT14,
GL_SEGMENT15,
GL_SEGMENT16,
GL_SEGMENT17,
GL_SEGMENT18,
GL_SEGMENT19,
GL_SEGMENT20,
GL_SEGMENT21,
GL_SEGMENT22,
GL_SEGMENT23,
GL_SEGMENT24,
GL_SEGMENT25,
GL_SEGMENT26,
GL_SEGMENT27,
GL_SEGMENT28,
GL_SEGMENT29,
GL_SEGMENT30,
project_id,
expenditure_organization_id,
expenditure_type,
task_id,
award_id,
actual_salary_amt,
payroll_percent,
schedule_start_date,
schedule_end_date,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
values
(
psp_eff_report_details_s.nextval,
1,
1,
det_assignment_id(i),
det_segment1(i),
det_segment2(i),
det_segment3(i),
det_segment4(i),
det_segment5(i),
det_segment6(i),
det_segment7(i),
det_segment8(i),
det_segment9(i),
det_segment10(i),
det_segment11(i),
det_segment12(i),
det_segment13(i),
det_segment14(i),
det_segment15(i),
det_segment16(i),
det_segment17(i),
det_segment18(i),
det_segment19(i),
det_segment20(i),
det_segment21(i),
det_segment22(i),
det_segment23(i),
det_segment24(i),
det_segment25(i),
det_segment26(i),
det_segment27(i),
det_segment28(i),
det_segment29(i),
det_segment30(i),
det_project_id(i),
det_exp_org_id(i) ,
det_expenditure_type(i),
det_task_id(i),
det_award_id(i),
det_distribution_amount(i),
0,
det_schedule_start_date(i),
det_schedule_end_date(i),
sysdate,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.user_id,
sysdate
) returning to_number(det_person_id(i)), effort_report_detail_id bulk collect into effort_det_lines_rec.person_id, effort_det_lines_rec.effort_report_detail_id;
update psp_eff_report_details set effort_report_id =
(select effort_report_id from psp_eff_reports where
person_id =effort_Det_lines_rec.person_id(i) and status_code = 'T'
and request_id = l_request_id ) where
effort_report_detail_id= effort_det_lines_rec.effort_report_detail_id(i) ;
update psp_eff_report_details set effort_report_id =
(select effort_report_id from psp_eff_reports where
person_id =effort_Det_lines_rec.person_id(i) and effort_report_id > min_effort_report_id ) where
effort_report_detail_id= effort_det_lines_rec.effort_report_detail_id(i) ;
hr_utility.trace('psp_create_eff_reports--> After update of psp_eff_report_detail ');
effort_Det_lines_rec.effort_report_detail_id.delete;
effort_det_lines_rec.person_id.delete;
/* delete the det_details arrays */
det_person_id.delete;
det_assignment_id.delete;
det_project_id.delete;
det_task_id.delete;
det_award_id.delete;
det_exp_org_id.delete;
det_expenditure_type.delete;
det_segment1.delete;
det_segment2.delete;
det_segment3.delete;
det_segment4.delete;
det_segment5.delete;
det_segment6.delete;
det_segment7.delete;
det_segment8.delete;
det_segment9.delete;
det_segment10.delete;
det_segment11.delete;
det_segment12.delete;
det_segment13.delete;
det_segment14.delete;
det_segment15.delete;
det_segment16.delete;
det_segment17.delete;
det_segment18.delete;
det_segment19.delete;
det_segment20.delete;
det_segment21.delete;
det_segment22.delete;
det_segment23.delete;
det_segment24.delete;
det_segment25.delete;
det_segment26.delete;
det_segment27.delete;
det_segment28.delete;
det_segment29.delete;
det_segment30.delete;
det_distribution_amount.delete;
det_schedule_start_date.delete;
det_schedule_end_date.delete;
select sum(actual_salary_amt), effort_report_id, assignment_id bulk collect
into person_rec.sum_tot, person_rec.array_effort_report_id, person_rec.array_assignment_id
from psp_eff_report_details where
effort_report_id > min_effort_report_id group by effort_report_id, assignment_id;
update psp_Eff_report_details set payroll_percent = decode(person_rec.sum_tot(i),0,0,round( ((actual_salary_amt * 100) / person_rec.sum_tot(i)),2))
where effort_report_id = person_rec.array_effort_report_id(i)
and assignment_id = person_rec.array_assignment_id(i);
person_rec.array_effort_report_id.delete;
person_rec.sum_tot.delete;
person_rec.array_assignment_id.delete;
select sum(payroll_percent), effort_report_id, assignment_id bulk collect
into person_rec.payroll_percent_tot, person_rec.array_effort_report_id, person_rec.array_assignment_id
from psp_eff_report_details where effort_report_id > min_effort_report_id group by effort_report_id , assignment_id;
update psp_eff_report_details set payroll_percent = payroll_percent + (100.00 - person_rec.payroll_percent_tot(i))
where effort_report_detail_id in (
select max(effort_report_detail_id)
from psp_eff_report_details
where effort_report_id = person_rec.array_effort_report_id(i)
and person_rec.payroll_percent_tot(i)<>0 and
assignment_id = person_rec.array_assignment_id(i)
) ;
select sum(actual_salary_amt), effort_report_id bulk collect into person_rec.sum_tot, person_rec.array_effort_report_id
from psp_eff_report_details where
effort_report_id > min_effort_report_id group by effort_report_id ;
-- update psp_Eff_report_details set payroll_percent = round(actual_salary_amt/person_rec.sum_tot(i),2)*100 where effort_report_id =
-- person_rec.array_effort_report_id(i);
update psp_Eff_report_details set payroll_percent = decode(person_rec.sum_tot(i),0,0,round( ((actual_salary_amt * 100) / person_rec.sum_tot(i)),2)) where effort_report_id =
person_rec.array_effort_report_id(i);
person_rec.array_effort_report_id.delete;
person_rec.sum_tot.delete;
select sum(payroll_percent), effort_report_id
bulk collect into person_rec.payroll_percent_tot, person_rec.array_effort_report_id
from psp_eff_report_details where effort_report_id > min_effort_report_id group by effort_report_id ;
update psp_eff_report_details set payroll_percent = payroll_percent + (100.00 - person_rec.payroll_percent_tot(i)) where
effort_report_detail_id in (select max(effort_report_detail_id) from psp_eff_report_details where
effort_report_id = person_rec.array_effort_report_id(i) and person_rec.payroll_percent_tot(i)<>0) ;
/* to update the last line in the case it exceeds 100 */
-- fnd_file.put_line(fnd_file.log,' after details update ');
update psp_eff_report_details set payroll_percent = payroll_percent + (100.00 - person_rec.payroll_percent_tot(i)) where
effort_report_detail_id in (select max(effort_report_detail_id) from psp_eff_report_details where
effort_report_id = person_rec.array_effort_report_id(i) and person_rec.payroll_percent_tot(i)<>0) ;
delete from psp_eff_reports per where effort_report_id > min_effort_report_id and
not exists (select 1 from psp_eff_report_details perd where
perd.effort_report_id = per.effort_report_id);
person_rec.array_effort_report_id.delete;
person_rec.payroll_percent_tot.delete;
psp_xmlgen.update_er_details(l_start_person ,
l_end_person ,
l_request_id,
l_retry_request_id,
l_return_status) ;
hr_utility.trace('psp_create_eff_reports--> After xmlgen.update_er_details');
psp_xmlgen.update_grouping_category(l_start_person, l_end_person, l_request_id, l_return_status) ;
hr_utility.trace('psp_create_eff_reports--> After xmlgen.update_grouping_category');
psp_eff_report_details_api.update_eff_report_details(p_validate , l_request_id,l_start_person, l_end_person, p_warning );
psp_xmlgen.update_er_person_xml(l_start_person ,
l_end_person ,
l_request_id,
l_retry_request_id,
l_return_status) ;
hr_utility.trace('psp_create_eff_reports--> After xmlgen.update_er_person_xml');
select distinct pspt.skip_reason, pspt.person_id, paf.assignment_number
from psp_selected_persons_t pspt,
per_all_assignments_f paf
where pspt.request_id = p_request_id
and pspt.person_id between p_start_person and p_end_person
and pspt.assignment_id = paf.assignment_id
and pspt.skip_reason is not null
and paf.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id);
Select max(request_id) into l_old_request_id from psp_report_templates_h prth where request_id < p_request_id and payroll_action_id = p_pact_id;
update psp_selected_persons_t
set skip_reason = 'ALREADY_EXISTS'
where person_id between p_start_person and p_end_person
and request_id = p_request_id
and person_id in
(select person_id from psp_eff_reports
where status_code in ('N', 'A')
and g_psp_effort_end >= start_date
and g_psp_effort_start <= end_date );
insert into psp_report_errors
(error_sequence_id, request_id, message_level,
source_id, error_message, retry_request_id, pdf_request_id,
source_name, parent_source_id, parent_source_name)
(select psp_report_errors_s.nextval , p_request_id,
'W', pspt.person_id , l_msg_str , p_retry_request_id, null, NULL, NULL, null
from psp_selected_persons_t pspt where pspt.person_id between p_start_person and p_end_person and
pspt.request_id = p_request_id and skip_reason is not null
AND rowid = (select min(rowid) from psp_selected_persons_t inner
where inner.request_id = pspt.request_id
AND inner.person_id = pspt.person_id ));
update psp_selected_persons_t
set skip_reason = 'ALREADY_EXISTS'
where person_id between p_start_person and p_end_person
and request_id = p_request_id
and (person_id, assignment_id) in
(select er.person_id, erd.assignment_id
from psp_eff_reports er, psp_eff_report_details erd
where er.status_code in ('N', 'A')
and er.effort_Report_id = erd.effort_report_id
and g_psp_effort_end >= er.start_date
and g_psp_effort_start <= er.end_date );
update psp_selected_persons_t
set skip_reason = 'ALREADY_EXISTS'
where person_id between p_start_person and p_end_person
and request_id = p_request_id
and person_id in
(select er.person_id
from psp_eff_reports er, psp_eff_report_details erd
where er.status_code in ('N', 'A')
and er.effort_Report_id = erd.effort_report_id
and g_psp_effort_end >= er.start_date
and g_psp_effort_start <= er.end_date
and erd.assignment_id is null);
update psp_selected_persons_t
set skip_reason = 'OTHER_ASG_SKIPPED'
where person_id between p_start_person and p_end_person
and request_id = p_request_id
and skip_reason is null
and person_id in
(select person_id
from psp_selected_persons_t
where person_id between p_start_person and p_end_person
and request_id = p_request_id
and skip_reason = 'ALREADY_EXISTS' );
insert into psp_report_errors
(error_sequence_id, request_id, message_level,
source_id, error_message, retry_request_id, pdf_request_id,
source_name, parent_source_id, parent_source_name)
(select psp_report_errors_s.nextval , p_request_id,
'W', skipped_asg_rec.person_id , l_msg_str , p_retry_request_id, null, NULL, NULL, null
from dual);
insert into psp_report_errors
(error_sequence_id, request_id, message_level,
source_id, error_message, retry_request_id, pdf_request_id,
source_name, parent_source_id, parent_source_name)
select psp_report_errors_s.nextval, p_request_id,'W',
pspt.person_id, l_msg_str, p_retry_request_id , null, NULL, NULL, NULL
from psp_selected_persons_t pspt where pspt.person_id between p_start_person
and p_end_person and
pspt.request_id = p_request_id and
pspt.person_id not in (select nvl(person_id,0) from psp_eff_reports where g_psp_effort_end >= start_date and
g_psp_effort_start <= end_date and status_code in ('N', 'A'))
and pspt.person_id not in (select nvl(source_id,0) from psp_report_errors where request_id = g_psp_request_id);
insert into psp_report_errors
(error_sequence_id, request_id, message_level,
source_id, error_message, retry_request_id, pdf_request_id,
source_name, parent_source_id, parent_source_name)
select psp_report_errors_s.nextval, p_request_id,'W',
pspt.person_id , l_msg_str, p_retry_request_id , null, NULL, NULL, NULL
from psp_selected_persons_t pspt where pspt.person_id between p_start_person and p_end_person and
pspt.request_id = p_request_id and
exists (select nvl(person_id,0) from psp_adjustment_lines where effective_date between g_psp_effort_start
and g_psp_effort_end ) and pspt.person_id not in (select source_id from psp_report_errors where request_id=p_request_id) and
pspt.person_id not in (select nvl(person_id, 0) from psp_eff_reports where g_psp_effort_end>=start_date and
g_psp_effort_start <= end_date);
l_sqlerrm := 'Error inserting in psp_report_errors '|| substr(sqlerrm,1,200);
select 1, per.person_id, perd.project_id, NVL(perd.project_name,'NOTFOUND'), perd.task_id, NVL(perd.task_name,'NOTFOUND'),
perd.award_id, NVL(perd.AWARD_SHORT_NAME,'NOTFOUND'), EXPENDITURE_ORGANIZATION_ID ,NVL(perd.exp_org_name,'NOTFOUND'),
perd.expenditure_type
from psp_eff_reports per ,
psp_eff_report_details perd
where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
AND per.request_id = p_request_id
AND person_id between p_start_person and p_end_person
AND(( PROJECT_ID is not null AND PROJECT_NAME is NULL)
OR ( TASK_ID is not null AND TASK_NAME is NULL)
OR ( AWARD_ID is not null AND AWARD_SHORT_NAME is NULL)
OR ( EXPENDITURE_ORGANIZATION_ID is not null AND EXP_ORG_NAME is NULL))
UNION ALL
select 2, per.person_id, perd.project_id, perd.project_name, perd.task_id, perd.task_name,
perd.award_id, perd.AWARD_SHORT_NAME, EXPENDITURE_ORGANIZATION_ID ,perd.exp_org_name,
perd.expenditure_type
from psp_eff_reports per ,
psp_eff_report_details perd
where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
and per.request_id = p_request_id
AND person_id between p_start_person and p_end_person
AND PROJECT_ID is NULL
AND TASK_ID is NULL
AND AWARD_ID is NULL
AND EXPENDITURE_ORGANIZATION_ID is NULL
AND EXPENDITURE_TYPE is NULL
AND gl_sum_criteria_segment_name IS NULL;
SELECT SUBSTR(report_template_code, 6, 3) layout_type
FROM psp_report_templates_h prth
WHERE prth.request_id = p_request_id;
SELECT DISTINCT per.person_id,
perd.award_id,
perd.award_number
FROM psp_eff_reports per,
psp_eff_report_details perd
WHERE per.request_id = p_request_id
AND per.effort_report_id = perd.effort_report_id
AND per.person_id BETWEEN p_start_person AND p_end_person
--AND perd.award_id IS NOT NULL Commented as part of UVA fix 4537063
AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
SELECT DISTINCT per.person_id,
perd.project_id,
perd.project_number
FROM psp_eff_reports per,
psp_eff_report_details perd
WHERE per.request_id = p_request_id
AND per.effort_report_id = perd.effort_report_id
AND per.person_id BETWEEN p_start_person AND p_end_person
AND perd.project_id IS NOT NULL
AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
SELECT DISTINCT per.person_id,
perd.project_number,
perd.task_id,
perd.task_number
FROM psp_eff_reports per,
psp_eff_report_details perd
WHERE per.request_id = p_request_id
AND per.effort_report_id = perd.effort_report_id
AND per.person_id BETWEEN p_start_person AND p_end_person
AND perd.task_id IS NOT NULL
AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
SELECT DISTINCT per.person_id,
ppa.segment1,
perd.task_id,
perd.task_number
FROM psp_eff_reports per,
psp_eff_report_details perd,
pa_tasks pt,
pa_projects_all ppa
WHERE per.request_id = p_request_id
AND per.effort_report_id = perd.effort_report_id
AND pt.task_id = perd.task_id
AND ppa.project_id = pt.project_id
AND per.person_id BETWEEN p_start_person AND p_end_person
AND perd.task_id IS NOT NULL
AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
SELECT COUNT(1)
FROM psp_eff_reports per,
psp_eff_report_details perd
WHERE per.request_id = p_request_id
AND per.person_id BETWEEN p_start_person AND p_end_person
AND perd.task_id IS NOT NULL
AND perd.project_id IS NULL
AND ROWNUM = 1;