[Home] [Help]
PACKAGE BODY: APPS.CN_DIHY_TWO_API_PKG
Source
1 PACKAGE BODY CN_DIHY_TWO_API_PKG AS
2 -- $Header: cndihy2b.pls 120.3 2005/12/13 01:52:38 hanaraya ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_DIHY_TWO_API_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cndihy2b.pls';
6
7
8 PROCEDURE Insert_Edge ( X_name IN VARCHAR2,
9 X_dim_hierarchy_id IN NUMBER,
10 X_value_id IN OUT NOCOPY NUMBER,
11 X_parent_value_id IN NUMBER,
12 X_external_id IN NUMBER,
13 X_hierarchy_api_id IN NUMBER,
14 --R12 MOAC Changes--Start
15 X_org_id IN NUMBER) IS
16 --R12 MOAC Changes--End
17 Dummy NUMBER(15);
18
19 BEGIN
20
21 IF (X_value_id IS NULL) OR (X_value_id = -1) THEN
22
23 SELECT count(*)
24 INTO Dummy
25 FROM cn_hierarchy_nodes
26 WHERE external_id = X_external_id
27 AND dim_hierarchy_id = X_dim_hierarchy_id;
28
29 IF (Dummy = 0) THEN
30
31 SELECT cn_hierarchy_nodes_s.nextval INTO X_value_id
32 FROM dual;
33
34 INSERT INTO cn_hierarchy_nodes
35 (value_id, name, dim_hierarchy_id, external_id,
36 CREATED_BY, CREATION_DATE,
37 LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,ORG_ID)
38 VALUES
39 (X_value_id, X_name, X_dim_hierarchy_id, X_external_id,
40 fnd_global.user_id, sysdate,
41 fnd_global.user_id, sysdate, fnd_global.login_id,X_org_id);
42
43 ELSE
44
45 SELECT value_id
46 INTO X_value_id
47 FROM cn_hierarchy_nodes
48 WHERE dim_hierarchy_id = X_dim_hierarchy_id
49 AND external_id = X_external_id;
50
51 UPDATE cn_hierarchy_nodes
52 SET name = x_name
53 WHERE value_id = x_value_id;
54
55 END IF;
56
57 END IF;
58
59 INSERT INTO cn_hierarchy_edges
60 (dim_hierarchy_id,
61 value_id,
62 parent_value_id,
63 hierarchy_api_id,
64 CREATED_BY,
65 CREATION_DATE,
66 LAST_UPDATE_LOGIN,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,ORG_ID)
69 VALUES
70 (X_dim_hierarchy_id,
71 X_value_id,
72 X_parent_value_id,
73 x_hierarchy_api_id,
74 fnd_global.user_id,
75 sysdate,
76 fnd_global.login_id,
77 sysdate,
78 fnd_global.user_id,X_org_id);
79
80 IF (X_parent_value_id IS NOT NULL) THEN
81
82 DELETE cn_hierarchy_edges
83 WHERE dim_hierarchy_id = X_dim_hierarchy_id
84 AND value_id = X_value_id
85 AND parent_value_id IS NULL
86 --R12 MOAC Changes--Start
87 AND org_id = X_org_id;
88 --R12 MOAC Changes--End
89
90 ELSE
91
92 DELETE cn_hierarchy_edges
93 WHERE dim_hierarchy_id = X_dim_hierarchy_id
94 AND value_id = X_value_id
95 AND parent_value_id IS NOT NULL
96 --R12 MOAC Changes--Start
97 AND org_id = X_org_id;
98 --R12 MOAC Changes--End
99
100 END IF;
101
102 END Insert_Edge;
103
104
105
106
107 PROCEDURE Insert_Dimension (X_dimension_id NUMBER,
108 X_name VARCHAR2,
109 X_base_table_id NUMBER,
110 X_primary_key_id NUMBER,
111 X_user_column_name_id NUMBER,
112 --R12 MOAC Changes--Start
113 X_org_id NUMBER) IS
114 --R12 MOAC Changes--End
115 CountVal NUMBER(15);
116 BEGIN
117
118
119 UPDATE cn_objects SET user_column_name = 'Y',
120 dimension_id = X_dimension_id,
121 primary_key = 'N',
122 last_updated_by = fnd_global.user_id,
123 last_update_date = sysdate,
124 last_update_login = fnd_global.login_id
125 WHERE object_id = X_user_column_name_id
126 --R12 MOAC Changes--Start
127 and org_id = X_org_id;
128 --R12 MOAC Changes--End
129
130 UPDATE cn_objects SET primary_key = 'Y',
131 dimension_id = X_dimension_id,
132 last_updated_by = fnd_global.user_id,
133 last_update_date = sysdate,
134 last_update_login = fnd_global.login_id
135 WHERE object_id = X_primary_key_id
136 --R12 MOAC Changes--Start
137 and org_id = X_org_id;
138 --R12 MOAC Changes--End
139
140
141
142 END Insert_Dimension;
143
144 --+
145 -- Procedure Name
146 -- Cascade_Delete
147 -- History
148 -- 8/02/95 Tony Lower Created
149 --+
150 PROCEDURE Cascade_Delete(X_value_id number,
151 X_parent_value_id number,
152 X_dim_hierarchy_id number,
153 --R12 MOAC Changes--Start
154 X_org_id NUMBER) IS
155 --R12 MOAC Changes--End
156
157 Cursor Children IS SELECT *
158 FROM cn_hierarchy_edges
159 WHERE parent_value_id = X_value_id
160 AND dim_hierarchy_id = X_dim_hierarchy_id;
161
162 X_refcount number(15);
163
164 BEGIN
165
166 --+
167 -- This deletes a node from the graph, and also deletes all nodes
168 -- beneath it (if they are not part of the graph by way of some other
169 -- parent).
170 --+
171
172 IF X_parent_value_id IS NULL THEN
173
174 DELETE cn_hierarchy_edges WHERE value_id = X_value_id
175 AND parent_value_id IS NULL
176 AND dim_hierarchy_id = X_dim_hierarchy_id;
177
178 ELSE
179
180 DELETE cn_hierarchy_edges WHERE value_id = X_value_id
181 AND parent_value_id = X_parent_value_id
182 AND dim_hierarchy_id = X_dim_hierarchy_id;
183
184 END IF;
185
186 SELECT ref_count INTO X_refcount FROM cn_hierarchy_nodes
187 WHERE value_id = X_value_id
188 AND dim_hierarchy_id = X_dim_hierarchy_id;
189
190 IF X_refcount = 0 THEN
191
192 FOR c IN Children LOOP
193
194 Cascade_Delete(c.value_id, X_value_id,
195 X_dim_hierarchy_id,
196 --R12 MOAC Changes--Start
197 X_org_id);
198 --R12 MOAC Changes--End
199
200
201 END LOOP;
202
203 END IF;
204
205 /* delete cn_hierarchy_nodes WHERE value_id = X_value_id
206 AND dim_hierarchy_id = X_dim_hierarchy_id;*/
207
208 END Cascade_Delete;
209
210 END CN_DIHY_TWO_API_PKG;