[Home] [Help]
216: cursor wf_item_key_cur(p_request_id integer) is
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
295: RAISE fnd_api.g_exc_unexpected_error;
296: end if;
297:
298: forall k in 1..wf_rname_array.count
299: update psp_eff_report_approvals
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
451: if (funcmode = 'RUN') then
452: l_request_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
453: itemkey => itemkey,
454: aname => 'REQUEST_ID');
455: delete psp_eff_report_approvals
456: where effort_report_detail_id in
457: (select effort_report_detail_id
458: from psp_eff_report_details
459: where effort_report_id in
485: WHERE fl.file_id IN (SELECT fdl.media_id
486: FROM fnd_attached_documents fad,
487: fnd_documents_vl fdl
488: WHERE fad.pk1_value IN (SELECT wf_item_key
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
566: l_login_id number;
567:
568: cursor role_name_cur is
569: select wf_role_name
570: from psp_eff_report_approvals
571: where wf_item_key = itemkey
572: and rownum = 1
573: and approver_order_num = 1;
574:
576: select distinct erd.ame_transaction_id
577: from psp_eff_report_details erd
578: where erd.effort_report_detail_id in
579: (select era.effort_report_detail_id
580: from psp_eff_report_approvals era
581: where wf_item_key = itemkey
582: and approval_status = 'A');
583:
584: --- same level unapproved ER details
584: --- same level unapproved ER details
585: cursor same_approval_level_cur(p_approver_order_num in integer) is
586: select wf_role_name,
587: wf_role_display_name
588: from psp_eff_report_approvals
589: where wf_item_key = itemkey
590: and approver_order_num = p_approver_order_num
591: and approval_status = 'P'
592: and rownum = 1;
671: where er.status_code = 'N'
672: and er.effort_report_id in
673: (select erd.effort_report_id
674: from psp_eff_report_details erd,
675: psp_eff_report_approvals era
676: where era.effort_report_detail_id = erd.effort_report_detail_id
677: and era.wf_item_key = itemkey)
678: and not exists
679: (select 1
676: where era.effort_report_detail_id = erd.effort_report_detail_id
677: and era.wf_item_key = itemkey)
678: and not exists
679: (select 1
680: from psp_eff_report_approvals era,
681: psp_eff_report_details erd
682: where era.effort_report_detail_id = erd.effort_report_detail_id
683: and erd.effort_report_id = er.effort_report_id
684: and era.approval_status <> 'A');
718: else
719: l_wf_rname := l_next_approver(1).name;
720: l_role_display_name := l_next_approver(1).display_name;
721: result := 'COMPLETE:FOUND';
722: insert into psp_eff_report_approvals
723: (effort_report_approval_id,
724: effort_report_detail_id,
725: wf_role_name,
726: wf_orig_system_id,
734: creation_date,
735: created_by,
736: wf_role_display_name,
737: object_version_number)
738: select psp_eff_report_approvals_s.nextval,
739: erd.effort_report_detail_id,
740: l_next_approver(1).name,
741: l_next_approver(1).orig_system_id,
742: l_next_approver(1).orig_system,
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
762: if l_next_approver(1).approver_order_number > 1 then
763: update psp_eff_report_approvals A1
764: set (A1.actual_cost_share, A1.overwritten_effort_percent, comments,
765: /* Add DF Columns for Hospital Effort report */
766: pera_information1, pera_information2, pera_information3, pera_information4, pera_information5,
767: pera_information6, pera_information7, pera_information8, pera_information9, pera_information10,
776: pera_information11, pera_information12, pera_information13, pera_information14, pera_information15,
777: eff_information1, eff_information2, eff_information3,eff_information4, eff_information5,
778: eff_information6,eff_information7, eff_information8 , eff_information9,eff_information10,
779: eff_information11, eff_information12, eff_information13, eff_information14, eff_information15
780: from psp_eff_report_approvals A2
781: where A1.effort_report_detail_id = A2.effort_report_detail_id
782: /* Bug 5235725: Replacing l_next_approver(1).approver_order_number with l_approver_order_num.
783: In case of reassign a notification function ame_api2.getNextApprovers4 resturns nextApproversOut.approver_order_number with an
784: incremented value while in our system (Effort rporting) we do not increment the approver_order_number in case of reassign */
810: where er.status_code = 'N'
811: and er.effort_report_id in
812: (select erd.effort_report_id
813: from psp_eff_report_details erd,
814: psp_eff_report_approvals era
815: where era.effort_report_detail_id = erd.effort_report_detail_id
816: and era.wf_item_key = itemkey)
817: and not exists
818: (select 1
815: where era.effort_report_detail_id = erd.effort_report_detail_id
816: and era.wf_item_key = itemkey)
817: and not exists
818: (select 1
819: from psp_eff_report_approvals era,
820: psp_eff_report_details erd
821: where era.effort_report_detail_id = erd.effort_report_detail_id
822: and erd.effort_report_id = er.effort_report_id
823: and era.approval_status <> 'A');
921: (select effort_report_id
922: from psp_eff_report_details
923: where effort_report_detail_id in
924: (select effort_report_detail_id
925: from psp_eff_report_approvals
926: where wf_item_key = itemkey
927: and wf_role_name = l_rname));
928:
929: --- one person can have more than one approver.
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
938: and erd.effort_report_id = er.effort_report_id
939: and era.approval_status in ( 'A','P')
945: (select effort_report_id
946: from psp_eff_report_details
947: where effort_report_detail_id in
948: (select effort_report_detail_id
949: from psp_eff_report_approvals
950: where wf_item_key = itemkey
951: and wf_role_name = l_rname)))
952: and era.wf_role_name = fu.user_name -- Bug 6641216
953: and trunc(sysdate) between trunc(fu.start_date) and nvl(trunc(fu.end_date),trunc(sysdate)) -- Bug 6641216
962:
963: cursor get_txn_id_cur is
964: select distinct ame_transaction_id
965: from psp_eff_report_details erd,
966: psp_eff_report_approvals era
967: where erd.effort_report_detail_id = era.effort_report_detail_id
968: and era.wf_item_key = itemkey
969: and era.approval_status = 'P'
970: and era.wf_role_name = l_rname
973:
974: --- same level unapproved ER details
975: cursor same_approval_level_cur(p_approver_order_num in integer) is
976: select wf_role_name
977: from psp_eff_report_approvals
978: where wf_item_key = itemkey
979: and approver_order_num = p_approver_order_num
980: and approval_status = 'P'
981: and rownum = 1;
1030: hr_utility.trace('er_workflow -->6 ');
1031: end loop;
1032:
1033: hr_utility.trace('er_workflow -->7 ');
1034: update psp_eff_report_approvals era
1035: set era.approval_status = 'S',
1036: era.response_date = sysdate,
1037: era.last_update_date = sysdate,
1038: era.last_update_login = fnd_global.login_id,
1047: and erd.effort_report_detail_id = era.effort_report_detail_id
1048: and er.status_code = 'S' );
1049:
1050:
1051: update psp_eff_report_approvals era
1052: set era.approval_status = 'R',
1053: era.response_date = sysdate,
1054: era.last_update_date = sysdate,
1055: era.last_update_login = fnd_global.login_id,
1147:
1148: cursor get_orig_system is
1149: select wf_orig_system_id,
1150: wf_orig_system
1151: from psp_eff_report_approvals
1152: where wf_item_key = itemkey
1153: and approval_status = 'P'
1154: and wf_role_name = l_rname;
1155:
1155:
1156: cursor get_txn_id_cur is
1157: select distinct ame_transaction_id
1158: from psp_eff_report_details erd,
1159: psp_eff_report_approvals era
1160: where erd.effort_report_detail_id = era.effort_report_detail_id
1161: and era.wf_item_key = itemkey
1162: and era.approval_status = 'A'
1163: and era.wf_role_name = l_rname
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,
1202: era.last_update_date = sysdate,
1203: era.last_update_login = fnd_global.login_id,
1215: and erd.effort_report_detail_id = era.effort_report_detail_id
1216: and er.status_code = 'S' );
1217:
1218:
1219: update psp_eff_report_approvals era
1220: set era.approval_status = 'A',
1221: era.response_date = SYSDATE,
1222: era.last_update_date = sysdate,
1223: era.last_update_login = fnd_global.login_id,
1292:
1293: cursor get_orig_system is
1294: select wf_orig_system_id,
1295: wf_orig_system
1296: from psp_eff_report_approvals
1297: where wf_item_key = itemkey
1298: and approval_status = 'P'
1299: and wf_role_name = l_rname;
1300:
1300:
1301: cursor get_txn_id_cur is
1302: select distinct ame_transaction_id
1303: from psp_eff_report_details erd,
1304: psp_eff_report_approvals era
1305: where erd.effort_report_detail_id = era.effort_report_detail_id
1306: and era.wf_item_key = itemkey
1307: and era.approval_status = 'P'
1308: and era.wf_role_name = l_rname2
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
1350: AND perd.effort_report_detail_id = prea.effort_report_detail_id
1351: AND per.request_id = l_request_id
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
1363: AND per.person_id = l_person_id
1364: AND per.start_date = l_start_date
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
1378: AND perd.assignment_id = l_assignment_id
1379: AND per.start_date = l_start_date
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
1394: AND per.person_id = l_person_id
1395: AND per.start_date = l_start_date
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
1408: AND perd.assignment_id = l_assignment_id
1409: AND per.start_date = l_start_date
1517: hr_utility.trace('post notification...Transfer . Forwardee='||l_rname2||' nid ='||l_nid);
1518: forward_rec.orig_system := l_orig_system2;
1519: forward_rec.orig_system_id := l_orig_system_id2;
1520: forward_rec.approval_status:= ame_util.notifiedStatus;
1521: update psp_eff_report_approvals
1522: set wf_role_name = l_rname2,
1523: wf_role_display_name = l_role_display_name2,
1524: wf_orig_system = l_orig_system2,
1525: wf_orig_system_id = l_orig_system_id2
1571: hr_utility.trace('funcmode, role name from RUN = '||funcmode||','||l_rname);
1572: end if; ---funcmode
1573:
1574: -- BUG 4334816 START
1575: -- New code added to capture the Notification id in psp_eff_report_approvals table
1576: l_nid := wf_engine.context_nid;
1577: l_rname := wf_engine.GetItemAttrText(itemtype => itemtype,
1578: itemkey => itemkey,
1579: aname => 'APPROVER_ROLE_NAME');
1577: l_rname := wf_engine.GetItemAttrText(itemtype => itemtype,
1578: itemkey => itemkey,
1579: aname => 'APPROVER_ROLE_NAME');
1580:
1581: update psp_eff_report_approvals set NOTIFICATION_ID = l_nid
1582: where WF_ITEM_KEY = itemkey
1583: AND WF_ROLE_NAME = l_rname;
1584:
1585: -- BUG 4334816 END
1631: from psp_eff_report_details
1632: where request_id = l_request_id
1633: and effort_report_detail_id in
1634: (select effort_report_detail_id
1635: from psp_eff_report_approvals
1636: where wf_item_key = itemkey
1637: and wf_role_name = l_rname));*/
1638: begin
1639: if funcmode = 'RUN' then