[Home] [Help]
PACKAGE BODY: APPS.IEU_NEXT_WORK_IEUSCPOP
Source
1 PACKAGE BODY IEU_NEXT_WORK_IEUSCPOP AS
2 /* $Header: IEUGNWDB.pls 115.6 2004/06/03 15:57:48 pkumble noship $ */
3
4
5 PROCEDURE EXECUTE_NEXT_WORK_PROC(
6 p_resource_id IN number,
7 p_ws_id_str IN VARCHAR2,
8 p_disp_cnt IN number,
9 x_wr_item_data_list IN OUT nocopy IEU_NEXT_WORK_IEUSCPOP.IEU_WR_ITEM_DATA)
10 IS
11 l_extra_where_clause varchar2(4000);
12 l_sql_stmt varchar2(4000);
13
14 l_ctr number;
15 l_nw_ctr number;
16
17 l_nw_cur IEU_NEXT_WORK_IEUSCPOP.l_get_work;
18 l_nw_item IEU_NEXT_WORK_IEUSCPOP.IEU_WR_ITEM_DATA_REC := null;
19 -- l_nw_items_list IEU_NEXT_WORK_IEUSCPOP.IEU_WR_ITEM_DATA;
20
21 BEGIN
22
23 -- insert into p_temp(msg) values('inside'); commit;
24 l_extra_where_clause := ' ( distribution_status_id = 1 and owner_type = '||''''||'RS_GROUP'||''''||
25 ' and owner_id in(select group_id from jtf_rs_group_members where resource_id = ' || p_resource_id ||
26 ' and nvl(delete_flag,'||''''||'N'||''''||') = '||''''||'N'||''''||') ' || ' ) OR ( '||
27 ' distribution_status_id = 3 and assignee_type = ' || ''''|| 'RS_INDIVIDUAL'||''''||
28 ' and assignee_id = '|| p_resource_id || ' ) ';
29
30 -- insert into p_temp(msg) values(l_extra_where_clause); commit;
31
32 -- Build the complete select stmt
33 l_sql_stmt := 'SELECT /*+ FIRST_ROWS */ WORK_ITEM_ID, WORKITEM_OBJ_CODE, WORKITEM_PK_ID,' ||
34 'PRIORITY_LEVEL, DUE_DATE, OWNER_ID, OWNER_TYPE_ACTUAL OWNER_TYPE, ASSIGNEE_ID, ' ||
35 'ASSIGNEE_TYPE_ACTUAL ASSIGNEE_TYPE, SOURCE_OBJECT_TYPE_CODE, RESCHEDULE_TIME, WS_ID, ' ||
36 'DISTRIBUTION_STATUS_ID, WORK_ITEM_NUMBER FROM IEU_UWQM_ITEMS '||
37 ' WHERE ( '|| l_extra_where_clause || ' ) '||
38 ' AND WS_ID in ( ' || p_ws_id_str || ' ) ' ||
39 -- ' AND DISTRIBUTION_STATUS_ID in (1,3) ' ||
40 ' AND STATUS_ID = 0 ' ||
41 ' and reschedule_time <= sysdate ' ||
42 ' order by priority_level, due_date ';
43
44 -- insert into p_temp(msg) values(l_sql_stmt); commit;
45
46 l_ctr := 0;
47 l_nw_ctr := 1;
48
49 OPEN l_nw_cur FOR l_sql_stmt;
50
51 LOOP
52 FETCH l_nw_cur into l_nw_item;
53
54 exit when ( (l_nw_cur%NOTFOUND) OR (l_nw_ctr > p_disp_cnt) ) ;
55
56 l_nw_ctr := l_nw_ctr + 1;
57
58 x_wr_item_data_list(l_ctr).WORK_ITEM_ID := l_nw_item.WORK_ITEM_ID;
59 x_wr_item_data_list(l_ctr).WORKITEM_OBJ_CODE := l_nw_item.WORKITEM_OBJ_CODE;
60 x_wr_item_data_list(l_ctr).WORKITEM_PK_ID := l_nw_item.WORKITEM_PK_ID;
61 x_wr_item_data_list(l_ctr).PRIORITY_LEVEL := l_nw_item.PRIORITY_LEVEL;
62 x_wr_item_data_list(l_ctr).DUE_DATE := l_nw_item.DUE_DATE;
63 x_wr_item_data_list(l_ctr).OWNER_ID := l_nw_item.OWNER_ID;
64 x_wr_item_data_list(l_ctr).OWNER_TYPE := l_nw_item.OWNER_TYPE;
65 x_wr_item_data_list(l_ctr).ASSIGNEE_ID := l_nw_item.ASSIGNEE_ID;
66 x_wr_item_data_list(l_ctr).ASSIGNEE_TYPE := l_nw_item.ASSIGNEE_TYPE;
67 x_wr_item_data_list(l_ctr).SOURCE_OBJECT_TYPE_CODE := l_nw_item.SOURCE_OBJECT_TYPE_CODE;
68 x_wr_item_data_list(l_ctr).RESCHEDULE_TIME := l_nw_item.RESCHEDULE_TIME;
69 x_wr_item_data_list(l_ctr).WS_ID := l_nw_item.WS_ID;
70 x_wr_item_data_list(l_ctr).DISTRIBUTION_STATUS_ID := l_nw_item.DISTRIBUTION_STATUS_ID;
71 x_wr_item_data_list(l_ctr).WORK_ITEM_NUMBER := l_nw_item.WORK_ITEM_NUMBER;
72
73 l_ctr := l_ctr + 1;
74
75 END LOOP;
76 CLOSE l_nw_cur;
77
78 commit;
79 END EXECUTE_NEXT_WORK_PROC;
80
81 procedure WORK_SOURCE_PROFILE_ENABLED(p_name IN varchar2, p_user_id IN number, p_responsibility_id IN number, p_application_id IN number, x_enabled_flag out nocopy varchar2) IS
82 l_enabled_flag varchar2(5);
83 begin
84 -- insert into p_temp(msg) values('before execute profile flag ');commit;
85
86 execute immediate 'select FND_PROFILE.VALUE_SPECIFIC(:1, :2, :3, :4) from dual' into l_enabled_flag
87 using IN p_name, IN p_user_id, IN p_responsibility_id, IN p_application_id;
88
89 -- insert into p_temp(msg) values('profile flag '||l_enabled_flag);commit;
90 x_enabled_flag := l_enabled_flag;
91 end;
92
93 END IEU_NEXT_WORK_IEUSCPOP;