1 PACKAGE BODY FEM_WEBADI_FACT_TAB_UTILS_PVT AS
2 /* $Header: FEMVADIFCTRUTILB.pls 120.5 2008/02/20 06:48:27 jcliving noship $ */
3
4 G_OBJECT_DEFINITION_ID NUMBER := NULL;
5 G_LEDGER_ID NUMBER := FND_PROFILE.Value_Specific('FEM_LEDGER',FND_GLOBAL.USER_ID);
6 G_OBJECT_ID NUMBER := NULL;
7 G_ENTER_IN_DIMS VARCHAR2(1) := NULL;
8
9 PROCEDURE UPLOAD_FACTOR_TABLE1_INTERFACE(
10 P_RULE_NAME VARCHAR2,
11 P_FOLDER_NAME VARCHAR2,
12 P_RULE_DESCRIPTION VARCHAR2,
13 P_VERSION_NAME VARCHAR2,
14 P_START_DATE VARCHAR2,
15 P_END_DATE VARCHAR2,
16 P_VERSION_DESCRIPTION VARCHAR2,
17 P_FACTOR_TYPE VARCHAR2,
18 P_MATCHING_DIM1 VARCHAR2,
19 P_HIERARCHY1 VARCHAR2,
20 P_HIER1_VER VARCHAR2,
21 P_LEVEL1 VARCHAR2,
22 P_HIERARCHY_REL1 VARCHAR2,
23 P_MATCHING_DIM2 VARCHAR2,
24 P_HIERARCHY2 VARCHAR2,
25 P_HIER2_VER VARCHAR2,
26 P_LEVEL2 VARCHAR2,
27 P_HIERARCHY_REL2 VARCHAR2,
28 P_MATCHING_DIM3 VARCHAR2,
29 P_HIERARCHY3 VARCHAR2,
30 P_HIER3_VER VARCHAR2,
31 P_LEVEL3 VARCHAR2,
32 P_HIERARCHY_REL3 VARCHAR2,
33 P_DISTRIBUTION_DIM VARCHAR2,
34 P_FORCE_TO_HUNDRED VARCHAR2,
35 P_OBJECT_ACCESS_CODE VARCHAR2,
36 P_MATCHING_DIM1_MEM VARCHAR2,
37 P_MATCHING_DIM2_MEM VARCHAR2,
38 P_MATCHING_DIM3_MEM VARCHAR2,
39 P_DISTRIBUTION_DIM_MEM VARCHAR2,
40 P_AMOUNT VARCHAR2)
41 IS
42
43 l_user_id number := FND_GLOBAL.USER_ID;
44 l_update_login number := FND_GLOBAL.LOGIN_ID;
45 l_row_num number := NULL;
46 l_parent_row_num number := -1;
47 l_level_num number := 0;
48 l_dimension_member varchar2(30);
49 x_err_code number;
50 x_num_msg number;
51 l_vs_combo_id number := FEM_DIMENSION_UTIL_PKG.Local_VS_Combo_ID(G_LEDGER_ID,x_err_code,x_num_msg);
52 e_invalid_amount exception;
53 l_insert_m1 varchar2(1);
54 l_insert_m2 varchar2(1);
55 l_insert_m3 varchar2(1);
56 BEGIN
57
58 if(P_AMOUNT = 0) THEN
59 raise e_invalid_amount;
60 end if;
61 --------------------------------------------------------- PART 1 ------------------------------------------------------------------------------
62 --- Entering the rule and rule version details in required tables.This block is executed for only once first time the PLSQL API is invoked.
63 -----------------------------------------------------------------------------------------------------------------------------------------------
64
65 POPULATE_RULE_DETAILS(P_RULE_NAME,P_FOLDER_NAME,P_RULE_DESCRIPTION,P_VERSION_NAME,P_START_DATE,P_END_DATE,P_VERSION_DESCRIPTION,l_vs_combo_id,P_OBJECT_ACCESS_CODE);
66
67 ------------------------------------------------------ PART 1 ENDS ----------------------------------------------------------------------------
68
69 --------------------------------------------------------- PART 2 ------------------------------------------------------------------------------
70 --- Entering data into fem_factor_tables and fem_factor_table_dims. This block is executed for only once first time the PLSQL API is invoked.
71 -----------------------------------------------------------------------------------------------------------------------------------------------
72
73 POPULATE_FACTOR_TABLE_DIMS(P_VERSION_NAME,P_FACTOR_TYPE,P_MATCHING_DIM1,P_HIERARCHY1,P_HIER1_VER,P_LEVEL1,P_HIERARCHY_REL1,P_MATCHING_DIM2,
74 P_HIERARCHY2,P_HIER2_VER,P_LEVEL2,P_HIERARCHY_REL2,P_MATCHING_DIM3,P_HIERARCHY3,P_HIER3_VER,P_LEVEL3,P_HIERARCHY_REL3,P_DISTRIBUTION_DIM,P_FORCE_TO_HUNDRED);
75
76 ----------------------------------------------------- PART 2 ENDS -----------------------------------------------------------------------------
77
78 --------------------------------------------------------- PART 3 ------------------------------------------------------------------------------
79 --- Entering dimension members and factor value in the fem_factor_table_fctrs.
80 -----------------------------------------------------------------------------------------------------------------------------------------------
81
82 --------------- Entering values for the first matching dimension member. The factor_value will go with distribution dimension as that shall be the leaf dimension member.
83
84 select decode((select 'Y' from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim1_mem and level_num =0),'Y','N','Y') into l_insert_m1 from dual;
85
86 if(l_insert_m1 = 'Y') then
87
88 select FEM_FACTORS_ROW_NUM_SEQ.NEXTVAL into l_row_num from dual;
89
90 l_dimension_member := p_matching_dim1_mem;
91
92 insert into FEM_FACTOR_TABLE_FCTRS(OBJECT_DEFINITION_ID,ROW_NUM,PARENT_ROW_NUM,LEVEL_NUM,DIM_MEMBER,FACTOR_VALUE,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,
93 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
94 values(G_OBJECT_DEFINITION_ID,l_row_num,l_parent_row_num,l_level_num,l_dimension_member,0,sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
95
96 else
97 select row_num into l_row_num from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim1_mem and level_num = 0;
98 end if;
99
100 l_parent_row_num := l_row_num;
101 l_level_num := l_level_num + 1;
102
103 --------------- Entering values for the second matching dimension member. The factor_value will go with distribution dimension as that shall be the leaf dimension member.
104
105 if(p_matching_dim2_mem is not NULL AND length(trim(p_matching_dim2_mem)) <> 0) then
106
107 select decode((select 'Y' from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim2_mem and level_num = 1 and parent_row_num = l_parent_row_num),'Y','N','Y') into l_insert_m2 from dual;
108
109 if(l_insert_m2 = 'Y') then
110
111 select FEM_FACTORS_ROW_NUM_SEQ.NEXTVAL into l_row_num from dual;
112
113 l_dimension_member := p_matching_dim2_mem;
114
115 insert into FEM_FACTOR_TABLE_FCTRS(OBJECT_DEFINITION_ID,ROW_NUM,PARENT_ROW_NUM,LEVEL_NUM,DIM_MEMBER,FACTOR_VALUE,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,
116 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
117 values(G_OBJECT_DEFINITION_ID,l_row_num,l_parent_row_num,l_level_num,l_dimension_member,0,sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
118 else
119 select row_num into l_row_num from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim2_mem and level_num = 1 and parent_row_num = l_parent_row_num;
120 end if;
121
122 l_parent_row_num := l_row_num;
123 l_level_num := l_level_num + 1;
124 end if;
125
126 --------------- Entering values for the third matching dimension member. The factor_value will go with distribution dimension as that shall be the leaf dimension member.
127
128 if(p_matching_dim3_mem is not NULL AND length(trim(p_matching_dim3_mem)) <> 0) then
129
130 select decode((select 'Y' from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim3_mem and level_num =2 and parent_row_num = l_parent_row_num),'Y','N','Y') into l_insert_m3 from dual;
131
132 if(l_insert_m3 = 'Y') then
133
134 select FEM_FACTORS_ROW_NUM_SEQ.NEXTVAL into l_row_num from dual;
135
136 l_dimension_member := p_matching_dim3_mem;
137
138 insert into FEM_FACTOR_TABLE_FCTRS(OBJECT_DEFINITION_ID,ROW_NUM,PARENT_ROW_NUM,LEVEL_NUM,DIM_MEMBER,FACTOR_VALUE,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,
139 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
140 values(G_OBJECT_DEFINITION_ID,l_row_num,l_parent_row_num,l_level_num,l_dimension_member,0,sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
141 else
142 select row_num into l_row_num from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim3_mem and level_num = 2 and parent_row_num = l_parent_row_num;
143 end if;
144
145 l_parent_row_num := l_row_num;
146 l_level_num := l_level_num + 1;
147 end if;
148
149 --------------- Entering values for the distribution dimension. The factor_value will go with distribution dimension as that shall be the leaf dimension member.
150
151 select FEM_FACTORS_ROW_NUM_SEQ.NEXTVAL into l_row_num from dual;
152
153 l_dimension_member := p_distribution_dim_mem;
154
155 insert into FEM_FACTOR_TABLE_FCTRS(OBJECT_DEFINITION_ID,ROW_NUM,PARENT_ROW_NUM,LEVEL_NUM,DIM_MEMBER,FACTOR_VALUE,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,
156 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
157 values(G_OBJECT_DEFINITION_ID,l_row_num,l_parent_row_num,l_level_num,l_dimension_member,p_amount,sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
158
159 --------------------------------------------------------- END PART 3 --------------------------------------------------------------------------
160 EXCEPTION
161 --
162 WHEN DUP_VAL_ON_INDEX THEN
163 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_DUPLICATE_ROWS');
164 APP_EXCEPTION.Raise_Exception ;
165 WHEN e_invalid_amount THEN
166 FND_MESSAGE.SET_NAME('FEM','FEM_NO_FACTORS_ENTERED_TXT');
167 APP_EXCEPTION.Raise_Exception ;
168
169 END UPLOAD_FACTOR_TABLE1_INTERFACE;
170
171 PROCEDURE UPLOAD_FACTOR_TABLE2_INTERFACE(
172 P_RULE_NAME VARCHAR2,
173 P_FOLDER_NAME VARCHAR2,
174 P_RULE_DESCRIPTION VARCHAR2,
175 P_VERSION_NAME VARCHAR2,
176 P_START_DATE VARCHAR2,
177 P_END_DATE VARCHAR2,
178 P_VERSION_DESCRIPTION VARCHAR2,
179 P_FACTOR_TYPE VARCHAR2,
180 P_MATCHING_DIM1 VARCHAR2,
181 P_HIERARCHY1 VARCHAR2,
182 P_HIER1_VER VARCHAR2,
183 P_LEVEL1 VARCHAR2,
184 P_HIERARCHY_REL1 VARCHAR2,
185 P_MATCHING_DIM2 VARCHAR2,
186 P_HIERARCHY2 VARCHAR2,
187 P_HIER2_VER VARCHAR2,
188 P_LEVEL2 VARCHAR2,
189 P_HIERARCHY_REL2 VARCHAR2,
190 P_MATCHING_DIM3 VARCHAR2,
191 P_HIERARCHY3 VARCHAR2,
192 P_HIER3_VER VARCHAR2,
193 P_LEVEL3 VARCHAR2,
194 P_HIERARCHY_REL3 VARCHAR2,
195 P_FORCE_TO_HUNDRED VARCHAR2,
196 P_OBJECT_ACCESS_CODE VARCHAR2,
197 P_MATCHING_DIM1_MEM VARCHAR2,
198 P_MATCHING_DIM2_MEM VARCHAR2,
199 P_MATCHING_DIM3_MEM VARCHAR2,
200 P_AMOUNT VARCHAR2)
201 IS
202
203 l_user_id number := FND_GLOBAL.USER_ID;
204 l_update_login number := FND_GLOBAL.LOGIN_ID;
205 l_row_num number := NULL;
206 l_parent_row_num number := -1;
207 l_level_num number := 0;
208 l_dimension_member varchar2(30);
209 l_amount number := null;
210 x_err_code number;
211 x_num_msg number;
212 l_vs_combo_id number := FEM_DIMENSION_UTIL_PKG.Local_VS_Combo_ID(G_LEDGER_ID,x_err_code,x_num_msg);
213 l_insert_m1 varchar2(1);
214 l_insert_m2 varchar2(1);
215 l_insert_m3 varchar2(1);
216
217 e_invalid_amount exception;
218 BEGIN
219
220
221 if(P_AMOUNT = 0) THEN
222 raise e_invalid_amount;
223 END IF;
224 --------------------------------------------------------- PART 1 ------------------------------------------------------------------------------
225 --- Entering the rule and rule version details in required tables.This block is executed for only once first time the PLSQL API is invoked.
226 -----------------------------------------------------------------------------------------------------------------------------------------------
227
228 POPULATE_RULE_DETAILS(P_RULE_NAME,P_FOLDER_NAME,P_RULE_DESCRIPTION,P_VERSION_NAME,P_START_DATE,P_END_DATE,P_VERSION_DESCRIPTION,l_vs_combo_id,P_OBJECT_ACCESS_CODE);
229
230 ------------------------------------------------------ PART 1 ENDS ----------------------------------------------------------------------------
231
232 --------------------------------------------------------- PART 2 ------------------------------------------------------------------------------
233 --- Entering data into fem_factor_tables and fem_factor_table_dims. This block is executed for only once first time the PLSQL API is invoked.
234 -----------------------------------------------------------------------------------------------------------------------------------------------
235
236 POPULATE_FACTOR_TABLE_DIMS(P_VERSION_NAME,P_FACTOR_TYPE,P_MATCHING_DIM1,P_HIERARCHY1,P_HIER1_VER,P_LEVEL1,P_HIERARCHY_REL1,P_MATCHING_DIM2,
237 P_HIERARCHY2,P_HIER2_VER,P_LEVEL2,P_HIERARCHY_REL2,P_MATCHING_DIM3,P_HIERARCHY3,P_HIER3_VER,P_LEVEL3,P_HIERARCHY_REL3,NULL,P_FORCE_TO_HUNDRED);
238
239 ----------------------------------------------------- PART 2 ENDS -----------------------------------------------------------------------------
240
241 --------------------------------------------------------- PART 3 ------------------------------------------------------------------------------
242 --- Entering dimension members and factor value in the fem_factor_table_fctrs.
243 -----------------------------------------------------------------------------------------------------------------------------------------------
244
245 --------------- Entering values for the first matching dimension member.
246
247 select decode((select 'Y' from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim1_mem and level_num =0),'Y','N','Y') into l_insert_m1 from dual;
248
249 if(l_insert_m1 = 'Y') then
250
251 select FEM_FACTORS_ROW_NUM_SEQ.NEXTVAL into l_row_num from dual;
252
253 l_dimension_member := p_matching_dim1_mem;
254
255 if((p_matching_dim2_mem is null OR length(trim(p_matching_dim2_mem)) = 0) AND (p_matching_dim3_mem is null OR length(trim(p_matching_dim3_mem)) = 0)) then
256 l_amount := p_amount;
257 else
258 l_amount := 0;
259 end if;
260
261 insert into FEM_FACTOR_TABLE_FCTRS(OBJECT_DEFINITION_ID,ROW_NUM,PARENT_ROW_NUM,LEVEL_NUM,DIM_MEMBER,FACTOR_VALUE,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,
262 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
263 values(G_OBJECT_DEFINITION_ID,l_row_num,l_parent_row_num,l_level_num,l_dimension_member,l_amount,sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
264 else
265 select row_num into l_row_num from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim1_mem and level_num = 0;
266 end if;
267
268 l_parent_row_num := l_row_num;
269 l_level_num := l_level_num + 1;
270
271 --------------- Entering values for the second matching dimension member. The factor_value will go with distribution dimension as that shall be the leaf dimension member.
272
273 if(p_matching_dim2_mem is not NULL AND length(trim(p_matching_dim2_mem)) <> 0) then
274
275 select decode((select 'Y' from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim2_mem and level_num = 1 and parent_row_num = l_parent_row_num),'Y','N','Y') into l_insert_m2 from dual;
276
277 if(l_insert_m2 = 'Y') then
278
279 select FEM_FACTORS_ROW_NUM_SEQ.NEXTVAL into l_row_num from dual;
280
281 l_dimension_member := p_matching_dim2_mem;
282
283 if(p_matching_dim3_mem is null OR length(trim(p_matching_dim3_mem)) = 0) then
284 l_amount := p_amount;
285 else
286 l_amount := 0;
287 end if;
288
289 insert into FEM_FACTOR_TABLE_FCTRS(OBJECT_DEFINITION_ID,ROW_NUM,PARENT_ROW_NUM,LEVEL_NUM,DIM_MEMBER,FACTOR_VALUE,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,
290 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
291 values(G_OBJECT_DEFINITION_ID,l_row_num,l_parent_row_num,l_level_num,l_dimension_member,l_amount,sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
292 else
293 select row_num into l_row_num from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim2_mem and level_num = 1 and parent_row_num = l_parent_row_num;
294 end if;
295
296 l_parent_row_num := l_row_num;
297 l_level_num := l_level_num + 1;
298 end if;
299
300 --------------- Entering values for the third matching dimension member. The factor_value will go with distribution dimension as that shall be the leaf dimension member.
301
302 if(p_matching_dim3_mem is not NULL AND length(trim(p_matching_dim3_mem)) <> 0) then
306 if(l_insert_m3 = 'Y') then
303
304 select decode((select 'Y' from fem_factor_table_fctrs where object_definition_id = g_object_definition_id and dim_member = p_matching_dim3_mem and level_num =2 and parent_row_num = l_parent_row_num),'Y','N','Y') into l_insert_m3 from dual;
305
307
308 select FEM_FACTORS_ROW_NUM_SEQ.NEXTVAL into l_row_num from dual;
309
310 l_dimension_member := p_matching_dim3_mem;
311
312 insert into FEM_FACTOR_TABLE_FCTRS(OBJECT_DEFINITION_ID,ROW_NUM,PARENT_ROW_NUM,LEVEL_NUM,DIM_MEMBER,FACTOR_VALUE,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,
313 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
314 values(G_OBJECT_DEFINITION_ID,l_row_num,l_parent_row_num,l_level_num,l_dimension_member,p_amount,sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
315
316 end if;
317 end if;
318
319 --------------------------------------------------------- END PART 3 --------------------------------------------------------------------------
320 EXCEPTION
321 --
322 WHEN DUP_VAL_ON_INDEX THEN
323 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_DUPLICATE_ROWS');
324 APP_EXCEPTION.Raise_Exception ;
325 WHEN e_invalid_amount THEN
326 FND_MESSAGE.SET_NAME('FEM','FEM_NO_FACTORS_ENTERED_TXT');
327 APP_EXCEPTION.Raise_Exception ;
328
329 END UPLOAD_FACTOR_TABLE2_INTERFACE;
330
331 PROCEDURE UPLOAD_FACTOR_TABLE3_INTERFACE(
332 P_RULE_NAME VARCHAR2,
333 P_FOLDER_NAME VARCHAR2,
334 P_RULE_DESCRIPTION VARCHAR2,
335 P_VERSION_NAME VARCHAR2,
336 P_START_DATE VARCHAR2,
337 P_END_DATE VARCHAR2,
338 P_VERSION_DESCRIPTION VARCHAR2,
339 P_FACTOR_TYPE VARCHAR2,
340 P_DISTRIBUTION_DIM VARCHAR2,
341 P_FORCE_TO_HUNDRED VARCHAR2,
342 P_OBJECT_ACCESS_CODE VARCHAR2,
343 P_DISTRIBUTION_DIM_MEM VARCHAR2,
344 P_AMOUNT VARCHAR2)
345 IS
346
347 l_user_id number := FND_GLOBAL.USER_ID;
348 l_update_login number := FND_GLOBAL.LOGIN_ID;
349 l_row_num number := NULL;
350 l_parent_row_num number := -1;
351 l_level_num number := 0;
352 l_dimension_member varchar2(30);
353 x_err_code number;
354 x_num_msg number;
355 l_vs_combo_id number := FEM_DIMENSION_UTIL_PKG.Local_VS_Combo_ID(G_LEDGER_ID,x_err_code,x_num_msg);
356 e_invalid_amount exception;
357
358 BEGIN
359
360 if(P_AMOUNT = 0) THEN
361 raise e_invalid_amount;
362 end if;
363 --------------------------------------------------------- PART 1 ------------------------------------------------------------------------------
364 --- Entering the rule and rule version details in required tables.This block is executed for only once first time the PLSQL API is invoked.
365 -----------------------------------------------------------------------------------------------------------------------------------------------
366
367 POPULATE_RULE_DETAILS(P_RULE_NAME,P_FOLDER_NAME,P_RULE_DESCRIPTION,P_VERSION_NAME,P_START_DATE,P_END_DATE,P_VERSION_DESCRIPTION,l_vs_combo_id,P_OBJECT_ACCESS_CODE);
368
369 ------------------------------------------------------ PART 1 ENDS ----------------------------------------------------------------------------
370
371 --------------------------------------------------------- PART 2 ------------------------------------------------------------------------------
372 --- Entering data into fem_factor_tables and fem_factor_table_dims. This block is executed for only once first time the PLSQL API is invoked.
373 -----------------------------------------------------------------------------------------------------------------------------------------------
374
375 POPULATE_FACTOR_TABLE_DIMS(P_VERSION_NAME,P_FACTOR_TYPE,NULL,NULL,NULL,NULL,NULL,NULL,
376 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,P_DISTRIBUTION_DIM,P_FORCE_TO_HUNDRED);
377
378 ----------------------------------------------------- PART 2 ENDS -----------------------------------------------------------------------------
379
380 --------------------------------------------------------- PART 3 ------------------------------------------------------------------------------
381 --- Entering dimension members and factor value in the fem_factor_table_fctrs.
382 -----------------------------------------------------------------------------------------------------------------------------------------------
383
384 --------------- Entering values for the distribution dimension member.
385
386 select FEM_FACTORS_ROW_NUM_SEQ.NEXTVAL into l_row_num from dual;
387
388 l_dimension_member := p_distribution_dim_mem;
389
390 insert into FEM_FACTOR_TABLE_FCTRS(OBJECT_DEFINITION_ID,ROW_NUM,PARENT_ROW_NUM,LEVEL_NUM,DIM_MEMBER,FACTOR_VALUE,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,
391 LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
392 values(G_OBJECT_DEFINITION_ID,l_row_num,l_parent_row_num,l_level_num,l_dimension_member,P_AMOUNT,sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
393
394
395 --------------------------------------------------------- END PART 3 --------------------------------------------------------------------------
396 EXCEPTION
397 --
398 WHEN DUP_VAL_ON_INDEX THEN
399 FND_MESSAGE.SET_NAME('FEM','FEM_ADI_DUPLICATE_ROWS');
400 APP_EXCEPTION.Raise_Exception ;
401 WHEN e_invalid_amount THEN
402 FND_MESSAGE.SET_NAME('FEM','FEM_NO_FACTORS_ENTERED_TXT');
403 APP_EXCEPTION.Raise_Exception ;
404
405 END UPLOAD_FACTOR_TABLE3_INTERFACE;
409 P_FOLDER_NAME VARCHAR2,
406
407 PROCEDURE POPULATE_RULE_DETAILS(
408 P_RULE_NAME VARCHAR2,
410 P_RULE_DESCRIPTION VARCHAR2,
411 P_VERSION_NAME VARCHAR2,
412 P_START_DATE VARCHAR2,
413 P_END_DATE VARCHAR2,
414 P_VERSION_DESCRIPTION VARCHAR2,
415 P_VS_COMBO_ID VARCHAR2,
416 P_OBJECT_ACCESS_CODE VARCHAR2)
417 IS
418
419 l_old_rule varchar2(1) := NULL;
420 l_object_type_code varchar2(30):= 'FACTOR_TABLE';
421 l_folder_id number;
422 l_local_vs_combo_id number := p_vs_combo_id;
423 l_user_id number := FND_GLOBAL.USER_ID;
424 l_object_access_code varchar2(30);
425 l_object_origin_code varchar2(30):= 'USER';
426 l_update_login number := FND_GLOBAL.LOGIN_ID;
427
428 BEGIN
429 select folder_id into l_folder_id from fem_folders_vl where folder_name = P_FOLDER_NAME;
430
431 select lookup_code into l_object_access_code from fem_lookups where lookup_type = 'FEM_EDIT_PERMISSION_DSC' and meaning = p_object_access_code;
432
433 select decode((select 'Y' from fem_object_catalog_vl where object_name = p_rule_name),'Y','Y',NULL) into l_old_rule from dual;
434
435 IF(l_old_rule is NULL) THEN
436 select FEM_OBJECT_ID_SEQ.NEXTVAL into G_OBJECT_ID from dual;
437
438 INSERT INTO FEM_OBJECT_CATALOG_B(OBJECT_ID,OBJECT_TYPE_CODE,FOLDER_ID,LOCAL_VS_COMBO_ID,CREATION_DATE,CREATED_BY,OBJECT_ACCESS_CODE,OBJECT_ORIGIN_CODE,LAST_UPDATED_BY,
439 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
440 VALUES(G_OBJECT_ID,l_object_type_code,l_folder_id,l_local_vs_combo_id,sysdate,l_user_id,l_object_access_code,
441 l_object_origin_code,l_user_id,sysdate,l_update_login,0);
442
443 INSERT INTO FEM_OBJECT_CATALOG_TL(OBJECT_ID,OBJECT_NAME,LANGUAGE,SOURCE_LANG,DESCRIPTION,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
444 SELECT G_OBJECT_ID,P_RULE_NAME,LANGUAGE_CODE,'US',P_RULE_DESCRIPTION,l_user_id,sysdate,l_user_id,sysdate,l_update_login
445 FROM FND_LANGUAGES WHERE INSTALLED_FLAG IN ('I','B');
446
447
448 select FEM_OBJECT_DEFINITION_ID_SEQ.NEXTVAL into G_OBJECT_DEFINITION_ID from dual;
449
450 INSERT INTO FEM_OBJECT_DEFINITION_B(OBJECT_DEFINITION_ID,OBJECT_ID,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,OBJECT_ORIGIN_CODE,CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,
451 LAST_UPDATE_DATE,APPROVAL_STATUS_CODE,OLD_APPROVED_COPY_FLAG,OLD_APPROVED_COPY_OBJ_DEF_ID,APPROVED_BY,APPROVAL_DATE,LAST_UPDATE_LOGIN,
452 OBJECT_VERSION_NUMBER)
453 VALUES(G_OBJECT_DEFINITION_ID,G_OBJECT_ID,to_date(P_START_DATE,'DD-MM-RRRR'),to_date(P_END_DATE,'DD-MM-RRRR'),l_object_origin_code,sysdate,
454 l_user_id,l_user_id,sysdate,'NOT_APPLICABLE','N',NULL,NULL,NULL,l_update_login,0);
455
456 INSERT INTO FEM_OBJECT_DEFINITION_TL(OBJECT_DEFINITION_ID,OBJECT_ID,LANGUAGE,SOURCE_LANG,OLD_APPROVED_COPY_FLAG,DISPLAY_NAME,DESCRIPTION,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
457 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
458 SELECT G_OBJECT_DEFINITION_ID,G_OBJECT_ID,LANGUAGE_CODE,'US','N',P_VERSION_NAME,P_VERSION_DESCRIPTION,l_user_id,sysdate,l_user_id,sysdate,
459 l_update_login FROM FND_LANGUAGES WHERE INSTALLED_FLAG IN ('I','B');
460 G_ENTER_IN_DIMS := 'Y';
461
462 ELSE
463
464 select object_id into G_OBJECT_ID from fem_object_catalog_vl where object_name = p_rule_name;
465 select object_definition_id into G_OBJECT_DEFINITION_ID from fem_object_definition_vl where object_id = G_OBJECT_ID and display_name = p_version_name;
466 G_ENTER_IN_DIMS := 'N';
467 END IF;
468
469 END POPULATE_RULE_DETAILS;
470
471 PROCEDURE POPULATE_FACTOR_TABLE_DIMS(
472 P_VERSION_NAME VARCHAR2,
473 P_FACTOR_TYPE VARCHAR2,
474 P_MATCHING_DIM1 VARCHAR2,
475 P_HIERARCHY1 VARCHAR2,
476 P_HIER1_VER VARCHAR2,
477 P_LEVEL1 VARCHAR2,
478 P_HIERARCHY_REL1 VARCHAR2,
479 P_MATCHING_DIM2 VARCHAR2,
480 P_HIERARCHY2 VARCHAR2,
481 P_HIER2_VER VARCHAR2,
482 P_LEVEL2 VARCHAR2,
483 P_HIERARCHY_REL2 VARCHAR2,
484 P_MATCHING_DIM3 VARCHAR2,
485 P_HIERARCHY3 VARCHAR2,
486 P_HIER3_VER VARCHAR2,
487 P_LEVEL3 VARCHAR2,
488 P_HIERARCHY_REL3 VARCHAR2,
489 P_DISTRIBUTION_DIM VARCHAR2,
490 P_FORCE_TO_HUNDRED VARCHAR2)
491 IS
492
493 l_user_id number := FND_GLOBAL.USER_ID;
494 l_update_login number := FND_GLOBAL.LOGIN_ID;
495 l_factor_type_code varchar2(50);
496 l_dim_id number := NULL;
497 l_hier_obj_id number := NULL;
498 l_hier_obj_def_id number := NULL;
499 l_level_id number := NULL;
500 l_level_num number := 0;
501 l_hierarchy_rel varchar2(50);
502 l_force_to_hundred varchar2(10);
503
504 BEGIN
505
509
506 select lookup_code into l_factor_type_code from fem_lookups where lookup_type = 'FEM_FACTOR_TABLE_TYPES_DSC' and meaning = p_factor_type;
507
508 if(G_ENTER_IN_DIMS = 'Y') THEN
510 insert into FEM_FACTOR_TABLES(OBJECT_DEFINITION_ID,
511 FACTOR_TYPE,
512 CREATION_DATE,
513 CREATED_BY,
514 LAST_UPDATED_BY,
515 LAST_UPDATE_DATE,
516 LAST_UPDATE_LOGIN,
517 OBJECT_VERSION_NUMBER)
518 values(
519 G_OBJECT_DEFINITION_ID,
520 l_factor_type_code,
521 sysdate,
522 l_user_id,
523 l_user_id,
524 sysdate,
525 l_update_login,
526 0);
527
528 if(l_factor_type_code <> 'DISTRIBUTE') then
529
530 -- for factor type 'MATCH' and 'MATCH_AND_DISTRIBUTE' p_match_dim1 parameter will never be null/blank/empty
531 select dimension_id into l_dim_id from fem_dimensions_vl where dimension_name = p_matching_dim1;
532
533 if(p_hierarchy1 is not null AND length(trim(p_hierarchy1)) <> 0) then ---------------------- If p_hierarchy1 is not null then p_hier1_ver and p_level1 will also be not null
534 select object_id into l_hier_obj_id from fem_object_catalog_vl where object_name = p_hierarchy1 and object_type_code = 'HIERARCHY';
535 select object_definition_id into l_hier_obj_def_id from fem_object_definition_vl where display_name = p_hier1_ver and object_id = l_hier_obj_id;
536 select dimension_group_id into l_level_id from fem_dimension_grps_vl where dimension_group_name = p_level1;
537 select lookup_code into l_hierarchy_rel from fem_lookups where lookup_type = 'FEM_COND_HIER_RELATIONS' and meaning = p_hierarchy_rel1;
538 else
539 l_hier_obj_id := null;
540 l_hier_obj_def_id := null;
541 l_level_id := null;
542 l_hierarchy_rel :=null;
543 end if;
544
545 insert into fem_factor_table_dims(OBJECT_DEFINITION_ID,LEVEL_NUM,DIMENSION_ID,DIM_USAGE_CODE,FORCE_PERCENT_FLAG,HIER_OBJECT_ID,HIER_OBJ_DEF_ID,HIER_GROUP_ID,HIER_RELATION_CODE,
546 CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
547 values(G_OBJECT_DEFINITION_ID,l_level_num,l_dim_id,'MATCH',NULL,l_hier_obj_id,l_hier_obj_def_id,l_level_id,l_hierarchy_rel,
548 sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
549 l_level_num := l_level_num + 1;
550
551 if(p_matching_dim2 is not null AND length(trim(p_matching_dim2)) <> 0) then
552 select dimension_id into l_dim_id from fem_dimensions_vl where dimension_name = p_matching_dim2;
553
554 if(p_hierarchy2 is not null AND length(trim(p_hierarchy2)) <> 0) then
555 select object_id into l_hier_obj_id from fem_object_catalog_vl where object_name = p_hierarchy2 and object_type_code = 'HIERARCHY';
556 select object_definition_id into l_hier_obj_def_id from fem_object_definition_vl where display_name = p_hier2_ver and object_id = l_hier_obj_id;
557 select dimension_group_id into l_level_id from fem_dimension_grps_vl where dimension_group_name = p_level2;
558 select lookup_code into l_hierarchy_rel from fem_lookups where lookup_type = 'FEM_COND_HIER_RELATIONS' and meaning = p_hierarchy_rel2;
559 else
560 l_hier_obj_id := null;
561 l_hier_obj_def_id := null;
562 l_level_id := null;
563 l_hierarchy_rel :=null;
564 end if;
565
566 insert into fem_factor_table_dims(OBJECT_DEFINITION_ID,LEVEL_NUM,DIMENSION_ID,DIM_USAGE_CODE,FORCE_PERCENT_FLAG,HIER_OBJECT_ID,HIER_OBJ_DEF_ID,HIER_GROUP_ID,HIER_RELATION_CODE,
567 CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
568 values(G_OBJECT_DEFINITION_ID,l_level_num,l_dim_id,'MATCH',NULL,l_hier_obj_id,l_hier_obj_def_id,l_level_id,l_hierarchy_rel,
569 sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
570 l_level_num := l_level_num + 1;
571 end if;
572
573 if(p_matching_dim3 is not null AND length(trim(p_matching_dim3)) <> 0) then
574 select dimension_id into l_dim_id from fem_dimensions_vl where dimension_name = p_matching_dim3;
575
576 if(p_hierarchy3 is not null AND length(trim(p_hierarchy3)) <> 0) then
577 select object_id into l_hier_obj_id from fem_object_catalog_vl where object_name = p_hierarchy3 and object_type_code = 'HIERARCHY';
578 select object_definition_id into l_hier_obj_def_id from fem_object_definition_vl where display_name = p_hier3_ver and object_id = l_hier_obj_id;
579 select dimension_group_id into l_level_id from fem_dimension_grps_vl where dimension_group_name = p_level3;
580 select lookup_code into l_hierarchy_rel from fem_lookups where lookup_type = 'FEM_COND_HIER_RELATIONS' and meaning = p_hierarchy_rel3;
581 else
582 l_hier_obj_id := null;
583 l_hier_obj_def_id := null;
584 l_level_id := null;
585 l_hierarchy_rel :=null;
586 end if;
587
588 insert into fem_factor_table_dims(OBJECT_DEFINITION_ID,LEVEL_NUM,DIMENSION_ID,DIM_USAGE_CODE,FORCE_PERCENT_FLAG,HIER_OBJECT_ID,HIER_OBJ_DEF_ID,HIER_GROUP_ID,HIER_RELATION_CODE,
589 CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
590 values(G_OBJECT_DEFINITION_ID,l_level_num,l_dim_id,'MATCH',NULL,l_hier_obj_id,l_hier_obj_def_id,l_level_id,l_hierarchy_rel,
591 sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
592 l_level_num := l_level_num + 1;
593 end if;
594
595 end if; --- End for factor_type check
596
597 if(l_factor_type_code <> 'MATCH') then
598
599 select lookup_code into l_force_to_hundred from fem_lookups where lookup_type = 'FEM_WEBADI_YES_NO' and meaning = p_force_to_hundred;
600
601 if(p_distribution_dim is not null AND length(trim(p_distribution_dim)) <> 0) then
602 select dimension_id into l_dim_id from fem_dimensions_vl where dimension_name = p_distribution_dim;
603 l_hier_obj_id := null;
604 l_hier_obj_def_id := null;
605 l_level_id := null;
606 insert into fem_factor_table_dims(OBJECT_DEFINITION_ID,LEVEL_NUM,DIMENSION_ID,DIM_USAGE_CODE,FORCE_PERCENT_FLAG,HIER_OBJECT_ID,HIER_OBJ_DEF_ID,HIER_GROUP_ID,HIER_RELATION_CODE,
607 CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,OBJECT_VERSION_NUMBER)
608 values(G_OBJECT_DEFINITION_ID,l_level_num,l_dim_id,'DISTRIBUTE',l_force_to_hundred,l_hier_obj_id,l_hier_obj_def_id,l_level_id,null,
609 sysdate,l_user_id,l_user_id,sysdate,l_update_login,0);
610 end if;
611
612 end if; ---- End for factor_type check
613
614 END IF;
615
616 END POPULATE_FACTOR_TABLE_DIMS;
617
618 END FEM_WEBADI_FACT_TAB_UTILS_PVT;
[Home] [Help]