[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;
655: where er.status_code = 'N'
656: and er.effort_report_id in
657: (select erd.effort_report_id
658: from psp_eff_report_details erd,
659: psp_eff_report_approvals era
660: where era.effort_report_detail_id = erd.effort_report_detail_id
661: and era.wf_item_key = itemkey)
662: and not exists
663: (select 1
660: where era.effort_report_detail_id = erd.effort_report_detail_id
661: and era.wf_item_key = itemkey)
662: and not exists
663: (select 1
664: from psp_eff_report_approvals era,
665: psp_eff_report_details erd
666: where era.effort_report_detail_id = erd.effort_report_detail_id
667: and erd.effort_report_id = er.effort_report_id
668: and era.approval_status <> 'A');
702: else
703: l_wf_rname := l_next_approver(1).name;
704: l_role_display_name := l_next_approver(1).display_name;
705: result := 'COMPLETE:FOUND';
706: insert into psp_eff_report_approvals
707: (effort_report_approval_id,
708: effort_report_detail_id,
709: wf_role_name,
710: wf_orig_system_id,
718: creation_date,
719: created_by,
720: wf_role_display_name,
721: object_version_number)
722: select psp_eff_report_approvals_s.nextval,
723: erd.effort_report_detail_id,
724: l_next_approver(1).name,
725: l_next_approver(1).orig_system_id,
726: l_next_approver(1).orig_system,
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
746: if l_next_approver(1).approver_order_number > 1 then
747: update psp_eff_report_approvals A1
748: set (A1.actual_cost_share, A1.overwritten_effort_percent, comments,
749: /* Add DF Columns for Hospital Effort report */
750: pera_information1, pera_information2, pera_information3, pera_information4, pera_information5,
751: pera_information6, pera_information7, pera_information8, pera_information9, pera_information10,
760: pera_information11, pera_information12, pera_information13, pera_information14, pera_information15,
761: eff_information1, eff_information2, eff_information3,eff_information4, eff_information5,
762: eff_information6,eff_information7, eff_information8 , eff_information9,eff_information10,
763: eff_information11, eff_information12, eff_information13, eff_information14, eff_information15
764: from psp_eff_report_approvals A2
765: where A1.effort_report_detail_id = A2.effort_report_detail_id
766: /* Bug 5235725: Replacing l_next_approver(1).approver_order_number with l_approver_order_num.
767: In case of reassign a notification function ame_api2.getNextApprovers4 resturns nextApproversOut.approver_order_number with an
768: incremented value while in our system (Effort rporting) we do not increment the approver_order_number in case of reassign */
794: where er.status_code = 'N'
795: and er.effort_report_id in
796: (select erd.effort_report_id
797: from psp_eff_report_details erd,
798: psp_eff_report_approvals era
799: where era.effort_report_detail_id = erd.effort_report_detail_id
800: and era.wf_item_key = itemkey)
801: and not exists
802: (select 1
799: where era.effort_report_detail_id = erd.effort_report_detail_id
800: and era.wf_item_key = itemkey)
801: and not exists
802: (select 1
803: from psp_eff_report_approvals era,
804: psp_eff_report_details erd
805: where era.effort_report_detail_id = erd.effort_report_detail_id
806: and erd.effort_report_id = er.effort_report_id
807: and era.approval_status <> 'A');
905: (select effort_report_id
906: from psp_eff_report_details
907: where effort_report_detail_id in
908: (select effort_report_detail_id
909: from psp_eff_report_approvals
910: where wf_item_key = itemkey
911: and wf_role_name = l_rname));
912:
913: --- one person can have more than one approver.
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
922: and erd.effort_report_id = er.effort_report_id
923: and era.approval_status in ( 'A','P')
929: (select effort_report_id
930: from psp_eff_report_details
931: where effort_report_detail_id in
932: (select effort_report_detail_id
933: from psp_eff_report_approvals
934: where wf_item_key = itemkey
935: and wf_role_name = l_rname)))
936: and era.wf_role_name = fu.user_name -- Bug 6641216
937: and trunc(sysdate) between trunc(fu.start_date) and nvl(trunc(fu.end_date),trunc(sysdate)) -- Bug 6641216
946:
947: cursor get_txn_id_cur is
948: select distinct ame_transaction_id
949: from psp_eff_report_details erd,
950: psp_eff_report_approvals era
951: where erd.effort_report_detail_id = era.effort_report_detail_id
952: and era.wf_item_key = itemkey
953: and era.approval_status = 'P'
954: and era.wf_role_name = l_rname
957:
958: --- same level unapproved ER details
959: cursor same_approval_level_cur(p_approver_order_num in integer) is
960: select wf_role_name
961: from psp_eff_report_approvals
962: where wf_item_key = itemkey
963: and approver_order_num = p_approver_order_num
964: and approval_status = 'P'
965: and rownum = 1;
1014: hr_utility.trace('er_workflow -->6 ');
1015: end loop;
1016:
1017: hr_utility.trace('er_workflow -->7 ');
1018: update psp_eff_report_approvals era
1019: set era.approval_status = 'S',
1020: era.response_date = sysdate,
1021: era.last_update_date = sysdate,
1022: era.last_update_login = fnd_global.login_id,
1031: and erd.effort_report_detail_id = era.effort_report_detail_id
1032: and er.status_code = 'S' );
1033:
1034:
1035: update psp_eff_report_approvals era
1036: set era.approval_status = 'R',
1037: era.response_date = sysdate,
1038: era.last_update_date = sysdate,
1039: era.last_update_login = fnd_global.login_id,
1131:
1132: cursor get_orig_system is
1133: select wf_orig_system_id,
1134: wf_orig_system
1135: from psp_eff_report_approvals
1136: where wf_item_key = itemkey
1137: and approval_status = 'P'
1138: and wf_role_name = l_rname;
1139:
1139:
1140: cursor get_txn_id_cur is
1141: select distinct ame_transaction_id
1142: from psp_eff_report_details erd,
1143: psp_eff_report_approvals era
1144: where erd.effort_report_detail_id = era.effort_report_detail_id
1145: and era.wf_item_key = itemkey
1146: and era.approval_status = 'A'
1147: and era.wf_role_name = l_rname
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,
1170: era.last_update_date = sysdate,
1171: era.last_update_login = fnd_global.login_id,
1183: and erd.effort_report_detail_id = era.effort_report_detail_id
1184: and er.status_code = 'S' );
1185:
1186:
1187: update psp_eff_report_approvals era
1188: set era.approval_status = 'A',
1189: era.response_date = SYSDATE,
1190: era.last_update_date = sysdate,
1191: era.last_update_login = fnd_global.login_id,
1260:
1261: cursor get_orig_system is
1262: select wf_orig_system_id,
1263: wf_orig_system
1264: from psp_eff_report_approvals
1265: where wf_item_key = itemkey
1266: and approval_status = 'P'
1267: and wf_role_name = l_rname;
1268:
1268:
1269: cursor get_txn_id_cur is
1270: select distinct ame_transaction_id
1271: from psp_eff_report_details erd,
1272: psp_eff_report_approvals era
1273: where erd.effort_report_detail_id = era.effort_report_detail_id
1274: and era.wf_item_key = itemkey
1275: and era.approval_status = 'P'
1276: and era.wf_role_name = l_rname2
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
1316: AND perd.effort_report_detail_id = prea.effort_report_detail_id
1317: AND per.request_id = l_request_id
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
1329: AND per.person_id = l_person_id
1330: AND per.start_date = l_start_date
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
1344: AND perd.assignment_id = l_assignment_id
1345: AND per.start_date = l_start_date
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
1360: AND per.person_id = l_person_id
1361: AND per.start_date = l_start_date
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
1374: AND perd.assignment_id = l_assignment_id
1375: AND per.start_date = l_start_date
1477: hr_utility.trace('post notification...Transfer . Forwardee='||l_rname2||' nid ='||l_nid);
1478: forward_rec.orig_system := l_orig_system2;
1479: forward_rec.orig_system_id := l_orig_system_id2;
1480: forward_rec.approval_status:= ame_util.notifiedStatus;
1481: update psp_eff_report_approvals
1482: set wf_role_name = l_rname2,
1483: wf_role_display_name = l_role_display_name2,
1484: wf_orig_system = l_orig_system2,
1485: wf_orig_system_id = l_orig_system_id2
1531: hr_utility.trace('funcmode, role name from RUN = '||funcmode||','||l_rname);
1532: end if; ---funcmode
1533:
1534: -- BUG 4334816 START
1535: -- New code added to capture the Notification id in psp_eff_report_approvals table
1536: l_nid := wf_engine.context_nid;
1537: l_rname := wf_engine.GetItemAttrText(itemtype => itemtype,
1538: itemkey => itemkey,
1539: aname => 'APPROVER_ROLE_NAME');
1537: l_rname := wf_engine.GetItemAttrText(itemtype => itemtype,
1538: itemkey => itemkey,
1539: aname => 'APPROVER_ROLE_NAME');
1540:
1541: update psp_eff_report_approvals set NOTIFICATION_ID = l_nid
1542: where WF_ITEM_KEY = itemkey
1543: AND WF_ROLE_NAME = l_rname;
1544:
1545: -- BUG 4334816 END
1591: from psp_eff_report_details
1592: where request_id = l_request_id
1593: and effort_report_detail_id in
1594: (select effort_report_detail_id
1595: from psp_eff_report_approvals
1596: where wf_item_key = itemkey
1597: and wf_role_name = l_rname));*/
1598: begin
1599: if funcmode = 'RUN' then