[Home] [Help]
2251: ,resultout OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
2252: l_number_of_approvers NUMBER := 0;
2253: l_approver_loop_counter NUMBER := 0;
2254: l_approver_item_attr_name VARCHAR2(30);
2255: l_approver_user_name pa_wf_ntf_performers.user_name%TYPE; ----VARCHAR2(240); Commented for bug 3267790
2256: l_prev_approver_user_name wf_users.name%TYPE; ----VARCHAR2(240); Commented for bug 3267790 as this takes the value from wf_users
2257: l_approver_type VARCHAR2(30);
2258: l_assignment_id NUMBER;
2259: l_wf_startedby_uname fnd_user.user_name%type;
2364:
2365: -- Need to set the current user as the current approver
2366: -- and set the flag to 'N' for the rest
2367: --
2368: UPDATE pa_wf_ntf_performers
2369: SET current_approver_flag =
2370: (DECODE(user_name,l_approver_user_name,'Y','N'))
2371: WHERE item_type = itemtype
2372: AND item_key = itemkey
2509: ,aname => 'NUMBER_OF_APPRVL_NF_RECIPIENTS'
2510: ,avalue => l_number_of_recipients
2511: );
2512: -- Now populate the Comments fields, which would have
2513: -- been stored in the pa_wf_ntf_performers table
2514: IF (l_apprvl_item_type IS NOT NULL AND l_apprvl_item_key
2515: IS NOT NULL ) THEN
2516: Set_approver_comments (p_apprvl_item_type => l_apprvl_item_type
2517: ,p_apprvl_item_key => l_apprvl_item_key
2541:
2542: l_number_of_apprvl_nf_rects NUMBER := 0;
2543: l_approval_nf_loop_counter NUMBER := 0;
2544: l_ntfy_rect_item_attr_name VARCHAR2(30);
2545: l_ntfy_apprvl_recipient_name pa_wf_ntf_performers.user_name%TYPE; ---VARCHAR2(240); Changed for bug 3267790
2546: l_ntfy_apprvl_rect_type VARCHAR2(30);
2547: l_assignment_id NUMBER := 0;
2548:
2549: BEGIN
2746: ,aname => 'NUMBER_OF_REJECT_NF_RECIPIENTS'
2747: ,avalue => l_number_of_recipients
2748: );
2749: -- Now populate the Comments fields, which would have
2750: -- been stored in the pa_wf_ntf_performers table
2751: IF (l_apprvl_item_type IS NOT NULL AND l_apprvl_item_key
2752: IS NOT NULL ) THEN
2753: Set_approver_comments (p_apprvl_item_type => l_apprvl_item_type
2754: ,p_apprvl_item_key => l_apprvl_item_key
2777:
2778: l_number_of_reject_nf_rects NUMBER := 0;
2779: l_rejection_nf_loop_counter NUMBER := 0;
2780: l_ntfy_rect_item_attr_name VARCHAR2(30);
2781: l_ntfy_reject_recipient_name pa_wf_ntf_performers.user_name%type; ----- VARCHAR2(240);Changed for bug 3267790
2782: l_ntfy_reject_rect_type VARCHAR2(30);
2783: l_assignment_id NUMBER := 0;
2784:
2785: BEGIN
2995: ,aname => 'NUMBER_OF_CANCEL_NF_RECIPIENTS'
2996: ,avalue => l_number_of_recipients
2997: );
2998: -- Now populate the Comments fields, which would have
2999: -- been stored in the pa_wf_ntf_performers table
3000: IF (l_apprvl_item_type IS NOT NULL AND l_apprvl_item_key
3001: IS NOT NULL ) THEN
3002: Set_approver_comments (p_apprvl_item_type => l_apprvl_item_type
3003: ,p_apprvl_item_key => l_apprvl_item_key
3027:
3028: l_number_of_cancel_nf_rects NUMBER := 0;
3029: l_cancellation_nf_loop_counter NUMBER := 0;
3030: l_ntfy_rect_item_attr_name VARCHAR2(30);
3031: l_ntfy_cancel_recipient_name pa_wf_ntf_performers.user_name%TYPE; ----- VARCHAR2(240);Changed for bug 3267790
3032: l_ntfy_cancel_rect_type VARCHAR2(30);
3033: l_assignment_id NUMBER := 0;
3034:
3035: BEGIN
3555: l_comment VARCHAR2(4000); --VARCHAR2(2000); Bug 7459451 : Changed the length to accomodate max length returned by wf_engine.getItemAttrText
3556: l_approver_user_name wf_users.name%type; ----- VARCHAR2(240);Changed for bug 3267790
3557: /* Modified length from 30 to 100 for bug 3148857 */
3558: l_assignment_id NUMBER;
3559: l_wf_context_user pa_wf_ntf_performers.user_name%TYPE;
3560: l_reassignee_user_name pa_wf_ntf_performers.user_name%TYPE;
3561: l_object_id1 pa_wf_ntf_performers.object_id1%TYPE;
3562: l_object_id2 pa_wf_ntf_performers.object_id2%TYPE;
3563: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3556: l_approver_user_name wf_users.name%type; ----- VARCHAR2(240);Changed for bug 3267790
3557: /* Modified length from 30 to 100 for bug 3148857 */
3558: l_assignment_id NUMBER;
3559: l_wf_context_user pa_wf_ntf_performers.user_name%TYPE;
3560: l_reassignee_user_name pa_wf_ntf_performers.user_name%TYPE;
3561: l_object_id1 pa_wf_ntf_performers.object_id1%TYPE;
3562: l_object_id2 pa_wf_ntf_performers.object_id2%TYPE;
3563: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3564:
3557: /* Modified length from 30 to 100 for bug 3148857 */
3558: l_assignment_id NUMBER;
3559: l_wf_context_user pa_wf_ntf_performers.user_name%TYPE;
3560: l_reassignee_user_name pa_wf_ntf_performers.user_name%TYPE;
3561: l_object_id1 pa_wf_ntf_performers.object_id1%TYPE;
3562: l_object_id2 pa_wf_ntf_performers.object_id2%TYPE;
3563: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3564:
3565: CURSOR get_reassignee IS
3558: l_assignment_id NUMBER;
3559: l_wf_context_user pa_wf_ntf_performers.user_name%TYPE;
3560: l_reassignee_user_name pa_wf_ntf_performers.user_name%TYPE;
3561: l_object_id1 pa_wf_ntf_performers.object_id1%TYPE;
3562: l_object_id2 pa_wf_ntf_performers.object_id2%TYPE;
3563: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3564:
3565: CURSOR get_reassignee IS
3566: SELECT USER_NAME
3559: l_wf_context_user pa_wf_ntf_performers.user_name%TYPE;
3560: l_reassignee_user_name pa_wf_ntf_performers.user_name%TYPE;
3561: l_object_id1 pa_wf_ntf_performers.object_id1%TYPE;
3562: l_object_id2 pa_wf_ntf_performers.object_id2%TYPE;
3563: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3564:
3565: CURSOR get_reassignee IS
3566: SELECT USER_NAME
3567: FROM pa_wf_ntf_performers
3563: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3564:
3565: CURSOR get_reassignee IS
3566: SELECT USER_NAME
3567: FROM pa_wf_ntf_performers
3568: WHERE user_name = l_wf_context_user
3569: AND wf_type_code = 'ASSIGNMENT_APPROVAL'
3570: AND item_type = itemtype
3571: AND item_key = itemkey;
3571: AND item_key = itemkey;
3572:
3573: CURSOR get_objectids IS
3574: SELECT object_id1, object_id2
3575: FROM pa_wf_ntf_performers
3576: WHERE wf_type_code = 'ASSIGNMENT_APPROVAL'
3577: AND item_type = itemtype
3578: AND item_key = itemkey
3579: AND current_approver_flag = 'Y';
3594: ( itemtype => itemtype
3595: , itemkey => itemkey
3596: , aname => 'ASSIGNMENT_ID'
3597: );
3598: UPDATE pa_wf_ntf_performers
3599: SET approver_comments = substr(l_comment,1,255)
3600: WHERE item_type = itemtype
3601: AND item_key = itemkey
3602: AND object_id1 = l_assignment_id
3632: OPEN get_objectids;
3633: FETCH get_objectids INTO l_object_id1, l_object_id2;
3634: CLOSE get_objectids;
3635:
3636: INSERT INTO pa_wf_ntf_performers (WF_TYPE_CODE, ITEM_TYPE, ITEM_KEY,
3637: OBJECT_ID1, OBJECT_ID2, USER_NAME,
3638: USER_TYPE, CURRENT_APPROVER_FLAG)
3639: VALUES ('ASSIGNMENT_APPROVAL', itemtype, itemkey,
3640: l_object_id1, l_object_id2, l_wf_context_user,
3643: END IF;
3644:
3645:
3646: --Set current_approver_flag to 'N' for the previous current_approver
3647: UPDATE pa_wf_ntf_performers
3648: SET current_approver_flag = (DECODE(user_name,l_wf_context_user,'Y','N'))
3649: WHERE wf_type_code = 'ASSIGNMENT_APPROVAL'
3650: AND item_type = itemtype
3651: AND item_key = itemkey
3691: ,avalue => l_prev_approver_user_name);
3692:
3693: /*
3694:
3695: INSERT INTO pa_wf_ntf_performers (WF_TYPE_CODE, ITEM_TYPE, ITEM_KEY,
3696: OBJECT_ID1, OBJECT_ID2, USER_NAME,
3697: USER_TYPE, CURRENT_APPROVER_FLAG)
3698: VALUES ('ASSIGNMENT_APPROVAL', itemtype, itemkey,
3699: 1, 2, l_wf_context_user,
3956: IF p_in_performers_tbl.EXISTS(1) THEN
3957: FOR i IN 1..p_in_performers_tbl.COUNT LOOP
3958: IF wf_directory.UserActive
3959: (p_in_performers_tbl(i).User_Name) THEN
3960: INSERT INTO pa_wf_ntf_performers (
3961: Wf_Type_Code,Item_Type,
3962: Item_Key,object_id1,
3963: object_id2,User_Name,User_Type,
3964: Routing_Order,Current_Approver_flag
3991: x_performer_type OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
3992: IS
3993: CURSOR l_get_next_performer_csr
3994: IS
3995: SELECT User_Name , User_Type FROM pa_wf_ntf_performers
3996: WHERE wf_type_code = p_wf_type_code
3997: AND item_type = p_item_type
3998: AND item_key = p_item_key
3999: AND routing_order = p_routing_order
3998: AND item_key = p_item_key
3999: AND routing_order = p_routing_order
4000: AND object_id1 = p_object_id1;
4001:
4002: l_user_name pa_wf_ntf_performers.user_name%TYPE;
4003:
4004: BEGIN
4005:
4006: OPEN l_get_next_performer_csr ;
4041: p_assignment_id NUMBER )
4042: IS
4043: CURSOR l_get_approver_comments_csr IS
4044: SELECT user_Name,approver_comments
4045: FROM pa_wf_ntf_performers
4046: WHERE item_type = p_apprvl_item_type
4047: AND item_key = p_apprvl_item_key
4048: AND object_id1 = p_assignment_id
4049: AND approver_comments IS NOT NULL;
4384: AND entity_key2 = to_char(p_assignment_id)
4385: AND wf_type_code = 'ASSIGNMENT_APPROVAL';
4386:
4387:
4388: DELETE FROM pa_wf_ntf_performers
4389: WHERE wf_type_code in ('ASSIGNMENT_APPROVAL','REJECTION_FYI','APPROVAL_FYI','CANCELLATION_FYI')
4390: AND object_id1 = p_assignment_id;
4391:
4392: END Delete_Assignment_WF_Records;
4459: l_err_code NUMBER := 0;
4460: l_err_stage VARCHAR2(2000);
4461: l_err_stack VARCHAR2(2000);
4462:
4463: l_ntfy_apprvl_recipient_name pa_wf_ntf_performers.user_name%TYPE; --- Commented for 3267790 fnd_user.user_name%TYPE; --used to set NTFY_APPRVL_RECIPIENT_NAME attribute
4464: l_prev_user fnd_user.user_name%TYPE; --used to set FORWARDED_FROM attribute
4465: l_number_of_assignments NUMBER;
4466: l_mass_approve_url VARCHAR2(2000); --Mass Approve page URL
4467: l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); -- 5345171
4510: -----------------------------------
4511: BEGIN
4512: SELECT user_name
4513: INTO l_ntfy_apprvl_recipient_name
4514: FROM pa_wf_ntf_performers
4515: WHERE group_id = p_group_id
4516: AND approver_group_id = p_approver_group_id
4517: AND routing_order = p_routing_order
4518: AND rownum = 1;
4540: --------------------------------------------
4541: BEGIN
4542: SELECT count(object_id1)
4543: INTO l_number_of_assignments
4544: FROM pa_wf_ntf_performers pf,
4545: pa_project_assignments asmt
4546: WHERE pf.group_id = p_group_id
4547: AND pf.approver_group_id = p_approver_group_id
4548: AND pf.routing_order = p_routing_order
5541:
5542: BEGIN
5543: SELECT object_id1
5544: INTO l_assignment_id
5545: FROM pa_wf_ntf_performers
5546: WHERE group_id = p_group_id
5547: AND rownum = 1;
5548: EXCEPTION
5549: WHEN OTHERS THEN
5853: * 1. Staffing Manager FYI for the mass txn
5854: * 2. Resource Manager FYI for the mass txn
5855: * 3. Project Manager FYI for the mass txn
5856: * The logic involves calling the client extension for every
5857: * assignment and populating pa_wf_ntf_performers table
5858: * Notifications are then sent to recipients from the pa_wf_ntf_performers table
5859: */
5860: PROCEDURE process_mgr_fyi_notification
5861: ( p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
5854: * 2. Resource Manager FYI for the mass txn
5855: * 3. Project Manager FYI for the mass txn
5856: * The logic involves calling the client extension for every
5857: * assignment and populating pa_wf_ntf_performers table
5858: * Notifications are then sent to recipients from the pa_wf_ntf_performers table
5859: */
5860: PROCEDURE process_mgr_fyi_notification
5861: ( p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
5862: ,p_project_id IN NUMBER := FND_API.G_MISS_NUM
5924: CURSOR Resource_Manager IS
5925: SELECT distinct res.manager_id
5926: FROM pa_resources_denorm res,
5927: pa_project_assignments asgn,
5928: pa_wf_ntf_performers ntf,
5929: fnd_user fnd
5930: WHERE ntf.group_id = p_group_id
5931: AND asgn.assignment_id = l_assignment_id
5932: AND asgn.assignment_id = ntf.object_id1
5945: /* Commenting this for performance tuning Bug#2499051
5946: (select pa_security_pvt.get_menu_id('PA_PRM_RES_PRMRY_CONTACT') menu_id
5947: from dual) temp, */
5948: pa_project_assignments asgn,
5949: pa_wf_ntf_performers ntf,
5950: wf_roles wf,
5951: per_all_people_f per
5952: WHERE ntf.group_id = p_group_id
5953: AND asgn.assignment_id = l_assignment_id
5975:
5976: CURSOR get_managers IS
5977: SELECT distinct user_name,
5978: user_type
5979: FROM pa_wf_ntf_performers
5980: WHERE wf_type_code = 'MASS_APPROVAL_FYI'
5981: AND group_id = p_group_id;
5982: l_notified_id NUMBER;
5983: l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); -- 5345171
6050: l_assignment_id_tbl := p_assignment_id_tbl;
6051:
6052: ---------------------------------------------------------
6053: --For every assignment in this mass transaction populate
6054: --pa_wf_ntf_performers with recipients
6055: --Assumptions: The project manager remains the same for
6056: --all assignments in the client extension
6057: --------------------------------------------------------
6058: FOR i in 1..l_assignment_id_tbl.COUNT LOOP
6190:
6191: IF l_out_approval_nf_rects_tbl.COUNT > 0 THEN
6192:
6193: ---------------------------------------------------------
6194: --Get Recipients and populate pa_wf_ntf_performers table
6195: --Use WF_TYPE_CODE as MASS_APPROVAL_FYI
6196: ---------------------------------------------------------
6197: FOR j IN 1..l_out_approval_nf_rects_tbl.COUNT LOOP
6198: l_approval_nf_rects_rec := l_out_approval_nf_rects_tbl(j);
6202: IF l_approval_nf_rects_rec.user_name IS NOT NULL THEN
6203:
6204: log_message('Insert in loop:' || j);
6205:
6206: INSERT INTO pa_wf_ntf_performers(
6207: WF_TYPE_CODE
6208: ,ITEM_TYPE
6209: ,ITEM_KEY
6210: ,OBJECT_ID1
6232: log_message('After inserting manager records');
6233:
6234: --------------------------------------------------------
6235: --Select distinct recipients for this mass transaction
6236: --from pa_wf_ntf_performers table and send notifications
6237: --------------------------------------------------------
6238: OPEN get_managers;
6239: FETCH get_managers BULK COLLECT INTO l_recipients_tbl, l_recipients_type_tbl;
6240: CLOSE get_managers;
6240: CLOSE get_managers;
6241:
6242: log_message('Number of manangers :' || l_recipients_tbl.COUNT);
6243:
6244: --Delete recipients from pa_wf_ntf_performers table
6245: --for this mass transaction
6246: DELETE
6247: FROM pa_wf_ntf_performers
6248: WHERE wf_type_code = 'MASS_APPROVAL_FYI'
6243:
6244: --Delete recipients from pa_wf_ntf_performers table
6245: --for this mass transaction
6246: DELETE
6247: FROM pa_wf_ntf_performers
6248: WHERE wf_type_code = 'MASS_APPROVAL_FYI'
6249: AND group_id = p_group_id;
6250:
6251: --------------------------------------------------------
6634: WHERE assignment_id = p_assignment_id;
6635:
6636: CURSOR get_assignment_status IS
6637: SELECT approval_status
6638: FROM pa_wf_ntf_performers
6639: WHERE group_id = p_group_id
6640: AND object_id1 = p_assignment_id
6641: AND rownum = 1;
6642:
6719: l_msg_data VARCHAR2(2000);
6720:
6721: CURSOR pending_approvals (c_assignment_id IN NUMBER) IS
6722: SELECT user_name
6723: FROM pa_wf_ntf_performers ntf
6724: WHERE ntf.group_id = p_group_id
6725: AND ntf.routing_order = p_routing_order + 1
6726: AND ntf.object_id1 = c_assignment_id;
6727:
6893:
6894: /* Commented and altered as below for bug 5595003
6895: CURSOR distinct_approvers IS
6896: SELECT distinct user_name
6897: FROM pa_wf_ntf_performers ntf,
6898: pa_project_assignments asgn
6899: WHERE ntf.group_id = p_group_id
6900: AND ntf.object_id1 = asgn.assignment_id
6901: AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted
6902: AND ntf.routing_order = p_routing_order + 1; */
6903:
6904: CURSOR distinct_approvers IS
6905: SELECT distinct ntf.user_name
6906: FROM pa_wf_ntf_performers ntf,
6907: pa_project_assignments asgn,
6908: pa_wf_ntf_performers ntf1
6909: WHERE ntf.group_id = p_group_id
6910: AND ntf.object_id1 = asgn.assignment_id
6904: CURSOR distinct_approvers IS
6905: SELECT distinct ntf.user_name
6906: FROM pa_wf_ntf_performers ntf,
6907: pa_project_assignments asgn,
6908: pa_wf_ntf_performers ntf1
6909: WHERE ntf.group_id = p_group_id
6910: AND ntf.object_id1 = asgn.assignment_id
6911: AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted
6912: AND ntf.routing_order = p_routing_order + 1
6916: AND ntf1.approver_group_id = p_approver_group_id;
6917:
6918: CURSOR pending_txn_approvals IS
6919: SELECT 'Y'
6920: FROM pa_wf_ntf_performers ntf,
6921: pa_project_assignments asgn
6922: WHERE ntf.group_id = p_group_id
6923: AND ntf.object_id1 = asgn.assignment_id
6924: AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted;
7059: --start approval required notification
7060: ---------------------------------------------------
7061: FOR rec IN distinct_approvers LOOP
7062:
7063: SELECT PA_WF_NTF_PERFORMERS_S.nextval
7064: INTO l_approver_group_id
7065: FROM dual;
7066:
7067: /* Commented and altered as below for bug 5595003
7064: INTO l_approver_group_id
7065: FROM dual;
7066:
7067: /* Commented and altered as below for bug 5595003
7068: UPDATE pa_wf_ntf_performers
7069: SET approver_group_id = l_approver_group_id
7070: WHERE group_id = p_group_id
7071: AND user_name = rec.user_name
7072: AND routing_order = p_routing_order + 1; */
7071: AND user_name = rec.user_name
7072: AND routing_order = p_routing_order + 1; */
7073:
7074: FORALL k in p_assignment_id_tbl.first..p_assignment_id_tbl.last
7075: UPDATE pa_wf_ntf_performers
7076: SET approver_group_id = l_approver_group_id
7077: WHERE group_id = p_group_id
7078: AND user_name = rec.user_name
7079: AND routing_order = p_routing_order + 1
7127:
7128: --Get all assignments in this mass transaction
7129: SELECT ntf.object_id1
7130: BULK COLLECT INTO l_assignment_id_tbl
7131: FROM pa_wf_ntf_performers ntf
7132: WHERE ntf.group_id = p_group_id
7133: AND ntf.routing_order = 1;
7134: EXCEPTION
7135: WHEN NO_DATA_FOUND THEN
7447:
7448: BEGIN
7449: SELECT object_id1
7450: INTO l_assignment_id
7451: FROM pa_wf_ntf_performers
7452: WHERE group_id = p_group_id
7453: AND rownum = 1;
7454: EXCEPTION
7455: WHEN NO_DATA_FOUND THEN
7848: l_ntfy_apprvl_recip_name fnd_user.user_name%TYPE; --skkoppul commented for bug 6744129 VARCHAR2(30); -- added for bug 5488496
7849:
7850: CURSOR pending_txn_approvals IS
7851: SELECT 'Y'
7852: FROM pa_wf_ntf_performers ntf,
7853: pa_project_assignments asgn
7854: WHERE ntf.group_id = l_group_id
7855: AND ntf.approver_group_id = l_approver_group_id --uncommented this line for Bug#5662785
7856: AND ntf.routing_order = l_routing_order
7918: IF funcmode = 'TIMEOUT' THEN
7919:
7920: SELECT ntf.object_id1
7921: BULK COLLECT INTO l_assignment_id_tbl
7922: FROM pa_wf_ntf_performers ntf,
7923: pa_project_assignments asgn
7924: WHERE ntf.group_id = l_group_id
7925: AND ntf.approver_group_id = l_approver_group_id
7926: AND ntf.routing_order = l_routing_order
8091: aname => 'GROUP_ID');
8092:
8093: SELECT ntf.object_id1
8094: BULK COLLECT INTO l_assignment_id_tbl
8095: FROM pa_wf_ntf_performers ntf,
8096: pa_project_assignments asgn
8097: WHERE ntf.group_id = l_group_id
8098: AND ntf.routing_order = 1
8099: AND ntf.object_id1 = asgn.assignment_id