[Home] [Help]
2261: ,resultout OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
2262: l_number_of_approvers NUMBER := 0;
2263: l_approver_loop_counter NUMBER := 0;
2264: l_approver_item_attr_name VARCHAR2(30);
2265: l_approver_user_name pa_wf_ntf_performers.user_name%TYPE; ----VARCHAR2(240); Commented for bug 3267790
2266: l_prev_approver_user_name wf_users.name%TYPE; ----VARCHAR2(240); Commented for bug 3267790 as this takes the value from wf_users
2267: l_approver_type VARCHAR2(30);
2268: l_assignment_id NUMBER;
2269: l_wf_startedby_uname fnd_user.user_name%type;
2374:
2375: -- Need to set the current user as the current approver
2376: -- and set the flag to 'N' for the rest
2377: --
2378: UPDATE pa_wf_ntf_performers
2379: SET current_approver_flag =
2380: (DECODE(user_name,l_approver_user_name,'Y','N'))
2381: WHERE item_type = itemtype
2382: AND item_key = itemkey
2520: ,aname => 'NUMBER_OF_APPRVL_NF_RECIPIENTS'
2521: ,avalue => l_number_of_recipients
2522: );
2523: -- Now populate the Comments fields, which would have
2524: -- been stored in the pa_wf_ntf_performers table
2525: IF (l_apprvl_item_type IS NOT NULL AND l_apprvl_item_key
2526: IS NOT NULL ) THEN
2527: Set_approver_comments (p_apprvl_item_type => l_apprvl_item_type
2528: ,p_apprvl_item_key => l_apprvl_item_key
2552:
2553: l_number_of_apprvl_nf_rects NUMBER := 0;
2554: l_approval_nf_loop_counter NUMBER := 0;
2555: l_ntfy_rect_item_attr_name VARCHAR2(30);
2556: l_ntfy_apprvl_recipient_name pa_wf_ntf_performers.user_name%TYPE; ---VARCHAR2(240); Changed for bug 3267790
2557: l_ntfy_apprvl_rect_type VARCHAR2(30);
2558: l_assignment_id NUMBER := 0;
2559:
2560: BEGIN
2761: ,aname => 'NUMBER_OF_REJECT_NF_RECIPIENTS'
2762: ,avalue => l_number_of_recipients
2763: );
2764: -- Now populate the Comments fields, which would have
2765: -- been stored in the pa_wf_ntf_performers table
2766: IF (l_apprvl_item_type IS NOT NULL AND l_apprvl_item_key
2767: IS NOT NULL ) THEN
2768: Set_approver_comments (p_apprvl_item_type => l_apprvl_item_type
2769: ,p_apprvl_item_key => l_apprvl_item_key
2792:
2793: l_number_of_reject_nf_rects NUMBER := 0;
2794: l_rejection_nf_loop_counter NUMBER := 0;
2795: l_ntfy_rect_item_attr_name VARCHAR2(30);
2796: l_ntfy_reject_recipient_name pa_wf_ntf_performers.user_name%type; ----- VARCHAR2(240);Changed for bug 3267790
2797: l_ntfy_reject_rect_type VARCHAR2(30);
2798: l_assignment_id NUMBER := 0;
2799:
2800: BEGIN
3010: ,aname => 'NUMBER_OF_CANCEL_NF_RECIPIENTS'
3011: ,avalue => l_number_of_recipients
3012: );
3013: -- Now populate the Comments fields, which would have
3014: -- been stored in the pa_wf_ntf_performers table
3015: IF (l_apprvl_item_type IS NOT NULL AND l_apprvl_item_key
3016: IS NOT NULL ) THEN
3017: Set_approver_comments (p_apprvl_item_type => l_apprvl_item_type
3018: ,p_apprvl_item_key => l_apprvl_item_key
3042:
3043: l_number_of_cancel_nf_rects NUMBER := 0;
3044: l_cancellation_nf_loop_counter NUMBER := 0;
3045: l_ntfy_rect_item_attr_name VARCHAR2(30);
3046: l_ntfy_cancel_recipient_name pa_wf_ntf_performers.user_name%TYPE; ----- VARCHAR2(240);Changed for bug 3267790
3047: l_ntfy_cancel_rect_type VARCHAR2(30);
3048: l_assignment_id NUMBER := 0;
3049:
3050: BEGIN
3570: l_comment VARCHAR2(4000); --VARCHAR2(2000); Bug 7459451 : Changed the length to accomodate max length returned by wf_engine.getItemAttrText
3571: l_approver_user_name wf_users.name%type; ----- VARCHAR2(240);Changed for bug 3267790
3572: /* Modified length from 30 to 100 for bug 3148857 */
3573: l_assignment_id NUMBER;
3574: l_wf_context_user pa_wf_ntf_performers.user_name%TYPE;
3575: l_reassignee_user_name pa_wf_ntf_performers.user_name%TYPE;
3576: l_object_id1 pa_wf_ntf_performers.object_id1%TYPE;
3577: l_object_id2 pa_wf_ntf_performers.object_id2%TYPE;
3578: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3571: l_approver_user_name wf_users.name%type; ----- VARCHAR2(240);Changed for bug 3267790
3572: /* Modified length from 30 to 100 for bug 3148857 */
3573: l_assignment_id NUMBER;
3574: l_wf_context_user pa_wf_ntf_performers.user_name%TYPE;
3575: l_reassignee_user_name pa_wf_ntf_performers.user_name%TYPE;
3576: l_object_id1 pa_wf_ntf_performers.object_id1%TYPE;
3577: l_object_id2 pa_wf_ntf_performers.object_id2%TYPE;
3578: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3579:
3572: /* Modified length from 30 to 100 for bug 3148857 */
3573: l_assignment_id NUMBER;
3574: l_wf_context_user pa_wf_ntf_performers.user_name%TYPE;
3575: l_reassignee_user_name pa_wf_ntf_performers.user_name%TYPE;
3576: l_object_id1 pa_wf_ntf_performers.object_id1%TYPE;
3577: l_object_id2 pa_wf_ntf_performers.object_id2%TYPE;
3578: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3579:
3580: CURSOR get_reassignee IS
3573: l_assignment_id NUMBER;
3574: l_wf_context_user pa_wf_ntf_performers.user_name%TYPE;
3575: l_reassignee_user_name pa_wf_ntf_performers.user_name%TYPE;
3576: l_object_id1 pa_wf_ntf_performers.object_id1%TYPE;
3577: l_object_id2 pa_wf_ntf_performers.object_id2%TYPE;
3578: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3579:
3580: CURSOR get_reassignee IS
3581: SELECT USER_NAME
3574: l_wf_context_user pa_wf_ntf_performers.user_name%TYPE;
3575: l_reassignee_user_name pa_wf_ntf_performers.user_name%TYPE;
3576: l_object_id1 pa_wf_ntf_performers.object_id1%TYPE;
3577: l_object_id2 pa_wf_ntf_performers.object_id2%TYPE;
3578: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3579:
3580: CURSOR get_reassignee IS
3581: SELECT USER_NAME
3582: FROM pa_wf_ntf_performers
3578: l_prev_approver_user_name pa_wf_ntf_performers.user_name%TYPE;
3579:
3580: CURSOR get_reassignee IS
3581: SELECT USER_NAME
3582: FROM pa_wf_ntf_performers
3583: WHERE user_name = l_wf_context_user
3584: AND wf_type_code = 'ASSIGNMENT_APPROVAL'
3585: AND item_type = itemtype
3586: AND item_key = itemkey;
3586: AND item_key = itemkey;
3587:
3588: CURSOR get_objectids IS
3589: SELECT object_id1, object_id2
3590: FROM pa_wf_ntf_performers
3591: WHERE wf_type_code = 'ASSIGNMENT_APPROVAL'
3592: AND item_type = itemtype
3593: AND item_key = itemkey
3594: AND current_approver_flag = 'Y';
3609: ( itemtype => itemtype
3610: , itemkey => itemkey
3611: , aname => 'ASSIGNMENT_ID'
3612: );
3613: UPDATE pa_wf_ntf_performers
3614: SET approver_comments = substr(l_comment,1,255)
3615: WHERE item_type = itemtype
3616: AND item_key = itemkey
3617: AND object_id1 = l_assignment_id
3647: OPEN get_objectids;
3648: FETCH get_objectids INTO l_object_id1, l_object_id2;
3649: CLOSE get_objectids;
3650:
3651: INSERT INTO pa_wf_ntf_performers (WF_TYPE_CODE, ITEM_TYPE, ITEM_KEY,
3652: OBJECT_ID1, OBJECT_ID2, USER_NAME,
3653: USER_TYPE, CURRENT_APPROVER_FLAG)
3654: VALUES ('ASSIGNMENT_APPROVAL', itemtype, itemkey,
3655: l_object_id1, l_object_id2, l_wf_context_user,
3658: END IF;
3659:
3660:
3661: --Set current_approver_flag to 'N' for the previous current_approver
3662: UPDATE pa_wf_ntf_performers
3663: SET current_approver_flag = (DECODE(user_name,l_wf_context_user,'Y','N'))
3664: WHERE wf_type_code = 'ASSIGNMENT_APPROVAL'
3665: AND item_type = itemtype
3666: AND item_key = itemkey
3706: ,avalue => l_prev_approver_user_name);
3707:
3708: /*
3709:
3710: INSERT INTO pa_wf_ntf_performers (WF_TYPE_CODE, ITEM_TYPE, ITEM_KEY,
3711: OBJECT_ID1, OBJECT_ID2, USER_NAME,
3712: USER_TYPE, CURRENT_APPROVER_FLAG)
3713: VALUES ('ASSIGNMENT_APPROVAL', itemtype, itemkey,
3714: 1, 2, l_wf_context_user,
3971: IF p_in_performers_tbl.EXISTS(1) THEN
3972: FOR i IN 1..p_in_performers_tbl.COUNT LOOP
3973: IF wf_directory.UserActive
3974: (p_in_performers_tbl(i).User_Name) THEN
3975: INSERT INTO pa_wf_ntf_performers (
3976: Wf_Type_Code,Item_Type,
3977: Item_Key,object_id1,
3978: object_id2,User_Name,User_Type,
3979: Routing_Order,Current_Approver_flag
4006: x_performer_type OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
4007: IS
4008: CURSOR l_get_next_performer_csr
4009: IS
4010: SELECT User_Name , User_Type FROM pa_wf_ntf_performers
4011: WHERE wf_type_code = p_wf_type_code
4012: AND item_type = p_item_type
4013: AND item_key = p_item_key
4014: AND routing_order = p_routing_order
4013: AND item_key = p_item_key
4014: AND routing_order = p_routing_order
4015: AND object_id1 = p_object_id1;
4016:
4017: l_user_name pa_wf_ntf_performers.user_name%TYPE;
4018:
4019: BEGIN
4020:
4021: OPEN l_get_next_performer_csr ;
4056: p_assignment_id NUMBER )
4057: IS
4058: CURSOR l_get_approver_comments_csr IS
4059: SELECT user_Name,approver_comments
4060: FROM pa_wf_ntf_performers
4061: WHERE item_type = p_apprvl_item_type
4062: AND item_key = p_apprvl_item_key
4063: AND object_id1 = p_assignment_id
4064: AND approver_comments IS NOT NULL;
4399: AND entity_key2 = to_char(p_assignment_id)
4400: AND wf_type_code = 'ASSIGNMENT_APPROVAL';
4401:
4402:
4403: DELETE FROM pa_wf_ntf_performers
4404: WHERE wf_type_code in ('ASSIGNMENT_APPROVAL','REJECTION_FYI','APPROVAL_FYI','CANCELLATION_FYI')
4405: AND object_id1 = p_assignment_id;
4406:
4407: END Delete_Assignment_WF_Records;
4474: l_err_code NUMBER := 0;
4475: l_err_stage VARCHAR2(2000);
4476: l_err_stack VARCHAR2(2000);
4477:
4478: 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
4479: l_prev_user fnd_user.user_name%TYPE; --used to set FORWARDED_FROM attribute
4480: l_number_of_assignments NUMBER;
4481: l_mass_approve_url VARCHAR2(2000); --Mass Approve page URL
4482: l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); -- 5345171
4525: -----------------------------------
4526: BEGIN
4527: SELECT user_name
4528: INTO l_ntfy_apprvl_recipient_name
4529: FROM pa_wf_ntf_performers
4530: WHERE group_id = p_group_id
4531: AND approver_group_id = p_approver_group_id
4532: AND routing_order = p_routing_order
4533: AND rownum = 1;
4555: --------------------------------------------
4556: BEGIN
4557: SELECT count(object_id1)
4558: INTO l_number_of_assignments
4559: FROM pa_wf_ntf_performers pf,
4560: pa_project_assignments asmt
4561: WHERE pf.group_id = p_group_id
4562: AND pf.approver_group_id = p_approver_group_id
4563: AND pf.routing_order = p_routing_order
5557:
5558: BEGIN
5559: SELECT object_id1
5560: INTO l_assignment_id
5561: FROM pa_wf_ntf_performers
5562: WHERE group_id = p_group_id
5563: AND rownum = 1;
5564: EXCEPTION
5565: WHEN OTHERS THEN
5869: * 1. Staffing Manager FYI for the mass txn
5870: * 2. Resource Manager FYI for the mass txn
5871: * 3. Project Manager FYI for the mass txn
5872: * The logic involves calling the client extension for every
5873: * assignment and populating pa_wf_ntf_performers table
5874: * Notifications are then sent to recipients from the pa_wf_ntf_performers table
5875: */
5876: PROCEDURE process_mgr_fyi_notification
5877: ( p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
5870: * 2. Resource Manager FYI for the mass txn
5871: * 3. Project Manager FYI for the mass txn
5872: * The logic involves calling the client extension for every
5873: * assignment and populating pa_wf_ntf_performers table
5874: * Notifications are then sent to recipients from the pa_wf_ntf_performers table
5875: */
5876: PROCEDURE process_mgr_fyi_notification
5877: ( p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
5878: ,p_project_id IN NUMBER := FND_API.G_MISS_NUM
5940: CURSOR Resource_Manager IS
5941: SELECT distinct res.manager_id
5942: FROM pa_resources_denorm res,
5943: pa_project_assignments asgn,
5944: pa_wf_ntf_performers ntf,
5945: fnd_user fnd
5946: WHERE ntf.group_id = p_group_id
5947: AND asgn.assignment_id = l_assignment_id
5948: AND asgn.assignment_id = ntf.object_id1
5961: /* Commenting this for performance tuning Bug#2499051
5962: (select pa_security_pvt.get_menu_id('PA_PRM_RES_PRMRY_CONTACT') menu_id
5963: from dual) temp, */
5964: pa_project_assignments asgn,
5965: pa_wf_ntf_performers ntf,
5966: wf_roles wf,
5967: per_all_people_f per
5968: WHERE ntf.group_id = p_group_id
5969: AND asgn.assignment_id = l_assignment_id
5991:
5992: CURSOR get_managers IS
5993: SELECT distinct user_name,
5994: user_type
5995: FROM pa_wf_ntf_performers
5996: WHERE wf_type_code = 'MASS_APPROVAL_FYI'
5997: AND group_id = p_group_id;
5998: l_notified_id NUMBER;
5999: l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); -- 5345171
6066: l_assignment_id_tbl := p_assignment_id_tbl;
6067:
6068: ---------------------------------------------------------
6069: --For every assignment in this mass transaction populate
6070: --pa_wf_ntf_performers with recipients
6071: --Assumptions: The project manager remains the same for
6072: --all assignments in the client extension
6073: --------------------------------------------------------
6074: FOR i in 1..l_assignment_id_tbl.COUNT LOOP
6206:
6207: IF l_out_approval_nf_rects_tbl.COUNT > 0 THEN
6208:
6209: ---------------------------------------------------------
6210: --Get Recipients and populate pa_wf_ntf_performers table
6211: --Use WF_TYPE_CODE as MASS_APPROVAL_FYI
6212: ---------------------------------------------------------
6213: FOR j IN 1..l_out_approval_nf_rects_tbl.COUNT LOOP
6214: l_approval_nf_rects_rec := l_out_approval_nf_rects_tbl(j);
6218: IF l_approval_nf_rects_rec.user_name IS NOT NULL THEN
6219:
6220: log_message('Insert in loop:' || j);
6221:
6222: INSERT INTO pa_wf_ntf_performers(
6223: WF_TYPE_CODE
6224: ,ITEM_TYPE
6225: ,ITEM_KEY
6226: ,OBJECT_ID1
6248: log_message('After inserting manager records');
6249:
6250: --------------------------------------------------------
6251: --Select distinct recipients for this mass transaction
6252: --from pa_wf_ntf_performers table and send notifications
6253: --------------------------------------------------------
6254: OPEN get_managers;
6255: FETCH get_managers BULK COLLECT INTO l_recipients_tbl, l_recipients_type_tbl;
6256: CLOSE get_managers;
6256: CLOSE get_managers;
6257:
6258: log_message('Number of manangers :' || l_recipients_tbl.COUNT);
6259:
6260: --Delete recipients from pa_wf_ntf_performers table
6261: --for this mass transaction
6262: DELETE
6263: FROM pa_wf_ntf_performers
6264: WHERE wf_type_code = 'MASS_APPROVAL_FYI'
6259:
6260: --Delete recipients from pa_wf_ntf_performers table
6261: --for this mass transaction
6262: DELETE
6263: FROM pa_wf_ntf_performers
6264: WHERE wf_type_code = 'MASS_APPROVAL_FYI'
6265: AND group_id = p_group_id;
6266:
6267: --------------------------------------------------------
6650: WHERE assignment_id = p_assignment_id;
6651:
6652: CURSOR get_assignment_status IS
6653: SELECT approval_status
6654: FROM pa_wf_ntf_performers
6655: WHERE group_id = p_group_id
6656: AND object_id1 = p_assignment_id
6657: AND rownum = 1;
6658:
6735: l_msg_data VARCHAR2(2000);
6736:
6737: CURSOR pending_approvals (c_assignment_id IN NUMBER) IS
6738: SELECT user_name
6739: FROM pa_wf_ntf_performers ntf
6740: WHERE ntf.group_id = p_group_id
6741: AND ntf.routing_order = p_routing_order + 1
6742: AND ntf.object_id1 = c_assignment_id;
6743:
6909:
6910: /* Commented and altered as below for bug 5595003
6911: CURSOR distinct_approvers IS
6912: SELECT distinct user_name
6913: FROM pa_wf_ntf_performers ntf,
6914: pa_project_assignments asgn
6915: WHERE ntf.group_id = p_group_id
6916: AND ntf.object_id1 = asgn.assignment_id
6917: AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted
6918: AND ntf.routing_order = p_routing_order + 1; */
6919:
6920: CURSOR distinct_approvers IS
6921: SELECT distinct ntf.user_name
6922: FROM pa_wf_ntf_performers ntf,
6923: pa_project_assignments asgn,
6924: pa_wf_ntf_performers ntf1
6925: WHERE ntf.group_id = p_group_id
6926: AND ntf.object_id1 = asgn.assignment_id
6920: CURSOR distinct_approvers IS
6921: SELECT distinct ntf.user_name
6922: FROM pa_wf_ntf_performers ntf,
6923: pa_project_assignments asgn,
6924: pa_wf_ntf_performers ntf1
6925: WHERE ntf.group_id = p_group_id
6926: AND ntf.object_id1 = asgn.assignment_id
6927: AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted
6928: AND ntf.routing_order = p_routing_order + 1
6932: AND ntf1.approver_group_id = p_approver_group_id;
6933:
6934: CURSOR pending_txn_approvals IS
6935: SELECT 'Y'
6936: FROM pa_wf_ntf_performers ntf,
6937: pa_project_assignments asgn
6938: WHERE ntf.group_id = p_group_id
6939: AND ntf.object_id1 = asgn.assignment_id
6940: AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted;
7075: --start approval required notification
7076: ---------------------------------------------------
7077: FOR rec IN distinct_approvers LOOP
7078:
7079: SELECT PA_WF_NTF_PERFORMERS_S.nextval
7080: INTO l_approver_group_id
7081: FROM dual;
7082:
7083: /* Commented and altered as below for bug 5595003
7080: INTO l_approver_group_id
7081: FROM dual;
7082:
7083: /* Commented and altered as below for bug 5595003
7084: UPDATE pa_wf_ntf_performers
7085: SET approver_group_id = l_approver_group_id
7086: WHERE group_id = p_group_id
7087: AND user_name = rec.user_name
7088: AND routing_order = p_routing_order + 1; */
7087: AND user_name = rec.user_name
7088: AND routing_order = p_routing_order + 1; */
7089:
7090: FORALL k in p_assignment_id_tbl.first..p_assignment_id_tbl.last
7091: UPDATE pa_wf_ntf_performers
7092: SET approver_group_id = l_approver_group_id
7093: WHERE group_id = p_group_id
7094: AND user_name = rec.user_name
7095: AND routing_order = p_routing_order + 1
7143:
7144: --Get all assignments in this mass transaction
7145: SELECT ntf.object_id1
7146: BULK COLLECT INTO l_assignment_id_tbl
7147: FROM pa_wf_ntf_performers ntf
7148: WHERE ntf.group_id = p_group_id
7149: AND ntf.routing_order = 1;
7150: EXCEPTION
7151: WHEN NO_DATA_FOUND THEN
7463:
7464: BEGIN
7465: SELECT object_id1
7466: INTO l_assignment_id
7467: FROM pa_wf_ntf_performers
7468: WHERE group_id = p_group_id
7469: AND rownum = 1;
7470: EXCEPTION
7471: WHEN NO_DATA_FOUND THEN
7864: l_ntfy_apprvl_recip_name fnd_user.user_name%TYPE; --skkoppul commented for bug 6744129 VARCHAR2(30); -- added for bug 5488496
7865:
7866: CURSOR pending_txn_approvals IS
7867: SELECT 'Y'
7868: FROM pa_wf_ntf_performers ntf,
7869: pa_project_assignments asgn
7870: WHERE ntf.group_id = l_group_id
7871: AND ntf.approver_group_id = l_approver_group_id --uncommented this line for Bug#5662785
7872: AND ntf.routing_order = l_routing_order
7934: IF funcmode = 'TIMEOUT' THEN
7935:
7936: SELECT ntf.object_id1
7937: BULK COLLECT INTO l_assignment_id_tbl
7938: FROM pa_wf_ntf_performers ntf,
7939: pa_project_assignments asgn
7940: WHERE ntf.group_id = l_group_id
7941: AND ntf.approver_group_id = l_approver_group_id
7942: AND ntf.routing_order = l_routing_order
8107: aname => 'GROUP_ID');
8108:
8109: SELECT ntf.object_id1
8110: BULK COLLECT INTO l_assignment_id_tbl
8111: FROM pa_wf_ntf_performers ntf,
8112: pa_project_assignments asgn
8113: WHERE ntf.group_id = l_group_id
8114: AND ntf.routing_order = 1
8115: AND ntf.object_id1 = asgn.assignment_id