[Home] [Help]
PACKAGE BODY: APPS.CN_CLASSIFICATION_GEN
Source
1 PACKAGE BODY cn_classification_gen AS
2 -- $Header: cnclgenb.pls 120.16 2007/11/26 16:29:43 hanaraya ship $
3
4
5 --
6 -- Package Body Name
7 -- cn_classification_gen
8 -- Purpose
9 -- This package is used by Oracle Commissions to generate the code for
10 -- classifying transactions into revenue classes based on certain rules.
11 -- History
12 -- 12/13/93 Devesh Khatu Created
13 -- 05-04-95 Amy Erickson Updated
14 -- 06-06-95 Amy Erickson Updated
15 -- 06-21-95 Amy Erickson Updated
16 -- 07-14-95 Peter Cook Changed classify_batch parameters
17 -- 07-19-95 Amy Erickson ABS for negative ruleset_id's
18 -- 07-19-95 Amy Erickson Remove debug_pipe references for PCOOK
19 -- 07-21-95 Amy Erickson Bug fix. classify BETWEEN statement incorect.
20 -- 07-23-95 Amy Erickson Bug fix. Classify_batch update code.
21 -- 08-18-95 Amy Erickson Bug fix. is_descendant_of routine.
22 -- 09-05-95 Amy Erickson Bug fix. Output lines were too long.
23 -- 09-18-95 Amy Erickson Bug fix. Parse value fields for apostrophes.
24 -- 10-31-95 Amy Erickson Use cn_message_pkg.debug in generated code.
25 -- 01-08-96 Amy Erickson Updated cn_mod_obj_depends change
26 -- 01-15-96 Amy Erickson Updated changed cn_utils. proc_init.
27 -- 06-27-96 Amy Erickson Updated BETWEEN, LTE, GTE all numeric.
28 -- 11-Feb-98 Achung reference CLIENT_INFO need to use SUBSTRB
29 -- 30-Nov-99 SKhawaja Commented out classify_line section as it is no longer used.
30 -- Changes made to replace periods with Start date and End date.
31 -- 07-Dec-99 SKhawaja Changes made to procedure attribute_rules and create_attribute_rules_expr
32 -- 20-Dec-99 jpendyal Made changes to remove references to package CN_COMMISSION_LINES_PKG as it is obsoleted
33 -- 06-Apr-00 SKhawaja Created procedure Classification_Install
34 -- 09-SEP-00 Kumar Added the where clause in the rules_gen cursor
35 -- 09-SEP-00 Kumar Modified the rule builder and the expression builder to handle
36 -- null in the transaction attribute columns
37 -- modified the processed_period_id to Processed_date
38 -- modified the parameter list of in_descendant_of
39 -- column name data type and rollup_value data type
40 -- 29-SEP-00 Modified the Install classification logic to handle more
41 -- 255 characters in the buffer.
42 -- modified the expr variable from 1900 to 32000
43 --===========================================================================================================
44 -- 16-AUG-01 Kumar Sivasankaran , Modified Added the Sequence by Rule_id
45 -- 07-NOV-01 Kumar Sivasankaran, Modified Added the GENERATED status at the
46 -- end of Classification Install.
47 -- Added INSTFAIL at the end of classification Install package.
48 -- 13-FEB-02 Kumar Sivasankaran, Modified Added to check the rulesets exists
49 -- Delete the cn_objects record before process the classification
50 -- 31-Oct-02 Introduced clob to accomodate large expression. To fix the bug# 2579204
51 -- methods got updated - attribute_rules,create_attribute_rules_expr,file_header
52 -- - package_header,proc_init,rules_recurse_call,rules_recurse_gen
53 -- - rules_recurse_assign, revenue_classes
54 -- 07-MAR-2006 SBADAMI COmmented lines of debug based on the CR / ER# 5019394
55 -- 08-JUN-2006 HANARAYA Added code under attribute_rules procedure for Date Classification (Bug 5191966)
56 --===========================================================================================================
57
58
59
60
61 -- cached_org_id integer;
62 -- cached_org_append varchar2(100);
63 g_module_type cn_rulesets.module_type%type;
64 --
65 -- Private Functions and Procedures
66 --
67
68 --
69 -- Procedure Name
70 -- attribute_rules
71 -- Purpose
72 -- This function generates a conditional expression by ANDing all the
73 -- attribute rules for the rule X_rule_id
74 -- History
75 -- 12-13-93 Devesh Khatu Created
76 -- 09-19-95 Amy Erickson Updated
77 --
78
79 PROCEDURE attribute_rules (
80 X_rule_id cn_rules.rule_id%TYPE,
81 x_org_id cn_rules.org_id%TYPE,
82 code IN OUT NOCOPY cn_utils.clob_code_type) IS
83
84 first_flag VARCHAR2(1);
85 quote VARCHAR2(1);
86
87 l_dummy NUMBER := '-1000';
88 l_dummy_date VARCHAR2(60):= '01/01/1600';
89
90
91 x_value cn_attribute_rules.column_value%TYPE; --AE 09-18-95
92 x_high_value cn_attribute_rules.high_value%TYPE; --AE
93 x_low_value cn_attribute_rules.low_value%TYPE; --AE
94 x_ruleset_id cn_rulesets.ruleset_id%TYPE; -- RK
95
96 cached_org_id integer;
97 cached_org_append varchar2(100);
98
99 CURSOR attribute_rules_cursor IS
100 SELECT LOWER(cocv.name) column_name, cocv.column_datatype data_type,cocv.data_type datatype, --RC Chnaged
101 --from data_type to column_datatype as per new functionality in 11i
102 --which will enable to distinguish between numeric and alphanumeric usage
103 --of the attribute columns
104 column_value value, high_value, low_value,
105 not_flag, dimension_hierarchy_id
106 FROM cn_attribute_rules car, cn_obj_columns_v cocv
107 WHERE rule_id = X_rule_id
108 AND car.org_id=cocv.org_id
109 and car.org_id=x_org_id
110 AND cocv.column_id = car.column_id;
111
112 BEGIN
113
114 cn_debug.print_msg('attribute_rules>>', 1);
115 first_flag := 'Y';
116 x_ruleset_id := g_ruleset_id;
117
118 -- get_cached_org_info (cached_org_id, cached_org_append);
119 cached_org_id:= x_org_id;
120 cached_org_append:='_'||cached_org_id;
121 -- cn_utils.set_org_id(x_org_id);
122 FOR arc IN attribute_rules_cursor LOOP
123 quote := NULL;
124
125 x_value := replace(arc.value, '''', '''''' ) ; --AE 09-18-95
126 x_high_value := replace(arc.high_value, '''', '''''' ) ; --AE
127 x_low_value := replace(arc.low_value, '''', '''''' ) ; --AE
128
129 IF (first_flag = 'Y') THEN
130 first_flag := 'N';
131 ELSE
132 cn_utils.appendcr(code); --AE 09-05-95
133 cn_utils.appind(code, ' AND '); --AE 09-05-95
134 END IF;
135
136 IF (arc.not_flag = 'Y') THEN
137 cn_utils.append(code, ' NOT ');
138 END IF;
139
140 cn_utils.append(code, '(');
141
142 -- RC Replacing the following code with the new 11i feature
143 --IF ((arc.data_type = 'VARCHAR2') OR (arc.data_type = 'DATE')) THEN
144
145 IF (arc.data_type = 'ALPN') OR (arc.data_type = 'DATE') THEN
146 quote := '''';
147 ELSIF (arc.data_type = 'NUMB') THEN
148 quote := ' ';
149 ELSE
150 quote := '''';
151 END IF;
152
153 IF (arc.value IS NOT NULL) THEN
154 IF (arc.dimension_hierarchy_id IS NULL) THEN
155 IF (arc.data_type = 'DATE' OR arc.datatype = 'DATE' ) THEN
156 --AE cn_utils.append(code, 'row.' || arc.column_name || ' = ' || quote || arc.value || quote);
157 -- Kumar cn_utils.append(code, 'row.' || arc.column_name || ' = ' || quote || x_value || quote);
158
159 IF arc.datatype = 'DATE' THEN
160 cn_utils.append(code, 'nvl(row.' || arc.column_name || ' , '
161 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')' ||
162 ' = ' ||' TO_DATE(' || quote || x_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')');
163 ELSE
164 cn_utils.append(code, 'nvl(TO_DATE(row.' || arc.column_name || ',' || quote || 'DD/MM/RRRR'||quote ||')'|| ' , '
165 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')' ||
166 ' = ' ||' TO_DATE(' || quote || x_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')');
167 END IF;
168
169
170 ELSE
171
172 cn_utils.append(code, 'nvl(row.' || arc.column_name ||' , '
173 || quote || l_dummy || quote || ' ) '
174 || ' = ' || quote || x_value || quote);
175
176 END IF;
177 ELSE
178
179 -- Kumar Fixed cn_utils.append(code, 'cn_clsfn_'||ABS(x_ruleset_id)||cached_org_append||'.is_descendant_of(row.' || arc.column_name || ', ' || quote || arc.value || quote || ', ' || arc.dimension_hierarchy_id || ', row.processed_period_id)');
180
181 cn_utils.append(code, 'cn_clsfn_'||ABS(x_ruleset_id)||cached_org_append||'.is_descendant_of(row.' || arc.column_name || ', ' || quote || arc.value || quote || ', ' || arc.dimension_hierarchy_id || ', row.processed_date)');
182
183 END IF;
184
185 ELSIF (arc.high_value IS NOT NULL) THEN
186 IF (arc.low_value IS NOT NULL) THEN
187 IF (arc.data_type = 'DATE' OR arc.datatype = 'DATE') THEN
188 --AE cn_utils.append(code, 'row.' || arc.column_name || ' BETWEEN ' || quote || arc.low_value || quote || ' AND ' || quote || arc.high_value || quote);
189 --062796 cn_utils.append(code, 'row.' || arc.column_name || ' BETWEEN ' || quote || x_low_value || quote || ' AND ' || quote || x_high_value || quote);
190 -- cn_utils.append(code, 'row.' || arc.column_name || ' BETWEEN ' || quote || arc.low_value || quote || ' AND ' || quote || arc.high_value || quote);
191 -- Kumar fix
192 IF arc.datatype = 'DATE' THEN
193 cn_utils.append(code, 'nvl(row.' || arc.column_name || ' , '
194 ||' TO_DATE(' || quote|| l_dummy_date||quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')'
195 || ' BETWEEN ' || ' TO_DATE(' || quote || arc.low_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')'
196 || ' AND ' || ' TO_DATE(' || quote || arc.high_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')');
197 ELSE
198 cn_utils.append(code, 'nvl(TO_DATE(row.' || arc.column_name || ',' || quote || 'DD/MM/RRRR'|| quote ||')'|| ' , '
199 ||' TO_DATE(' || quote|| l_dummy_date||quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')'
200 || ' BETWEEN ' || ' TO_DATE(' || quote || arc.low_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')'
201 || ' AND ' || ' TO_DATE(' || quote || arc.high_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')');
202 END IF;
203
204 ELSE
205
206
207 cn_utils.append(code, 'nvl(row.' || arc.column_name || ' , '
208 || quote || l_dummy || quote || ' )'
209 || ' BETWEEN ' || quote || arc.low_value || quote
210 || ' AND ' || quote || arc.high_value || quote);
211
212 END IF;
213
214
215 ELSE
216 --AE cn_utils.append(code, 'row.' || arc.column_name || ' <= ' || quote || arc.high_value || quote);
217 --062796 cn_utils.append(code, 'row.' || arc.column_name || ' <= ' || quote || x_high_value || quote);
218 --SK cn_utils.append(code, 'row.' || arc.column_name || ' <= ' || arc.high_value );
219
220 -- Kumar Fix cn_utils.append(code, 'row.' || arc.column_name || ' <= ' || quote || arc.high_value || quote);
221 IF (arc.data_type = 'DATE' OR arc.datatype = 'DATE') THEN
222
223 IF arc.datatype = 'DATE' THEN
224 cn_utils.append(code, 'nvl(row.' || arc.column_name || ' , '
225 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')' ||
226 ' <= ' ||' TO_DATE(' || quote || arc.high_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')');
227 ELSE
228 cn_utils.append(code, 'nvl(TO_DATE(row.' || arc.column_name || ',' || quote || 'DD/MM/RRRR'||quote ||')'|| ' , '
229 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')' ||
230 ' <= ' ||' TO_DATE(' || quote || arc.high_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')');
231 END IF;
232 ELSE
233
234
235 cn_utils.append(code, 'nvl(row.' || arc.column_name || ' , '
236 || quote || l_dummy || quote || ' ) '
237 || ' <= ' || quote || arc.high_value || quote);
238
239 END IF;
240 END IF;
241
242 ELSIF (arc.low_value IS NOT NULL) THEN
243 --AE cn_utils.append(code, 'row.' || arc.column_name || ' >= ' || quote || arc.low_value || quote);
244 --062796 cn_utils.append(code, 'row.' || arc.column_name || ' >= ' || quote || x_low_value || quote);
245 --SK cn_utils.appen(code, 'row.' || arc.column_name || ' >= ' || arc.low_value );
246 -- Kumar Fix cn_utils.append(code, 'row.' || arc.column_name || ' >= ' || quote || arc.low_value || quote);
247
248 IF (arc.data_type = 'DATE' OR arc.datatype = 'DATE') THEN
249 IF arc.datatype = 'DATE' THEN
250 cn_utils.append(code, 'nvl(row.' || arc.column_name || ' , '
251 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')' ||
252 ' >= ' ||' TO_DATE(' || quote || arc.low_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')');
253 ELSE
254 cn_utils.append(code, 'nvl(TO_DATE(row.' || arc.column_name || ',' || quote || 'DD/MM/RRRR'||quote ||')'|| ' , '
255 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')' ||
256 ' >= ' ||' TO_DATE(' || quote || arc.low_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')');
257 END IF;
258 ELSE
259
260
261 cn_utils.append(code, 'nvl(row.' || arc.column_name || ' , '
262 || quote || l_dummy || quote || ' ) '
263 || ' >= ' || quote || arc.low_value || quote);
264
265 END IF;
266 END IF;
267 cn_utils.append(code, ')');
268
269 END LOOP;
270 IF (first_flag = 'Y') THEN
271 -- no attribute rules were found for this rule, so always enable it
272 cn_utils.append(code, 'TRUE');
273 END IF;
274
275 cn_utils.append(code, ')');
276
277 cn_debug.print_msg('attribute_rules<<', 1);
278 --cn_utils.unset_org_id;
279 EXCEPTION
280 WHEN NO_DATA_FOUND THEN
281 cn_debug.print_msg('attribute_rules: in exception handler for NO_DATA_FOUND', 1);
282 RETURN;
283 END attribute_rules;
284
285 --
286 -- Procedure Name
287 -- create_attribute_rules_expr
288 -- Purpose
289 -- This function generates a conditional expression by ANDing all the
290 -- attribute rules for the rule X_rule_id as default and by creating
291 -- a complex expression using the cn_rule_attr_expression table when
292 -- such data is available
293 -- History
294 -- 06/22/98 Ramkarthik Kalyanasundaram Created
295 --
296
297 PROCEDURE create_attribute_rules_expr (X_rule_id cn_rules.rule_id%TYPE,
298 x_org_id cn_rules.org_id%TYPE,
299 code IN OUT NOCOPY cn_utils.clob_code_type) IS
300 l_dummy NUMBER := '-1000';
301 first_flag VARCHAR2(1);
302 quote VARCHAR2(1);
303 default_flag VARCHAR2(1);
304 x_ruleset_id cn_rulesets.ruleset_id%TYPE;
305 buffer VARCHAR2(32000);
306 cached_org_id integer;
307 cached_org_append varchar2(100);
308 l_dummy_date VARCHAR2(60):= '01/01/1600';
309
310
311 TYPE intermediate_expr_type IS RECORD ( id cn_rule_attr_expression.rule_attr_expression_id%TYPE,
312 expr_id cn_rule_attr_expression.operand_expression_id%TYPE,
313 expr CLOB);
314 TYPE expr_type IS TABLE OF intermediate_expr_type INDEX BY BINARY_INTEGER;
315 expr_table expr_type;
316 table_index NUMBER := 0;
317
318 CURSOR attribute_rules_cursor IS
319 SELECT attribute_rule_id, LOWER(cocv.name) column_name,
320 cocv.column_datatype data_type,cocv.data_type datatype,column_value value,
321 high_value, low_value, not_flag, dimension_hierarchy_id
322 FROM cn_attribute_rules car, cn_obj_columns_v cocv
323 WHERE rule_id = X_rule_id
324 and car.org_id= cocv.org_id
325 AND cocv.column_id = car.column_id;
326 CURSOR rule_attr_expr_cursor IS
327 SELECT *
328 FROM cn_rule_attr_expression
329 WHERE rule_id = X_rule_id
330 ORDER BY rule_attr_expression_id;
331 BEGIN
332 cn_debug.print_msg('attribute_rules>>', 1);
333 first_flag := 'Y';
334 default_flag := 'Y';
335
336 -- get_cached_org_info (cached_org_id, cached_org_append);
337 cached_org_id:= x_org_id;
338 cached_org_append:='_'||cached_org_id;
339
340 SELECT ruleset_id
341 INTO x_ruleset_id
342 FROM cn_rules
343 WHERE rule_id = X_rule_id
344 AND org_id=x_org_id
345 AND ruleset_id = g_ruleset_id;
346
347
348 FOR arc IN attribute_rules_cursor LOOP
349 quote := NULL;
350 first_flag := 'N';
351 table_index := table_index + 1;
352 expr_table(table_index).id := arc.attribute_rule_id;
353 if (DBMS_LOB.ISTEMPORARY(expr_table(table_index).expr) IS NULL) THEN
354 DBMS_LOB.CREATETEMPORARY(expr_table(table_index).expr,FALSE,DBMS_LOB.CALL);
355 END IF;
356 IF (arc.not_flag = 'Y') THEN
357 buffer := ' NOT';
358 DBMS_LOB.WRITE(expr_table(table_index).expr,length(buffer),1,buffer);
359 END IF;
360 --expr_table(table_index).expr := expr_table(table_index).expr || '(';
361 buffer:='(';
362 DBMS_LOB.WRITEAPPEND(expr_table(table_index).expr,length(buffer),buffer);
363 IF ((arc.data_type = 'VARCHAR2') OR (arc.data_type = 'DATE')) THEN
364 quote := '''';
365 ELSIF (arc.data_type = 'NUMB') THEN
366 quote := ' ';
367 ELSE
368 quote := '''';
369 END IF;
370 IF (arc.value IS NOT NULL) THEN
371 IF (arc.dimension_hierarchy_id IS NULL) THEN
372 IF (arc.data_type = 'DATE' OR arc.datatype = 'DATE') THEN
373 IF arc.datatype = 'DATE' THEN
374 buffer := 'nvl(row.' || arc.column_name || ' , '
375 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')' ||
376 ' = ' ||' TO_DATE(' || quote || arc.value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')';
377 ELSE
378 buffer := 'nvl(TO_DATE(row.' || arc.column_name || ',' || quote || 'DD/MM/RRRR'||quote ||')'|| ' , '
379 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')' ||
380 ' = ' ||' TO_DATE(' || quote || arc.value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')';
381 END IF;
382 ELSE
383 buffer := 'nvl(row.' || arc.column_name ||
384 ' , ' || quote || l_dummy || quote || ' ) ' ||
385 ' = ' || quote || arc.value || quote;
386
387
388 END IF ;
389 DBMS_LOB.WRITEAPPEND(expr_table(table_index).expr,length(buffer),buffer);
390 ELSE
391 buffer := 'cn_clsfn_'||ABS(x_ruleset_id)||cached_org_append
392 ||'.is_descendant_of(row.' || arc.column_name
393 || ', ' || quote || arc.value || quote || ', '
394 || arc.dimension_hierarchy_id || ', row.processed_date)';
395 DBMS_LOB.WRITEAPPEND(expr_table(table_index).expr,length(buffer),buffer);
396 END IF;
397 ELSIF (arc.high_value IS NOT NULL) THEN
398 IF (arc.low_value IS NOT NULL) THEN
399 IF (arc.data_type = 'DATE' OR arc.datatype = 'DATE') THEN
400 IF arc.datatype = 'DATE' THEN
401 buffer := 'nvl(row.' || arc.column_name || ' , '
402 ||' TO_DATE(' || quote|| l_dummy_date||quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')'
403 || ' BETWEEN ' || ' TO_DATE(' || quote || arc.low_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')'
404 || ' AND ' || ' TO_DATE(' || quote || arc.high_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')';
405 ELSE
406 buffer := 'nvl(TO_DATE(row.' || arc.column_name || ',' || quote || 'DD/MM/RRRR'|| quote ||')'|| ' , '
407 ||' TO_DATE(' || quote|| l_dummy_date||quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')'
408 || ' BETWEEN ' || ' TO_DATE(' || quote || arc.low_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')'
409 || ' AND ' || ' TO_DATE(' || quote || arc.high_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')';
410 END IF;
411
412 ELSE
413 buffer := ' nvl(row.' || arc.column_name
414 || ' , '|| quote || l_dummy || quote || ' ) '
415 || ' BETWEEN ' || quote || arc.low_value || quote || ' AND '
416 || quote || arc.high_value || quote;
417
418 END IF;
419 DBMS_LOB.WRITEAPPEND(expr_table(table_index).expr,length(buffer),buffer);
420 ELSE
421 IF (arc.data_type = 'DATE' OR arc.datatype = 'DATE') THEN
422 IF arc.datatype = 'DATE' THEN
423 buffer := 'nvl(row.' || arc.column_name || ' , '
424 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')' ||
425 ' <= ' ||' TO_DATE(' || quote || arc.high_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')';
426 ELSE
427 buffer := 'nvl(TO_DATE(row.' || arc.column_name || ',' || quote || 'DD/MM/RRRR'||quote ||')'|| ' , '
428 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')' ||
429 ' <= ' ||' TO_DATE(' || quote || arc.high_value || quote ||',' ||quote ||'DD/MM/RRRR'||quote||')';
430 END IF;
431 ELSE
432 buffer := 'nvl(row.' || arc.column_name || ' , ' || quote || l_dummy || quote || ' ) ' || ' <= ' || quote || arc.high_value || quote;
433 END IF;
434 DBMS_LOB.WRITEAPPEND(expr_table(table_index).expr,length(buffer),buffer);
435 END IF;
436 ELSIF (arc.low_value IS NOT NULL) THEN
437 IF (arc.data_type = 'DATE' OR arc.datatype = 'DATE') THEN
438 buffer := 'nvl(TO_DATE(row.' || arc.column_name || ',' || quote || 'DD/MM/RRRR'||quote ||')'|| ' , '
439 ||' TO_DATE(' || quote || l_dummy_date || quote ||',' ||quote ||'DD/MM/RRRR'||quote ||')'|| ')'
440 || ' >= ' || quote || arc.low_value || quote;
441 ELSE
442 buffer := 'nvl(row.' || arc.column_name
443 || ' ,' || quote || l_dummy || quote || ' ) '
444 || ' >= ' || quote || arc.low_value || quote;
445 END IF;
446 DBMS_LOB.WRITEAPPEND(expr_table(table_index).expr,length(buffer),buffer);
447 END IF;
448 buffer := ')';
449 DBMS_LOB.WRITEAPPEND(expr_table(table_index).expr,length(buffer),buffer);
450 END LOOP;
451
452
453 FOR rae IN rule_attr_expr_cursor LOOP
454 default_flag := 'N';
455 table_index := table_index + 1;
456 expr_table(table_index).id := rae.rule_attr_expression_id;
457 expr_table(table_index).expr_id := rae.operand_expression_id;
458 buffer := '(';
459 if (DBMS_LOB.ISTEMPORARY(expr_table(table_index).expr) IS NULL) THEN
460 DBMS_LOB.CREATETEMPORARY(expr_table(table_index).expr,FALSE,DBMS_LOB.CALL);
461 END IF;
462 DBMS_LOB.WRITE(expr_table(table_index).expr,length(buffer),1,buffer);
463 FOR v_counter IN 1 .. table_index LOOP
464 IF rae.operand1_ra_rae_flag = 'RA' THEN
465 IF expr_table(v_counter).id = rae.operand1 THEN
466 DBMS_LOB.APPEND(expr_table(table_index).expr,expr_table(v_counter).expr);
467 END IF;
468 ELSIF rae.operand1_ra_rae_flag = 'RAE' THEN
469 IF expr_table(v_counter).expr_id = rae.operand1 THEN
470 DBMS_LOB.APPEND(expr_table(table_index).expr,expr_table(v_counter).expr);
471 END IF;
472 END IF;
473 END LOOP;
474 IF rae.operator = 0 THEN
475 buffer:= 'AND';
476 ELSE
477 buffer:= 'OR';
478 END IF;
479 DBMS_LOB.WRITEAPPEND(expr_table(table_index).expr,length(buffer),buffer);
480 FOR v_counter IN 1 .. table_index LOOP
481 IF rae.operand2_ra_rae_flag = 'RA' THEN
482 IF expr_table(v_counter).id = rae.operand2 THEN
483 DBMS_LOB.APPEND(expr_table(table_index).expr,expr_table(v_counter).expr);
484 END IF;
485 ELSIF rae.operand2_ra_rae_flag = 'RAE' THEN
486 IF expr_table(v_counter).expr_id = rae.operand2 THEN
487 DBMS_LOB.APPEND(expr_table(table_index).expr,expr_table(v_counter).expr);
488 END IF;
489 END IF;
490 END LOOP;
491 buffer:= ')';
492 DBMS_LOB.WRITEAPPEND(expr_table(table_index).expr,length(buffer),buffer);
493 END LOOP;
494 IF (first_flag = 'Y') THEN
495 -- no attribute rules were found for this rule, so always enable it
496 cn_utils.append(code, 'TRUE)');
497 END IF;
498 IF(first_flag = 'N' AND default_flag = 'Y') THEN
499 attribute_rules(X_rule_id,x_org_id,code);
500 ELSE
501 cn_utils.append(code, expr_table(table_index).expr, ')');
502 END IF;
503 FOR clob_counter IN 1 .. expr_table.count LOOP
504 DBMS_LOB.FREETEMPORARY(expr_table(clob_counter).expr);
505 END LOOP;
506 cn_debug.print_msg('attribute_rules<<', 1);
507 EXCEPTION
508 WHEN NO_DATA_FOUND THEN
509 cn_debug.print_msg('create_attribute_rules_expr: in exception handler for NO_DATA_FOUND', 1); RETURN;
510 END create_attribute_rules_expr;
511
512
513 PROCEDURE pkg_init_boilerplate (
514 code IN OUT NOCOPY cn_utils.code_type,
515 package_name cn_obj_packages_v.name%TYPE,
516 description cn_obj_packages_v.description%TYPE,
517 object_type VARCHAR2) IS
518
519 X_userid VARCHAR2(20);
520
521 BEGIN
522 cn_utils.appendcr(code, '-- +======================================================================+ --');
523 cn_utils.appendcr(code, '-- | Copyright (c) 1998 Oracle Corporation | --');
524 cn_utils.appendcr(code, '-- | Redwood Shores, California, USA | --');
525 cn_utils.appendcr(code, '-- | All rights reserved. | --');
526 cn_utils.appendcr(code, '-- +======================================================================+ --');
527 cn_utils.appendcr(code);
528
529 SELECT user INTO X_userid FROM sys.dual;
530
531 cn_utils.appendcr(code, 'SET VERIFY OFF');
532 cn_utils.appendcr(code, 'WHENEVER SQLERROR EXIT FAILURE ROLLBACK;');
533
534 END pkg_init_boilerplate;
535
536 PROCEDURE create_object( module_id cn_modules.module_id%TYPE,
537 name cn_objects.name%TYPE,
538 object_type cn_objects.object_type%TYPE,
539 object_id IN OUT NOCOPY cn_objects.object_id%TYPE,
540 description IN OUT NOCOPY cn_objects.description%TYPE,
541 x_org_id cn_rulesets.org_id%TYPE) IS
542 next_id NUMBER;
543 BEGIN
544
545 next_id := cn_utils.get_repository(module_id,x_org_id);
546 SELECT cn_objects_s.NEXTVAL
547 INTO object_id
548 FROM dual;
549 insert into CN_OBJECTS (
550 OBJECT_ID, DEPENDENCY_MAP_COMPLETE, NAME, OBJECT_TYPE, REPOSITORY_ID,
551 OBJECT_STATUS, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
552 LAST_UPDATE_LOGIN, DESCRIPTION, NEXT_SYNCHRONIZATION_DATE,
553 SYNCHRONIZATION_FREQUENCY, DATA_LENGTH, DATA_TYPE, NULLABLE, PRIMARY_KEY,
554 POSITION, DIMENSION_ID, DATA_SCALE, COLUMN_TYPE, TABLE_ID, UNIQUE_FLAG,
555 PACKAGE_TYPE, PACKAGE_SPECIFICATION_ID, PARAMETER_LIST, RETURN_TYPE,
556 PROCEDURE_TYPE, PACKAGE_ID, START_VALUE, INCREMENT_VALUE, STATEMENT_TEXT,
557 ALIAS, TABLE_LEVEL, TABLE_TYPE, WHEN_CLAUSE, TRIGGERING_EVENT, EVENT_ID,
558 PUBLIC_FLAG, CHILD_FLAG, FOR_EACH_ROW, TRIGGER_TYPE, USER_COLUMN_NAME,
559 SEED_OBJECT_ID, PRIMARY_KEY_COLUMN_ID, USER_NAME_COLUMN_ID,
560 CONNECT_TO_USERNAME, CONNECT_TO_PASSWORD, CONNECT_TO_HOST, USER_NAME,
561 SCHEMA, FOREIGN_KEY, CLASSIFICATION_COLUMN,OBJECT_VERSION_NUMBER,ORG_ID)
562 values(
563 object_id, 'N', name,object_type, next_id,
564 'A', NULL, NULL, NULL, NULL,
565 NULL, description, NULL,
566 NULL, NULL, NULL, NULL, NULL,
567 NULL, NULL, NULL, NULL, NULL, NULL,
568 'CLS', NULL, NULL, NULL,
569 NULL, NULL, NULL, NULL, NULL,
570 NULL, NULL, NULL, NULL, NULL, NULL,
571 NULL, NULL, NULL, NULL, NULL,
572 NULL, NULL, NULL,
573 NULL, NULL, NULL, NULL,
574 'CN', NULL, NULL,1,x_org_id);
575 END create_object;
576
577 PROCEDURE file_header (
578 module_id cn_modules.module_id%TYPE,
579 package_name cn_obj_packages_v.name%TYPE,
580 package_type cn_obj_packages_v.package_type%TYPE,
581
582 package_spec_id IN OUT NOCOPY cn_obj_packages_v.package_id%TYPE,
583 package_body_id IN OUT NOCOPY cn_obj_packages_v.package_id%TYPE,
584 package_spec_desc IN OUT NOCOPY cn_obj_packages_v.description%TYPE,
585 package_body_desc IN OUT NOCOPY cn_obj_packages_v.description%TYPE,
586
587 spec_code IN OUT NOCOPY cn_utils.clob_code_type,
588 body_code IN OUT NOCOPY cn_utils.clob_code_type,
589 x_org_id cn_rulesets.org_id%TYPE) IS
590
591 x_rowid ROWID;
592 null_id NUMBER;
593 delete_flag VARCHAR2(1) := 'Y';
594 l_count NUMBER;
595
596 BEGIN
597 --cn_utils.set_org_id(x_org_id);
598 delete from cn_objects where name = package_name and object_type = 'PKS';
599 delete from cn_objects where name = package_name and object_type = 'PKB';
600
601 -- Find the package objects AE 01-08-96
602 cn_utils.find_object(package_name,'PKS',package_spec_id, package_spec_desc,x_org_id);
603 cn_utils.find_object(package_name,'PKB',package_body_id, package_body_desc,x_org_id);
604
605 IF (package_spec_id IS NULL AND package_body_id IS NULL) THEN
606 create_object(module_id, package_name, 'PKS',package_spec_id, package_spec_desc,x_org_id);
607 create_object(module_id, package_name, 'PKB',package_body_id, package_body_desc,x_org_id);
608 delete_flag := 'N';
609 END IF;
610
611 -- Delete module source code from cn_source
612 -- Delete module object dependencies for this module
613 IF (delete_flag = 'Y') THEN
614 cn_utils.delete_module(module_id, package_spec_id, package_body_id,x_org_id);
615 END IF;
616
617 cn_utils.init_code (package_spec_id, spec_code); -- AE 05-02-95
618 cn_utils.init_code (package_body_id, body_code); -- AE 05-02-95
619
620 -- pkg_init_boilerplate(spec_code, package_name, package_spec_desc, 'PKS');
621 -- pkg_init_boilerplate(body_code, package_name, package_body_desc, 'PKB');
622
623
624 cn_utils.indent(spec_code, 1);
625 cn_utils.indent(body_code, 1);
626 -- cn_utils.unset_org_id;
627
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN
630 create_object(module_id, package_name, 'PKS',package_spec_id, package_spec_desc,x_org_id);
631
632 create_object(module_id, package_name, 'PKB',package_body_id, package_body_desc,x_org_id);
633 cn_utils.init_code (package_spec_id, spec_code); -- AE 05-02-95
634 cn_utils.init_code (package_body_id, body_code); -- AE 05-02-95
635
636 -- pkg_init_boilerplate(spec_code, package_name, package_spec_desc, 'PKS');
637 -- pkg_init_boilerplate(body_code, package_name, package_body_desc, 'PKB');
638
639
640 -- cn_utils.indent(spec_code, 1);
641 -- cn_utils.indent(body_code, 1);
642 END file_header;
643
644 PROCEDURE package_header (
645 code IN OUT NOCOPY cn_utils.clob_code_type,
646 package_name cn_obj_packages_v.name%TYPE,
647 description cn_obj_packages_v.description%TYPE,
648 object_type VARCHAR2,
649 x_org_id cn_rules.org_id%TYPE) IS
650
651 X_userid VARCHAR2(20);
652 x_package_name varchar2(100);
653 cached_org_id integer;
654 cached_org_append varchar2(100);
655 BEGIN
656
657 --get_cached_org_info (cached_org_id, cached_org_append);
658 cached_org_id:=x_org_id;
659 cached_org_append:='_'||cached_org_id;
660
661
662 SELECT user INTO X_userid FROM sys.dual;
663
664 X_package_name := Lower(package_name) || cached_org_append;
665
666 cn_utils.appendcr(code, 'DEFINE PACKAGE_NAME="' || LOWER(x_package_name) || '"');
667
668 IF (object_type = 'PKS') THEN
669 --RC 26-SEP-99 Commenting out the following line and replacing it with a
670 --the actual package name
671
672 --cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE &' || 'PACKAGE_NAME AS');
673 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE '||x_package_name|| ' AS --START-OF-PKS');
674 ELSE
675 --cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE BODY &' || 'PACKAGE_NAME AS');
676 cn_utils.appendcr(code, 'CREATE OR REPLACE PACKAGE BODY '||x_package_name|| ' AS --START-OF-PKB');
677 END IF;
678
679 cn_utils.appendcr(code);
680 cn_utils.indent(code, 1);
681
682 END package_header;
683
684 PROCEDURE proc_init (
685 procedure_name cn_obj_procedures_v.name%TYPE,
686 description cn_obj_procedures_v.description%TYPE,
687 parameter_list cn_obj_procedures_v.parameter_list%TYPE,
688 procedure_type cn_obj_procedures_v.procedure_type%TYPE,
689 return_type cn_obj_procedures_v.return_type%TYPE,
690 code IN OUT NOCOPY cn_utils.clob_code_type,
691 object_type varchar2) IS
692
693 X_rowid ROWID;
694
695 BEGIN
696
697 -- Generate procedure header and parameters in both spec and body
698 IF (procedure_type = 'P') THEN
699 cn_utils.appind(code, 'PROCEDURE ' || procedure_name);
700 ELSIF (procedure_type = 'F') THEN
701 cn_utils.appind(code, 'FUNCTION ' || procedure_name);
702 END IF;
703
704 IF (parameter_list IS NOT NULL) THEN
705 cn_utils.append(code, ' (' || parameter_list || ')');
706 END IF;
707
708 IF (procedure_type = 'F') THEN
709 cn_utils.append(code, ' RETURN ' || return_type);
710 END IF;
711
712 IF (object_type = 'PKS') THEN
713 cn_utils.appendcr(code, ';');
714 cn_utils.appendcr(code);
715 ELSE
716 cn_utils.appendcr(code, ' IS');
717 END IF;
718
719 END proc_init;
720
721 PROCEDURE rules_recurse_call (
722 X_ruleset_id cn_rules.ruleset_id%TYPE,
723 x_org_id cn_rules.org_id%TYPE,
724 X_rule_id cn_rules_hierarchy.rule_id%TYPE,
725 code IN OUT NOCOPY cn_utils.clob_code_type,
726 X_package_count cn_rules.package_id%TYPE) IS
727
728 X_revenue_class NUMBER;
729 X_expense_ccid number;
730 X_liability_ccid number;
731 dummy NUMBER(7);
732 cached_org_id INTEGER;
733 cached_org_append VARCHAR2(100);
734
735 BEGIN
736 -- get_cached_org_info (cached_org_id, cached_org_append);
737
738 cached_org_id:=x_org_id;
739 cached_org_append:='_'||cached_org_id;
740 -- cn_utils.set_org_id(x_org_id);
741 create_attribute_rules_expr(X_rule_id,x_org_id,code);
742 cn_utils.appendcr(code, ' THEN');
743 cn_utils.indent(code, 1);
744
745 SELECT count(*)
746 INTO dummy
747 FROM cn_rules_hierarchy crh
748 WHERE parent_rule_id = X_rule_id
749 AND org_id=x_org_id
750 AND ruleset_id = x_ruleset_id;
751
752 IF (dummy>0) THEN
753 IF (g_module_type = 'REVCLS' OR g_module_type = 'PECLS')
754 THEN
755 cn_utils.appindcr(code,
756 'revenue_class := cn_clsfn_'||ABS(X_ruleset_id)||'_'||x_package_count||cached_org_append||'.classify_rule_'||ABS(x_rule_id)||' (row);');
757 ELSE
758 cn_utils.appindcr(code,
759 'cn_clsfn_'||ABS(X_ruleset_id)||'_'||x_package_count||cached_org_append||'.classify_rule_'||ABS(x_rule_id)||' (row, expense_ccid, liability_ccid);');
760 end if;
761 ELSE
762 IF (g_module_type = 'REVCLS' OR g_module_type = 'PECLS')
763 THEN
764 SELECT revenue_class_id
765 INTO X_revenue_class
766 FROM cn_rules
767 WHERE rule_id = X_rule_id
768 AND ruleset_id = X_ruleset_id;
769
770 IF (X_revenue_class IS NOT NULL) THEN
771 cn_utils.appindcr(code, 'revenue_class := ' || X_revenue_class || ';');
772 ELSE
773 cn_utils.appindcr(code, 'NULL;');
774 END IF;
775 ELSE
776 SELECT expense_ccid
777 INTO X_expense_ccid
778 FROM cn_rules
779 WHERE rule_id = X_rule_id
780 AND org_id=x_org_id
781 AND ruleset_id = X_ruleset_id;
782 SELECT liability_ccid
783 INTO X_liability_ccid
784 FROM cn_rules
785 WHERE rule_id = X_rule_id
786 AND org_id=x_org_id
787 AND ruleset_id = X_ruleset_id;
788
789 IF (X_expense_ccid IS NOT NULL) THEN
790 cn_utils.appindcr(code, 'expense_ccid := ' || X_expense_ccid || ';');
791 ELSE
792 cn_utils.appindcr(code, 'NULL;');
793 END IF;
794 IF (X_liability_ccid IS NOT NULL) THEN
795 cn_utils.appindcr(code, 'liability_ccid := ' || X_liability_ccid || ';');
796 ELSE
797 cn_utils.appindcr(code, 'NULL;');
798 END IF;
799 END IF;
800
801 END IF;
802 --cn_utils.unset_org_id;
803 EXCEPTION
804 WHEN NO_DATA_FOUND THEN
805 cn_debug.print_msg('rules_recurse: in exception handler for NO_DATA_FOUND', 1);
806 RETURN;
807 END rules_recurse_call;
808
809 --Cisco API
810 --Add the Classify function spec in the Ruleset package
811 PROCEDURE add_classify_spec (
812 code IN OUT NOCOPY cn_utils.clob_code_type) IS
813
814 procedure_name cn_obj_procedures_v.name%TYPE;
815 procedure_desc cn_obj_procedures_v.description%TYPE;
816 parameter_list varchar2(10000);
817
818
819 BEGIN
820
821 procedure_name := 'CLASSIFY';
822 procedure_desc := 'classify transactions outside OIC for Cisco';
823 cn_utils.appind(code, 'FUNCTION ' || procedure_name);
824
825 cn_utils.appendcr(code,' (' ||'p_source_doc_type VARCHAR2, ');
826 cn_utils.appendcr(code,'p_attribute50 VARCHAR2, ');
827 cn_utils.appendcr(code,'p_invoice_number VARCHAR2, ');
828 cn_utils.appendcr(code,'p_attribute73 VARCHAR2, ');
829 cn_utils.appendcr(code,'p_attribute87 VARCHAR2, ');
830 cn_utils.appendcr(code,'p_forecast_id NUMBER, ');
831 cn_utils.appendcr(code,'p_upside_quantity NUMBER, ');
832 cn_utils.appendcr(code,'p_upside_amount NUMBER, ');
833 cn_utils.appendcr(code,'p_uom_code VARCHAR2, ');
834 cn_utils.appendcr(code,'p_source_trx_id NUMBER, ');
835 cn_utils.appendcr(code,'p_source_trx_line_id NUMBER, ');
836 cn_utils.appendcr(code,'p_source_trx_sales_line_id NUMBER, ');
837 cn_utils.appendcr(code,'p_negated_flag VARCHAR2, ');
838 cn_utils.appendcr(code,'p_customer_id NUMBER, ');
839 cn_utils.appendcr(code,'p_inventory_item_id NUMBER, ');
840 cn_utils.appendcr(code,'p_order_number NUMBER, ');
841 cn_utils.appendcr(code,'p_booked_date DATE, ');
842 cn_utils.appendcr(code,'p_invoice_date DATE, ');
843 cn_utils.appendcr(code,'p_bill_to_address_id NUMBER, ');
844 cn_utils.appendcr(code,'p_ship_to_address_id NUMBER, ');
845 cn_utils.appendcr(code,'p_bill_to_contact_id NUMBER, ');
846 cn_utils.appendcr(code,'p_ship_to_contact_id NUMBER, ');
847 cn_utils.appendcr(code,'p_adj_comm_lines_api_id NUMBER, ');
848 cn_utils.appendcr(code,'p_adjust_date DATE, ');
849 cn_utils.appendcr(code,'p_adjusted_by VARCHAR2, ');
850 cn_utils.appendcr(code,'p_revenue_type VARCHAR2, ');
851 cn_utils.appendcr(code,'p_adjust_rollup_flag VARCHAR2, ');
852 cn_utils.appendcr(code,'p_adjust_comments VARCHAR2, ');
853 cn_utils.appendcr(code,'p_adjust_status VARCHAR2, ');
854 cn_utils.appendcr(code,'p_line_number NUMBER, ');
855 cn_utils.appendcr(code,'p_request_id NUMBER, ');
856 cn_utils.appendcr(code,'p_program_id NUMBER, ');
857 cn_utils.appendcr(code,'p_program_application_id NUMBER, ');
858 cn_utils.appendcr(code,'p_program_update_date DATE, ');
859 cn_utils.appendcr(code,'p_type VARCHAR2, ');
860 cn_utils.appendcr(code,'p_sales_channel VARCHAR2, ');
861 cn_utils.appendcr(code,'p_object_version_number NUMBER, ');
862 cn_utils.appendcr(code,'p_split_pct NUMBER, ');
863 cn_utils.appendcr(code,'p_split_status VARCHAR2, ');
864 cn_utils.appendcr(code,'p_security_group_id NUMBER, ');
865 cn_utils.appendcr(code,'p_parent_header_id NUMBER, ');
866 cn_utils.appendcr(code,'p_trx_type VARCHAR2, ');
867 cn_utils.appendcr(code,'p_status VARCHAR2, ');
868 cn_utils.appendcr(code,'p_pre_processed_code VARCHAR2, ');
869 cn_utils.appendcr(code,'p_comm_lines_api_id NUMBER, ');
870 cn_utils.appendcr(code,'p_source_trx_number VARCHAR2, ');
871 cn_utils.appendcr(code,'p_quota_id NUMBER, ');
872 cn_utils.appendcr(code,'p_srp_plan_assign_id NUMBER, ');
873 cn_utils.appendcr(code,'p_revenue_class_id NUMBER, ');
874 cn_utils.appendcr(code,'p_role_id NUMBER, ');
875 cn_utils.appendcr(code,'p_comp_group_id NUMBER, ');
876 cn_utils.appendcr(code,'p_commission_amount NUMBER, ');
877 cn_utils.appendcr(code,'p_trx_batch_id NUMBER, ');
878 cn_utils.appendcr(code,'p_reversal_flag VARCHAR2, ');
879 cn_utils.appendcr(code,'p_reversal_header_id NUMBER, ');
880 cn_utils.appendcr(code,'p_reason_code VARCHAR2, ');
881 cn_utils.appendcr(code,'p_comments VARCHAR2, ');
882 cn_utils.appendcr(code,'p_attribute_category VARCHAR2, ');
883 cn_utils.appendcr(code,'p_attribute1 VARCHAR2, ');
884 cn_utils.appendcr(code,'p_attribute2 VARCHAR2, ');
885 cn_utils.appendcr(code,'p_attribute3 VARCHAR2, ');
886 cn_utils.appendcr(code,'p_attribute4 VARCHAR2, ');
887 cn_utils.appendcr(code,'p_attribute5 VARCHAR2, ');
888 cn_utils.appendcr(code,'p_attribute6 VARCHAR2, ');
889 cn_utils.appendcr(code,'p_attribute7 VARCHAR2, ');
890 cn_utils.appendcr(code,'p_attribute8 VARCHAR2, ');
891 cn_utils.appendcr(code,'p_attribute9 VARCHAR2, ');
892 cn_utils.appendcr(code,'p_attribute10 VARCHAR2, ');
893 cn_utils.appendcr(code,'p_attribute11 VARCHAR2, ');
894 cn_utils.appendcr(code,'p_attribute12 VARCHAR2, ');
895 cn_utils.appendcr(code,'p_attribute13 VARCHAR2, ');
896 cn_utils.appendcr(code,'p_attribute14 VARCHAR2, ');
897 cn_utils.appendcr(code,'p_attribute15 VARCHAR2, ');
898 cn_utils.appendcr(code,'p_attribute16 VARCHAR2, ');
899 cn_utils.appendcr(code,'p_attribute17 VARCHAR2, ');
900 cn_utils.appendcr(code,'p_attribute18 VARCHAR2, ');
901 cn_utils.appendcr(code,'p_attribute19 VARCHAR2, ');
902 cn_utils.appendcr(code,'p_attribute20 VARCHAR2, ');
903 cn_utils.appendcr(code,'p_attribute21 VARCHAR2, ');
904 cn_utils.appendcr(code,'p_attribute22 VARCHAR2, ');
905 cn_utils.appendcr(code,'p_attribute23 VARCHAR2, ');
906 cn_utils.appendcr(code,'p_attribute24 VARCHAR2, ');
907 cn_utils.appendcr(code,'p_attribute25 VARCHAR2, ');
908 cn_utils.appendcr(code,'p_attribute26 VARCHAR2, ');
909 cn_utils.appendcr(code,'p_attribute27 VARCHAR2, ');
910 cn_utils.appendcr(code,'p_attribute28 VARCHAR2, ');
911 cn_utils.appendcr(code,'p_attribute29 VARCHAR2, ');
912 cn_utils.appendcr(code,'p_attribute30 VARCHAR2, ');
913 cn_utils.appendcr(code,'p_attribute31 VARCHAR2, ');
914 cn_utils.appendcr(code,'p_attribute32 VARCHAR2, ');
915 cn_utils.appendcr(code,'p_attribute33 VARCHAR2, ');
916 cn_utils.appendcr(code,'p_attribute34 VARCHAR2, ');
917 cn_utils.appendcr(code,'p_attribute35 VARCHAR2, ');
918 cn_utils.appendcr(code,'p_attribute36 VARCHAR2, ');
919 cn_utils.appendcr(code,'p_attribute37 VARCHAR2, ');
920 cn_utils.appendcr(code,'p_attribute38 VARCHAR2, ');
921 cn_utils.appendcr(code,'p_attribute39 VARCHAR2, ');
922 cn_utils.appendcr(code,'p_attribute40 VARCHAR2, ');
923 cn_utils.appendcr(code,'p_attribute41 VARCHAR2, ');
924 cn_utils.appendcr(code,'p_attribute42 VARCHAR2, ');
925 cn_utils.appendcr(code,'p_attribute43 VARCHAR2, ');
926 cn_utils.appendcr(code,'p_attribute44 VARCHAR2, ');
927 cn_utils.appendcr(code,'p_attribute45 VARCHAR2, ');
928 cn_utils.appendcr(code,'p_attribute46 VARCHAR2, ');
929 cn_utils.appendcr(code,'p_attribute47 VARCHAR2, ');
930 cn_utils.appendcr(code,'p_attribute48 VARCHAR2, ');
931 cn_utils.appendcr(code,'p_attribute49 VARCHAR2, ');
932 cn_utils.appendcr(code,'p_attribute51 VARCHAR2, ');
933 cn_utils.appendcr(code,'p_attribute52 VARCHAR2, ');
934 cn_utils.appendcr(code,'p_attribute53 VARCHAR2, ');
935 cn_utils.appendcr(code,'p_attribute54 VARCHAR2, ');
936 cn_utils.appendcr(code,'p_attribute55 VARCHAR2, ');
937 cn_utils.appendcr(code,'p_attribute56 VARCHAR2, ');
938 cn_utils.appendcr(code,'p_attribute57 VARCHAR2, ');
939 cn_utils.appendcr(code,'p_attribute58 VARCHAR2, ');
940 cn_utils.appendcr(code,'p_attribute59 VARCHAR2, ');
941 cn_utils.appendcr(code,'p_attribute60 VARCHAR2, ');
942 cn_utils.appendcr(code,'p_attribute61 VARCHAR2, ');
943 cn_utils.appendcr(code,'p_attribute62 VARCHAR2, ');
944 cn_utils.appendcr(code,'p_attribute63 VARCHAR2, ');
945 cn_utils.appendcr(code,'p_attribute64 VARCHAR2, ');
946 cn_utils.appendcr(code,'p_attribute65 VARCHAR2, ');
947 cn_utils.appendcr(code,'p_attribute66 VARCHAR2, ');
948 cn_utils.appendcr(code,'p_attribute67 VARCHAR2, ');
949 cn_utils.appendcr(code,'p_attribute68 VARCHAR2, ');
950 cn_utils.appendcr(code,'p_attribute69 VARCHAR2, ');
951 cn_utils.appendcr(code,'p_attribute70 VARCHAR2, ');
952 cn_utils.appendcr(code,'p_attribute71 VARCHAR2, ');
953 cn_utils.appendcr(code,'p_attribute72 VARCHAR2, ');
954 cn_utils.appendcr(code,'p_attribute74 VARCHAR2, ');
955 cn_utils.appendcr(code,'p_attribute75 VARCHAR2, ');
956 cn_utils.appendcr(code,'p_attribute76 VARCHAR2, ');
957 cn_utils.appendcr(code,'p_attribute77 VARCHAR2, ');
958 cn_utils.appendcr(code,'p_attribute78 VARCHAR2, ');
959 cn_utils.appendcr(code,'p_attribute79 VARCHAR2, ');
960 cn_utils.appendcr(code,'p_attribute80 VARCHAR2, ');
961 cn_utils.appendcr(code,'p_attribute81 VARCHAR2, ');
962 cn_utils.appendcr(code,'p_attribute82 VARCHAR2, ');
963 cn_utils.appendcr(code,'p_attribute83 VARCHAR2, ');
964 cn_utils.appendcr(code,'p_attribute84 VARCHAR2, ');
965 cn_utils.appendcr(code,'p_attribute85 VARCHAR2, ');
966 cn_utils.appendcr(code,'p_attribute86 VARCHAR2, ');
967 cn_utils.appendcr(code,'p_attribute88 VARCHAR2, ');
968 cn_utils.appendcr(code,'p_attribute89 VARCHAR2, ');
969 cn_utils.appendcr(code,'p_attribute90 VARCHAR2, ');
970 cn_utils.appendcr(code,'p_attribute91 VARCHAR2, ');
971 cn_utils.appendcr(code,'p_attribute92 VARCHAR2, ');
972 cn_utils.appendcr(code,'p_attribute93 VARCHAR2, ');
973 cn_utils.appendcr(code,'p_attribute94 VARCHAR2, ');
974 cn_utils.appendcr(code,'p_attribute95 VARCHAR2, ');
975 cn_utils.appendcr(code,'p_attribute96 VARCHAR2, ');
976 cn_utils.appendcr(code,'p_attribute97 VARCHAR2, ');
977 cn_utils.appendcr(code,'p_attribute98 VARCHAR2, ');
978 cn_utils.appendcr(code,'p_attribute99 VARCHAR2, ');
979 cn_utils.appendcr(code,'p_attribute100 VARCHAR2, ');
980 cn_utils.appendcr(code,'p_last_update_date DATE, ');
981 cn_utils.appendcr(code,'p_last_updated_by NUMBER, ');
982 cn_utils.appendcr(code,'p_last_update_login NUMBER, ');
983 cn_utils.appendcr(code,'p_creation_date DATE, ');
984 cn_utils.appendcr(code,'p_created_by NUMBER, ');
985 cn_utils.appendcr(code,'p_org_id NUMBER, ');
986 cn_utils.appendcr(code,'p_exchange_rate NUMBER, ');
987 cn_utils.appendcr(code,'p_commission_header_id NUMBER, ');
988 cn_utils.appendcr(code,'p_direct_salesrep_id NUMBER, ');
989 cn_utils.appendcr(code,'p_processed_date DATE, ');
990 cn_utils.appendcr(code,'p_processed_period_id NUMBER, ');
991 cn_utils.appendcr(code,'p_rollup_date DATE, ');
992 cn_utils.appendcr(code,'p_transaction_amount NUMBER, ');
993 cn_utils.appendcr(code,'p_quantity NUMBER, ');
994 cn_utils.appendcr(code,'p_discount_percentage NUMBER, ');
995 cn_utils.appendcr(code,'p_margin_percentage NUMBER, ');
996 cn_utils.appendcr(code,'p_orig_currency_code VARCHAR2, ');
997 cn_utils.appendcr(code,'p_transaction_amount_orig NUMBER' || ')');
998
999 cn_utils.append(code, ' RETURN ' || 'NUMBER');
1000 cn_utils.appendcr(code, ';');
1001 cn_utils.appendcr(code);
1002
1003
1004 EXCEPTION
1005 WHEN NO_DATA_FOUND THEN
1006 cn_debug.print_msg('add_classify_spec: in exception handler for NO_DATA_FOUND', 1);
1007 RETURN;
1008 END add_classify_spec;
1009 --Cisco API
1010
1011 --Cisco API
1012 --Add the Classify function body in the Ruleset package
1013 PROCEDURE add_classify_body (
1014 code IN OUT NOCOPY cn_utils.clob_code_type) IS
1015
1016 procedure_name cn_obj_procedures_v.name%TYPE;
1017 procedure_desc cn_obj_procedures_v.description%TYPE;
1018
1019
1020 BEGIN
1021
1022 procedure_name := 'CLASSIFY';
1023 procedure_desc := 'classify transactions outside OIC for Cisco';
1024 cn_utils.appind(code, 'FUNCTION ' || procedure_name);
1025
1026 cn_utils.appendcr(code,' (' ||'p_source_doc_type VARCHAR2, ');
1027 cn_utils.appendcr(code,'p_attribute50 VARCHAR2, ');
1028 cn_utils.appendcr(code,'p_invoice_number VARCHAR2, ');
1029 cn_utils.appendcr(code,'p_attribute73 VARCHAR2, ');
1030 cn_utils.appendcr(code,'p_attribute87 VARCHAR2, ');
1031 cn_utils.appendcr(code,'p_forecast_id NUMBER, ');
1032 cn_utils.appendcr(code,'p_upside_quantity NUMBER, ');
1033 cn_utils.appendcr(code,'p_upside_amount NUMBER, ');
1034 cn_utils.appendcr(code,'p_uom_code VARCHAR2, ');
1035 cn_utils.appendcr(code,'p_source_trx_id NUMBER, ');
1036 cn_utils.appendcr(code,'p_source_trx_line_id NUMBER, ');
1037 cn_utils.appendcr(code,'p_source_trx_sales_line_id NUMBER, ');
1038 cn_utils.appendcr(code,'p_negated_flag VARCHAR2, ');
1039 cn_utils.appendcr(code,'p_customer_id NUMBER, ');
1040 cn_utils.appendcr(code,'p_inventory_item_id NUMBER, ');
1041 cn_utils.appendcr(code,'p_order_number NUMBER, ');
1042 cn_utils.appendcr(code,'p_booked_date DATE, ');
1043 cn_utils.appendcr(code,'p_invoice_date DATE, ');
1044 cn_utils.appendcr(code,'p_bill_to_address_id NUMBER, ');
1045 cn_utils.appendcr(code,'p_ship_to_address_id NUMBER, ');
1046 cn_utils.appendcr(code,'p_bill_to_contact_id NUMBER, ');
1047 cn_utils.appendcr(code,'p_ship_to_contact_id NUMBER, ');
1048 cn_utils.appendcr(code,'p_adj_comm_lines_api_id NUMBER, ');
1049 cn_utils.appendcr(code,'p_adjust_date DATE, ');
1050 cn_utils.appendcr(code,'p_adjusted_by VARCHAR2, ');
1051 cn_utils.appendcr(code,'p_revenue_type VARCHAR2, ');
1052 cn_utils.appendcr(code,'p_adjust_rollup_flag VARCHAR2, ');
1053 cn_utils.appendcr(code,'p_adjust_comments VARCHAR2, ');
1054 cn_utils.appendcr(code,'p_adjust_status VARCHAR2, ');
1055 cn_utils.appendcr(code,'p_line_number NUMBER, ');
1056 cn_utils.appendcr(code,'p_request_id NUMBER, ');
1057 cn_utils.appendcr(code,'p_program_id NUMBER, ');
1058 cn_utils.appendcr(code,'p_program_application_id NUMBER, ');
1059 cn_utils.appendcr(code,'p_program_update_date DATE, ');
1060 cn_utils.appendcr(code,'p_type VARCHAR2, ');
1061 cn_utils.appendcr(code,'p_sales_channel VARCHAR2, ');
1062 cn_utils.appendcr(code,'p_object_version_number NUMBER, ');
1063 cn_utils.appendcr(code,'p_split_pct NUMBER, ');
1064 cn_utils.appendcr(code,'p_split_status VARCHAR2, ');
1065 cn_utils.appendcr(code,'p_security_group_id NUMBER, ');
1066 cn_utils.appendcr(code,'p_parent_header_id NUMBER, ');
1067 cn_utils.appendcr(code,'p_trx_type VARCHAR2, ');
1068 cn_utils.appendcr(code,'p_status VARCHAR2, ');
1069 cn_utils.appendcr(code,'p_pre_processed_code VARCHAR2, ');
1070 cn_utils.appendcr(code,'p_comm_lines_api_id NUMBER, ');
1071 cn_utils.appendcr(code,'p_source_trx_number VARCHAR2, ');
1072 cn_utils.appendcr(code,'p_quota_id NUMBER, ');
1073 cn_utils.appendcr(code,'p_srp_plan_assign_id NUMBER, ');
1074 cn_utils.appendcr(code,'p_revenue_class_id NUMBER, ');
1075 cn_utils.appendcr(code,'p_role_id NUMBER, ');
1076 cn_utils.appendcr(code,'p_comp_group_id NUMBER, ');
1077 cn_utils.appendcr(code,'p_commission_amount NUMBER, ');
1078 cn_utils.appendcr(code,'p_trx_batch_id NUMBER, ');
1079 cn_utils.appendcr(code,'p_reversal_flag VARCHAR2, ');
1080 cn_utils.appendcr(code,'p_reversal_header_id NUMBER, ');
1081 cn_utils.appendcr(code,'p_reason_code VARCHAR2, ');
1082 cn_utils.appendcr(code,'p_comments VARCHAR2, ');
1083 cn_utils.appendcr(code,'p_attribute_category VARCHAR2, ');
1084 cn_utils.appendcr(code,'p_attribute1 VARCHAR2, ');
1085 cn_utils.appendcr(code,'p_attribute2 VARCHAR2, ');
1086 cn_utils.appendcr(code,'p_attribute3 VARCHAR2, ');
1087 cn_utils.appendcr(code,'p_attribute4 VARCHAR2, ');
1088 cn_utils.appendcr(code,'p_attribute5 VARCHAR2, ');
1089 cn_utils.appendcr(code,'p_attribute6 VARCHAR2, ');
1090 cn_utils.appendcr(code,'p_attribute7 VARCHAR2, ');
1091 cn_utils.appendcr(code,'p_attribute8 VARCHAR2, ');
1092 cn_utils.appendcr(code,'p_attribute9 VARCHAR2, ');
1093 cn_utils.appendcr(code,'p_attribute10 VARCHAR2, ');
1094 cn_utils.appendcr(code,'p_attribute11 VARCHAR2, ');
1095 cn_utils.appendcr(code,'p_attribute12 VARCHAR2, ');
1096 cn_utils.appendcr(code,'p_attribute13 VARCHAR2, ');
1097 cn_utils.appendcr(code,'p_attribute14 VARCHAR2, ');
1098 cn_utils.appendcr(code,'p_attribute15 VARCHAR2, ');
1099 cn_utils.appendcr(code,'p_attribute16 VARCHAR2, ');
1100 cn_utils.appendcr(code,'p_attribute17 VARCHAR2, ');
1101 cn_utils.appendcr(code,'p_attribute18 VARCHAR2, ');
1102 cn_utils.appendcr(code,'p_attribute19 VARCHAR2, ');
1103 cn_utils.appendcr(code,'p_attribute20 VARCHAR2, ');
1104 cn_utils.appendcr(code,'p_attribute21 VARCHAR2, ');
1105 cn_utils.appendcr(code,'p_attribute22 VARCHAR2, ');
1106 cn_utils.appendcr(code,'p_attribute23 VARCHAR2, ');
1107 cn_utils.appendcr(code,'p_attribute24 VARCHAR2, ');
1108 cn_utils.appendcr(code,'p_attribute25 VARCHAR2, ');
1109 cn_utils.appendcr(code,'p_attribute26 VARCHAR2, ');
1110 cn_utils.appendcr(code,'p_attribute27 VARCHAR2, ');
1111 cn_utils.appendcr(code,'p_attribute28 VARCHAR2, ');
1112 cn_utils.appendcr(code,'p_attribute29 VARCHAR2, ');
1113 cn_utils.appendcr(code,'p_attribute30 VARCHAR2, ');
1114 cn_utils.appendcr(code,'p_attribute31 VARCHAR2, ');
1115 cn_utils.appendcr(code,'p_attribute32 VARCHAR2, ');
1116 cn_utils.appendcr(code,'p_attribute33 VARCHAR2, ');
1117 cn_utils.appendcr(code,'p_attribute34 VARCHAR2, ');
1118 cn_utils.appendcr(code,'p_attribute35 VARCHAR2, ');
1119 cn_utils.appendcr(code,'p_attribute36 VARCHAR2, ');
1120 cn_utils.appendcr(code,'p_attribute37 VARCHAR2, ');
1121 cn_utils.appendcr(code,'p_attribute38 VARCHAR2, ');
1122 cn_utils.appendcr(code,'p_attribute39 VARCHAR2, ');
1123 cn_utils.appendcr(code,'p_attribute40 VARCHAR2, ');
1124 cn_utils.appendcr(code,'p_attribute41 VARCHAR2, ');
1125 cn_utils.appendcr(code,'p_attribute42 VARCHAR2, ');
1126 cn_utils.appendcr(code,'p_attribute43 VARCHAR2, ');
1127 cn_utils.appendcr(code,'p_attribute44 VARCHAR2, ');
1128 cn_utils.appendcr(code,'p_attribute45 VARCHAR2, ');
1129 cn_utils.appendcr(code,'p_attribute46 VARCHAR2, ');
1130 cn_utils.appendcr(code,'p_attribute47 VARCHAR2, ');
1131 cn_utils.appendcr(code,'p_attribute48 VARCHAR2, ');
1132 cn_utils.appendcr(code,'p_attribute49 VARCHAR2, ');
1133 cn_utils.appendcr(code,'p_attribute51 VARCHAR2, ');
1134 cn_utils.appendcr(code,'p_attribute52 VARCHAR2, ');
1135 cn_utils.appendcr(code,'p_attribute53 VARCHAR2, ');
1136 cn_utils.appendcr(code,'p_attribute54 VARCHAR2, ');
1137 cn_utils.appendcr(code,'p_attribute55 VARCHAR2, ');
1138 cn_utils.appendcr(code,'p_attribute56 VARCHAR2, ');
1139 cn_utils.appendcr(code,'p_attribute57 VARCHAR2, ');
1140 cn_utils.appendcr(code,'p_attribute58 VARCHAR2, ');
1141 cn_utils.appendcr(code,'p_attribute59 VARCHAR2, ');
1142 cn_utils.appendcr(code,'p_attribute60 VARCHAR2, ');
1143 cn_utils.appendcr(code,'p_attribute61 VARCHAR2, ');
1144 cn_utils.appendcr(code,'p_attribute62 VARCHAR2, ');
1145 cn_utils.appendcr(code,'p_attribute63 VARCHAR2, ');
1146 cn_utils.appendcr(code,'p_attribute64 VARCHAR2, ');
1147 cn_utils.appendcr(code,'p_attribute65 VARCHAR2, ');
1148 cn_utils.appendcr(code,'p_attribute66 VARCHAR2, ');
1149 cn_utils.appendcr(code,'p_attribute67 VARCHAR2, ');
1150 cn_utils.appendcr(code,'p_attribute68 VARCHAR2, ');
1151 cn_utils.appendcr(code,'p_attribute69 VARCHAR2, ');
1152 cn_utils.appendcr(code,'p_attribute70 VARCHAR2, ');
1153 cn_utils.appendcr(code,'p_attribute71 VARCHAR2, ');
1154 cn_utils.appendcr(code,'p_attribute72 VARCHAR2, ');
1155 cn_utils.appendcr(code,'p_attribute74 VARCHAR2, ');
1156 cn_utils.appendcr(code,'p_attribute75 VARCHAR2, ');
1157 cn_utils.appendcr(code,'p_attribute76 VARCHAR2, ');
1158 cn_utils.appendcr(code,'p_attribute77 VARCHAR2, ');
1159 cn_utils.appendcr(code,'p_attribute78 VARCHAR2, ');
1160 cn_utils.appendcr(code,'p_attribute79 VARCHAR2, ');
1161 cn_utils.appendcr(code,'p_attribute80 VARCHAR2, ');
1162 cn_utils.appendcr(code,'p_attribute81 VARCHAR2, ');
1163 cn_utils.appendcr(code,'p_attribute82 VARCHAR2, ');
1164 cn_utils.appendcr(code,'p_attribute83 VARCHAR2, ');
1165 cn_utils.appendcr(code,'p_attribute84 VARCHAR2, ');
1166 cn_utils.appendcr(code,'p_attribute85 VARCHAR2, ');
1167 cn_utils.appendcr(code,'p_attribute86 VARCHAR2, ');
1168 cn_utils.appendcr(code,'p_attribute88 VARCHAR2, ');
1169 cn_utils.appendcr(code,'p_attribute89 VARCHAR2, ');
1170 cn_utils.appendcr(code,'p_attribute90 VARCHAR2, ');
1171 cn_utils.appendcr(code,'p_attribute91 VARCHAR2, ');
1172 cn_utils.appendcr(code,'p_attribute92 VARCHAR2, ');
1173 cn_utils.appendcr(code,'p_attribute93 VARCHAR2, ');
1174 cn_utils.appendcr(code,'p_attribute94 VARCHAR2, ');
1175 cn_utils.appendcr(code,'p_attribute95 VARCHAR2, ');
1176 cn_utils.appendcr(code,'p_attribute96 VARCHAR2, ');
1177 cn_utils.appendcr(code,'p_attribute97 VARCHAR2, ');
1178 cn_utils.appendcr(code,'p_attribute98 VARCHAR2, ');
1179 cn_utils.appendcr(code,'p_attribute99 VARCHAR2, ');
1180 cn_utils.appendcr(code,'p_attribute100 VARCHAR2, ');
1181 cn_utils.appendcr(code,'p_last_update_date DATE, ');
1182 cn_utils.appendcr(code,'p_last_updated_by NUMBER, ');
1183 cn_utils.appendcr(code,'p_last_update_login NUMBER, ');
1184 cn_utils.appendcr(code,'p_creation_date DATE, ');
1185 cn_utils.appendcr(code,'p_created_by NUMBER, ');
1186 cn_utils.appendcr(code,'p_org_id NUMBER, ');
1187 cn_utils.appendcr(code,'p_exchange_rate NUMBER, ');
1188 cn_utils.appendcr(code,'p_commission_header_id NUMBER, ');
1189 cn_utils.appendcr(code,'p_direct_salesrep_id NUMBER, ');
1190 cn_utils.appendcr(code,'p_processed_date DATE, ');
1191 cn_utils.appendcr(code,'p_processed_period_id NUMBER, ');
1192 cn_utils.appendcr(code,'p_rollup_date DATE, ');
1193 cn_utils.appendcr(code,'p_transaction_amount NUMBER, ');
1194 cn_utils.appendcr(code,'p_quantity NUMBER, ');
1195 cn_utils.appendcr(code,'p_discount_percentage NUMBER, ');
1196 cn_utils.appendcr(code,'p_margin_percentage NUMBER, ');
1197 cn_utils.appendcr(code,'p_orig_currency_code VARCHAR2, ');
1198 cn_utils.appendcr(code,'p_transaction_amount_orig NUMBER' || ')');
1199
1200 cn_utils.append(code, ' RETURN ' || 'NUMBER');
1201 cn_utils.appendcr(code, ' IS');
1202
1203
1204 cn_utils.appindcr(code, ' l_rec cn_commission_headers_all%rowtype;');
1205 cn_utils.appindcr(code, 'BEGIN');
1206 cn_utils.indent(code, 1);
1207 cn_utils.appindcr(code,'l_rec.source_doc_type := p_source_doc_type;');
1208 cn_utils.appindcr(code,'l_rec.attribute50 := p_attribute50;');
1209 cn_utils.appindcr(code,'l_rec.invoice_number := p_invoice_number;');
1210 cn_utils.appindcr(code,'l_rec.attribute73 := p_attribute73;');
1211 cn_utils.appindcr(code,'l_rec.attribute87 := p_attribute87;');
1212 cn_utils.appindcr(code,'l_rec.forecast_id := p_forecast_id;');
1213 cn_utils.appindcr(code,'l_rec.upside_quantity := p_upside_quantity;');
1214 cn_utils.appindcr(code,'l_rec.upside_amount := p_upside_amount;');
1215 cn_utils.appindcr(code,'l_rec.uom_code := p_uom_code;');
1216 cn_utils.appindcr(code,'l_rec.source_trx_id := p_source_trx_id;');
1217 cn_utils.appindcr(code,'l_rec.source_trx_line_id := p_source_trx_line_id;');
1218 cn_utils.appindcr(code,'l_rec.source_trx_sales_line_id := p_source_trx_sales_line_id;');
1219 cn_utils.appindcr(code,'l_rec.negated_flag := p_negated_flag;');
1220 cn_utils.appindcr(code,'l_rec.customer_id := p_customer_id;');
1221 cn_utils.appindcr(code,'l_rec.inventory_item_id := p_inventory_item_id;');
1222 cn_utils.appindcr(code,'l_rec.order_number := p_order_number;');
1223 cn_utils.appindcr(code,'l_rec.booked_date := p_booked_date;');
1224 cn_utils.appindcr(code,'l_rec.invoice_date := p_invoice_date;');
1225 cn_utils.appindcr(code,'l_rec.bill_to_address_id := p_bill_to_address_id;');
1226 cn_utils.appindcr(code,'l_rec.ship_to_address_id := p_ship_to_address_id;');
1227 cn_utils.appindcr(code,'l_rec.bill_to_contact_id := p_bill_to_contact_id;');
1228 cn_utils.appindcr(code,'l_rec.ship_to_contact_id := p_ship_to_contact_id;');
1229 cn_utils.appindcr(code,'l_rec.adj_comm_lines_api_id := p_adj_comm_lines_api_id;');
1230 cn_utils.appindcr(code,'l_rec.adjust_date := p_adjust_date;');
1231 cn_utils.appindcr(code,'l_rec.adjusted_by := p_adjusted_by;');
1232 cn_utils.appindcr(code,'l_rec.revenue_type := p_revenue_type;');
1233 cn_utils.appindcr(code,'l_rec.adjust_rollup_flag := p_adjust_rollup_flag;');
1234 cn_utils.appindcr(code,'l_rec.adjust_comments := p_adjust_comments;');
1235 cn_utils.appindcr(code,'l_rec.adjust_status := p_adjust_status;');
1236 cn_utils.appindcr(code,'l_rec.line_number := p_line_number;');
1237 cn_utils.appindcr(code,'l_rec.request_id := p_request_id;');
1238 cn_utils.appindcr(code,'l_rec.program_id := p_program_id;');
1239 cn_utils.appindcr(code,'l_rec.program_application_id := p_program_application_id;');
1240 cn_utils.appindcr(code,'l_rec.program_update_date := p_program_update_date;');
1241 cn_utils.appindcr(code,'l_rec.type := p_type;');
1242 cn_utils.appindcr(code,'l_rec.sales_channel := p_sales_channel;');
1243 cn_utils.appindcr(code,'l_rec.object_version_number := p_object_version_number;');
1244 cn_utils.appindcr(code,'l_rec.split_pct := p_split_pct;');
1245 cn_utils.appindcr(code,'l_rec.split_status := p_split_status;');
1246 cn_utils.appindcr(code,'l_rec.security_group_id := p_security_group_id;');
1247 cn_utils.appindcr(code,'l_rec.parent_header_id := p_parent_header_id;');
1248 cn_utils.appindcr(code,'l_rec.trx_type := p_trx_type;');
1249 cn_utils.appindcr(code,'l_rec.status := p_status;');
1250 cn_utils.appindcr(code,'l_rec.pre_processed_code := p_pre_processed_code;');
1251 cn_utils.appindcr(code,'l_rec.comm_lines_api_id := p_comm_lines_api_id;');
1252 cn_utils.appindcr(code,'l_rec.source_trx_number := p_source_trx_number;');
1253 cn_utils.appindcr(code,'l_rec.quota_id := p_quota_id;');
1254 cn_utils.appindcr(code,'l_rec.srp_plan_assign_id := p_srp_plan_assign_id;');
1255 cn_utils.appindcr(code,'l_rec.revenue_class_id := p_revenue_class_id;');
1256 cn_utils.appindcr(code,'l_rec.role_id := p_role_id;');
1257 cn_utils.appindcr(code,'l_rec.comp_group_id := p_comp_group_id;');
1258 cn_utils.appindcr(code,'l_rec.commission_amount := p_commission_amount;');
1259 cn_utils.appindcr(code,'l_rec.trx_batch_id := p_trx_batch_id;');
1260 cn_utils.appindcr(code,'l_rec.reversal_flag := p_reversal_flag;');
1261 cn_utils.appindcr(code,'l_rec.reversal_header_id := p_reversal_header_id;');
1262 cn_utils.appindcr(code,'l_rec.reason_code := p_reason_code;');
1263 cn_utils.appindcr(code,'l_rec.comments := p_comments;');
1264 cn_utils.appindcr(code,'l_rec.attribute_category := p_attribute_category;');
1265 cn_utils.appindcr(code,'l_rec.attribute1 := p_attribute1;');
1266 cn_utils.appindcr(code,'l_rec.attribute2 := p_attribute2;');
1267 cn_utils.appindcr(code,'l_rec.attribute3 := p_attribute3;');
1268 cn_utils.appindcr(code,'l_rec.attribute4 := p_attribute4;');
1269 cn_utils.appindcr(code,'l_rec.attribute5 := p_attribute5;');
1270 cn_utils.appindcr(code,'l_rec.attribute6 := p_attribute6;');
1271 cn_utils.appindcr(code,'l_rec.attribute7 := p_attribute7;');
1272 cn_utils.appindcr(code,'l_rec.attribute8 := p_attribute8;');
1273 cn_utils.appindcr(code,'l_rec.attribute9 := p_attribute9;');
1274 cn_utils.appindcr(code,'l_rec.attribute10 := p_attribute10;');
1275 cn_utils.appindcr(code,'l_rec.attribute11 := p_attribute11;');
1276 cn_utils.appindcr(code,'l_rec.attribute12 := p_attribute12;');
1277 cn_utils.appindcr(code,'l_rec.attribute13 := p_attribute13;');
1278 cn_utils.appindcr(code,'l_rec.attribute14 := p_attribute14;');
1279 cn_utils.appindcr(code,'l_rec.attribute15 := p_attribute15;');
1280 cn_utils.appindcr(code,'l_rec.attribute16 := p_attribute16;');
1281 cn_utils.appindcr(code,'l_rec.attribute17 := p_attribute17;');
1282 cn_utils.appindcr(code,'l_rec.attribute18 := p_attribute18;');
1283 cn_utils.appindcr(code,'l_rec.attribute19 := p_attribute19;');
1284 cn_utils.appindcr(code,'l_rec.attribute20 := p_attribute20;');
1285 cn_utils.appindcr(code,'l_rec.attribute21 := p_attribute21;');
1286 cn_utils.appindcr(code,'l_rec.attribute22 := p_attribute22;');
1287 cn_utils.appindcr(code,'l_rec.attribute23 := p_attribute23;');
1288 cn_utils.appindcr(code,'l_rec.attribute24 := p_attribute24;');
1289 cn_utils.appindcr(code,'l_rec.attribute25 := p_attribute25;');
1290 cn_utils.appindcr(code,'l_rec.attribute26 := p_attribute26;');
1291 cn_utils.appindcr(code,'l_rec.attribute27 := p_attribute27;');
1292 cn_utils.appindcr(code,'l_rec.attribute28 := p_attribute28;');
1293 cn_utils.appindcr(code,'l_rec.attribute29 := p_attribute29;');
1294 cn_utils.appindcr(code,'l_rec.attribute30 := p_attribute30;');
1295 cn_utils.appindcr(code,'l_rec.attribute31 := p_attribute31;');
1296 cn_utils.appindcr(code,'l_rec.attribute32 := p_attribute32;');
1297 cn_utils.appindcr(code,'l_rec.attribute33 := p_attribute33;');
1298 cn_utils.appindcr(code,'l_rec.attribute34 := p_attribute34;');
1299 cn_utils.appindcr(code,'l_rec.attribute35 := p_attribute35;');
1300 cn_utils.appindcr(code,'l_rec.attribute36 := p_attribute36;');
1301 cn_utils.appindcr(code,'l_rec.attribute37 := p_attribute37;');
1302 cn_utils.appindcr(code,'l_rec.attribute38 := p_attribute38;');
1303 cn_utils.appindcr(code,'l_rec.attribute39 := p_attribute39;');
1304 cn_utils.appindcr(code,'l_rec.attribute40 := p_attribute40;');
1305 cn_utils.appindcr(code,'l_rec.attribute41 := p_attribute41;');
1306 cn_utils.appindcr(code,'l_rec.attribute42 := p_attribute42;');
1307 cn_utils.appindcr(code,'l_rec.attribute43 := p_attribute43;');
1308 cn_utils.appindcr(code,'l_rec.attribute44 := p_attribute44;');
1309 cn_utils.appindcr(code,'l_rec.attribute45 := p_attribute45;');
1310 cn_utils.appindcr(code,'l_rec.attribute46 := p_attribute46;');
1311 cn_utils.appindcr(code,'l_rec.attribute47 := p_attribute47;');
1312 cn_utils.appindcr(code,'l_rec.attribute48 := p_attribute48;');
1313 cn_utils.appindcr(code,'l_rec.attribute49 := p_attribute49;');
1314 cn_utils.appindcr(code,'l_rec.attribute51 := p_attribute51;');
1315 cn_utils.appindcr(code,'l_rec.attribute52 := p_attribute52;');
1316 cn_utils.appindcr(code,'l_rec.attribute53 := p_attribute53;');
1317 cn_utils.appindcr(code,'l_rec.attribute54 := p_attribute54;');
1318 cn_utils.appindcr(code,'l_rec.attribute55 := p_attribute55;');
1319 cn_utils.appindcr(code,'l_rec.attribute56 := p_attribute56;');
1320 cn_utils.appindcr(code,'l_rec.attribute57 := p_attribute57;');
1321 cn_utils.appindcr(code,'l_rec.attribute58 := p_attribute58;');
1322 cn_utils.appindcr(code,'l_rec.attribute59 := p_attribute59;');
1323 cn_utils.appindcr(code,'l_rec.attribute60 := p_attribute60;');
1324 cn_utils.appindcr(code,'l_rec.attribute61 := p_attribute61;');
1325 cn_utils.appindcr(code,'l_rec.attribute62 := p_attribute62;');
1326 cn_utils.appindcr(code,'l_rec.attribute63 := p_attribute63;');
1327 cn_utils.appindcr(code,'l_rec.attribute64 := p_attribute64;');
1328 cn_utils.appindcr(code,'l_rec.attribute65 := p_attribute65;');
1329 cn_utils.appindcr(code,'l_rec.attribute66 := p_attribute66;');
1330 cn_utils.appindcr(code,'l_rec.attribute67 := p_attribute67;');
1331 cn_utils.appindcr(code,'l_rec.attribute68 := p_attribute68;');
1332 cn_utils.appindcr(code,'l_rec.attribute69 := p_attribute69;');
1333 cn_utils.appindcr(code,'l_rec.attribute70 := p_attribute70;');
1334 cn_utils.appindcr(code,'l_rec.attribute71 := p_attribute71;');
1335 cn_utils.appindcr(code,'l_rec.attribute72 := p_attribute72;');
1336 cn_utils.appindcr(code,'l_rec.attribute74 := p_attribute74;');
1337 cn_utils.appindcr(code,'l_rec.attribute75 := p_attribute75;');
1338 cn_utils.appindcr(code,'l_rec.attribute76 := p_attribute76;');
1339 cn_utils.appindcr(code,'l_rec.attribute77 := p_attribute77;');
1340 cn_utils.appindcr(code,'l_rec.attribute78 := p_attribute78;');
1341 cn_utils.appindcr(code,'l_rec.attribute79 := p_attribute79;');
1342 cn_utils.appindcr(code,'l_rec.attribute80 := p_attribute80;');
1343 cn_utils.appindcr(code,'l_rec.attribute81 := p_attribute81;');
1344 cn_utils.appindcr(code,'l_rec.attribute82 := p_attribute82;');
1345 cn_utils.appindcr(code,'l_rec.attribute83 := p_attribute83;');
1346 cn_utils.appindcr(code,'l_rec.attribute84 := p_attribute84;');
1347 cn_utils.appindcr(code,'l_rec.attribute85 := p_attribute85;');
1348 cn_utils.appindcr(code,'l_rec.attribute86 := p_attribute86;');
1349 cn_utils.appindcr(code,'l_rec.attribute88 := p_attribute88;');
1350 cn_utils.appindcr(code,'l_rec.attribute89 := p_attribute89;');
1351 cn_utils.appindcr(code,'l_rec.attribute90 := p_attribute90;');
1352 cn_utils.appindcr(code,'l_rec.attribute91 := p_attribute91;');
1353 cn_utils.appindcr(code,'l_rec.attribute92 := p_attribute92;');
1354 cn_utils.appindcr(code,'l_rec.attribute93 := p_attribute93;');
1355 cn_utils.appindcr(code,'l_rec.attribute94 := p_attribute94;');
1356 cn_utils.appindcr(code,'l_rec.attribute95 := p_attribute95;');
1357 cn_utils.appindcr(code,'l_rec.attribute96 := p_attribute96;');
1358 cn_utils.appindcr(code,'l_rec.attribute97 := p_attribute97;');
1359 cn_utils.appindcr(code,'l_rec.attribute98 := p_attribute98;');
1360 cn_utils.appindcr(code,'l_rec.attribute99 := p_attribute99;');
1361 cn_utils.appindcr(code,'l_rec.attribute100 := p_attribute100;');
1362 cn_utils.appindcr(code,'l_rec.last_update_date := p_last_update_date;');
1363 cn_utils.appindcr(code,'l_rec.last_updated_by := p_last_updated_by;');
1364 cn_utils.appindcr(code,'l_rec.last_update_login := p_last_update_login;');
1365 cn_utils.appindcr(code,'l_rec.creation_date := p_creation_date;');
1366 cn_utils.appindcr(code,'l_rec.created_by := p_created_by;');
1367 cn_utils.appindcr(code,'l_rec.org_id := p_org_id;');
1368 cn_utils.appindcr(code,'l_rec.exchange_rate := p_exchange_rate;');
1369 cn_utils.appindcr(code,'l_rec.commission_header_id := p_commission_header_id;');
1370 cn_utils.appindcr(code,'l_rec.direct_salesrep_id := p_direct_salesrep_id;');
1371 cn_utils.appindcr(code,'l_rec.processed_date := p_processed_date;');
1372 cn_utils.appindcr(code,'l_rec.processed_period_id := p_processed_period_id;');
1373 cn_utils.appindcr(code,'l_rec.rollup_date := p_rollup_date;');
1374 cn_utils.appindcr(code,'l_rec.transaction_amount := p_transaction_amount;');
1375 cn_utils.appindcr(code,'l_rec.quantity := p_quantity;');
1376 cn_utils.appindcr(code,'l_rec.discount_percentage := p_discount_percentage;');
1377 cn_utils.appindcr(code,'l_rec.margin_percentage := p_margin_percentage;');
1378 cn_utils.appindcr(code,'l_rec.orig_currency_code := p_orig_currency_code;');
1379 cn_utils.appindcr(code,'l_rec.transaction_amount_orig := p_transaction_amount_orig;');
1380 cn_utils.appendcr(code);
1381 cn_utils.appindcr(code, 'RETURN classify_rule_1002(l_rec);');
1382 cn_utils.appendcr(code);
1383 cn_utils.unindent(code, 1);
1384 cn_utils.appindcr(code, 'END ' || procedure_name || ';');
1385 cn_utils.appendcr(code);
1386
1387 EXCEPTION
1388 WHEN NO_DATA_FOUND THEN
1389 cn_debug.print_msg('add_classify_spec: in exception handler for NO_DATA_FOUND', 1);
1390 RETURN;
1391 END add_classify_body;
1392 --Cisco API
1393
1394
1395
1396
1397 PROCEDURE rules_recurse_gen (
1398 x_ruleset_id cn_rulesets.ruleset_id%TYPE,
1399 x_org_id cn_rulesets.org_id%TYPE,
1400 X_rule_id cn_rules_hierarchy.rule_id%TYPE,
1401 code IN OUT NOCOPY cn_utils.clob_code_type) IS
1402 first_flag VARCHAR2(1);
1403 X_revenue_class NUMBER;
1404 X_expense_ccid number;
1405 x_liability_ccid number;
1406
1407 procedure_name cn_obj_procedures_v.name%TYPE;
1408 procedure_desc cn_obj_procedures_v.description%TYPE;
1409 parameter_list cn_obj_procedures_v.parameter_list%TYPE;
1410
1411 dummy NUMBER(7);
1412
1413 CURSOR rules IS
1414 SELECT crh.rule_id rule_id,cr.org_id,cr.package_id package_id, cr.ruleset_id ruleset_id
1415 FROM cn_rules_hierarchy crh,cn_rules cr
1416 WHERE parent_rule_id = X_rule_id
1417 AND crh.rule_id = cr.rule_id
1418 AND crh.org_id=cr.org_id
1419 AND crh.org_id=x_org_id
1420 AND cr.ruleset_id = x_ruleset_id
1421 --ORDER BY sequence_number;
1422 ORDER BY crh.rule_id;
1423
1424 BEGIN
1425
1426
1427 procedure_name := 'CLASSIFY_RULE_'||ABS(x_rule_id);
1428 procedure_desc := 'classify transactions using rule '||x_rule_id;
1429
1430 IF g_module_type = 'REVCLS' OR g_module_type = 'ACCGEN' THEN
1431 parameter_list := 'row cn_commission_headers%ROWTYPE';
1432 ELSIF g_module_type = 'PECLS' THEN
1433 parameter_list := 'row cn_proj_compensation_gtt%ROWTYPE';
1434 END IF;
1435
1436 IF (g_module_type = 'REVCLS' OR g_module_type = 'PECLS')
1437 then
1438 proc_init (procedure_name, procedure_desc,parameter_list,
1439 'F', 'NUMBER', code,'PKB');
1440 else
1441
1442 parameter_list := parameter_list || ', expense_ccid out nocopy number, liability_ccid out nocopy number';
1443 proc_init (procedure_name, procedure_desc,parameter_list,
1444 'P', 'NUMBER', code,'PKB');
1445 end if;
1446 cn_utils.appindcr(code, ' revenue_class NUMBER := NULL ;');
1447 cn_utils.appindcr(code, 'BEGIN');
1448 cn_utils.indent(code, 1);
1449 -- cn_utils.appindcr(code, 'cn_message_pkg.debug(''' || procedure_name || '>>'');');
1450 if g_module_type = 'REVCLS' OR g_module_type = 'PECLS'
1451 then
1452 SELECT revenue_class_id
1453 INTO X_revenue_class
1454 FROM cn_rules
1455 WHERE rule_id = X_rule_id
1456 AND ruleset_id = x_ruleset_id
1457 AND org_id=x_org_id;
1458 else
1459 SELECT expense_ccid
1460 INTO X_expense_ccid
1461 FROM cn_rules
1462 WHERE rule_id = X_rule_id
1463 AND org_id=x_org_id
1464 AND ruleset_id = x_ruleset_id;
1465 SELECT liability_ccid
1466 INTO X_liability_ccid
1467 FROM cn_rules
1468 WHERE rule_id = X_rule_id
1469 AND org_id=x_org_id
1470 AND ruleset_id = x_ruleset_id;
1471 end if;
1472 IF (X_revenue_class IS NOT NULL) THEN
1473 cn_utils.appindcr(code, 'revenue_class := ' || X_revenue_class || ';');
1474 END IF;
1475 IF (X_expense_ccid IS NOT NULL) THEN
1476 cn_utils.appindcr(code, 'expense_ccid := ' || X_expense_ccid || ';');
1477 END IF;
1478 IF (X_liability_ccid IS NOT NULL) THEN
1479 cn_utils.appindcr(code, 'liability_ccid := ' || X_liability_ccid || ';');
1480 END IF;
1481 first_flag := 'Y';
1482 FOR r IN rules LOOP
1483 IF (first_flag = 'Y') THEN
1484 first_flag := 'N';
1485 cn_utils.appind(code, 'IF (');
1486 ELSE
1487 cn_utils.unindent(code, 1);
1488 cn_utils.appind(code, 'ELSIF (');
1489 END IF;
1490 rules_recurse_call(r.ruleset_id,r.org_id,r.rule_id, code, r.package_id);
1491 END LOOP;
1492 IF (first_flag = 'N') THEN
1493 cn_utils.unindent(code, 1);
1494 cn_utils.appindcr(code, 'END IF;');
1495 END IF;
1496 -- cn_utils.appindcr(code, 'cn_message_pkg.debug(''' || procedure_name || '<<'');');
1497 cn_utils.appendcr(code);
1498 IF (g_module_type = 'REVCLS' OR g_module_type = 'PECLS')
1499 then
1500 cn_utils.appindcr(code, 'RETURN revenue_class;');
1501 else
1502 cn_utils.appindcr(code, 'RETURN;');
1503 end if;
1504 cn_utils.appendcr(code);
1505 cn_utils.unindent(code, 1);
1506 cn_utils.appindcr(code, 'END ' || procedure_name || ';');
1507 cn_utils.appendcr(code);
1508
1509 EXCEPTION
1510 WHEN NO_DATA_FOUND THEN
1511 cn_debug.print_msg('rules_recurse_gen: in exception handler for NO_DATA_FOUND', 1);
1512 RETURN;
1513 END rules_recurse_gen;
1514
1515 PROCEDURE rules_recurse_assign (
1516 X_ruleset_id cn_rules.ruleset_id%TYPE,
1517 x_org_id cn_rules.org_id %TYPE,
1518 X_rule_id cn_rules_hierarchy.rule_id%TYPE,
1519 code IN OUT NOCOPY cn_utils.clob_code_type,
1520 x_rule_count IN OUT NOCOPY NUMBER,
1521 x_package_count IN OUT NOCOPY NUMBER) IS
1522
1523 first_flag VARCHAR2(1);
1524 X_revenue_class NUMBER;
1525
1526 procedure_name cn_obj_procedures_v.name%TYPE;
1527 procedure_desc cn_obj_procedures_v.description%TYPE;
1528 parameter_list cn_obj_procedures_v.parameter_list%TYPE;
1529
1530 dummy NUMBER(7);
1531
1532 CURSOR rules IS
1533 SELECT rule_id,org_id
1534 FROM cn_rules_hierarchy crh
1535 WHERE parent_rule_id = X_rule_id
1536 AND org_id = x_org_id
1537 AND ruleset_id = X_ruleset_id --RC added condition for multiple classification rulesets
1538 ORDER BY sequence_number;
1539
1540 BEGIN
1541 IF ( x_rule_count=0 ) THEN
1542
1543 package_header ( code,'CN_CLSFN_'||ABS(X_ruleset_id)||'_'||x_package_count,
1544 'CN_CLSFN_'||ABS(X_ruleset_id)||'_'||x_package_count,'PKS',x_org_id);
1545
1546 END IF;
1547
1548 procedure_name := 'CLASSIFY_RULE_'||ABS(x_rule_id);
1549 procedure_desc := 'classify transactions using rule '||x_rule_id;
1550
1551 IF g_module_type = 'REVCLS' OR g_module_type = 'ACCGEN' THEN
1552 parameter_list := 'row cn_commission_headers%ROWTYPE';
1553 ELSIF g_module_type = 'PECLS' THEN
1554 parameter_list := 'row cn_proj_compensation_gtt%ROWTYPE';
1555 END IF;
1556
1557 IF (g_module_type = 'REVCLS' OR g_module_type = 'PECLS')
1558 then
1559 proc_init (procedure_name, procedure_desc,parameter_list,
1560 'F', 'NUMBER', code,'PKS');
1561 else
1562 parameter_list := parameter_list || ', expense_ccid out nocopy number, liability_ccid out nocopy number';
1563 proc_init (procedure_name, procedure_desc,parameter_list,
1564 'P', 'NUMBER', code,'PKS');
1565 end if;
1566
1567 UPDATE cn_rules_all_b
1568 SET package_id = x_package_count
1569 WHERE rule_id = x_rule_id
1570 AND org_id = x_org_id
1571 AND ruleset_id = X_ruleset_id;
1572
1573
1574 x_rule_count := x_rule_count + 1;
1575 -- cn_utils.appindcr(code,'-- count:'||x_rule_count||' max:'||cn_global_var.g_cls_package_size);
1576
1577 IF ( x_rule_count = cn_global_var.g_cls_package_size ) THEN
1578
1579 --Call the Procedure to add Classify Function spec code
1580 IF g_module_type = 'REVCLS' and x_package_count = 1 THEN
1581 add_classify_spec(code);
1582 END IF;
1583
1584 cn_utils.unindent (code,1);
1585 cn_utils.pkg_end_boilerplate(code,'PKS');
1586
1587 x_rule_count :=0;
1588 x_package_count := x_package_count +1;
1589
1590 END IF;
1591
1592 FOR r in rules LOOP
1593
1594 SELECT count(*)
1595 INTO dummy
1596 FROM cn_rules_hierarchy
1597 WHERE parent_rule_id = r.rule_id
1598 AND org_id=r.org_id ;
1599
1600 IF (dummy>0) THEN
1601 rules_recurse_assign(x_ruleset_id,x_org_id,r.rule_id,code,x_rule_count,x_package_count);
1602 END IF;
1603
1604 END LOOP;
1605
1606 EXCEPTION
1607 WHEN NO_DATA_FOUND THEN
1608 cn_debug.print_msg('rules_recurse_assign: in exception handler for NO_DATA_FOUND', 1);
1609 RETURN;
1610 END rules_recurse_assign;
1611
1612
1613 --
1614 -- Public Procedures
1615 --
1616
1617 FUNCTION revenue_classes (
1618 debug_pipe VARCHAR2,
1619 debug_level NUMBER := 1,
1620 x_module_id cn_modules.module_id%TYPE,
1621 x_ruleset_id_in cn_rulesets.ruleset_id%TYPE,
1622 x_org_id_in cn_rulesets.org_id% TYPE) RETURN BOOLEAN IS
1623
1624 -- Declare and initialize procedure variables AE 01-10-96
1625 package_name cn_obj_packages_v.name%TYPE
1626 := 'cn_clsfn' || '_' || ABS(x_ruleset_id_in);
1627 package_type cn_obj_packages_v.package_type%TYPE := 'CLS';
1628
1629 package_spec_id cn_obj_packages_v.package_id%TYPE;
1630 package_body_id cn_obj_packages_v.package_id%TYPE;
1631 package_spec_desc cn_obj_packages_v.description%TYPE;
1632 package_body_desc cn_obj_packages_v.description%TYPE;
1633
1634 x_repository_id cn_repositories.repository_id%TYPE
1635 := cn_utils.get_repository(x_module_id,x_org_id_in);
1636 x_event_id cn_events.event_id%TYPE
1637 := cn_utils.get_event(x_module_id,x_org_id_in);
1638 x_ruleset_id cn_rulesets.ruleset_id%TYPE;
1639 x_org_id cn_rulesets.org_id%TYPE;
1640 x_package_count cn_rules.package_id%TYPE;
1641 x_rule_count NUMBER(7);
1642
1643 package_max NUMBER(7);
1644
1645 -- procedure_id cn_obj_procedures_v.procedure_id%TYPE; --AE 01-15-96
1646
1647 procedure_name cn_obj_procedures_v.name%TYPE;
1648 procedure_desc cn_obj_procedures_v.description%TYPE;
1649 parameter_list cn_obj_procedures_v.parameter_list%TYPE;
1650
1651 spec_code cn_utils.clob_code_type;
1652 body_code cn_utils.clob_code_type;
1653 first_flag VARCHAR2(1);
1654
1655 dummy NUMBER(7);
1656
1657 -- selects the top level rule in a ruleset.
1658 -- Note: this assumes that there is exactly one top-level rule for each
1659 -- ruleset. (This assumption is shared by Tony too)
1660 CURSOR rules IS
1661 SELECT DISTINCT cr.rule_id rule_id,cr.org_id org_id,cr.package_id package_id
1662 FROM cn_rules_hierarchy crh, cn_rules cr
1663 WHERE NOT EXISTS (SELECT rule_id
1664 FROM cn_rules_hierarchy where rule_id = crh.parent_rule_id and org_id=crh.org_id)
1665 AND cr.rule_id = crh.parent_rule_id
1666 AND crh.org_id=cr.org_id
1667 and cr.org_id=x_org_id
1668 AND cr.ruleset_id = X_ruleset_id;
1669
1670 -- Added Where clause by Kumar.S
1671
1672 CURSOR rules_gen IS
1673 SELECT DISTINCT cr.rule_id rule_id,cr.org_id org_id
1674 FROM cn_rules cr
1675 WHERE cr.package_id = x_package_count
1676 and org_id = x_org_id
1677 and ruleset_id = x_ruleset_id;
1678
1679 CURSOR rulesets IS
1680 SELECT LOWER(cocv.name) dest_column, cr.ruleset_id,cr.org_id,module_type
1681 FROM cn_rulesets cr, cn_obj_columns_v cocv
1682 WHERE cr.destination_column_id = cocv.column_id
1683 AND cr.repository_id = x_repository_id
1684 AND cr.org_id = cocv.org_id
1685 AND cr.org_id=x_org_id_in
1686 AND ruleset_id = x_ruleset_id_in;
1687
1688 BEGIN
1689 cn_utils.set_org_id(x_org_id_in);
1690 DBMS_LOB.CREATETEMPORARY(spec_code.text,FALSE,DBMS_LOB.CALL);
1691 DBMS_LOB.CREATETEMPORARY(body_code.text,FALSE,DBMS_LOB.CALL);
1692 g_ruleset_id := X_ruleset_id_in;
1693 IF (debug_pipe IS NOT NULL) THEN
1694 cn_debug.init_pipe(debug_pipe, debug_level);
1695 END IF;
1696 cn_debug.print_msg('revenue_classes>>', 1);
1697
1698 -- AE 01-10-96
1699 file_header(x_module_id, package_name||'_'||x_org_id_in, package_type,
1700 package_spec_id, package_body_id, package_spec_desc,
1701 package_body_desc, spec_code, body_code,x_org_id_in);
1702
1703 x_package_count :=1;
1704
1705 cn_global_var.initialize_instance_info(x_org_id_in);
1706
1707 FOR rs IN rulesets LOOP
1708 g_module_type := rs.module_type;
1709
1710 x_rule_count := 0;
1711 x_ruleset_id := rs.ruleset_id;
1712 x_org_id := rs.org_id;
1713
1714 FOR r IN rules LOOP
1715
1716 SELECT count(*)
1717 INTO dummy
1718 FROM cn_rules_hierarchy
1719 WHERE parent_rule_id = r.rule_id
1720 and org_id=r.org_id;
1721
1722 IF (dummy>0) THEN
1723 rules_recurse_assign(x_ruleset_id,x_org_id,r.rule_id,spec_code,x_rule_count,x_package_count);
1724 END IF;
1725
1726 END LOOP;
1727
1728 IF ( x_rule_count = 0 ) THEN
1729
1730 package_max := x_package_count - 1;
1731
1732 ELSE
1733
1734 package_max := x_package_count;
1735
1736 --Call the Procedure to add Classify Function spec code
1737 IF g_module_type = 'REVCLS' and x_package_count = 1 THEN
1738 add_classify_spec(spec_code);
1739 END IF;
1740
1741 cn_utils.unindent (spec_code,1);
1742 cn_utils.pkg_end_boilerplate(spec_code,'PKS');
1743
1744 END IF;
1745
1746 x_package_count := 1;
1747
1748 WHILE ( x_package_count <= package_max) LOOP
1749
1750 package_header( body_code, 'CN_CLSFN_'||ABS(x_ruleset_id)||'_'||x_package_count,
1751 'CN_CLSFN_'||ABS(x_ruleset_id)||'_'||x_package_count, 'PKB',x_org_id);
1752
1753 FOR rgen IN rules_gen LOOP
1754
1755 SELECT count(*)
1756 INTO dummy
1757 FROM cn_rules_hierarchy
1758 WHERE parent_rule_id = rgen.rule_id and
1759 org_id=rgen.org_id;
1760
1761 IF (dummy>0) THEN
1762 rules_recurse_gen(x_ruleset_id,x_org_id,rgen.rule_id,body_code);
1763 END IF;
1764
1765 END LOOP;
1766
1767 --Call the Procedure to add Classify Function body code
1768 IF g_module_type = 'REVCLS' and x_package_count = 1 THEN
1769 add_classify_body(body_code);
1770 END IF;
1771
1772 cn_utils.unindent (body_code,1);
1773 cn_utils.pkg_end_boilerplate(Body_code,'PKB');
1774 x_package_count := x_package_count + 1;
1775
1776 END LOOP;
1777
1778 END LOOP;
1779 package_header( spec_code,package_name,package_body_desc, 'PKS',x_org_id);
1780 package_header( body_code,package_name,package_body_desc, 'PKB',x_org_id);
1781 -- procedure_id := cn_utils.get_object_id; -- AE 01-15-96
1782 procedure_name := 'is_descendant_of';
1783 procedure_desc := 'This function returns TRUE if value is a descendant of value according to the dimension hierarchy hierarchy_id';
1784 -- parameter_list := 'X_column_value NUMBER, X_rollup_value NUMBER, X_hierarchy_id NUMBER, X_period NUMBER';
1785
1786 -- 30-NOV-99 SK Changes made to replace periods with Start and End dates.
1787 -- Kumar Fixed parameter_list := 'X_column_value NUMBER, X_rollup_value NUMBER, X_hierarchy_id NUMBER, X_processed_date DATE';
1788 parameter_list := 'X_column_value VARCHAR2, X_rollup_value VARCHAR2, X_hierarchy_id NUMBER, X_processed_date DATE';
1789
1790
1791 -- AE 01-15-96
1792 cn_utils.proc_init(procedure_name, procedure_desc, parameter_list,
1793 'F', 'BOOLEAN', package_spec_id, x_repository_id,
1794 spec_code, body_code);
1795
1796 cn_utils.appindcr(body_code, ' dummy NUMBER;');
1797 cn_utils.appindcr(body_code, ' x_name1 VARCHAR2(30);'); --AE 08-98-95
1798 cn_utils.appindcr(body_code, ' x_name2 VARCHAR2(30);'); --AE
1799 cn_utils.appendcr(body_code);
1800
1801 -- replaced by inline code for Peter's debug messages. 11-02-95
1802 -- cn_utils.proc_begin(procedure_name, 'N', body_code);
1803 cn_utils.appindcr(body_code, 'BEGIN');
1804 cn_utils.indent(body_code, 1);
1805 -- cn_utils.appindcr(body_code, 'cn_message_pkg.debug(''' || procedure_name || '>>'');');
1806 -- end of inline code 11-02-95
1807
1808
1809 -- 30-NOV-99 SK Changes made to replace periods with Start and End dates.
1810
1811 cn_utils.appindcr(body_code, 'SELECT ancestor_external_id');
1812 cn_utils.appindcr(body_code, ' INTO dummy');
1813 cn_utils.appindcr(body_code, ' FROM cn_dim_explosion');
1814 cn_utils.appindcr(body_code, ' WHERE value_external_id = X_column_value');
1815 cn_utils.appindcr(body_code, ' AND ancestor_id = X_rollup_value');
1816 cn_utils.appindcr(body_code, ' AND dim_hierarchy_id = (');
1817 cn_utils.appindcr(body_code, ' SELECT cdh.dim_hierarchy_id');
1818 cn_utils.appindcr(body_code, ' FROM cn_dim_hierarchies cdh');
1819 cn_utils.appindcr(body_code, ' WHERE cdh.header_dim_hierarchy_id = X_hierarchy_id');
1820 cn_utils.appindcr(body_code, ' AND X_processed_date BETWEEN cdh.start_date and cdh.end_date);');
1821 cn_utils.appindcr(body_code, 'RETURN TRUE;');
1822 cn_utils.appendcr(body_code);
1823 cn_utils.appindcr(body_code, 'EXCEPTION');
1824 cn_utils.appindcr(body_code, ' WHEN NO_DATA_FOUND THEN');
1825 cn_utils.appindcr(body_code, ' RETURN FALSE;');
1826
1827 -- replaced by inline code for Peter's debug messages. 11-02-95
1828 -- cn_utils.proc_end(procedure_name, 'N', body_code);
1829 cn_utils.appendcr(body_code);
1830 -- cn_utils.appindcr(body_code, 'cn_message_pkg.debug(''' || procedure_name || '<<'');');
1831 cn_utils.appendcr(body_code);
1832 cn_utils.unindent(body_code, 1);
1833 cn_utils.appindcr(body_code, 'END ' || procedure_name || ';');
1834 cn_utils.appendcr(body_code);
1835 -- end of inline code 11-02-95
1836 FOR rs IN rulesets LOOP
1837
1838 X_ruleset_id := rs.ruleset_id;
1839
1840
1841 --AE procedure_id := cn_utils.get_object_id; --AE 01-15-96
1842 procedure_name := 'classify_' || ABS(rs.ruleset_id);
1843 procedure_desc := 'This function classifies transactions into a revenue class according to the rules in the ruleset ' || rs.ruleset_id;
1844 --RC changing the parameters for 11i for as per the requirements
1845 --for the calculation process
1846 --parameter_list := 'row cn_commission_lines%ROWTYPE';
1847 parameter_list := 'p_commission_header_id NUMBER';
1848
1849 IF g_module_type = 'REVCLS' OR g_module_type = 'ACCGEN' THEN
1850 parameter_list := 'p_commission_header_id NUMBER';
1851 ELSIF g_module_type = 'PECLS' THEN
1852 parameter_list := 'p_line_id NUMBER';
1853 END IF;
1854
1855 --AE 01-15-96
1856 IF (g_module_type = 'REVCLS' OR g_module_type = 'PECLS')
1857 then
1858 cn_utils.proc_init(procedure_name, procedure_desc, parameter_list,
1859 'F', 'NUMBER', package_spec_id, x_repository_id,
1860 spec_code, body_code);
1861 else
1862
1863 parameter_list := parameter_list || ', expense_ccid out nocopy number, liability_ccid out nocopy number';
1864 cn_utils.proc_init(procedure_name, procedure_desc, parameter_list,
1865 'P', 'NUMBER', package_spec_id, x_repository_id,
1866 spec_code, body_code);
1867 end if;
1868
1869
1870 cn_utils.appindcr(body_code, ' revenue_class NUMBER := NULL ;');
1871 --RC Adding the following code in 11i for calculation
1872 --RK Added the 'PECLS' condition for projected commission code enhancement
1873
1874 IF g_module_type = 'REVCLS' OR g_module_type = 'ACCGEN' THEN
1875 cn_utils.appindcr(body_code, ' row cn_commission_headers%ROWTYPE ;');
1876 ELSIF g_module_type = 'PECLS' THEN
1877 cn_utils.appindcr(body_code, ' row cn_proj_compensation_gtt%ROWTYPE ;');
1878 END IF;
1879
1880 -- replaced by inline code for Peter's debug messages. 11-02-95
1881 -- cn_utils.proc_begin(procedure_name, 'N', body_code);
1882 cn_utils.appindcr(body_code, 'BEGIN');
1883 cn_utils.indent(body_code, 1);
1884 -- cn_utils.appindcr(body_code, 'cn_message_pkg.debug(''' || procedure_name || '>>'');');
1885 -- end of inline code 11-02-95
1886
1887
1888 --RC Adding code for 11i changes as needed by calculation
1889 --RK Added the 'PECLS' condition for projected commission code enhancement
1890
1891 IF g_module_type = 'REVCLS' OR g_module_type = 'ACCGEN' THEN
1892 cn_utils.appindcr(body_code, 'SELECT * INTO row FROM cn_commission_headers WHERE commission_header_id = p_commission_header_id;');
1893 ELSIF g_module_type = 'PECLS' THEN
1894 cn_utils.appindcr(body_code, 'SELECT * INTO row FROM cn_proj_compensation_gtt WHERE line_id = p_line_id;');
1895 END IF;
1896
1897
1898 -- for each rule at the top level of the hierarchy do
1899 -- generate the if(elsif) rule for that rule then .. endif;
1900 first_flag := 'Y';
1901
1902 FOR r IN rules LOOP
1903 IF (first_flag = 'Y') THEN
1904 first_flag := 'N';
1905 cn_utils.appind(body_code, 'IF (');
1906 ELSE
1907 cn_utils.unindent(body_code, 1);
1908 cn_utils.appind(body_code, 'ELSIF (');
1909 END IF;
1910 rules_recurse_call(X_ruleset_id,x_org_id,r.rule_id, body_code,r.package_id);
1911 END LOOP;
1912
1913 IF (first_flag = 'N') THEN
1914 cn_utils.unindent(body_code, 1);
1915 cn_utils.appindcr(body_code, 'END IF;');
1916 END IF;
1917 -- cn_utils.appindcr(body_code, 'cn_message_pkg.debug(''' || procedure_name || '<<'');');
1918 cn_utils.appendcr(body_code);
1919 IF (g_module_type = 'REVCLS' OR g_module_type = 'PECLS') THEN
1920 cn_utils.appindcr(body_code, 'RETURN revenue_class;');
1921 else
1922 cn_utils.appindcr(body_code, 'RETURN;');
1923 end if;
1924
1925 -- replaced by inline code for Peter's debug messages. 11-02-95
1926 -- cn_utils.proc_end(procedure_name, 'N', body_code);
1927 cn_utils.appendcr(body_code);
1928 cn_utils.unindent(body_code, 1);
1929 cn_utils.appindcr(body_code, 'END ' || procedure_name || ';');
1930 cn_utils.appendcr(body_code);
1931 -- end of inline code 11-02-95
1932
1933 END LOOP;
1934
1935
1936 --- generate classify_batch routine.
1937 -- procedure_id := cn_utils.get_object_id; --AE 01-15-96
1938
1939 -- AE 01-10-96
1940 cn_utils.pkg_end(package_name, package_spec_id, package_body_id,
1941 spec_code, body_code);
1942
1943 cn_debug.print_msg('revenue_classes<<', 1);
1944
1945 DBMS_LOB.FREETEMPORARY(spec_code.text);
1946 DBMS_LOB.FREETEMPORARY(body_code.text);
1947 cn_utils.unset_org_id;
1948 RETURN TRUE;
1949
1950 EXCEPTION
1951 WHEN NO_DATA_FOUND THEN
1952 DBMS_LOB.FREETEMPORARY(spec_code.text);
1953 DBMS_LOB.FREETEMPORARY(body_code.text);
1954 cn_debug.print_msg('revenue_classes: in exception handler for NO_DATA_FOUND', 1);
1955 RETURN FALSE;
1956 END revenue_classes;
1957
1958
1959 PROCEDURE Classification_Install(
1960 x_errbuf OUT NOCOPY VARCHAR2,
1961 x_retcode OUT NOCOPY NUMBER,
1962 x_ruleset_id IN NUMBER,
1963 x_org_id IN NUMBER) IS
1964
1965 l_applsys_schema VARCHAR2(20) ;
1966 l_pks_start NUMBER;
1967 l_pks_end NUMBER;
1968 l_pkb_start NUMBER;
1969 l_pkb_end NUMBER;
1970 l_count NUMBER;
1971 l_comp_error VARCHAR2(10);
1972 l_errors BOOLEAN := FALSE;
1973 l_max_len NUMBER := 1800;
1974 l_remainder NUMBER;
1975 l_pks_object_id NUMBER;
1976 l_pkb_object_id NUMBER;
1977 l_pkg_name VARCHAR2(3000);
1978 k NUMBER;
1979 l_min_line_no NUMBER;
1980
1981 -- rchenna, bug 3960877
1982 l_text1 VARCHAR2(300);
1983 l_next_occurance_count NUMBER;
1984 l_paired_quotes BOOLEAN;
1985
1986 CURSOR pkg_spec_start (p_object_id NUMBER) IS
1987 SELECT cs.line_no
1988 FROM cn_source cs
1989 WHERE cs.object_id = p_object_id
1990 AND substr(cs.text, 1, 25) = 'CREATE OR REPLACE PACKAGE'
1991 AND instr(cs.text, '--START-OF-PKS') <> 0
1992 ORDER BY line_no;
1993
1994 CURSOR pkg_spec_end (p_object_id NUMBER) IS
1995 SELECT cs.line_no
1996 FROM cn_source cs
1997 WHERE cs.object_id = p_object_id
1998 AND cs.text like 'END%'
1999 ORDER BY line_no;
2000
2001 CURSOR pkg_body_start (p_object_id NUMBER) IS
2002 SELECT cs.line_no
2003 FROM cn_source cs
2004 WHERE cs.object_id = p_object_id
2005 AND substr(cs.text, 1, 30) = 'CREATE OR REPLACE PACKAGE BODY'
2006 AND instr(cs.text, '--START-OF-PKB') <> 0
2007 ORDER BY line_no;
2008
2009 CURSOR pkg_body_end (p_object_id NUMBER) IS
2010 SELECT cs.line_no
2011 FROM cn_source cs
2012 WHERE cs.object_id = p_object_id
2013 AND cs.text like 'END%'
2014 ORDER BY line_no;
2015
2016 CURSOR fetch_code (p_pks_start NUMBER,
2017 p_pks_end NUMBER,
2018 p_pks_object_id NUMBER) IS
2019 SELECT cs.text
2020 FROM cn_source cs
2021 WHERE cs.object_id = p_pks_object_id
2022 AND cs.line_no between p_pks_start and (p_pks_end - 1)
2023 ORDER BY cs.line_no;
2024
2025 CURSOR get_ruleset_data ( p_ruleset_id in NUMBER,p_org_id in NUMBER ) IS
2026 SELECT *
2027 FROM cn_rulesets
2028 WHERE ruleset_id = p_ruleset_id and
2029 org_id=p_org_id;
2030
2031 l_get_ruleset_data_rec get_ruleset_data%ROWTYPE;
2032
2033 l_ruleset_id NUMBER;
2034 l_org_id NUMBER;
2035 l_text VARCHAR2(32000);
2036 l_pos NUMBER;
2037
2038 BEGIN
2039 cn_utils.set_org_id(x_org_id);
2040 SELECT co.object_id
2041 INTO l_pks_object_id
2042 FROM cn_objects co
2043 WHERE co.name = 'cn_clsfn_'||x_ruleset_id||'_'||x_org_id
2044 AND co.org_id=x_org_id
2045 AND co.object_type = 'PKS';
2046
2047 SELECT co.object_id
2048 INTO l_pkb_object_id
2049 FROM cn_objects co
2050 WHERE co.name = 'cn_clsfn_'||x_ruleset_id||'_'||x_org_id
2051 AND co.org_id=x_org_id
2052 AND co.object_type = 'PKB';
2053
2054 FOR i in pkg_spec_start(l_pks_object_id)
2055 LOOP
2056 l_pks_start := i.line_no;
2057 SELECT substr(text, instr(text, 'cn_clsfn'),
2058 instr(substr(text, instr(text, 'cn_clsfn'), length(text)),
2059 ' AS --START-OF'))
2060 INTO l_pkg_name
2061 FROM cn_source
2062 WHERE line_no = l_pks_start
2063 AND object_id = l_pks_object_id;
2064
2065 l_pkg_name := RTRIM(UPPER(l_pkg_name));
2066
2067 IF NOT pkg_spec_end%ISOPEN
2068 THEN
2069 OPEN pkg_spec_end(l_pks_object_id);
2070 END IF;
2071 FETCH pkg_spec_end INTO l_pks_end;
2072
2073 k := 1;
2074 --Create package spec
2075 FOR j IN fetch_code(l_pks_start, l_pks_end - 1, l_pks_object_id)
2076 LOOP
2077 ad_ddl.build_package(j.text, k);
2078 k := k + 1;
2079 END LOOP;
2080 ad_ddl.build_package('END;', k);
2081 -----+
2082 -- Added Select..Into.. From. Statement
2083 --pramadas/24-Dec-2003/Bug Fix : 3322008
2084 -----+
2085
2086 SELECT user
2087 INTO l_applsys_schema
2088 FROM dual;
2089
2090 ad_ddl.create_plsql_object(
2091 applsys_schema => l_applsys_schema,
2092 application_short_name => 'CN',
2093 object_name => l_pkg_name,
2094 lb => 1,
2095 ub => k,
2096 insert_newlines => 'FALSE',
2097 comp_error => l_comp_error);
2098
2099 IF l_comp_error = 'TRUE' THEN
2100 l_errors := TRUE;
2101 END IF;
2102 END LOOP;
2103
2104 FOR i in pkg_body_start(l_pkb_object_id)
2105 LOOP
2106 l_pkb_start := i.line_no;
2107 SELECT substr(text, instr(text, 'cn_clsfn_'),
2108 instr(substr(text, instr(text, 'cn_clsfn_'), length(text)),
2109 ' AS --START-OF'))
2110 INTO l_pkg_name
2111 FROM cn_source
2112 WHERE line_no = l_pkb_start
2113 AND object_id = l_pkb_object_id;
2114
2115 l_pkg_name := RTRIM(UPPER(l_pkg_name));
2116
2117 IF NOT pkg_body_end%ISOPEN
2118 THEN
2119 OPEN pkg_body_end(l_pkb_object_id);
2120 END IF;
2121 FETCH pkg_body_end INTO l_pkb_end;
2122
2123 k := 1;
2124 --Create package body
2125 FOR j IN fetch_code(l_pkb_start, l_pkb_end - 1, l_pkb_object_id)
2126 LOOP
2127
2128 -- Modified By Kumar.S
2129 -- Dated on 09/29/00
2130 -- Bug No 1406969
2131 -- Truncation Error pl/sql numeric value error
2132 -- our program looks OK, only the do ad_ddl build package
2133 -- using an variable which has a limitation of 255 Characters
2134 --
2135
2136 IF length(j.text ) <= 254 then
2137 ad_ddl.build_package(j.text, k);
2138 ELSE
2139
2140 l_text := j.text;
2141 LOOP
2142 -- rchenna, bug 3960877, initialize local variables
2143 l_next_occurance_count := 1;
2144 l_paired_quotes := FALSE;
2145
2146 -- rchenna, bug 3960877, keep finding the next occurance
2147 -- of close parathesis until single quotes in the substr
2148 -- match up
2149 WHILE NOT l_paired_quotes LOOP
2150 -- rchenna, bug 3960877
2151 l_pos := instr(l_text, ')', 1, l_next_occurance_count );
2152
2153 -- rchenna, bug 3960877
2154 -- check whether the substr(l_text,1,l_pos) has paried
2155 -- single quotes
2156
2157 l_text1 := substr(l_text,1,l_pos);
2158 l_paired_quotes := TRUE;
2159
2160 IF length(l_text1) > 0 THEN
2161
2162 check_text_paired_quotes(substr(l_text,1,l_pos), l_paired_quotes);
2163
2164 END IF;
2165
2166 l_next_occurance_count := l_next_occurance_count + 1;
2167
2168 END LOOP;
2169
2170
2171 ad_ddl.build_package(substr(l_text,1,l_pos), k);
2172 l_text := substr(l_text, l_pos +1 );
2173
2174 IF nvl(l_pos,0) = 0 then
2175 ad_ddl.build_package(substr(l_text,l_pos), k);
2176 EXIT;
2177 END IF;
2178 k := k +1;
2179
2180 END LOOP;
2181
2182 END IF;
2183 k := k + 1;
2184 END LOOP;
2185 ad_ddl.build_package('END;', k);
2186 ad_ddl.create_plsql_object(
2187 applsys_schema => l_applsys_schema,
2188 application_short_name => 'CN',
2189 object_name => l_pkg_name,
2190 lb => 1,
2191 ub => k,
2192 insert_newlines => 'TRUE',
2193 comp_error => l_comp_error);
2194
2195 IF l_comp_error = 'TRUE' THEN
2196 l_errors := TRUE;
2197 END IF;
2198
2199 END LOOP;
2200
2201 ------------------------------------------------------------------------------
2202 -- The rest of the procedure is concerned with providing log messages if the
2203 -- creation of any of the packages failed
2204 ------------------------------------------------------------------------------
2205 IF l_errors THEN -- some specs/bodies were in error
2206 x_retcode := 1; -- set failure return code
2207
2208 -- Search the User_Errors table, for errors belonging to any of the Classification
2209 -- packages for this Org.
2210 --
2211 FOR rec IN
2212 (SELECT
2213 '*** '||type||' '||LOWER(name)||' LINE: '||line||'/'||position||fnd_global.local_CHR(10)||text||fnd_global.local_CHR(10) outstr
2214 FROM user_errors WHERE name = l_pkg_name)
2215 LOOP
2216 -- If there is enough space, append this error to the end of the
2217 -- Errbuf, otherwise aappend as mauch as possible and then quit
2218 -- the loop.
2219 IF LENGTHB(x_errbuf) + LENGTHB(rec.outstr) <= l_max_len THEN
2220 x_errbuf := x_errbuf || rec.outstr;
2221 ELSE
2222 l_remainder := l_max_len - LENGTHB(x_errbuf);
2223 x_errbuf := x_errbuf || SUBSTRB(rec.outstr,1,l_remainder);
2224 EXIT;
2225 END IF;
2226 END LOOP;
2227 END IF;
2228
2229 --
2230 -- Added change status to 'GENERATED' if the install is completed successfully
2231 -- Kumar Sivasankaran Date: 11/07/2001
2232 --
2233 OPEN get_ruleset_data (x_ruleset_id,x_org_id);
2234 FETCH get_ruleset_data INTO l_get_ruleset_data_rec;
2235 CLOSE get_ruleset_data;
2236
2237 if l_get_ruleset_data_rec.ruleset_id is null THEN
2238 l_ruleset_id := -x_ruleset_id;
2239 l_org_id :=x_org_id;
2240 OPEN get_ruleset_data ( l_ruleset_id,l_org_id);
2241 FETCH get_ruleset_data INTO l_get_ruleset_data_rec;
2242 CLOSE get_ruleset_data;
2243 end if;
2244
2245 if l_errors THEN
2246 l_get_ruleset_data_rec.ruleset_status := 'INSTFAIL';
2247 else
2248 l_get_ruleset_data_rec.ruleset_status := 'GENERATED';
2249 end if;
2250
2251 cn_syin_rulesets_pkg.update_row(l_get_ruleset_data_rec.ruleset_id,
2252 l_get_ruleset_data_rec.object_version_number,
2253 l_get_ruleset_data_rec.ruleset_status,
2254 l_get_ruleset_data_rec.destination_column_id,
2255 l_get_ruleset_data_rec.repository_id,
2256 l_get_ruleset_data_rec.start_date,
2257 l_get_ruleset_data_rec.end_date,
2258 l_get_ruleset_data_rec.name,
2259 l_get_ruleset_data_rec.module_type,
2260 null,
2261 null,
2262 null,
2263 l_get_ruleset_data_rec.org_id);
2264
2265
2266 END Classification_Install;
2267
2268
2269 -- Called by the methods to get the current org_id and the org_id string rep
2270 PROCEDURE get_cached_org_info (x_cached_org_id OUT NOCOPY integer, x_cached_org_append OUT NOCOPY VARCHAR2) IS
2271 BEGIN
2272 SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
2273 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) INTO x_cached_org_id
2274 FROM DUAL;
2275
2276 if x_cached_org_id = -99 then
2277 x_cached_org_append := '_MINUS99';
2278 else
2279 x_cached_org_append := '_' || x_cached_org_id;
2280 end if;
2281 END get_cached_org_info;
2282
2283 -- clku
2284 -- procedure to check if a string has paired single quotes
2285 PROCEDURE check_text_paired_quotes (l_in_text IN VARCHAR2,
2286 l_out_paired_quotes OUT NOCOPY BOOLEAN )
2287 IS
2288 -- clku
2289 l_char_count NUMBER;
2290 a NUMBER;
2291 l_next_occurance_count NUMBER;
2292
2293
2294 BEGIN
2295
2296 l_char_count := 0;
2297 a := 1;
2298
2299
2300
2301 IF length(l_in_text) > 0 THEN
2302 FOR a IN 1..(length(l_in_text) - 1)LOOP
2303
2304
2305
2306 IF substr(l_in_text,a,1) = '''' THEN
2307
2308
2309
2310 l_char_count := l_char_count + 1;
2311
2312 END IF;
2313
2314 END LOOP;
2315 END IF;
2316
2317 IF mod(l_char_count,2) = 1 THEN
2318 l_out_paired_quotes := FALSE;
2319 ELSE
2320 l_out_paired_quotes := TRUE;
2321 END IF;
2322
2323 cn_utils.unset_org_id;
2324 EXCEPTION
2325 WHEN NO_DATA_FOUND THEN
2326 return;
2327
2328 END check_text_paired_quotes;
2329 END cn_classification_gen;