[Home] [Help]
PACKAGE BODY: APPS.JTS_SETUP_FLOW_PVT
Source
1 PACKAGE BODY JTS_SETUP_FLOW_PVT as
2 /* $Header: jtsvcsfb.pls 115.6 2002/04/10 18:10:14 pkm ship $ */
3
4
5 -- --------------------------------------------------------------------------------------
6 -- Package name : JTS_SETUP_FLOW_PVT
7 -- Purpose : Setup Summary Hiearchy.
8 -- History : 21-Feb-02 Sung Ha Huh Created.
9 -- 27-Feb-02 SHUH Moved insert, update, delete,
10 -- translate, and load to
11 -- new package JTS_SETUP_FLOW_HIEARCHY_PKG.
12 -- NOTE :
13 -- --------------------------------------------------------------------------------------
14
15
16 -- Returns the flow id of a flow's parent
17 FUNCTION GET_PARENT_FLOW_ID(p_flow_id IN NUMBER)
18 RETURN NUMBER IS
19 l_flow_id JTS_SETUP_FLOWS_B.flow_id%TYPE;
20 BEGIN
21 SELECT parent_id
22 INTO l_flow_id
23 FROM jts_setup_flows_b
24 WHERE flow_id = p_flow_id;
25
26 return (l_flow_id);
27 EXCEPTION
28 WHEN OTHERS THEN
29 return NULL;
30 END GET_PARENT_FLOW_ID;
31
32 -- Returns the flow name of a flow given a flow id
33 FUNCTION GET_FLOW_NAME(p_flow_id IN NUMBER)
34 RETURN VARCHAR2 IS
35 l_flow_name JTS_SETUP_FLOWS_VL.flow_name%TYPE;
36 BEGIN
37 SELECT flow_name
38 INTO l_flow_name
39 FROM jts_setup_flows_vl
40 WHERE flow_id = p_flow_id;
41
42 return (l_flow_name);
43 EXCEPTION
44 WHEN OTHERS THEN
45 return NULL;
46 END GET_FLOW_NAME;
47
48 -- Gets Configuration Types that is a Complete Business Flow
49 PROCEDURE GET_FLOW_ROOT_FLOWS(
50 p_api_version IN NUMBER,
51 x_flow_tbl OUT NOCOPY Root_Setup_Flow_Tbl_Type
52 ) IS
53 l_api_version CONSTANT NUMBER := 1.0;
54 l_api_name CONSTANT VARCHAR2 (30) := 'GET_FLOW_ROOT_FLOWS';
55 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
56
57 i NUMBER := 1;
58
59 CURSOR l_flow_csr IS
60 SELECT flow_id, flow_name, flow_type
61 FROM jts_setup_flows_vl
62 WHERE parent_id IS NULL
63 AND flow_type = C_FLOW_FLOW_TYPE;
64
65 BEGIN
66 IF NOT fnd_api.compatible_api_call ( l_api_version,
67 p_api_version,
68 l_api_name,
69 G_PKG_NAME
70 )
71 THEN
72 RAISE fnd_api.g_exc_unexpected_error;
73 END IF;
74
75 i := 1;
76 OPEN l_flow_csr;
77 LOOP
78 FETCH l_flow_csr INTO x_flow_tbl(i);
79 EXIT WHEN l_flow_csr%NOTFOUND;
80 i := i + 1;
81 END LOOP;
82 CLOSE l_flow_csr;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 APP_EXCEPTION.RAISE_EXCEPTION;
87 END GET_FLOW_ROOT_FLOWS;
88
89 -- Gets Configuration Types that are indivdual modules
90 PROCEDURE GET_MODULE_ROOT_FLOWS(p_api_version IN NUMBER,
91 x_flow_tbl OUT NOCOPY Root_Setup_Flow_Tbl_Type) IS
92 l_api_version CONSTANT NUMBER := 1.0;
93 l_api_name CONSTANT VARCHAR2 (30) := 'GET_MODULE_ROOT_FLOWS';
94 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
95
96 i NUMBER := 1;
97
98 CURSOR l_flow_csr IS
99 SELECT flow_id, flow_name, flow_type
100 FROM jts_setup_flows_vl
101 WHERE parent_id IS NULL
102 AND flow_type = C_MODULE_FLOW_TYPE;
103
104 BEGIN
105 IF NOT fnd_api.compatible_api_call ( l_api_version,
106 p_api_version,
107 l_api_name,
108 G_PKG_NAME
109 )
110 THEN
111 RAISE fnd_api.g_exc_unexpected_error;
112 END IF;
113
114 i := 1;
115 OPEN l_flow_csr;
116 LOOP
117 FETCH l_flow_csr INTO x_flow_tbl(i);
118 EXIT WHEN l_flow_csr%NOTFOUND;
119 i := i + 1;
120 END LOOP;
121 CLOSE l_flow_csr;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 APP_EXCEPTION.RAISE_EXCEPTION;
126 END GET_MODULE_ROOT_FLOWS;
127
128 -- Gets Setup Hiearchy through recursion, starting from the root
129 PROCEDURE GET_FLOW_HIEARCHY(p_api_version IN NUMBER,
130 p_flow_id IN NUMBER,
131 x_flow_tbl OUT NOCOPY Setup_Flow_Tbl_Type) IS
132
133 l_api_version CONSTANT NUMBER := 1.0;
134 l_api_name CONSTANT VARCHAR2 (30) := 'GET_FLOW_HIEARCHY';
135 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
136
137 i NUMBER := 1;
138
139 CURSOR l_flows_csr IS
140 SELECT sb.flow_id, sv.flow_name, sb.flow_code, sb.parent_id,
141 sb.mlevel, sb.flow_sequence, sb.overview_url, sb.diagnostics_url,
142 sb.dpf_code, sb.dpf_asn, sb.num_steps, sb.flow_type, sb.has_child_flag
143 FROM (SELECT flow_id, flow_code, parent_id, level mlevel, flow_sequence, flow_type,
144 overview_url, diagnostics_url,
145 dpf_code, dpf_asn, num_steps, has_child_flag
146 FROM jts_setup_flows_b
147 START WITH flow_id = p_flow_id
148 CONNECT BY prior flow_id= parent_id) sb,
149 jts_setup_flows_vl sv
150 WHERE sv.flow_id=sb.flow_id;
151
152 BEGIN
153 IF NOT fnd_api.compatible_api_call ( l_api_version,
154 p_api_version,
155 l_api_name,
156 G_PKG_NAME
157 )
158 THEN
159 RAISE fnd_api.g_exc_unexpected_error;
160 END IF;
161
162 i := 1;
163 OPEN l_flows_csr;
164 LOOP
165 FETCH l_flows_csr INTO x_flow_tbl(i);
166 EXIT WHEN l_flows_csr%NOTFOUND;
167 i := i + 1;
168 END LOOP;
169 CLOSE l_flows_csr;
170
171 EXCEPTION
172 WHEN OTHERS THEN
173 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
174 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
175 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', 'Hiearchy');
176 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', '');
177 FND_MESSAGE.SET_TOKEN('PARAMETERS', '');
178 APP_EXCEPTION.RAISE_EXCEPTION;
179 END GET_FLOW_HIEARCHY;
180
181
182 -- Gets Setup Hiearchy through recursion, starting from the root
183 PROCEDURE GET_FLOW_DATA_HIEARCHY(p_api_version IN NUMBER,
184 p_flow_id IN NUMBER,
185 p_version_id IN NUMBER,
186 x_flow_tbl OUT NOCOPY Flow_Tbl_Type) IS
187
188 l_api_version CONSTANT NUMBER := 1.0;
189 l_api_name CONSTANT VARCHAR2 (30) := 'GET_FLOW_DATA_HIEARCHY';
190 l_full_name CONSTANT VARCHAR2 (60) := G_PKG_NAME || '.' || l_api_name;
191
192 i NUMBER := 1;
193
194 CURSOR l_flows_csr IS
195 SELECT sb.flow_id, sv.flow_name, sb.flow_code, sb.parent_id,
196 sb.mlevel, sb.flow_sequence, sb.overview_url, sb.diagnostics_url,
197 sb.dpf_code, sb.dpf_asn, sb.num_steps, sb.flow_type, sb.has_child_flag,
198 vf.version_id, vf.complete_flag, vf.creation_date, vf.last_update_date,
199 u1.user_name, u2.user_name
200 FROM (SELECT flow_id, level mlevel, flow_code, flow_sequence, parent_id, flow_type,
201 has_child_flag, overview_url, diagnostics_url,
202 dpf_code, dpf_asn, num_steps
203 FROM jts_setup_flows_b
204 START WITH flow_id = p_flow_id
205 CONNECT BY prior flow_id= parent_id) sb,
206 jts_setup_flows_vl sv,
207 jts_config_version_flows vf,
208 fnd_user u1,
209 fnd_user u2
210 WHERE sb.flow_id=sv.flow_id
211 AND vf.flow_id = sb.flow_id
212 AND vf.version_id = p_version_id
213 AND u1.user_id (+) = vf.created_by
214 AND u2.user_id (+) = vf.last_updated_by;
215
216 BEGIN
217 IF NOT fnd_api.compatible_api_call ( l_api_version,
218 p_api_version,
219 l_api_name,
220 G_PKG_NAME
221 )
222 THEN
223 RAISE fnd_api.g_exc_unexpected_error;
224 END IF;
225
226 i := 1;
227 OPEN l_flows_csr;
228 LOOP
229 FETCH l_flows_csr INTO x_flow_tbl(i);
230 EXIT WHEN l_flows_csr%NOTFOUND;
231 i := i + 1;
232 END LOOP;
233 CLOSE l_flows_csr;
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 APP_EXCEPTION.RAISE_EXCEPTION;
238 END GET_FLOW_DATA_HIEARCHY;
239
240
241 END JTS_SETUP_FLOW_PVT;