[Home] [Help]
PACKAGE BODY: APPS.MSD_CS_DEFN_DIM_LOAD_DATA
Source
1 package body msd_CS_DEFN_DIM_load_data as
2 /* $Header: msdcsddb.pls 115.6 2003/09/12 22:16:43 pinamati ship $ */
3
4 Procedure load_row (
5 p_definition_name in varchar2,
6 p_dimension_code in varchar2,
7 p_collect_flag in varchar2,
8 /* p_collect_level_name in varchar2, */
9 p_collect_level_id in number,
10 p_aggregation_type in varchar2,
11 p_allocation_type in varchar2,
12 p_owner in varchar2,
13 p_last_update_date in varchar2,
14 p_custom_mode in varchar2
15 ) is
16 Begin
17
18 Update_row(
19 p_definition_name ,
20 p_dimension_code ,
21 p_collect_flag ,
22 p_collect_level_id ,
23 p_aggregation_type ,
24 p_allocation_type ,
25 p_owner ,
26 p_last_update_date ,
27 p_custom_mode );
28 Exception
29 when no_data_found then
30 Insert_row(
31 p_definition_name ,
32 p_dimension_code ,
33 p_collect_flag ,
34 p_collect_level_id ,
35 p_aggregation_type ,
36 p_allocation_type ,
37 p_owner ,
38 p_last_update_date );
39
40 End;
41
42 Procedure Update_row (
43 p_definition_name in varchar2,
44 p_dimension_code in varchar2,
45 p_collect_flag in varchar2,
46 p_collect_level_id in number,
47 p_aggregation_type in varchar2,
48 p_allocation_type in varchar2,
49 p_owner in varchar2,
50 p_last_update_date in varchar2,
51 p_custom_mode in varchar2
52 ) is
53
54 l_user number;
55 l_definition_id number;
56 l_cs_definition_id number;
57 l_level_id number;
58
59 f_ludate date; -- entity update date in file
60 db_luby number; -- entity owner in db
61 db_ludate date; -- entity update date in db
62
63
64 cursor c1 is
65 select cs_definition_id from msd_cs_definitions
66 where name = p_definition_name;
67
68 /* cursor c2 is
69 select level_id from msd_levels
70 where level_name = p_collect_level_name;
71 */
72 cursor c3(p_cs_def_id in number, p_dim_code in varchar2) is
73 select last_updated_by, last_update_date
74 from msd_cs_defn_dim_dtls
75 where cs_definition_id = p_cs_def_id
76 and dimension_code = p_dim_code;
77
78 Begin
79
80 if p_owner = 'SEED' then
81 l_user := 1;
82 else
83 l_user := 0;
84 end if;
85
86 -- Translate char last_update_date to date
87 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
88
89 open c1;
90 fetch c1 into l_cs_definition_id;
91 close c1;
92
93 if l_cs_definition_id is null then
94 raise no_data_found;
95 /* fnd_message.set_name('MSD', 'MSD_CS_LOAD_INVALID_DEFN');
96 fnd_message.raise_error;
97 */
98 end if;
99
100 /* if p_collect_level_name is not null then
101
102 l_level_id := msd_cs_dfn_utl.get_level_id(p_dimension_code, p_collect_level_name);
103 */
104 l_level_id := p_collect_level_id;
105
106 /* end if; */
107
108 open c3(l_cs_definition_id, p_dimension_code);
109 fetch c3 into db_luby, db_ludate;
110
111 if (c3%notfound) then
112 raise no_data_found;
113 end if;
114
115 close c3;
116
117 --
118 -- Update record, honoring customization mode.
119 -- Record should be updated only if:
120 -- a. CUSTOM_MODE = FORCE, or
121 -- b. file owner is CUSTOM, db owner is SEED
122 -- c. owners are the same, and file_date > db_date
123 if ((p_custom_mode = 'FORCE') or
124 ((l_user = 0) and (db_luby = 1)) or
125 ((l_user = db_luby) and (f_ludate > db_ludate)))
126 then
127
128 update MSD_CS_DEFN_DIM_DTLS set
129 collect_flag = p_collect_flag,
130 collect_level_id = l_level_id,
131 aggregation_type = p_aggregation_type,
132 allocation_type = p_allocation_type,
133 last_updated_by = l_user,
134 last_update_date = f_ludate
135 where
136 cs_definition_id = l_cs_definition_id and
137 dimension_code = p_dimension_code;
138 end if;
139
140 if (sql%notfound) then
141 raise no_data_found;
142 end if;
143
144 End;
145
146 Procedure Insert_row (
147 p_definition_name in varchar2,
148 p_dimension_code in varchar2,
149 p_collect_flag in varchar2,
150 p_collect_level_id in number ,
151 p_aggregation_type in varchar2,
152 p_allocation_type in varchar2,
153 p_owner in varchar2,
154 p_last_update_date in varchar2
155 ) is
156
157
158 l_user number;
159 l_pk_id number;
160 l_level_id number;
161 l_cs_definition_id number;
162 f_ludate date; -- entity update date in file
163
164 cursor c1 is
165 select cs_definition_id
166 from msd_cs_definitions
167 where
168 name = p_definition_name;
169 /*
170 cursor c2 is
171 select level_id
172 from msd_levels
173 where
174 level_name = p_collect_level_name;
175 */
176 Begin
177 if p_owner = 'SEED' then
178 l_user := 1;
179 else
180 l_user := 0;
181 end if;
182
183 open c1;
184 fetch c1 into l_cs_definition_id;
185 close c1;
186
187 if l_cs_definition_id is null then
188 fnd_message.set_name('MSD', 'MSD_CS_LOAD_INVALID_DEFN');
189 fnd_message.raise_error;
190 end if;
191
192 /* if p_collect_level_name is not null then
193
194 l_level_id := msd_cs_dfn_utl.get_level_id(p_dimension_code, p_collect_level_name);
195
196 open c2;
197 fetch c2 into l_level_id;
198 close c2;
199 */
200 l_level_id := p_collect_level_id;
201
202 /* end if; */
203
204 -- Translate char last_update_date to date
205 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
206
207 select MSD_CS_DEFN_DIM_DTLS_S.nextval into l_pk_id from dual;
208
209 insert into MSD_CS_DEFN_DIM_DTLS(
210 cs_defn_dim_dtls_id,
211 cs_definition_id,
212 dimension_code,
213 collect_flag,
214 collect_level_id,
215 aggregation_type,
216 allocation_type,
217 created_by,
218 creation_date,
219 last_updated_by,
220 last_update_date ,
221 last_update_login
222 )
223 values
224 (
225 l_pk_id,
226 l_cs_definition_id,
227 p_dimension_code,
228 p_collect_flag,
229 l_level_id,
230 p_aggregation_type,
231 p_allocation_type,
232 l_user,
233 f_ludate,
234 l_user,
235 f_ludate,
236 fnd_global.login_id
237 );
238
239 End;
240
241 End;