[Home] [Help]
PACKAGE BODY: APPS.CNSYIN_ATTR_RULES_PKG
Source
1 PACKAGE BODY CNSYIN_Attr_Rules_PKG AS
2 -- $Header: cnsyinhb.pls 115.10 2002/02/05 00:25:31 pkm ship $
3
4
5 --
6 -- Procedure Name
7 -- populate_fields
8 -- Purpose
9 --
10 -- History
11 -- 01/26/94 Tony Lower Created
12 -- 07-18-95 Amy Erickson Updated
13 --
14 -- SEP-19 Kumar Sivasankaran Added Exceptions
15
16 PROCEDURE Populate_Fields (x_column_id number,
17 x_dimension_id IN OUT number,
18 x_column_name IN OUT varchar2,
19 x_user_column_name IN OUT varchar2,
20 x_dim_hier_id number,
21 x_hier_name IN OUT varchar2,
22 x_value_id varchar2,
23 x_hier_value IN OUT varchar2) IS
24 BEGIN
25
26 IF x_column_id IS NOT NULL THEN
27
28 BEGIN
29
30 SELECT name, user_name, dimension_id
31 INTO x_column_name, x_user_column_name, x_dimension_id
32 FROM cn_objects
33 WHERE object_id = x_column_id;
34
35 EXCEPTION
36
37 when no_data_found then
38
39 x_column_name := null;
40 x_user_column_name := null;
41 END;
42
43 END IF;
44
45 IF x_dim_hier_id IS NOT NULL THEN
46
47 BEGIN
48
49 SELECT name
50 INTO x_hier_name
51 FROM cn_head_hierarchies
52 WHERE head_hierarchy_id = x_dim_hier_id;
53
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 x_hier_name := NULL;
57 END;
58
59 --END IF;
60
61 IF x_value_id IS NOT NULL THEN
62
63 SELECT max(hn.name) INTO x_hier_value
64 FROM cn_hierarchy_nodes hn,
65 cn_dim_hierarchies dh
66 WHERE hn.value_id = to_number(x_value_id)
67 AND hn.dim_hierarchy_id = dh.dim_hierarchy_id
68 AND dh.header_dim_hierarchy_id = x_dim_hier_id;
69
70 ELSE x_hier_value := NULL;
71
72 END IF;
73
74 ELSE x_hier_name := NULL;
75
76 END IF;
77
78
79 END Populate_Fields;
80
81 --
82 -- Procedure Name
83 -- default_row
84 -- Purpose
85 --
86 -- History
87 -- 01/26/94 Tony Lower Created
88 --
89 PROCEDURE Default_Row (X_rule_id IN OUT number) IS
90 BEGIN
91
92 IF X_rule_id IS NULL THEN
93 SELECT cn_objects_s.nextval
94 INTO X_rule_id
95 FROM sys.dual;
96 END IF;
97
98 END Default_Row;
99
100 --
101 -- Procedure Name
102 -- Insert_Row
103 -- Purpose
104 --
105 -- History
106 -- 26-AUG-98 Ram Kalyanasundaram Created
107 --
108 PROCEDURE Insert_Row(p_attribute_rule_id NUMBER,
109 p_column_id NUMBER,
110 p_column_value VARCHAR2,
111 p_low_value VARCHAR2,
112 p_high_value VARCHAR2,
113 p_dimension_hierarchy_id NUMBER,
114 p_not_flag VARCHAR2,
115 p_rule_id NUMBER,
116 p_ruleset_id NUMBER,
117 p_last_update_date DATE,
118 p_last_updated_by NUMBER,
119 p_creation_date DATE,
120 p_created_by NUMBER,
121 p_last_update_login NUMBER
122 )
123 IS
124 BEGIN
125 INSERT INTO cn_attribute_rules(object_version_number,
126 attribute_rule_id, column_id,
127 column_value, low_value, high_value,
128 dimension_hierarchy_id, not_flag,
129 rule_id, ruleset_id, last_update_date, last_updated_by,
130 creation_date,created_by,
131 last_update_login)
132 VALUES(1,
133 p_attribute_rule_id, p_column_id,
134 p_column_value, p_low_value, p_high_value,
135 p_dimension_hierarchy_id, p_not_flag,
136 p_rule_id,p_ruleset_id, p_last_update_date, p_last_updated_by,
137 p_creation_date, p_created_by,
138 p_last_update_login);
139 END Insert_Row;
140
141 --
142 -- Procedure Name
143 -- Update_Row
144 -- Purpose
145 --
146 -- History
147 -- 26-AUG-98 Ram Kalyanasundaram Created
148 --
149 PROCEDURE Update_Row(p_attribute_rule_id NUMBER,
150 p_object_version_number number, --added rckalyan
151 p_column_id NUMBER,
152 p_column_value VARCHAR2,
153 p_low_value VARCHAR2,
154 p_high_value VARCHAR2,
155 p_dimension_hierarchy_id NUMBER,
156 p_not_flag VARCHAR2,
157 p_last_update_date DATE,
158 p_last_updated_by NUMBER,
159 p_last_update_login NUMBER
160 )
161 IS
162 BEGIN
163 UPDATE cn_attribute_rules
164 SET column_id = p_column_id,
165 object_version_number = p_object_version_number + 1,
166 column_value = p_column_value,
167 low_value = p_low_value,
168 high_value = p_high_value,
169 dimension_hierarchy_id = p_dimension_hierarchy_id,
173 last_update_login = p_last_update_login
170 not_flag = p_not_flag,
171 last_update_date = p_last_update_date,
172 last_updated_by = p_last_updated_by,
174 WHERE attribute_rule_id = p_attribute_rule_id;
175 END Update_Row;
176
177 --
178 -- Procedure Name
179 -- Delete_Row
180 -- Purpose
181 --
182 -- History
183 -- 26-AUG-98 Ram Kalyanasundaram Created
184 --
185 PROCEDURE delete_row(p_attribute_rule_id NUMBER )
186 IS
187 BEGIN
188
189 DELETE FROM cn_attribute_rules
190 WHERE attribute_rule_id = p_attribute_rule_id;
191
192 END Delete_Row;
193
194 END CNSYIN_Attr_Rules_PKG;