[Home] [Help]
PACKAGE BODY: APPS.BIS_PMV_SCHED_PARAMETERS_PVT
Source
1 package body BIS_PMV_SCHED_PARAMETERS_PVT as
2 /* $Header: BISVSCPB.pls 115.3 2003/11/21 05:41:19 nkishore noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile:~PROD:~PATH:~FILE
5
6
7 procedure saveParameters
8 (pRegionCode in varchar2,
9 pFunctionName in varchar2,
10 pPageId in Varchar2 default null,
11 pSessionId in Varchar2 default null,
12 pUserId in Varchar2 default null,
13
14 pResponsibilityId in Varchar2 default null,
15 pOrgParam in number default 0,
16 pParameter1 in varchar2 default null,
17 pParameterValue1 in varchar2 default null,
18 pParameter2 in varchar2 default null,
19 pParameterValue2 in varchar2 default null,
20 pParameter3 in varchar2 default null,
21 pParameterValue3 in varchar2 default null,
22 pParameter4 in varchar2 default null,
23 pParameterValue4 in varchar2 default null,
24 pParameter5 in varchar2 default null,
25 pParameterValue5 in varchar2 default null,
26 pParameter6 in varchar2 default null,
27 pParameterValue6 in varchar2 default null,
28 pParameter7 in varchar2 default null,
29 pParameterValue7 in varchar2 default null,
30 pParameter8 in varchar2 default null,
31 pParameterValue8 in varchar2 default null,
32 pParameter9 in varchar2 default null,
33 pParameterValue9 in varchar2 default null,
34 pParameter10 in varchar2 default null,
35 pParameterValue10 in varchar2 default null,
36 pParameter11 in varchar2 default null,
37 pParameterValue11 in varchar2 default null,
38 pParameter12 in varchar2 default null,
39 pParameterValue12 in varchar2 default null,
40 pParameter13 in varchar2 default null,
41 pParameterValue13 in varchar2 default null,
42 pParameter14 in varchar2 default null,
43 pParameterValue14 in varchar2 default null,
44 pParameter15 in varchar2 default null,
45 pParameterValue15 in varchar2 default null,
46 pTimeParameter in varchar2 default null,
47 pTimeFromParameter in varchar2 default null,
48 pTimeToParameter in varchar2 default null,
49 pViewByValue in varchar2 default null,
50 pAsOfDateValue in varchar2 default null,
51 pAsOfDateMode in varchar2 default null,
52 pSaveByIds in varchar2 default 'N',
53 pScheduleId in varchar2 default null,
54 x_return_status out NOCOPY VARCHAR2,
55 x_msg_count out NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2
57 )
58 is
59 l_parameter_rec BIS_PMV_PARAMETERS_PVT.PARAMETER_REC_TYPE;
60 l_parameter_Rec_tbl BIS_PMV_PARAMETERS_PVT.parameter_tbl_type;
61 l_user_Session_rec BIS_PMV_SESSION_PVT.session_rec_type;
62 l_time_parameter_rec BIS_PMV_PARAMETERS_PVT.TIME_PARAMETER_REC_TYPE;
63 l_count NUMBER;
64 l_asof_Date DATE;
65 l_Start_Date DATE;
66 l_End_date DATE;
67 l_time_level_id VARCHAR2(2000);
68 l_time_level_value VARCHAR2(2000);
69 l_time_comparison_type VARCHAR2(2000) := null;
70 l_time_comp_const VARCHAR2(200) := 'TIME_COMPARISON_TYPE';
71 l_canonical_date_format VARCHAR2(30) :='DD/MM/YYYY';
72 l_prev_asof_Date date;
73 l_current_report_start_date date;
74 l_prev_report_start_Date date;
75 lAsOfDateValue date;
76 cursor c_asof_date is
77 select period_date
78 from bis_user_attributes
79 where schedule_id=pScheduleId and
80 attribute_name = 'AS_OF_DATE';
81 begin
82 FND_MSG_PUB.INITIALIZE;
83 l_user_session_rec.function_name := pFunctionNAme;
84 l_user_session_rec.region_code := pRegionCode;
85 l_user_session_rec.page_id := pPageId;
86 l_user_session_rec.session_id := pSessionId;
87 l_user_Session_rec.user_id := pUserId;
88 l_user_Session_rec.schedule_id := pScheduleId;
89 l_user_session_rec.responsibility_id := pResponsibilityId;
90 l_parameter_rec.parameter_name := pParameter1;
91 l_parameter_rec.dimension := substr(pParameter1,1, instr(pParameter1,'+')-1);
92 l_parameter_rec.parameter_description := pParameterValue1;
93 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
94 l_time_comparison_type := l_parameter_rec.parameter_description;
95 end if;
96 l_parameter_rec.id_flag := pSaveByIds;
97 l_parameter_rec_tbl(1) := l_parameter_rec;
98 l_parameter_rec.parameter_name := pParameter2;
99 l_parameter_rec.dimension := substr(pParameter2,1, instr(pParameter2,'+')-1);
100 l_parameter_rec.parameter_description := pParameterValue2;
101 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
102 l_time_comparison_type := l_parameter_rec.parameter_description;
103 end if;
104 l_parameter_rec.id_flag := pSaveByIds;
105 l_parameter_rec_tbl(2) := l_parameter_rec;
106 l_parameter_rec.parameter_name := pParameter3;
107 l_parameter_rec.parameter_description := pParameterValue3;
108 l_parameter_rec.dimension := substr(pParameter3,1, instr(pParameter3,'+')-1);
109 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
110 l_time_comparison_type := l_parameter_rec.parameter_description;
111 end if;
112 l_parameter_rec.id_flag := pSaveByIds;
113 l_parameter_rec_tbl(3) := l_parameter_rec;
114 l_parameter_rec.parameter_name := pParameter4;
115 l_parameter_rec.parameter_description := pParameterValue4;
116 l_parameter_rec.dimension := substr(pParameter4,1, instr(pParameter4,'+')-1);
117 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
118 l_time_comparison_type := l_parameter_rec.parameter_description;
119 end if;
120 l_parameter_rec.id_flag := pSaveByIds;
121 l_parameter_rec_tbl(4) := l_parameter_rec;
122 l_parameter_rec.parameter_name := pParameter5;
123 l_parameter_rec.parameter_description := pParameterValue5;
124 l_parameter_rec.dimension := substr(pParameter5,1, instr(pParameter5,'+')-1);
125 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
126 l_time_comparison_type := l_parameter_rec.parameter_description;
127 end if;
128 l_parameter_rec.id_flag := pSaveByIds;
129 l_parameter_rec_tbl(5) := l_parameter_rec;
130 l_parameter_rec.parameter_name := pParameter6;
131 l_parameter_rec.parameter_description := pParameterValue6;
132 l_parameter_rec.dimension := substr(pParameter6,1, instr(pParameter6,'+')-1);
133 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
134 l_time_comparison_type := l_parameter_rec.parameter_description;
135 end if;
136 l_parameter_rec.id_flag := pSaveByIds;
137 l_parameter_rec_tbl(6) := l_parameter_rec;
138 l_parameter_rec.parameter_name := pParameter7;
139 l_parameter_rec.parameter_description := pParameterValue7;
140 l_parameter_rec.dimension := substr(pParameter7,1, instr(pParameter7,'+')-1);
141 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
142 l_time_comparison_type := l_parameter_rec.parameter_description;
143 end if;
144 l_parameter_rec.id_flag := pSaveByIds;
145 l_parameter_rec_tbl(7) := l_parameter_rec;
146 l_parameter_rec.parameter_name := pParameter8;
147 l_parameter_rec.parameter_description := pParameterValue8;
148 l_parameter_rec.dimension := substr(pParameter8,1, instr(pParameter8,'+')-1);
149 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
150 l_time_comparison_type := l_parameter_rec.parameter_description;
151 end if;
152 l_parameter_rec.id_flag := pSaveByIds;
153 l_parameter_rec_tbl(8) := l_parameter_rec;
154 l_parameter_rec.parameter_name := pParameter9;
155 l_parameter_rec.parameter_description := pParameterValue9;
156 l_parameter_rec.dimension := substr(pParameter9,1, instr(pParameter9,'+')-1);
157 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
158 l_time_comparison_type := l_parameter_rec.parameter_description;
159 end if;
160 l_parameter_rec.id_flag := pSaveByIds;
161 l_parameter_rec_tbl(9) := l_parameter_rec;
162 l_parameter_rec.parameter_name := pParameter10;
163 l_parameter_rec.parameter_description := pParameterValue10;
164 l_parameter_rec.dimension := substr(pParameter10,1, instr(pParameter10,'+')-1);
165 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
166 l_time_comparison_type := l_parameter_rec.parameter_description;
167 end if;
168 l_parameter_rec.id_flag := pSaveByIds;
169 l_parameter_rec_tbl(10) := l_parameter_rec;
170 l_parameter_rec.parameter_name := pParameter11;
171 l_parameter_rec.parameter_description := pParameterValue11;
172 l_parameter_rec.dimension := substr(pParameter11,1, instr(pParameter11,'+')-1);
173 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
174 l_time_comparison_type := l_parameter_rec.parameter_description;
175 end if;
176 l_parameter_rec.id_flag := pSaveByIds;
177 l_parameter_rec_tbl(11) := l_parameter_rec;
178 l_parameter_rec.parameter_name := pParameter12;
179 l_parameter_rec.parameter_description := pParameterValue12;
180 l_parameter_rec.dimension := substr(pParameter12,1, instr(pParameter12,'+')-1);
181 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
182 l_time_comparison_type := l_parameter_rec.parameter_description;
183 end if;
184 l_parameter_rec.id_flag := pSaveByIds;
185 l_parameter_rec_tbl(12) := l_parameter_rec;
186 l_parameter_rec.parameter_name := pParameter13;
187 l_parameter_rec.parameter_description := pParameterValue13;
188 l_parameter_rec.dimension := substr(pParameter13,1, instr(pParameter13,'+')-1);
189 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
190 l_time_comparison_type := l_parameter_rec.parameter_description;
191 end if;
192 l_parameter_rec.id_flag := pSaveByIds;
193 l_parameter_rec_tbl(13) := l_parameter_rec;
194 l_parameter_rec.parameter_name := pParameter14;
195 l_parameter_rec.parameter_description := pParameterValue14;
196 l_parameter_rec.dimension := substr(pParameter14,1, instr(pParameter14,'+')-1);
197 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
198 l_time_comparison_type := l_parameter_rec.parameter_description;
199 end if;
200 l_parameter_rec.id_flag := pSaveByIds;
201 l_parameter_rec_tbl(14) := l_parameter_rec;
202 l_parameter_rec.parameter_name := pParameter15;
203 l_parameter_rec.parameter_description := pParameterValue15;
204 l_parameter_rec.dimension := substr(pParameter15,1, instr(pParameter15,'+')-1);
205 if(l_parameter_Rec.dimension = l_time_comp_const) THEN
206 l_time_comparison_type := l_parameter_rec.parameter_description;
207 end if;
208 l_parameter_rec.id_flag := pSaveByIds;
209 l_parameter_rec_tbl(15) := l_parameter_rec;
210 if (pViewByValue is not null) then
211 l_parameter_rec.parameter_name := 'VIEW_BY';
212 l_parameter_rec.parameter_description := pViewByValue;
213 l_parameter_rec.hierarchy_flag := 'N';
214 l_count := l_count+1;
215 l_parameter_rec_tbl(l_count) := l_parameter_rec;
216 end if ;
217 create_Schedule_parameters(p_user_param_tbl => l_parameter_rec_tbl
218 ,p_user_session_rec => l_user_session_rec
219 ,x_return_Status => x_return_Status
220 ,x_msg_count => x_msg_count
221 ,x_msg_Data => x_msg_Data);
222
223 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
224 ROLLBACK;
225 RETURN;
226 END IF;
227
228
229 IF (pTimeFromParameter = 'DBC_TIME') then
230 IF (c_asof_date%ISOPEN) then
231 CLOSE c_asof_date;
232 END IF;
233 OPEN c_asof_date;
234 FETCH c_asof_date into l_asof_date;
235 close c_asof_Date;
236 IF l_asof_date is null then
237 IF (pAsOfDateValue is null) then
238 lAsOFDateValue := SYSDATE;
239 ELSE
240 lAsOFDateValue := to_date(pAsOfDateValue, 'DD/MM/YYYY');
241 END IF;
242 l_parameter_rec.parameter_name := 'AS_OF_DATE';
243 IF (pAsOfDateMode = 'NEXT') then
244 l_asof_date := lAsOfDateValue+1;
245 --As of Date Enh 3094234-- Change to Canonical dates
246 --l_parameter_rec.parameter_description := to_char(l_asof_date,'DD-MON-YYYY');
247 --l_parameter_rec.parameter_value := to_char(l_asof_date,'DD-MON-YYYY');
248 l_parameter_rec.parameter_description := to_char(l_asof_date,l_canonical_date_format);
249 l_parameter_rec.parameter_value := to_char(l_asof_date,l_canonical_date_format);
250 ELSIF (pAsOFDateMode = 'PREVIOUS') then
251 l_asof_date := lAsOfDateValue-1;
252 --l_parameter_rec.parameter_description := to_char(l_asof_Date,'DD-MON-YYYY');
253 --l_parameter_rec.parameter_value := to_char(l_asof_date,'DD-MON-YYYY');
254 l_parameter_rec.parameter_description := to_char(l_asof_Date,l_canonical_date_format);
255 l_parameter_rec.parameter_value := to_char(l_asof_date,l_canonical_date_format);
256 ELSIF (pAsOfDateMode = 'CURRENT' or pAsOfDateMode is null) then
257 l_asof_Date := lAsOfDateValue;
258 --l_parameter_Rec.parameter_description := to_char(l_asof_date,'DD-MON-YYYY');
259 --l_parameter_rec.parameter_value := to_char(l_asof_date,'DD-MON-YYYY');
260 l_parameter_Rec.parameter_description := to_char(l_asof_date,l_canonical_date_format);
261 l_parameter_rec.parameter_value := to_char(l_asof_date,l_canonical_date_format);
262 END IF;
263 l_parameter_Rec.default_flag := 'N';
264 l_parameter_rec.hierarchy_flag := 'N';
265 BIS_PMV_SCHED_PARAMETERS_PVT.CREATE_PARAMETER
266 (p_user_session_rec => l_user_session_rec
267 ,p_parameter_rec => l_parameter_rec
268 ,x_return_status => x_return_status
269 ,x_msg_count => x_msg_count
270 ,x_msg_data => x_msg_data);
271 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
272 ROLLBACK;
273 RETURN;
274 END IF;
275 END IF;
276 END IF;
277 IF (pTimePArameter IS NOT NULL) THEN
278 IF (pTimeFromParameter = 'DBC_TIME') then
279 --get the information for the current period
280 BIS_PMV_TIME_LEVELS_PVT.GET_TIME_LEVEL_INFO(p_dimensionlevel => pTimeParameter,
281 p_region_code => pregioncode,
282 p_Responsibility_id => presponsibilityid,
283 p_Asof_date => l_asof_date,
284 p_mode => 'GET_CURRENT',
285 x_time_level_id => l_time_level_id,
286 x_time_level_value => l_time_level_Value,
287 x_Start_date => l_start_date,
288 x_end_date => l_end_date,
289 x_return_Status => x_return_status,
290 x_msg_count => x_msg_count,
291 x_msg_data => x_msg_data
292 );
293 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
294 ROLLBACK;
295 RETURN;
296 END IF;
297 l_parameter_rec.dimension := substr(pTimeParameter,1, instr(pTimeParameter,'+')-1);
298 l_parameter_rec.default_flag := 'N';
299 l_parameter_rec.parameter_name := ptimeparameter || '_FROM';
300 l_parameter_rec.parameter_description := l_time_level_Value;
301 l_parameter_rec.period_date := l_start_Date;
302 l_parameter_Rec.parameter_Value := l_time_level_id;
303 --create the "from" record
304 BIS_PMV_SCHED_PARAMETERS_PVT.CREATE_PARAMETER (
305 p_user_session_rec => l_user_session_rec
306 ,p_parameter_rec => l_parameter_rec
307 ,x_return_status => x_return_status
308 ,x_msg_count => x_msg_count
309 ,x_msg_data => x_msg_data);
310 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
311 ROLLBACK;
312 RETURN;
313 END IF;
314 l_parameter_rec.default_flag := 'N';
315 l_parameter_rec.parameter_name := ptimeparameter || '_TO';
316 l_parameter_rec.parameter_description := l_time_level_Value;
317 l_parameter_rec.period_date := l_end_Date;
318 l_parameter_Rec.parameter_Value := l_time_level_id;
319
320 --create the "to" record
321 BIS_PMV_SCHED_PARAMETERS_PVT.CREATE_PARAMETER
322 (p_user_session_rec => l_user_session_rec
323 ,p_parameter_rec => l_parameter_rec
324 ,x_return_status => x_return_status
325 ,x_msg_count => x_msg_count
326 ,x_msg_data => x_msg_data);
327 --Store the Previous As Of Date as well
328 BIS_PMV_TIME_LEVELS_PVT.GET_PREVIOUS_ASOF_DATE
329 ( p_DimensionLevel => pTimePArameter
330 ,p_time_comparison_type => l_time_comparison_Type
331 ,p_asof_date => l_Asof_date
332 ,x_prev_asof_Date => l_prev_asof_Date
333 ,x_Return_status => x_return_Status
334 ,x_msg_count => x_msg_count
335 ,x_msg_data => x_msg_data
336 );
337 l_parameter_rec.dimension := null;
338 l_parameter_rec.default_flag := 'N';
339 l_parameter_Rec.parameter_name := 'BIS_P_ASOF_DATE';
340 --l_parameter_rec.parameter_description := to_char(l_prev_asof_date,'DD-MON-YYYY');
341 --l_parameter_rec.parameter_value := to_char(l_prev_asof_date,'DD-MON-YYYY');
342 l_parameter_rec.parameter_description := to_char(l_prev_asof_date,l_canonical_date_format);
343 l_parameter_rec.parameter_value := to_char(l_prev_asof_date,l_canonical_date_format);
344
345 BIS_PMV_SCHED_PARAMETERS_PVT.CREATE_PARAMETER (
346 p_user_session_rec => l_user_session_rec
347 ,p_parameter_rec => l_parameter_rec
348 ,x_return_status => x_return_status
349 ,x_msg_count => x_msg_count
350 ,x_msg_data => x_msg_data);
351 --Get the values for the previous report start date and current report start date and store them
352 BIS_PMV_TIME_LEVELS_PVT.GET_REPORT_START_DATE
353 (p_time_comparison_type => l_time_comparison_type
354 ,p_asof_date => l_asof_date
355 ,p_time_level => pTimeParameter
356 ,x_report_start_date => l_current_report_start_date
357 ,x_return_status => x_return_status
358 ,x_msg_count => x_msg_count
359 ,x_msg_data => x_msg_data
360 );
361 l_parameter_rec.dimension := null;
362 l_parameter_rec.default_flag := 'N';
363 l_parameter_Rec.parameter_name := 'BIS_CUR_REPORT_START_DATE';
364 --l_parameter_rec.parameter_description := to_char(l_current_report_start_date,'DD-MON-YYYY');
365 --l_parameter_rec.parameter_value := to_char(l_current_report_start_date,'DD-MON-YYYY');
366 l_parameter_rec.parameter_description := to_char(l_current_report_start_date,l_canonical_date_format);
367 l_parameter_rec.parameter_value := to_char(l_current_report_start_date,l_canonical_date_format);
368
369 BIS_PMV_SCHED_PARAMETERS_PVT.CREATE_PARAMETER (
370 p_user_session_rec => l_user_session_rec
371 ,p_parameter_rec => l_parameter_rec
372 ,x_return_status => x_return_status
373 ,x_msg_count => x_msg_count
374 ,x_msg_data => x_msg_data);
375 BIS_PMV_TIME_LEVELS_PVT.GET_REPORT_START_DATE
376 (p_time_comparison_type => l_time_comparison_type
377 ,p_asof_date => l_prev_asof_date
378 ,p_time_level => pTimeParameter
379 ,x_report_start_date => l_prev_report_start_date
380 ,x_return_status => x_return_status
381 ,x_msg_count => x_msg_count
382 ,x_msg_data => x_msg_data
383 );
384 l_parameter_rec.dimension := null;
385 l_parameter_rec.default_flag := 'N';
386 l_parameter_Rec.parameter_name := 'BIS_PREV_REPORT_START_DATE';
387 --l_parameter_rec.parameter_description := to_char(l_prev_report_start_date,'DD-MON-YYYY');
388 --l_parameter_rec.parameter_value := to_char(l_prev_report_start_date,'DD-MON-YYYY');
389 l_parameter_rec.parameter_description := to_char(l_prev_report_start_date,l_canonical_date_format);
390 l_parameter_rec.parameter_value := to_char(l_prev_report_start_date,l_canonical_date_format);
391
392 BIS_PMV_SCHED_PARAMETERS_PVT.CREATE_PARAMETER (
393 p_user_session_rec => l_user_session_rec
394 ,p_parameter_rec => l_parameter_rec
395 ,x_return_status => x_return_status
396 ,x_msg_count => x_msg_count
397 ,x_msg_data => x_msg_data);
398
399
400 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
401 ROLLBACK;
402 RETURN;
403 END IF;
404 end if;
405 end if;
406 COMMIT;
407 EXCEPTION
408 WHEN FND_API.G_EXC_ERROR THEN
409 ROLLBACK;
410 x_return_status := FND_API.G_RET_STS_ERROR;
411 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
412 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
413 ROLLBACK;
414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
416 WHEN OTHERS THEN
417 ROLLBACK;
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
420
421 end;
422 PROCEDURE CREATE_SCHEDULE_PARAMETERS
423 (p_user_param_tbl IN BIS_PMV_PARAMETERS_PVT.parameter_tbl_type
424 ,p_user_session_rec IN BIS_PMV_SESSION_PVT.session_rec_type
425 ,x_return_status OUT NOCOPY VARCHAR2
426 ,x_msg_count OUT NOCOPY NUMBER
427 ,x_msg_data OUT NOCOPY VARCHAR2
428 )
429 IS
430 l_user_param_tbl BIS_PMV_PARAMETERS_PVT.parameter_tbl_type;
431 BEGIN
432 IF p_user_param_tbl.COUNT > 0 THEN
433 l_useR_param_Tbl := p_user_param_Tbl;
434 FOR i in 1..p_user_param_tbl.COUNT LOOP
435 IF (l_user_param_tbl(i).parameter_name IS NOT NULL ) THEN
436 BIS_PMV_SCHED_PARAMETERS_PVT.VALIDATE_AND_SAVE
437 (p_user_session_rec => p_user_session_rec
438 ,p_parameter_rec => l_user_param_tbl(i)
439 ,x_return_status => x_return_status
440 ,x_msg_count => x_msg_count
441 ,x_msg_Data => x_msg_data);
442 END IF;
443 END LOOP;
444 END IF;
445 EXCEPTION
446 WHEN FND_API.G_EXC_ERROR THEN
447 x_return_status := FND_API.G_RET_STS_ERROR;
448 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
451 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
452 WHEN OTHERS THEN
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
454 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
455
456 END;
457 PROCEDURE VALIDATE_AND_SAVE
458 (p_user_session_rec IN BIS_PMV_SESSION_PVT.session_rec_type
459 ,p_parameter_rec IN OUT NOCOPY BIS_PMV_PARAMETERS_PVT.parameter_rec_type
460 ,x_return_status OUT NOCOPY VARCHAR2
461 ,x_msg_count OUT NOCOPY NUMBER
462 ,x_msg_data OUT NOCOPY VARCHAR2
463 )
464 IS
465 l_valid varchar2(2);
466 BEGIN
467 if (substr(p_parameter_rec.parameter_name,1,length('TIME_COMPARISON_TYPE')) = 'TIME_COMPARISON_TYPE') then
468 p_parameter_rec.parameter_name := p_parameter_rec.parameter_description;
469 p_parameter_rec.parameter_value := p_parameter_Rec.parameter_description;
470 l_valid := 'Y';
471 elsif instr(p_parameter_rec.parameter_description, '^~]*') > 0 then
472 l_valid := 'Y';
473 --BugFix#2577374 -ansingh
474 elsif (p_parameter_rec.parameter_value='-1') then
475 p_parameter_rec.parameter_description := FND_MESSAGE.get_string('BIS','BIS_UNASSIGNED'
476 );
477 l_valid := 'Y';
478 elsif (upper(p_parameter_rec.parameter_description) = upper(FND_MESSAGE.get_string('BIS','BIS_UNASSIGNED')))
479 then
480 p_parameter_rec.parameter_value := '-1';
481 l_valid := 'Y';
482
483 else
484 BIS_PMV_PARAMETERS_PVT.VALIDATE_NONTIME_PARAMETER (p_user_session_rec => p_user_session_rec
485 ,p_parameter_rec => p_parameter_rec
486 ,x_valid => l_valid
487 ,x_return_status => x_return_status
488 ,x_msg_count => x_msg_count
489 ,x_msg_data => x_msg_data);
490 end if;
491 IF l_valid = 'Y' THEN
492 BIS_PMV_SCHED_PARAMETERS_PVT.CREATE_PARAMETER (p_user_session_rec => p_user_session_rec
493 ,p_parameter_rec => p_parameter_rec
494 ,x_return_status => x_return_status
495 ,x_msg_count => x_msg_count
496 ,x_msg_data => x_msg_data);
497 END IF;
498
499 EXCEPTION
500 WHEN FND_API.G_EXC_ERROR THEN
501 x_return_status := FND_API.G_RET_STS_ERROR;
502 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
505 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
506 WHEN OTHERS THEN
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
509
510 END;
511 PROCEDURE CREATE_PARAMETER
512 (p_user_session_rec IN BIS_PMV_SESSION_PVT.session_rec_type
513 ,p_parameter_rec IN BIS_PMV_PARAMETERS_PVT.parameter_rec_type
514 ,x_return_status OUT NOCOPY VARCHAR2
515 ,x_msg_count OUT NOCOPY NUMBER
516 ,x_msg_Data OUT NOCOPY VARCHAR2
517 )
518 IS
519 l_parameter_name VARCHAR2(32000) := p_parameter_rec.parameter_name;
520 l_parameter_value VARCHAR2(32000) := p_parameter_rec.parameter_value;
521 l_parameter_description VARCHAR2(32000) := p_parameter_rec.parameter_description;
522 l_index number := 0;
523 l_dimension VARCHAR2(80) := p_parameter_rec.dimension;
524 BEGIN
525 if l_dimension is null then
526 l_index := instr(l_parameter_name,'+');
527 IF l_index > 0 THEN
528 l_dimension := substr(l_parameter_name,1,l_index-1);
529 END IF;
530 end if;
531 INSERT INTO BIS_USER_ATTRIBUTES (user_id, schedule_id, attribute_name, function_name,
532 session_id, session_value, session_description,
533 period_date, dimension, operator,
534 creation_date, created_by,
535 last_update_Date, last_updated_by)
536 VALUES (p_user_session_rec.user_id, p_user_session_rec.schedule_id,
537 l_parameter_name,
538 p_user_session_rec.function_name, p_user_session_rec.session_id,
539 l_parameter_value, l_parameter_description,
540 p_parameter_rec.period_date, l_dimension,
541 p_parameter_rec.operator,
542 sysdate, -1, sysdate, -1);
543 EXCEPTION
544 WHEN FND_API.G_EXC_ERROR THEN
545 x_return_status := FND_API.G_RET_STS_ERROR;
546 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
547 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
550 WHEN OTHERS THEN
551 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data);
553
554 END;
555 end;