[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:
662: close role_name_cur;
663: l_role_display_name := wf_directory.GetRoleDisplayName(l_wf_rname);
664: result := 'COMPLETE:FOUND';
665: elsif l_approval_type in ('PMG', 'TMG', 'GPI') then
666: update psp_eff_reports er
667: set er.status_code = 'A',
668: er.last_update_date = sysdate,
669: er.last_update_login = fnd_global.login_id,
670: er.last_updated_by = fnd_global.user_id
753: from psp_eff_report_details erd
754: where erd.ame_transaction_id = ame_txn_id_array(i)
755: and erd.effort_report_id in
756: (select er.effort_report_id
757: from psp_eff_reports er
758: where er.request_id = l_request_id
759: and er.status_code = 'N');
760: hr_utility.trace(' ER workflow -> order number = '||l_next_approver(1).approver_order_number);
761: -- copy the previous approvers overwrites to the new approver
787: -- where A1.approver_order_num = l_next_approver(1).approver_order_number and
788: where A1.approver_order_num = l_approver_order_num and
789: A1.effort_report_detail_id in
790: (select erd.effort_report_detail_id
791: from psp_eff_reports er,
792: psp_eff_report_details erd
793: where er.request_id = l_request_id
794: and er.effort_report_id = erd.effort_report_id
795: and erd.ame_transaction_id = ame_txn_id_array(i)
801: end if;
802: i := i +1;
803: end loop;
804: if l_temp_not_found = 'Y' then
805: update psp_eff_reports er
806: set er.status_code = 'A',
807: er.last_update_date = sysdate,
808: er.last_update_login = fnd_global.login_id,
809: er.last_updated_by = fnd_global.user_id
915: l_recipnt_role varchar2(300);
916:
917: cursor effort_report_id_cur is
918: select effort_report_id
919: from psp_eff_reports
920: where effort_report_id in
921: (select effort_report_id
922: from psp_eff_report_details
923: where effort_report_detail_id in
929: --- one person can have more than one approver.
930: --- partial approval by 2 approvers.
931: cursor past_approvers_cur is
932: select distinct era.wf_role_name
933: from psp_eff_reports er,
934: psp_eff_report_details erd,
935: psp_eff_report_approvals era,
936: fnd_user fu -- Bug 6641216
937: where era.effort_report_detail_id = erd.effort_report_detail_id
939: and era.approval_status in ( 'A','P')
940: and er.request_id = l_request_id
941: and er.effort_report_id in
942: (select effort_report_id
943: from psp_eff_reports
944: where effort_report_id in
945: (select effort_report_id
946: from psp_eff_report_details
947: where effort_report_detail_id in
1041: and era.wf_role_name = l_rname
1042: and exists
1043: ( select erd.effort_report_detail_id
1044: from psp_eff_report_details erd,
1045: psp_eff_reports er
1046: where er.effort_report_id = erd.effort_report_id
1047: and erd.effort_report_detail_id = era.effort_report_detail_id
1048: and er.status_code = 'S' );
1049:
1058: and era.wf_role_name = l_rname
1059: and not exists
1060: ( select erd.effort_report_detail_id
1061: from psp_eff_report_details erd,
1062: psp_eff_reports er
1063: where er.effort_report_id = erd.effort_report_id
1064: and erd.effort_report_detail_id = era.effort_report_detail_id
1065: and er.status_code = 'S' );
1066:
1072: if effort_report_id_cur%notfound then
1073: close effort_report_id_cur;
1074: exit;
1075: end if;
1076: update psp_eff_reports
1077: set status_code = 'R',
1078: last_update_date = sysdate,
1079: last_update_login = fnd_global.login_id,
1080: last_updated_by = fnd_global.user_id
1193: close get_orig_system;
1194:
1195:
1196: -- Intorduced for Supercedence to set status_code = 'S'
1197: -- when the psp_eff_reports gets superceded
1198:
1199: update psp_eff_report_approvals era
1200: set era.approval_status = 'S',
1201: era.response_date = SYSDATE,
1209: and era.approval_status = 'P'
1210: and exists
1211: ( select erd.effort_report_detail_id
1212: from psp_eff_report_details erd,
1213: psp_eff_reports er
1214: where er.effort_report_id = erd.effort_report_id
1215: and erd.effort_report_detail_id = era.effort_report_detail_id
1216: and er.status_code = 'S' );
1217:
1229: and era.approval_status = 'P'
1230: and not exists
1231: ( select erd.effort_report_detail_id
1232: from psp_eff_report_details erd,
1233: psp_eff_reports er
1234: where er.effort_report_id = erd.effort_report_id
1235: and erd.effort_report_detail_id = era.effort_report_detail_id
1236: and er.status_code = 'S' );
1237:
1341: CURSOR get_person_Assignment_list_csr IS
1342: SELECT distinct prth.hundred_pcent_eff_at_per_asg, per.start_date,
1343: per.end_date, per.person_id, perd.assignment_id, per.full_name, perd.assignment_number
1344: FROM psp_report_templates_h prth,
1345: psp_eff_reports per,
1346: psp_eff_report_details perd,
1347: psp_eff_report_approvals prea
1348: WHERE prth.request_id = per.request_id
1349: AND per.effort_report_id = perd.effort_report_id
1354: AND prea.approver_order_num = l_approver_order_num;
1355:
1356: CURSOR is_person_last_approver_csr IS
1357: SELECT 1
1358: FROM psp_eff_reports per,
1359: psp_eff_report_details perd,
1360: psp_eff_report_approvals prea
1361: WHERE per.effort_report_id = perd.effort_report_id
1362: AND perd.effort_report_detail_id = prea.effort_report_detail_id
1369: AND per.status_code IN ('N','A');
1370:
1371: CURSOR is_asg_last_approver_csr IS
1372: SELECT 1
1373: FROM psp_eff_reports per,
1374: psp_eff_report_details perd,
1375: psp_eff_report_approvals prea
1376: WHERE per.effort_report_id = perd.effort_report_id
1377: AND perd.effort_report_detail_id = prea.effort_report_detail_id
1385:
1386:
1387: CURSOR person_percent_csr IS
1388: SELECT sum(nvl(overwritten_effort_percent,payroll_percent)),sum(actual_salary_amt) -- 14653352 : Added sum(actual_salary_amt)
1389: FROM psp_eff_reports per,
1390: psp_eff_report_details perd,
1391: psp_eff_report_approvals prea
1392: WHERE per.effort_report_id = perd.effort_report_id
1393: AND perd.effort_report_detail_id = prea.effort_report_detail_id
1399: AND per.status_code IN ('N','A');
1400:
1401: CURSOR assignment_percent_csr IS
1402: SELECT sum(nvl(overwritten_effort_percent,payroll_percent)),sum(actual_salary_amt) -- 14653352 : Added sum(actual_salary_amt)
1403: FROM psp_eff_reports per,
1404: psp_eff_report_details perd,
1405: psp_eff_report_approvals prea
1406: WHERE per.effort_report_id = perd.effort_report_id
1407: AND perd.effort_report_detail_id = prea.effort_report_detail_id
1624: WHERE pdf_request_id = l_pdf_request_id;
1625:
1626: /*cursor get_person_id is
1627: select person_id
1628: from psp_eff_reports
1629: where effort_report_id in
1630: (select effort_report_id
1631: from psp_eff_report_details
1632: where request_id = l_request_id
2028: l_frp_role_display varchar2(100);
2029:
2030: cursor check_approval_count is
2031: select count(*)
2032: from psp_eff_reports
2033: where status_code = 'A'
2034: and request_id = l_request_id;
2035:
2036: l_count integer;