APPS.PSP_ER_AME dependencies on PSP_EFF_REPORT_DETAILS

Line 105: from psp_eff_report_details erd,

101: where request_id = p_request_id;
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;

Line 121: from psp_eff_report_details erd,

117: l_err_person_id number;
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;

Line 133: psp_eff_report_details erd

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
137: and er.person_id between p_start_person and p_end_person

Line 146: psp_eff_report_details erd

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
150: and er.person_id between p_start_person and p_end_person

Line 159: psp_eff_report_details erd

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'
163: and trunc(er.end_date) between asg.effective_start_date and

Line 861: from psp_eff_report_details erd,

857: sysdate,
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)

Line 902: from psp_eff_report_details erd,

898: sysdate,
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)

Line 942: from psp_eff_report_details erd,

938: sysdate,
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

Line 977: from psp_eff_Report_details

973:
974:
975: cursor er_PTA_cur(p_effort_Report_detail_id integer) is
976: select project_id, task_id, award_id
977: from psp_eff_Report_details
978: where effort_report_Detail_id = p_effort_Report_Detail_id;
979:
980: er_pta_rec er_pta_cur%rowtype;
981:

Line 986: from psp_eff_report_details erd,

982: cursor task_manager_error is
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)

Line 1001: from psp_eff_report_details erd,

997: cursor project_manager_error is
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)

Line 1017: from psp_eff_report_details erd,

1013: p_group_attribute integer) is
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)

Line 1030: psp_eff_report_details erd

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
1034: and er.request_id = p_request_id;

Line 1319: ' from psp_eff_report_details dtls,

1315: g_approver_basis := lower(g_approver_basis);
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

Line 1349: update psp_eff_report_details

1345: hr_utility.trace ('psperamb-->size, AME TXN Id before ame call-102 length, txn_id ='||length( l_ame_txn_id)||' '||l_ame_Txn_id);
1346: if g_approver_basis is not null then
1347: if break_group then
1348: forall i in 1..t_erd_id.count
1349: update psp_eff_report_details
1350: set ame_transaction_id = l_ame_txn_id
1351: where effort_report_detail_id = t_erd_id(i);
1352: ame_api2.getNextApprovers4(applicationidIn => 8403,
1353: transactiontypeIn => 'PSP-ER-APPROVAL',

Line 1361: update psp_eff_report_details

1357: nextApproversOut=> l_next_approver);
1358: l_counter := 0;
1359: end if;
1360: else
1361: update psp_eff_report_details
1362: set ame_transaction_id = l_ame_txn_id
1363: where effort_report_detail_id = er_rec.effort_report_detail_id;
1364:
1365: ame_api2.getNextApprovers4(applicationidIn => 8403,

Line 1388: psp_eff_report_details erd

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;
1392: l_sqlerrm := substr(l_sqlerrm,1,2000);

Line 1406: update psp_eff_report_details

1402: hr_utility.trace ('psperamb-->exception when others: '||length( l_ame_txn_id)||' '||l_ame_Txn_id);
1403: ---raise;
1404: p_return_status := fnd_api.g_ret_sts_unexp_error;
1405: end;
1406: update psp_eff_report_details
1407: set ame_transaction_id = l_ame_txn_id
1408: where effort_report_detail_id = er_rec.effort_report_detail_id;
1409: i := 1;
1410: loop

Line 1482: update psp_eff_report_details

1478:
1479: if approval_type_rec.approval_type = 'GPI'
1480: or approval_type_rec.approval_type = 'PMG'
1481: or approval_type_rec.approval_type = 'TMG' then
1482: update psp_eff_report_details
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

Line 1493: update psp_eff_report_details

1489: and er.person_id between p_start_person and p_end_person
1490: and er.status_code = 'N');
1491: --- Employee or Employee/Supervisor
1492: elsif approval_type_rec.approval_type in ('EMP', 'ESU') then
1493: update psp_eff_report_details
1494: set ame_transaction_id = l_ame_txn_id
1495: where effort_report_id in
1496: (select erd.effort_report_id
1497: from psp_eff_report_details erd

Line 1497: from psp_eff_report_details erd

1493: update psp_eff_report_details
1494: set ame_transaction_id = l_ame_txn_id
1495: where effort_report_id in
1496: (select erd.effort_report_id
1497: from psp_eff_report_details erd
1498: where erd.effort_report_detail_id = break_rec.array_detail_id(l_counter));
1499: elsif approval_type_rec.approval_type in ('SUP') then
1500: update psp_eff_report_details erd
1501: set erd.ame_transaction_id = l_ame_txn_id

Line 1500: update psp_eff_report_details erd

1496: (select erd.effort_report_id
1497: from psp_eff_report_details erd
1498: where erd.effort_report_detail_id = break_rec.array_detail_id(l_counter));
1499: elsif approval_type_rec.approval_type in ('SUP') then
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,

Line 1664: psp_eff_report_details erd

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);
1668: l_sqlerrm := substr(l_sqlerrm,1,2000);