[Home] [Help]
PACKAGE BODY: APPS.AHL_WBS_RULES_PVT
Source
4 G_PKG_NAME VARCHAR2(30) := 'AHL_WBS_RULES_PVT';
1 PACKAGE BODY AHL_WBS_RULES_PVT AS
2 /* $Header: AHLVWBSB.pls 120.0.12020000.2 2012/12/11 05:41:09 prakkum noship $ */
3
5 G_DEBUG VARCHAR2(1) := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
6
7 -- constants for WHO Columns
8 G_LAST_UPDATE_DATE DATE := SYSDATE;
9 G_LAST_UPDATED_BY NUMBER(15) := FND_GLOBAL.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER(15) := FND_GLOBAL.login_id;
11 G_CREATION_DATE DATE := SYSDATE;
12 G_CREATED_BY NUMBER(15) := FND_GLOBAL.user_id;
13
14 -- Log constants and variables
15 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
16 l_log_statement NUMBER := fnd_log.level_statement;
17 l_log_procedure NUMBER := fnd_log.level_procedure;
18
19 -- Constants specific to WBS
20 G_SCH_WO VARCHAR2(30) := 'Schedule Workorders with ';
21 G_BEF_WO VARCHAR2(30) := ' Before Workorders with ';
22
23 -- Utility function used by 'validate_rule' function
24 -- Joins the attributes and formulates rule statement for one part of the rule
25 FUNCTION formulate_rule_stmt
26 (
27 p_route_num VARCHAR2,
28 p_route_type VARCHAR2,
29 p_system VARCHAR2,
30 p_process VARCHAR2
31 )
32 RETURN VARCHAR2
33 IS
34 L_API_VERSION CONSTANT NUMBER := 1.0;
35 L_API_NAME CONSTANT VARCHAR2(30) := 'formulate_rule_stmt';
36 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
37 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
38
39 l_rule_stmt VARCHAR2(500) := NULL;
40
41 BEGIN
42 IF (l_log_procedure >= l_log_current_level) THEN
43 FND_LOG.string(l_log_procedure,L_DEBUG||'.begin','At the start of function');
44 END IF;
45
46 -- Adding Route
47 IF(p_route_num is NOT NULL) THEN
51 -- Adding Route Type
48 l_rule_stmt := 'Route=' || p_route_num;
49 END IF;
50
52 IF(p_route_type is NOT NULL) THEN
53 IF(l_rule_stmt is NOT NULL) THEN
54 l_rule_stmt := l_rule_stmt || ' AND Route Type=' || p_route_type;
55 ELSE
56 l_rule_stmt := ' Route Type=' || p_route_type;
57 END IF;
58 END IF;
59
60 -- Adding System
61 IF(p_system is NOT NULL) THEN
62 IF(l_rule_stmt is NOT NULL) THEN
63 l_rule_stmt := l_rule_stmt || ' AND System=' || p_system;
64 ELSE
65 l_rule_stmt := ' System=' || p_system;
66 END IF;
67 END IF;
68
69 -- Adding Process
70 IF(p_process is NOT NULL) THEN
71 IF(l_rule_stmt is NOT NULL) THEN
72 l_rule_stmt := l_rule_stmt || ' AND Process=' || p_process;
73 ELSE
74 l_rule_stmt := ' Process=' || p_process;
75 END IF;
76 END IF;
77
78 IF (l_log_statement >= l_log_current_level) THEN
79 FND_LOG.string(l_log_statement, L_DEBUG, 'l_rule_stmt = ' || l_rule_stmt);
80 END IF;
81
82 IF (l_log_procedure >= l_log_current_level) THEN
83 FND_LOG.string(l_log_procedure,L_DEBUG||'.end','At the end of function');
84 END IF;
85
86 RETURN l_rule_stmt;
87 END formulate_rule_stmt;
88
89
90 -- Perform all validations on the rule attributes
91 PROCEDURE validate_rule
92 (
93 p_x_wbs_rules_rec IN OUT NOCOPY wbs_rules_rec_type,
94 x_return_status IN OUT NOCOPY VARCHAR2
95 )
96 IS
97
98 CURSOR CHK_ROUTE_NUM(c_route_num VARCHAR2)
99 IS
100 SELECT count(route_no)
101 FROM ahl_routes_b
102 WHERE sysdate BETWEEN nvl(start_date_active, sysdate) AND nvl(end_date_active, sysdate)
103 AND route_no = c_route_num;
104
105 CURSOR CHK_ROUTE_TYPE(c_route_type VARCHAR2)
106 IS
107 SELECT count(lookup_code)
108 FROM fnd_lookup_values_vl
109 WHERE lookup_type='AHL_ROUTE_TYPE'
110 AND sysdate BETWEEN NVL(start_date_active,sysdate) AND nvl(end_date_active,sysdate)
111 AND lookup_code = c_route_type;
112
113 CURSOR CHK_SYSTEM(c_system VARCHAR2)
114 IS
115 SELECT count(CONCATENATED_SEGMENTS)
116 FROM AHL_ROUTES_B_KFV
117 WHERE CONCATENATED_SEGMENTS = c_system;
118
119 CURSOR CHK_PROCESS(c_process VARCHAR2)
120 IS
121 SELECT count(lookup_code)
122 FROM fnd_lookup_values_vl
123 WHERE lookup_type='AHL_PROCESS_CODE'
124 AND sysdate BETWEEN NVL(start_date_active,sysdate) AND nvl(end_date_active,sysdate)
125 AND lookup_code = c_process;
126
127 CURSOR CHK_DUP_RULE(c_rule_text VARCHAR2)
128 IS
129 SELECT count(RULE_ID)
130 FROM AHL_WBS_RULES
131 WHERE RULE_TEXT = c_rule_text
132 AND RULE_ID <> p_x_wbs_rules_rec.RULE_ID;
133
134 L_API_VERSION CONSTANT NUMBER := 1.0;
135 L_API_NAME CONSTANT VARCHAR2(30) := 'validate_rule';
136 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
137 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
138
139 L_PRE_ROUTE AHL_WBS_RULES.PRE_ROUTE_NUMBER%TYPE := p_x_wbs_rules_rec.PRE_ROUTE_NUMBER;
140 L_PRE_ROUTE_TYPE_CODE AHL_WBS_RULES.PRE_ROUTE_TYPE_CODE%TYPE := p_x_wbs_rules_rec.PRE_ROUTE_TYPE_CODE;
141 L_PRE_SYSTEM_CODE AHL_WBS_RULES.PRE_SYSTEM_CODE%TYPE := p_x_wbs_rules_rec.PRE_SYSTEM_CODE;
142 L_PRE_PROCESS_CODE AHL_WBS_RULES.PRE_PROCESS_CODE%TYPE := p_x_wbs_rules_rec.PRE_PROCESS_CODE;
143 L_POST_ROUTE AHL_WBS_RULES.POST_ROUTE_NUMBER%TYPE := p_x_wbs_rules_rec.POST_ROUTE_NUMBER;
144 L_POST_ROUTE_TYPE_CODE AHL_WBS_RULES.POST_ROUTE_TYPE_CODE%TYPE := p_x_wbs_rules_rec.POST_ROUTE_TYPE_CODE;
145 L_POST_SYSTEM_CODE AHL_WBS_RULES.POST_SYSTEM_CODE%TYPE := p_x_wbs_rules_rec.POST_SYSTEM_CODE;
146 L_POST_PROCESS_CODE AHL_WBS_RULES.POST_PROCESS_CODE%TYPE := p_x_wbs_rules_rec.POST_PROCESS_CODE;
147 L_PRE_ROUTE_TYPE_MEANING VARCHAR2(80) := p_x_wbs_rules_rec.PRE_ROUTE_TYPE_MEANING;
148 L_PRE_PROCESS_MEANING VARCHAR2(80) := p_x_wbs_rules_rec.PRE_PROCESS_MEANING;
149 L_POST_ROUTE_TYPE_MEANING VARCHAR2(80) := p_x_wbs_rules_rec.POST_ROUTE_TYPE_MEANING;
150 L_POST_PROCESS_MEANING VARCHAR2(80) := p_x_wbs_rules_rec.POST_PROCESS_MEANING;
151
152 L_RULE_PRE_PART VARCHAR2(500);
153 L_RULE_POST_PART VARCHAR2(500);
154 L_DUMMY_COUNT NUMBER;
155
156 BEGIN
157 IF (l_log_procedure >= l_log_current_level) THEN
158 FND_LOG.string(l_log_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
159 END IF;
160
161 --Check if Route is same on both sides of the Rule
162 IF(L_PRE_ROUTE = L_POST_ROUTE) THEN
163 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_SAME_ROUTE' );
167
164 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
165 FND_MSG_PUB.add;
166 x_return_status := FND_API.G_RET_STS_ERROR;
168 IF (l_log_statement >= l_log_current_level) THEN
169 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Same Route on both Sides');
170 END IF;
171 END IF;
172
173 L_RULE_PRE_PART := formulate_rule_stmt(L_PRE_ROUTE, L_PRE_ROUTE_TYPE_MEANING, L_PRE_SYSTEM_CODE, L_PRE_PROCESS_MEANING);
174 L_RULE_POST_PART := formulate_rule_stmt(L_POST_ROUTE, L_POST_ROUTE_TYPE_MEANING, L_POST_SYSTEM_CODE, L_POST_PROCESS_MEANING);
175
176 --Check if Rule is filled on both sides
177 IF (L_RULE_PRE_PART = '' OR L_RULE_POST_PART = '') THEN
178 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_INCOMPLETE_RULE' );
179 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
180 FND_MSG_PUB.add;
181 x_return_status := FND_API.G_RET_STS_ERROR;
182 IF (l_log_statement >= l_log_current_level) THEN
183 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Incomplete Rule');
184 END IF;
185 END IF;
186
187 --Check if both part of the rule is exactly same
188 IF(L_RULE_PRE_PART = L_RULE_POST_PART) THEN
189 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_PRE_POST_SAME' );
190 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
191 FND_MSG_PUB.add;
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 IF (l_log_statement >= l_log_current_level) THEN
194 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Both part are same');
195 END IF;
196 END IF;
197
198 --Check if same set of attributes are used on both sides
199 IF((L_PRE_ROUTE IS NULL AND L_POST_ROUTE IS NOT NULL)
200 OR (L_PRE_ROUTE IS NOT NULL AND L_POST_ROUTE IS NULL)) THEN
201 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_DIFF_ATTR' );
202 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
203 FND_MSG_PUB.add;
204 x_return_status := FND_API.G_RET_STS_ERROR;
205 IF (l_log_statement >= l_log_current_level) THEN
206 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Not Same Attributes');
207 END IF;
208
209 ELSIF ((L_PRE_ROUTE_TYPE_CODE IS NULL AND L_POST_ROUTE_TYPE_CODE IS NOT NULL)
210 OR (L_PRE_ROUTE_TYPE_CODE IS NOT NULL AND L_POST_ROUTE_TYPE_CODE IS NULL)) THEN
211 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_DIFF_ATTR' );
212 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
213 FND_MSG_PUB.add;
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 IF (l_log_statement >= l_log_current_level) THEN
216 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Not Same Attributes');
217 END IF;
218
219 ELSIF ((L_PRE_SYSTEM_CODE IS NULL AND L_POST_SYSTEM_CODE IS NOT NULL)
220 OR (L_PRE_SYSTEM_CODE IS NOT NULL AND L_POST_SYSTEM_CODE IS NULL)) THEN
221 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_DIFF_ATTR' );
222 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
223 FND_MSG_PUB.add;
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 IF (l_log_statement >= l_log_current_level) THEN
226 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Not Same Attributes');
227 END IF;
228
229 ELSIF ((L_PRE_PROCESS_CODE IS NULL AND L_POST_PROCESS_CODE IS NOT NULL)
230 OR (L_PRE_PROCESS_CODE IS NOT NULL AND L_POST_PROCESS_CODE IS NULL)) THEN
231 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_DIFF_ATTR' );
232 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
233 FND_MSG_PUB.add;
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 IF (l_log_statement >= l_log_current_level) THEN
236 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Not Same Attributes');
237 END IF;
238 END IF;
239
240 --Check for the existence of all attributes in DB
241 --Start of individual attribute validation-------------------------------
242 --Check PRE_ROUTE_NUMBER
243 IF (L_PRE_ROUTE IS NOT NULL) THEN
244 OPEN CHK_ROUTE_NUM(L_PRE_ROUTE);
245 FETCH CHK_ROUTE_NUM into L_DUMMY_COUNT;
246 CLOSE CHK_ROUTE_NUM;
247 IF(L_DUMMY_COUNT < 1) THEN
248 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_INV_ROUTE' );
249 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
250 FND_MSG_PUB.add;
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 IF (l_log_statement >= l_log_current_level) THEN
256 END IF;
253 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Invalid PreRoute');
254 END IF;
255 END IF;
257
258 --Check PRE_ROUTE_TYPE_CODE
259 IF (L_PRE_ROUTE_TYPE_CODE IS NOT NULL) THEN
260 OPEN CHK_ROUTE_TYPE(L_PRE_ROUTE_TYPE_CODE);
261 FETCH CHK_ROUTE_TYPE into L_DUMMY_COUNT;
262 CLOSE CHK_ROUTE_TYPE;
263 IF(L_DUMMY_COUNT < 1) THEN
264 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_INV_ROUTE_TYPE' );
265 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
266 FND_MSG_PUB.add;
267 x_return_status := FND_API.G_RET_STS_ERROR;
268 IF (l_log_statement >= l_log_current_level) THEN
269 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Invalid PreRouteType');
270 END IF;
271 END IF;
272 END IF;
273
274 --Check PRE_SYSTEM_CODE
275 IF (L_PRE_SYSTEM_CODE IS NOT NULL) THEN
276 OPEN CHK_SYSTEM(L_PRE_SYSTEM_CODE);
277 FETCH CHK_SYSTEM into L_DUMMY_COUNT;
278 CLOSE CHK_SYSTEM;
279 IF(L_DUMMY_COUNT < 1) THEN
280 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_INV_SYSTEM' );
281 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
282 FND_MSG_PUB.add;
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 IF (l_log_statement >= l_log_current_level) THEN
285 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Invalid PreSystem');
286 END IF;
287 END IF;
288 END IF;
289
293 FETCH CHK_PROCESS into L_DUMMY_COUNT;
290 --Check PRE_PROCESS_CODE
291 IF (L_PRE_PROCESS_CODE IS NOT NULL) THEN
292 OPEN CHK_PROCESS(L_PRE_PROCESS_CODE);
294 CLOSE CHK_PROCESS;
295 IF(L_DUMMY_COUNT < 1) THEN
296 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_INV_PROCESS' );
297 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
298 FND_MSG_PUB.add;
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 IF (l_log_statement >= l_log_current_level) THEN
301 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Invalid PreProcess');
302 END IF;
303 END IF;
304 END IF;
305
306 --Check POST_ROUTE_NUMBER
307 IF (L_POST_ROUTE IS NOT NULL) THEN
308 OPEN CHK_ROUTE_NUM(L_POST_ROUTE);
309 FETCH CHK_ROUTE_NUM into L_DUMMY_COUNT;
310 CLOSE CHK_ROUTE_NUM;
311 IF(L_DUMMY_COUNT < 1) THEN
312 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_INV_ROUTE' );
313 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
314 FND_MSG_PUB.add;
315 x_return_status := FND_API.G_RET_STS_ERROR;
316 IF (l_log_statement >= l_log_current_level) THEN
317 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Invalid PostRoute');
318 END IF;
319 END IF;
320 END IF;
321
322 --Check POST_ROUTE_TYPE_CODE
323 IF (L_POST_ROUTE_TYPE_CODE IS NOT NULL) THEN
324 OPEN CHK_ROUTE_TYPE(L_POST_ROUTE_TYPE_CODE);
325 FETCH CHK_ROUTE_TYPE into L_DUMMY_COUNT;
326 CLOSE CHK_ROUTE_TYPE;
327 IF(L_DUMMY_COUNT < 1) THEN
328 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_INV_ROUTE_TYPE' );
329 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
330 FND_MSG_PUB.add;
331 x_return_status := FND_API.G_RET_STS_ERROR;
332 IF (l_log_statement >= l_log_current_level) THEN
333 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Invalid PostRouteType');
334 END IF;
335 END IF;
336 END IF;
337
338 --Check POST_SYSTEM_CODE
339 IF (L_POST_SYSTEM_CODE IS NOT NULL) THEN
340 OPEN CHK_SYSTEM(L_POST_SYSTEM_CODE);
341 FETCH CHK_SYSTEM into L_DUMMY_COUNT;
342 CLOSE CHK_SYSTEM;
343 IF(L_DUMMY_COUNT < 1) THEN
344 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_INV_SYSTEM' );
345 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
346 FND_MSG_PUB.add;
347 x_return_status := FND_API.G_RET_STS_ERROR;
348 IF (l_log_statement >= l_log_current_level) THEN
349 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Invalid PostSystem');
350 END IF;
351 END IF;
352 END IF;
353
354 --Check POST_PROCESS_CODE
355 IF (L_POST_PROCESS_CODE IS NOT NULL) THEN
356 OPEN CHK_PROCESS(L_POST_PROCESS_CODE);
357 FETCH CHK_PROCESS into L_DUMMY_COUNT;
358 CLOSE CHK_PROCESS;
359 IF(L_DUMMY_COUNT < 1) THEN
360 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_INV_PROCESS' );
361 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
362 FND_MSG_PUB.add;
363 x_return_status := FND_API.G_RET_STS_ERROR;
364 IF (l_log_statement >= l_log_current_level) THEN
368 END IF;
365 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Invalid PostProcess');
366 END IF;
367 END IF;
369 --End of individual attribute validation---------------------------------
370
371 --Check for Duplicate Rule
372 OPEN CHK_DUP_RULE(G_SCH_WO || L_RULE_PRE_PART || G_BEF_WO || L_RULE_POST_PART);
373 FETCH CHK_DUP_RULE INTO L_DUMMY_COUNT;
374 CLOSE CHK_DUP_RULE;
375 IF(L_DUMMY_COUNT > 0) THEN
376 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_DUP_RULE' );
377 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
378 FND_MSG_PUB.add;
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 IF (l_log_statement >= l_log_current_level) THEN
381 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': Duplicate Rule');
382 END IF;
383 END IF;
384
385 --Check for inverse rule
386 OPEN CHK_DUP_RULE(G_SCH_WO || L_RULE_POST_PART || G_BEF_WO || L_RULE_PRE_PART);
387 FETCH CHK_DUP_RULE INTO L_DUMMY_COUNT;
388 CLOSE CHK_DUP_RULE;
389 IF(L_DUMMY_COUNT > 0) THEN
390 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_INVERSE_RULE' );
391 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
392 FND_MSG_PUB.add;
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 IF (l_log_statement >= l_log_current_level) THEN
395 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || 'Inverse of Existing Rule');
396 END IF;
397 END IF;
398
399 --If validations are over, write rule text into the Record type, so that insertion/updation is easier
400 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
401 p_x_wbs_rules_rec.RULE_TEXT := G_SCH_WO || L_RULE_PRE_PART || G_BEF_WO || L_RULE_POST_PART;
402 END IF;
403
404 IF (l_log_procedure >= l_log_current_level) THEN
405 FND_LOG.string(l_log_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
406 END IF;
407 END validate_rule;
408
409
410 -- Validation for Non-Cyclic Workorders dependency check.
411 PROCEDURE validate_cyclic_routes
412 (
413 p_x_wbs_rules_rec IN OUT NOCOPY wbs_rules_rec_type,
414 x_return_status IN OUT NOCOPY VARCHAR2,
415 x_msg_count IN OUT NOCOPY NUMBER,
416 x_msg_data IN OUT NOCOPY VARCHAR2
417 )
418 IS
419
420 L_API_NAME CONSTANT VARCHAR2(30) := 'validate_cyclic_routes';
421 l_cyclic_loop EXCEPTION;
422 PRAGMA EXCEPTION_INIT(l_cyclic_loop,-1436);
423 l_counter NUMBER;
424 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'|| G_PKG_NAME || '.' || L_API_NAME;
425
426 BEGIN
427
428 SELECT COUNT(*) INTO l_counter
429 FROM ahl_wbs_rules
430 START WITH POST_ROUTE_NUMBER = p_x_wbs_rules_rec.PRE_ROUTE_NUMBER
431 CONNECT BY PRIOR PRE_ROUTE_NUMBER = POST_ROUTE_NUMBER;
432
433 EXCEPTION
437 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
434 WHEN l_cyclic_loop THEN
435 x_return_status := FND_API.G_RET_STS_ERROR;
436 FND_MESSAGE.set_name( 'AHL', 'AHL_WBS_CYC_ROUTE' );
438 FND_MSG_PUB.add;
439 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
440 p_count => x_msg_count,
441 p_data => x_msg_data);
442 IF (l_log_statement >= l_log_current_level) THEN
443 FND_LOG.string(l_log_statement, L_DEBUG, 'validate_cyclic_routes: rule_id-' || p_x_wbs_rules_rec.rule_id || 'In l_cyclic_loop block' || ' x_msg_count ' || x_msg_count );
444 END IF;
445 WHEN OTHERS THEN
446 ROLLBACK TO update_rule_sp;
447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
448 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
449 FND_MSG_PUB.add_exc_msg (G_PKG_NAME, L_API_NAME);
450 END IF;
451 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
452 p_count => x_msg_count,
453 p_data => x_msg_data);
454 IF (l_log_statement >= l_log_current_level) THEN
455 FND_LOG.string(l_log_statement, L_DEBUG, 'validate_cyclic_routes: rule_id-' || p_x_wbs_rules_rec.rule_id || 'In OTHERS block' || ' x_msg_count ' || x_msg_count );
456 END IF;
457
458 END validate_cyclic_routes;
459
460
461 -- Procedure to Create WBS Rules
462 -- Called by the EO of the UI
463 PROCEDURE create_rule
464 (
465 p_api_version IN NUMBER := 1.0,
466 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
467 p_commit IN VARCHAR2 := FND_API.G_FALSE,
468 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
469 p_module_type IN VARCHAR2 := NULL,
470 x_return_status OUT NOCOPY VARCHAR2,
471 x_msg_count OUT NOCOPY NUMBER,
472 x_msg_data OUT NOCOPY VARCHAR2,
473 p_x_wbs_rules_rec IN OUT NOCOPY wbs_rules_rec_type
474 )
475 IS
476
477 L_API_VERSION CONSTANT NUMBER := 1.0;
478 L_API_NAME CONSTANT VARCHAR2(30) := 'create_rule';
479 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
480 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
481
482 BEGIN
483
484 -- Standard Start of API savepoint
485 SAVEPOINT create_rule_sp;
486
487 IF (l_log_procedure >= l_log_current_level) THEN
488 FND_LOG.string(l_log_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
489 END IF;
490
491 -- Initialize message list if p_init_msg_list is set to TRUE.
492 IF FND_API.to_boolean( p_init_msg_list ) THEN
493 FND_MSG_PUB.initialize;
494 END IF;
495
496 -- Initialize API return status to success
497 x_return_status := FND_API.G_RET_STS_SUCCESS;
498
499 -- Standard call to check for call compatibility.
500 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
502 END IF;
503
504 ------------------------Start of API Body------------------------------------
505
506 --Perform all validations through the helper API
507 validate_rule(p_x_wbs_rules_rec, x_return_status);
508
509 IF (l_log_statement >= l_log_current_level) THEN
510 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': x_return_status after call to validate_rule' || x_return_status);
511 END IF;
512
513 --If Return Status is successful, then Insert the Rule
514 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
515 IF (l_log_statement >= l_log_current_level) THEN
516 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': About to insert rule');
517 END IF;
518
519 INSERT INTO AHL_WBS_RULES
520 (
521 RULE_ID,
522 OBJECT_VERSION_NUMBER,
523 LAST_UPDATE_DATE,
524 LAST_UPDATED_BY,
525 CREATION_DATE,
526 CREATED_BY,
527 LAST_UPDATE_LOGIN,
528 RULE_NUMBER,
529 RULE_TEXT,
530 PRE_ROUTE_NUMBER,
531 PRE_ROUTE_TYPE_CODE,
532 PRE_SYSTEM_CODE,
533 PRE_PROCESS_CODE,
534 POST_ROUTE_NUMBER,
535 POST_ROUTE_TYPE_CODE,
536 POST_SYSTEM_CODE,
537 POST_PROCESS_CODE,
538 SECURITY_GROUP_ID
539 )
540 VALUES
541 (
542 p_x_wbs_rules_rec.RULE_ID,
543 1,
544 G_LAST_UPDATE_DATE,
545 G_LAST_UPDATED_BY,
546 G_CREATION_DATE,
547 G_CREATED_BY,
548 G_LAST_UPDATE_LOGIN,
552 p_x_wbs_rules_rec.PRE_ROUTE_TYPE_CODE,
549 p_x_wbs_rules_rec.RULE_NUMBER,
550 p_x_wbs_rules_rec.RULE_TEXT,
551 p_x_wbs_rules_rec.PRE_ROUTE_NUMBER,
553 p_x_wbs_rules_rec.PRE_SYSTEM_CODE,
554 p_x_wbs_rules_rec.PRE_PROCESS_CODE,
555 p_x_wbs_rules_rec.POST_ROUTE_NUMBER,
556 p_x_wbs_rules_rec.POST_ROUTE_TYPE_CODE,
557 p_x_wbs_rules_rec.POST_SYSTEM_CODE,
558 p_x_wbs_rules_rec.POST_PROCESS_CODE,
559 null
560 );
561
562 IF ( SQL%ROWCOUNT = 0 ) THEN
563 FND_MESSAGE.set_name('AHL','AHL_WBS_RECORD_CHANGED');
564 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
565 FND_MSG_PUB.add;
566 x_return_status := FND_API.G_RET_STS_ERROR;
567 ELSE
568 -- Check whether Routes form a cycle among the rules. Cannot perform this check before inserting
569 validate_cyclic_routes(p_x_wbs_rules_rec, x_return_status, x_msg_count, x_msg_data);
570 END IF;
571
572 END IF;
573
574 ------------------------End of API Body--------------------------------------
575
576 --Standard check for commit
577 IF (FND_API.TO_BOOLEAN(p_commit) AND x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
578 COMMIT;
579 END IF;
580
581 -- Count and Get messages
582 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
583 p_count => x_msg_count,
584 p_data => x_msg_data);
585
586 IF (l_log_procedure >= l_log_current_level) THEN
590 EXCEPTION
587 FND_LOG.string(l_log_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
588 END IF;
589
591 WHEN FND_API.G_EXC_ERROR THEN
592 ROLLBACK TO create_rule_sp;
593 x_return_status := FND_API.G_RET_STS_ERROR;
594 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
595 p_count => x_msg_count,
596 p_data => x_msg_data);
597 IF (l_log_statement >= l_log_current_level) THEN
598 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || 'In G_EXC_ERROR block' || ' x_msg_count ' || x_msg_count );
599 END IF;
600
601 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
602 ROLLBACK TO create_rule_sp;
603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
604 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
605 p_count => x_msg_count,
606 p_data => x_msg_data);
607 IF (l_log_statement >= l_log_current_level) THEN
608 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || 'In G_EXC_UNEXPECTED_ERROR block' || ' x_msg_count ' || x_msg_count );
609 END IF;
610
611 WHEN OTHERS THEN
612 ROLLBACK TO create_rule_sp;
613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
614 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
615 FND_MSG_PUB.add_exc_msg (G_PKG_NAME, L_API_NAME);
616 END IF;
617 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
618 p_count => x_msg_count,
619 p_data => x_msg_data);
620 IF (l_log_statement >= l_log_current_level) THEN
621 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || 'In OTHERS block' || ' x_msg_count ' || x_msg_count );
622 END IF;
623 END create_rule;
624
625
626 -- Procedure to Update WBS Rules
627 -- Called by the EO of the UI
628 PROCEDURE update_rule
629 (
630 p_api_version IN NUMBER := 1.0,
631 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
632 p_commit IN VARCHAR2 := FND_API.G_FALSE,
633 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
634 p_module_type IN VARCHAR2 := NULL,
635 x_return_status OUT NOCOPY VARCHAR2,
636 x_msg_count OUT NOCOPY NUMBER,
637 x_msg_data OUT NOCOPY VARCHAR2,
638 p_x_wbs_rules_rec IN OUT NOCOPY wbs_rules_rec_type
639 )
640 IS
641
642 L_API_VERSION CONSTANT NUMBER := 1.0;
643 L_API_NAME CONSTANT VARCHAR2(30) := 'update_rule';
644 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
648 BEGIN
645 L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
646 L_OVN NUMBER;
647
649
650 -- Standard Start of API savepoint
651 SAVEPOINT update_rule_sp;
652
653 IF (l_log_procedure >= l_log_current_level) THEN
654 FND_LOG.string(l_log_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
655 END IF;
656
657 -- Initialize message list if p_init_msg_list is set to TRUE.
658 IF FND_API.to_boolean( p_init_msg_list ) THEN
659 FND_MSG_PUB.initialize;
660 END IF;
661
662 -- Initialize API return status to success
663 x_return_status := FND_API.G_RET_STS_SUCCESS;
664
665 -- Standard call to check for call compatibility.
666 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
668 END IF;
669
670 ------------------------Start of API Body------------------------------------
671
672 --Perform all validations through the helper API
673 validate_rule(p_x_wbs_rules_rec, x_return_status);
674
675 IF (l_log_statement >= l_log_current_level) THEN
676 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': x_return_status after call to validate_rule' || x_return_status);
677 END IF;
678
679 --If Return Status is successful, then Update the Rule
680 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
681 IF (l_log_statement >= l_log_current_level) THEN
682 FND_LOG.string(l_log_statement, L_DEBUG, 'rule_id-' || p_x_wbs_rules_rec.rule_id || ': About to update rule');
683 END IF;
684
685 UPDATE AHL_WBS_RULES
686 SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
687 LAST_UPDATE_DATE = G_LAST_UPDATE_DATE,
688 LAST_UPDATED_BY = G_LAST_UPDATED_BY,
689 LAST_UPDATE_LOGIN = G_LAST_UPDATE_LOGIN,
690 RULE_TEXT = p_x_wbs_rules_rec.RULE_TEXT,
691 PRE_ROUTE_NUMBER = p_x_wbs_rules_rec.PRE_ROUTE_NUMBER,
692 PRE_ROUTE_TYPE_CODE = p_x_wbs_rules_rec.PRE_ROUTE_TYPE_CODE,
693 PRE_SYSTEM_CODE = p_x_wbs_rules_rec.PRE_SYSTEM_CODE,
694 PRE_PROCESS_CODE = p_x_wbs_rules_rec.PRE_PROCESS_CODE,
695 POST_ROUTE_NUMBER = p_x_wbs_rules_rec.POST_ROUTE_NUMBER,
696 POST_ROUTE_TYPE_CODE = p_x_wbs_rules_rec.POST_ROUTE_TYPE_CODE,
697 POST_SYSTEM_CODE = p_x_wbs_rules_rec.POST_SYSTEM_CODE,
698 POST_PROCESS_CODE = p_x_wbs_rules_rec.POST_PROCESS_CODE
699 WHERE RULE_ID = p_x_wbs_rules_rec.rule_id
700 AND object_version_number = p_x_wbs_rules_rec.object_version_number;
701
702 IF ( SQL%ROWCOUNT = 0 ) THEN
703 FND_MESSAGE.set_name('AHL','AHL_WBS_RECORD_CHANGED');
704 FND_MESSAGE.set_token( 'RULE_NO', p_x_wbs_rules_rec.RULE_NUMBER );
705 FND_MSG_PUB.add;
706 x_return_status := FND_API.G_RET_STS_ERROR;
707 ELSE
708 -- Check whether Routes form a cycle among the rules. Cannot perform this check before updating
709 validate_cyclic_routes(p_x_wbs_rules_rec, x_return_status, x_msg_count, x_msg_data);
710 END IF;
711
712 END IF;
713
714 ------------------------End of API Body--------------------------------------
715
716 --Standard check for commit
717 IF (FND_API.TO_BOOLEAN(p_commit) AND x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
718 COMMIT;
719 END IF;
720
721 -- Count and Get messages
722 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
723 p_count => x_msg_count,
727 FND_LOG.string(l_log_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
724 p_data => x_msg_data);
725
726 IF (l_log_procedure >= l_log_current_level) THEN
728 END IF;
729
730 EXCEPTION
731 WHEN FND_API.G_EXC_ERROR THEN
732 ROLLBACK TO update_rule_sp;
733 x_return_status := FND_API.G_RET_STS_ERROR;
734 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
735 p_count => x_msg_count,
736 p_data => x_msg_data);
737 IF (l_log_statement >= l_log_current_level) THEN
738 FND_LOG.string(l_log_statement, L_DEBUG, 'Update Rule: rule_id-' || p_x_wbs_rules_rec.rule_id || 'In G_EXC_ERROR block' || ' x_msg_count ' || x_msg_count );
739 END IF;
740
741 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745 p_count => x_msg_count,
742 ROLLBACK TO update_rule_sp;
743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
744 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
746 p_data => x_msg_data);
747 IF (l_log_statement >= l_log_current_level) THEN
748 FND_LOG.string(l_log_statement, L_DEBUG, 'Update Rule: rule_id-' || p_x_wbs_rules_rec.rule_id || 'In G_EXC_UNEXPECTED_ERROR block' || ' x_msg_count ' || x_msg_count );
749 END IF;
750
751 WHEN OTHERS THEN
752 ROLLBACK TO update_rule_sp;
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
754 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
755 FND_MSG_PUB.add_exc_msg (G_PKG_NAME, L_API_NAME);
756 END IF;
757 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
758 p_count => x_msg_count,
759 p_data => x_msg_data);
760 IF (l_log_statement >= l_log_current_level) THEN
761 FND_LOG.string(l_log_statement, L_DEBUG, 'Update Rule: rule_id-' || p_x_wbs_rules_rec.rule_id || 'In OTHERS block' || ' x_msg_count ' || x_msg_count );
762 END IF;
763 END update_rule;
764
765
766 END AHL_WBS_RULES_PVT;