[Home] [Help]
17: and message_level = 'E'; --- count fatal errors that require
18: --- to stop the process.
19: cursor count_er is
20: select count(*)
21: from psp_eff_reports
22: where status_code IN ('N', 'A')
23: and request_id = l_request_id;
24:
25: l_er_count integer;
158:
159: open check_pre_approved_cur;
160: fetch check_pre_approved_cur into l_flag;
161: if check_pre_approved_cur%found then
162: update psp_eff_reports
163: set status_code = 'A',
164: last_update_date = sysdate,
165: last_update_login = fnd_global.login_id,
166: last_updated_by = fnd_global.user_id
217: select wf_role_name,
218: psp_wf_item_key_s.nextval
219: from (select era.wf_role_name
220: from psp_eff_report_approvals era,
221: psp_eff_reports er,
222: psp_eff_report_details erd
223: where erd.effort_report_id = er.effort_report_id
224: and erd.effort_report_detail_id = era.effort_report_detail_id
225: and era.approval_status = 'P'
300: set wf_item_key = wf_ikey_array(k)
301: where wf_role_name = wf_rname_array(k)
302: and effort_report_detail_id in
303: (select erd.effort_report_detail_id
304: from psp_eff_reports er,
305: psp_eff_report_details erd
306: where erd.effort_report_id = er.effort_report_id
307: and er.request_id = l_request_id
308: and er.status_code = 'N');
457: (select effort_report_detail_id
458: from psp_eff_report_details
459: where effort_report_id in
460: (select effort_report_id
461: from psp_eff_reports
462: where request_id = l_request_id));
463:
464: delete psp_eff_report_details
465: where effort_report_id in
463:
464: delete psp_eff_report_details
465: where effort_report_id in
466: (select effort_report_id
467: from psp_eff_reports
468: where request_id = l_request_id);
469:
470: delete psp_eff_reports
471: where request_id = l_request_id;
466: (select effort_report_id
467: from psp_eff_reports
468: where request_id = l_request_id);
469:
470: delete psp_eff_reports
471: where request_id = l_request_id;
472:
473: delete psp_report_errors
474: where request_id = l_request_id;
489: FROM psp_eff_report_approvals pera
490: WHERE pera.effort_report_detail_id IN (SELECT perd.effort_report_detail_id
491: FROM psp_eff_report_details perd
492: WHERE perd.effort_report_id IN (SELECT per.effort_report_id
493: FROM psp_eff_reports per
494: WHERE per.request_id = l_request_id)))
495: AND fdl.document_id = fad.document_id
496: AND fad.entity_name = 'ERDETAILS');
497:
646: close role_name_cur;
647: l_role_display_name := wf_directory.GetRoleDisplayName(l_wf_rname);
648: result := 'COMPLETE:FOUND';
649: elsif l_approval_type in ('PMG', 'TMG', 'GPI') then
650: update psp_eff_reports er
651: set er.status_code = 'A',
652: er.last_update_date = sysdate,
653: er.last_update_login = fnd_global.login_id,
654: er.last_updated_by = fnd_global.user_id
737: from psp_eff_report_details erd
738: where erd.ame_transaction_id = ame_txn_id_array(i)
739: and erd.effort_report_id in
740: (select er.effort_report_id
741: from psp_eff_reports er
742: where er.request_id = l_request_id
743: and er.status_code = 'N');
744: hr_utility.trace(' ER workflow -> order number = '||l_next_approver(1).approver_order_number);
745: -- copy the previous approvers overwrites to the new approver
771: -- where A1.approver_order_num = l_next_approver(1).approver_order_number and
772: where A1.approver_order_num = l_approver_order_num and
773: A1.effort_report_detail_id in
774: (select erd.effort_report_detail_id
775: from psp_eff_reports er,
776: psp_eff_report_details erd
777: where er.request_id = l_request_id
778: and er.effort_report_id = erd.effort_report_id
779: and erd.ame_transaction_id = ame_txn_id_array(i)
785: end if;
786: i := i +1;
787: end loop;
788: if l_temp_not_found = 'Y' then
789: update psp_eff_reports er
790: set er.status_code = 'A',
791: er.last_update_date = sysdate,
792: er.last_update_login = fnd_global.login_id,
793: er.last_updated_by = fnd_global.user_id
899: l_recipnt_role varchar2(300);
900:
901: cursor effort_report_id_cur is
902: select effort_report_id
903: from psp_eff_reports
904: where effort_report_id in
905: (select effort_report_id
906: from psp_eff_report_details
907: where effort_report_detail_id in
913: --- one person can have more than one approver.
914: --- partial approval by 2 approvers.
915: cursor past_approvers_cur is
916: select distinct era.wf_role_name
917: from psp_eff_reports er,
918: psp_eff_report_details erd,
919: psp_eff_report_approvals era,
920: fnd_user fu -- Bug 6641216
921: where era.effort_report_detail_id = erd.effort_report_detail_id
923: and era.approval_status in ( 'A','P')
924: and er.request_id = l_request_id
925: and er.effort_report_id in
926: (select effort_report_id
927: from psp_eff_reports
928: where effort_report_id in
929: (select effort_report_id
930: from psp_eff_report_details
931: where effort_report_detail_id in
1025: and era.wf_role_name = l_rname
1026: and exists
1027: ( select erd.effort_report_detail_id
1028: from psp_eff_report_details erd,
1029: psp_eff_reports er
1030: where er.effort_report_id = erd.effort_report_id
1031: and erd.effort_report_detail_id = era.effort_report_detail_id
1032: and er.status_code = 'S' );
1033:
1042: and era.wf_role_name = l_rname
1043: and not exists
1044: ( select erd.effort_report_detail_id
1045: from psp_eff_report_details erd,
1046: psp_eff_reports er
1047: where er.effort_report_id = erd.effort_report_id
1048: and erd.effort_report_detail_id = era.effort_report_detail_id
1049: and er.status_code = 'S' );
1050:
1056: if effort_report_id_cur%notfound then
1057: close effort_report_id_cur;
1058: exit;
1059: end if;
1060: update psp_eff_reports
1061: set status_code = 'R',
1062: last_update_date = sysdate,
1063: last_update_login = fnd_global.login_id,
1064: last_updated_by = fnd_global.user_id
1161: close get_orig_system;
1162:
1163:
1164: -- Intorduced for Supercedence to set status_code = 'S'
1165: -- when the psp_eff_reports gets superceded
1166:
1167: update psp_eff_report_approvals era
1168: set era.approval_status = 'S',
1169: era.response_date = SYSDATE,
1177: and era.approval_status = 'P'
1178: and exists
1179: ( select erd.effort_report_detail_id
1180: from psp_eff_report_details erd,
1181: psp_eff_reports er
1182: where er.effort_report_id = erd.effort_report_id
1183: and erd.effort_report_detail_id = era.effort_report_detail_id
1184: and er.status_code = 'S' );
1185:
1197: and era.approval_status = 'P'
1198: and not exists
1199: ( select erd.effort_report_detail_id
1200: from psp_eff_report_details erd,
1201: psp_eff_reports er
1202: where er.effort_report_id = erd.effort_report_id
1203: and erd.effort_report_detail_id = era.effort_report_detail_id
1204: and er.status_code = 'S' );
1205:
1307: CURSOR get_person_Assignment_list_csr IS
1308: SELECT distinct prth.hundred_pcent_eff_at_per_asg, per.start_date,
1309: per.end_date, per.person_id, perd.assignment_id, per.full_name, perd.assignment_number
1310: FROM psp_report_templates_h prth,
1311: psp_eff_reports per,
1312: psp_eff_report_details perd,
1313: psp_eff_report_approvals prea
1314: WHERE prth.request_id = per.request_id
1315: AND per.effort_report_id = perd.effort_report_id
1320: AND prea.approver_order_num = l_approver_order_num;
1321:
1322: CURSOR is_person_last_approver_csr IS
1323: SELECT 1
1324: FROM psp_eff_reports per,
1325: psp_eff_report_details perd,
1326: psp_eff_report_approvals prea
1327: WHERE per.effort_report_id = perd.effort_report_id
1328: AND perd.effort_report_detail_id = prea.effort_report_detail_id
1335: AND per.status_code IN ('N','A');
1336:
1337: CURSOR is_asg_last_approver_csr IS
1338: SELECT 1
1339: FROM psp_eff_reports per,
1340: psp_eff_report_details perd,
1341: psp_eff_report_approvals prea
1342: WHERE per.effort_report_id = perd.effort_report_id
1343: AND perd.effort_report_detail_id = prea.effort_report_detail_id
1351:
1352:
1353: CURSOR person_percent_csr IS
1354: SELECT sum(nvl(overwritten_effort_percent,payroll_percent))
1355: FROM psp_eff_reports per,
1356: psp_eff_report_details perd,
1357: psp_eff_report_approvals prea
1358: WHERE per.effort_report_id = perd.effort_report_id
1359: AND perd.effort_report_detail_id = prea.effort_report_detail_id
1365: AND per.status_code IN ('N','A');
1366:
1367: CURSOR assignment_percent_csr IS
1368: SELECT sum(nvl(overwritten_effort_percent,payroll_percent))
1369: FROM psp_eff_reports per,
1370: psp_eff_report_details perd,
1371: psp_eff_report_approvals prea
1372: WHERE per.effort_report_id = perd.effort_report_id
1373: AND perd.effort_report_detail_id = prea.effort_report_detail_id
1584: WHERE pdf_request_id = l_pdf_request_id;
1585:
1586: /*cursor get_person_id is
1587: select person_id
1588: from psp_eff_reports
1589: where effort_report_id in
1590: (select effort_report_id
1591: from psp_eff_report_details
1592: where request_id = l_request_id
1988: l_frp_role_display varchar2(100);
1989:
1990: cursor check_approval_count is
1991: select count(*)
1992: from psp_eff_reports
1993: where status_code = 'A'
1994: and request_id = l_request_id;
1995:
1996: l_count integer;