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