[Home] [Help]
PACKAGE BODY: APPS.CSI_COUNTER_PVT
Source
1 PACKAGE BODY CSI_COUNTER_PVT AS
2 /* $Header: csivctib.pls 120.27.12010000.2 2008/10/31 21:01:22 rsinn ship $ */
3
4 -- --------------------------------------------------------
5 -- Define global variables
6 -- --------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_COUNTER_PVT';
9 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csivctib.pls';
10
11 --|---------------------------------------------------
12 --| Object : ExitWithErrMsg
13 --| Scope : Private to package
14 --| Description : Common procedure to raise error
15 --| Parameters : p_msg_name - message name in CS schema
16 --| p_token?_name/val - Token for message and its value
17 --|---------------------------------------------------
18 Procedure ExitWithErrMsg
19 (
20 p_msg_name in varchar2,
21 p_token1_name in varchar2 := null,
22 p_token1_val in varchar2 := null,
23 p_token2_name in varchar2 := null,
24 p_token2_val in varchar2 := null,
25 p_token3_name in varchar2 := null,
26 p_token3_val in varchar2 := null,
27 p_token4_name in varchar2 := null,
28 p_token4_val in varchar2 := null
29 ) is
30 begin
31 FND_MESSAGE.SET_NAME('CS',p_msg_name);
32 if p_token1_name is not null then
33 FND_MESSAGE.SET_TOKEN(p_token1_name, p_token1_val);
34 end if;
35 if p_token2_name is not null then
36 FND_MESSAGE.SET_TOKEN(p_token2_name, p_token2_val);
37 end if;
38 if p_token3_name is not null then
39 FND_MESSAGE.SET_TOKEN(p_token3_name, p_token3_val);
40 end if;
41 if p_token4_name is not null then
42 FND_MESSAGE.SET_TOKEN(p_token4_name, p_token4_val);
43 end if;
44 --
45 FND_MSG_PUB.Add;
46 RAISE FND_API.G_EXC_ERROR;
47 end ExitWithErrMsg;
48
49 --|---------------------------------------------------
50 --| procedure name: validate_uom
51 --| description : procedure used to
52 --| validate unit of measure
53 --|---------------------------------------------------
54
55 PROCEDURE validate_uom(p_uom_code varchar2) is
56 l_dummy varchar2(1);
57 BEGIN
58 select 'x'
59 into l_dummy
60 from mtl_units_of_measure
61 where uom_code = p_uom_code;
62 exception when no_data_found then
63 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_ALL_INVALID_UOM_CODE');
64 END;
65
66 --|---------------------------------------------------
67 --| function name: counter_name_exists
68 --| description : Function used to check
69 --| duplicate counter name
70 --|---------------------------------------------------
71 FUNCTION counter_name_exists(p_name VARCHAR2, p_ctr_id NUMBER) RETURN BOOLEAN IS
72 l_return_value BOOLEAN := TRUE;
73 l_dummy VARCHAR2(1);
74 BEGIN
75 SELECT 'x'
76 INTO l_dummy
77 FROM csi_counters_vl
78 WHERE name = p_name
79 AND counter_id <> nvl(p_ctr_id,-1);
80
81 -- There already exists a counter with the same name.Return true
82 RETURN (l_return_value);
83 EXCEPTION WHEN NO_DATA_FOUND THEN
84 --counter name doesnot exist.
85 l_return_value := FALSE;
86 RETURN (l_return_value);
87 END;
88
89 --|---------------------------------------------------
90 --| function name: VALIDATE_FORMULA
91 --| description : Function used to validate
92 --| and parse formula text
93 --|---------------------------------------------------
94 FUNCTION VALIDATE_FORMULA(p_formula_text VARCHAR2) RETURN BOOLEAN IS
95 iSQLCur number;
96 sSQL varchar2(2090);
97 BEGIN
98 sSQL := 'select ' || P_FORMULA_TEXT || ' from dual';
99 iSQLCur := DBMS_SQL.open_cursor;
100 dbms_sql.parse(iSQLCur,sSQL,2);
101 dbms_sql.close_cursor(iSQLCur);
102 return TRUE;
103 EXCEPTION
104 WHEN OTHERS THEN
105 return FALSE;
106 END;
107
108
109 --|---------------------------------------------------
110 --| procedure name: validate_unique_ctr
111 --| description : procedure used to
112 --| validate unique counter name
113 --|---------------------------------------------------
114 PROCEDURE Validate_Unique_ctr(p_name VARCHAR2, p_ctr_id NUMBER) IS
115 l_dummy VARCHAR2(1);
116 BEGIN
117 SELECT 'x'
118 INTO l_dummy
119 FROM csi_counters_vl
120 WHERE name = p_name
121 AND counter_id <> nvl(p_ctr_id,-1);
122
123 -- There already exists a counter with the same name. Raise error
124 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_DUP_NAME','CTR_NAME',p_name);
125 EXCEPTION WHEN NO_DATA_FOUND THEN
126 NULL;
127 END;
128
129 --|---------------------------------------------------
130 --| procedure name: validate_unique_ctrprop
131 --| description : procedure used to
132 --| validate unique counter property name
133 --|---------------------------------------------------
134 PROCEDURE Validate_Unique_ctrprop
135 (
136 p_name VARCHAR2,
137 p_ctr_id NUMBER,
138 p_ctr_prop_id NUMBER
139 )
140 IS
141 l_dummy VARCHAR2(1);
142 BEGIN
143 SELECT 'x'
144 INTO l_dummy
145 FROM csi_counter_properties_vl
146 WHERE name = p_name
147 AND counter_id = p_ctr_id
148 AND counter_property_id <> nvl(p_ctr_prop_id,-1);
149
150 -- There already exists a counter property within the counter with
151 -- the same name. Raise error
152 ExitWithErrMsg('CSI_API_CTR_PROP_DUP_NAME','CTR_PROP_NAME',p_name,'CTR_NAME',p_ctr_id);
153
154 EXCEPTION WHEN NO_DATA_FOUND THEN
155 NULL;
156 END ;
157
158 --|---------------------------------------------------
159 --| procedure name: validate_counter
160 --| description : procedure used to
161 --| validate counter
162 --|---------------------------------------------------
163
164 PROCEDURE Validate_Counter
165 (
166 p_group_id NUMBER
167 ,p_name VARCHAR2
168 ,p_counter_type VARCHAR2
169 ,p_uom_code VARCHAR2
170 ,p_usage_item_id NUMBER
171 ,p_reading_type VARCHAR2
172 ,p_direction VARCHAR2
173 ,p_estimation_id NUMBER
174 ,p_derive_function VARCHAR2
175 ,p_formula_text VARCHAR2
176 ,p_derive_counter_id NUMBER
177 ,p_filter_type VARCHAR2
178 ,p_filter_reading_count NUMBER
179 ,p_filter_time_uom VARCHAR2
180 ,p_automatic_rollover VARCHAR2
181 ,p_rollover_last_reading NUMBER
182 ,p_rollover_first_reading NUMBER
183 ,p_tolerance_plus NUMBER
184 ,p_tolerance_minus NUMBER
185 ,p_used_in_scheduling VARCHAR2
186 ,p_initial_reading NUMBER
187 ,p_default_usage_rate NUMBER
188 ,p_use_past_reading NUMBER
189 ,p_counter_id NUMBER
190 ,p_start_date_active DATE
191 ,p_end_date_active DATE
192 )
193 IS
194 l_dummy varchar2(1);
195 l_time_uom varchar2(1);
196 l_inv_valdn_org_id NUMBER := fnd_profile.value('CS_INV_VALIDATION_ORG');
197 BEGIN
198
199 -- validate counter name is not null
200 if p_name is null then
201 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_REQ_PARM_CTR_NAME');
202 end if;
203
204 -- validate uom code
205 validate_uom(p_uom_code);
206
207 --validate Usage item id
208 if p_usage_item_id is not null then
209 begin
210 select 'x'
211 into l_dummy
212 from mtl_system_items
213 where inventory_item_id = p_usage_item_id
214 and organization_id = l_inv_valdn_org_id
215 --and organization_id = cs_std.get_item_valdn_orgzn_id
216 and usage_item_flag = 'Y';
217 exception when no_data_found then
218 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_USAGE_ITEM');
219 end;
220
221 if p_group_id is null then
222 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_REQ_PARM_GRP_NAME');
223 end if;
224 end if;
225
226 -- validate counter group id
227 if p_group_id is not null then
228 begin
229 select 'x'
230 into l_dummy
231 from csi_counter_groups_v
232 where counter_group_id = p_group_id;
233 exception when no_data_found then
234 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_GRP_INVALID');
235 end;
236 end if;
237
238 --validate estimation id not exist if direction is Bi-Direction
239 if p_estimation_id is not null and p_direction not in ('A','D') then
240 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_EST_NO_DIRECTION');
241 end if;
242
243 -- Validate that automatic rollover should not exist if direction is Bi-Direction
244 if nvl(p_automatic_rollover,'N') = 'Y' and p_direction not in ('A','D') then
245 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_ARO_NO_DIRECTION');
246 end if;
247
248 --validate tolerance plus and tolerance minus for negative values
249 if p_tolerance_plus < 0 or p_tolerance_minus <0 then
250 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_TOLERANCE');
251 end if;
252
253 --validate counter type
254 if p_counter_type not in ('REGULAR','FORMULA') then
255 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INVALID_CTR_TYPE');
256 end if;
257
258 --validate counter type parameters
259 if p_counter_type = 'REGULAR' then
260 begin
261 select 'Y'
262 into l_time_uom
263 from mtl_units_of_measure
264 where uom_code = p_uom_code
265 and upper(UOM_CLASS)='TIME';
266 exception
267 when no_data_found then
268 l_time_uom := 'N';
269 end;
270
271 if l_time_uom = 'N' then
272 if p_reading_type not in (1,2) then
273 --ExitWithErrMsg('CSI_API_CTR_REQ_PARM_CTR_TYPE','PARAM','p_reading_type','CTR_TYPE',p_counter_type);
274 csi_ctr_gen_utility_pvt.ExitWithErrMsg
275 ( p_msg_name => 'CSI_API_CTR_REQ_PARM_CTR_TYPE',
276 p_token1_name => 'PARAM',
277 p_token1_val => 'p_reading_type',
278 p_token2_name => 'CTR_TYPE',
279 p_token2_val => p_counter_type
280 );
281 end if;
282 if p_derive_function is not null or
283 p_formula_text is not null or
284 p_derive_counter_id is not null or
285 p_filter_type is not null or
286 p_filter_reading_count is not null or
287 p_filter_time_uom is not null then
288 --ExitWithErrMsg('CSI_API_CTR_INV_PARM_CTR_TYPE','CTR_TYPE',p_counter_type);
289 csi_ctr_gen_utility_pvt.ExitWithErrMsg
290 ( p_msg_name => 'CSI_API_CTR_INV_PARM_CTR_TYPE',
291 p_token1_name => 'CTR_TYPE',
292 p_token1_val => p_counter_type
293 );
294 end if;
295
296 --validate required parameter values exist for automatic rollover
297 if nvl(p_automatic_rollover,'N') = 'Y' then
298 if p_rollover_last_reading is null then
299 --ExitWithErrMsg('CSI_API_CTR_REQ_PARM_ROLLOVER','PARAM','p_rollover_last_reading');
300 csi_ctr_gen_utility_pvt.ExitWithErrMsg
301 ( p_msg_name => 'CSI_API_CTR_REQ_PARM',
302 p_token1_name => 'PARAM',
303 p_token1_val => 'p_rollover_last_reading'
304 );
305 elsif p_rollover_first_reading is null then
306 --ExitWithErrMsg('CSI_API_CTR_REQ_PARM_ROLLOVER','PARAM','p_rollover_first_reading');
307 csi_ctr_gen_utility_pvt.ExitWithErrMsg
308 ( p_msg_name => 'CSI_API_CTR_REQ_PARM',
309 p_token1_name => 'PARAM',
310 p_token1_val => 'p_rollover_first_reading'
311 );
312 end if;
313 --Rollover from must be greater than Rollover to for direction Ascending
314 if p_direction = 'A' and p_rollover_last_reading <= p_rollover_first_reading then
315 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_RO_DATA');
316 end if;
317 --Rollover from must be less than Rollover to for direction Descending
318 if p_direction = 'D' and p_rollover_last_reading >= p_rollover_first_reading then
319 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_RO_DATA');
320 end if;
321 end if;
322 -- Rollover from and Rollover to field should not have values if automatic rollover is not checked.
323 if nvl(p_automatic_rollover,'N') = 'N' and (p_rollover_last_reading is NOT NULL or p_rollover_first_reading IS NOT NULL) then
324 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_RO_ARO');
325 end if;
326
327 --validate if required parameter values exist for used in scheduling
328 if nvl(p_used_in_scheduling,'N') = 'Y' then
329 if p_initial_reading is null then
330 --ExitWithErrMsg('CSI_API_CTR_REQ_PARM_USE_SCHD','PARAM','p_initial_reading');
331 csi_ctr_gen_utility_pvt.ExitWithErrMsg
332 ( p_msg_name => 'CSI_API_CTR_REQ_PARM',
333 p_token1_name => 'PARAM',
334 p_token1_val => 'p_initial_reading'
335 );
336 elsif p_default_usage_rate is null then
337 --ExitWithErrMsg('CSI_API_CTR_REQ_PARM_USE_SCHD','PARAM','p_default_usage_rate');
338 csi_ctr_gen_utility_pvt.ExitWithErrMsg
339 ( p_msg_name => 'CSI_API_CTR_REQ_PARM',
340 p_token1_name => 'PARAM',
341 p_token1_val => 'p_default_usage_rate'
342 );
343 elsif p_use_past_reading is null then
344 --ExitWithErrMsg('CSI_API_CTR_REQ_PARM_USE_SCHD','PARAM','p_use_past_reading');
345 csi_ctr_gen_utility_pvt.ExitWithErrMsg
346 ( p_msg_name => 'CSI_API_CTR_REQ_PARM',
347 p_token1_name => 'PARAM',
348 p_token1_val => 'p_use_past_reading'
349 );
350 end if;
351 end if;
352 else -- if type is a time counter
353 if p_derive_function is not null or
354 p_formula_text is not null or
355 p_derive_counter_id is not null or
356 p_filter_type is not null or
357 p_filter_reading_count is not null or
358 p_filter_time_uom is not null or
359 --p_reading_type is not null or
360 nvl(p_automatic_rollover,'N') = 'Y' or
361 p_rollover_last_reading is not null or
362 p_rollover_first_reading is not null or
363 --nvl(p_used_in_scheduling,'N') = 'Y' or
364 --p_initial_reading is not null or
365 --p_default_usage_rate is not null or
366 --p_use_past_reading is not null or
367 p_tolerance_plus is not null or
368 p_tolerance_minus is not null or
369 p_estimation_id is not null then
370 --ExitWithErrMsg('CSI_API_CTR_INV_PARM_CTR_TYPE','CTR_TYPE',p_counter_type);
371 csi_ctr_gen_utility_pvt.ExitWithErrMsg
372 ( p_msg_name => 'CSI_API_CTR_INV_PARM_CTR_TYPE',
373 p_token1_name => 'CTR_TYPE',
374 p_token1_val => p_counter_type
375 );
376 end if;
377 end if; --l_time_uom
378 elsif p_counter_type = 'FORMULA' then
379 if p_derive_function is null then
380 if p_formula_text is null then
381 csi_ctr_gen_utility_pvt.ExitWithErrMsg
382 ( p_msg_name => 'CSI_API_CTR_REQ_PARM',
383 p_token1_name => 'PARAM',
384 p_token1_val => 'p_formula_text'
385 );
386 end if;
387 IF NOT VALIDATE_FORMULA(p_formula_text) THEN
388 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_FORMULA_TEXT');
389 END IF;
390 if p_derive_counter_id is not null or
391 p_filter_type is not null or
392 p_filter_reading_count is not null or
393 p_filter_time_uom is not null or
394 nvl(p_automatic_rollover,'N') = 'Y' or
395 p_rollover_last_reading is not null or
396 p_rollover_first_reading is not null or
397 p_initial_reading is not null or
398 p_tolerance_plus is not null or
399 p_tolerance_minus is not null then
400 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_PARM_CTR_TYPE','CTR_TYPE',p_counter_type);
401 end if;
402 elsif p_derive_function='AVERAGE' and p_filter_type='COUNT' then
403 if p_filter_reading_count < 0 then
404 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_REQ_PARM','PARAM','p_filter_type');
405 end if;
406 if p_formula_text is not null or
407 p_filter_time_uom is not null or
408 nvl(p_automatic_rollover,'N') = 'Y' or
409 p_rollover_last_reading is not null or
410 p_rollover_first_reading is not null or
411 -- nvl(p_used_in_scheduling,'N') = 'Y' or
412 p_initial_reading is not null or
413 -- p_default_usage_rate is not null or
414 -- p_use_past_reading is not null or
415 p_estimation_id is not null or
416 p_tolerance_plus is not null or
417 p_tolerance_minus is not null then
418 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_PARM_CTR_TYPE','CTR_TYPE',p_counter_type);
419 end if;
420 elsif p_derive_function='AVERAGE' and p_filter_type='TIME' then
421 if p_filter_time_uom is null then
422 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_REQ_PARM','PARAM','p_filter_type');
423 end if;
424 if p_formula_text is not null or
425 p_filter_reading_count is not null or
426 nvl(p_automatic_rollover,'N') = 'Y' or
427 p_rollover_last_reading is not null or
428 p_rollover_first_reading is not null or
429 -- nvl(p_used_in_scheduling,'N') = 'Y' or
430 p_initial_reading is not null or
431 -- p_default_usage_rate is not null or
432 -- p_use_past_reading is not null or
433 p_estimation_id is not null or
434 p_tolerance_plus is not null or
435 p_tolerance_minus is not null then
436 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_PARM_CTR_TYPE','CTR_TYPE',p_counter_type);
437 end if;
438 elsif p_derive_function in ('SUM','COUNT') then
439 -- if p_derive_counter_id is null then
440 -- csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_REQ_PARM','PARAM','p_derive_counter_id');
441 -- end if;
442 if p_formula_text is not null or
443 p_filter_time_uom is not null or
444 p_filter_reading_count is not null or
445 nvl(p_automatic_rollover,'N') = 'Y' or
446 p_rollover_last_reading is not null or
447 p_rollover_first_reading is not null or
448 -- nvl(p_used_in_scheduling,'N') = 'Y' or
449 p_initial_reading is not null or
450 -- p_default_usage_rate is not null or
451 -- p_use_past_reading is not null or
452 p_estimation_id is not null or
453 p_tolerance_plus is not null or
454 p_tolerance_minus is not null then
455 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_PARM_CTR_TYPE','CTR_TYPE',p_counter_type);
456 end if;
457 end if;
458 end if; -- p_counter_type
459
460 END validate_counter;
461
462 --|---------------------------------------------------
463 --| procedure name: validate_data_type
464 --| description : procedure used to
465 --| validate property datatype
466 --|---------------------------------------------------
467
468 PROCEDURE Validate_Data_Type
469 (
470 p_property_data_type IN VARCHAR2,
471 p_default_value IN VARCHAR2,
472 p_minimum_value IN VARCHAR2,
473 p_maximum_value IN VARCHAR2
474 )
475 IS
476 l_char VARCHAR2(240);
477 l_num NUMBER;
478 l_date DATE;
479 l_default_value VARCHAR2(240);
480 l_minimum_value VARCHAR2(240);
481 l_maximum_value VARCHAR2(240);
482
483 BEGIN
484 IF p_default_value = FND_API.G_MISS_CHAR THEN
485 l_default_value := NULL;
486 END IF;
487
488 IF p_maximum_value = FND_API.G_MISS_CHAR THEN
489 l_maximum_value := NULL;
490 END IF;
491
492 IF p_minimum_value = FND_API.G_MISS_CHAR THEN
493 l_minimum_value := NULL;
494 END IF;
495
496 IF p_property_data_type = 'CHAR' THEN
497 NULL;
498 -- any value is okay becoz even if the values are numbers or dates
499 -- they are going to be varchar2
500 ELSIF p_property_data_type = 'NUMBER' THEN
501 l_num := l_default_value;
502 l_num := l_minimum_value;
503 l_num := l_maximum_value;
504 ELSIF p_property_data_type = 'DATE' THEN
505 l_date := l_default_value;
506 l_date := l_minimum_value;
507 l_date := l_maximum_value;
508 ELSE
509 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_PROP_DATA_TYPE');
510 END IF;
511
512 EXCEPTION WHEN OTHERS THEN
513 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_VAL_DATATYPE','DATA_TYPE',p_property_data_type);
514
515 END Validate_Data_Type;
516
517 --|---------------------------------------------------
518 --| procedure name: create_counter
519 --| description : procedure used to
520 --| create counter instance
521 --|---------------------------------------------------
522
523 PROCEDURE create_counter
524 (
525 p_api_version IN NUMBER
526 ,p_init_msg_list IN VARCHAR2
527 ,p_commit IN VARCHAR2
528 ,p_validation_level IN VARCHAR2
529 ,p_counter_instance_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.Counter_instance_rec
530 ,x_return_status OUT NOCOPY VARCHAR2
531 ,x_msg_count OUT NOCOPY NUMBER
532 ,x_msg_data OUT NOCOPY VARCHAR2
533 ,x_ctr_id OUT NOCOPY NUMBER
534 )
535 IS
536 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_COUNTER';
537 l_api_version CONSTANT NUMBER := 1.0;
538 -- l_debug_level NUMBER;
539 l_flag VARCHAR2(1) := 'N';
540 l_msg_data VARCHAR2(2000);
541 l_msg_index NUMBER;
542 l_msg_count NUMBER;
543 l_count NUMBER;
544 l_return_message VARCHAR2(100);
545
546 l_counter_id NUMBER;
547 l_group_id NUMBER;
548 l_name VARCHAR2(50);
549 l_description VARCHAR2(240);
550 l_counter_type VARCHAR2(30);
551 l_uom_code VARCHAR2(3);
552 l_usage_item_id NUMBER;
553 l_reading_type NUMBER;
554 l_direction VARCHAR2(1);
555 l_estimation_id NUMBER;
556 l_derive_function VARCHAR2(30);
557 l_formula_text VARCHAR2(1996);
558 l_derive_counter_id NUMBER;
559 l_filter_type VARCHAR2(30);
560 l_filter_reading_count NUMBER;
561 l_filter_time_uom VARCHAR2(30);
562 l_automatic_rollover VARCHAR2(1);
563 l_rollover_last_reading NUMBER;
564 l_rollover_first_reading NUMBER;
565 l_tolerance_plus NUMBER;
566 l_tolerance_minus NUMBER;
567 l_formula_incomplete_flag VARCHAR2(1);
568 l_used_in_scheduling VARCHAR2(1);
569 l_initial_reading NUMBER;
570 l_default_usage_rate NUMBER;
571 l_use_past_reading NUMBER;
572 l_start_date_active DATE;
573 l_end_date_active DATE;
574 l_initial_reading_date DATE;
575 l_defaulted_group_id NUMBER;
576 l_created_from_counter_tmpl_id NUMBER;
577 l_attribute1 VARCHAR2(150);
578 l_attribute2 VARCHAR2(150);
579 l_attribute3 VARCHAR2(150);
580 l_attribute4 VARCHAR2(150);
581 l_attribute5 VARCHAR2(150);
582 l_attribute6 VARCHAR2(150);
583 l_attribute7 VARCHAR2(150);
584 l_attribute8 VARCHAR2(150);
585 l_attribute9 VARCHAR2(150);
586 l_attribute10 VARCHAR2(150);
587 l_attribute11 VARCHAR2(150);
588 l_attribute12 VARCHAR2(150);
589 l_attribute13 VARCHAR2(150);
590 l_attribute14 VARCHAR2(150);
591 l_attribute15 VARCHAR2(150);
592 l_attribute16 VARCHAR2(150);
593 l_attribute17 VARCHAR2(150);
594 l_attribute18 VARCHAR2(150);
595 l_attribute19 VARCHAR2(150);
596 l_attribute20 VARCHAR2(150);
597 l_attribute21 VARCHAR2(150);
598 l_attribute22 VARCHAR2(150);
599 l_attribute23 VARCHAR2(150);
600 l_attribute24 VARCHAR2(150);
601 l_attribute25 VARCHAR2(150);
602 l_attribute26 VARCHAR2(150);
603 l_attribute27 VARCHAR2(150);
604 l_attribute28 VARCHAR2(150);
605 l_attribute29 VARCHAR2(150);
606 l_attribute30 VARCHAR2(150);
607 l_attribute_category VARCHAR2(30);
608 l_step_value NUMBER;
609 l_time_based_manual_entry VARCHAR2(1);
610 l_eam_required_flag VARCHAR2(1);
611
612 l_dummy VARCHAR2(1);
613 l_counter_groups_rec CSI_CTR_DATASTRUCTURES_PUB.counter_groups_rec;
614 l_ctr_group_id NUMBER;
615 BEGIN
616 -- Standard Start of API savepoint
617 SAVEPOINT create_counter_pvt;
618
619 -- Standard call to check for call compatibility.
620 IF NOT FND_API.Compatible_API_Call (l_api_version,
621 p_api_version,
622 l_api_name ,
623 G_PKG_NAME )
624 THEN
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626 END IF;
627
628 -- Initialize message list if p_init_msg_list is set to TRUE.
629 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
630 FND_MSG_PUB.initialize;
631 END IF;
632
633 -- Initialize API return status to success
634 x_return_status := FND_API.G_RET_STS_SUCCESS;
635
636 -- Read the debug profiles values in to global variable 7197402
637 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
638
639 -- Check the profile option debug_level for debug message reporting
640 -- l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
641
642 -- If debug_level = 1 then dump the procedure name
643 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 0) THEN
644 csi_ctr_gen_utility_pvt.put_line( 'create_counter');
645 END IF;
646
647 -- If the debug level = 2 then dump all the parameters values.
648 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 1) THEN
649 csi_ctr_gen_utility_pvt.put_line( 'create_counter' ||
650 p_api_version ||'-'||
651 p_commit ||'-'||
652 p_init_msg_list ||'-'||
653 p_validation_level );
654 csi_ctr_gen_utility_pvt.dump_counter_instance_rec(p_counter_instance_rec);
655 END IF;
656
657 -- Start of API Body
658 if p_counter_instance_rec.counter_id = FND_API.G_MISS_NUM then
659 l_counter_id := null;
660 else
661 l_counter_id := p_counter_instance_rec.counter_id;
662 end if;
663 if p_counter_instance_rec.group_id = FND_API.G_MISS_NUM then
664 l_group_id := null;
665 else
666 l_group_id := p_counter_instance_rec.group_id;
667 end if;
668 if p_counter_instance_rec.name = FND_API.G_MISS_CHAR then
669 l_name := null;
670 else
671 l_name := p_counter_instance_rec.name;
672 end if;
673 if p_counter_instance_rec.description = FND_API.G_MISS_CHAR then
674 l_description := null;
675 else
676 l_description := p_counter_instance_rec.description;
677 end if;
678 if p_counter_instance_rec.counter_type = FND_API.G_MISS_CHAR then
679 l_counter_type := null;
680 else
681 l_counter_type := p_counter_instance_rec.counter_type;
682 end if;
683 if p_counter_instance_rec.uom_code = FND_API.G_MISS_CHAR then
684 l_uom_code := null;
685 else
686 l_uom_code := p_counter_instance_rec.uom_code;
687 end if;
688 if p_counter_instance_rec.usage_item_id = FND_API.G_MISS_NUM then
689 l_usage_item_id := null;
690 else
691 l_usage_item_id := p_counter_instance_rec.usage_item_id;
692 end if;
693 if p_counter_instance_rec.reading_type = FND_API.G_MISS_NUM then
694 l_reading_type := null;
695 else
696 l_reading_type := p_counter_instance_rec.reading_type;
697 end if;
698 if p_counter_instance_rec.direction = FND_API.G_MISS_CHAR then
699 l_direction := null;
700 else
701 l_direction := p_counter_instance_rec.direction;
702 end if;
703 if p_counter_instance_rec.estimation_id = FND_API.G_MISS_NUM then
704 l_estimation_id := null;
705 else
706 l_estimation_id := p_counter_instance_rec.estimation_id;
707 end if;
708 if p_counter_instance_rec.derive_function = FND_API.G_MISS_CHAR then
709 l_derive_function := null;
710 else
711 l_derive_function := p_counter_instance_rec.derive_function;
712 end if;
713 if p_counter_instance_rec.formula_text = FND_API.G_MISS_CHAR then
714 l_formula_text := null;
715 else
716 l_formula_text := p_counter_instance_rec.formula_text;
717 end if;
718 if p_counter_instance_rec.derive_counter_id = FND_API.G_MISS_NUM then
719 l_derive_counter_id := null;
720 else
721 l_derive_counter_id := p_counter_instance_rec.derive_counter_id;
722 end if;
723 if p_counter_instance_rec.filter_type = FND_API.G_MISS_CHAR then
724 l_filter_type := null;
725 else
726 l_filter_type := p_counter_instance_rec.filter_type;
727 end if;
728 if p_counter_instance_rec.filter_reading_count = FND_API.G_MISS_NUM then
729 l_filter_reading_count := null;
730 else
731 l_filter_reading_count := p_counter_instance_rec.filter_reading_count;
732 end if;
733 if p_counter_instance_rec.filter_time_uom = FND_API.G_MISS_CHAR then
734 l_filter_time_uom := null;
735 else
736 l_filter_time_uom := p_counter_instance_rec.filter_time_uom;
737 end if;
738 if p_counter_instance_rec.automatic_rollover = FND_API.G_MISS_CHAR then
739 l_automatic_rollover := null;
740 else
741 l_automatic_rollover := p_counter_instance_rec.automatic_rollover;
742 end if;
743 if p_counter_instance_rec.rollover_last_reading = FND_API.G_MISS_NUM then
744 l_rollover_last_reading := null;
745 else
746 l_rollover_last_reading := p_counter_instance_rec.rollover_last_reading;
747 end if;
748 if p_counter_instance_rec.rollover_first_reading = FND_API.G_MISS_NUM then
749 l_rollover_first_reading := null;
750 else
751 l_rollover_first_reading := p_counter_instance_rec.rollover_first_reading;
752 end if;
753 if p_counter_instance_rec.tolerance_plus = FND_API.G_MISS_NUM then
754 l_tolerance_plus := null;
755 else
756 l_tolerance_plus := p_counter_instance_rec.tolerance_plus;
757 end if;
758 if p_counter_instance_rec.tolerance_minus = FND_API.G_MISS_NUM then
759 l_tolerance_minus := null;
760 else
761 l_tolerance_minus := p_counter_instance_rec.tolerance_minus;
762 end if;
763 if p_counter_instance_rec.used_in_scheduling = FND_API.G_MISS_CHAR then
764 l_used_in_scheduling := null;
765 else
766 l_used_in_scheduling := p_counter_instance_rec.used_in_scheduling;
767 end if;
768 if p_counter_instance_rec.initial_reading = FND_API.G_MISS_NUM then
769 l_initial_reading := null;
770 else
771 l_initial_reading := p_counter_instance_rec.initial_reading;
772 end if;
773 if p_counter_instance_rec.default_usage_rate = FND_API.G_MISS_NUM then
774 l_default_usage_rate := null;
775 else
776 l_default_usage_rate := p_counter_instance_rec.default_usage_rate;
777 end if;
778 if p_counter_instance_rec.use_past_reading = FND_API.G_MISS_NUM then
779 l_use_past_reading := null;
780 else
781 l_use_past_reading := p_counter_instance_rec.use_past_reading;
782 end if;
783 if p_counter_instance_rec.start_date_active = FND_API.G_MISS_DATE then
784 l_start_date_active := sysdate;
785 else
786 l_start_date_active := p_counter_instance_rec.start_date_active;
787 end if;
788 if p_counter_instance_rec.end_date_active = FND_API.G_MISS_DATE then
789 l_end_date_active := null;
790 else
791 l_end_date_active := p_counter_instance_rec.end_date_active;
792 end if;
793 if p_counter_instance_rec.defaulted_group_id = FND_API.G_MISS_NUM then
794 l_defaulted_group_id := null;
795 else
796 l_defaulted_group_id := p_counter_instance_rec.defaulted_group_id;
797 end if;
798 if p_counter_instance_rec.created_from_counter_tmpl_id = FND_API.G_MISS_NUM then
799 l_created_from_counter_tmpl_id := null;
800 else
801 l_created_from_counter_tmpl_id := p_counter_instance_rec.created_from_counter_tmpl_id;
802 end if;
803 if p_counter_instance_rec.attribute1 = FND_API.G_MISS_CHAR then
804 l_attribute1 := null;
805 else
806 l_attribute1 := p_counter_instance_rec.attribute1;
807 end if;
808 if p_counter_instance_rec.attribute2 = FND_API.G_MISS_CHAR then
809 l_attribute2 := null;
810 else
811 l_attribute2 := p_counter_instance_rec.attribute2;
812 end if;
813 if p_counter_instance_rec.attribute3 = FND_API.G_MISS_CHAR then
814 l_attribute3 := null;
815 else
816 l_attribute3 := p_counter_instance_rec.attribute3;
817 end if;
818 if p_counter_instance_rec.attribute4 = FND_API.G_MISS_CHAR then
819 l_attribute4 := null;
820 else
821 l_attribute4 := p_counter_instance_rec.attribute4;
822 end if;
823 if p_counter_instance_rec.attribute5 = FND_API.G_MISS_CHAR then
824 l_attribute5 := null;
825 else
826 l_attribute5 := p_counter_instance_rec.attribute5;
827 end if;
828 if p_counter_instance_rec.attribute6 = FND_API.G_MISS_CHAR then
829 l_attribute6 := null;
830 else
831 l_attribute6 := p_counter_instance_rec.attribute6;
832 end if;
833 if p_counter_instance_rec.attribute7 = FND_API.G_MISS_CHAR then
834 l_attribute7 := null;
835 else
836 l_attribute7 := p_counter_instance_rec.attribute7;
837 end if;
838 if p_counter_instance_rec.attribute8 = FND_API.G_MISS_CHAR then
839 l_attribute8 := null;
840 else
841 l_attribute8 := p_counter_instance_rec.attribute8;
842 end if;
843 if p_counter_instance_rec.attribute9 = FND_API.G_MISS_CHAR then
844 l_attribute9 := null;
845 else
846 l_attribute9 := p_counter_instance_rec.attribute9;
847 end if;
848 if p_counter_instance_rec.attribute10 = FND_API.G_MISS_CHAR then
849 l_attribute10 := null;
850 else
851 l_attribute10 := p_counter_instance_rec.attribute10;
852 end if;
853 if p_counter_instance_rec.attribute11 = FND_API.G_MISS_CHAR then
854 l_attribute11 := null;
855 else
856 l_attribute11 := p_counter_instance_rec.attribute11;
857 end if;
858 if p_counter_instance_rec.attribute12 = FND_API.G_MISS_CHAR then
859 l_attribute12 := null;
860 else
861 l_attribute12 := p_counter_instance_rec.attribute12;
862 end if;
863 if p_counter_instance_rec.attribute13 = FND_API.G_MISS_CHAR then
864 l_attribute13 := null;
865 else
866 l_attribute13 := p_counter_instance_rec.attribute13;
867 end if;
868 if p_counter_instance_rec.attribute14 = FND_API.G_MISS_CHAR then
869 l_attribute14 := null;
870 else
871 l_attribute14 := p_counter_instance_rec.attribute14;
872 end if;
873 if p_counter_instance_rec.attribute15 = FND_API.G_MISS_CHAR then
874 l_attribute15 := null;
875 else
876 l_attribute15 := p_counter_instance_rec.attribute15;
877 end if;
878 if p_counter_instance_rec.attribute16 = FND_API.G_MISS_CHAR then
879 l_attribute16 := null;
880 else
881 l_attribute16 := p_counter_instance_rec.attribute16;
882 end if;
883 if p_counter_instance_rec.attribute17 = FND_API.G_MISS_CHAR then
884 l_attribute17 := null;
885 else
886 l_attribute17 := p_counter_instance_rec.attribute17;
887 end if;
888 if p_counter_instance_rec.attribute18 = FND_API.G_MISS_CHAR then
889 l_attribute18 := null;
890 else
891 l_attribute18 := p_counter_instance_rec.attribute18;
892 end if;
893 if p_counter_instance_rec.attribute19 = FND_API.G_MISS_CHAR then
894 l_attribute19 := null;
895 else
896 l_attribute19 := p_counter_instance_rec.attribute19;
897 end if;
898 if p_counter_instance_rec.attribute20 = FND_API.G_MISS_CHAR then
899 l_attribute20 := null;
900 else
901 l_attribute20 := p_counter_instance_rec.attribute20;
902 end if;
903 if p_counter_instance_rec.attribute21 = FND_API.G_MISS_CHAR then
904 l_attribute21 := null;
905 else
906 l_attribute21 := p_counter_instance_rec.attribute21;
907 end if;
908 if p_counter_instance_rec.attribute22 = FND_API.G_MISS_CHAR then
909 l_attribute22 := null;
910 else
911 l_attribute22 := p_counter_instance_rec.attribute22;
912 end if;
913 if p_counter_instance_rec.attribute23 = FND_API.G_MISS_CHAR then
914 l_attribute23 := null;
915 else
916 l_attribute23 := p_counter_instance_rec.attribute23;
917 end if;
918 if p_counter_instance_rec.attribute24 = FND_API.G_MISS_CHAR then
919 l_attribute24 := null;
920 else
921 l_attribute24 := p_counter_instance_rec.attribute24;
922 end if;
923 if p_counter_instance_rec.attribute25 = FND_API.G_MISS_CHAR then
924 l_attribute25 := null;
925 else
926 l_attribute25 := p_counter_instance_rec.attribute25;
927 end if;
928 if p_counter_instance_rec.attribute26 = FND_API.G_MISS_CHAR then
929 l_attribute26 := null;
930 else
931 l_attribute26 := p_counter_instance_rec.attribute26;
932 end if;
933 if p_counter_instance_rec.attribute27 = FND_API.G_MISS_CHAR then
934 l_attribute27 := null;
935 else
936 l_attribute27 := p_counter_instance_rec.attribute27;
937 end if;
938 if p_counter_instance_rec.attribute28 = FND_API.G_MISS_CHAR then
939 l_attribute28 := null;
940 else
941 l_attribute28 := p_counter_instance_rec.attribute28;
942 end if;
943 if p_counter_instance_rec.attribute29 = FND_API.G_MISS_CHAR then
944 l_attribute29 := null;
945 else
946 l_attribute29 := p_counter_instance_rec.attribute29;
947 end if;
948 if p_counter_instance_rec.attribute30 = FND_API.G_MISS_CHAR then
949 l_attribute30 := null;
950 else
951 l_attribute30 := p_counter_instance_rec.attribute30;
952 end if;
953 if p_counter_instance_rec.attribute_category = FND_API.G_MISS_CHAR then
954 l_attribute_category := null;
955 else
956 l_attribute_category := p_counter_instance_rec.attribute_category;
957 end if;
958 if p_counter_instance_rec.step_value = FND_API.G_MISS_NUM then
959 l_step_value := null;
960 else
961 l_step_value := p_counter_instance_rec.step_value;
962 end if;
963
964 if p_counter_instance_rec.time_based_manual_entry = FND_API.G_MISS_CHAR then
965 l_time_based_manual_entry := 'N';
966 else
967 l_time_based_manual_entry := p_counter_instance_rec.time_based_manual_entry;
968 end if;
969
970 if p_counter_instance_rec.eam_required_flag = FND_API.G_MISS_CHAR then
971 l_eam_required_flag := null;
972 else
973 l_eam_required_flag := p_counter_instance_rec.eam_required_flag;
974 end if;
975
976 -- Validate counter name is unique
977 --Validate_Unique_ctr(l_name, l_counter_id);
978 -- Validate start date
979 /*
980 IF l_start_date_active IS NULL THEN
981 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_STDATE_INVALID');
982 ELS */
983 IF l_start_date_active > sysdate THEN
984 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_INVALID_START_DATE');
985 END IF;
986
987 if nvl(p_counter_instance_rec.initial_reading_date, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE then
988 l_initial_reading_date := sysdate;
989 else
990 l_initial_reading_date := p_counter_instance_rec.initial_reading_date;
991 end if;
992
993
994 if l_created_from_counter_tmpl_id is not null then
995 begin
996 select 'x'
997 into l_dummy
998 from csi_counter_template_b
999 where counter_id = l_created_from_counter_tmpl_id;
1000 exception when no_data_found then
1001 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_TMPL_INVALID');
1002 end;
1003 end if;
1004
1005 --call validate counter to validate the counter instance
1006 validate_counter(l_group_id, l_name, l_counter_type, l_uom_code, l_usage_item_id,
1007 l_reading_type, l_direction, l_estimation_id, l_derive_function,
1008 l_formula_text, l_derive_counter_id, l_filter_type, l_filter_reading_count,
1009 l_filter_time_uom, l_automatic_rollover, l_rollover_last_reading,
1010 l_rollover_first_reading, l_tolerance_plus, l_tolerance_minus,
1011 l_used_in_scheduling, l_initial_reading, l_default_usage_rate,
1012 l_use_past_reading, -1, l_start_date_active, l_end_date_active
1013 );
1014
1015 -- Check and Generate Counter_value_id
1016 IF l_counter_id IS NULL OR l_counter_id = FND_API.G_MISS_NUM THEN
1017 select CSI_COUNTERS_B_S.nextval
1018 into l_counter_id from dual;
1019 END IF;
1020 -- counter name suffix counter id is created from template or if duplicate exist.
1021 if l_created_from_counter_tmpl_id is not null then
1022 l_name := l_name||'-'||l_counter_id;
1023 else
1024 if counter_name_exists(l_name, l_counter_id) then
1025 l_name := l_name||'-'||l_counter_id;
1026 end if;
1027 end if;
1028
1029 -- if counter group id is null, generate the defaulted group id
1030 if l_group_id is null and l_defaulted_group_id is null then
1031 l_counter_groups_rec.NAME := l_name||'-Group';
1032 l_counter_groups_rec.DESCRIPTION := l_name||'-Group';
1033 IF l_counter_groups_rec.ASSOCIATION_TYPE is NULL THEN
1034 l_counter_groups_rec.ASSOCIATION_TYPE := 'TRACKABLE';
1035 END IF;
1036
1037 SELECT CS_COUNTER_GROUPS_S.nextval
1038 INTO l_ctr_group_id FROM dual;
1039
1040 csi_Counter_template_pvt.create_counter_group
1041 (
1042 p_api_version => p_api_version
1043 ,p_commit => p_commit
1044 ,p_init_msg_list => p_init_msg_list
1045 ,p_validation_level => p_validation_level
1046 ,p_counter_groups_rec => l_counter_groups_rec
1047 ,x_return_status => x_return_status
1048 ,x_msg_count => x_msg_count
1049 ,x_msg_data => x_msg_data
1050 );
1051 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1052 ROLLBACK TO create_counter_pvt;
1053 RETURN;
1054 END IF;
1055 l_defaulted_group_id := l_counter_groups_rec.counter_group_id;
1056 end if;
1057
1058 IF l_defaulted_group_id IS NULL and l_group_id IS NOT NULL THEN
1059 l_defaulted_group_id := l_group_id;
1060 END IF;
1061
1062 --
1063 csi_ctr_gen_utility_pvt.put_line('Inserting Counter with Value ID '||to_char(l_counter_id));
1064 -- call table handler here
1065 CSI_COUNTERS_PKG.Insert_Row
1066 (
1067 px_COUNTER_ID => l_counter_id
1068 ,p_GROUP_ID => l_group_id
1069 ,p_COUNTER_TYPE => l_counter_type
1070 ,p_INITIAL_READING => l_initial_reading
1071 ,p_INITIAL_READING_DATE => l_initial_reading_date
1072 ,p_TOLERANCE_PLUS => l_tolerance_plus
1073 ,p_TOLERANCE_MINUS => l_tolerance_minus
1074 ,p_UOM_CODE => l_uom_code
1075 ,p_DERIVE_COUNTER_ID => l_derive_counter_id
1076 ,p_DERIVE_FUNCTION => l_derive_function
1077 ,p_DERIVE_PROPERTY_ID => null
1078 ,p_VALID_FLAG => null
1079 ,p_FORMULA_INCOMPLETE_FLAG => l_formula_incomplete_flag
1080 ,p_FORMULA_TEXT => l_formula_text
1081 ,p_ROLLOVER_LAST_READING => l_rollover_last_reading
1082 ,p_ROLLOVER_FIRST_READING => l_rollover_first_reading
1083 ,p_USAGE_ITEM_ID => l_usage_item_id
1084 ,p_CTR_VAL_MAX_SEQ_NO => 0
1085 ,p_START_DATE_ACTIVE => l_start_date_active
1086 ,p_END_DATE_ACTIVE => l_end_date_active
1087 ,p_OBJECT_VERSION_NUMBER => 1
1088 ,p_LAST_UPDATE_DATE => sysdate
1089 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1090 ,p_CREATION_DATE => sysdate
1091 ,p_CREATED_BY => FND_GLOBAL.USER_ID
1092 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
1093 ,p_ATTRIBUTE1 => l_attribute1
1094 ,p_ATTRIBUTE2 => l_attribute2
1095 ,p_ATTRIBUTE3 => l_attribute3
1096 ,p_ATTRIBUTE4 => l_attribute4
1097 ,p_ATTRIBUTE5 => l_attribute5
1098 ,p_ATTRIBUTE6 => l_attribute6
1099 ,p_ATTRIBUTE7 => l_attribute7
1100 ,p_ATTRIBUTE8 => l_attribute8
1101 ,p_ATTRIBUTE9 => l_attribute9
1102 ,p_ATTRIBUTE10 => l_attribute10
1103 ,p_ATTRIBUTE11 => l_attribute11
1104 ,p_ATTRIBUTE12 => l_attribute12
1105 ,p_ATTRIBUTE13 => l_attribute13
1106 ,p_ATTRIBUTE14 => l_attribute14
1107 ,p_ATTRIBUTE15 => l_attribute15
1108 ,p_ATTRIBUTE16 => l_attribute16
1109 ,p_ATTRIBUTE17 => l_attribute17
1110 ,p_ATTRIBUTE18 => l_attribute18
1111 ,p_ATTRIBUTE19 => l_attribute19
1112 ,p_ATTRIBUTE20 => l_attribute20
1113 ,p_ATTRIBUTE21 => l_attribute21
1114 ,p_ATTRIBUTE22 => l_attribute22
1115 ,p_ATTRIBUTE23 => l_attribute23
1116 ,p_ATTRIBUTE24 => l_attribute24
1117 ,p_ATTRIBUTE25 => l_attribute25
1118 ,p_ATTRIBUTE26 => l_attribute26
1119 ,p_ATTRIBUTE27 => l_attribute27
1120 ,p_ATTRIBUTE28 => l_attribute28
1121 ,p_ATTRIBUTE29 => l_attribute29
1122 ,p_ATTRIBUTE30 => l_attribute30
1123 ,p_ATTRIBUTE_CATEGORY => l_attribute_category
1124 ,p_MIGRATED_FLAG => null
1125 ,p_CUSTOMER_VIEW => null
1126 ,p_DIRECTION => l_direction
1127 ,p_FILTER_TYPE => l_filter_type
1128 ,p_FILTER_READING_COUNT => l_filter_reading_count
1129 ,p_FILTER_TIME_UOM => l_filter_time_uom
1130 ,p_ESTIMATION_ID => l_estimation_id
1131 --,p_COUNTER_CODE => l_name||'-'||l_counter_id
1132 ,p_READING_TYPE => l_reading_type
1133 ,p_AUTOMATIC_ROLLOVER => l_automatic_rollover
1134 ,p_DEFAULT_USAGE_RATE => l_default_usage_rate
1135 ,p_USE_PAST_READING => l_use_past_reading
1136 ,p_USED_IN_SCHEDULING => l_used_in_scheduling
1137 ,p_DEFAULTED_GROUP_ID => l_defaulted_group_id
1138 ,p_CREATED_FROM_COUNTER_TMPL_ID => l_created_from_counter_tmpl_id
1139 ,p_SECURITY_GROUP_ID => NULL
1140 ,p_STEP_VALUE => l_step_value
1141 --,p_NAME => l_name||'-'||l_counter_id
1142 ,p_NAME => l_name
1143 ,p_DESCRIPTION => l_description
1144 ,p_TIME_BASED_MANUAL_ENTRY => l_time_based_manual_entry
1145 ,p_EAM_REQUIRED_FLAG => l_eam_required_flag
1146 ,p_comments => NULL
1147 );
1148 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1149 ROLLBACK TO create_counter_pvt;
1150 RETURN;
1151 END IF;
1152 x_ctr_id := l_counter_id;
1153 -- End of API body
1154
1155 -- Standard check of p_commit.
1156 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
1157 COMMIT WORK;
1158 END IF;
1159
1160 EXCEPTION
1161 WHEN FND_API.G_EXC_ERROR THEN
1162 x_return_status := FND_API.G_RET_STS_ERROR ;
1163 ROLLBACK TO create_counter_pvt;
1164 FND_MSG_PUB.Count_And_Get
1165 (p_count => x_msg_count,
1166 p_data => x_msg_data
1167 );
1168
1169 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1171 ROLLBACK TO create_counter_pvt;
1172 FND_MSG_PUB.Count_And_Get
1173 (p_count => x_msg_count,
1174 p_data => x_msg_data
1175 );
1176 WHEN OTHERS THEN
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1178 ROLLBACK TO create_counter_pvt;
1179 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1180 THEN
1181 FND_MSG_PUB.Add_Exc_Msg
1182 (G_PKG_NAME,
1183 l_api_name
1184 );
1185 END IF;
1186 FND_MSG_PUB.Count_And_Get
1187 (p_count => x_msg_count,
1188 p_data => x_msg_data
1189 );
1190
1191 END create_counter;
1192
1193 --|---------------------------------------------------
1194 --| procedure name: create_ctr_property
1195 --| description : procedure used to
1196 --| create counter properties
1197 --|---------------------------------------------------
1198
1199 PROCEDURE create_ctr_property
1200 (
1201 p_api_version IN NUMBER
1202 ,p_commit IN VARCHAR2
1203 ,p_init_msg_list IN VARCHAR2
1204 ,p_validation_level IN NUMBER
1205 ,p_ctr_properties_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.Ctr_Properties_Rec
1206 ,x_return_status OUT NOCOPY VARCHAR2
1207 ,x_msg_count OUT NOCOPY NUMBER
1208 ,x_msg_data OUT NOCOPY VARCHAR2
1209 ,x_ctr_property_id OUT NOCOPY NUMBER
1210 )
1211 IS
1212 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CTR_PROPERTY';
1213 l_api_version CONSTANT NUMBER := 1.0;
1214 -- l_debug_level NUMBER;
1215 l_flag VARCHAR2(1) := 'N';
1216 l_msg_data VARCHAR2(2000);
1217 l_msg_index NUMBER;
1218 l_msg_count NUMBER;
1219 l_count NUMBER;
1220 l_return_message VARCHAR2(100);
1221
1222 l_counter_property_id NUMBER;
1223 l_counter_id NUMBER;
1224 l_name VARCHAR2(50);
1225 l_description VARCHAR2(240);
1226 l_property_data_type VARCHAR2(30);
1227 l_is_nullable VARCHAR2(1);
1228 l_uom_code VARCHAR2(3);
1229 l_property_lov_type VARCHAR2(30);
1230 l_default_value VARCHAR2(240);
1231 l_minimum_value VARCHAR2(240);
1232 l_maximum_value VARCHAR2(240);
1233 l_start_date_active DATE;
1234 l_end_date_active DATE;
1235 l_created_from_ctrprop_tmpl_id NUMBER;
1236 l_attribute1 VARCHAR2(150);
1237 l_attribute2 VARCHAR2(150);
1238 l_attribute3 VARCHAR2(150);
1239 l_attribute4 VARCHAR2(150);
1240 l_attribute5 VARCHAR2(150);
1241 l_attribute6 VARCHAR2(150);
1242 l_attribute7 VARCHAR2(150);
1243 l_attribute8 VARCHAR2(150);
1244 l_attribute9 VARCHAR2(150);
1245 l_attribute10 VARCHAR2(150);
1246 l_attribute11 VARCHAR2(150);
1247 l_attribute12 VARCHAR2(150);
1248 l_attribute13 VARCHAR2(150);
1249 l_attribute14 VARCHAR2(150);
1250 l_attribute15 VARCHAR2(150);
1251 l_attribute_category VARCHAR2(30);
1252
1253 l_dummy VARCHAR2(1);
1254 BEGIN
1255 -- Standard Start of API savepoint
1256 SAVEPOINT create_ctr_property_pvt;
1257
1258 -- Standard call to check for call compatibility.
1259 IF NOT FND_API.Compatible_API_Call (l_api_version,
1260 p_api_version,
1261 l_api_name ,
1262 G_PKG_NAME )
1263 THEN
1264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1265 END IF;
1266
1267 -- Initialize message list if p_init_msg_list is set to TRUE.
1268 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1269 FND_MSG_PUB.initialize;
1270 END IF;
1271
1272 -- Initialize API return status to success
1273 x_return_status := FND_API.G_RET_STS_SUCCESS;
1274
1275 -- Read the debug profiles values in to global variable 7197402
1276 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
1277
1278 -- Check the profile option debug_level for debug message reporting
1279 -- l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
1280
1281 -- If debug_level = 1 then dump the procedure name
1282 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 0) THEN
1283 csi_ctr_gen_utility_pvt.put_line( 'create_ctr_property');
1284 END IF;
1285
1286 -- If the debug level = 2 then dump all the parameters values.
1287 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 1) THEN
1288 csi_ctr_gen_utility_pvt.put_line( 'create_ctr_property' ||
1289 p_api_version ||'-'||
1290 p_commit ||'-'||
1291 p_init_msg_list ||'-'||
1292 p_validation_level );
1293 csi_ctr_gen_utility_pvt.dump_ctr_properties_rec(p_ctr_properties_rec);
1294 END IF;
1295
1296 -- Start of API Body
1297 if p_ctr_properties_rec.counter_id = FND_API.G_MISS_NUM then
1298 l_counter_id := null;
1299 else
1300 l_counter_id := p_ctr_properties_rec.counter_id;
1301 end if;
1302 if p_ctr_properties_rec.name = FND_API.G_MISS_CHAR then
1303 l_name := null;
1304 else
1305 l_name := p_ctr_properties_rec.name;
1306 end if;
1307 if p_ctr_properties_rec.description = FND_API.G_MISS_CHAR then
1308 l_description := null;
1309 else
1310 l_description := p_ctr_properties_rec.description;
1311 end if;
1312 if p_ctr_properties_rec.property_data_type = FND_API.G_MISS_CHAR then
1313 l_property_data_type := null;
1314 else
1315 l_property_data_type := p_ctr_properties_rec.property_data_type;
1316 end if;
1317 if p_ctr_properties_rec.is_nullable = FND_API.G_MISS_CHAR then
1318 l_is_nullable := null;
1319 else
1320 l_is_nullable := p_ctr_properties_rec.is_nullable;
1321 if l_is_nullable not in ('Y', 'N') then
1322 l_is_nullable := 'N';
1323 end if;
1324 end if;
1325 if p_ctr_properties_rec.uom_code = FND_API.G_MISS_CHAR then
1326 l_uom_code := null;
1327 else
1328 l_uom_code := p_ctr_properties_rec.uom_code;
1329 end if;
1330 if p_ctr_properties_rec.property_lov_type = FND_API.G_MISS_CHAR then
1331 l_property_lov_type := null;
1332 else
1333 l_property_lov_type := p_ctr_properties_rec.property_lov_type;
1334 end if;
1335 if p_ctr_properties_rec.default_value = FND_API.G_MISS_CHAR then
1336 l_default_value := null;
1337 else
1338 l_default_value := p_ctr_properties_rec.default_value;
1339 end if;
1340 if p_ctr_properties_rec.minimum_value = FND_API.G_MISS_CHAR then
1341 l_minimum_value := null;
1342 else
1343 l_minimum_value := p_ctr_properties_rec.minimum_value;
1344 end if;
1345 if p_ctr_properties_rec.maximum_value = FND_API.G_MISS_CHAR then
1346 l_maximum_value := null;
1347 else
1348 l_maximum_value := p_ctr_properties_rec.maximum_value;
1349 end if;
1350 if p_ctr_properties_rec.start_date_active = FND_API.G_MISS_DATE then
1351 l_start_date_active := sysdate;
1352 else
1353 l_start_date_active := p_ctr_properties_rec.start_date_active;
1354 end if;
1355 if p_ctr_properties_rec.end_date_active = FND_API.G_MISS_DATE then
1356 l_end_date_active := null;
1357 else
1358 l_end_date_active := p_ctr_properties_rec.end_date_active;
1359 end if;
1360 if p_ctr_properties_rec.created_from_ctr_prop_tmpl_id = FND_API.G_MISS_NUM then
1361 l_created_from_ctrprop_tmpl_id := null;
1362 else
1363 l_created_from_ctrprop_tmpl_id := p_ctr_properties_rec.created_from_ctr_prop_tmpl_id;
1364 end if;
1365 if p_ctr_properties_rec.attribute1 = FND_API.G_MISS_CHAR then
1366 l_attribute1 := null;
1367 else
1368 l_attribute1 := p_ctr_properties_rec.attribute1;
1369 end if;
1370 if p_ctr_properties_rec.attribute2 = FND_API.G_MISS_CHAR then
1371 l_attribute2 := null;
1372 else
1373 l_attribute2 := p_ctr_properties_rec.attribute2;
1374 end if;
1375 if p_ctr_properties_rec.attribute3 = FND_API.G_MISS_CHAR then
1376 l_attribute3 := null;
1377 else
1378 l_attribute3 := p_ctr_properties_rec.attribute3;
1379 end if;
1380 if p_ctr_properties_rec.attribute4 = FND_API.G_MISS_CHAR then
1381 l_attribute4 := null;
1382 else
1383 l_attribute4 := p_ctr_properties_rec.attribute4;
1384 end if;
1385 if p_ctr_properties_rec.attribute5 = FND_API.G_MISS_CHAR then
1386 l_attribute5 := null;
1387 else
1388 l_attribute5 := p_ctr_properties_rec.attribute5;
1389 end if;
1390 if p_ctr_properties_rec.attribute6 = FND_API.G_MISS_CHAR then
1391 l_attribute6 := null;
1392 else
1393 l_attribute6 := p_ctr_properties_rec.attribute6;
1394 end if;
1395 if p_ctr_properties_rec.attribute7 = FND_API.G_MISS_CHAR then
1396 l_attribute7 := null;
1397 else
1398 l_attribute7 := p_ctr_properties_rec.attribute7;
1399 end if;
1400 if p_ctr_properties_rec.attribute8 = FND_API.G_MISS_CHAR then
1401 l_attribute8 := null;
1402 else
1403 l_attribute8 := p_ctr_properties_rec.attribute8;
1404 end if;
1405 if p_ctr_properties_rec.attribute9 = FND_API.G_MISS_CHAR then
1406 l_attribute9 := null;
1407 else
1408 l_attribute9 := p_ctr_properties_rec.attribute9;
1409 end if;
1410 if p_ctr_properties_rec.attribute10 = FND_API.G_MISS_CHAR then
1411 l_attribute10 := null;
1412 else
1413 l_attribute10 := p_ctr_properties_rec.attribute10;
1414 end if;
1415 if p_ctr_properties_rec.attribute11 = FND_API.G_MISS_CHAR then
1416 l_attribute11 := null;
1417 else
1418 l_attribute11 := p_ctr_properties_rec.attribute11;
1419 end if;
1420 if p_ctr_properties_rec.attribute12 = FND_API.G_MISS_CHAR then
1421 l_attribute12 := null;
1422 else
1423 l_attribute12 := p_ctr_properties_rec.attribute12;
1424 end if;
1425 if p_ctr_properties_rec.attribute13 = FND_API.G_MISS_CHAR then
1426 l_attribute13 := null;
1427 else
1428 l_attribute13 := p_ctr_properties_rec.attribute13;
1429 end if;
1430 if p_ctr_properties_rec.attribute14 = FND_API.G_MISS_CHAR then
1431 l_attribute14 := null;
1432 else
1433 l_attribute14 := p_ctr_properties_rec.attribute14;
1434 end if;
1435 if p_ctr_properties_rec.attribute15 = FND_API.G_MISS_CHAR then
1436 l_attribute15 := null;
1437 else
1438 l_attribute15 := p_ctr_properties_rec.attribute15;
1439 end if;
1440 if p_ctr_properties_rec.attribute_category = FND_API.G_MISS_CHAR then
1441 l_attribute_category := null;
1442 else
1443 l_attribute_category := p_ctr_properties_rec.attribute_category;
1444 end if;
1445
1446 --Validate Counter Id
1447 begin
1448 select 'x'
1449 into l_dummy
1450 from csi_counters_b
1451 where counter_id = l_counter_id;
1452 exception when no_data_found then
1453 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INVALID');
1454 end;
1455
1456 -- validate unique property name within counter
1457 Validate_Unique_ctrprop( l_name, l_counter_id,p_ctr_properties_rec.counter_property_id);
1458
1459 -- validate property date type in char,number,date
1460 if l_property_data_type not in ('CHAR', 'NUMBER', 'DATE') then
1461 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_PROP_DATA_TYPE');
1462 end if;
1463
1464 -- Validate values for data type
1465 Validate_Data_Type(l_property_data_type, l_default_value,
1466 l_minimum_value, l_maximum_value);
1467
1468 -- Validate uom
1469 if l_uom_code is not null then
1470 Validate_UOM(l_uom_code);
1471 end if;
1472
1473 -- Validate start date
1474 /*
1475 IF l_start_date_active IS NULL THEN
1476 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_STDATE_INVALID');
1477 ELS */
1478 IF l_start_date_active > sysdate THEN
1479 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_INVALID_START_DATE');
1480 END IF;
1481
1482 if l_created_from_ctrprop_tmpl_id is not null then
1483 begin
1484 select 'x'
1485 into l_dummy
1486 from csi_counter_template_b c, csi_ctr_property_template_b p
1487 where p.counter_id = c.counter_id
1488 and p.counter_property_id = l_created_from_ctrprop_tmpl_id;
1489 exception when no_data_found then
1490 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_PROP_TMPL_INVALID');
1491 end;
1492 end if;
1493
1494 -- call table handler here
1495
1496 CSI_COUNTER_PROPERTIES_PKG.INSERT_ROW
1497 (
1498 px_COUNTER_PROPERTY_ID => l_counter_property_id
1499 ,p_COUNTER_ID => l_counter_id
1500 ,p_PROPERTY_DATA_TYPE => l_property_data_type
1501 ,p_IS_NULLABLE => l_is_nullable
1502 ,p_DEFAULT_VALUE => l_default_value
1503 ,p_MINIMUM_VALUE => l_minimum_value
1504 ,p_MAXIMUM_VALUE => l_maximum_value
1505 ,p_UOM_CODE => l_uom_code
1506 ,p_START_DATE_ACTIVE => l_start_date_active
1507 ,p_END_DATE_ACTIVE => l_end_date_active
1508 ,p_OBJECT_VERSION_NUMBER => 1
1509 ,p_SECURITY_GROUP_ID => null
1510 ,p_LAST_UPDATE_DATE => sysdate
1511 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1512 ,p_CREATION_DATE => sysdate
1513 ,p_CREATED_BY => FND_GLOBAL.USER_ID
1514 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
1515 ,p_ATTRIBUTE1 => l_attribute1
1516 ,p_ATTRIBUTE2 => l_attribute2
1517 ,p_ATTRIBUTE3 => l_attribute3
1518 ,p_ATTRIBUTE4 => l_attribute4
1519 ,p_ATTRIBUTE5 => l_attribute5
1520 ,p_ATTRIBUTE6 => l_attribute6
1521 ,p_ATTRIBUTE7 => l_attribute7
1522 ,p_ATTRIBUTE8 => l_attribute8
1523 ,p_ATTRIBUTE9 => l_attribute9
1524 ,p_ATTRIBUTE10 => l_attribute10
1525 ,p_ATTRIBUTE11 => l_attribute11
1526 ,p_ATTRIBUTE12 => l_attribute12
1527 ,p_ATTRIBUTE13 => l_attribute13
1528 ,p_ATTRIBUTE14 => l_attribute14
1529 ,p_ATTRIBUTE15 => l_attribute15
1530 ,p_ATTRIBUTE_CATEGORY => l_attribute_category
1531 ,p_MIGRATED_FLAG => null
1532 ,p_PROPERTY_LOV_TYPE => l_property_lov_type
1533 ,p_NAME => l_name
1534 ,p_DESCRIPTION => l_description
1535 ,p_create_from_ctr_prop_tmpl_id => l_created_from_ctrprop_tmpl_id
1536 );
1537 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1538 ROLLBACK TO create_ctr_property_pvt;
1539 RETURN;
1540 END IF;
1541
1542 -- End of API body
1543
1544 -- Standard check of p_commit.
1545 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
1546 COMMIT WORK;
1547 END IF;
1548
1549 EXCEPTION
1550 WHEN FND_API.G_EXC_ERROR THEN
1551 x_return_status := FND_API.G_RET_STS_ERROR ;
1552 ROLLBACK TO create_ctr_property_pvt;
1553 FND_MSG_PUB.Count_And_Get
1554 (p_count => x_msg_count,
1555 p_data => x_msg_data
1556 );
1557
1558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1560 ROLLBACK TO create_ctr_property_pvt;
1561 FND_MSG_PUB.Count_And_Get
1562 (p_count => x_msg_count,
1563 p_data => x_msg_data
1564 );
1565 WHEN OTHERS THEN
1566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1567 ROLLBACK TO create_ctr_property_pvt;
1568 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1569 THEN
1570 FND_MSG_PUB.Add_Exc_Msg
1571 (G_PKG_NAME,
1572 l_api_name
1573 );
1574 END IF;
1575 FND_MSG_PUB.Count_And_Get
1576 (p_count => x_msg_count,
1577 p_data => x_msg_data
1578 );
1579 END create_ctr_property;
1580
1581 --|---------------------------------------------------
1582 --| procedure name: create_ctr_associations
1583 --| description : procedure used to
1584 --| create counter associations
1585 --|---------------------------------------------------
1586
1587 PROCEDURE create_ctr_associations
1588 (
1589 p_api_version IN NUMBER
1590 ,p_commit IN VARCHAR2
1591 ,p_init_msg_list IN VARCHAR2
1592 ,p_validation_level IN NUMBER
1593 ,P_counter_associations_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.counter_associations_rec
1594 ,x_return_status OUT NOCOPY VARCHAR2
1595 ,x_msg_count OUT NOCOPY NUMBER
1596 ,x_msg_data OUT NOCOPY VARCHAR2
1597 ,x_instance_association_id OUT NOCOPY NUMBER
1598 )
1599 IS
1600 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CTR_ASSOCIATIONS';
1601 l_api_version CONSTANT NUMBER := 1.0;
1602 -- l_debug_level NUMBER;
1603 l_flag VARCHAR2(1) := 'N';
1604 l_msg_data VARCHAR2(2000);
1605 l_msg_index NUMBER;
1606 l_msg_count NUMBER;
1607 l_count NUMBER;
1608 l_return_message VARCHAR2(100);
1609
1610 l_instance_association_id NUMBER;
1611 l_source_object_code VARCHAR2(30);
1612 l_source_object_id NUMBER;
1613 l_counter_id NUMBER;
1614 l_maint_organization_id NUMBER;
1615 l_start_date_active DATE;
1616 l_end_date_active DATE;
1617 l_attribute1 VARCHAR2(150);
1618 l_attribute2 VARCHAR2(150);
1619 l_attribute3 VARCHAR2(150);
1620 l_attribute4 VARCHAR2(150);
1621 l_attribute5 VARCHAR2(150);
1622 l_attribute6 VARCHAR2(150);
1623 l_attribute7 VARCHAR2(150);
1624 l_attribute8 VARCHAR2(150);
1625 l_attribute9 VARCHAR2(150);
1626 l_attribute10 VARCHAR2(150);
1627 l_attribute11 VARCHAR2(150);
1628 l_attribute12 VARCHAR2(150);
1629 l_attribute13 VARCHAR2(150);
1630 l_attribute14 VARCHAR2(150);
1631 l_attribute15 VARCHAR2(150);
1632 l_attribute_category VARCHAR2(30);
1633 l_instance_number VARCHAR2(30);
1634 l_primary_failure_flag VARCHAR2(1);
1635 l_dummy VARCHAR2(1);
1636 l_is_association_exist VARCHAR2(1);
1637 l_maint_organization_i NUMBER;
1638 l_cp_inventory_id NUMBER;
1639 l_cp_last_vld_org NUMBER;
1640 l_eam_item_type NUMBER;
1641
1642 CURSOR association_exist(p_ctr_id number, p_src_obj_id number) IS
1643 select 'X'
1644 from csi_counter_associations
1645 where counter_id = p_ctr_id
1646 and source_object_id = p_src_obj_id;
1647
1648 BEGIN
1649 -- Standard Start of API savepoint
1650 SAVEPOINT create_ctr_associations_pvt;
1651
1652 -- Standard call to check for call compatibility.
1653 IF NOT FND_API.Compatible_API_Call (l_api_version,
1654 p_api_version,
1655 l_api_name ,
1656 G_PKG_NAME )
1657 THEN
1658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1659 END IF;
1660
1661 -- Initialize message list if p_init_msg_list is set to TRUE.
1662 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1663 FND_MSG_PUB.initialize;
1664 END IF;
1665
1666 -- Initialize API return status to success
1667 x_return_status := FND_API.G_RET_STS_SUCCESS;
1668
1669 -- Read the debug profiles values in to global variable 7197402
1670 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
1671
1672 -- Check the profile option debug_level for debug message reporting
1673 -- l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
1674
1675 -- If debug_level = 1 then dump the procedure name
1676 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 0) THEN
1677 csi_ctr_gen_utility_pvt.put_line( 'create_ctr_associations');
1678 END IF;
1679
1680 -- If the debug level = 2 then dump all the parameters values.
1681 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 1) THEN
1682 csi_ctr_gen_utility_pvt.put_line( 'create_ctr_associations' ||
1683 p_api_version ||'-'||
1684 p_commit ||'-'||
1685 p_init_msg_list ||'-'||
1686 p_validation_level );
1687 csi_ctr_gen_utility_pvt.dump_counter_associations_rec(p_counter_associations_rec);
1688 END IF;
1689
1690 -- Start of API Body
1691
1692 if P_counter_associations_rec.source_object_code = FND_API.G_MISS_CHAR then
1693 l_source_object_code := null;
1694 else
1695 l_source_object_code := P_counter_associations_rec.source_object_code;
1696 end if;
1697 if P_counter_associations_rec.source_object_id = FND_API.G_MISS_NUM then
1698 l_source_object_id := null;
1699 else
1700 l_source_object_id := P_counter_associations_rec.source_object_id;
1701 end if;
1702 if P_counter_associations_rec.counter_id = FND_API.G_MISS_NUM then
1703 l_counter_id := null;
1704 else
1705 l_counter_id := P_counter_associations_rec.counter_id;
1706 end if;
1707 if P_counter_associations_rec.start_date_active = FND_API.G_MISS_DATE then
1708 l_start_date_active := sysdate;
1709 else
1710 l_start_date_active := P_counter_associations_rec.start_date_active;
1711 end if;
1712 if P_counter_associations_rec.end_date_active = FND_API.G_MISS_DATE then
1713 l_end_date_active := null;
1714 else
1715 l_end_date_active := P_counter_associations_rec.end_date_active;
1716 end if;
1717 if p_counter_associations_rec.attribute1 = FND_API.G_MISS_CHAR then
1718 l_attribute1 := null;
1719 else
1720 l_attribute1 := p_counter_associations_rec.attribute1;
1721 end if;
1722 if p_counter_associations_rec.attribute2 = FND_API.G_MISS_CHAR then
1723 l_attribute2 := null;
1724 else
1725 l_attribute2 := p_counter_associations_rec.attribute2;
1726 end if;
1727 if p_counter_associations_rec.attribute3 = FND_API.G_MISS_CHAR then
1728 l_attribute3 := null;
1729 else
1730 l_attribute3 := p_counter_associations_rec.attribute3;
1731 end if;
1732 if p_counter_associations_rec.attribute4 = FND_API.G_MISS_CHAR then
1733 l_attribute4 := null;
1734 else
1735 l_attribute4 := p_counter_associations_rec.attribute4;
1736 end if;
1737 if p_counter_associations_rec.attribute5 = FND_API.G_MISS_CHAR then
1738 l_attribute5 := null;
1739 else
1740 l_attribute5 := p_counter_associations_rec.attribute5;
1741 end if;
1742 if p_counter_associations_rec.attribute6 = FND_API.G_MISS_CHAR then
1743 l_attribute6 := null;
1744 else
1745 l_attribute6 := p_counter_associations_rec.attribute6;
1746 end if;
1747 if p_counter_associations_rec.attribute7 = FND_API.G_MISS_CHAR then
1748 l_attribute7 := null;
1749 else
1750 l_attribute7 := p_counter_associations_rec.attribute7;
1751 end if;
1752 if p_counter_associations_rec.attribute8 = FND_API.G_MISS_CHAR then
1753 l_attribute8 := null;
1754 else
1755 l_attribute8 := p_counter_associations_rec.attribute8;
1756 end if;
1757 if p_counter_associations_rec.attribute9 = FND_API.G_MISS_CHAR then
1758 l_attribute9 := null;
1759 else
1760 l_attribute9 := p_counter_associations_rec.attribute9;
1761 end if;
1762 if p_counter_associations_rec.attribute10 = FND_API.G_MISS_CHAR then
1763 l_attribute10 := null;
1764 else
1765 l_attribute10 := p_counter_associations_rec.attribute10;
1766 end if;
1767 if p_counter_associations_rec.attribute11 = FND_API.G_MISS_CHAR then
1768 l_attribute11 := null;
1769 else
1770 l_attribute11 := p_counter_associations_rec.attribute11;
1771 end if;
1772 if p_counter_associations_rec.attribute12 = FND_API.G_MISS_CHAR then
1773 l_attribute12 := null;
1774 else
1775 l_attribute12 := p_counter_associations_rec.attribute12;
1776 end if;
1777 if p_counter_associations_rec.attribute13 = FND_API.G_MISS_CHAR then
1778 l_attribute13 := null;
1779 else
1780 l_attribute13 := p_counter_associations_rec.attribute13;
1781 end if;
1782 if p_counter_associations_rec.attribute14 = FND_API.G_MISS_CHAR then
1783 l_attribute14 := null;
1784 else
1785 l_attribute14 := p_counter_associations_rec.attribute14;
1786 end if;
1787 if p_counter_associations_rec.attribute15 = FND_API.G_MISS_CHAR then
1788 l_attribute15 := null;
1789 else
1790 l_attribute15 := p_counter_associations_rec.attribute15;
1791 end if;
1792 if p_counter_associations_rec.attribute_category = FND_API.G_MISS_CHAR then
1793 l_attribute_category := null;
1794 else
1795 l_attribute_category := p_counter_associations_rec.attribute_category;
1796 end if;
1797
1798 if p_counter_associations_rec.maint_organization_id = FND_API.G_MISS_NUM then
1799 l_maint_organization_id := null;
1800 else
1801 l_maint_organization_id := p_counter_associations_rec.maint_organization_id;
1802 end if;
1803
1804 if p_counter_associations_rec.primary_failure_flag = FND_API.G_MISS_CHAR then
1805 l_primary_failure_flag := null;
1806 else
1807 l_primary_failure_flag := p_counter_associations_rec.primary_failure_flag;
1808 end if;
1809
1810 --Validate Counter Id
1811 begin
1812 select 'x'
1813 into l_dummy
1814 from csi_counters_b
1815 where counter_id = l_counter_id;
1816 exception when no_data_found then
1817 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INVALID');
1818 end;
1819
1820 if l_source_object_code = 'CP' then
1821 begin
1822 select inventory_item_id, last_vld_organization_id, instance_number
1823 into l_cp_inventory_id, l_cp_last_vld_org, l_instance_number
1824 from csi_item_instances
1825 where instance_id = l_source_object_id;
1826 exception
1827 when no_data_found then
1828 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_IB_CPID_INVALID', 'PARAM', to_char(l_source_object_id));
1829 end;
1830 elsif l_source_object_code = 'CONTRACT_LINE' then
1831 begin
1832 select 'x'
1833 into l_dummy
1834 from okc_k_lines_b
1835 where id = l_source_object_id;
1836 exception when no_data_found then
1837 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_INV_CONTRACT_LINE', 'PARAM', to_char(l_source_object_id));
1838 end;
1839 else
1840 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_SRC_OBJ_CD','SRC_OBJ_CODE',l_source_object_code);
1841 end if;
1842
1843 /* Check if it is an EAM item to get the maint_organization_id */
1844 BEGIN
1845 SELECT eam_item_type
1846 INTO l_eam_item_type
1847 FROM mtl_system_items_b
1848 WHERE inventory_item_id = l_cp_inventory_id
1849 AND organization_id = l_cp_last_vld_org;
1850 EXCEPTION
1851 WHEN NO_DATA_FOUND THEN
1852 l_eam_item_type := 0;
1853 WHEN TOO_MANY_ROWS THEN
1854 l_eam_item_type := 1;
1855 END;
1856
1857 IF l_eam_item_type = 1 or l_eam_item_type = 3 THEN
1858 BEGIN
1859 SELECT maint_organization_id
1860 INTO l_maint_organization_id
1861 FROM mtl_parameters
1862 WHERE organization_id = l_cp_last_vld_org;
1863 EXCEPTION
1864 WHEN OTHERS THEN
1865 NULL;
1866 END;
1867 END IF;
1868 /* End of checking*/
1869 csi_ctr_gen_utility_pvt.put_line( ' EAM Item Type = '||to_char(l_eam_item_type));
1870 csi_ctr_gen_utility_pvt.put_line( ' Maint organization id = '||to_char(l_maint_organization_id));
1871
1872 -- Validate start date
1873 /*
1874 IF l_start_date_active IS NULL THEN
1875 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_STDATE_INVALID');
1876 ELS */
1877 IF l_start_date_active > sysdate THEN
1878 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_INVALID_START_DATE');
1879 END IF;
1880
1881 -- Check for duplicate assocition
1882 OPEN association_exist(l_counter_id, l_source_object_id );
1883 FETCH association_exist INTO l_is_association_exist;
1884 CLOSE association_exist;
1885 IF l_is_association_exist is not null then
1886 -- Association exist for this counter. Raise error
1887 csi_ctr_gen_utility_pvt.put_line('Association exist for this counter. Duplicate error...');
1888 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_ASSOCIATION_EXIST','INSTANCE_ID',l_instance_number);
1889 END IF;
1890
1891 -- call table handler here
1892
1893 CSI_COUNTER_ASSOCIATIONS_PKG.INSERT_ROW
1894 (
1895 px_INSTANCE_ASSOCIATION_ID => l_instance_association_id
1896 ,p_SOURCE_OBJECT_CODE => l_source_object_code
1897 ,p_SOURCE_OBJECT_ID => l_source_object_id
1898 ,p_COUNTER_ID => l_counter_id
1899 ,p_START_DATE_ACTIVE => l_start_date_active
1900 ,p_END_DATE_ACTIVE => l_end_date_active
1901 ,p_OBJECT_VERSION_NUMBER => 1
1902 ,p_LAST_UPDATE_DATE => sysdate
1903 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1904 ,p_CREATION_DATE => sysdate
1905 ,p_CREATED_BY => FND_GLOBAL.USER_ID
1906 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
1907 ,p_ATTRIBUTE1 => l_attribute1
1908 ,p_ATTRIBUTE2 => l_attribute2
1909 ,p_ATTRIBUTE3 => l_attribute3
1910 ,p_ATTRIBUTE4 => l_attribute4
1911 ,p_ATTRIBUTE5 => l_attribute5
1912 ,p_ATTRIBUTE6 => l_attribute6
1913 ,p_ATTRIBUTE7 => l_attribute7
1914 ,p_ATTRIBUTE8 => l_attribute8
1915 ,p_ATTRIBUTE9 => l_attribute9
1916 ,p_ATTRIBUTE10 => l_attribute10
1917 ,p_ATTRIBUTE11 => l_attribute11
1918 ,p_ATTRIBUTE12 => l_attribute12
1919 ,p_ATTRIBUTE13 => l_attribute13
1920 ,p_ATTRIBUTE14 => l_attribute14
1921 ,p_ATTRIBUTE15 => l_attribute15
1922 ,p_ATTRIBUTE_CATEGORY => l_attribute_category
1923 ,p_MIGRATED_FLAG => null
1924 ,p_SECURITY_GROUP_ID => null
1925 ,p_MAINT_ORGANIZATION_ID => l_maint_organization_id
1926 ,p_PRIMARY_FAILURE_FLAG => l_primary_failure_flag
1927 );
1928 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1929 ROLLBACK TO create_ctr_associations_pvt;
1930 RETURN;
1931 END IF;
1932
1933 -- End of API body
1934
1935 -- Standard check of p_commit.
1936 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
1937 COMMIT WORK;
1938 END IF;
1939
1940 EXCEPTION
1941 WHEN FND_API.G_EXC_ERROR THEN
1942 x_return_status := FND_API.G_RET_STS_ERROR ;
1943 ROLLBACK TO create_ctr_associations_pvt;
1944 FND_MSG_PUB.Count_And_Get
1945 (p_count => x_msg_count,
1946 p_data => x_msg_data
1947 );
1948
1949 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1950 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1951 ROLLBACK TO create_ctr_associations_pvt;
1952 FND_MSG_PUB.Count_And_Get
1953 (p_count => x_msg_count,
1954 p_data => x_msg_data
1955 );
1956 WHEN OTHERS THEN
1957 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1958 ROLLBACK TO create_ctr_associations_pvt;
1959 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1960 THEN
1961 FND_MSG_PUB.Add_Exc_Msg
1962 (G_PKG_NAME,
1963 l_api_name
1964 );
1965 END IF;
1966 FND_MSG_PUB.Count_And_Get
1967 (p_count => x_msg_count,
1968 p_data => x_msg_data
1969 );
1970 END create_ctr_associations;
1971
1972 --|---------------------------------------------------
1973 --| procedure name: create_reading_lock
1974 --| description : procedure used to
1975 --| create reading lock on a counter
1976 --|---------------------------------------------------
1977
1978 PROCEDURE create_reading_lock
1979 (
1980 p_api_version IN NUMBER
1981 ,p_commit IN VARCHAR2
1982 ,p_init_msg_list IN VARCHAR2
1983 ,p_validation_level IN NUMBER
1984 ,p_ctr_reading_lock_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.ctr_reading_lock_rec
1985 ,x_return_status OUT NOCOPY VARCHAR2
1986 ,x_msg_count OUT NOCOPY NUMBER
1987 ,x_msg_data OUT NOCOPY VARCHAR2
1988 ,x_reading_lock_id OUT NOCOPY NUMBER
1989 )
1990 IS
1991 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_READING_LOCK';
1992 l_api_version CONSTANT NUMBER := 1.0;
1993 -- l_debug_level NUMBER;
1994 l_flag VARCHAR2(1) := 'N';
1995 l_msg_data VARCHAR2(2000);
1996 l_msg_index NUMBER;
1997 l_msg_count NUMBER;
1998 l_count NUMBER;
1999 l_return_message VARCHAR2(100);
2000
2001 l_reading_lock_id NUMBER;
2002 l_counter_id NUMBER;
2003 l_reading_lock_date DATE;
2004 l_active_start_date DATE;
2005 l_active_end_date DATE;
2006 l_source_group_ref_id NUMBER;
2007 l_source_group_REF VARCHAR2(50);
2008 l_source_header_ref_id NUMBER;
2009 l_source_header_ref VARCHAR2(50);
2010 l_source_line_ref_id NUMBER;
2011 l_source_line_ref VARCHAR2(50);
2012 l_source_dist_ref_id1 NUMBER;
2013 l_source_dist_ref_id2 NUMBER;
2014
2015 CURSOR LAST_CTR_READING_CUR(p_counter_id IN NUMBER,p_reading_lock_date IN DATE) IS
2016 select value_timestamp
2017 from CSI_COUNTER_READINGS
2018 where counter_id = p_counter_id
2019 and nvl(disabled_flag,'N') = 'N'
2020 and value_timestamp <= p_reading_lock_date
2021 ORDER BY value_timestamp desc;
2022
2023 l_last_reading_date DATE;
2024
2025 l_dummy VARCHAR2(1);
2026
2027 BEGIN
2028 -- Standard Start of API savepoint
2029 SAVEPOINT create_reading_lock_pvt;
2030
2031 -- Standard call to check for call compatibility.
2032 IF NOT FND_API.Compatible_API_Call (l_api_version,
2033 p_api_version,
2034 l_api_name ,
2035 G_PKG_NAME )
2036 THEN
2037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2038 END IF;
2039
2040 -- Initialize message list if p_init_msg_list is set to TRUE.
2041 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2042 FND_MSG_PUB.initialize;
2043 END IF;
2044
2045 -- Initialize API return status to success
2046 x_return_status := FND_API.G_RET_STS_SUCCESS;
2047
2048 -- Read the debug profiles values in to global variable 7197402
2049 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
2050
2051 -- Check the profile option debug_level for debug message reporting
2052 -- l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2053
2054 -- If debug_level = 1 then dump the procedure name
2055 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 0) THEN
2056 csi_ctr_gen_utility_pvt.put_line( 'create_reading_lock');
2057 END IF;
2058
2059 -- If the debug level = 2 then dump all the parameters values.
2060 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 1) THEN
2061 csi_ctr_gen_utility_pvt.put_line( 'create_reading_lock' ||
2062 p_api_version ||'-'||
2063 p_commit ||'-'||
2064 p_init_msg_list ||'-'||
2065 p_validation_level );
2066 csi_ctr_gen_utility_pvt.dump_ctr_reading_lock_rec(p_ctr_reading_lock_rec);
2067 END IF;
2068
2069 -- Start of API Body
2070
2071 if p_ctr_reading_lock_rec.counter_id = FND_API.G_MISS_NUM then
2072 l_counter_id := null;
2073 else
2074 l_counter_id := p_ctr_reading_lock_rec.counter_id;
2075 end if;
2076 if p_ctr_reading_lock_rec.reading_lock_date = FND_API.G_MISS_DATE then
2077 l_reading_lock_date := null;
2078 else
2079 l_reading_lock_date := p_ctr_reading_lock_rec.reading_lock_date;
2080 end if;
2081 /*
2082 if p_ctr_reading_lock_rec.active_start_date = FND_API.G_MISS_DATE then
2083 l_active_start_date := null;
2084 else
2085 l_active_start_date := p_ctr_reading_lock_rec.active_start_date;
2086 end if;
2087 if p_ctr_reading_lock_rec.active_end_date = FND_API.G_MISS_DATE then
2088 l_active_end_date := null;
2089 else
2090 l_active_end_date := p_ctr_reading_lock_rec.active_end_date;
2091 end if;
2092 */
2093 if p_ctr_reading_lock_rec.source_group_ref_id = FND_API.G_MISS_NUM then
2094 l_source_group_ref_id := null;
2095 else
2096 l_source_group_ref_id := p_ctr_reading_lock_rec.source_group_ref_id;
2097 end if;
2098 if p_ctr_reading_lock_rec.source_group_ref = FND_API.G_MISS_CHAR then
2099 l_source_group_ref := null;
2100 else
2101 l_source_group_ref := p_ctr_reading_lock_rec.source_group_ref;
2102 end if;
2103 if p_ctr_reading_lock_rec.source_header_ref_id = FND_API.G_MISS_NUM then
2104 l_source_header_ref_id := null;
2105 else
2106 l_source_header_ref_id := p_ctr_reading_lock_rec.source_header_ref_id;
2107 end if;
2108 if p_ctr_reading_lock_rec.source_header_ref = FND_API.G_MISS_CHAR then
2109 l_source_header_ref := null;
2110 else
2111 l_source_header_ref := p_ctr_reading_lock_rec.source_header_ref;
2112 end if;
2113 if p_ctr_reading_lock_rec.source_line_ref_id = FND_API.G_MISS_NUM then
2114 l_source_line_ref_id := null;
2115 else
2116 l_source_line_ref_id := p_ctr_reading_lock_rec.source_line_ref_id;
2117 end if;
2118 if p_ctr_reading_lock_rec.source_line_ref = FND_API.G_MISS_CHAR then
2119 l_source_line_ref := null;
2120 else
2121 l_source_line_ref := p_ctr_reading_lock_rec.source_line_ref;
2122 end if;
2123 if p_ctr_reading_lock_rec.source_dist_ref_id1 = FND_API.G_MISS_NUM then
2124 l_source_dist_ref_id1 := null;
2125 else
2126 l_source_dist_ref_id1 := p_ctr_reading_lock_rec.source_dist_ref_id1;
2127 end if;
2128 if p_ctr_reading_lock_rec.source_dist_ref_id2 = FND_API.G_MISS_NUM then
2129 l_source_dist_ref_id2 := null;
2130 else
2131 l_source_dist_ref_id2 := p_ctr_reading_lock_rec.source_dist_ref_id2;
2132 end if;
2133
2134 --Validate Counter Id
2135 begin
2136 select 'x'
2137 into l_dummy
2138 from csi_counters_b
2139 where counter_id = l_counter_id;
2140 exception when no_data_found then
2141 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INVALID');
2142 end;
2143
2144 --validate reading lock date for null
2145 if l_reading_lock_date is null then
2146 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_RDG_LOCK_DT','RDG_LOCK_DATE',l_reading_lock_date);
2147 end if;
2148 --validate reading lock date is not future date
2149 if l_reading_lock_date > sysdate then
2150 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_RDG_LOCK_DT','RDG_LOCK_DATE',l_reading_lock_date);
2151 end if;
2152
2153 -- Get the last reading for this counter
2154 OPEN LAST_CTR_READING_CUR(l_counter_id, l_reading_lock_date);
2155 FETCH LAST_CTR_READING_CUR
2156 into l_last_reading_date;
2157 CLOSE LAST_CTR_READING_CUR;
2158
2159 If l_last_reading_date is null then
2160 l_last_reading_date := l_reading_lock_date;
2161 End If;
2162
2163 -- call table handler here
2164
2165 CSI_COUNTER_READING_LOCK_PKG.INSERT_ROW
2166 (
2167 px_reading_lock_id => l_reading_lock_id
2168 ,p_counter_id => l_counter_id
2169 -- ,p_reading_lock_date => l_last_reading_date
2170 ,p_reading_lock_date => l_reading_lock_date
2171 --,p_active_start_date => l_active_start_date
2172 --,p_active_end_date => l_active_end_date
2173 ,p_object_version_number => 1
2174 ,p_last_update_date => sysdate
2175 ,p_last_updated_by => FND_GLOBAL.USER_ID
2176 ,p_creation_date => sysdate
2177 ,p_created_by => FND_GLOBAL.USER_ID
2178 ,p_last_update_login => FND_GLOBAL.USER_ID
2179 ,p_SOURCE_GROUP_REF_ID => l_source_group_ref_id
2180 ,p_SOURCE_GROUP_REF => l_source_group_ref
2181 ,p_SOURCE_HEADER_REF_ID => l_source_header_ref_id
2182 ,p_SOURCE_HEADER_REF => l_source_header_ref
2183 ,p_SOURCE_LINE_REF_ID => l_source_line_ref_id
2184 ,p_SOURCE_LINE_REF => l_source_line_ref
2185 ,p_SOURCE_DIST_REF_ID1 => l_source_dist_ref_id1
2186 ,p_SOURCE_DIST_REF_ID2 => l_source_dist_ref_id2
2187 );
2188 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2189 ROLLBACK TO create_reading_lock_pvt;
2190 RETURN;
2191 END IF;
2192
2193 -- End of API body
2194
2195 -- Standard check of p_commit.
2196 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
2197 COMMIT WORK;
2198 END IF;
2199
2200 EXCEPTION
2201 WHEN FND_API.G_EXC_ERROR THEN
2202 x_return_status := FND_API.G_RET_STS_ERROR ;
2203 ROLLBACK TO create_reading_lock_pvt;
2204 FND_MSG_PUB.Count_And_Get
2205 (p_count => x_msg_count,
2206 p_data => x_msg_data
2207 );
2208
2209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2211 ROLLBACK TO create_reading_lock_pvt;
2212 FND_MSG_PUB.Count_And_Get
2213 (p_count => x_msg_count,
2214 p_data => x_msg_data
2215 );
2216 WHEN OTHERS THEN
2217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2218 ROLLBACK TO create_reading_lock_pvt;
2219 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2220 THEN
2221 FND_MSG_PUB.Add_Exc_Msg
2222 (G_PKG_NAME,
2223 l_api_name
2224 );
2225 END IF;
2226 FND_MSG_PUB.Count_And_Get
2227 (p_count => x_msg_count,
2228 p_data => x_msg_data
2229 );
2230 END create_reading_lock;
2231
2232 --|---------------------------------------------------
2233 --| procedure name: create_daily_usage
2234 --| description : procedure used to
2235 --| create daily usage
2236 --|---------------------------------------------------
2237
2238 PROCEDURE create_daily_usage
2239 (
2240 p_api_version IN NUMBER
2241 ,p_commit IN VARCHAR2
2242 ,p_init_msg_list IN VARCHAR2
2243 ,p_validation_level IN NUMBER
2244 ,p_ctr_usage_forecast_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.ctr_usage_forecast_rec
2245 ,x_return_status OUT NOCOPY VARCHAR2
2246 ,x_msg_count OUT NOCOPY NUMBER
2247 ,x_msg_data OUT NOCOPY VARCHAR2
2248 ,x_instance_forecast_id OUT NOCOPY NUMBER
2249 )
2250 IS
2251 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DAILY_USAGE';
2252 l_api_version CONSTANT NUMBER := 1.0;
2253 -- l_debug_level NUMBER;
2254 l_flag VARCHAR2(1) := 'N';
2255 l_msg_data VARCHAR2(2000);
2256 l_msg_index NUMBER;
2257 l_msg_count NUMBER;
2258 l_count NUMBER;
2259 l_return_message VARCHAR2(100);
2260
2261 l_instance_forecast_id NUMBER;
2262 l_counter_id NUMBER;
2263 l_usage_rate NUMBER;
2264 l_use_past_reading NUMBER;
2265 l_active_start_date DATE;
2266 l_active_end_date DATE;
2267 l_max_start_date DATE;
2268
2269 l_dummy VARCHAR2(1);
2270
2271
2272 BEGIN
2273 -- Standard Start of API savepoint
2274 SAVEPOINT create_daily_usage_pvt;
2275
2276 -- Standard call to check for call compatibility.
2277 IF NOT FND_API.Compatible_API_Call (l_api_version,
2278 p_api_version,
2279 l_api_name ,
2280 G_PKG_NAME )
2281 THEN
2282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2283 END IF;
2284
2285 -- Initialize message list if p_init_msg_list is set to TRUE.
2286 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2287 FND_MSG_PUB.initialize;
2288 END IF;
2289
2290 -- Initialize API return status to success
2291 x_return_status := FND_API.G_RET_STS_SUCCESS;
2292
2293 -- Read the debug profiles values in to global variable 7197402
2294 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
2295
2296 -- Check the profile option debug_level for debug message reporting
2297 -- l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2298
2299 -- If debug_level = 1 then dump the procedure name
2300 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 0) THEN
2301 csi_ctr_gen_utility_pvt.put_line( 'create_daily_usage');
2302 END IF;
2303
2304 -- If the debug level = 2 then dump all the parameters values.
2305 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 1) THEN
2306 csi_ctr_gen_utility_pvt.put_line( 'create_daily_usage' ||
2307 p_api_version ||'-'||
2308 p_commit ||'-'||
2309 p_init_msg_list ||'-'||
2310 p_validation_level );
2311 csi_ctr_gen_utility_pvt.dump_ctr_usage_forecast_rec(p_ctr_usage_forecast_rec);
2312 END IF;
2313
2314 -- Start of API Body
2315
2316 if p_ctr_usage_forecast_rec.counter_id = FND_API.G_MISS_NUM then
2317 l_counter_id := null;
2318 else
2319 l_counter_id := p_ctr_usage_forecast_rec.counter_id;
2320 end if;
2321 if p_ctr_usage_forecast_rec.usage_rate = FND_API.G_MISS_NUM then
2322 l_usage_rate := null;
2323 else
2324 l_usage_rate := p_ctr_usage_forecast_rec.usage_rate;
2325 end if;
2326 if p_ctr_usage_forecast_rec.use_past_reading = FND_API.G_MISS_NUM then
2327 l_use_past_reading := null;
2328 else
2329 l_use_past_reading := p_ctr_usage_forecast_rec.use_past_reading;
2330 end if;
2331 if p_ctr_usage_forecast_rec.active_start_date = FND_API.G_MISS_DATE then
2332 l_active_start_date := null;
2333 else
2334 l_active_start_date := p_ctr_usage_forecast_rec.active_start_date;
2335 end if;
2336
2337 IF l_active_start_date IS NULL THEN
2338 l_active_start_date := sysdate;
2339 ELSE
2340 IF l_active_start_date > sysdate THEN
2341 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_INVALID_START_DATE');
2342 END IF;
2343 END IF;
2344
2345 /* Validate start date */
2346 IF l_counter_id IS NOT NULL THEN
2347 BEGIN
2348 SELECT max(active_start_date)
2349 INTO l_max_start_date
2350 FROM CSI_COUNTER_USAGE_FORECAST
2351 WHERE counter_id = l_counter_id;
2352
2353 IF l_max_start_date IS NOT NULL THEN
2354 IF l_active_start_date < l_max_start_date THEN
2355 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_USAGE_STDATE_INV');
2356 END IF;
2357 END IF;
2358 END;
2359 END IF;
2360
2361 if p_ctr_usage_forecast_rec.active_end_date = FND_API.G_MISS_DATE then
2362 l_active_end_date := null;
2363 else
2364 l_active_end_date := p_ctr_usage_forecast_rec.active_end_date;
2365 end if;
2366
2367 --Validate Counter Id
2368 begin
2369 select 'x'
2370 into l_dummy
2371 from csi_counters_b
2372 where counter_id = l_counter_id;
2373 exception when no_data_found then
2374 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INVALID');
2375 end;
2376
2377 -- Validate usage rate is not negative
2378 if l_usage_rate < 0 or l_usage_rate is null then
2379 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_USAGE_RATE');
2380 end if;
2381
2382 -- Validate use past reading is not negative
2383 if l_use_past_reading < 0 or l_use_past_reading is null then
2384 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_USE_PAST_RDG');
2385 end if;
2386
2387 -- update to end date existing active usage rate
2388 UPDATE CSI_COUNTER_USAGE_FORECAST
2389 SET active_end_date = l_active_start_date
2390 -- SET active_end_date = sysdate
2391 WHERE instance_forecast_id in (select instance_forecast_id
2392 from CSI_COUNTER_USAGE_FORECAST
2393 where counter_id = l_counter_id
2394 and active_end_date is null);
2395
2396 -- call table handler here
2397 CSI_CTR_USAGE_FORECAST_PKG.insert_row
2398 (
2399 px_instance_forecast_id => l_instance_forecast_id
2400 ,p_counter_id => l_counter_id
2401 ,p_usage_rate => l_usage_rate
2402 ,p_use_past_reading => l_use_past_reading
2403 ,p_active_start_date => l_active_start_date
2404 ,p_active_end_date => l_active_end_date
2405 ,p_object_version_number => 1
2406 ,p_last_update_date => sysdate
2407 ,p_last_updated_by => FND_GLOBAL.USER_ID
2408 ,p_creation_date => sysdate
2409 ,p_created_by => FND_GLOBAL.USER_ID
2410 ,p_last_update_login => FND_GLOBAL.USER_ID
2411 );
2412 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2413 ROLLBACK TO create_daily_usage_pvt;
2414 RETURN;
2415 END IF;
2416
2417 -- End of API body
2418
2419 -- Standard check of p_commit.
2420 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
2421 COMMIT WORK;
2422 END IF;
2423
2424 EXCEPTION
2425 WHEN FND_API.G_EXC_ERROR THEN
2426 x_return_status := FND_API.G_RET_STS_ERROR ;
2427 ROLLBACK TO create_daily_usage_pvt;
2428 FND_MSG_PUB.Count_And_Get
2429 (p_count => x_msg_count,
2430 p_data => x_msg_data
2431 );
2432
2433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2435 ROLLBACK TO create_daily_usage_pvt;
2436 FND_MSG_PUB.Count_And_Get
2437 (p_count => x_msg_count,
2438 p_data => x_msg_data
2439 );
2440 WHEN OTHERS THEN
2441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2442 ROLLBACK TO create_daily_usage_pvt;
2443 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2444 THEN
2445 FND_MSG_PUB.Add_Exc_Msg
2446 (G_PKG_NAME,
2447 l_api_name
2448 );
2449 END IF;
2450 FND_MSG_PUB.Count_And_Get
2451 (p_count => x_msg_count,
2452 p_data => x_msg_data
2453 );
2454 END create_daily_usage;
2455
2456 --|---------------------------------------------------
2457 --| procedure name: update_counter
2458 --| description : procedure used to
2459 --| create counter instance
2460 --|---------------------------------------------------
2461
2462 PROCEDURE update_counter
2463 (
2464 p_api_version IN NUMBER
2465 ,p_init_msg_list IN VARCHAR2
2466 ,p_commit IN VARCHAR2
2467 ,p_validation_level IN VARCHAR2
2468 ,p_counter_instance_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.Counter_instance_rec
2469 ,x_return_status OUT NOCOPY VARCHAR2
2470 ,x_msg_count OUT NOCOPY NUMBER
2471 ,x_msg_data OUT NOCOPY VARCHAR2
2472 )
2473 IS
2474 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_COUNTER';
2475 l_api_version CONSTANT NUMBER := 1.0;
2476 -- l_debug_level NUMBER;
2477 l_flag VARCHAR2(1) := 'N';
2478 l_msg_data VARCHAR2(2000);
2479 l_msg_index NUMBER;
2480 l_msg_count NUMBER;
2481 l_count NUMBER;
2482 l_return_message VARCHAR2(100);
2483 l_return_status VARCHAR2(40);
2484
2485 l_old_counter_instance_rec CSI_CTR_DATASTRUCTURES_PUB.Counter_instance_rec;
2486 l_counter_instance_rec CSI_CTR_DATASTRUCTURES_PUB.Counter_instance_rec;
2487
2488 CURSOR formula_ref_cur(p_counter_id number) IS
2489 SELECT relationship_id
2490 FROM csi_counter_relationships
2491 WHERE object_counter_id = p_counter_id and relationship_type_code = 'FORMULA';
2492 l_formula_ref_count NUMBER;
2493 CURSOR derived_filters_cur(p_counter_id NUMBER) IS
2494 SELECT counter_derived_filter_id
2495 FROM csi_counter_derived_filters
2496 WHERE counter_id = p_counter_id;
2497 l_der_filter_count NUMBER;
2498 CURSOR target_counter_cur(p_counter_id NUMBER) IS
2499 SELECT relationship_id
2500 FROM csi_counter_relationships
2501 WHERE source_counter_id = p_counter_id and relationship_type_code = 'CONFIGURATION';
2502 l_target_ctr_exist NUMBER;
2503 CURSOR counter_readings_cur(p_counter_id NUMBER) IS
2504 SELECT counter_value_id
2505 FROM csi_counter_readings
2506 WHERE counter_id = p_counter_id;
2507 l_rdg_exists NUMBER;
2508
2509 -- for counter name check
2510 l_new_name_instr NUMBER;
2511 l_old_name_instr NUMBER;
2512
2513 BEGIN
2514 -- Standard Start of API savepoint
2515 SAVEPOINT update_counter_pvt;
2516
2517 -- Standard call to check for call compatibility.
2518 IF NOT FND_API.Compatible_API_Call (l_api_version,
2519 p_api_version,
2520 l_api_name ,
2521 G_PKG_NAME )
2522 THEN
2523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2524 END IF;
2525
2526 -- Initialize message list if p_init_msg_list is set to TRUE.
2527 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2528 FND_MSG_PUB.initialize;
2529 END IF;
2530
2531 -- Initialize API return status to success
2532 x_return_status := FND_API.G_RET_STS_SUCCESS;
2533
2534 -- Read the debug profiles values in to global variable 7197402
2535 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
2536
2537 -- Check the profile option debug_level for debug message reporting
2538 -- l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
2539
2540 -- If debug_level = 1 then dump the procedure name
2541 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 0) THEN
2542 csi_ctr_gen_utility_pvt.put_line( 'update_counter');
2543 END IF;
2544
2545 -- If the debug level = 2 then dump all the parameters values.
2546 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 1) THEN
2547 csi_ctr_gen_utility_pvt.put_line( 'update_counter' ||
2548 p_api_version ||'-'||
2549 p_commit ||'-'||
2550 p_init_msg_list ||'-'||
2551 p_validation_level );
2552 csi_ctr_gen_utility_pvt.dump_counter_instance_rec(p_counter_instance_rec);
2553 END IF;
2554
2555 -- Start of API Body
2556
2557 SELECT group_id,
2558 counter_type,
2559 initial_reading,
2560 initial_reading_date,
2561 created_from_counter_tmpl_id,
2562 tolerance_plus,
2563 tolerance_minus,
2564 uom_code,
2565 derive_counter_id,
2566 derive_function,
2567 valid_flag,
2568 formula_incomplete_flag,
2569 formula_text,
2570 rollover_last_reading,
2571 rollover_first_reading,
2572 usage_item_id,
2573 ctr_val_max_seq_no,
2574 start_date_active,
2575 end_date_active,
2576 attribute1,
2577 attribute2,
2578 attribute3,
2579 attribute4,
2580 attribute5,
2581 attribute6,
2582 attribute7,
2583 attribute8,
2584 attribute9,
2585 attribute10,
2586 attribute11,
2587 attribute12,
2588 attribute13,
2589 attribute14,
2590 attribute15,
2591 attribute16,
2592 attribute17,
2593 attribute18,
2594 attribute19,
2595 attribute20,
2596 attribute21,
2597 attribute22,
2598 attribute23,
2599 attribute24,
2600 attribute25,
2601 attribute26,
2602 attribute27,
2603 attribute28,
2604 attribute29,
2605 attribute30,
2606 attribute_category,
2607 customer_view,
2608 direction,
2609 filter_type,
2610 filter_reading_count,
2611 filter_time_uom,
2612 estimation_id,
2613 --counter_code,
2614 reading_type,
2615 automatic_rollover,
2616 default_usage_rate,
2617 use_past_reading,
2618 used_in_scheduling,
2619 defaulted_group_id
2620 ,name
2621 ,comments
2622 ,step_value
2623 ,object_version_number
2624 ,time_based_manual_entry
2625 ,eam_required_flag
2626 INTO l_old_counter_instance_rec.group_id,
2627 l_old_counter_instance_rec.counter_type,
2628 l_old_counter_instance_rec.initial_reading,
2629 l_old_counter_instance_rec.initial_reading_date,
2630 l_old_counter_instance_rec.created_from_counter_tmpl_id,
2631 l_old_counter_instance_rec.tolerance_plus,
2632 l_old_counter_instance_rec.tolerance_minus,
2633 l_old_counter_instance_rec.uom_code,
2634 l_old_counter_instance_rec.derive_counter_id,
2635 l_old_counter_instance_rec.derive_function,
2636 l_old_counter_instance_rec.valid_flag,
2637 l_old_counter_instance_rec.formula_incomplete_flag,
2638 l_old_counter_instance_rec.formula_text,
2639 l_old_counter_instance_rec.rollover_last_reading,
2640 l_old_counter_instance_rec.rollover_first_reading,
2641 l_old_counter_instance_rec.usage_item_id,
2642 l_old_counter_instance_rec.ctr_val_max_seq_no,
2643 l_old_counter_instance_rec.start_date_active,
2644 l_old_counter_instance_rec.end_date_active,
2645 l_old_counter_instance_rec.attribute1,
2646 l_old_counter_instance_rec.attribute2,
2647 l_old_counter_instance_rec.attribute3,
2648 l_old_counter_instance_rec.attribute4,
2649 l_old_counter_instance_rec.attribute5,
2650 l_old_counter_instance_rec.attribute6,
2651 l_old_counter_instance_rec.attribute7,
2652 l_old_counter_instance_rec.attribute8,
2653 l_old_counter_instance_rec.attribute9,
2654 l_old_counter_instance_rec.attribute10,
2655 l_old_counter_instance_rec.attribute11,
2656 l_old_counter_instance_rec.attribute12,
2657 l_old_counter_instance_rec.attribute13,
2658 l_old_counter_instance_rec.attribute14,
2659 l_old_counter_instance_rec.attribute15,
2660 l_old_counter_instance_rec.attribute16,
2661 l_old_counter_instance_rec.attribute17,
2662 l_old_counter_instance_rec.attribute18,
2663 l_old_counter_instance_rec.attribute19,
2664 l_old_counter_instance_rec.attribute20,
2665 l_old_counter_instance_rec.attribute21,
2666 l_old_counter_instance_rec.attribute22,
2667 l_old_counter_instance_rec.attribute23,
2668 l_old_counter_instance_rec.attribute24,
2669 l_old_counter_instance_rec.attribute25,
2670 l_old_counter_instance_rec.attribute26,
2671 l_old_counter_instance_rec.attribute27,
2672 l_old_counter_instance_rec.attribute28,
2673 l_old_counter_instance_rec.attribute29,
2674 l_old_counter_instance_rec.attribute30,
2675 l_old_counter_instance_rec.attribute_category,
2676 l_old_counter_instance_rec.customer_view,
2677 l_old_counter_instance_rec.direction,
2678 l_old_counter_instance_rec.filter_type,
2679 l_old_counter_instance_rec.filter_reading_count,
2680 l_old_counter_instance_rec.filter_time_uom,
2681 l_old_counter_instance_rec.estimation_id,
2682 --l_old_counter_instance_rec.counter_code,
2683 l_old_counter_instance_rec.reading_type,
2684 l_old_counter_instance_rec.automatic_rollover,
2685 l_old_counter_instance_rec.default_usage_rate,
2686 l_old_counter_instance_rec.use_past_reading,
2687 l_old_counter_instance_rec.used_in_scheduling,
2688 l_old_counter_instance_rec.defaulted_group_id
2689 ,l_old_counter_instance_rec.name
2690 ,l_old_counter_instance_rec.comments
2691 ,l_old_counter_instance_rec.step_value
2692 ,l_old_counter_instance_rec.object_version_number
2693 ,l_old_counter_instance_rec.time_based_manual_entry
2694 ,l_old_counter_instance_rec.eam_required_flag
2695 FROM csi_counters_vl
2696 WHERE counter_id = p_counter_instance_rec.counter_id
2697 FOR UPDATE OF OBJECT_VERSION_NUMBER;
2698 IF SQL%NOTFOUND THEN
2699 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INVALID');
2700 END IF;
2701
2702 l_counter_instance_rec := p_counter_instance_rec;
2703
2704 IF p_counter_instance_rec.group_id = FND_API.G_MISS_NUM THEN
2705 l_counter_instance_rec.group_id := NULL;
2706 ELSIF p_counter_instance_rec.group_id IS NULL THEN
2707 l_counter_instance_rec.group_id := l_old_counter_instance_rec.group_id;
2708 END IF;
2709 IF p_counter_instance_rec.counter_type = FND_API.G_MISS_CHAR THEN
2710 l_counter_instance_rec.counter_type := NULL;
2711 ELSIF p_counter_instance_rec.counter_type IS NULL THEN
2712 l_counter_instance_rec.counter_type := l_old_counter_instance_rec.counter_type;
2713 END IF;
2714 IF p_counter_instance_rec.initial_reading = FND_API.G_MISS_NUM THEN
2715 l_counter_instance_rec.initial_reading := NULL;
2716 ELSIF p_counter_instance_rec.initial_reading IS NULL THEN
2717 l_counter_instance_rec.initial_reading := l_old_counter_instance_rec.initial_reading;
2718 END IF;
2719 IF p_counter_instance_rec.initial_reading_date = FND_API.G_MISS_DATE THEN
2720 l_counter_instance_rec.initial_reading_date := NULL;
2721 ELSIF p_counter_instance_rec.initial_reading_date IS NULL THEN
2722 l_counter_instance_rec.initial_reading_date := l_old_counter_instance_rec.initial_reading_date;
2723 END IF;
2724 IF p_counter_instance_rec.created_from_counter_tmpl_id = FND_API.G_MISS_NUM THEN
2725 l_counter_instance_rec.created_from_counter_tmpl_id := NULL;
2726 ELSIF p_counter_instance_rec.created_from_counter_tmpl_id IS NULL THEN
2727 l_counter_instance_rec.created_from_counter_tmpl_id := l_old_counter_instance_rec.created_from_counter_tmpl_id;
2728 END IF;
2729 IF p_counter_instance_rec.tolerance_plus = FND_API.G_MISS_NUM THEN
2730 l_counter_instance_rec.tolerance_plus := NULL;
2731 ELSIF p_counter_instance_rec.tolerance_plus IS NULL THEN
2732 l_counter_instance_rec.tolerance_plus := l_old_counter_instance_rec.tolerance_plus;
2733 END IF;
2734 IF p_counter_instance_rec.tolerance_minus = FND_API.G_MISS_NUM THEN
2735 l_counter_instance_rec.tolerance_minus := NULL;
2736 ELSIF p_counter_instance_rec.tolerance_minus IS NULL THEN
2737 l_counter_instance_rec.tolerance_minus := l_old_counter_instance_rec.tolerance_minus;
2738 END IF;
2739 IF p_counter_instance_rec.uom_code = FND_API.G_MISS_CHAR THEN
2740 l_counter_instance_rec.uom_code := NULL;
2741 ELSIF p_counter_instance_rec.uom_code IS NULL THEN
2742 l_counter_instance_rec.uom_code := l_old_counter_instance_rec.uom_code;
2743 END IF;
2744 IF p_counter_instance_rec.derive_counter_id = FND_API.G_MISS_NUM THEN
2745 l_counter_instance_rec.derive_counter_id := NULL;
2746 ELSIF p_counter_instance_rec.derive_counter_id IS NULL THEN
2747 l_counter_instance_rec.derive_counter_id := l_old_counter_instance_rec.derive_counter_id;
2748 END IF;
2749 IF p_counter_instance_rec.derive_function = FND_API.G_MISS_CHAR THEN
2750 l_counter_instance_rec.derive_function := NULL;
2751 ELSIF p_counter_instance_rec.derive_function IS NULL THEN
2752 l_counter_instance_rec.derive_function := l_old_counter_instance_rec.derive_function;
2753 END IF;
2754 IF p_counter_instance_rec.valid_flag = FND_API.G_MISS_CHAR THEN
2755 l_counter_instance_rec.valid_flag := NULL;
2756 ELSIF p_counter_instance_rec.valid_flag IS NULL THEN
2757 l_counter_instance_rec.valid_flag := l_old_counter_instance_rec.valid_flag;
2758 END IF;
2759 IF p_counter_instance_rec.formula_incomplete_flag = FND_API.G_MISS_CHAR THEN
2760 l_counter_instance_rec.formula_incomplete_flag := NULL;
2761 ELSIF p_counter_instance_rec.formula_incomplete_flag IS NULL THEN
2762 l_counter_instance_rec.formula_incomplete_flag := l_old_counter_instance_rec.formula_incomplete_flag;
2763 END IF;
2764 IF p_counter_instance_rec.formula_text = FND_API.G_MISS_CHAR THEN
2765 l_counter_instance_rec.formula_text := NULL;
2766 ELSIF p_counter_instance_rec.formula_text IS NULL THEN
2767 l_counter_instance_rec.formula_text := l_old_counter_instance_rec.formula_text;
2768 END IF;
2769 IF p_counter_instance_rec.rollover_last_reading = FND_API.G_MISS_NUM THEN
2770 l_counter_instance_rec.rollover_last_reading := NULL;
2771 ELSIF p_counter_instance_rec.rollover_last_reading IS NULL THEN
2772 l_counter_instance_rec.rollover_last_reading := l_old_counter_instance_rec.rollover_last_reading;
2773 END IF;
2774 IF p_counter_instance_rec.rollover_first_reading = FND_API.G_MISS_NUM THEN
2775 l_counter_instance_rec.rollover_first_reading := NULL;
2776 ELSIF p_counter_instance_rec.rollover_first_reading IS NULL THEN
2777 l_counter_instance_rec.rollover_first_reading := l_old_counter_instance_rec.rollover_first_reading;
2778 END IF;
2779 IF p_counter_instance_rec.usage_item_id = FND_API.G_MISS_NUM THEN
2780 l_counter_instance_rec.usage_item_id := NULL;
2781 ELSIF p_counter_instance_rec.usage_item_id IS NULL THEN
2782 l_counter_instance_rec.usage_item_id := l_old_counter_instance_rec.usage_item_id;
2783 END IF;
2784 IF p_counter_instance_rec.ctr_val_max_seq_no = FND_API.G_MISS_NUM THEN
2785 l_counter_instance_rec.ctr_val_max_seq_no := NULL;
2786 ELSIF p_counter_instance_rec.ctr_val_max_seq_no IS NULL THEN
2787 l_counter_instance_rec.ctr_val_max_seq_no := l_old_counter_instance_rec.ctr_val_max_seq_no;
2788 END IF;
2789 IF p_counter_instance_rec.start_date_active = FND_API.G_MISS_DATE THEN
2790 l_counter_instance_rec.start_date_active := NULL;
2791 ELSIF p_counter_instance_rec.start_date_active IS NULL THEN
2792 l_counter_instance_rec.start_date_active := l_old_counter_instance_rec.start_date_active;
2793 END IF;
2794 IF p_counter_instance_rec.end_date_active = FND_API.G_MISS_DATE THEN
2795 l_counter_instance_rec.end_date_active := NULL;
2796 ELSIF p_counter_instance_rec.end_date_active IS NULL THEN
2797 l_counter_instance_rec.end_date_active := l_old_counter_instance_rec.end_date_active;
2798 END IF;
2799 IF p_counter_instance_rec.attribute1 = FND_API.G_MISS_CHAR THEN
2800 l_counter_instance_rec.attribute1 := NULL;
2801 ELSIF p_counter_instance_rec.attribute1 IS NULL THEN
2802 l_counter_instance_rec.attribute1 := l_old_counter_instance_rec.attribute1;
2803 END IF;
2804 IF p_counter_instance_rec.attribute2 = FND_API.G_MISS_CHAR THEN
2805 l_counter_instance_rec.attribute2 := NULL;
2806 ELSIF p_counter_instance_rec.attribute2 IS NULL THEN
2807 l_counter_instance_rec.attribute2 := l_old_counter_instance_rec.attribute2;
2808 END IF;
2809 IF p_counter_instance_rec.attribute3 = FND_API.G_MISS_CHAR THEN
2810 l_counter_instance_rec.attribute3 := NULL;
2811 ELSIF p_counter_instance_rec.attribute3 IS NULL THEN
2812 l_counter_instance_rec.attribute3 := l_old_counter_instance_rec.attribute3;
2813 END IF;
2814 IF p_counter_instance_rec.attribute4 = FND_API.G_MISS_CHAR THEN
2815 l_counter_instance_rec.attribute4 := NULL;
2816 ELSIF p_counter_instance_rec.attribute4 IS NULL THEN
2817 l_counter_instance_rec.attribute4 := l_old_counter_instance_rec.attribute4;
2818 END IF;
2819 IF p_counter_instance_rec.attribute5 = FND_API.G_MISS_CHAR THEN
2820 l_counter_instance_rec.attribute5 := NULL;
2821 ELSIF p_counter_instance_rec.attribute5 IS NULL THEN
2822 l_counter_instance_rec.attribute5 := l_old_counter_instance_rec.attribute5;
2823 END IF;
2824 IF p_counter_instance_rec.attribute6 = FND_API.G_MISS_CHAR THEN
2825 l_counter_instance_rec.attribute6 := NULL;
2826 ELSIF p_counter_instance_rec.attribute6 IS NULL THEN
2827 l_counter_instance_rec.attribute6 := l_old_counter_instance_rec.attribute6;
2828 END IF;
2829 IF p_counter_instance_rec.attribute7 = FND_API.G_MISS_CHAR THEN
2830 l_counter_instance_rec.attribute7 := NULL;
2831 ELSIF p_counter_instance_rec.attribute7 IS NULL THEN
2832 l_counter_instance_rec.attribute7 := l_old_counter_instance_rec.attribute7;
2833 END IF;
2834 IF p_counter_instance_rec.attribute8 = FND_API.G_MISS_CHAR THEN
2835 l_counter_instance_rec.attribute8 := NULL;
2836 ELSIF p_counter_instance_rec.attribute8 IS NULL THEN
2837 l_counter_instance_rec.attribute8 := l_old_counter_instance_rec.attribute8;
2838 END IF;
2839 IF p_counter_instance_rec.attribute9 = FND_API.G_MISS_CHAR THEN
2840 l_counter_instance_rec.attribute9 := NULL;
2841 ELSIF p_counter_instance_rec.attribute9 IS NULL THEN
2842 l_counter_instance_rec.attribute9 := l_old_counter_instance_rec.attribute9;
2843 END IF;
2844 IF p_counter_instance_rec.attribute10 = FND_API.G_MISS_CHAR THEN
2845 l_counter_instance_rec.attribute10 := NULL;
2846 ELSIF p_counter_instance_rec.attribute10 IS NULL THEN
2847 l_counter_instance_rec.attribute10 := l_old_counter_instance_rec.attribute10;
2848 END IF;
2849 IF p_counter_instance_rec.attribute11 = FND_API.G_MISS_CHAR THEN
2850 l_counter_instance_rec.attribute11 := NULL;
2851 ELSIF p_counter_instance_rec.attribute11 IS NULL THEN
2852 l_counter_instance_rec.attribute11 := l_old_counter_instance_rec.attribute11;
2853 END IF;
2854 IF p_counter_instance_rec.attribute12 = FND_API.G_MISS_CHAR THEN
2855 l_counter_instance_rec.attribute12 := NULL;
2856 ELSIF p_counter_instance_rec.attribute12 IS NULL THEN
2857 l_counter_instance_rec.attribute12 := l_old_counter_instance_rec.attribute12;
2858 END IF;
2859 IF p_counter_instance_rec.attribute13 = FND_API.G_MISS_CHAR THEN
2860 l_counter_instance_rec.attribute13 := NULL;
2861 ELSIF p_counter_instance_rec.attribute13 IS NULL THEN
2862 l_counter_instance_rec.attribute13 := l_old_counter_instance_rec.attribute13;
2863 END IF;
2864 IF p_counter_instance_rec.attribute14 = FND_API.G_MISS_CHAR THEN
2865 l_counter_instance_rec.attribute14 := NULL;
2866 ELSIF p_counter_instance_rec.attribute14 IS NULL THEN
2867 l_counter_instance_rec.attribute14 := l_old_counter_instance_rec.attribute14;
2868 END IF;
2869 IF p_counter_instance_rec.attribute15 = FND_API.G_MISS_CHAR THEN
2870 l_counter_instance_rec.attribute15 := NULL;
2871 ELSIF p_counter_instance_rec.attribute15 IS NULL THEN
2872 l_counter_instance_rec.attribute15 := l_old_counter_instance_rec.attribute15;
2873 END IF;
2874 IF p_counter_instance_rec.attribute16 = FND_API.G_MISS_CHAR THEN
2875 l_counter_instance_rec.attribute16 := NULL;
2876 ELSIF p_counter_instance_rec.attribute16 IS NULL THEN
2877 l_counter_instance_rec.attribute16 := l_old_counter_instance_rec.attribute16;
2878 END IF;
2879 IF p_counter_instance_rec.attribute17 = FND_API.G_MISS_CHAR THEN
2880 l_counter_instance_rec.attribute17 := NULL;
2881 ELSIF p_counter_instance_rec.attribute17 IS NULL THEN
2882 l_counter_instance_rec.attribute17 := l_old_counter_instance_rec.attribute17;
2883 END IF;
2884 IF p_counter_instance_rec.attribute18 = FND_API.G_MISS_CHAR THEN
2885 l_counter_instance_rec.attribute18 := NULL;
2886 ELSIF p_counter_instance_rec.attribute18 IS NULL THEN
2887 l_counter_instance_rec.attribute18 := l_old_counter_instance_rec.attribute18;
2888 END IF;
2889 IF p_counter_instance_rec.attribute19 = FND_API.G_MISS_CHAR THEN
2890 l_counter_instance_rec.attribute19 := NULL;
2891 ELSIF p_counter_instance_rec.attribute19 IS NULL THEN
2892 l_counter_instance_rec.attribute19 := l_old_counter_instance_rec.attribute19;
2893 END IF;
2894 IF p_counter_instance_rec.attribute20 = FND_API.G_MISS_CHAR THEN
2895 l_counter_instance_rec.attribute20 := NULL;
2896 ELSIF p_counter_instance_rec.attribute20 IS NULL THEN
2897 l_counter_instance_rec.attribute20 := l_old_counter_instance_rec.attribute20;
2898 END IF;
2899 IF p_counter_instance_rec.attribute21 = FND_API.G_MISS_CHAR THEN
2900 l_counter_instance_rec.attribute21 := NULL;
2901 ELSIF p_counter_instance_rec.attribute21 IS NULL THEN
2902 l_counter_instance_rec.attribute21 := l_old_counter_instance_rec.attribute21;
2903 END IF;
2904 IF p_counter_instance_rec.attribute22 = FND_API.G_MISS_CHAR THEN
2905 l_counter_instance_rec.attribute22 := NULL;
2906 ELSIF p_counter_instance_rec.attribute22 IS NULL THEN
2907 l_counter_instance_rec.attribute22 := l_old_counter_instance_rec.attribute22;
2908 END IF;
2909 IF p_counter_instance_rec.attribute23 = FND_API.G_MISS_CHAR THEN
2910 l_counter_instance_rec.attribute23 := NULL;
2911 ELSIF p_counter_instance_rec.attribute23 IS NULL THEN
2912 l_counter_instance_rec.attribute23 := l_old_counter_instance_rec.attribute23;
2913 END IF;
2914 IF p_counter_instance_rec.attribute24 = FND_API.G_MISS_CHAR THEN
2915 l_counter_instance_rec.attribute24 := NULL;
2916 ELSIF p_counter_instance_rec.attribute24 IS NULL THEN
2917 l_counter_instance_rec.attribute24 := l_old_counter_instance_rec.attribute24;
2918 END IF;
2919 IF p_counter_instance_rec.attribute25 = FND_API.G_MISS_CHAR THEN
2920 l_counter_instance_rec.attribute25 := NULL;
2921 ELSIF p_counter_instance_rec.attribute25 IS NULL THEN
2922 l_counter_instance_rec.attribute25 := l_old_counter_instance_rec.attribute25;
2923 END IF;
2924 IF p_counter_instance_rec.attribute26 = FND_API.G_MISS_CHAR THEN
2925 l_counter_instance_rec.attribute26 := NULL;
2926 ELSIF p_counter_instance_rec.attribute26 IS NULL THEN
2927 l_counter_instance_rec.attribute26 := l_old_counter_instance_rec.attribute26;
2928 END IF;
2929 IF p_counter_instance_rec.attribute27 = FND_API.G_MISS_CHAR THEN
2930 l_counter_instance_rec.attribute27 := NULL;
2931 ELSIF p_counter_instance_rec.attribute27 IS NULL THEN
2932 l_counter_instance_rec.attribute27 := l_old_counter_instance_rec.attribute27;
2933 END IF;
2934 IF p_counter_instance_rec.attribute28 = FND_API.G_MISS_CHAR THEN
2935 l_counter_instance_rec.attribute28 := NULL;
2936 ELSIF p_counter_instance_rec.attribute28 IS NULL THEN
2937 l_counter_instance_rec.attribute28 := l_old_counter_instance_rec.attribute28;
2938 END IF;
2939 IF p_counter_instance_rec.attribute29 = FND_API.G_MISS_CHAR THEN
2940 l_counter_instance_rec.attribute29 := NULL;
2941 ELSIF p_counter_instance_rec.attribute29 IS NULL THEN
2942 l_counter_instance_rec.attribute29 := l_old_counter_instance_rec.attribute29;
2943 END IF;
2944 IF p_counter_instance_rec.attribute30 = FND_API.G_MISS_CHAR THEN
2945 l_counter_instance_rec.attribute30 := NULL;
2946 ELSIF p_counter_instance_rec.attribute30 IS NULL THEN
2947 l_counter_instance_rec.attribute30 := l_old_counter_instance_rec.attribute30;
2948 END IF;
2949 IF p_counter_instance_rec.attribute_category = FND_API.G_MISS_CHAR THEN
2950 l_counter_instance_rec.attribute_category := NULL;
2951 ELSIF p_counter_instance_rec.attribute_category IS NULL THEN
2952 l_counter_instance_rec.attribute_category := l_old_counter_instance_rec.attribute_category;
2953 END IF;
2954 IF p_counter_instance_rec.customer_view = FND_API.G_MISS_CHAR THEN
2955 l_counter_instance_rec.customer_view := NULL;
2956 ELSIF p_counter_instance_rec.customer_view IS NULL THEN
2957 l_counter_instance_rec.customer_view := l_old_counter_instance_rec.customer_view;
2958 END IF;
2959 IF p_counter_instance_rec.direction = FND_API.G_MISS_CHAR THEN
2960 l_counter_instance_rec.direction := NULL;
2961 ELSIF p_counter_instance_rec.direction IS NULL THEN
2962 l_counter_instance_rec.direction := l_old_counter_instance_rec.direction;
2963 END IF;
2964 IF p_counter_instance_rec.filter_type = FND_API.G_MISS_CHAR THEN
2965 l_counter_instance_rec.filter_type := NULL;
2966 ELSIF p_counter_instance_rec.filter_type IS NULL THEN
2967 l_counter_instance_rec.filter_type := l_old_counter_instance_rec.filter_type;
2968 END IF;
2969 IF p_counter_instance_rec.filter_reading_count = FND_API.G_MISS_NUM THEN
2970 l_counter_instance_rec.filter_reading_count := NULL;
2971 ELSIF p_counter_instance_rec.filter_reading_count IS NULL THEN
2972 l_counter_instance_rec.filter_reading_count := l_old_counter_instance_rec.filter_reading_count;
2973 END IF;
2974 IF p_counter_instance_rec.filter_time_uom = FND_API.G_MISS_CHAR THEN
2975 l_counter_instance_rec.filter_time_uom := NULL;
2976 ELSIF p_counter_instance_rec.filter_time_uom IS NULL THEN
2977 l_counter_instance_rec.filter_time_uom := l_old_counter_instance_rec.filter_time_uom;
2978 END IF;
2979 IF p_counter_instance_rec.estimation_id = FND_API.G_MISS_NUM THEN
2980 l_counter_instance_rec.estimation_id := NULL;
2981 ELSIF p_counter_instance_rec.estimation_id IS NULL THEN
2982 l_counter_instance_rec.estimation_id := l_old_counter_instance_rec.estimation_id;
2983 END IF;
2984
2985 IF p_counter_instance_rec.time_based_manual_entry = FND_API.G_MISS_CHAR THEN
2986 l_counter_instance_rec.time_based_manual_entry := NULL;
2987 ELSIF p_counter_instance_rec.time_based_manual_entry IS NULL THEN
2988 l_counter_instance_rec.time_based_manual_entry := l_old_counter_instance_rec.time_based_manual_entry;
2989 END IF;
2990
2991 IF p_counter_instance_rec.reading_type = FND_API.G_MISS_NUM THEN
2992 l_counter_instance_rec.reading_type := NULL;
2993 ELSIF p_counter_instance_rec.reading_type IS NULL THEN
2994 l_counter_instance_rec.reading_type := l_old_counter_instance_rec.reading_type;
2995 END IF;
2996 IF p_counter_instance_rec.automatic_rollover = FND_API.G_MISS_CHAR THEN
2997 l_counter_instance_rec.automatic_rollover := NULL;
2998 ELSIF p_counter_instance_rec.automatic_rollover IS NULL THEN
2999 l_counter_instance_rec.automatic_rollover := l_old_counter_instance_rec.automatic_rollover;
3000 END IF;
3001 IF p_counter_instance_rec.default_usage_rate = FND_API.G_MISS_NUM THEN
3002 l_counter_instance_rec.default_usage_rate := NULL;
3003 ELSIF p_counter_instance_rec.default_usage_rate IS NULL THEN
3004 l_counter_instance_rec.default_usage_rate := l_old_counter_instance_rec.default_usage_rate;
3005 END IF;
3006 IF p_counter_instance_rec.use_past_reading = FND_API.G_MISS_NUM THEN
3007 l_counter_instance_rec.use_past_reading := NULL;
3008 ELSIF p_counter_instance_rec.use_past_reading IS NULL THEN
3009 l_counter_instance_rec.use_past_reading := l_old_counter_instance_rec.use_past_reading;
3010 END IF;
3011 IF p_counter_instance_rec.used_in_scheduling = FND_API.G_MISS_CHAR THEN
3012 l_counter_instance_rec.used_in_scheduling := NULL;
3013 ELSIF p_counter_instance_rec.used_in_scheduling IS NULL THEN
3014 l_counter_instance_rec.used_in_scheduling := l_old_counter_instance_rec.used_in_scheduling;
3015 END IF;
3016 IF p_counter_instance_rec.defaulted_group_id = FND_API.G_MISS_NUM THEN
3017 l_counter_instance_rec.defaulted_group_id := NULL;
3018 ELSIF p_counter_instance_rec.defaulted_group_id IS NULL THEN
3019 l_counter_instance_rec.defaulted_group_id := l_old_counter_instance_rec.defaulted_group_id;
3020 END IF;
3021 IF p_counter_instance_rec.name = FND_API.G_MISS_CHAR THEN
3022 l_counter_instance_rec.name := NULL;
3023 ELSIF p_counter_instance_rec.name IS NULL THEN
3024 l_counter_instance_rec.name := l_old_counter_instance_rec.name;
3025 END IF;
3026 IF p_counter_instance_rec.comments = FND_API.G_MISS_CHAR THEN
3027 l_counter_instance_rec.comments := NULL;
3028 ELSIF p_counter_instance_rec.comments IS NULL THEN
3029 l_counter_instance_rec.comments := l_old_counter_instance_rec.comments;
3030 END IF;
3031 IF p_counter_instance_rec.step_value = FND_API.G_MISS_NUM THEN
3032 l_counter_instance_rec.step_value := NULL;
3033 ELSIF p_counter_instance_rec.step_value IS NULL THEN
3034 l_counter_instance_rec.step_value := l_old_counter_instance_rec.step_value;
3035 END IF;
3036 IF p_counter_instance_rec.object_version_number = FND_API.G_MISS_NUM THEN
3037 l_counter_instance_rec.object_version_number := NULL;
3038 ELSIF p_counter_instance_rec.object_version_number IS NULL THEN
3039 l_counter_instance_rec.object_version_number := l_old_counter_instance_rec.object_version_number;
3040 END IF;
3041
3042 -- compare object version number
3043 IF l_old_counter_instance_rec.object_version_number <> nvl(l_counter_instance_rec.object_version_number,0) THEN
3044 csi_ctr_gen_utility_pvt.put_line('Object version mismatch in update counter');
3045 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_OBJ_VER_MISMATCH');
3046 END IF;
3047
3048 IF p_counter_instance_rec.eam_required_flag = FND_API.G_MISS_CHAR THEN
3049 l_counter_instance_rec.eam_required_flag := NULL;
3050 ELSIF p_counter_instance_rec.eam_required_flag IS NULL THEN
3051 l_counter_instance_rec.eam_required_flag := l_old_counter_instance_rec.eam_required_flag;
3052 END IF;
3053
3054 -- Check the validation for used in scheduling flag
3055 IF l_old_counter_instance_rec.used_in_scheduling = 'Y' AND l_counter_instance_rec.used_in_scheduling = 'N' THEN
3056 Eam_Meters_Util.Validate_Used_In_Scheduling
3057 (
3058 p_meter_id => p_counter_instance_rec.counter_id,
3059 X_return_status => l_return_status,
3060 X_msg_count => l_msg_count,
3061 X_msg_data => l_msg_data
3062 );
3063 IF NOT (l_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3064 csi_ctr_gen_utility_pvt.put_line('used in scheduling cannot be updated');
3065 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_USEDINSCHED_NOT_UPDT');
3066 END IF;
3067
3068 END IF;
3069
3070 -- Counter group is not updateable
3071 /*
3072 IF l_counter_instance_rec.group_id <> l_old_counter_instance_rec.group_id THEN
3073 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_GRP_NOT_UPDATABLE');
3074 END IF;
3075 */
3076 IF l_counter_instance_rec.counter_type <> l_old_counter_instance_rec.counter_type THEN
3077 IF l_old_counter_instance_rec.counter_type = 'FORMULA'
3078 and l_old_counter_instance_rec.derive_function is null THEN
3079 OPEN formula_ref_cur(p_counter_instance_rec.counter_id);
3080 FETCH formula_ref_cur INTO l_formula_ref_count;
3081 CLOSE formula_ref_cur;
3082 IF l_formula_ref_count is not null then
3083 -- Formula references exist for this counter. You cannot
3084 -- change the type to something different.
3085 csi_ctr_gen_utility_pvt.put_line('Formula References exist for this counter. Cannot change counter type...');
3086 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_FMLA_REF_EXIST','CTR_NAME',l_counter_instance_rec.name);
3087 END IF;
3088 ELSIF l_old_counter_instance_rec.counter_type = 'FORMULA'
3089 and l_old_counter_instance_rec.derive_function in ('SUM','COUNT') THEN
3090 OPEN derived_filters_cur(p_counter_instance_rec.counter_id);
3091 FETCH derived_filters_cur INTO l_der_filter_count;
3092 CLOSE derived_filters_cur;
3093 IF l_der_filter_count is not null then
3094 -- Derived filters exist for this counter. You cannot
3095 -- change the type to something different.
3096 csi_ctr_gen_utility_pvt.put_line('Derived Filters exist for this counter. Cannot change counter type...');
3097 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_DER_FILTER_EXIST','CTR_NAME',l_counter_instance_rec.name);
3098 END IF;
3099 ELSIF l_old_counter_instance_rec.counter_type = 'REGULAR' THEN
3100 OPEN target_counter_cur(p_counter_instance_rec.counter_id);
3101 FETCH target_counter_cur INTO l_target_ctr_exist;
3102 CLOSE target_counter_cur;
3103 IF l_target_ctr_exist is not null then
3104 -- Target counters exist for this counter. You cannot
3105 -- change the type to something different.
3106 csi_ctr_gen_utility_pvt.put_line('Target Counters exist for this counter. Cannot change counter type...');
3107 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_CONFIG_CTR_EXIST','CTR_NAME',l_counter_instance_rec.name);
3108 END IF;
3109 END IF;
3110 END IF;
3111
3112 -- Validate reading type. Reading type cannot be changed if readings exist
3113 IF l_counter_instance_rec.reading_type <> l_old_counter_instance_rec.reading_type THEN
3114 OPEN counter_readings_cur(p_counter_instance_rec.counter_id);
3115 FETCH counter_readings_cur INTO l_rdg_exists;
3116 CLOSE counter_readings_cur;
3117 IF l_rdg_exists is not null then
3118 -- Counter readings exist for this counter. You cannot
3119 -- change the reading type to something different.counter.
3120 csi_ctr_gen_utility_pvt.put_line('Counter readings exist for this counter. Cannot change reading type...');
3121 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_RDGS_EXIST','CTR_NAME',l_counter_instance_rec.name);
3122 END IF;
3123 END IF;
3124
3125 -- check and update name
3126 IF l_counter_instance_rec.name <> l_old_counter_instance_rec.name THEN
3127 -- l_new_name_instr := instrb(l_counter_instance_rec.name,'-',-1,1);
3128 -- l_old_name_instr := instrb(l_old_counter_instance_rec.name,'-',-1,1);
3129 -- if l_new_name_instr <> 0 and l_old_name_instr <> 0 then
3130 -- if substrb(l_counter_instance_rec.name,l_new_name_instr+1) <> substrb(l_old_counter_instance_rec.name,l_old_name_instr+1) then
3131 -- l_counter_instance_rec.name := l_counter_instance_rec.name||'-'||p_counter_instance_rec.counter_id;
3132 -- end if;
3133 -- els
3134 if (l_counter_instance_rec.created_from_counter_tmpl_id is not null) or (counter_name_exists(l_counter_instance_rec.name, l_counter_instance_rec.counter_id) ) THEN
3135 l_counter_instance_rec.name := l_counter_instance_rec.name||'-'||p_counter_instance_rec.counter_id;
3136 end if;
3137 END IF;
3138
3139 -- Validate start date
3140 IF l_counter_instance_rec.start_date_active IS NULL THEN
3141 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_STDATE_INVALID');
3142 ELSIF l_counter_instance_rec.start_date_active > sysdate THEN
3143 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_INVALID_START_DATE');
3144 END IF;
3145
3146 -- Validate counter name is unique
3147 Validate_Unique_ctr(l_counter_instance_rec.name, p_counter_instance_rec.counter_id);
3148
3149 -- Validate Counter
3150 validate_counter(l_counter_instance_rec.group_id, l_counter_instance_rec.name,
3151 l_counter_instance_rec.counter_type, l_counter_instance_rec.uom_code,
3152 l_counter_instance_rec.usage_item_id, l_counter_instance_rec.reading_type,
3153 l_counter_instance_rec.direction, l_counter_instance_rec.estimation_id,
3154 l_counter_instance_rec.derive_function, l_counter_instance_rec.formula_text,
3155 l_counter_instance_rec.derive_counter_id,l_counter_instance_rec.filter_type,
3156 l_counter_instance_rec.filter_reading_count, l_counter_instance_rec.filter_time_uom,
3157 l_counter_instance_rec.automatic_rollover, l_counter_instance_rec.rollover_last_reading,
3158 l_counter_instance_rec.rollover_first_reading, l_counter_instance_rec.tolerance_plus,
3159 l_counter_instance_rec.tolerance_minus, l_counter_instance_rec.used_in_scheduling,
3160 l_counter_instance_rec.initial_reading, l_counter_instance_rec.default_usage_rate,
3161 l_counter_instance_rec.use_past_reading, l_old_counter_instance_rec.counter_id,
3162 l_counter_instance_rec.start_date_active, l_counter_instance_rec.end_date_active
3163 );
3164
3165 -- call table handler here
3166
3167 CSI_COUNTERS_PKG.update_row
3168 (
3169 p_counter_id => p_counter_instance_rec.counter_id
3170 ,p_group_id => p_counter_instance_rec.group_id
3171 ,p_counter_type => p_counter_instance_rec.counter_type
3172 ,p_initial_reading => p_counter_instance_rec.initial_reading
3173 ,p_initial_reading_date => p_counter_instance_rec.initial_reading_date
3174 ,p_tolerance_plus => p_counter_instance_rec.tolerance_plus
3175 ,p_tolerance_minus => p_counter_instance_rec.tolerance_minus
3176 ,p_uom_code => p_counter_instance_rec.uom_code
3177 ,p_derive_counter_id => p_counter_instance_rec.derive_counter_id
3178 ,p_derive_function => p_counter_instance_rec.derive_function
3179 ,p_derive_property_id => p_counter_instance_rec.derive_property_id
3180 ,p_valid_flag => p_counter_instance_rec.valid_flag
3181 ,p_formula_incomplete_flag => p_counter_instance_rec.formula_incomplete_flag
3182 ,p_formula_text => p_counter_instance_rec.formula_text
3183 ,p_rollover_last_reading => p_counter_instance_rec.rollover_last_reading
3184 ,p_rollover_first_reading => p_counter_instance_rec.rollover_first_reading
3185 ,p_usage_item_id => p_counter_instance_rec.usage_item_id
3186 ,p_ctr_val_max_seq_no => p_counter_instance_rec.ctr_val_max_seq_no
3187 ,p_start_date_active => p_counter_instance_rec.start_date_active
3188 ,p_end_date_active => p_counter_instance_rec.end_date_active
3189 ,p_object_version_number => p_counter_instance_rec.object_version_number + 1
3190 ,p_last_update_date => sysdate
3191 ,p_last_updated_by => FND_GLOBAL.USER_ID
3192 ,p_creation_date => p_counter_instance_rec.creation_date
3193 ,p_created_by => p_counter_instance_rec.created_by
3194 ,p_last_update_login => FND_GLOBAL.USER_ID
3195 ,p_attribute1 => p_counter_instance_rec.attribute1
3196 ,p_attribute2 => p_counter_instance_rec.attribute2
3197 ,p_attribute3 => p_counter_instance_rec.attribute3
3198 ,p_attribute4 => p_counter_instance_rec.attribute4
3199 ,p_attribute5 => p_counter_instance_rec.attribute5
3200 ,p_attribute6 => p_counter_instance_rec.attribute6
3201 ,p_attribute7 => p_counter_instance_rec.attribute7
3202 ,p_attribute8 => p_counter_instance_rec.attribute8
3203 ,p_attribute9 => p_counter_instance_rec.attribute9
3204 ,p_attribute10 => p_counter_instance_rec.attribute10
3205 ,p_attribute11 => p_counter_instance_rec.attribute11
3206 ,p_attribute12 => p_counter_instance_rec.attribute12
3207 ,p_attribute13 => p_counter_instance_rec.attribute13
3208 ,p_attribute14 => p_counter_instance_rec.attribute14
3209 ,p_attribute15 => p_counter_instance_rec.attribute15
3210 ,p_attribute16 => p_counter_instance_rec.attribute16
3211 ,p_attribute17 => p_counter_instance_rec.attribute17
3212 ,p_attribute18 => p_counter_instance_rec.attribute18
3213 ,p_attribute19 => p_counter_instance_rec.attribute19
3214 ,p_attribute20 => p_counter_instance_rec.attribute20
3215 ,p_attribute21 => p_counter_instance_rec.attribute21
3216 ,p_attribute22 => p_counter_instance_rec.attribute22
3217 ,p_attribute23 => p_counter_instance_rec.attribute23
3218 ,p_attribute24 => p_counter_instance_rec.attribute24
3219 ,p_attribute25 => p_counter_instance_rec.attribute25
3220 ,p_attribute26 => p_counter_instance_rec.attribute26
3221 ,p_attribute27 => p_counter_instance_rec.attribute27
3222 ,p_attribute28 => p_counter_instance_rec.attribute28
3223 ,p_attribute29 => p_counter_instance_rec.attribute29
3224 ,p_attribute30 => p_counter_instance_rec.attribute30
3225 ,p_attribute_category => p_counter_instance_rec.attribute_category
3226 ,p_migrated_flag => p_counter_instance_rec.migrated_flag
3227 ,p_customer_view => p_counter_instance_rec.customer_view
3228 ,p_direction => p_counter_instance_rec.direction
3229 ,p_filter_type => p_counter_instance_rec.filter_type
3230 ,p_filter_reading_count => p_counter_instance_rec.filter_reading_count
3231 ,p_filter_time_uom => p_counter_instance_rec.filter_time_uom
3232 ,p_estimation_id => p_counter_instance_rec.estimation_id
3233 --,p_counter_code => p_counter_instance_rec.counter_code
3234 ,p_reading_type => p_counter_instance_rec.reading_type
3235 ,p_automatic_rollover => p_counter_instance_rec.automatic_rollover
3236 ,p_default_usage_rate => p_counter_instance_rec.default_usage_rate
3237 ,p_use_past_reading => p_counter_instance_rec.use_past_reading
3238 ,p_used_in_scheduling => p_counter_instance_rec.used_in_scheduling
3239 ,p_defaulted_group_id => p_counter_instance_rec.defaulted_group_id
3240 ,p_created_from_counter_tmpl_id => p_counter_instance_rec.created_from_counter_tmpl_id
3241 ,p_SECURITY_GROUP_ID => p_counter_instance_rec.SECURITY_GROUP_ID
3242 ,p_STEP_VALUE => p_counter_instance_rec.step_value
3243 ,p_name => l_counter_instance_rec.name
3244 ,p_description => p_counter_instance_rec.description
3245 ,p_time_based_manual_entry => p_counter_instance_rec.time_based_manual_entry
3246 ,p_eam_required_flag => p_counter_instance_rec.eam_required_flag
3247 ,p_comments => NULL
3248 );
3249 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3250 ROLLBACK TO update_counter_pvt;
3251 RETURN;
3252 END IF;
3253
3254 -- End of API body
3255
3256 -- Standard check of p_commit.
3257 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
3258 COMMIT WORK;
3259 END IF;
3260
3261 EXCEPTION
3262 WHEN FND_API.G_EXC_ERROR THEN
3263 x_return_status := FND_API.G_RET_STS_ERROR ;
3264 ROLLBACK TO update_counter_pvt;
3265 FND_MSG_PUB.Count_And_Get
3266 (p_count => x_msg_count,
3267 p_data => x_msg_data
3268 );
3269
3270 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3272 ROLLBACK TO update_counter_pvt;
3273 FND_MSG_PUB.Count_And_Get
3274 (p_count => x_msg_count,
3275 p_data => x_msg_data
3276 );
3277 WHEN OTHERS THEN
3278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3279 ROLLBACK TO update_counter_pvt;
3280 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3281 THEN
3282 FND_MSG_PUB.Add_Exc_Msg
3283 (G_PKG_NAME,
3284 l_api_name
3285 );
3286 END IF;
3287 FND_MSG_PUB.Count_And_Get
3288 (p_count => x_msg_count,
3289 p_data => x_msg_data
3290 );
3291 END update_counter;
3292
3293 --|---------------------------------------------------
3294 --| procedure name: update_ctr_property
3295 --| description : procedure used to
3296 --| update counter properties
3297 --|---------------------------------------------------
3298
3299 PROCEDURE update_ctr_property
3300 (
3301 p_api_version IN NUMBER
3302 ,p_commit IN VARCHAR2
3303 ,p_init_msg_list IN VARCHAR2
3304 ,p_validation_level IN NUMBER
3305 ,P_ctr_properties_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.Ctr_properties_rec
3306 ,x_return_status OUT NOCOPY VARCHAR2
3307 ,x_msg_count OUT NOCOPY NUMBER
3308 ,x_msg_data OUT NOCOPY VARCHAR2
3309 )
3310 IS
3311 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CTR_PROPERTY';
3312 l_api_version CONSTANT NUMBER := 1.0;
3313 -- l_debug_level NUMBER;
3314 l_flag VARCHAR2(1) := 'N';
3315 l_msg_data VARCHAR2(2000);
3316 l_msg_index NUMBER;
3317 l_msg_count NUMBER;
3318 l_count NUMBER;
3319 l_return_message VARCHAR2(100);
3320
3321 l_old_ctr_properties_rec CSI_CTR_DATASTRUCTURES_PUB.ctr_properties_rec;
3322 l_ctr_properties_rec CSI_CTR_DATASTRUCTURES_PUB.ctr_properties_rec;
3323 CURSOR prop_rdgs_cur(p_counter_property_id NUMBER) IS
3324 SELECT counter_prop_value_id
3325 FROM CSI_CTR_PROPERTY_READINGS
3326 WHERE counter_property_id = p_counter_property_id;
3327 l_prop_rdgs_exist NUMBER;
3328
3329 BEGIN
3330 -- Standard Start of API savepoint
3331 SAVEPOINT update_ctr_property_pvt;
3332
3333 -- Standard call to check for call compatibility.
3334 IF NOT FND_API.Compatible_API_Call (l_api_version,
3335 p_api_version,
3336 l_api_name ,
3337 G_PKG_NAME )
3338 THEN
3339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3340 END IF;
3341
3342 -- Initialize message list if p_init_msg_list is set to TRUE.
3343 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3344 FND_MSG_PUB.initialize;
3345 END IF;
3346
3347 -- Initialize API return status to success
3348 x_return_status := FND_API.G_RET_STS_SUCCESS;
3349
3350 -- Read the debug profiles values in to global variable 7197402
3351 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
3352
3353 -- Check the profile option debug_level for debug message reporting
3354 -- l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
3355
3356 -- If debug_level = 1 then dump the procedure name
3357 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 0) THEN
3358 csi_ctr_gen_utility_pvt.put_line( 'update_ctr_property');
3359 END IF;
3360
3361 -- If the debug level = 2 then dump all the parameters values.
3362 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 1) THEN
3363 csi_ctr_gen_utility_pvt.put_line( 'update_ctr_property' ||
3364 p_api_version ||'-'||
3365 p_commit ||'-'||
3366 p_init_msg_list ||'-'||
3367 p_validation_level );
3368 csi_ctr_gen_utility_pvt.dump_ctr_properties_rec(p_ctr_properties_rec);
3369 END IF;
3370
3371 -- Start of API Body
3372
3373 SELECT NAME
3374 ,DESCRIPTION
3375 ,COUNTER_ID
3376 ,PROPERTY_DATA_TYPE
3377 ,IS_NULLABLE
3378 ,DEFAULT_VALUE
3379 ,MINIMUM_VALUE
3380 , MAXIMUM_VALUE
3381 , UOM_CODE
3382 , START_DATE_ACTIVE
3383 , END_DATE_ACTIVE
3384 , ATTRIBUTE1
3385 , ATTRIBUTE2
3386 , ATTRIBUTE3
3387 , ATTRIBUTE4
3388 , ATTRIBUTE5
3389 , ATTRIBUTE6
3390 , ATTRIBUTE7
3391 , ATTRIBUTE8
3392 , ATTRIBUTE9
3393 , ATTRIBUTE10
3394 , ATTRIBUTE11
3395 , ATTRIBUTE12
3396 , ATTRIBUTE13
3397 , ATTRIBUTE14
3398 , ATTRIBUTE15
3399 , ATTRIBUTE_CATEGORY
3400 , PROPERTY_LOV_TYPE
3401 , object_version_number
3402 --, created_from_ctr_prop_tmpl_id
3403 INTO l_old_ctr_properties_rec.NAME
3404 ,l_old_ctr_properties_rec.DESCRIPTION
3405 ,l_old_ctr_properties_rec.COUNTER_ID
3406 ,l_old_ctr_properties_rec.PROPERTY_DATA_TYPE
3407 ,l_old_ctr_properties_rec.IS_NULLABLE
3408 ,l_old_ctr_properties_rec.DEFAULT_VALUE
3409 ,l_old_ctr_properties_rec.MINIMUM_VALUE
3410 ,l_old_ctr_properties_rec.MAXIMUM_VALUE
3411 ,l_old_ctr_properties_rec.UOM_CODE
3412 ,l_old_ctr_properties_rec.START_DATE_ACTIVE
3413 ,l_old_ctr_properties_rec.END_DATE_ACTIVE
3414 ,l_old_ctr_properties_rec.ATTRIBUTE1
3415 ,l_old_ctr_properties_rec.ATTRIBUTE2
3416 ,l_old_ctr_properties_rec.ATTRIBUTE3
3417 ,l_old_ctr_properties_rec.ATTRIBUTE4
3418 ,l_old_ctr_properties_rec.ATTRIBUTE5
3419 ,l_old_ctr_properties_rec.ATTRIBUTE6
3420 ,l_old_ctr_properties_rec.ATTRIBUTE7
3421 ,l_old_ctr_properties_rec.ATTRIBUTE8
3422 ,l_old_ctr_properties_rec.ATTRIBUTE9
3423 ,l_old_ctr_properties_rec.ATTRIBUTE10
3424 ,l_old_ctr_properties_rec.ATTRIBUTE11
3425 ,l_old_ctr_properties_rec.ATTRIBUTE12
3426 ,l_old_ctr_properties_rec.ATTRIBUTE13
3427 ,l_old_ctr_properties_rec.ATTRIBUTE14
3428 ,l_old_ctr_properties_rec.ATTRIBUTE15
3429 ,l_old_ctr_properties_rec.ATTRIBUTE_CATEGORY
3430 ,l_old_ctr_properties_rec.PROPERTY_LOV_TYPE
3431 ,l_old_ctr_properties_rec.object_version_number
3432 --,l_old_ctr_properties_rec.created_from_ctr_prop_tmpl_id
3433 FROM csi_counter_properties_vl
3434 WHERE counter_property_id = p_ctr_properties_rec.counter_property_id
3435 FOR UPDATE OF OBJECT_VERSION_NUMBER;
3436 IF SQL%NOTFOUND THEN
3437 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_PROP_INVALID');
3438 END IF;
3439
3440 l_ctr_properties_rec := p_ctr_properties_rec;
3441
3442 IF p_ctr_properties_rec.NAME = FND_API.G_MISS_CHAR THEN
3443 l_ctr_properties_rec.NAME := NULL;
3444 ELSIF p_ctr_properties_rec.NAME IS NULL THEN
3445 l_ctr_properties_rec.NAME := l_old_ctr_properties_rec.NAME;
3446 END IF;
3447 IF p_ctr_properties_rec.DESCRIPTION = FND_API.G_MISS_CHAR THEN
3448 l_ctr_properties_rec.DESCRIPTION := NULL;
3449 ELSIF p_ctr_properties_rec.DESCRIPTION IS NULL THEN
3450 l_ctr_properties_rec.DESCRIPTION := l_old_ctr_properties_rec.DESCRIPTION;
3451 END IF;
3452 IF p_ctr_properties_rec.COUNTER_ID = FND_API.G_MISS_NUM THEN
3453 l_ctr_properties_rec.COUNTER_ID := NULL;
3454 ELSIF p_ctr_properties_rec.COUNTER_ID IS NULL THEN
3455 l_ctr_properties_rec.COUNTER_ID := l_old_ctr_properties_rec.COUNTER_ID;
3456 END IF;
3457 IF p_ctr_properties_rec.PROPERTY_DATA_TYPE = FND_API.G_MISS_CHAR THEN
3458 l_ctr_properties_rec.PROPERTY_DATA_TYPE := NULL;
3459 ELSIF p_ctr_properties_rec.PROPERTY_DATA_TYPE IS NULL THEN
3460 l_ctr_properties_rec.PROPERTY_DATA_TYPE := l_old_ctr_properties_rec.PROPERTY_DATA_TYPE;
3461 END IF;
3462 IF p_ctr_properties_rec.IS_NULLABLE = FND_API.G_MISS_CHAR THEN
3463 l_ctr_properties_rec.IS_NULLABLE := NULL;
3464 ELSIF p_ctr_properties_rec.IS_NULLABLE IS NULL THEN
3465 l_ctr_properties_rec.IS_NULLABLE := l_old_ctr_properties_rec.IS_NULLABLE;
3466 END IF;
3467 IF p_ctr_properties_rec.DEFAULT_VALUE = FND_API.G_MISS_CHAR THEN
3468 l_ctr_properties_rec.DEFAULT_VALUE := NULL;
3469 ELSIF p_ctr_properties_rec.DEFAULT_VALUE IS NULL THEN
3470 l_ctr_properties_rec.DEFAULT_VALUE := l_old_ctr_properties_rec.DEFAULT_VALUE;
3471 END IF;
3472 IF p_ctr_properties_rec.MINIMUM_VALUE = FND_API.G_MISS_CHAR THEN
3473 l_ctr_properties_rec.MINIMUM_VALUE := NULL;
3474 ELSIF p_ctr_properties_rec.MINIMUM_VALUE IS NULL THEN
3475 l_ctr_properties_rec.MINIMUM_VALUE := l_old_ctr_properties_rec.MINIMUM_VALUE;
3476 END IF;
3477 IF p_ctr_properties_rec.MAXIMUM_VALUE = FND_API.G_MISS_CHAR THEN
3478 l_ctr_properties_rec.MAXIMUM_VALUE := NULL;
3479 ELSIF p_ctr_properties_rec.MAXIMUM_VALUE IS NULL THEN
3480 l_ctr_properties_rec.MAXIMUM_VALUE := l_old_ctr_properties_rec.MAXIMUM_VALUE;
3481 END IF;
3482 IF p_ctr_properties_rec.UOM_CODE = FND_API.G_MISS_CHAR THEN
3483 l_ctr_properties_rec.UOM_CODE := NULL;
3484 ELSIF p_ctr_properties_rec.UOM_CODE IS NULL THEN
3485 l_ctr_properties_rec.UOM_CODE := l_old_ctr_properties_rec.UOM_CODE;
3486 END IF;
3487 IF p_ctr_properties_rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE THEN
3488 l_ctr_properties_rec.START_DATE_ACTIVE := NULL;
3489 ELSIF p_ctr_properties_rec.START_DATE_ACTIVE IS NULL THEN
3490 l_ctr_properties_rec.START_DATE_ACTIVE := l_old_ctr_properties_rec.START_DATE_ACTIVE;
3491 END IF;
3492 IF p_ctr_properties_rec.END_DATE_ACTIVE = FND_API.G_MISS_DATE THEN
3493 l_ctr_properties_rec.END_DATE_ACTIVE := NULL;
3494 ELSIF p_ctr_properties_rec.END_DATE_ACTIVE IS NULL THEN
3495 l_ctr_properties_rec.END_DATE_ACTIVE := l_old_ctr_properties_rec.END_DATE_ACTIVE;
3496 END IF;
3497 IF p_ctr_properties_rec.PROPERTY_LOV_TYPE = FND_API.G_MISS_CHAR THEN
3498 l_ctr_properties_rec.PROPERTY_LOV_TYPE := NULL;
3499 ELSIF p_ctr_properties_rec.PROPERTY_LOV_TYPE IS NULL THEN
3500 l_ctr_properties_rec.PROPERTY_LOV_TYPE := l_old_ctr_properties_rec.PROPERTY_LOV_TYPE;
3501 END IF;
3502 IF p_ctr_properties_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR THEN
3503 l_ctr_properties_rec.ATTRIBUTE1 := NULL;
3504 ELSIF p_ctr_properties_rec.ATTRIBUTE1 IS NULL THEN
3505 l_ctr_properties_rec.ATTRIBUTE1 := l_old_ctr_properties_rec.ATTRIBUTE1;
3506 END IF;
3507 IF p_ctr_properties_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR THEN
3508 l_ctr_properties_rec.ATTRIBUTE2 := NULL;
3509 ELSIF p_ctr_properties_rec.ATTRIBUTE2 IS NULL THEN
3510 l_ctr_properties_rec.ATTRIBUTE2 := l_old_ctr_properties_rec.ATTRIBUTE2;
3511 END IF;
3512 IF p_ctr_properties_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR THEN
3513 l_ctr_properties_rec.ATTRIBUTE3 := NULL;
3514 ELSIF p_ctr_properties_rec.ATTRIBUTE3 IS NULL THEN
3515 l_ctr_properties_rec.ATTRIBUTE3 := l_old_ctr_properties_rec.ATTRIBUTE3;
3516 END IF;
3517 IF p_ctr_properties_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR THEN
3518 l_ctr_properties_rec.ATTRIBUTE4 := NULL;
3519 ELSIF p_ctr_properties_rec.ATTRIBUTE4 IS NULL THEN
3520 l_ctr_properties_rec.ATTRIBUTE4 := l_old_ctr_properties_rec.ATTRIBUTE4;
3521 END IF;
3522 IF p_ctr_properties_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR THEN
3523 l_ctr_properties_rec.ATTRIBUTE5 := NULL;
3524 ELSIF p_ctr_properties_rec.ATTRIBUTE5 IS NULL THEN
3525 l_ctr_properties_rec.ATTRIBUTE5 := l_old_ctr_properties_rec.ATTRIBUTE5;
3526 END IF;
3527 IF p_ctr_properties_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR THEN
3528 l_ctr_properties_rec.ATTRIBUTE6 := NULL;
3529 ELSIF p_ctr_properties_rec.ATTRIBUTE6 IS NULL THEN
3530 l_ctr_properties_rec.ATTRIBUTE6 := l_old_ctr_properties_rec.ATTRIBUTE6;
3531 END IF;
3532 IF p_ctr_properties_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR THEN
3533 l_ctr_properties_rec.ATTRIBUTE7 := NULL;
3534 ELSIF p_ctr_properties_rec.ATTRIBUTE7 IS NULL THEN
3535 l_ctr_properties_rec.ATTRIBUTE7 := l_old_ctr_properties_rec.ATTRIBUTE7;
3536 END IF;
3537 IF p_ctr_properties_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR THEN
3538 l_ctr_properties_rec.ATTRIBUTE8 := NULL;
3539 ELSIF p_ctr_properties_rec.ATTRIBUTE8 IS NULL THEN
3540 l_ctr_properties_rec.ATTRIBUTE8 := l_old_ctr_properties_rec.ATTRIBUTE8;
3541 END IF;
3542 IF p_ctr_properties_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR THEN
3543 l_ctr_properties_rec.ATTRIBUTE9 := NULL;
3544 ELSIF p_ctr_properties_rec.ATTRIBUTE9 IS NULL THEN
3545 l_ctr_properties_rec.ATTRIBUTE9 := l_old_ctr_properties_rec.ATTRIBUTE9;
3546 END IF;
3547 IF p_ctr_properties_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR THEN
3548 l_ctr_properties_rec.ATTRIBUTE10 := NULL;
3549 ELSIF p_ctr_properties_rec.ATTRIBUTE10 IS NULL THEN
3550 l_ctr_properties_rec.ATTRIBUTE10 := l_old_ctr_properties_rec.ATTRIBUTE10;
3551 END IF;
3552 IF p_ctr_properties_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR THEN
3553 l_ctr_properties_rec.ATTRIBUTE11 := NULL;
3554 ELSIF p_ctr_properties_rec.ATTRIBUTE11 IS NULL THEN
3555 l_ctr_properties_rec.ATTRIBUTE11 := l_old_ctr_properties_rec.ATTRIBUTE11;
3556 END IF;
3557 IF p_ctr_properties_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR THEN
3558 l_ctr_properties_rec.ATTRIBUTE12 := NULL;
3559 ELSIF p_ctr_properties_rec.ATTRIBUTE12 IS NULL THEN
3560 l_ctr_properties_rec.ATTRIBUTE12 := l_old_ctr_properties_rec.ATTRIBUTE12;
3561 END IF;
3562 IF p_ctr_properties_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR THEN
3563 l_ctr_properties_rec.ATTRIBUTE13 := NULL;
3564 ELSIF p_ctr_properties_rec.ATTRIBUTE13 IS NULL THEN
3565 l_ctr_properties_rec.ATTRIBUTE13 := l_old_ctr_properties_rec.ATTRIBUTE13;
3566 END IF;
3567 IF p_ctr_properties_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR THEN
3568 l_ctr_properties_rec.ATTRIBUTE14 := NULL;
3569 ELSIF p_ctr_properties_rec.ATTRIBUTE14 IS NULL THEN
3570 l_ctr_properties_rec.ATTRIBUTE14 := l_old_ctr_properties_rec.ATTRIBUTE14;
3571 END IF;
3572 IF p_ctr_properties_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR THEN
3573 l_ctr_properties_rec.ATTRIBUTE15 := NULL;
3574 ELSIF p_ctr_properties_rec.ATTRIBUTE15 IS NULL THEN
3575 l_ctr_properties_rec.ATTRIBUTE15 := l_old_ctr_properties_rec.ATTRIBUTE15;
3576 END IF;
3577 IF p_ctr_properties_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR THEN
3578 l_ctr_properties_rec.ATTRIBUTE_CATEGORY := NULL;
3579 ELSIF p_ctr_properties_rec.ATTRIBUTE_CATEGORY IS NULL THEN
3580 l_ctr_properties_rec.ATTRIBUTE_CATEGORY := l_old_ctr_properties_rec.ATTRIBUTE_CATEGORY;
3581 END IF;
3582 IF p_ctr_properties_rec.created_from_ctr_prop_tmpl_id = FND_API.G_MISS_NUM THEN
3583 l_ctr_properties_rec.created_from_ctr_prop_tmpl_id := NULL;
3584 ELSIF p_ctr_properties_rec.created_from_ctr_prop_tmpl_id IS NULL THEN
3585 l_ctr_properties_rec.created_from_ctr_prop_tmpl_id := l_old_ctr_properties_rec.created_from_ctr_prop_tmpl_id;
3586 END IF;
3587 IF p_ctr_properties_rec.object_version_number = FND_API.G_MISS_NUM THEN
3588 l_ctr_properties_rec.object_version_number := NULL;
3589 ELSIF p_ctr_properties_rec.object_version_number IS NULL THEN
3590 l_ctr_properties_rec.object_version_number := l_old_ctr_properties_rec.object_version_number;
3591 END IF;
3592
3593 IF l_old_ctr_properties_rec.object_version_number <> nvl(l_ctr_properties_rec.object_version_number,0) THEN
3594 csi_ctr_gen_utility_pvt.put_line('Object version mismatch in update counter');
3595 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_OBJ_VER_MISMATCH');
3596 END IF;
3597
3598 -- do validation here
3599
3600 IF l_ctr_properties_rec.counter_id <> l_old_ctr_properties_rec.counter_id THEN
3601 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_NOT_UPDATABLE');
3602 END IF;
3603
3604 -- validate unique property name within counter
3605 Validate_Unique_ctrprop(l_ctr_properties_rec.NAME,l_ctr_properties_rec.COUNTER_ID,p_ctr_properties_rec.counter_property_id);
3606
3607 IF l_ctr_properties_rec.PROPERTY_DATA_TYPE <> l_old_ctr_properties_rec.PROPERTY_DATA_TYPE THEN
3608 OPEN prop_rdgs_cur(p_ctr_properties_rec.counter_property_id);
3609 FETCH prop_rdgs_cur into l_prop_rdgs_exist;
3610 CLOSE prop_rdgs_cur;
3611 IF l_prop_rdgs_exist is not null then
3612 -- Counter property readings exist for this counter property. You cannot
3613 -- change the property datatype to something different.
3614 csi_ctr_gen_utility_pvt.put_line('Ctr prop rdgs exist for this ctr prop. Cannot change property datatype...');
3615 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_PROP_DTYP_NOT_UPD');
3616 END IF;
3617 END IF;
3618
3619 -- validate property date type in char,number,date
3620 if l_ctr_properties_rec.PROPERTY_DATA_TYPE not in ('CHAR', 'NUMBER', 'DATE') then
3621 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_PROP_DATA_TYPE');
3622 end if;
3623
3624 -- Validate start date
3625 IF l_ctr_properties_rec.START_DATE_ACTIVE IS NULL THEN
3626 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_STDATE_INVALID');
3627 ELSIF l_ctr_properties_rec.START_DATE_ACTIVE > sysdate THEN
3628 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_INVALID_START_DATE');
3629 END IF;
3630
3631 -- Validate values for data type
3632 Validate_Data_Type(l_ctr_properties_rec.PROPERTY_DATA_TYPE, l_ctr_properties_rec.default_value,
3633 l_ctr_properties_rec.minimum_value, l_ctr_properties_rec.maximum_value);
3634
3635 -- Validate uom
3636 if l_ctr_properties_rec.uom_code is not null then
3637 Validate_UOM(l_ctr_properties_rec.uom_code);
3638 end if;
3639
3640 -- call table handler here
3641
3642 CSI_COUNTER_PROPERTIES_PKG.update_row
3643 (
3644 p_counter_property_id => p_ctr_properties_rec.counter_property_id
3645 ,p_counter_id => p_ctr_properties_rec.counter_id
3646 ,p_property_data_type => p_ctr_properties_rec.property_data_type
3647 ,p_is_nullable => p_ctr_properties_rec.is_nullable
3648 ,p_default_value => p_ctr_properties_rec.default_value
3649 ,p_minimum_value => p_ctr_properties_rec.minimum_value
3650 ,p_maximum_value => p_ctr_properties_rec.maximum_value
3651 ,p_uom_code => p_ctr_properties_rec.uom_code
3652 ,p_start_date_active => p_ctr_properties_rec.start_date_active
3653 ,p_end_date_active => p_ctr_properties_rec.end_date_active
3654 ,p_object_version_number => p_ctr_properties_rec.object_version_number + 1
3655 ,p_SECURITY_GROUP_ID => null
3656 ,p_last_update_date => sysdate
3657 ,p_last_updated_by => FND_GLOBAL.USER_ID
3658 ,p_creation_date => p_ctr_properties_rec.creation_date
3659 ,p_created_by => p_ctr_properties_rec.created_by
3660 ,p_last_update_login => FND_GLOBAL.USER_ID
3661 ,p_attribute1 => p_ctr_properties_rec.attribute1
3662 ,p_attribute2 => p_ctr_properties_rec.attribute2
3663 ,p_attribute3 => p_ctr_properties_rec.attribute3
3664 ,p_attribute4 => p_ctr_properties_rec.attribute4
3665 ,p_attribute5 => p_ctr_properties_rec.attribute5
3666 ,p_attribute6 => p_ctr_properties_rec.attribute6
3667 ,p_attribute7 => p_ctr_properties_rec.attribute7
3668 ,p_attribute8 => p_ctr_properties_rec.attribute8
3669 ,p_attribute9 => p_ctr_properties_rec.attribute9
3670 ,p_attribute10 => p_ctr_properties_rec.attribute10
3671 ,p_attribute11 => p_ctr_properties_rec.attribute11
3672 ,p_attribute12 => p_ctr_properties_rec.attribute12
3673 ,p_attribute13 => p_ctr_properties_rec.attribute13
3674 ,p_attribute14 => p_ctr_properties_rec.attribute14
3675 ,p_attribute15 => p_ctr_properties_rec.attribute15
3676 ,p_attribute_category => p_ctr_properties_rec.attribute_category
3677 ,p_migrated_flag => p_ctr_properties_rec.migrated_flag
3678 ,p_property_lov_type => p_ctr_properties_rec.property_lov_type
3679 ,p_name => p_ctr_properties_rec.name
3680 ,p_description => p_ctr_properties_rec.description
3681 ,p_create_from_ctr_prop_tmpl_id => p_ctr_properties_rec.created_from_ctr_prop_tmpl_id
3682 );
3683 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3684 ROLLBACK TO update_ctr_property_pvt;
3685 RETURN;
3686 END IF;
3687
3688 -- End of API body
3689
3690 -- Standard check of p_commit.
3691 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
3692 COMMIT WORK;
3693 END IF;
3694
3695 EXCEPTION
3696 WHEN FND_API.G_EXC_ERROR THEN
3697 x_return_status := FND_API.G_RET_STS_ERROR ;
3698 ROLLBACK TO update_ctr_property_pvt;
3699 FND_MSG_PUB.Count_And_Get
3700 (p_count => x_msg_count,
3701 p_data => x_msg_data
3702 );
3703
3704 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3706 ROLLBACK TO update_ctr_property_pvt;
3707 FND_MSG_PUB.Count_And_Get
3708 (p_count => x_msg_count,
3709 p_data => x_msg_data
3710 );
3711 WHEN OTHERS THEN
3712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3713 ROLLBACK TO update_ctr_property_pvt;
3714 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3715 THEN
3716 FND_MSG_PUB.Add_Exc_Msg
3717 (G_PKG_NAME,
3718 l_api_name
3719 );
3720 END IF;
3721 FND_MSG_PUB.Count_And_Get
3722 (p_count => x_msg_count,
3723 p_data => x_msg_data
3724 );
3725 END update_ctr_property;
3726
3727 --|---------------------------------------------------
3728 --| procedure name: update_ctr_associations
3729 --| description : procedure used to
3730 --| update counter associations
3731 --|---------------------------------------------------
3732
3733 PROCEDURE update_ctr_associations
3734 (
3735 p_api_version IN NUMBER
3736 ,p_commit IN VARCHAR2
3737 ,p_init_msg_list IN VARCHAR2
3738 ,p_validation_level IN NUMBER
3739 ,P_counter_associations_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.counter_associations_rec
3740 ,x_return_status OUT NOCOPY VARCHAR2
3741 ,x_msg_count OUT NOCOPY NUMBER
3742 ,x_msg_data OUT NOCOPY VARCHAR2
3743 )
3744 IS
3745 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CTR_ASSOCIATIONS';
3746 l_api_version CONSTANT NUMBER := 1.0;
3747 -- l_debug_level NUMBER;
3748 l_flag VARCHAR2(1) := 'N';
3749 l_msg_data VARCHAR2(2000);
3750 l_msg_index NUMBER;
3751 l_msg_count NUMBER;
3752 l_count NUMBER;
3753 l_return_message VARCHAR2(100);
3754
3755 l_old_counter_associations_rec CSI_CTR_DATASTRUCTURES_PUB.counter_associations_rec;
3756 l_counter_associations_rec CSI_CTR_DATASTRUCTURES_PUB.counter_associations_rec;
3757
3758 BEGIN
3759 -- Standard Start of API savepoint
3760 SAVEPOINT update_ctr_associations_pvt;
3761
3762 -- Standard call to check for call compatibility.
3763 IF NOT FND_API.Compatible_API_Call (l_api_version,
3764 p_api_version,
3765 l_api_name ,
3766 G_PKG_NAME )
3767 THEN
3768 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3769 END IF;
3770
3771 -- Initialize message list if p_init_msg_list is set to TRUE.
3772 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3773 FND_MSG_PUB.initialize;
3774 END IF;
3775
3776 -- Initialize API return status to success
3777 x_return_status := FND_API.G_RET_STS_SUCCESS;
3778
3779 -- Read the debug profiles values in to global variable 7197402
3780 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
3781
3782 -- Check the profile option debug_level for debug message reporting
3783 -- l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
3784
3785 -- If debug_level = 1 then dump the procedure name
3786 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 0) THEN
3787 csi_gen_utility_pvt.put_line( 'update_ctr_associations');
3788 END IF;
3789
3790 -- If the debug level = 2 then dump all the parameters values.
3791 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 1) THEN
3792 csi_gen_utility_pvt.put_line( 'update_ctr_associations' ||
3793 p_api_version ||'-'||
3794 p_commit ||'-'||
3795 p_init_msg_list ||'-'||
3796 p_validation_level );
3797 csi_ctr_gen_utility_pvt.dump_counter_associations_rec(p_counter_associations_rec);
3798 END IF;
3799
3800 -- Start of API Body
3801 SELECT SOURCE_OBJECT_CODE
3802 , SOURCE_OBJECT_ID
3803 , COUNTER_ID
3804 , START_DATE_ACTIVE
3805 , END_DATE_ACTIVE
3806 , ATTRIBUTE1
3807 , ATTRIBUTE2
3808 , ATTRIBUTE3
3809 , ATTRIBUTE4
3810 , ATTRIBUTE5
3811 , ATTRIBUTE6
3812 , ATTRIBUTE7
3813 , ATTRIBUTE8
3814 , ATTRIBUTE9
3815 , ATTRIBUTE10
3816 , ATTRIBUTE11
3817 , ATTRIBUTE12
3818 , ATTRIBUTE13
3819 , ATTRIBUTE14
3820 , ATTRIBUTE15
3821 , ATTRIBUTE_CATEGORY
3822 , object_version_number
3823 , maint_organization_id
3824 , primary_failure_flag
3825 INTO l_old_counter_associations_rec.SOURCE_OBJECT_CODE
3826 , l_old_counter_associations_rec.SOURCE_OBJECT_ID
3827 , l_old_counter_associations_rec.COUNTER_ID
3828 , l_old_counter_associations_rec.START_DATE_ACTIVE
3829 , l_old_counter_associations_rec.END_DATE_ACTIVE
3830 , l_old_counter_associations_rec.ATTRIBUTE1
3831 , l_old_counter_associations_rec.ATTRIBUTE2
3832 , l_old_counter_associations_rec.ATTRIBUTE3
3833 , l_old_counter_associations_rec.ATTRIBUTE4
3834 , l_old_counter_associations_rec.ATTRIBUTE5
3835 , l_old_counter_associations_rec.ATTRIBUTE6
3836 , l_old_counter_associations_rec.ATTRIBUTE7
3837 , l_old_counter_associations_rec.ATTRIBUTE8
3838 , l_old_counter_associations_rec.ATTRIBUTE9
3839 , l_old_counter_associations_rec.ATTRIBUTE10
3840 , l_old_counter_associations_rec.ATTRIBUTE11
3841 , l_old_counter_associations_rec.ATTRIBUTE12
3842 , l_old_counter_associations_rec.ATTRIBUTE13
3843 , l_old_counter_associations_rec.ATTRIBUTE14
3844 , l_old_counter_associations_rec.ATTRIBUTE15
3845 , l_old_counter_associations_rec.ATTRIBUTE_CATEGORY
3846 , l_old_counter_associations_rec.object_version_number
3847 , l_old_counter_associations_rec.maint_organization_id
3848 , l_old_counter_associations_rec.primary_failure_flag
3849 FROM CSI_COUNTER_ASSOCIATIONS
3850 WHERE INSTANCE_ASSOCIATION_ID = P_counter_associations_rec.INSTANCE_ASSOCIATION_ID
3851 FOR UPDATE OF OBJECT_VERSION_NUMBER;
3852 IF SQL%NOTFOUND THEN
3853 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_ASSOC_INVALID');
3854 END IF;
3855
3856 l_counter_associations_rec := p_counter_associations_rec;
3857
3858 IF p_counter_associations_rec.SOURCE_OBJECT_CODE = FND_API.G_MISS_CHAR THEN
3859 l_counter_associations_rec.SOURCE_OBJECT_CODE := NULL;
3860 ELSIF p_counter_associations_rec.SOURCE_OBJECT_CODE IS NULL THEN
3861 l_counter_associations_rec.SOURCE_OBJECT_CODE := l_old_counter_associations_rec.SOURCE_OBJECT_CODE;
3862 END IF;
3863 IF p_counter_associations_rec.SOURCE_OBJECT_ID = FND_API.G_MISS_NUM THEN
3864 l_counter_associations_rec.SOURCE_OBJECT_ID := NULL;
3865 ELSIF p_counter_associations_rec.SOURCE_OBJECT_ID IS NULL THEN
3866 l_counter_associations_rec.SOURCE_OBJECT_ID := l_old_counter_associations_rec.SOURCE_OBJECT_ID;
3867 END IF;
3868 IF p_counter_associations_rec.COUNTER_ID = FND_API.G_MISS_NUM THEN
3869 l_counter_associations_rec.COUNTER_ID := NULL;
3870 ELSIF p_counter_associations_rec.COUNTER_ID IS NULL THEN
3871 l_counter_associations_rec.COUNTER_ID := l_old_counter_associations_rec.COUNTER_ID;
3872 END IF;
3873 IF p_counter_associations_rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE THEN
3874 l_counter_associations_rec.START_DATE_ACTIVE := NULL;
3875 ELSIF p_counter_associations_rec.START_DATE_ACTIVE IS NULL THEN
3876 l_counter_associations_rec.START_DATE_ACTIVE := l_old_counter_associations_rec.START_DATE_ACTIVE;
3877 END IF;
3878 IF p_counter_associations_rec.END_DATE_ACTIVE = FND_API.G_MISS_DATE THEN
3879 l_counter_associations_rec.END_DATE_ACTIVE := NULL;
3880 ELSIF p_counter_associations_rec.END_DATE_ACTIVE IS NULL THEN
3881 l_counter_associations_rec.END_DATE_ACTIVE := l_old_counter_associations_rec.END_DATE_ACTIVE;
3882 END IF;
3883 IF p_counter_associations_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR THEN
3884 l_counter_associations_rec.ATTRIBUTE1 := NULL;
3885 ELSIF p_counter_associations_rec.ATTRIBUTE1 IS NULL THEN
3886 l_counter_associations_rec.ATTRIBUTE1 := l_old_counter_associations_rec.ATTRIBUTE1;
3887 END IF;
3888 IF p_counter_associations_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR THEN
3889 l_counter_associations_rec.ATTRIBUTE2 := NULL;
3890 ELSIF p_counter_associations_rec.ATTRIBUTE2 IS NULL THEN
3891 l_counter_associations_rec.ATTRIBUTE2 := l_old_counter_associations_rec.ATTRIBUTE2;
3892 END IF;
3893 IF p_counter_associations_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR THEN
3894 l_counter_associations_rec.ATTRIBUTE3 := NULL;
3895 ELSIF p_counter_associations_rec.ATTRIBUTE3 IS NULL THEN
3896 l_counter_associations_rec.ATTRIBUTE3 := l_old_counter_associations_rec.ATTRIBUTE3;
3897 END IF;
3898 IF p_counter_associations_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR THEN
3899 l_counter_associations_rec.ATTRIBUTE4 := NULL;
3900 ELSIF p_counter_associations_rec.ATTRIBUTE4 IS NULL THEN
3901 l_counter_associations_rec.ATTRIBUTE4 := l_old_counter_associations_rec.ATTRIBUTE4;
3902 END IF;
3903 IF p_counter_associations_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR THEN
3904 l_counter_associations_rec.ATTRIBUTE5 := NULL;
3905 ELSIF p_counter_associations_rec.ATTRIBUTE5 IS NULL THEN
3906 l_counter_associations_rec.ATTRIBUTE5 := l_old_counter_associations_rec.ATTRIBUTE5;
3907 END IF;
3908 IF p_counter_associations_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR THEN
3909 l_counter_associations_rec.ATTRIBUTE6 := NULL;
3910 ELSIF p_counter_associations_rec.ATTRIBUTE6 IS NULL THEN
3911 l_counter_associations_rec.ATTRIBUTE6 := l_old_counter_associations_rec.ATTRIBUTE6;
3912 END IF;
3913 IF p_counter_associations_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR THEN
3914 l_counter_associations_rec.ATTRIBUTE7 := NULL;
3915 ELSIF p_counter_associations_rec.ATTRIBUTE7 IS NULL THEN
3916 l_counter_associations_rec.ATTRIBUTE7 := l_old_counter_associations_rec.ATTRIBUTE7;
3917 END IF;
3918 IF p_counter_associations_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR THEN
3919 l_counter_associations_rec.ATTRIBUTE8 := NULL;
3920 ELSIF p_counter_associations_rec.ATTRIBUTE8 IS NULL THEN
3921 l_counter_associations_rec.ATTRIBUTE8 := l_old_counter_associations_rec.ATTRIBUTE8;
3922 END IF;
3923 IF p_counter_associations_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR THEN
3924 l_counter_associations_rec.ATTRIBUTE9 := NULL;
3925 ELSIF p_counter_associations_rec.ATTRIBUTE9 IS NULL THEN
3926 l_counter_associations_rec.ATTRIBUTE9 := l_old_counter_associations_rec.ATTRIBUTE9;
3927 END IF;
3928 IF p_counter_associations_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR THEN
3929 l_counter_associations_rec.ATTRIBUTE10 := NULL;
3930 ELSIF p_counter_associations_rec.ATTRIBUTE10 IS NULL THEN
3931 l_counter_associations_rec.ATTRIBUTE10 := l_old_counter_associations_rec.ATTRIBUTE10;
3932 END IF;
3933 IF p_counter_associations_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR THEN
3934 l_counter_associations_rec.ATTRIBUTE11 := NULL;
3935 ELSIF p_counter_associations_rec.ATTRIBUTE11 IS NULL THEN
3936 l_counter_associations_rec.ATTRIBUTE11 := l_old_counter_associations_rec.ATTRIBUTE11;
3937 END IF;
3938 IF p_counter_associations_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR THEN
3939 l_counter_associations_rec.ATTRIBUTE12 := NULL;
3940 ELSIF p_counter_associations_rec.ATTRIBUTE12 IS NULL THEN
3941 l_counter_associations_rec.ATTRIBUTE12 := l_old_counter_associations_rec.ATTRIBUTE12;
3942 END IF;
3943 IF p_counter_associations_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR THEN
3944 l_counter_associations_rec.ATTRIBUTE13 := NULL;
3945 ELSIF p_counter_associations_rec.ATTRIBUTE13 IS NULL THEN
3946 l_counter_associations_rec.ATTRIBUTE13 := l_old_counter_associations_rec.ATTRIBUTE13;
3947 END IF;
3948 IF p_counter_associations_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR THEN
3949 l_counter_associations_rec.ATTRIBUTE14 := NULL;
3950 ELSIF p_counter_associations_rec.ATTRIBUTE14 IS NULL THEN
3951 l_counter_associations_rec.ATTRIBUTE14 := l_old_counter_associations_rec.ATTRIBUTE14;
3952 END IF;
3953 IF p_counter_associations_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR THEN
3954 l_counter_associations_rec.ATTRIBUTE15 := NULL;
3955 ELSIF p_counter_associations_rec.ATTRIBUTE15 IS NULL THEN
3956 l_counter_associations_rec.ATTRIBUTE15 := l_old_counter_associations_rec.ATTRIBUTE15;
3957 END IF;
3958 IF p_counter_associations_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR THEN
3959 l_counter_associations_rec.ATTRIBUTE_CATEGORY := NULL;
3960 ELSIF p_counter_associations_rec.ATTRIBUTE_CATEGORY IS NULL THEN
3961 l_counter_associations_rec.ATTRIBUTE_CATEGORY := l_old_counter_associations_rec.ATTRIBUTE_CATEGORY;
3962 END IF;
3963 IF p_counter_associations_rec.object_version_number = FND_API.G_MISS_NUM THEN
3964 l_counter_associations_rec.object_version_number := NULL;
3965 ELSIF p_counter_associations_rec.object_version_number IS NULL THEN
3966 l_counter_associations_rec.object_version_number := l_old_counter_associations_rec.object_version_number;
3967 END IF;
3968
3969 IF p_counter_associations_rec.maint_organization_id = FND_API.G_MISS_NUM THEN
3970 l_counter_associations_rec.maint_organization_id := NULL;
3971 ELSIF p_counter_associations_rec.maint_organization_id IS NULL THEN
3972 l_counter_associations_rec.maint_organization_id := l_old_counter_associations_rec.maint_organization_id;
3973 END IF;
3974
3975 IF l_old_counter_associations_rec.object_version_number <> nvl(l_counter_associations_rec.object_version_number,0) THEN
3976 csi_ctr_gen_utility_pvt.put_line('Object version mismatch in update counter');
3977 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_OBJ_VER_MISMATCH');
3978 END IF;
3979
3980 IF p_counter_associations_rec.primary_failure_flag = FND_API.G_MISS_CHAR THEN
3981 l_counter_associations_rec.primary_failure_flag := NULL;
3982 ELSIF p_counter_associations_rec.primary_failure_flag IS NULL THEN
3983 l_counter_associations_rec.primary_failure_flag := l_old_counter_associations_rec.primary_failure_flag;
3984 END IF;
3985
3986 -- do validation here
3987 IF l_counter_associations_rec.counter_id <> l_old_counter_associations_rec.counter_id THEN
3988 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_ASC_NOT_UPDATABLE');
3989 END IF;
3990
3991 IF l_counter_associations_rec.SOURCE_OBJECT_CODE <> l_old_counter_associations_rec.SOURCE_OBJECT_CODE THEN
3992 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_SOC_ASC_NOT_UPDATABLE');
3993 END IF;
3994
3995 IF l_counter_associations_rec.SOURCE_OBJECT_ID <> l_old_counter_associations_rec.SOURCE_OBJECT_ID THEN
3996 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_SOI_ASC_NOT_UPDATABLE');
3997 END IF;
3998
3999 -- Validate start date
4000 IF l_counter_associations_rec.START_DATE_ACTIVE IS NULL THEN
4001 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_STDATE_INVALID');
4002 ELSIF l_counter_associations_rec.START_DATE_ACTIVE > sysdate THEN
4003 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_INVALID_START_DATE');
4004 END IF;
4005
4006 -- call table handler here
4007 CSI_COUNTER_ASSOCIATIONS_PKG.UPDATE_ROW
4008 (
4009 p_INSTANCE_ASSOCIATION_ID => p_counter_associations_rec.INSTANCE_ASSOCIATION_ID
4010 ,p_SOURCE_OBJECT_CODE => p_counter_associations_rec.SOURCE_OBJECT_CODE
4011 ,p_SOURCE_OBJECT_ID => p_counter_associations_rec.SOURCE_OBJECT_ID
4012 ,p_OBJECT_VERSION_NUMBER => p_counter_associations_rec.OBJECT_VERSION_NUMBER + 1
4013 ,p_LAST_UPDATE_DATE => sysdate
4014 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
4015 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
4016 ,p_CREATION_DATE => p_counter_associations_rec.CREATION_DATE
4017 ,p_CREATED_BY => p_counter_associations_rec.CREATED_BY
4018 ,p_ATTRIBUTE1 => p_counter_associations_rec.ATTRIBUTE1
4019 ,p_ATTRIBUTE2 => p_counter_associations_rec.ATTRIBUTE2
4020 ,p_ATTRIBUTE3 => p_counter_associations_rec.ATTRIBUTE3
4021 ,p_ATTRIBUTE4 => p_counter_associations_rec.ATTRIBUTE4
4022 ,p_ATTRIBUTE5 => p_counter_associations_rec.ATTRIBUTE5
4023 ,p_ATTRIBUTE6 => p_counter_associations_rec.ATTRIBUTE6
4024 ,p_ATTRIBUTE7 => p_counter_associations_rec.ATTRIBUTE7
4025 ,p_ATTRIBUTE8 => p_counter_associations_rec.ATTRIBUTE8
4026 ,p_ATTRIBUTE9 => p_counter_associations_rec.ATTRIBUTE9
4027 ,p_ATTRIBUTE10 => p_counter_associations_rec.ATTRIBUTE10
4028 ,p_ATTRIBUTE11 => p_counter_associations_rec.ATTRIBUTE11
4029 ,p_ATTRIBUTE12 => p_counter_associations_rec.ATTRIBUTE12
4030 ,p_ATTRIBUTE13 => p_counter_associations_rec.ATTRIBUTE13
4031 ,p_ATTRIBUTE14 => p_counter_associations_rec.ATTRIBUTE14
4032 ,p_ATTRIBUTE15 => p_counter_associations_rec.ATTRIBUTE15
4033 ,p_ATTRIBUTE_CATEGORY => p_counter_associations_rec.ATTRIBUTE_CATEGORY
4034 ,p_SECURITY_GROUP_ID => p_counter_associations_rec.SECURITY_GROUP_ID
4035 ,p_MIGRATED_FLAG => p_counter_associations_rec.MIGRATED_FLAG
4036 ,p_COUNTER_ID => p_counter_associations_rec.COUNTER_ID
4037 ,p_START_DATE_ACTIVE => p_counter_associations_rec.START_DATE_ACTIVE
4038 ,p_END_DATE_ACTIVE => p_counter_associations_rec.END_DATE_ACTIVE
4039 ,p_MAINT_ORGANIZATION_ID => p_counter_associations_rec.MAINT_ORGANIZATION_ID
4040 ,p_PRIMARY_FAILURE_FLAG => p_counter_associations_rec.PRIMARY_FAILURE_FLAG
4041 );
4042 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4043 ROLLBACK TO update_ctr_associations_pvt;
4044 RETURN;
4045 END IF;
4046
4047 -- End of API body
4048 -- Standard check of p_commit.
4049 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
4050 COMMIT WORK;
4051 END IF;
4052
4053 EXCEPTION
4054 WHEN FND_API.G_EXC_ERROR THEN
4055 x_return_status := FND_API.G_RET_STS_ERROR ;
4056 ROLLBACK TO update_ctr_associations_pvt;
4057 FND_MSG_PUB.Count_And_Get
4058 (p_count => x_msg_count,
4059 p_data => x_msg_data
4060 );
4061
4062 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4064 ROLLBACK TO update_ctr_associations_pvt;
4065 FND_MSG_PUB.Count_And_Get
4066 (p_count => x_msg_count,
4067 p_data => x_msg_data
4068 );
4069 WHEN OTHERS THEN
4070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4071 ROLLBACK TO update_ctr_associations_pvt;
4072 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4073 THEN
4074 FND_MSG_PUB.Add_Exc_Msg
4075 (G_PKG_NAME,
4076 l_api_name
4077 );
4078 END IF;
4079 FND_MSG_PUB.Count_And_Get
4080 (p_count => x_msg_count,
4081 p_data => x_msg_data
4082 );
4083 END update_ctr_associations;
4084
4085 --|---------------------------------------------------
4086 --| procedure name: update_ctr_val_max_seq_no
4087 --| description : procedure used to update
4088 --| the ctr_val_max_seq_no for
4089 --| a particular counter
4090 --|---------------------------------------------------
4091
4092 PROCEDURE update_ctr_val_max_seq_no
4093 (
4094 p_api_version IN NUMBER
4095 ,p_commit IN VARCHAR2
4096 ,p_init_msg_list IN VARCHAR2
4097 ,p_validation_level IN NUMBER
4098 ,p_counter_id IN NUMBER
4099 ,px_ctr_val_max_seq_no IN OUT NOCOPY NUMBER
4100 ,x_return_status OUT NOCOPY VARCHAR2
4101 ,x_msg_count OUT NOCOPY NUMBER
4102 ,x_msg_data OUT NOCOPY VARCHAR2
4103 )
4104 IS
4105 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CTR_VAL_MAX_SEQ_NO';
4106 l_api_version CONSTANT NUMBER := 1.0;
4107
4108 l_old_ctr_val_max_seq_no NUMBER;
4109 l_old_object_version_number NUMBER;
4110 l_ctr_val_id_exist VARCHAR2(1) := 'N';
4111 l_new_value_timestamp DATE := NULL;
4112 l_new_reading_is_latest VARCHAR2(1) := 'N';
4113 BEGIN
4114 -- Standard Start of API savepoint
4115 SAVEPOINT update_ctr_val_max_seq_no;
4116
4117 -- Standard call to check for call compatibility.
4118 IF NOT FND_API.Compatible_API_Call (l_api_version,
4119 p_api_version,
4120 l_api_name ,
4121 G_PKG_NAME )
4122 THEN
4123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4124 END IF;
4125
4126 -- Initialize message list if p_init_msg_list is set to TRUE.
4127 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
4128 FND_MSG_PUB.initialize;
4129 END IF;
4130
4131 -- Initialize API return status to success
4132 x_return_status := FND_API.G_RET_STS_SUCCESS;
4133
4134 csi_ctr_gen_utility_pvt.read_debug_profiles;
4135
4136 -- If debug_level = 1 then dump the procedure name
4137 IF (csi_ctr_gen_utility_pvt.g_debug_level > 0) THEN
4138 csi_ctr_gen_utility_pvt.put_line( 'update_ctr_val_max_seq_no');
4139 END IF;
4140
4141 -- If the debug level = 2 then dump all the parameters values.
4142 IF (csi_ctr_gen_utility_pvt.g_debug_level > 1) THEN
4143 csi_ctr_gen_utility_pvt.put_line('update_ctr_val_max_seq_no' ||
4144 p_api_version ||'-'||
4145 p_commit ||'-'||
4146 p_init_msg_list ||'-'||
4147 p_validation_level);
4148 csi_ctr_gen_utility_pvt.put_line('p_counter_id : ' || p_counter_id);
4149 csi_ctr_gen_utility_pvt.put_line('px_ctr_val_max_seq_no : ' || px_ctr_val_max_seq_no);
4150 END IF;
4151
4152 -- Start of API Body
4153
4154 IF px_ctr_val_max_seq_no <= 0 THEN
4155 --px_ctr_val_max_seq_no = -1 means the ctr_val_max_seq_no column for this counter has never been updated
4156 --px_ctr_val_max_seq_no = 0 means there is currently no non-disabled reading for this counter
4157 --setting px_ctr_val_max_seq_no to NULL to force the program to figure out the correct value for ctr_val_max_seq_no
4158 px_ctr_val_max_seq_no := NULL;
4159 END IF;
4160
4161 SELECT ctr_val_max_seq_no, object_version_number
4162 INTO l_old_ctr_val_max_seq_no, l_old_object_version_number
4163 FROM csi_counters_b
4164 WHERE counter_id = p_counter_id;
4165
4166 IF (csi_ctr_gen_utility_pvt.g_debug_level > 1) THEN
4167 csi_ctr_gen_utility_pvt.put_line('l_old_ctr_val_max_seq_no : ' || l_old_ctr_val_max_seq_no);
4168 csi_ctr_gen_utility_pvt.put_line('l_old_object_version_number : ' || l_old_object_version_number);
4169 END IF;
4170
4171 IF (px_ctr_val_max_seq_no IS NOT NULL) AND
4172 (px_ctr_val_max_seq_no <> FND_API.G_MISS_NUM) THEN
4173 BEGIN
4174 SELECT 'Y', value_timestamp
4175 INTO l_ctr_val_id_exist, l_new_value_timestamp
4176 FROM csi_counter_readings
4177 WHERE counter_id = p_counter_id
4178 AND counter_value_id = px_ctr_val_max_seq_no
4179 AND NVL(disabled_flag, 'N') = 'N';
4180 EXCEPTION
4181 WHEN NO_DATA_FOUND THEN
4182 csi_ctr_gen_utility_pvt.put_line('Invalid parameter px_ctr_val_max_seq_no : '||px_ctr_val_max_seq_no);
4183 RAISE FND_API.G_EXC_ERROR;
4184 END;
4185
4186 IF (l_ctr_val_id_exist = 'Y') AND (px_ctr_val_max_seq_no <> l_old_ctr_val_max_seq_no) THEN
4187 IF (l_old_ctr_val_max_seq_no <= 0) THEN
4188 l_new_reading_is_latest := 'Y';
4189 ELSE --l_old_ctr_val_max_seq_no > 0
4190 BEGIN
4191 SELECT 'Y'
4192 INTO l_new_reading_is_latest
4193 FROM csi_counter_readings
4194 WHERE counter_id = p_counter_id
4195 AND counter_value_id = l_old_ctr_val_max_seq_no
4196 AND NVL(disabled_flag, 'N') = 'N'
4197 AND value_timestamp < l_new_value_timestamp;
4198 EXCEPTION
4199 WHEN NO_DATA_FOUND THEN
4200 l_new_reading_is_latest := 'N';
4201 END;
4202 END IF;
4203 ELSE --l_ctr_val_id_exist = 'N' or px_ctr_val_max_seq_no = l_old_ctr_val_max_seq_no
4204 l_new_reading_is_latest := 'N';
4205 END IF;
4206 ELSE --px_ctr_val_max_seq_no IS NULL or px_ctr_val_max_seq_no = FND_API.G_MISS_NUM
4207 --Need to figure out the latest valid counter reading
4208 px_ctr_val_max_seq_no := 0; --Default px_ctr_val_max_seq_no to 0
4209 BEGIN
4210 SELECT counter_value_id, value_timestamp, 'Y', 'Y'
4211 INTO px_ctr_val_max_seq_no, l_new_value_timestamp,
4212 l_ctr_val_id_exist, l_new_reading_is_latest
4213 FROM csi_counter_readings
4214 WHERE counter_id = p_counter_id
4215 AND value_timestamp =
4216 (SELECT MAX(value_timestamp) FROM csi_counter_readings
4217 WHERE counter_id = p_counter_id AND NVL(disabled_flag, 'N') = 'N');
4218 EXCEPTION
4219 WHEN NO_DATA_FOUND THEN
4220 px_ctr_val_max_seq_no := 0;
4221 l_ctr_val_id_exist := 'N';
4222 l_new_value_timestamp := NULL;
4223 l_new_reading_is_latest := 'N';
4224 END;
4225 END IF;
4226
4227 IF (csi_ctr_gen_utility_pvt.g_debug_level > 1) THEN
4228 csi_ctr_gen_utility_pvt.put_line('l_ctr_val_id_exist : ' || l_ctr_val_id_exist);
4229 csi_ctr_gen_utility_pvt.put_line('l_new_reading_is_latest : ' || l_new_reading_is_latest);
4230 csi_ctr_gen_utility_pvt.put_line('px_ctr_val_max_seq_no : ' || px_ctr_val_max_seq_no);
4231 csi_ctr_gen_utility_pvt.put_line('l_new_value_timestamp : ' || l_new_value_timestamp);
4232 END IF;
4233
4234 IF ((l_ctr_val_id_exist = 'Y') AND (l_new_reading_is_latest = 'Y') AND
4235 (l_old_ctr_val_max_seq_no <> px_ctr_val_max_seq_no)) OR
4236 (l_old_ctr_val_max_seq_no <> 0 AND px_ctr_val_max_seq_no = 0) THEN
4237 CSI_COUNTERS_PKG.update_row
4238 (
4239 p_counter_id => p_counter_id
4240 ,p_group_id => NULL
4241 ,p_counter_type => NULL
4242 ,p_initial_reading => NULL
4243 ,p_initial_reading_date => NULL
4244 ,p_tolerance_plus => NULL
4245 ,p_tolerance_minus => NULL
4246 ,p_uom_code => NULL
4247 ,p_derive_counter_id => NULL
4248 ,p_derive_function => NULL
4249 ,p_derive_property_id => NULL
4250 ,p_valid_flag => NULL
4251 ,p_formula_incomplete_flag => NULL
4252 ,p_formula_text => NULL
4253 ,p_rollover_last_reading => NULL
4254 ,p_rollover_first_reading => NULL
4255 ,p_usage_item_id => NULL
4256 ,p_ctr_val_max_seq_no => px_ctr_val_max_seq_no
4257 ,p_start_date_active => NULL
4258 ,p_end_date_active => NULL
4259 ,p_object_version_number => l_old_object_version_number + 1
4260 ,p_last_update_date => SYSDATE
4261 ,p_last_updated_by => FND_GLOBAL.USER_ID
4262 ,p_creation_date => NULL
4263 ,p_created_by => NULL
4264 ,p_last_update_login => FND_GLOBAL.USER_ID
4265 ,p_attribute1 => NULL
4266 ,p_attribute2 => NULL
4267 ,p_attribute3 => NULL
4268 ,p_attribute4 => NULL
4269 ,p_attribute5 => NULL
4270 ,p_attribute6 => NULL
4271 ,p_attribute7 => NULL
4272 ,p_attribute8 => NULL
4273 ,p_attribute9 => NULL
4274 ,p_attribute10 => NULL
4275 ,p_attribute11 => NULL
4276 ,p_attribute12 => NULL
4277 ,p_attribute13 => NULL
4278 ,p_attribute14 => NULL
4279 ,p_attribute15 => NULL
4280 ,p_attribute16 => NULL
4281 ,p_attribute17 => NULL
4282 ,p_attribute18 => NULL
4283 ,p_attribute19 => NULL
4284 ,p_attribute20 => NULL
4285 ,p_attribute21 => NULL
4286 ,p_attribute22 => NULL
4287 ,p_attribute23 => NULL
4288 ,p_attribute24 => NULL
4289 ,p_attribute25 => NULL
4290 ,p_attribute26 => NULL
4291 ,p_attribute27 => NULL
4292 ,p_attribute28 => NULL
4293 ,p_attribute29 => NULL
4294 ,p_attribute30 => NULL
4295 ,p_attribute_category => NULL
4296 ,p_migrated_flag => NULL
4297 ,p_customer_view => NULL
4298 ,p_direction => NULL
4299 ,p_filter_type => NULL
4300 ,p_filter_reading_count => NULL
4301 ,p_filter_time_uom => NULL
4302 ,p_estimation_id => NULL
4303 ,p_reading_type => NULL
4304 ,p_automatic_rollover => NULL
4305 ,p_default_usage_rate => NULL
4306 ,p_use_past_reading => NULL
4307 ,p_used_in_scheduling => NULL
4308 ,p_defaulted_group_id => NULL
4309 ,p_created_from_counter_tmpl_id => NULL
4310 ,p_security_group_id => NULL
4311 ,p_step_value => NULL
4312 ,p_name => NULL
4313 ,p_description => NULL
4314 ,p_time_based_manual_entry => NULL
4315 ,p_eam_required_flag => NULL
4316 ,p_comments => NULL
4317 );
4318 END IF;
4319 -- End of API body
4320
4321 -- Standard check of p_commit.
4322 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
4323 COMMIT WORK;
4324 END IF;
4325
4326 EXCEPTION
4327 WHEN FND_API.G_EXC_ERROR THEN
4328 x_return_status := FND_API.G_RET_STS_ERROR;
4329 ROLLBACK TO update_ctr_val_max_seq_no;
4330 FND_MSG_PUB.Count_And_Get
4331 (p_count => x_msg_count,
4332 p_data => x_msg_data
4333 );
4334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4336 ROLLBACK TO update_ctr_val_max_seq_no;
4337 FND_MSG_PUB.Count_And_Get
4338 (p_count => x_msg_count,
4339 p_data => x_msg_data
4340 );
4341 WHEN OTHERS THEN
4342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4343 ROLLBACK TO update_ctr_val_max_seq_no;
4344 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4345 THEN
4346 FND_MSG_PUB.Add_Exc_Msg
4347 (G_PKG_NAME,
4348 l_api_name
4349 );
4350 END IF;
4351 FND_MSG_PUB.Count_And_Get
4352 (p_count => x_msg_count,
4353 p_data => x_msg_data
4354 );
4355 END update_ctr_val_max_seq_no;
4356
4357 END CSI_COUNTER_PVT;