1 PACKAGE BODY az_validate_active AS
2 /* $Header: azvalidateactvb.pls 120.4 2006/07/21 09:31:27 gagupta noship $ */
3
4 PROCEDURE validate_selsets AS
5 BEGIN
6
7 UPDATE az_selection_sets_b
8 SET active = 'N'
9 WHERE selection_set_code IN
10 (SELECT selection_set_code
11 FROM az_selection_sets_b
12 WHERE user_id <> 1
13 AND structure_code IN (SELECT structure_code
14 FROM az_structures_b
15 WHERE active = 'N'));
16 COMMIT;
17
18 END validate_selsets;
19
20
21 PROCEDURE validate_transforms AS
22 BEGIN
23
24 UPDATE az_requests
25 SET active='N'
26 WHERE request_type = 'T'
27 AND selection_set IS NULL;
28
29 UPDATE az_requests req
30 SET req.active = (
31 SELECT decode(strct.active, 'N', 'N', NULL, 'N', 'Y')
32 FROM az_structures_b strct
33 WHERE structure_code =
34 (SELECT extractvalue(VALUE(e),
35 '/H/V[@N="StructureCode"]/text()')
36 FROM az_requests d,
37 TABLE(xmlsequence(extract(d.selection_set,
38 '/EXT/H/V[@N="StructureCode"]/..'))) e
39 WHERE req.job_name = d.job_name
40 AND req.user_id = d.user_id
41 AND req.request_type = d.request_type
42 AND d.selection_set IS NOT NULL))
43 WHERE req.request_type = 'T'
44 AND req.selection_set IS NOT NULL;
45
46 COMMIT;
47
48 END validate_transforms;
49
50 PROCEDURE validate_loads AS
51 BEGIN
52
53 UPDATE az_requests
54 SET active='N'
55 WHERE request_type = 'L'
56 AND selection_set IS NULL;
57
58 UPDATE az_requests req
59 SET req.active = (
60 SELECT decode(strct.active, 'N', 'N', NULL, 'N', 'Y')
61 FROM az_structures_b strct
62 WHERE structure_code =
63 (SELECT extractvalue(VALUE(e),
64 '/H/V[@N="StructureCode"]/text()')
65 FROM az_requests d,
66 TABLE(xmlsequence(extract(d.selection_set,
67 '/EXT/H/V[@N="StructureCode"]/..'))) e
68 WHERE req.job_name = d.job_name
69 AND req.user_id = d.user_id
70 AND req.request_type = d.request_type
71 AND d.selection_set IS NOT NULL))
72 WHERE req.request_type = 'L'
73 AND req.selection_set IS NOT NULL;
74
75 COMMIT;
76
77 END validate_loads;
78
79 PROCEDURE activate_apis AS
80 BEGIN
81
82 UPDATE az_apis
83 SET active = 'Y'
84 WHERE api_code IN (
85 SELECT DISTINCT api.api_code
86 FROM az_structures_b strct,
87 az_selection_sets_b sset,
88 az_selection_set_entities_b ent,
89 az_structure_apis_b sapi,
90 az_apis api
91 WHERE strct.structure_code = sset.structure_code
92 AND strct.structure_code = sapi.structure_code
93 AND sset.selection_set_code = ent.selection_set_code
94 AND sapi.api_code = api.api_code
95 AND strct.active = 'Y'
96 AND api.active IS NULL);
97
98 COMMIT;
99
100 END activate_apis;
101
102 PROCEDURE validate_extracts AS
103 BEGIN
104
105 activate_apis;
106
107 UPDATE az_requests
108 SET active='N'
109 WHERE request_type = 'E'
110 AND selection_set IS NULL;
111
112 UPDATE az_requests
113 SET active = 'N'
114 WHERE request_type = 'E'
115 AND request_phase <> 'C'
116 AND request_status <> 'C'
117 AND selection_set IS NOT NULL;
118
119 UPDATE az_requests req
120 SET req.active = (
121 SELECT decode(COUNT(api.active), 0, 'N', 'R')
122 FROM az_structures_b strct, az_apis api
123 WHERE strct.structure_code = -- query b
124 (SELECT extractvalue(VALUE(e),
125 '/H/V[@N="StructureCode"]/text()')
126 FROM az_requests d,
127 TABLE(xmlsequence(extract(d.selection_set,
128 '/EXT/H/V[@N="StructureCode"]/..'))) e
129 WHERE req.job_name = d.job_name
130 AND req.user_id = d.user_id
131 AND req.request_type = d.request_type
132 AND d.selection_set IS NOT NULL)
133 AND strct.active = 'N'
134 AND api.api_code IN -- 2
135 (SELECT api_code
136 FROM az_structure_apis_b
137 WHERE structure_code IN
138 (SELECT extractvalue(VALUE(e),
139 '/H/V[@N="StructureCode"]/text()')
140 FROM az_requests d,
141 TABLE(xmlsequence(extract(d.selection_set,
142 '/EXT/H/V[@N="StructureCode"]/..'))) e
143 WHERE d.job_name = req.job_name
144 AND d.request_type = req.request_type
145 AND d.user_id = req.user_id -- 2's first where
146 AND d.selection_set IS NOT NULL)
147 AND entity_code IN (SELECT extractvalue(VALUE(e),
148 '/H/V[@N="EntityOccuranceCode"]/text()')
149 FROM az_requests d,
150 TABLE(xmlsequence(extract(d.selection_set,
151 '/EXT/H/V[@N="EntityOccuranceCode"]/..'))) e
152 WHERE d.job_name = req.job_name
153 AND d.request_type = req.request_type
154 AND d.user_id = req.user_id -- 2's second where
155 AND d.selection_set IS NOT NULL))
156 AND api.active = 'Y')
157 WHERE req.request_type = 'E'
158 AND req.request_phase = 'C'
159 AND req.request_status = 'C'
160 AND req.selection_set IS NOT NULL;
161
162 UPDATE az_requests req
163 SET req.active = (decode(
164 (SELECT 'Y'
165 FROM az_structures_b strct
166 WHERE structure_code = -- query b
167 (SELECT extractvalue(VALUE(e),
168 '/H/V[@N="StructureCode"]/text()')
169 FROM az_requests d,
170 TABLE(xmlsequence(extract(d.selection_set,
171 '/EXT/H/V[@N="StructureCode"]/..'))) e
172 WHERE req.job_name = d.job_name
173 AND req.user_id = d.user_id
174 AND req.request_type = d.request_type
175 AND d.selection_set IS NOT NULL)
176 AND strct.active <> 'N'
177 AND strct.active IS NOT NULL),null,req.active,'Y'))
178 WHERE req.request_type = 'E'
179 AND req.request_phase = 'C'
180 AND req.request_status = 'C'
181 AND req.selection_set IS NOT NULL;
182
183 COMMIT;
184
185 END validate_extracts;
186
187 FUNCTION validate_active_request(p_job_name IN VARCHAR2,
188 p_request_type IN VARCHAR2,
189 p_user_id IN NUMBER) RETURN VARCHAR2 AS
190 v_active VARCHAR2(1);
191 BEGIN
192
193 activate_apis;
194
195 UPDATE az_requests
196 SET active='N'
197 WHERE selection_set IS NULL
198 AND job_name=p_job_name
199 AND request_type=p_request_type
200 AND user_id=p_user_id;
201
202 UPDATE az_requests req
203 SET req.active = (
204 SELECT decode(COUNT(api.active), 0, 'N', 'R')
205 FROM az_structures_b strct, az_apis api
206 WHERE strct.structure_code = -- query b
207 (SELECT extractvalue(VALUE(e),
208 '/H/V[@N="StructureCode"]/text()')
209 FROM az_requests d,
210 TABLE(xmlsequence(extract(d.selection_set,
211 '/EXT/H/V[@N="StructureCode"]/..'))) e
212 WHERE req.job_name = d.job_name
213 AND req.user_id = d.user_id
214 AND req.request_type = d.request_type
215 AND d.selection_set IS NOT NULL)
216 AND strct.active = 'N'
217 AND api.api_code IN -- 2
218 (SELECT api_code
219 FROM az_structure_apis_b
220 WHERE structure_code IN
221 (SELECT extractvalue(VALUE(e),
222 '/H/V[@N="StructureCode"]/text()')
223 FROM az_requests d,
224 TABLE(xmlsequence(extract(d.selection_set,
225 '/EXT/H/V[@N="StructureCode"]/..'))) e
226 WHERE d.job_name = req.job_name
227 AND d.request_type = req.request_type
228 AND d.user_id = req.user_id -- 2's first where
229 AND d.selection_set IS NOT NULL)
230 AND entity_code IN (SELECT extractvalue(VALUE(e),
231 '/H/V[@N="EntityOccuranceCode"]/text()')
232 FROM az_requests d,
233 TABLE(xmlsequence(extract(d.selection_set,
234 '/EXT/H/V[@N="EntityOccuranceCode"]/..'))) e
235 WHERE d.job_name = req.job_name
236 AND d.request_type = req.request_type
237 AND d.user_id = req.user_id -- 2's second where
238 AND d.selection_set IS NOT NULL))
239 AND api.active = 'Y')
240 WHERE req.selection_set IS NOT NULL
241 AND req.job_name=p_job_name
242 AND req.request_type=p_request_type
243 AND req.user_id=p_user_id;
244
245 UPDATE az_requests req
246 SET req.active = (decode(
247 (SELECT 'Y'
248 FROM az_structures_b strct
249 WHERE structure_code = -- query b
250 (SELECT extractvalue(VALUE(e),
251 '/H/V[@N="StructureCode"]/text()')
252 FROM az_requests d,
253 TABLE(xmlsequence(extract(d.selection_set,
254 '/EXT/H/V[@N="StructureCode"]/..'))) e
255 WHERE req.job_name = d.job_name
256 AND req.user_id = d.user_id
257 AND req.request_type = d.request_type
258 AND d.selection_set IS NOT NULL)
259 AND strct.active <> 'N'
260 AND strct.active IS NOT NULL),null,req.active,'Y'))
261 WHERE req.selection_set IS NOT NULL
262 AND req.job_name=p_job_name
263 AND req.request_type=p_request_type
264 AND req.user_id=p_user_id;
265
266 COMMIT;
267
268 SELECT active
269 INTO v_active
270 FROM az_requests
271 WHERE job_name = p_job_name
272 AND request_type = p_request_type
273 AND user_id = p_user_id;
274
275 RETURN v_active;
276
277 END validate_active_request;
278
279 PROCEDURE validate_active AS
280 BEGIN
281
282 validate_selsets;
283 validate_extracts;
284 validate_loads;
285 validate_transforms;
286
287 END validate_active;
288
289 END az_validate_active;
[Home] [Help]