[Home] [Help]
PACKAGE BODY: APPS.CN_IMP_RULE_PVT
Source
1 PACKAGE BODY CN_IMP_RULE_PVT AS
2 -- $Header: cnvimrlb.pls 120.3 2005/08/07 23:04:16 vensrini noship $
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_IMP_RULE_PVT';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvimrlb.pls';
7 G_INVALID_IMP_LINE_ID CONSTANT NUMBER := -99999;
8
9 -- Start of comments
10 -- API name : Rules_Import
11 -- Type : Private.
12 -- Function : program to transfer data from staging table into
13 -- cn_rulesets, cn_rules, cn_attributes, cn_rules_hierarchy
14 -- Pre-reqs : None.
15 -- Parameters :
16 -- IN : p_imp_header_id IN NUMBER Required
17 -- OUT : errbuf OUT VARCHAR2 Required
18 -- retcode OUT VARCHAR2 Optional
19 -- Version : Current version 1.0
20 --
21 -- Notes : Note text
22 --
23 -- End of comments
24 PROCEDURE Rules_Import
25 ( errbuf OUT NOCOPY VARCHAR2,
26 retcode OUT NOCOPY VARCHAR2,
27 p_imp_header_id IN NUMBER,
28 p_org_id IN NUMBER
29 ) IS
30 l_api_name CONSTANT VARCHAR2(30) := 'Rules_Import';
31
32 l_stage_status cn_imp_lines.status_code%TYPE := 'STAGE';
33 l_imp_header cn_imp_headers_pvt.imp_headers_rec_type := cn_imp_headers_pvt.G_MISS_IMP_HEADERS_REC;
34 l_processed_row NUMBER := 0;
35 l_failed_row NUMBER := 0;
36 l_message VARCHAR2(4000);
37 l_error_code VARCHAR2(30);
38 err_num NUMBER;
39 l_return_status VARCHAR2(50);
40 l_msg_count NUMBER := 0;
41 l_loading_status VARCHAR2(30);
42 x_loading_status VARCHAR2(30);
43 l_process_audit_id cn_process_audits.process_audit_id%TYPE;
44 l_current_ruleset_id NUMBER;
45 l_current_imp_line_id NUMBER;
46 l_temp_count NUMBER := 0;
47 l_ruleset_rec CN_Ruleset_PVT.ruleset_rec_type;
48 l_err_imp_line_id NUMBER;
49 l_org_id NUMBER;
50
51 -- Cursor to check if the ruleset is present in the system
52 CURSOR c_is_ruleset_present_csr (l_name cn_rulesets.name%TYPE, l_start_date cn_rulesets.start_date%TYPE, l_end_date cn_rulesets.end_date%TYPE, l_module_type cn_rulesets.module_type%TYPE) IS
53 SELECT ruleset_id
54 FROM cn_rulesets
55 WHERE name = l_name
56 AND start_date = l_start_date
57 AND end_date = l_end_date
58 AND module_type = l_module_type
59 AND org_id=p_org_id;
60
61 -- Cursor to get start date, end_date, ruleset type for the specified ruleset name
62 CURSOR c_ruleset_details_csr IS
63 SELECT ruleset_name, start_date, end_date, ruleset_type
64 FROM CN_RULES_IMP_V
65 WHERE imp_header_id = p_imp_header_id
66 AND status_code = l_stage_status
67 GROUP BY ruleset_name, start_date, end_date, ruleset_type
68 ORDER BY start_date;
69
70 detail_rec c_ruleset_details_csr%ROWTYPE;
71
72 BEGIN
73 -- Initialize API return status to success
74 l_return_status := FND_API.G_RET_STS_SUCCESS;
75 retcode := 0;
76 l_org_id:=p_org_id;
77
78 -- Get imp_header info
79 SELECT imp_header_id, name, status_code,server_flag,imp_map_id, source_column_num, import_type_code
80 INTO l_imp_header.imp_header_id,l_imp_header.name ,l_imp_header.status_code, l_imp_header.server_flag,
81 l_imp_header.imp_map_id, l_imp_header.source_column_num,l_imp_header.import_type_code
82 FROM cn_imp_headers
83 WHERE imp_header_id = p_imp_header_id;
84
85 -- open process audit batch
86 cn_message_pkg.begin_batch
87 ( x_process_type => l_imp_header.import_type_code,
88 x_parent_proc_audit_id => l_imp_header.imp_header_id,
89 x_process_audit_id => l_process_audit_id,
90 x_request_id => null,
91 p_org_id =>l_org_id);
92
93 cn_message_pkg.write
94 (p_message_text => 'RULES: Start Transfer Data. imp_header_id = ' || To_char(p_imp_header_id),
95 p_message_type => 'MILESTONE');
96
97 -- get the details for the ruleset
98 FOR detail_rec IN c_ruleset_details_csr LOOP
99 cn_message_pkg.debug('Rules_Import: #### START OF ruleset for loop');
100
101 -- check for the required fields
102 IF (detail_rec.ruleset_name IS NULL) OR (detail_rec.start_date IS NULL) OR (detail_rec.end_date IS NULL) OR
103 (detail_rec.ruleset_type IS NULL) OR (detail_rec.ruleset_type <>'REVCLS' AND detail_rec.ruleset_type <> 'ACCGEN') THEN
104 cn_message_pkg.debug('Rules_Import: Failed Test : All the required fields have not been entered');
105 cn_message_pkg.debug('Rules_Import: detail_rec.ruleset_name: ' || detail_rec.ruleset_name);
106 cn_message_pkg.debug('Rules_Import: detail_rec.start_date: ' || detail_rec.start_date);
107 cn_message_pkg.debug('Rules_Import: detail_rec.end_date: ' || detail_rec.end_date);
108 cn_message_pkg.debug('Rules_Import: detail_rec.ruleset_type: ' || detail_rec.ruleset_type);
109
110 update_imp_lines
111 (p_status => 'FAIL',
112 p_imp_line_id => G_INVALID_IMP_LINE_ID,
113 p_ruleset_name => detail_rec.ruleset_name,
114 p_start_date => detail_rec.start_date,
115 p_end_date => detail_rec.end_date,
116 p_ruleset_type => detail_rec.ruleset_type,
117 p_head_id => l_imp_header.imp_header_id,
118 p_error_code => 'CN_IMP_MISS_REQUIRED',
119 p_error_mssg => fnd_message.get_string('CN','CN_IMP_MISS_REQUIRED'),
120 x_failed_row => l_failed_row,
121 x_processed_row => l_processed_row);
122 GOTO end_of_ruleset_loop;
123 END IF;
124
125 -- the length is as specified in the JSP
126 IF LENGTH(detail_rec.ruleset_name) > 60 THEN
127 cn_message_pkg.debug('ERROR: Ruleset Name too long');
128
129 update_imp_lines
130 (p_status => 'FAIL',
131 p_imp_line_id => G_INVALID_IMP_LINE_ID,
132 p_ruleset_name => detail_rec.ruleset_name,
133 p_start_date => detail_rec.start_date,
134 p_end_date => detail_rec.end_date,
135 p_ruleset_type => detail_rec.ruleset_type,
136 p_head_id => l_imp_header.imp_header_id,
137 p_error_code => 'CN_RULESET_NAME_TOO_LONG',
138 p_error_mssg => fnd_message.get_string('CN','CN_RULESET_NAME_TOO_LONG'),
139 x_failed_row => l_failed_row,
140 x_processed_row => l_processed_row);
141 GOTO end_of_ruleset_loop;
142 END IF;
143
144 l_ruleset_rec.ruleset_name := detail_rec.ruleset_name;
145 l_ruleset_rec.org_id:=p_org_id;
146 BEGIN
147 l_ruleset_rec.start_date := TO_DATE (detail_rec.start_date,'DD/MM/YYYY');
148 l_ruleset_rec.end_date := TO_DATE (detail_rec.end_date,'DD/MM/YYYY');
149 EXCEPTION
150 WHEN OTHERS THEN
151 cn_message_pkg.debug('### IMP ### EXCEPTION : error parsing date');
152
153 update_imp_lines
154 (p_status => 'FAIL',
155 p_imp_line_id => G_INVALID_IMP_LINE_ID,
156 p_ruleset_name => detail_rec.ruleset_name,
157 p_start_date => detail_rec.start_date,
158 p_end_date => detail_rec.end_date,
159 p_ruleset_type => detail_rec.ruleset_type,
160 p_head_id => l_imp_header.imp_header_id,
161 p_error_code => 'CN_IMP_INVLD_RULESET_DATE',
162 p_error_mssg => fnd_message.get_string('CN','CN_IMP_INVLD_RULESET_DATE'),
163 x_failed_row => l_failed_row,
164 x_processed_row => l_processed_row);
165 GOTO end_of_ruleset_loop;
166 END;
167 l_ruleset_rec.module_type := detail_rec.ruleset_type;
168
169 -- this is to check if the ruleset is present. If yes then it is assumed that the ruleset is
170 -- being updated, i.e rules are being added.
171 -- Ref: BUG: 2403038
172 OPEN c_is_ruleset_present_csr (l_ruleset_rec.ruleset_name, l_ruleset_rec.start_date, l_ruleset_rec.end_date, l_ruleset_rec.module_type);
173 FETCH c_is_ruleset_present_csr INTO l_current_ruleset_id;
174 IF c_is_ruleset_present_csr%NOTFOUND THEN
175 l_current_ruleset_id := 0;
176 END IF;
177 CLOSE c_is_ruleset_present_csr;
178
179 cn_message_pkg.debug('value of l_current_ruleset_id:' || l_current_ruleset_id);
180
181 IF l_current_ruleset_id = 0 THEN
182 l_return_status := FND_API.G_RET_STS_SUCCESS;
183 cn_message_pkg.debug('Rules_Import: Before calling CN_RULESET_PVT.create_ruleset, ruleset name: ' || detail_rec.ruleset_name);
184
185 CN_RULESET_PVT.create_ruleset
186 ( p_api_version => 1.0,
187 p_init_msg_list => fnd_api.g_true,
188 p_commit => FND_API.G_FALSE,
189 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
190 x_return_status => l_return_status,
191 x_msg_count => l_msg_count,
192 x_msg_data => l_message,
193 x_loading_status => l_loading_status,
194 x_ruleset_id => l_current_ruleset_id,
195 p_ruleset_rec => l_ruleset_rec);
196
197 cn_message_pkg.debug('Rules_Import: After CN_RULESET_PVT.create_ruleset call, return status: ' || l_return_status);
198 cn_message_pkg.debug('After CN_RULESET_PVT.create_ruleset call, l_message: ' || l_message);
199
200 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
201 cn_message_pkg.debug('Rules_Import: Error after returning from CN_RULESET_PVT.create_ruleset');
202 retcode := 2;
203 errbuf:= l_message;
204 -- update all the rows related to the ruleset with a general message
205 update_imp_lines
206 (p_status => 'FAIL',
207 p_imp_line_id => G_INVALID_IMP_LINE_ID,
208 p_ruleset_name => detail_rec.ruleset_name,
209 p_start_date => detail_rec.start_date,
210 p_end_date => detail_rec.end_date,
211 p_ruleset_type => detail_rec.ruleset_type,
212 p_head_id => l_imp_header.imp_header_id,
213 p_error_code => 'CN_IMP_INVLD_RULESET',
214 p_error_mssg => l_message || ' ' || fnd_message.get_string('CN','CN_IMP_INVLD_RULESET'),
215 x_failed_row => l_failed_row,
216 x_processed_row => l_processed_row);
217
218 GOTO end_of_ruleset_loop;
219 cn_message_pkg.write
220 (p_message_text => 'Completed creating a ruleset, name: ' || detail_rec.ruleset_name,
221 p_message_type => 'MILESTONE');
222 END IF;
223 ELSE
224 cn_message_pkg.debug('Ruleset exists in the Database');
225 END IF; -- end IF l_current_ruleset_id = 0 THEN
226
227 l_err_imp_line_id := NULL;
228
229 cn_message_pkg.debug('Rules_Import: Before call to load_rules, ruleset: ' || detail_rec.ruleset_name);
230 -- now process the rules for this ruleset
231 load_rules
232 (p_ruleset_id => l_current_ruleset_id,
233 p_ruleset_name => detail_rec.ruleset_name,
234 p_ruleset_start_date => detail_rec.start_date,
235 p_ruleset_end_date => detail_rec.end_date,
236 p_ruleset_type => detail_rec.ruleset_type,
237 p_imp_header => l_imp_header,
238 x_err_mssg => errbuf,
239 x_retcode => retcode,
240 x_imp_line_id => l_err_imp_line_id,
241 x_failed_row => l_failed_row,
242 x_processed_row => l_processed_row,
243 p_org_id => l_org_id);
244
245 cn_message_pkg.debug('Rules_Import: After loading rules, x_retcode: ' || retcode);
246
247 IF retcode = 2 THEN
248 cn_message_pkg.debug('Error loading rules, ruleset: ' || detail_rec.ruleset_name);
249 GOTO end_of_ruleset_loop;
250 END IF;
251
252 cn_message_pkg.debug('Rules_Import: After call to load_rules, ruleset :' || detail_rec.ruleset_name);
253 cn_message_pkg.debug ('Rules_Import: Before synchronize, l_current_ruleset_id :' || l_current_ruleset_id);
254
255 IF CN_Ruleset_PVT.check_sync_allowed
256 ( detail_rec.ruleset_name,
257 l_current_ruleset_id,
258 l_org_id,
259 x_loading_status,
260 x_loading_status ) = fnd_api.g_true
261 THEN
262 cn_message_pkg.debug ('Rules_Import: Error synchronizing the ruleset :' || detail_rec.ruleset_name);
263 GOTO end_of_ruleset_loop;
264
265 END IF;
266 -- synchronize the package
267 cn_rulesets_pkg.Sync_ruleset
268 (x_ruleset_id_in => l_current_ruleset_id,
269 x_ruleset_status_in => l_return_status,
270 x_org_id => l_org_id);
271
272 cn_message_pkg.debug ('Rules_Import: Completed synchronize, l_return_status :' || l_return_status);
273 IF l_return_status = 'UNSYNC' THEN
274 cn_message_pkg.debug ('Rules_Import: Error synchronizing the ruleset :' || detail_rec.ruleset_name);
275 GOTO end_of_ruleset_loop;
276 END IF;
277
278 cn_message_pkg.debug ('Rules_Import: Classify the l_current_ruleset_id: ' || l_current_ruleset_id);
279 cn_classification_gen.Classification_Install
280 (x_errbuf => l_message,
281 x_retcode => l_return_status,
282 x_ruleset_id => l_current_ruleset_id,
283 x_org_id => l_org_id);
284
285 cn_message_pkg.debug ('Rules_Import: value returned by cn_classification_gen.Classification_Install: ' || l_return_status);
286 IF l_return_status = 1 THEN
287 cn_message_pkg.debug ('Rules_Import: Error classifying the ruleset: ' || detail_rec.ruleset_name);
288 GOTO end_of_ruleset_loop;
289 ELSE
293 p_imp_line_id => G_INVALID_IMP_LINE_ID,
290 cn_message_pkg.debug ('Rules_Import: No errors for this ruleset COMMIT: ' || detail_rec.ruleset_name);
291 update_imp_lines
292 (p_status => 'COMPLETE',
294 p_ruleset_name => detail_rec.ruleset_name,
295 p_start_date => detail_rec.start_date,
296 p_end_date => detail_rec.end_date,
297 p_ruleset_type => detail_rec.ruleset_type,
298 p_head_id => l_imp_header.imp_header_id,
299 p_error_code => '',
300 p_error_mssg => '',
301 x_failed_row => l_failed_row,
302 x_processed_row => l_processed_row);
303
304 cn_message_pkg.write
305 (p_message_text => 'Completed Synchronizing Ruleset name = ' || detail_rec.ruleset_name,
306 p_message_type => 'MILESTONE');
307 END IF;
308
309 <<end_of_ruleset_loop>>
310 NULL;
311 -- ruleset complete
312 COMMIT;
313 END LOOP; -- end of ruleset cursor
314
315 cn_message_pkg.debug ('Rules_Import: #### OUTSIDE THE LOOP, retcode: ' || retcode);
316
317 IF retcode = 2 THEN
318 CN_IMPORT_PVT.update_imp_headers
319 (p_imp_header_id => p_imp_header_id,
320 p_status_code => 'IMPORT_FAIL',
321 p_processed_row => l_processed_row,
322 p_failed_row => l_failed_row);
323 ELSE
324 CN_IMPORT_PVT.update_imp_headers
325 (p_imp_header_id => p_imp_header_id,
326 p_status_code => 'COMPLETE',
327 p_processed_row => l_processed_row,
328 p_failed_row => l_failed_row);
329 END IF;
330
331 cn_message_pkg.write
332 (p_message_text => 'Completed Transfer of data from staging table to destination tables for imp_header_id = ' || To_char(p_imp_header_id),
333 p_message_type => 'MILESTONE');
334
335 -- close process batch
336 cn_message_pkg.end_batch(l_process_audit_id);
337
338 EXCEPTION
339 WHEN OTHERS THEN
340 cn_message_pkg.debug ('### IMP ### EXCEPTION : ERROR CODE: ' || SQLCODE);
341 cn_message_pkg.debug ('ERROR MESSAGE: ' || SQLERRM);
342 err_num := SQLCODE;
343 IF err_num = -6501 THEN
344 retcode := 2;
345 errbuf := SQLERRM;
346 ELSE
347 retcode := 2 ;
348 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
349 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
350 END IF;
351 FND_MSG_PUB.count_and_get
352 (p_count => l_msg_count,
353 p_data => errbuf,
354 p_encoded => FND_API.G_FALSE);
355 END IF;
356
357 CN_IMPORT_PVT.update_imp_headers
358 (p_imp_header_id => p_imp_header_id,
359 p_status_code => 'IMPORT_FAIL',
360 p_processed_row => l_processed_row,
361 p_failed_row => l_failed_row);
362
363 cn_message_pkg.set_error(l_api_name,errbuf);
364 cn_message_pkg.end_batch(l_process_audit_id);
365
366 END Rules_Import;
367
368 -- --------------------------------------------------------+
369 -- seterr_imp_rules
370 --
371 -- This procedure will set error in cn_imp_lines(cn_rules_imp_v)
372 -- with passed in status and error code
373 -- --------------------------------------------------------+
374 PROCEDURE seterr_imp_rules
375 (p_status IN VARCHAR2,
376 p_ruleset_name IN VARCHAR2,
377 p_ruleset_start_date IN VARCHAR2,
378 p_ruleset_end_date IN VARCHAR2,
379 p_ruleset_type IN VARCHAR2,
380 p_rule_name IN VARCHAR2 := FND_API.g_miss_char,
381 p_parent_rule_name IN VARCHAR2 := FND_API.g_miss_char,
382 p_level_num IN VARCHAR2 := FND_API.g_miss_char,
383 p_expense_code IN VARCHAR2 := FND_API.g_miss_char,
384 p_liability_code IN VARCHAR2 := FND_API.g_miss_char,
385 p_revcls_name IN VARCHAR2 := FND_API.g_miss_char,
386 p_head_id IN NUMBER,
387 p_error_code IN VARCHAR2,
388 p_error_mssg IN VARCHAR2,
389 x_failed_row IN OUT NOCOPY NUMBER,
390 x_processed_row IN OUT NOCOPY NUMBER)
391 IS PRAGMA AUTONOMOUS_TRANSACTION;
392 BEGIN
393 UPDATE cn_rules_imp_v
394 SET status_code=p_status, error_code=p_error_code, error_msg=p_error_mssg
395 WHERE nvl(ruleset_name,FND_API.g_miss_char) = nvl(p_ruleset_name ,FND_API.g_miss_char)
396 AND nvl(start_date ,FND_API.g_miss_char) = nvl(p_ruleset_start_date, FND_API.g_miss_char)
397 AND nvl(end_date,FND_API.g_miss_char) = nvl(p_ruleset_end_date, FND_API.g_miss_char)
398 AND nvl(ruleset_type,FND_API.g_miss_char) = nvl(p_ruleset_type, FND_API.g_miss_char)
399 AND Nvl(rule_name,FND_API.g_miss_char) =
400 Decode(p_rule_name,NULL, FND_API.g_miss_char,
401 FND_API.g_miss_char,Nvl(rule_name,FND_API.g_miss_char),
402 p_rule_name)
403 AND Nvl(parent_rule_name,FND_API.g_miss_char) =
404 Decode(p_parent_rule_name,NULL, FND_API.g_miss_char,
405 FND_API.g_miss_char,Nvl(parent_rule_name,FND_API.g_miss_char),
406 p_parent_rule_name)
407 AND Nvl(level_num,FND_API.g_miss_char) =
408 Decode(p_level_num,NULL, FND_API.g_miss_char,
409 FND_API.g_miss_char,Nvl(level_num,FND_API.g_miss_char),
410 p_level_num)
411 AND Nvl(expense_code,FND_API.g_miss_char) =
412 Decode(p_expense_code,NULL, FND_API.g_miss_char,
413 FND_API.g_miss_char,Nvl(expense_code,FND_API.g_miss_char),
414 p_expense_code)
415 AND Nvl(liability_code,FND_API.g_miss_char) =
416 Decode(p_liability_code,NULL, FND_API.g_miss_char,
420 Decode(p_revcls_name,NULL, FND_API.g_miss_char,
417 FND_API.g_miss_char,Nvl(liability_code,FND_API.g_miss_char),
418 p_liability_code)
419 AND Nvl(revenue_class_name,FND_API.g_miss_char) =
421 FND_API.g_miss_char,Nvl(revenue_class_name,FND_API.g_miss_char),
422 p_revcls_name)
423 AND imp_header_id = p_head_id
424 AND status_code = 'STAGE'
425 ;
426
427 x_failed_row := x_failed_row + SQL%rowcount;
428 x_processed_row := x_processed_row + SQL%rowcount;
429
430 IF (SQL%ROWCOUNT=0) THEN
431 RAISE NO_DATA_FOUND;
432 END IF;
433 COMMIT;
434
435 CN_IMPORT_PVT.update_imp_headers
436 (p_imp_header_id => p_head_id,
437 p_status_code => 'IMPORT_FAIL',
438 p_failed_row => x_failed_row,
439 p_processed_row => x_processed_row);
440
441 END seterr_imp_rules;
442
443 --
444 -- Creat a new entry in the ruleset table
445 --
446 PROCEDURE load_rules
447 ( p_ruleset_id IN NUMBER,
448 p_ruleset_name IN VARCHAR2,
449 p_ruleset_start_date IN VARCHAR,
450 p_ruleset_end_date IN VARCHAR,
451 p_ruleset_type IN VARCHAR,
452 p_imp_header IN cn_imp_headers_pvt.imp_headers_rec_type,
453 x_err_mssg OUT NOCOPY VARCHAR2,
454 x_retcode OUT NOCOPY VARCHAR2,
455 x_imp_line_id OUT NOCOPY NUMBER,
456 x_failed_row IN OUT NOCOPY NUMBER,
457 x_processed_row IN OUT NOCOPY NUMBER,
458 p_org_id IN NUMBER
459 ) IS
460 l_api_name CONSTANT VARCHAR2(30) := 'load_rules';
461 l_stage_status cn_imp_lines.status_code%TYPE := 'STAGE';
462 l_loading_status VARCHAR2(30);
463 l_return_status VARCHAR2(30);
464 l_msg_count NUMBER;
465 l_msg_data VARCHAR2(4000);
466 l_rule_rec cn_rule_pvt.rule_rec_type;
467 l_current_rule_id NUMBER;
468 l_error_code VARCHAR2(30);
469 l_current_imp_line_id NUMBER;
470 l_temp_count NUMBER := 0;
471 l_value_found NUMBER;
472 l_org_id NUMBER;
473
474 -- cursor to get the number of entries that have no rule name
475 CURSOR c_null_rule_name_csr IS
476 SELECT count(*)
477 FROM CN_RULES_IMP_V
478 WHERE imp_header_id = p_imp_header.imp_header_id
479 AND status_code = l_stage_status
480 AND ruleset_name = p_ruleset_name
481 AND start_date = p_ruleset_start_date
482 AND end_date = p_ruleset_end_date
483 AND ruleset_type = p_ruleset_type
484 AND rule_name IS NULL;
485
486 CURSOR c_null_level_num_csr IS
487 SELECT count(*)
488 FROM CN_RULES_IMP_V
489 WHERE imp_header_id = p_imp_header.imp_header_id
490 AND status_code = l_stage_status
491 AND ruleset_name = p_ruleset_name
492 AND start_date = p_ruleset_start_date
493 AND end_date = p_ruleset_end_date
494 AND ruleset_type = p_ruleset_type
495 AND level_num IS NULL;
496
497 -- Cursor to get rules of the ruleset
498 CURSOR c_rule_name_csr IS
499 SELECT distinct(rule_name) rule_name, parent_rule_name, level_num
500 FROM CN_RULES_IMP_V
501 WHERE imp_header_id = p_imp_header.imp_header_id
502 AND status_code = l_stage_status
503 AND ruleset_name = p_ruleset_name
504 AND start_date = p_ruleset_start_date
505 AND end_date = p_ruleset_end_date
506 AND ruleset_type = p_ruleset_type
507 GROUP BY rule_name, parent_rule_name, level_num
508 ORDER BY level_num;
509
510 -- Cursor to check unique of revenue_class_name, expense_code,
511 -- liability_code
512 CURSOR c_rules_dtl_csr (l_name cn_rules_imp_v.rule_name%TYPE, l_parent_rule_name cn_rules_imp_v.parent_rule_name%TYPE, l_level_num cn_rules_imp_v.level_num%TYPE) IS
513 SELECT COUNT(1) FROM
514 (SELECT revenue_class_name, expense_code, liability_code
515 FROM CN_RULES_IMP_V
516 WHERE imp_header_id = p_imp_header.imp_header_id
517 AND status_code = l_stage_status
518 AND ruleset_name = p_ruleset_name
519 AND start_date = p_ruleset_start_date
520 AND end_date = p_ruleset_end_date
521 AND ruleset_type = p_ruleset_type
522 AND rule_name = l_name
523 AND level_num = l_level_num
524 AND nvl(parent_rule_name,FND_API.g_miss_char) = nvl(l_parent_rule_name,FND_API.g_miss_char)
525 GROUP BY revenue_class_name, expense_code, liability_code) v1;
526
527 -- Cursor to get rules details of the ruleset
528 CURSOR c_rules_csr (l_name cn_rules_imp_v.rule_name%TYPE, l_parent_rule_name cn_rules_imp_v.parent_rule_name%TYPE, l_level_num cn_rules_imp_v.level_num%TYPE) IS
529 SELECT revenue_class_name, expense_code, liability_code
530 FROM CN_RULES_IMP_V
531 WHERE imp_header_id = p_imp_header.imp_header_id
532 AND status_code = l_stage_status
533 AND ruleset_name = p_ruleset_name
534 AND start_date = p_ruleset_start_date
535 AND end_date = p_ruleset_end_date
536 AND ruleset_type = p_ruleset_type
537 AND rule_name = l_name
538 AND level_num = l_level_num
539 AND nvl(parent_rule_name,FND_API.g_miss_char) = nvl(l_parent_rule_name,FND_API.g_miss_char);
540
541 -- Cursor to get the rule id given the rule name
542 CURSOR c_parent_rule_csr (l_name cn_rules.name%TYPE) IS
543 SELECT rule_id
544 FROM CN_RULES
548
545 WHERE name = l_name
546 AND ruleset_id=p_ruleset_id
547 AND org_id=p_org_id;
549 -- Cursor to get the revenue class id, given the revenue class name
550 CURSOR c_revenue_id_csr (l_name cn_revenue_classes.name%TYPE) IS
551 SELECT revenue_class_id
552 FROM cn_revenue_classes
553 WHERE name = l_name
554 and org_id=p_org_id;
555
556 -- cursor to get the liability code id
557 --CURSOR c_liability_code_csr (l_name cn_rule_account_code_v.CODE_DESCRIPTION%TYPE) IS
558 CURSOR c_liability_code_csr (l_name varchar2) IS
559 SELECT code_combination_id
560 FROM (SELECT
561 gl.code_combination_id code_combination_id ,
562 cn_api.get_ccid_disp_func(gl.code_combination_id,r.org_id) code_description,
563 gl.account_type account_type
564 FROM
565 gl_code_combinations gl ,
566 cn_repositories r ,
567 gl_sets_of_books gls
568 WHERE
569 r.set_of_books_id = gls.set_of_books_id and
570 gls.chart_of_accounts_id = gl.chart_of_accounts_id
571 AND r.org_id=p_org_id)
572 WHERE account_type ='L'
573 AND code_description = l_name;
574
575 -- cursor to get the expense code id
576 CURSOR c_expense_code_csr (l_name varchar2) IS
577 SELECT code_combination_id
578 FROM (SELECT
579 gl.code_combination_id code_combination_id ,
580 cn_api.get_ccid_disp_func(gl.code_combination_id,r.org_id) code_description,
581 gl.account_type account_type
582 FROM
583 gl_code_combinations gl ,
584 cn_repositories r ,
585 gl_sets_of_books gls
586 WHERE
587 r.set_of_books_id = gls.set_of_books_id and
588 gls.chart_of_accounts_id = gl.chart_of_accounts_id
589 AND r.org_id=p_org_id)
590 WHERE account_type = 'E'
591 AND code_description = l_name;
592
593 rules_rec c_rules_csr%ROWTYPE;
594 expns_row c_expense_code_csr%ROWTYPE;
595 liabl_row c_liability_code_csr%ROWTYPE;
596 parent_rul_row c_parent_rule_csr%ROWTYPE;
597 revenue_row c_revenue_id_csr%ROWTYPE;
598 BEGIN
599 x_retcode := 0;
600 l_org_id:=p_org_id;
601 -- Check rule_name cannot null
602 OPEN c_null_rule_name_csr;
603 FETCH c_null_rule_name_csr INTO l_temp_count;
604 CLOSE c_null_rule_name_csr;
605
606 IF l_temp_count > 0 THEN
607 cn_message_pkg.debug ('Ruleset has rules with no rules name/null value');
608 x_err_mssg := fnd_message.get_string('CN','CN_INVLD_RULE_NAME');
609 x_retcode := 2;
610 x_imp_line_id := G_INVALID_IMP_LINE_ID;
611 l_error_code := 'CN_INVLD_RULE_NAME';
612 seterr_imp_rules
613 (p_status => 'FAIL',
614 p_ruleset_name => p_ruleset_name,
615 p_ruleset_start_date => p_ruleset_start_date,
616 p_ruleset_end_date => p_ruleset_end_date,
617 p_ruleset_type => p_ruleset_type,
618 p_rule_name => NULL,
619 p_head_id => p_imp_header.imp_header_id,
620 p_error_code => l_error_code,
621 p_error_mssg => x_err_mssg,
622 x_failed_row => x_failed_row,
623 x_processed_row => x_processed_row);
624 END IF;
625
626 -- Check level_num cannot null
627 l_temp_count := 0 ;
628 OPEN c_null_level_num_csr;
629 FETCH c_null_level_num_csr INTO l_temp_count;
630 CLOSE c_null_level_num_csr;
631
632 IF l_temp_count > 0 THEN
633 cn_message_pkg.debug ('Ruleset has level num with null value');
634 x_err_mssg := fnd_message.get_string('CN','CN_IMP_MISS_REQUIRED');
635 x_retcode := 2;
636 x_imp_line_id := G_INVALID_IMP_LINE_ID;
637 l_error_code := 'CN_IMP_MISS_REQUIRED';
638 seterr_imp_rules
639 (p_status => 'FAIL',
640 p_ruleset_name => p_ruleset_name,
641 p_ruleset_start_date => p_ruleset_start_date,
642 p_ruleset_end_date => p_ruleset_end_date,
643 p_ruleset_type => p_ruleset_type,
644 p_level_num => NULL,
645 p_head_id => p_imp_header.imp_header_id,
646 p_error_code => l_error_code,
647 p_error_mssg => x_err_mssg,
648 x_failed_row => x_failed_row,
649 x_processed_row => x_processed_row);
650 END IF;
651 -- Process each rules
652 FOR rule_name_rec IN c_rule_name_csr LOOP
653 -- Check unique of rev class, libility/expense code
654 l_temp_count := 0 ;
655 OPEN c_rules_dtl_csr
656 (rule_name_rec.rule_name, rule_name_rec.parent_rule_name,
657 rule_name_rec.level_num);
658 FETCH c_rules_dtl_csr INTO l_temp_count;
659 CLOSE c_rules_dtl_csr;
660
661 IF l_temp_count > 1 THEN
662 cn_message_pkg.debug ('Rule has multiple RC, libility/exp code value');
663 x_err_mssg := fnd_message.get_string('CN','CN_MULTI_RULE_DTL');
664 x_retcode := 2;
665 x_imp_line_id := G_INVALID_IMP_LINE_ID;
666 l_error_code := 'CN_MULTI_RULE_DTL';
667 seterr_imp_rules
668 (p_status => 'FAIL',
669 p_ruleset_name => p_ruleset_name,
670 p_ruleset_start_date => p_ruleset_start_date,
671 p_ruleset_end_date => p_ruleset_end_date,
672 p_ruleset_type => p_ruleset_type,
673 p_rule_name => rule_name_rec.rule_name,
674 p_parent_rule_name => rule_name_rec.parent_rule_name,
675 p_level_num => rule_name_rec.level_num,
676 p_head_id => p_imp_header.imp_header_id,
680 x_processed_row => x_processed_row);
677 p_error_code => l_error_code,
678 p_error_mssg => x_err_mssg,
679 x_failed_row => x_failed_row,
681
682 GOTO end_load_rule;
683 END IF;
684
685 -- check rule_name length
686 IF LENGTH(rule_name_rec.rule_name) > 60 THEN
687 cn_message_pkg.debug ('load_rules: Rule name too long :' || rule_name_rec.rule_name);
688 x_err_mssg := fnd_message.get_string('CN','CN_RULE_NAME_TOO_LONG');
689 x_retcode := 2;
690 l_error_code := 'CN_RULE_NAME_TOO_LONG';
691 seterr_imp_rules
692 (p_status => 'FAIL',
693 p_ruleset_name => p_ruleset_name,
694 p_ruleset_start_date => p_ruleset_start_date,
695 p_ruleset_end_date => p_ruleset_end_date,
696 p_ruleset_type => p_ruleset_type,
697 p_rule_name => rule_name_rec.rule_name,
698 p_parent_rule_name => rule_name_rec.parent_rule_name,
699 p_level_num => rule_name_rec.level_num,
700 p_head_id => p_imp_header.imp_header_id,
701 p_error_code => l_error_code,
702 p_error_mssg => x_err_mssg,
703 x_failed_row => x_failed_row,
704 x_processed_row => x_processed_row);
705 GOTO end_load_rule;
706 END IF;
707
708 -- get rule detail value
709 OPEN c_rules_csr(rule_name_rec.rule_name, rule_name_rec.parent_rule_name,
710 rule_name_rec.level_num);
711 FETCH c_rules_csr INTO rules_rec;
712 CLOSE c_rules_csr;
713
714 cn_message_pkg.write
715 (p_message_text => 'Start to create Rule, name: ' || rule_name_rec.rule_name,
716 p_message_type => 'MILESTONE');
717
718 -- get the required values to create a new rule
719 l_rule_rec.ruleset_id := p_ruleset_id;
720 l_rule_rec.org_id := p_org_id;
721 l_rule_rec.rule_name := rule_name_rec.rule_name;
722 -- l_current_imp_line_id := rules_rec.imp_line_id;
723 l_rule_rec.parent_rule_id := null;
724 l_rule_rec.revenue_class_id := null;
725 l_rule_rec.expense_ccid := null;
726 l_rule_rec.liability_ccid := null;
727 l_rule_rec.revenue_class_id := null;
728
729 -- Check for 'ACCGEN', rev class should not have value
730 IF (p_ruleset_type = 'ACCGEN' AND rules_rec.revenue_class_name IS NOT NULL) OR (p_ruleset_type = 'REVCLS' AND (rules_rec.liability_code IS NOT NULL OR rules_rec.expense_code IS NOT NULL))
731 THEN
732 x_err_mssg := fnd_message.get_string('CN','CN_INVLD_RULE_DTL');
733 x_retcode := 2;
734 l_error_code := 'CN_INVLD_RULE_DTL';
735 seterr_imp_rules
736 (p_status => 'FAIL',
737 p_ruleset_name => p_ruleset_name,
738 p_ruleset_start_date => p_ruleset_start_date,
739 p_ruleset_end_date => p_ruleset_end_date,
740 p_ruleset_type => p_ruleset_type,
741 p_rule_name => rule_name_rec.rule_name,
742 p_parent_rule_name => rule_name_rec.parent_rule_name,
743 p_level_num => rule_name_rec.level_num,
744 p_head_id => p_imp_header.imp_header_id,
745 p_error_code => l_error_code,
746 p_error_mssg => x_err_mssg,
747 x_failed_row => x_failed_row,
748 x_processed_row => x_processed_row);
749 GOTO end_load_rule;
750 END IF;
751
752 -- Check Expense code
753 IF rules_rec.expense_code IS NOT NULL THEN
754 l_value_found := 0;
755 FOR expns_row IN c_expense_code_csr (rules_rec.expense_code) LOOP
756 l_rule_rec.expense_ccid := expns_row.code_combination_id;
757 l_value_found := 1;
758 END LOOP;
759 IF l_value_found = 0 THEN
760 cn_message_pkg.debug ('load_rules: Invalid Expense code :' || rules_rec.expense_code);
761 x_err_mssg := fnd_message.get_string('CN','CN_IMP_INVLD_EXPENS_CODE');
762 x_retcode := 2;
763 l_error_code := 'CN_IMP_INVLD_EXPENS_CODE';
764 seterr_imp_rules
765 (p_status => 'FAIL',
766 p_ruleset_name => p_ruleset_name,
767 p_ruleset_start_date => p_ruleset_start_date,
768 p_ruleset_end_date => p_ruleset_end_date,
769 p_ruleset_type => p_ruleset_type,
770 p_rule_name => rule_name_rec.rule_name,
771 p_parent_rule_name => rule_name_rec.parent_rule_name,
772 p_level_num => rule_name_rec.level_num,
773 p_expense_code => rules_rec.expense_code,
774 p_head_id => p_imp_header.imp_header_id,
775 p_error_code => l_error_code,
776 p_error_mssg => x_err_mssg,
777 x_failed_row => x_failed_row,
778 x_processed_row => x_processed_row);
779 GOTO end_load_rule;
780 END IF;
781 END IF;
782 -- Check liability_code
783 IF rules_rec.liability_code IS NOT NULL THEN
784 l_value_found := 0;
785 FOR liabl_row IN c_liability_code_csr (rules_rec.liability_code) LOOP
786 l_value_found := 1;
787 l_rule_rec.liability_ccid := liabl_row.code_combination_id;
788 END LOOP;
789 IF l_value_found = 0 THEN
790 cn_message_pkg.debug ('load_rules: Invalid liability code :' || rules_rec.liability_code);
791 x_err_mssg := fnd_message.get_string('CN','CN_IMP_INVLD_LIABLTY_CODE');
792 x_retcode := 2;
793 l_error_code := 'CN_IMP_INVLD_LIABLTY_CODE';
794 seterr_imp_rules
795 (p_status => 'FAIL',
796 p_ruleset_name => p_ruleset_name,
800 p_rule_name => rule_name_rec.rule_name,
797 p_ruleset_start_date => p_ruleset_start_date,
798 p_ruleset_end_date => p_ruleset_end_date,
799 p_ruleset_type => p_ruleset_type,
801 p_parent_rule_name => rule_name_rec.parent_rule_name,
802 p_level_num => rule_name_rec.level_num,
803 p_liability_code => rules_rec.liability_code,
804 p_head_id => p_imp_header.imp_header_id,
805 p_error_code => l_error_code,
806 p_error_mssg => x_err_mssg,
807 x_failed_row => x_failed_row,
808 x_processed_row => x_processed_row);
809 GOTO end_load_rule;
810 END IF;
811 END IF;
812 -- Check parent_rule_name
813 IF rule_name_rec.parent_rule_name IS NULL THEN
814 l_rule_rec.parent_rule_id := -1002;
815 ELSE
816 l_value_found := 0;
817 FOR parent_rul_row IN c_parent_rule_csr (rule_name_rec.parent_rule_name) LOOP
818 l_rule_rec.parent_rule_id := parent_rul_row.rule_id;
819 l_value_found := 1;
820 END LOOP;
821 IF l_value_found = 0 THEN
822 cn_message_pkg.debug ('load_rules: Invalid Parent Rule name :' || rule_name_rec.parent_rule_name);
823 x_err_mssg := fnd_message.get_string('CN','CN_IMP_INVLD_PAR_RUL_NM');
824 x_retcode := 2;
825 l_error_code := 'CN_IMP_INVLD_PAR_RUL_NM';
826 seterr_imp_rules
827 (p_status => 'FAIL',
828 p_ruleset_name => p_ruleset_name,
829 p_ruleset_start_date => p_ruleset_start_date,
830 p_ruleset_end_date => p_ruleset_end_date,
831 p_ruleset_type => p_ruleset_type,
832 p_rule_name => rule_name_rec.rule_name,
833 p_parent_rule_name => rule_name_rec.parent_rule_name,
834 p_level_num => rule_name_rec.level_num,
835 p_head_id => p_imp_header.imp_header_id,
836 p_error_code => l_error_code,
837 p_error_mssg => x_err_mssg,
838 x_failed_row => x_failed_row,
839 x_processed_row => x_processed_row);
840 GOTO end_load_rule;
841 END IF;
842 END IF;
843
844 -- Check rev class name
845 IF rules_rec.revenue_class_name IS NOT NULL THEN
846 l_value_found := 0;
847 FOR revenue_row IN c_revenue_id_csr (rules_rec.revenue_class_name) LOOP
848 l_value_found := 1;
849 l_rule_rec.revenue_class_id := revenue_row.revenue_class_id;
850 END LOOP;
851 IF l_value_found = 0 THEN
852 cn_message_pkg.debug ('load_rules: Invalid Revenue class name :' || rules_rec.revenue_class_name);
853 x_err_mssg := fnd_message.get_string('CN','CN_IMP_INVLD_REVNU_CLASS_NM');
854 x_retcode := 2;
855 l_error_code := 'CN_IMP_INVLD_REVNU_CLASS_NM';
856 seterr_imp_rules
857 (p_status => 'FAIL',
858 p_ruleset_name => p_ruleset_name,
859 p_ruleset_start_date => p_ruleset_start_date,
860 p_ruleset_end_date => p_ruleset_end_date,
861 p_ruleset_type => p_ruleset_type,
862 p_rule_name => rule_name_rec.rule_name,
863 p_parent_rule_name => rule_name_rec.parent_rule_name,
864 p_level_num => rule_name_rec.level_num,
865 p_revcls_name => rules_rec.revenue_class_name,
866 p_head_id => p_imp_header.imp_header_id,
867 p_error_code => l_error_code,
868 p_error_mssg => x_err_mssg,
869 x_failed_row => x_failed_row,
870 x_processed_row => x_processed_row);
871 GOTO end_load_rule;
872 END IF;
873 END IF;
874
875 -- Create rule
876 cn_message_pkg.debug('load_rules: Creating rule, rule name:' || rule_name_rec.rule_name);
877 cn_message_pkg.debug('load_rules: rule_name_rec.parent_rule_name:' || rule_name_rec.parent_rule_name || ' l_rule_rec.parent_rule_id:' || l_rule_rec.parent_rule_id);
878 cn_message_pkg.debug('load_rules: rules_rec.revenue_class_name:' || rules_rec.revenue_class_name || ' l_rule_rec.revenue_class_id:' || l_rule_rec.revenue_class_id);
879 cn_message_pkg.debug('load_rules: rules_rec.expense_code: ' || rules_rec.expense_code || ' l_rule_rec.expense_ccid: ' || l_rule_rec.expense_ccid);
880 cn_message_pkg.debug('load_rules: rules_rec.liability_code: ' || rules_rec.liability_code || ' l_rule_rec.liability_ccid: ' || l_rule_rec.liability_ccid);
881 cn_message_pkg.debug('load_rules: rules_rec.revenue_class_name: ' || rules_rec.revenue_class_name || ' l_rule_rec.revenue_class_id: ' || l_rule_rec.revenue_class_id);
882
883 cn_rule_pvt.Create_Rule
884 ( p_api_version => 1.0,
885 p_init_msg_list => fnd_api.g_true,
886 p_commit => FND_API.G_FALSE,
887 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
888 x_return_status => l_return_status,
889 x_msg_count => l_msg_count,
890 x_msg_data => l_msg_data,
891 x_loading_status => l_loading_status,
892 p_rule_rec => l_rule_rec,
893 x_rule_id => l_current_rule_id);
894
895 cn_message_pkg.debug ('load_rules: completed creating new rule, l_return_status: ' || l_return_status || ' l_current_rule_id: ' || l_current_rule_id);
896
897 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
898 cn_message_pkg.debug ('load_rules: Error in Creating rule, rule name:' || rule_name_rec.rule_name);
899 x_err_mssg := l_msg_data;
900 x_retcode := 2;
901 l_error_code := 'CN_IMP_INVLD_RULE';
902 seterr_imp_rules
903 (p_status => 'FAIL',
904 p_ruleset_name => p_ruleset_name,
908 p_rule_name => rule_name_rec.rule_name,
905 p_ruleset_start_date => p_ruleset_start_date,
906 p_ruleset_end_date => p_ruleset_end_date,
907 p_ruleset_type => p_ruleset_type,
909 p_parent_rule_name => rule_name_rec.parent_rule_name,
910 p_level_num => rule_name_rec.level_num,
911 p_head_id => p_imp_header.imp_header_id,
912 p_error_code => l_error_code,
913 p_error_mssg => x_err_mssg,
914 x_failed_row => x_failed_row,
915 x_processed_row => x_processed_row);
916 GOTO end_load_rule;
917 END IF;
918
919 cn_message_pkg.write
920 (p_message_text => 'Completed create Rule, name: ' || rule_name_rec.rule_name,
921 p_message_type => 'MILESTONE');
922
923 cn_message_pkg.debug ('load_rules: Before load_rule_attributes, rule name: ' || rule_name_rec.rule_name);
924 -- now create the rule attributes
925 load_rule_attributes
926 ( p_ruleset_id => p_ruleset_id,
927 p_ruleset_name => p_ruleset_name,
928 p_ruleset_start_date => p_ruleset_start_date,
929 p_ruleset_end_date => p_ruleset_end_date,
930 p_ruleset_type => p_ruleset_type,
931 p_rule_id => l_current_rule_id,
932 p_rule_name => rule_name_rec.rule_name,
933 p_parent_rule_name => rule_name_rec.parent_rule_name,
934 p_level_num => rule_name_rec.level_num,
935 p_imp_header => p_imp_header,
936 x_err_mssg => x_err_mssg,
937 x_retcode => x_retcode,
938 x_imp_line_id => x_imp_line_id,
939 x_failed_row => x_failed_row,
940 x_processed_row => x_processed_row,
941 p_org_id => l_org_id);
942
943 cn_message_pkg.debug ('load_rules: After load_rule_attributes, x_retcode: ' || x_retcode);
944
945 IF x_retcode = 2 THEN
946 cn_message_pkg.debug ('load_rules: After call to load_rule_attributes for rule: ' || rule_name_rec.rule_name || ' with error msg:' || x_err_mssg);
947 GOTO end_load_rule;
948 END IF;
949 << end_load_rule >>
950 NULL;
951 END LOOP; -- of rules_rec loop
952
953 EXCEPTION
954 WHEN OTHERS THEN
955 cn_message_pkg.debug ('load_rules: ### IMP ### EXCEPTION : in Creating rule:' || SQLERRM);
956 x_err_mssg := SQLERRM;
957 x_retcode := 2;
958 l_error_code := 'CN_IMP_INVLD_RULE';
959 seterr_imp_rules
960 (p_status => 'FAIL',
961 p_ruleset_name => p_ruleset_name,
962 p_ruleset_start_date => p_ruleset_start_date,
963 p_ruleset_end_date => p_ruleset_end_date,
964 p_ruleset_type => p_ruleset_type,
965 p_rule_name => l_rule_rec.rule_name,
966 p_head_id => p_imp_header.imp_header_id,
967 p_error_code => l_error_code,
968 p_error_mssg => x_err_mssg,
969 x_failed_row => x_failed_row,
970 x_processed_row => x_processed_row);
971
972 END load_rules;
973
974 --
975 -- Creat a new entry for the rule attributes
976 --
977 PROCEDURE load_rule_attributes
978 ( p_ruleset_id IN NUMBER,
979 p_ruleset_name IN VARCHAR2,
980 p_ruleset_start_date IN VARCHAR,
981 p_ruleset_end_date IN VARCHAR,
982 p_ruleset_type IN VARCHAR,
983 p_rule_id IN NUMBER,
984 p_rule_name IN VARCHAR2,
985 p_parent_rule_name IN VARCHAR2,
986 p_level_num IN VARCHAR2,
987 p_imp_header IN cn_imp_headers_pvt.imp_headers_rec_type,
988 x_err_mssg OUT NOCOPY VARCHAR2,
989 x_retcode OUT NOCOPY VARCHAR2,
990 x_imp_line_id OUT NOCOPY NUMBER,
991 x_failed_row IN OUT NOCOPY NUMBER,
992 x_processed_row IN OUT NOCOPY NUMBER,
993 p_org_id IN NUMBER
994 ) IS
995 l_api_name CONSTANT VARCHAR2(30) := 'load_rule_attributes';
996 l_stage_status cn_imp_lines.status_code%TYPE := 'STAGE';
997 l_error_code VARCHAR2(30);
998 l_loading_status VARCHAR2(30);
999 l_return_status VARCHAR2(30);
1000 l_msg_count NUMBER;
1001 l_msg_data VARCHAR (4000);
1002 l_rule_attrb_rec CN_RuleAttribute_PVT.RuleAttribute_rec_type;
1003 l_current_imp_line_id NUMBER;
1004 l_value_found NUMBER;
1005 l_org_id NUMBER;
1006
1007 -- cursor to get attribute details, given ruleset name, rule name and parent rule name
1008 -- IMP parent rule name is required because we can have 2 rules with the same name but at different levels
1009 CURSOR c_rule_attrb_csr IS
1010 SELECT imp_line_id, record_num, rule_attribute, rule_value, not_flag, rule_hierarchy, rule_low_value, rule_high_value
1011 FROM CN_RULES_IMP_V
1012 WHERE imp_header_id = p_imp_header.imp_header_id
1013 AND status_code = l_stage_status
1014 AND ruleset_name = p_ruleset_name
1015 AND start_date = p_ruleset_start_date
1016 AND end_date = p_ruleset_end_date
1017 AND ruleset_type= p_ruleset_type
1018 AND rule_name = p_rule_name
1019 AND nvl(parent_rule_name, FND_API.g_miss_char) = nvl(p_parent_rule_name, FND_API.g_miss_char)
1020 AND level_num = p_level_num;
1021
1022 -- cursor to get the hierarchy id
1023 CURSOR c_rule_attrb_hier_id_csr (l_name CN_HEAD_HIERARCHIES.name%TYPE) IS
1024 SELECT head_hierarchy_id
1025 FROM CN_HEAD_HIERARCHIES
1026 WHERE name = l_name
1027 and org_id=p_org_id;
1028
1029 -- cursor to get the value associated with the hierarchy attribute
1033 WHERE cdh.header_dim_hierarchy_id = h_id
1030 CURSOR c_rule_attrb_hier_val_csr (h_id CN_HIERARCHY_NODES.dim_hierarchy_id%TYPE, l_name CN_HIERARCHY_NODES.NAME%TYPE) IS
1031 SELECT CHN.value_id
1032 FROM CN_DIM_HIERARCHIES CDH, CN_HIERARCHY_NODES CHN
1034 AND name = l_name AND
1035 CDH.org_id=p_org_id AND
1036 CDH.org_id=CHN.org_id
1037 AND cdh.dim_hierarchy_id = chn.dim_hierarchy_id;
1038
1039 -- Cursor to get the name of the column given the user name
1040 CURSOR c_rule_attrb_object_name (l_name cn_objects.user_name%TYPE) IS
1041 SELECT name
1042 FROM cn_objects
1043 WHERE table_id = -11803
1044 and org_id=p_org_id
1045 AND user_name = l_name;
1046
1047 attrb_name_row c_rule_attrb_object_name%ROWTYPE;
1048 BEGIN
1049 FOR attr_rec IN c_rule_attrb_csr LOOP
1050 cn_message_pkg.write
1051 (p_message_text => 'Start create Rule Attribute, name: ' || attr_rec.rule_attribute,
1052 p_message_type => 'MILESTONE');
1053
1054 l_current_imp_line_id := attr_rec.imp_line_id;
1055 cn_message_pkg.debug ('load_rule_attributes: attr_rec.rule_attribute: ' || attr_rec.rule_attribute);
1056 cn_message_pkg.debug ('load_rule_attributes: attr_rec.rule_value: ' || attr_rec.rule_value);
1057 cn_message_pkg.debug ('load_rule_attributes: attr_rec.rule_hierarchy: ' || attr_rec.rule_hierarchy);
1058 cn_message_pkg.debug ('load_rule_attributes: attr_rec.rule_low_value: ' || attr_rec.rule_low_value);
1059 cn_message_pkg.debug ('load_rule_attributes: attr_rec.rule_high_value: ' || attr_rec.rule_high_value);
1060
1061 IF (attr_rec.rule_attribute IS NULL) OR
1062 (attr_rec.rule_value IS NOT NULL AND attr_rec.rule_low_value IS NOT NULL) OR
1063 (attr_rec.rule_value IS NOT NULL AND attr_rec.rule_high_value IS NOT NULL)OR
1064 (attr_rec.rule_hierarchy IS NOT NULL AND attr_rec.rule_low_value IS NOT NULL) OR
1065 (attr_rec.rule_hierarchy IS NOT NULL AND attr_rec.rule_high_value IS NOT NULL) THEN
1066 cn_message_pkg.debug ('load_rule_attributes: Invalid rule attribute');
1067 x_err_mssg := fnd_message.get_string('CN', 'CN_IMP_INVLD_RULE_ATTRB');
1068 x_retcode := 2;
1069 x_imp_line_id := attr_rec.imp_line_id;
1070 l_error_code := 'CN_IMP_INVLD_RULE_ATTRB';
1071
1072 update_on_error
1073 (p_line_id => attr_rec.imp_line_id,
1074 p_err_code => l_error_code,
1075 p_err_mssg => x_err_mssg,
1076 p_head_id => p_imp_header.imp_header_id);
1077 x_failed_row := x_failed_row + 1;
1078 GOTO end_rule_attr;
1079 END IF;
1080
1081 l_rule_attrb_rec.ruleset_id := p_ruleset_id;
1082 l_rule_attrb_rec.rule_id := p_rule_id;
1083 l_rule_attrb_rec.org_id := p_org_id;
1084
1085 l_value_found := 0;
1086 FOR attrb_name_row IN c_rule_attrb_object_name (attr_rec.rule_attribute) LOOP
1087 l_value_found := 1;
1088 l_rule_attrb_rec.object_name := attrb_name_row.name;
1089 END LOOP;
1090 IF l_value_found = 0 THEN
1091 cn_message_pkg.debug ('load_rule_attributes: Invalid attr_rec.rule_attribute: ' || attr_rec.rule_attribute);
1092 x_err_mssg := fnd_message.get_string('CN', 'CN_IMP_INVLD_RUL_ATTR');
1093 x_retcode := 2;
1094 x_imp_line_id := attr_rec.imp_line_id;
1095 l_error_code := 'CN_IMP_INVLD_RUL_ATTR';
1096 update_on_error
1097 (p_line_id => attr_rec.imp_line_id,
1098 p_err_code => l_error_code,
1099 p_err_mssg => x_err_mssg,
1100 p_head_id => p_imp_header.imp_header_id);
1101 x_failed_row := x_failed_row + 1;
1102 GOTO end_rule_attr;
1103 END IF;
1104
1105 cn_message_pkg.debug ('load_rule_attributes: Past the basic validation');
1106 cn_message_pkg.debug ('load_rule_attributes: p_ruleset_id: ' || p_ruleset_id);
1107 cn_message_pkg.debug ('load_rule_attributes: p_rule_id: ' || p_rule_id);
1108 cn_message_pkg.debug ('load_rule_attributes: attr_rec.rule_attribute: ' || attr_rec.rule_attribute);
1109
1110 IF attr_rec.not_flag IS NULL OR attr_rec.not_flag = 'N' THEN
1111 l_rule_attrb_rec.not_flag := 'N';
1112 ELSIF attr_rec.not_flag = 'Y' THEN
1113 l_rule_attrb_rec.not_flag := 'Y';
1114 ELSE
1115 cn_message_pkg.debug ('load_rule_attributes: Invalid not flag value: ' || attr_rec.not_flag);
1116 x_err_mssg := fnd_message.get_string('CN', 'CN_IMP_INVLD_NOT_FLG_VAL');
1117 x_retcode := 2;
1118 x_imp_line_id := attr_rec.imp_line_id;
1119 l_error_code := 'CN_IMP_INVLD_NOT_FLG_VAL';
1120 update_on_error
1121 (p_line_id => attr_rec.imp_line_id,
1122 p_err_code => l_error_code,
1123 p_err_mssg => x_err_mssg,
1124 p_head_id => p_imp_header.imp_header_id);
1125 x_failed_row := x_failed_row + 1;
1126 GOTO end_rule_attr;
1127 END IF;
1128
1129 cn_message_pkg.debug ('load_rule_attributes: attr_rec.not_flag: ' || attr_rec.not_flag);
1130
1131 IF attr_rec.rule_value IS NOT NULL AND attr_rec.rule_hierarchy IS NULL THEN
1132 -- this is a Single Value Attribute
1133 cn_message_pkg.debug ('load_rule_attributes: CASE :Single Value Attribute');
1134 l_rule_attrb_rec.value_1 := attr_rec.rule_value;
1135 l_rule_attrb_rec.value_2 := NULL;
1136 l_rule_attrb_rec.data_flag := 'O';
1137 ELSIF attr_rec.rule_value IS NOT NULL AND attr_rec.rule_hierarchy IS NOT NULL THEN
1138 -- this is a Hierarchy Attribute
1139 cn_message_pkg.debug ('load_rule_attributes: CASE :Hierarchy Attribute');
1140 OPEN c_rule_attrb_hier_id_csr (attr_rec.rule_hierarchy);
1144 OPEN c_rule_attrb_hier_val_csr (l_rule_attrb_rec.value_1, attr_rec.rule_value);
1141 FETCH c_rule_attrb_hier_id_csr INTO l_rule_attrb_rec.value_1;
1142 CLOSE c_rule_attrb_hier_id_csr;
1143
1145 FETCH c_rule_attrb_hier_val_csr INTO l_rule_attrb_rec.value_2;
1146 CLOSE c_rule_attrb_hier_val_csr;
1147
1148 l_rule_attrb_rec.data_flag := 'H';
1149 ELSE
1150 -- this is a Range Value Attribute
1151 cn_message_pkg.debug ('load_rule_attributes: CASE :Range Value Attribute');
1152 l_rule_attrb_rec.value_1 := attr_rec.rule_low_value;
1153 l_rule_attrb_rec.value_2 := attr_rec.rule_high_value;
1154 l_rule_attrb_rec.data_flag := 'R';
1155 END IF;
1156
1157 cn_message_pkg.debug ('load_rule_attributes: l_rule_attrb_rec.data_flag:' || l_rule_attrb_rec.data_flag);
1158 cn_message_pkg.debug ('load_rule_attributes: l_rule_attrb_rec.value_2:' || l_rule_attrb_rec.value_2);
1159 cn_message_pkg.debug ('load_rule_attributes: l_rule_attrb_rec.value_1:' || l_rule_attrb_rec.value_1);
1160
1161 cn_message_pkg.debug ('load_rule_attributes: Before call to CN_RuleAttribute_PVT.Create_RuleAttribute for attribute: ' || attr_rec.rule_attribute);
1162
1163 CN_RuleAttribute_PVT.Create_RuleAttribute
1164 ( p_api_version => 1.0,
1165 p_init_msg_list => fnd_api.g_true,
1166 p_commit => FND_API.G_FALSE,
1167 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1168 x_return_status => l_return_status,
1169 x_msg_count => l_msg_count,
1170 x_msg_data => l_msg_data,
1171 x_loading_status => l_loading_status,
1172 p_RuleAttribute_rec => l_rule_attrb_rec);
1173
1174 cn_message_pkg.debug ('load_rule_attributes: Completed creating new rule attribute, l_return_status:' || l_return_status);
1175
1176 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1177 cn_message_pkg.debug ('load_rule_attributes: Error when creating new rule attribute, l_msg_data:' || l_msg_data);
1178 x_err_mssg := l_msg_data;
1179 x_retcode := 2;
1180 x_imp_line_id := attr_rec.imp_line_id;
1181 l_error_code := 'CN_IMP_INVLD_RULE_ATTRB';
1182 update_on_error
1183 (p_line_id => attr_rec.imp_line_id,
1184 p_err_code => l_error_code,
1185 p_err_mssg => x_err_mssg,
1186 p_head_id => p_imp_header.imp_header_id);
1187 x_failed_row := x_failed_row + 1;
1188 GOTO end_rule_attr;
1189 END IF;
1190 cn_message_pkg.write
1191 (p_message_text => 'Completed create Rule Attribute, name: ' || attr_rec.rule_attribute,
1192 p_message_type => 'MILESTONE');
1193 -- Set status to Complete
1194 CN_IMPORT_PVT.update_imp_lines
1195 (p_imp_line_id => attr_rec.imp_line_id,
1196 p_status_code => 'COMPLETE',
1197 p_error_code => '');
1198
1199 << end_rule_attr >>
1200 x_processed_row := x_processed_row + 1;
1201 END LOOP;
1202 EXCEPTION
1203 WHEN OTHERS THEN
1204 cn_message_pkg.debug ('load_rule_attributes: ### IMP ### EXCEPTIONS :' || SQLERRM);
1205 x_err_mssg := SQLERRM;
1206 x_retcode := 2;
1207 x_imp_line_id := l_current_imp_line_id;
1208 l_error_code := 'CN_IMP_INVLD_RULE_ATTRB';
1209 update_on_error
1210 (p_line_id => l_current_imp_line_id,
1211 p_err_code => l_error_code,
1212 p_err_mssg => x_err_mssg,
1213 p_head_id => p_imp_header.imp_header_id);
1214 x_failed_row := x_failed_row + 1;
1215 x_processed_row := x_processed_row + 1;
1216 END load_rule_attributes;
1217
1218 --
1219 -- this will update the row that caused the error
1220 --
1221 PROCEDURE update_on_error
1222 (p_line_id IN NUMBER,
1223 p_err_code IN VARCHAR2,
1224 p_err_mssg IN VARCHAR2,
1225 p_head_id IN NUMBER ) IS
1226 PRAGMA AUTONOMOUS_TRANSACTION;
1227 BEGIN
1228 CN_IMPORT_PVT.update_imp_lines
1229 (p_imp_line_id => p_line_id,
1230 p_status_code => 'FAIL',
1231 p_error_code => p_err_code,
1232 p_error_msg => p_err_mssg);
1233 COMMIT;
1234 END update_on_error;
1235
1236 --
1237 -- this will update all the rows that correspond to the ruleset
1238 --
1239 PROCEDURE update_imp_lines
1240 (p_status IN VARCHAR2,
1241 p_imp_line_id IN NUMBER,
1242 p_ruleset_name IN VARCHAR2,
1243 p_start_date IN VARCHAR2,
1244 p_end_date IN VARCHAR2,
1245 p_ruleset_type IN VARCHAR2,
1246 p_head_id IN NUMBER,
1247 p_error_code IN VARCHAR2,
1248 p_error_mssg IN VARCHAR2,
1249 x_failed_row IN OUT NOCOPY NUMBER,
1250 x_processed_row IN OUT NOCOPY NUMBER) IS
1251
1252 CURSOR c_check_imp_line_id_csr IS
1253 SELECT count(*)
1254 FROM cn_rules_imp_v
1255 WHERE nvl(ruleset_name,FND_API.g_miss_char) = nvl(p_ruleset_name ,FND_API.g_miss_char)
1256 AND nvl(start_date ,FND_API.g_miss_char) = nvl(p_start_date, FND_API.g_miss_char)
1257 AND nvl(end_date,FND_API.g_miss_char) = nvl(p_end_date, FND_API.g_miss_char)
1258 AND nvl(ruleset_type,FND_API.g_miss_char) = nvl(p_ruleset_type, FND_API.g_miss_char)
1259 AND imp_header_id = p_head_id
1260 AND status_code = 'STAGE'
1261 AND imp_line_id <> p_imp_line_id;
1262
1263 l_temp NUMBER := 0;
1264
1265 PRAGMA AUTONOMOUS_TRANSACTION;
1266 BEGIN
1267 cn_message_pkg.debug('Updating cn_rules_imp_v with status:' || p_status);
1268 cn_message_pkg.debug('p_ruleset_name:' || p_ruleset_name);
1269 cn_message_pkg.debug('p_start_date:' || p_start_date);
1270 cn_message_pkg.debug('p_end_date:' || p_end_date);
1271 cn_message_pkg.debug('p_ruleset_type:' || p_ruleset_type);
1272 cn_message_pkg.debug('p_head_id:' || p_head_id);
1273 cn_message_pkg.debug('p_imp_line_id:' || p_imp_line_id);
1274
1275 OPEN c_check_imp_line_id_csr;
1276 FETCH c_check_imp_line_id_csr INTO l_temp;
1277 CLOSE c_check_imp_line_id_csr;
1278
1279 -- This check is needed for the case when the user enters just one row for a ruleset and that row is invalid.
1280 IF l_temp = 0 THEN
1281 l_temp := G_INVALID_IMP_LINE_ID;
1282 ELSE
1283 l_temp := p_imp_line_id;
1284 END IF;
1285
1286 cn_message_pkg.debug('l_temp:' || l_temp);
1287
1288 UPDATE cn_rules_imp_v
1289 SET status_code=p_status, error_code=p_error_code, error_msg=p_error_mssg
1290 WHERE nvl(ruleset_name,FND_API.g_miss_char) = nvl(p_ruleset_name ,FND_API.g_miss_char)
1291 AND nvl(start_date ,FND_API.g_miss_char) = nvl(p_start_date, FND_API.g_miss_char)
1292 AND nvl(end_date,FND_API.g_miss_char) = nvl(p_end_date, FND_API.g_miss_char)
1293 AND nvl(ruleset_type,FND_API.g_miss_char) = nvl(p_ruleset_type, FND_API.g_miss_char)
1294 AND imp_header_id = p_head_id
1295 AND status_code = 'STAGE'
1296 AND imp_line_id <> l_temp;
1297
1298 IF p_status <> 'COMPLETE' THEN
1299 x_failed_row := x_failed_row + SQL%rowcount;
1300
1301 -- IF l_temp = p_imp_line_id THEN
1302 -- x_processed_row := x_processed_row + 1;
1303 -- x_failed_row := x_failed_row + 1;
1304 -- END IF;
1305 END IF;
1306
1307 x_processed_row := x_processed_row + SQL%rowcount;
1308
1309 cn_message_pkg.debug ('SQL%rowcount:' || SQL%rowcount || ' x_processed_row:' || x_processed_row || ' x_failed_row:' || x_failed_row);
1310 COMMIT;
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 cn_message_pkg.debug ('### IMP ### EXCEPTION: ' || SQLERRM);
1314 END update_imp_lines;
1315
1316 END CN_IMP_RULE_PVT;
1317