1 PACKAGE BODY JTS_CONFIG_VERSION_FLOW_PVT as
2 /* $Header: jtsvcvfb.pls 115.4 2002/04/10 18:10:19 pkm ship $ */
3
4
5 -- --------------------------------------------------------------------
6 -- Package name : JTS_CONFIG_VERSION_FLOW_PVT
7 -- Purpose : Setup Summary Data.
8 -- History : 22-Feb-02 Sung Ha Huh Created.
9 -- NOTE :
10 -- --------------------------------------------------------------------
11
12 -- Precondition: Complete Flag for all the parents have been set
13 -- UPDATE_COMPLETE_FLAGS have been called
14 FUNCTION GET_PERCENT_COMPLETE(p_api_version IN NUMBER,
15 p_version_id IN NUMBER) RETURN NUMBER IS
16 l_completed NUMBER := 0;
17 l_total NUMBER := 0;
18 BEGIN
19
20 SELECT COUNT(*)
21 INTO l_completed
22 FROM jts_config_version_flows vf,
23 jts_setup_flows_b sf
24 WHERE version_id = p_version_id
25 AND complete_flag = 'Y'
26 AND sf.flow_id = vf.flow_id
27 AND sf.has_child_flag = 'N';
28
29 SELECT COUNT(*)
30 INTO l_total
31 FROM jts_config_version_flows vf,
32 jts_setup_flows_b sf
33 WHERE version_id = p_version_id
34 AND sf.flow_id = vf.flow_id
35 AND sf.has_child_flag = 'N';
36
37 IF l_total = 0 THEN
38 return 0;
39 END IF;
40 return ROUND(l_completed*100/l_total);
41
42 EXCEPTION
43 WHEN OTHERS THEN
44 APP_EXCEPTION.RAISE_EXCEPTION;
45 END GET_PERCENT_COMPLETE;
46
47 -- Updates last_update_date, last_updated_by of a subflow and its -- parent up to one level below the root
48 PROCEDURE UPDATE_FLOW_DETAILS(p_api_version IN Number,
49 p_version_id IN NUMBER,
50 p_flow_id IN NUMBER,
51 p_complete_flag IN VARCHAR2) IS
52 l_api_version CONSTANT NUMBER := 1.0;
53 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_FLOW_DETAILS';
54 l_flow_id JTS_SETUP_FLOWS_B.flow_id%TYPE;
55 l_parent_id JTS_SETUP_FLOWS_B.parent_id%TYPE;
56 BEGIN
57
58 IF NOT fnd_api.compatible_api_call ( l_api_version,
59 p_api_version,
60 l_api_name,
61 G_PKG_NAME
62 )
63 THEN
64 RAISE fnd_api.g_exc_unexpected_error;
65 END IF;
66
67 --propagate the changes up the root for last update fields
68 UPDATE jts_config_version_flows
69 SET complete_flag = p_complete_flag,
70 last_updated_by = FND_GLOBAL.user_id,
71 last_update_login = FND_GLOBAL.user_id,
72 last_update_date = sysdate
73 WHERE version_id = p_version_id
74 AND flow_id = p_flow_id;
75
76 l_flow_id := JTS_SETUP_FLOW_PVT.GET_PARENT_FLOW_ID(p_flow_id);
77
78 -- Propagate last_update information up to the parent right below the root level
79 WHILE (l_flow_id IS NOT NULL) LOOP
80 l_parent_id := JTS_SETUP_FLOW_PVT.GET_PARENT_FLOW_ID(l_flow_id);
81 IF (l_parent_id IS NOT NULL) THEN --not at root level yet
82 UPDATE jts_config_version_flows
83 SET last_updated_by = FND_GLOBAL.user_id,
84 last_update_login = FND_GLOBAL.user_id,
85 last_update_date = sysdate
86 WHERE version_id = p_version_id
87 AND flow_id = l_flow_id;
88 END IF;
89 l_flow_id := l_parent_id;
90 END LOOP;
91
92 COMMIT;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 APP_EXCEPTION.RAISE_EXCEPTION;
97 END UPDATE_FLOW_DETAILS;
98
99 -- Creates Setup Summary data by getting the flow hiearchy
100 -- and inserting with the appropriate flow_id
101 PROCEDURE CREATE_VERSION_FLOWS(p_api_version IN NUMBER,
102 p_version_id IN NUMBER,
103 p_flow_hiearchy IN JTS_SETUP_FLOW_PVT.Setup_Flow_Tbl_Type)
104 IS
105 l_api_version CONSTANT NUMBER := 1.0;
106 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_VERSION_FLOWS';
107 BEGIN
108
109 IF NOT fnd_api.compatible_api_call ( l_api_version,
110 p_api_version,
111 l_api_name,
112 G_PKG_NAME
113 )
114 THEN
115 RAISE fnd_api.g_exc_unexpected_error;
116 END IF;
117
118 FOR I IN 1..p_flow_hiearchy.count LOOP
119 INSERT INTO jts_config_version_flows(
120 version_id,
121 flow_id,
122 complete_flag,
123 object_version_number,
124 creation_date,
125 created_by,
126 last_update_date,
127 last_updated_by,
128 last_update_login)
129 VALUES(
130 p_version_id,
131 p_flow_hiearchy(I).flow_id,
132 'N',
133 1,
134 sysdate,
135 FND_GLOBAL.user_id, --created_by
136 sysdate,
137 FND_GLOBAL.user_id,
138 FND_GLOBAL.user_id
139 );
140 END LOOP;
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 APP_EXCEPTION.RAISE_EXCEPTION;
145 END CREATE_VERSION_FLOWS;
146
147 --Deletes from jts_config_version_flows
148 PROCEDURE DELETE_VERSION_FLOWS(p_api_version IN Number,
149 p_version_id IN NUMBER) IS
150 l_api_version CONSTANT NUMBER := 1.0;
151 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_VERSION_FLOWS';
152 BEGIN
153
154 IF NOT fnd_api.compatible_api_call ( l_api_version,
155 p_api_version,
156 l_api_name,
157 G_PKG_NAME
158 )
159 THEN
160 RAISE fnd_api.g_exc_unexpected_error;
161 END IF;
162
163 DELETE FROM jts_config_version_flows
164 WHERE version_id = p_version_id;
165
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 NULL;
169 WHEN OTHERS THEN
170 APP_EXCEPTION.RAISE_EXCEPTION;
171 END DELETE_VERSION_FLOWS;
172
173 -- Deletes all records from jts_config_version_flows where
174 -- version_id exists for p_config_id in versions table
175 PROCEDURE DELETE_CONFIG_VERSION_FLOWS(p_api_version IN Number,
176 p_config_id IN NUMBER) IS
177 l_api_version CONSTANT NUMBER := 1.0;
178 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_CONFIG_VERSION_FLOWS';
179 versions_csr JTS_CONFIG_UTIL_PVT.Versions_Csr_Type;
180 l_version_id JTS_CONFIG_VERSION_FLOWS.version_id%TYPE;
181 BEGIN
182
183 IF NOT fnd_api.compatible_api_call ( l_api_version,
184 p_api_version,
185 l_api_name,
186 G_PKG_NAME
187 )
188 THEN
189 RAISE fnd_api.g_exc_unexpected_error;
190 END IF;
191
192 JTS_CONFIG_UTIL_PVT.GET_VERSIONS_CURSOR(p_api_version,
193 p_config_id,
194 versions_csr);
195 LOOP
196 FETCH versions_csr INTO l_version_id;
197 EXIT WHEN versions_csr%NOTFOUND;
198
199 DELETE FROM jts_config_version_flows
200 WHERE version_id = l_version_id;
201 END LOOP;
202
203 CLOSE versions_csr;
204
205 EXCEPTION
206 WHEN NO_DATA_FOUND THEN
207 NULL;
208 WHEN OTHERS THEN
209 APP_EXCEPTION.RAISE_EXCEPTION;
210 END DELETE_CONFIG_VERSION_FLOWS;
211
212 -- Gets all the version flows
213 PROCEDURE GET_VERSION_FLOWS(p_api_version IN Number,
214 p_version_id IN NUMBER,
215 p_flow_tbl OUT NOCOPY Version_Flow_Tbl_Type) IS
216 l_api_version CONSTANT NUMBER := 1.0;
217 l_api_name CONSTANT VARCHAR2 (30) := 'GET_VERSION_FLOWS';
218 i NUMBER := 1;
219 CURSOR vflows_csr IS
220 SELECT version_id, flow_id, v.complete_flag,
221 v.creation_date, v.created_by, v.last_update_date, v.last_updated_by, v.last_update_login,
222 u1.user_name, u2.user_name
223 FROM jts_config_version_flows v,
224 fnd_user u1,
225 fnd_user u2
226 WHERE version_id = p_version_id
227 AND u1.user_id (+) = v.created_by
228 AND u2.user_id (+) = v.last_updated_by
229 ORDER BY flow_id;
230 BEGIN
231
232 IF NOT fnd_api.compatible_api_call ( l_api_version,
233 p_api_version,
234 l_api_name,
235 G_PKG_NAME
236 )
237 THEN
238 RAISE fnd_api.g_exc_unexpected_error;
239 END IF;
240
241 OPEN vflows_csr;
242 i:=1;
243 LOOP
244 FETCH vflows_csr
245 INTO p_flow_tbl(i).version_id,
246 p_flow_tbl(i).flow_id,
247 p_flow_tbl(i).complete_flag,
248 p_flow_tbl(i).creation_date,
249 p_flow_tbl(i).created_by,
250 p_flow_tbl(i).last_update_date,
251 p_flow_tbl(i).last_updated_by,
252 p_flow_tbl(i).last_update_login,
253 p_flow_tbl(i).created_by_name,
254 p_flow_tbl(i).last_updated_by_name;
255 EXIT WHEN vflows_csr%NOTFOUND;
256
257 i := i+1;
258 END LOOP;
259
260 CLOSE vflows_csr;
261
262 EXCEPTION
263 WHEN OTHERS THEN
264 APP_EXCEPTION.RAISE_EXCEPTION;
265 END GET_VERSION_FLOWS;
266
267
268 END JTS_CONFIG_VERSION_FLOW_PVT;
[Home] [Help]