[Home] [Help]
102:
103: cursor check_valid_prj is
104: select er.person_id
105: from psp_eff_report_details erd,
106: psp_eff_reports er
107: where erd.project_id is null
108: and erd.effort_Report_id = er.effort_report_id
109: and er.request_id = p_request_id;
110:
118:
119: cursor check_non_sponsered_prj is
120: select er.person_id
121: from psp_eff_report_details erd,
122: psp_eff_reports er
123: where erd.award_id is null
124: and erd.effort_Report_id = er.effort_report_id
125: and er.request_id = p_request_id;
126:
128: cursor er_cur_gpi_pmg_tmg is
129: select min(erd.effort_report_detail_id),
130: erd.investigator_person_id,
131: null
132: from psp_eff_reports er,
133: psp_eff_report_details erd
134: where er.effort_report_id = erd.effort_report_id
135: and er.status_code = 'N'
136: and er.request_id = p_request_id
141:
142: cursor er_cur_emp is select min(effort_report_detail_id),
143: person_id,
144: null
145: from psp_eff_reports er,
146: psp_eff_report_details erd
147: where er.effort_report_id = erd.effort_report_id
148: and er.status_code = 'N'
149: and er.request_id = p_request_id
154: cursor er_cur_sup_1 is select min(erd.effort_report_detail_id),
155: min(er.person_id),
156: asg.supervisor_id
157: from per_assignments_f asg,
158: psp_eff_reports er,
159: psp_eff_report_details erd
160: where asg.person_id = er.person_id
161: and er.status_code = 'N'
162: and asg.assignment_type ='E'
858: l_user_id,
859: p_approver_display_name,
860: 1
861: from psp_eff_report_details erd,
862: psp_eff_reports er
863: where erd.effort_report_id = er.effort_report_id
864: and er.request_id = p_request_id
865: and nvl(investigator_person_id,-999) = nvl(p_break_attribute,-999)
866: and er.person_id between p_start_person and p_end_person
899: l_user_id,
900: p_approver_display_name,
901: 1
902: from psp_eff_report_details erd,
903: psp_eff_reports er
904: where erd.effort_report_id = er.effort_report_id
905: and er.request_id = p_request_id
906: and nvl(er.person_id,-999) = nvl(p_break_attribute,-999)
907: and er.person_id between p_start_person and p_end_person
939: l_user_id,
940: p_approver_display_name,
941: 1
942: from psp_eff_report_details erd,
943: psp_eff_reports er,
944: per_all_assignments_f asg
945: where erd.effort_report_id = er.effort_report_id
946: and er.request_id = p_request_id
947: and er.person_id between p_start_person and p_end_person
983: select er.person_id,
984: erd.task_number,
985: erd.assignment_number
986: from psp_eff_report_details erd,
987: psp_eff_reports er
988: where erd.effort_report_id = er.effort_report_id
989: and er.request_id = p_request_id
990: and nvl(erd.task_id,-9999) = nvl(p_group_attribute, -9999)
991: and er.person_id between p_start_person and p_end_person
998: select er.person_id,
999: erd.project_number,
1000: erd.assignment_number
1001: from psp_eff_report_details erd,
1002: psp_eff_reports er
1003: where erd.effort_report_id = er.effort_report_id
1004: and er.request_id = p_request_id
1005: and nvl(erd.project_id,-9999) = nvl(p_group_attribute, -9999)
1006: and er.person_id between p_start_person and p_end_person
1014: select er.person_id,
1015: erd.award_number,
1016: erd.assignment_number
1017: from psp_eff_report_details erd,
1018: psp_eff_reports er
1019: where erd.effort_report_id = er.effort_report_id
1020: and er.request_id = p_request_id
1021: and nvl(erd.award_id,-9999) = nvl(p_group_attribute, -9999)
1022: and er.person_id between p_start_person and p_end_person
1025: erd.assignment_number;
1026:
1027: cursor get_supervisor_1_emps is
1028: select distinct er.person_id
1029: from psp_eff_reports er,
1030: psp_eff_report_details erd
1031: where er.effort_report_id = erd.effort_report_id
1032: and erd.ame_transaction_id = p_ame_transaction_id
1033: and er.person_id between p_start_person and p_end_person
1298: end if;
1299: l_counter := 0;
1300: end if;
1301: hr_utility.trace( 'psperamb--> Workflow approval not reqd .. exiting' );
1302: update psp_eff_reports
1303: set status_code = 'A'
1304: where status_code = 'N'
1305: and request_id = p_request_id
1306: and person_id between p_start_person and p_end_person;
1316: sql_string := 'select dtls.effort_report_detail_id,
1317: rep.person_id '
1318: ||make_select||
1319: ' from psp_eff_report_details dtls,
1320: psp_eff_reports rep
1321: where rep.effort_report_id = dtls.effort_report_id
1322: and rep.status_code ='|| ''''||'N'||'''' || '
1323: and rep.request_id = :1
1324: and rep.person_id between :1 and :2 ';
1383: l_err_mesg, p_retry_request_id);
1384: begin
1385: select er.person_id
1386: into l_error_out
1387: from psp_eff_reports er,
1388: psp_eff_report_details erd
1389: where er.effort_report_id = erd.effort_report_id
1390: and er.request_id = p_request_id
1391: and erd.effort_report_detail_id = er_rec.effort_report_detail_id;
1483: set ame_transaction_id = l_ame_txn_id
1484: where nvl(investigator_person_id,-999) = nvl(break_rec.array_break_attribute(l_counter),-999)
1485: and effort_report_id in
1486: (select effort_report_id
1487: from psp_eff_reports er
1488: where er.request_id = p_request_id
1489: and er.person_id between p_start_person and p_end_person
1490: and er.status_code = 'N');
1491: --- Employee or Employee/Supervisor
1500: update psp_eff_report_details erd
1501: set erd.ame_transaction_id = l_ame_txn_id
1502: where erd.effort_report_id in
1503: (select er.effort_report_id
1504: from psp_eff_reports er,
1505: per_all_assignments_f asg
1506: where er.request_id = p_request_id
1507: and er.person_id between p_start_person and p_end_person
1508: and er.status_code = 'N'
1659: hr_utility.trace(l_sqlerrm);
1660: begin
1661: select er.person_id
1662: into l_error_out
1663: from psp_eff_reports er,
1664: psp_eff_report_details erd
1665: where er.effort_report_id = erd.effort_report_id
1666: and er.request_id = p_request_id
1667: and erd.effort_report_detail_id = break_rec.array_detail_id(l_counter);