[Home] [Help]
PACKAGE BODY: APPS.CN_DIM_HIERARCHIES_PKG
Source
1 PACKAGE BODY cn_dim_hierarchies_pkg AS
2 --$Header: cndidhb.pls 120.4 2005/12/14 00:26:17 hanaraya ship $
3
4 -- Procedure Name
5 -- insert_row
6 -- Purpose
7 -- Insert row into cn_dim_hierarchies
8 -- History
9 -- 18-MAY-2001 mblum Created
10 --
11 PROCEDURE insert_row
12 (x_header_dim_hierarchy_id IN CN_DIM_HIERARCHIES.HEADER_DIM_HIERARCHY_ID%TYPE,
13 x_start_date IN CN_DIM_HIERARCHIES.START_DATE%TYPE,
14 x_end_date IN CN_DIM_HIERARCHIES.END_DATE%TYPE,
15 x_root_node OUT NOCOPY CN_DIM_HIERARCHIES.ROOT_NODE%TYPE,
16 x_dim_hierarchy_id OUT NOCOPY CN_DIM_HIERARCHIES.DIM_HIERARCHY_ID%TYPE,
17 x_org_id IN CN_DIM_HIERARCHIES.ORG_ID%TYPE) IS
18
19 BEGIN
20 -- get the next pk from sequence
21 SELECT cn_dim_hierarchies_s.nextval
22 INTO x_dim_hierarchy_id
23 FROM dual;
24
25 SELECT cn_hierarchy_nodes_s.nextval
26 INTO x_root_node
27 FROM dual;
28
29
30
31 -- create hierarchy
32 INSERT into cn_dim_hierarchies
33 (dim_hierarchy_id,
34 org_id, --MOAC
35 start_date,
36 end_date,
37 root_node,
38 header_dim_hierarchy_id,
39 last_update_date,
40 last_updated_by,
41 last_update_login,
42 creation_date,
43 created_by,
44 object_version_number)
45 VALUES
46 (x_dim_hierarchy_id,
47 x_org_id, --MOAC
48 x_start_date,
49 x_end_date,
50 null,
51 x_header_dim_hierarchy_id,
52 sysdate,
53 fnd_global.user_id,
54 fnd_global.login_id,
55 sysdate,
56 fnd_global.user_id,
57 1);
58
59 -- create new root node
60 INSERT INTO cn_hierarchy_nodes
61 (dim_hierarchy_id, org_id,value_id, name,
62 created_by, creation_date,
63 last_updated_by, last_update_date, last_update_login)
64 VALUES
65 (X_dim_hierarchy_id,x_org_id, X_root_node, -- MOAC Change
66 fnd_message.get_string('CN', 'CN_BASE_NODE'),
67 fnd_global.user_id, sysdate,
68 fnd_global.user_id, sysdate, fnd_global.login_id);
69
70 -- create base node
71 CN_DIHY_TWO_API_PKG.Insert_Edge
72 (X_name => fnd_message.get_string('CN', 'CN_BASE_NODE'),
73 X_dim_hierarchy_id => x_dim_hierarchy_id,
74 X_value_id => x_root_node,
75 X_parent_value_id => null,
76 X_external_id => null,
77 X_hierarchy_api_id => NULL, -- not used
78 x_org_id => x_org_id);
79
80 Update cn_dim_hierarchies
81 Set root_node = x_root_node
82 Where dim_hierarchy_id = x_dim_hierarchy_id;
83
84
85 END Insert_Row;
86
87 --
88 -- Procedure Name
89 -- update_row
90 -- Purpose
91 -- Update a row in cn_dim_hierarchies
92 -- History
93 -- 18-MAY-2001 mblum Created
94 --
95 PROCEDURE update_row
96 (x_dim_hierarchy_id IN CN_DIM_HIERARCHIES.DIM_HIERARCHY_ID%TYPE,
97 x_header_dim_hierarchy_id IN CN_DIM_HIERARCHIES.HEADER_DIM_HIERARCHY_ID%TYPE,
98 x_start_date IN CN_DIM_HIERARCHIES.START_DATE%TYPE,
99 x_end_date IN CN_DIM_HIERARCHIES.END_DATE%TYPE,
100 x_root_node IN CN_DIM_HIERARCHIES.ROOT_NODE%TYPE,
101 x_object_version_number IN OUT NOCOPY CN_DIM_HIERARCHIES.OBJECT_VERSION_NUMBER%TYPE,
102 x_org_id IN CN_DIM_HIERARCHIES.ORG_ID%TYPE) IS
103 BEGIN
104 x_object_version_number := x_object_version_number + 1;
105 update cn_dim_hierarchies set
106 start_date = x_start_date,
107 end_date = x_end_date,
108 root_node = x_root_node,
109 header_dim_hierarchy_id = x_header_dim_hierarchy_id,
110 last_update_date = sysdate,
111 last_updated_by = fnd_global.user_id,
112 last_update_login = fnd_global.login_id,
113 object_version_number = x_object_version_number
114 where dim_hierarchy_id = x_dim_hierarchy_id;
115 END Update_Row;
116
117
118 --
119 -- Procedure Name
120 -- delete_row
121 -- Purpose
122 -- Delete a row from cn_dim_hierarchies
123 -- History
124 -- 18-MAY-2001 mblum Created
125 --
126 PROCEDURE delete_row
127 (x_dim_hierarchy_id IN CN_DIM_HIERARCHIES.DIM_HIERARCHY_ID%TYPE) IS
128 BEGIN
129 DELETE from cn_dim_hierarchies
130 WHERE dim_hierarchy_id = x_dim_hierarchy_id;
131 if (sql%notfound) then
132 fnd_message.set_name('CN', 'CN_RECORD_DELETED');
133 fnd_msg_pub.add;
134 raise fnd_api.g_exc_error;
135 end if;
136
137 -- remove dangling edges and nodes
138 delete from cn_hierarchy_nodes e where
139 dim_hierarchy_id = x_dim_hierarchy_id;
140 delete from cn_hierarchy_edges e where
141 dim_hierarchy_id = x_dim_hierarchy_id;
142
143 END Delete_Row;
144
145
146 END CN_DIM_HIERARCHIES_PKG;