[Home] [Help]
PACKAGE BODY: APPS.CSM_STATE_TRANSITION_EVENT_PKG
Source
1 PACKAGE BODY CSM_STATE_TRANSITION_EVENT_PKG AS
2 /* $Header: csmestrb.pls 120.7.12020000.2 2013/04/09 06:24:22 saradhak ship $ */
3
4 procedure Refresh_Acc_upd_Del (p_user_id NUMBER, p_resp_id NUMBER,p_lrd DATE)
5 IS
6
7 l_mark_dirty boolean;
8 l_user_tab asg_download.user_list;
9 l_acc_tab asg_download.access_list;
10
11 CURSOR l_deletes_cur IS
12 SELECT ACCESS_ID,USER_ID
13 FROM CSM_STATE_TRANSITIONS_ACC acc
14 WHERE acc.USER_ID = p_user_id
15 AND STATE_TRANSITION_ID not in (SELECT STATE_TRANSITION_ID
16 FROM JTF_STATE_TRANSITIONS trn
17 WHERE RULE_ID IN (SELECT sresp.RULE_ID
18 FROM JTF_STATE_RESPONSIBILITIES sresp, JTF_STATE_RULES_B srule
19 WHERE RESPONSIBILITY_ID = p_resp_id
20 AND sresp.RULE_ID = srule.RULE_ID
21 AND srule.STATE_TYPE = 'TASK_STATUS'));
22
23 CURSOR l_updates_cur IS
24 SELECT acc.ACCESS_ID,acc.USER_ID
25 FROM JTF_STATE_TRANSITIONS trn,
26 CSM_STATE_TRANSITIONS_ACC acc
27 WHERE trn.state_transition_id = acc.state_transition_id
28 AND acc.user_id = p_user_id AND trn.LAST_UPDATE_DATE > p_lrd;
29
30 BEGIN
31
32 l_acc_tab.DELETE; l_user_tab.DELETE;
33 OPEN l_deletes_cur;
34 FETCH l_deletes_cur BULK COLLECT INTO l_acc_tab,l_user_tab;
35 CLOSE l_deletes_cur;
36
37 IF l_acc_tab.COUNT > 0 THEN
38 FORALL I IN 1..l_acc_tab.COUNT
39 DELETE FROM CSM_STATE_TRANSITIONS_ACC WHERE ACCESS_ID=l_acc_tab(I);
40
41 l_mark_dirty := asg_download.mark_dirty(
42 P_PUB_ITEM => 'CSF_M_STATE_TRANSITIONS'
43 , p_accessList => l_acc_tab
44 , p_userid_list => l_user_tab
45 , p_dml_type => 'D'
46 , P_TIMESTAMP => sysdate );
47 END IF;
48
49 l_acc_tab.DELETE; l_user_tab.DELETE;
50 OPEN l_updates_cur;
51 FETCH l_updates_cur BULK COLLECT INTO l_acc_tab,l_user_tab;
52 CLOSE l_updates_cur;
53
54 IF l_acc_tab.COUNT > 0 THEN
55 l_mark_dirty := asg_download.mark_dirty(
56 P_PUB_ITEM => 'CSF_M_STATE_TRANSITIONS'
57 , p_accessList => l_acc_tab
58 , p_userid_list => l_user_tab
59 , p_dml_type => 'U'
60 , P_TIMESTAMP => sysdate );
61 END IF;
62
63 END Refresh_Acc_upd_Del;
64
65 procedure Refresh_Acc_Ins (p_user_id NUMBER, p_resp_id NUMBER)
66 IS
67
68 l_mark_dirty boolean;
69
70 l_user_tab asg_download.user_list;
71 l_acc_tab asg_download.access_list;
72 l_str_tab asg_download.access_list;
73
74 CURSOR l_inserts_cur IS
75 SELECT CSM_STATE_TRANSITIONS_ACC_S.NEXTVAL,p_user_id,trn.state_transition_id
76 FROM JTF_STATE_TRANSITIONS trn
77 WHERE trn.RULE_ID IN (SELECT sresp.RULE_ID
78 FROM JTF_STATE_RESPONSIBILITIES sresp, JTF_STATE_RULES_B srule
79 WHERE sresp.RULE_ID = srule.RULE_ID
80 AND srule.STATE_TYPE = 'TASK_STATUS'
81 AND RESPONSIBILITY_ID = p_resp_id )
82 AND not exists (SELECT 1 FROM CSM_STATE_TRANSITIONS_ACC acc
83 WHERE user_id = p_user_id
84 AND trn.state_transition_id=acc.state_transition_id);
85
86 BEGIN
87
88 OPEN l_inserts_cur;
89 FETCH l_inserts_cur BULK COLLECT INTO l_acc_tab,l_user_tab,l_str_tab;
90 CLOSE l_inserts_cur;
91
92 IF l_acc_tab.COUNT = 0 THEN
93 RETURN;
94 END IF;
95
96 FORALL I IN 1..l_acc_tab.COUNT
97 INSERT INTO CSM_STATE_TRANSITIONS_ACC(access_id,STATE_TRANSITION_ID, USER_ID, CREATED_BY,
98 CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
99 VALUES (l_acc_tab(I), l_str_tab(I), l_user_tab(I), fnd_global.user_id,
100 sysdate, fnd_global.user_id, sysdate,fnd_global.user_id);
101
102 l_mark_dirty := asg_download.mark_dirty(
103 P_PUB_ITEM => 'CSF_M_STATE_TRANSITIONS'
104 , p_accessList => l_acc_tab
105 , p_userid_list => l_user_tab
106 , p_dml_type => 'I'
107 , P_TIMESTAMP => sysdate );
108
109 END Refresh_Acc_Ins;
110
111 procedure Refresh_ACC (p_status OUT NOCOPY VARCHAR2,
112 p_message OUT NOCOPY VARCHAR2)
113 IS
114 PRAGMA AUTONOMOUS_TRANSACTION;
115 l_sqlerrno varchar2(20);
116 l_sqlerrmsg varchar2(2000);
117 l_run_date DATE;
118
119 CURSOR l_omfs_palm_resources_csr is
120 --R12 For multiple responsibility
121 select usr.user_id, usr.user_name
122 FROM asg_user_pub_resps pubresp
123 , asg_user usr
124 WHERE usr.enabled = 'Y'
125 AND pubresp.user_name = usr.user_name
126 AND usr.user_id=usr.owner_id
127 AND pubresp.pub_name ='SERVICEP';
128
129 CURSOR l_last_run_date_csr
130 IS
131 SELECT nvl(last_run_date, (sysdate - 365*50))
132 FROM jtm_con_request_data
133 WHERE package_name = 'CSM_STATE_TRANSITION_EVENT_PKG'
134 AND procedure_name = 'REFRESH_ACC';
135
136 BEGIN
137
138 OPEN l_last_run_date_csr;
139 FETCH l_last_run_date_csr INTO l_run_date;
140 CLOSE l_last_run_date_csr;
141
142 FOR rec IN (SELECT USER_ID,RESPONSIBILITY_ID FROM ASG_USER WHERE ENABLED='Y')
143 LOOP
144 Refresh_Acc_upd_Del(rec.USER_ID,rec.RESPONSIBILITY_ID,l_run_date);
145 Refresh_acc_ins(rec.USER_ID,rec.RESPONSIBILITY_ID);
146 END LOOP;
147
148
149 UPDATE jtm_con_request_data
150 SET last_run_date = sysdate
151 WHERE package_name = 'CSM_STATE_TRANSITION_EVENT_PKG'
152 AND procedure_name = 'REFRESH_ACC';
153
154 p_status := 'FINE';
155 p_message := 'CSM_STATE_TRANSITION_EVENT_PKG.Refresh_Acc Executed successfully';
156
157 COMMIT;
158
159 EXCEPTION
160 WHEN others THEN
161 l_sqlerrno := to_char(SQLCODE);
162 l_sqlerrmsg := substr(SQLERRM, 1,300);
163 csm_util_pkg.log('State Tansition Error:' || l_sqlerrno || ':' || l_sqlerrmsg, 'CSM_STATE_TRANISITION_EVENT_PKG.Refresh_Acc');
164 p_status := 'ERROR';
165 p_message := 'Error in CSM_STATE_TRANSITION_EVENT_PKG.Refresh_Acc :' ||l_sqlerrno || ':' || l_sqlerrmsg;
166 ROLLBACK;
167 END Refresh_ACC;
168 END CSM_STATE_TRANSITION_EVENT_PKG;