[Home] [Help]
PACKAGE BODY: APPS.CSI_COUNTER_PVT
Source
1 PACKAGE BODY CSI_COUNTER_PVT AS
2 /* $Header: csivctib.pls 120.31.12020000.1 2012/06/28 13:00:50 appldev 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(80); --Increased size from 50 to 80 from bug 8448273
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 --bug 9009809.Below check not needed during update.
3126
3127 -- check and update name
3128 /*
3129 IF l_counter_instance_rec.name <> l_old_counter_instance_rec.name THEN
3130 -- l_new_name_instr := instrb(l_counter_instance_rec.name,'-',-1,1);
3131 -- l_old_name_instr := instrb(l_old_counter_instance_rec.name,'-',-1,1);
3132 -- if l_new_name_instr <> 0 and l_old_name_instr <> 0 then
3133 -- 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
3134 -- l_counter_instance_rec.name := l_counter_instance_rec.name||'-'||p_counter_instance_rec.counter_id;
3135 -- end if;
3136 -- els
3137 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
3138 l_counter_instance_rec.name := l_counter_instance_rec.name||'-'||p_counter_instance_rec.counter_id;
3139 end if;
3140 END IF;
3141 */
3142 -- Validate start date
3143 IF l_counter_instance_rec.start_date_active IS NULL THEN
3144 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_STDATE_INVALID');
3145 ELSIF l_counter_instance_rec.start_date_active > sysdate THEN
3146 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_INVALID_START_DATE');
3147 END IF;
3148
3149 -- Validate counter name is unique
3150 Validate_Unique_ctr(l_counter_instance_rec.name, p_counter_instance_rec.counter_id);
3151
3152 -- Validate Counter
3153 validate_counter(l_counter_instance_rec.group_id, l_counter_instance_rec.name,
3154 l_counter_instance_rec.counter_type, l_counter_instance_rec.uom_code,
3155 l_counter_instance_rec.usage_item_id, l_counter_instance_rec.reading_type,
3156 l_counter_instance_rec.direction, l_counter_instance_rec.estimation_id,
3157 l_counter_instance_rec.derive_function, l_counter_instance_rec.formula_text,
3158 l_counter_instance_rec.derive_counter_id,l_counter_instance_rec.filter_type,
3159 l_counter_instance_rec.filter_reading_count, l_counter_instance_rec.filter_time_uom,
3160 l_counter_instance_rec.automatic_rollover, l_counter_instance_rec.rollover_last_reading,
3161 l_counter_instance_rec.rollover_first_reading, l_counter_instance_rec.tolerance_plus,
3162 l_counter_instance_rec.tolerance_minus, l_counter_instance_rec.used_in_scheduling,
3163 l_counter_instance_rec.initial_reading, l_counter_instance_rec.default_usage_rate,
3164 l_counter_instance_rec.use_past_reading, l_old_counter_instance_rec.counter_id,
3165 l_counter_instance_rec.start_date_active, l_counter_instance_rec.end_date_active
3166 );
3167
3168 -- call table handler here
3169
3170 CSI_COUNTERS_PKG.update_row
3171 (
3172 p_counter_id => p_counter_instance_rec.counter_id
3173 ,p_group_id => p_counter_instance_rec.group_id
3174 ,p_counter_type => p_counter_instance_rec.counter_type
3175 ,p_initial_reading => p_counter_instance_rec.initial_reading
3176 ,p_initial_reading_date => p_counter_instance_rec.initial_reading_date
3177 ,p_tolerance_plus => p_counter_instance_rec.tolerance_plus
3178 ,p_tolerance_minus => p_counter_instance_rec.tolerance_minus
3179 ,p_uom_code => p_counter_instance_rec.uom_code
3180 ,p_derive_counter_id => p_counter_instance_rec.derive_counter_id
3181 ,p_derive_function => p_counter_instance_rec.derive_function
3182 ,p_derive_property_id => p_counter_instance_rec.derive_property_id
3183 ,p_valid_flag => p_counter_instance_rec.valid_flag
3184 ,p_formula_incomplete_flag => p_counter_instance_rec.formula_incomplete_flag
3185 ,p_formula_text => p_counter_instance_rec.formula_text
3186 ,p_rollover_last_reading => p_counter_instance_rec.rollover_last_reading
3187 ,p_rollover_first_reading => p_counter_instance_rec.rollover_first_reading
3188 ,p_usage_item_id => p_counter_instance_rec.usage_item_id
3189 ,p_ctr_val_max_seq_no => p_counter_instance_rec.ctr_val_max_seq_no
3190 ,p_start_date_active => p_counter_instance_rec.start_date_active
3191 ,p_end_date_active => p_counter_instance_rec.end_date_active
3192 ,p_object_version_number => p_counter_instance_rec.object_version_number + 1
3193 ,p_last_update_date => sysdate
3194 ,p_last_updated_by => FND_GLOBAL.USER_ID
3195 ,p_creation_date => p_counter_instance_rec.creation_date
3196 ,p_created_by => p_counter_instance_rec.created_by
3197 ,p_last_update_login => FND_GLOBAL.USER_ID
3198 ,p_attribute1 => p_counter_instance_rec.attribute1
3199 ,p_attribute2 => p_counter_instance_rec.attribute2
3200 ,p_attribute3 => p_counter_instance_rec.attribute3
3201 ,p_attribute4 => p_counter_instance_rec.attribute4
3202 ,p_attribute5 => p_counter_instance_rec.attribute5
3203 ,p_attribute6 => p_counter_instance_rec.attribute6
3204 ,p_attribute7 => p_counter_instance_rec.attribute7
3205 ,p_attribute8 => p_counter_instance_rec.attribute8
3206 ,p_attribute9 => p_counter_instance_rec.attribute9
3207 ,p_attribute10 => p_counter_instance_rec.attribute10
3208 ,p_attribute11 => p_counter_instance_rec.attribute11
3209 ,p_attribute12 => p_counter_instance_rec.attribute12
3210 ,p_attribute13 => p_counter_instance_rec.attribute13
3211 ,p_attribute14 => p_counter_instance_rec.attribute14
3212 ,p_attribute15 => p_counter_instance_rec.attribute15
3213 ,p_attribute16 => p_counter_instance_rec.attribute16
3214 ,p_attribute17 => p_counter_instance_rec.attribute17
3215 ,p_attribute18 => p_counter_instance_rec.attribute18
3216 ,p_attribute19 => p_counter_instance_rec.attribute19
3217 ,p_attribute20 => p_counter_instance_rec.attribute20
3218 ,p_attribute21 => p_counter_instance_rec.attribute21
3219 ,p_attribute22 => p_counter_instance_rec.attribute22
3220 ,p_attribute23 => p_counter_instance_rec.attribute23
3221 ,p_attribute24 => p_counter_instance_rec.attribute24
3222 ,p_attribute25 => p_counter_instance_rec.attribute25
3223 ,p_attribute26 => p_counter_instance_rec.attribute26
3224 ,p_attribute27 => p_counter_instance_rec.attribute27
3225 ,p_attribute28 => p_counter_instance_rec.attribute28
3226 ,p_attribute29 => p_counter_instance_rec.attribute29
3227 ,p_attribute30 => p_counter_instance_rec.attribute30
3228 ,p_attribute_category => p_counter_instance_rec.attribute_category
3229 ,p_migrated_flag => p_counter_instance_rec.migrated_flag
3230 ,p_customer_view => p_counter_instance_rec.customer_view
3231 ,p_direction => p_counter_instance_rec.direction
3232 ,p_filter_type => p_counter_instance_rec.filter_type
3233 ,p_filter_reading_count => p_counter_instance_rec.filter_reading_count
3234 ,p_filter_time_uom => p_counter_instance_rec.filter_time_uom
3235 ,p_estimation_id => p_counter_instance_rec.estimation_id
3236 --,p_counter_code => p_counter_instance_rec.counter_code
3237 ,p_reading_type => p_counter_instance_rec.reading_type
3238 ,p_automatic_rollover => p_counter_instance_rec.automatic_rollover
3239 ,p_default_usage_rate => p_counter_instance_rec.default_usage_rate
3240 ,p_use_past_reading => p_counter_instance_rec.use_past_reading
3241 ,p_used_in_scheduling => p_counter_instance_rec.used_in_scheduling
3242 ,p_defaulted_group_id => p_counter_instance_rec.defaulted_group_id
3243 ,p_created_from_counter_tmpl_id => p_counter_instance_rec.created_from_counter_tmpl_id
3244 ,p_SECURITY_GROUP_ID => p_counter_instance_rec.SECURITY_GROUP_ID
3245 ,p_STEP_VALUE => p_counter_instance_rec.step_value
3246 ,p_name => l_counter_instance_rec.name
3247 ,p_description => p_counter_instance_rec.description
3248 ,p_time_based_manual_entry => p_counter_instance_rec.time_based_manual_entry
3249 ,p_eam_required_flag => p_counter_instance_rec.eam_required_flag
3250 ,p_comments => NULL
3251 );
3252 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3253 ROLLBACK TO update_counter_pvt;
3254 RETURN;
3255 END IF;
3256
3257 -- End of API body
3258
3259 -- Standard check of p_commit.
3260 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
3261 COMMIT WORK;
3262 END IF;
3263
3264 EXCEPTION
3265 WHEN FND_API.G_EXC_ERROR THEN
3266 x_return_status := FND_API.G_RET_STS_ERROR ;
3267 ROLLBACK TO update_counter_pvt;
3268 FND_MSG_PUB.Count_And_Get
3269 (p_count => x_msg_count,
3270 p_data => x_msg_data
3271 );
3272
3273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3275 ROLLBACK TO update_counter_pvt;
3276 FND_MSG_PUB.Count_And_Get
3277 (p_count => x_msg_count,
3278 p_data => x_msg_data
3279 );
3280 WHEN OTHERS THEN
3281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3282 ROLLBACK TO update_counter_pvt;
3283 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3284 THEN
3285 FND_MSG_PUB.Add_Exc_Msg
3286 (G_PKG_NAME,
3287 l_api_name
3288 );
3289 END IF;
3290 FND_MSG_PUB.Count_And_Get
3291 (p_count => x_msg_count,
3292 p_data => x_msg_data
3293 );
3294 END update_counter;
3295
3296 --|---------------------------------------------------
3297 --| procedure name: update_ctr_property
3298 --| description : procedure used to
3299 --| update counter properties
3300 --|---------------------------------------------------
3301
3302 PROCEDURE update_ctr_property
3303 (
3304 p_api_version IN NUMBER
3305 ,p_commit IN VARCHAR2
3306 ,p_init_msg_list IN VARCHAR2
3307 ,p_validation_level IN NUMBER
3308 ,P_ctr_properties_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.Ctr_properties_rec
3309 ,x_return_status OUT NOCOPY VARCHAR2
3310 ,x_msg_count OUT NOCOPY NUMBER
3311 ,x_msg_data OUT NOCOPY VARCHAR2
3312 )
3313 IS
3314 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CTR_PROPERTY';
3315 l_api_version CONSTANT NUMBER := 1.0;
3316 -- l_debug_level NUMBER;
3317 l_flag VARCHAR2(1) := 'N';
3318 l_msg_data VARCHAR2(2000);
3319 l_msg_index NUMBER;
3320 l_msg_count NUMBER;
3321 l_count NUMBER;
3322 l_return_message VARCHAR2(100);
3323
3324 l_old_ctr_properties_rec CSI_CTR_DATASTRUCTURES_PUB.ctr_properties_rec;
3325 l_ctr_properties_rec CSI_CTR_DATASTRUCTURES_PUB.ctr_properties_rec;
3326 CURSOR prop_rdgs_cur(p_counter_property_id NUMBER) IS
3327 SELECT counter_prop_value_id
3328 FROM CSI_CTR_PROPERTY_READINGS
3329 WHERE counter_property_id = p_counter_property_id;
3330 l_prop_rdgs_exist NUMBER;
3331
3332 BEGIN
3333 -- Standard Start of API savepoint
3334 SAVEPOINT update_ctr_property_pvt;
3335
3336 -- Standard call to check for call compatibility.
3337 IF NOT FND_API.Compatible_API_Call (l_api_version,
3338 p_api_version,
3339 l_api_name ,
3340 G_PKG_NAME )
3341 THEN
3342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3343 END IF;
3344
3345 -- Initialize message list if p_init_msg_list is set to TRUE.
3346 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3347 FND_MSG_PUB.initialize;
3348 END IF;
3349
3350 -- Initialize API return status to success
3351 x_return_status := FND_API.G_RET_STS_SUCCESS;
3352
3353 -- Read the debug profiles values in to global variable 7197402
3354 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
3355
3356 -- Check the profile option debug_level for debug message reporting
3357 -- l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
3358
3359 -- If debug_level = 1 then dump the procedure name
3360 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 0) THEN
3361 csi_ctr_gen_utility_pvt.put_line( 'update_ctr_property');
3362 END IF;
3363
3364 -- If the debug level = 2 then dump all the parameters values.
3365 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 1) THEN
3366 csi_ctr_gen_utility_pvt.put_line( 'update_ctr_property' ||
3367 p_api_version ||'-'||
3368 p_commit ||'-'||
3369 p_init_msg_list ||'-'||
3370 p_validation_level );
3371 csi_ctr_gen_utility_pvt.dump_ctr_properties_rec(p_ctr_properties_rec);
3372 END IF;
3373
3374 -- Start of API Body
3375
3376 SELECT NAME
3377 ,DESCRIPTION
3378 ,COUNTER_ID
3379 ,PROPERTY_DATA_TYPE
3380 ,IS_NULLABLE
3381 ,DEFAULT_VALUE
3382 ,MINIMUM_VALUE
3383 , MAXIMUM_VALUE
3384 , UOM_CODE
3385 , START_DATE_ACTIVE
3386 , END_DATE_ACTIVE
3387 , ATTRIBUTE1
3388 , ATTRIBUTE2
3389 , ATTRIBUTE3
3390 , ATTRIBUTE4
3391 , ATTRIBUTE5
3392 , ATTRIBUTE6
3393 , ATTRIBUTE7
3394 , ATTRIBUTE8
3395 , ATTRIBUTE9
3396 , ATTRIBUTE10
3397 , ATTRIBUTE11
3398 , ATTRIBUTE12
3399 , ATTRIBUTE13
3400 , ATTRIBUTE14
3401 , ATTRIBUTE15
3402 , ATTRIBUTE_CATEGORY
3403 , PROPERTY_LOV_TYPE
3404 , object_version_number
3405 --, created_from_ctr_prop_tmpl_id
3406 INTO l_old_ctr_properties_rec.NAME
3407 ,l_old_ctr_properties_rec.DESCRIPTION
3408 ,l_old_ctr_properties_rec.COUNTER_ID
3409 ,l_old_ctr_properties_rec.PROPERTY_DATA_TYPE
3410 ,l_old_ctr_properties_rec.IS_NULLABLE
3411 ,l_old_ctr_properties_rec.DEFAULT_VALUE
3412 ,l_old_ctr_properties_rec.MINIMUM_VALUE
3413 ,l_old_ctr_properties_rec.MAXIMUM_VALUE
3414 ,l_old_ctr_properties_rec.UOM_CODE
3415 ,l_old_ctr_properties_rec.START_DATE_ACTIVE
3416 ,l_old_ctr_properties_rec.END_DATE_ACTIVE
3417 ,l_old_ctr_properties_rec.ATTRIBUTE1
3418 ,l_old_ctr_properties_rec.ATTRIBUTE2
3419 ,l_old_ctr_properties_rec.ATTRIBUTE3
3420 ,l_old_ctr_properties_rec.ATTRIBUTE4
3421 ,l_old_ctr_properties_rec.ATTRIBUTE5
3422 ,l_old_ctr_properties_rec.ATTRIBUTE6
3423 ,l_old_ctr_properties_rec.ATTRIBUTE7
3424 ,l_old_ctr_properties_rec.ATTRIBUTE8
3425 ,l_old_ctr_properties_rec.ATTRIBUTE9
3426 ,l_old_ctr_properties_rec.ATTRIBUTE10
3427 ,l_old_ctr_properties_rec.ATTRIBUTE11
3428 ,l_old_ctr_properties_rec.ATTRIBUTE12
3429 ,l_old_ctr_properties_rec.ATTRIBUTE13
3430 ,l_old_ctr_properties_rec.ATTRIBUTE14
3431 ,l_old_ctr_properties_rec.ATTRIBUTE15
3432 ,l_old_ctr_properties_rec.ATTRIBUTE_CATEGORY
3433 ,l_old_ctr_properties_rec.PROPERTY_LOV_TYPE
3434 ,l_old_ctr_properties_rec.object_version_number
3435 --,l_old_ctr_properties_rec.created_from_ctr_prop_tmpl_id
3436 FROM csi_counter_properties_vl
3437 WHERE counter_property_id = p_ctr_properties_rec.counter_property_id
3438 FOR UPDATE OF OBJECT_VERSION_NUMBER;
3439 IF SQL%NOTFOUND THEN
3440 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_PROP_INVALID');
3441 END IF;
3442
3443 l_ctr_properties_rec := p_ctr_properties_rec;
3444
3445 IF p_ctr_properties_rec.NAME = FND_API.G_MISS_CHAR THEN
3446 l_ctr_properties_rec.NAME := NULL;
3447 ELSIF p_ctr_properties_rec.NAME IS NULL THEN
3448 l_ctr_properties_rec.NAME := l_old_ctr_properties_rec.NAME;
3449 END IF;
3450 IF p_ctr_properties_rec.DESCRIPTION = FND_API.G_MISS_CHAR THEN
3451 l_ctr_properties_rec.DESCRIPTION := NULL;
3452 ELSIF p_ctr_properties_rec.DESCRIPTION IS NULL THEN
3453 l_ctr_properties_rec.DESCRIPTION := l_old_ctr_properties_rec.DESCRIPTION;
3454 END IF;
3455 IF p_ctr_properties_rec.COUNTER_ID = FND_API.G_MISS_NUM THEN
3456 l_ctr_properties_rec.COUNTER_ID := NULL;
3457 ELSIF p_ctr_properties_rec.COUNTER_ID IS NULL THEN
3458 l_ctr_properties_rec.COUNTER_ID := l_old_ctr_properties_rec.COUNTER_ID;
3459 END IF;
3460 IF p_ctr_properties_rec.PROPERTY_DATA_TYPE = FND_API.G_MISS_CHAR THEN
3461 l_ctr_properties_rec.PROPERTY_DATA_TYPE := NULL;
3462 ELSIF p_ctr_properties_rec.PROPERTY_DATA_TYPE IS NULL THEN
3463 l_ctr_properties_rec.PROPERTY_DATA_TYPE := l_old_ctr_properties_rec.PROPERTY_DATA_TYPE;
3464 END IF;
3465 IF p_ctr_properties_rec.IS_NULLABLE = FND_API.G_MISS_CHAR THEN
3466 l_ctr_properties_rec.IS_NULLABLE := NULL;
3467 ELSIF p_ctr_properties_rec.IS_NULLABLE IS NULL THEN
3468 l_ctr_properties_rec.IS_NULLABLE := l_old_ctr_properties_rec.IS_NULLABLE;
3469 END IF;
3470 IF p_ctr_properties_rec.DEFAULT_VALUE = FND_API.G_MISS_CHAR THEN
3471 l_ctr_properties_rec.DEFAULT_VALUE := NULL;
3472 ELSIF p_ctr_properties_rec.DEFAULT_VALUE IS NULL THEN
3473 l_ctr_properties_rec.DEFAULT_VALUE := l_old_ctr_properties_rec.DEFAULT_VALUE;
3474 END IF;
3475 IF p_ctr_properties_rec.MINIMUM_VALUE = FND_API.G_MISS_CHAR THEN
3476 l_ctr_properties_rec.MINIMUM_VALUE := NULL;
3477 ELSIF p_ctr_properties_rec.MINIMUM_VALUE IS NULL THEN
3478 l_ctr_properties_rec.MINIMUM_VALUE := l_old_ctr_properties_rec.MINIMUM_VALUE;
3479 END IF;
3480 IF p_ctr_properties_rec.MAXIMUM_VALUE = FND_API.G_MISS_CHAR THEN
3481 l_ctr_properties_rec.MAXIMUM_VALUE := NULL;
3482 ELSIF p_ctr_properties_rec.MAXIMUM_VALUE IS NULL THEN
3483 l_ctr_properties_rec.MAXIMUM_VALUE := l_old_ctr_properties_rec.MAXIMUM_VALUE;
3484 END IF;
3485 IF p_ctr_properties_rec.UOM_CODE = FND_API.G_MISS_CHAR THEN
3486 l_ctr_properties_rec.UOM_CODE := NULL;
3487 ELSIF p_ctr_properties_rec.UOM_CODE IS NULL THEN
3488 l_ctr_properties_rec.UOM_CODE := l_old_ctr_properties_rec.UOM_CODE;
3489 END IF;
3490 IF p_ctr_properties_rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE THEN
3491 l_ctr_properties_rec.START_DATE_ACTIVE := NULL;
3492 ELSIF p_ctr_properties_rec.START_DATE_ACTIVE IS NULL THEN
3493 l_ctr_properties_rec.START_DATE_ACTIVE := l_old_ctr_properties_rec.START_DATE_ACTIVE;
3494 END IF;
3495 IF p_ctr_properties_rec.END_DATE_ACTIVE = FND_API.G_MISS_DATE THEN
3496 l_ctr_properties_rec.END_DATE_ACTIVE := NULL;
3497 ELSIF p_ctr_properties_rec.END_DATE_ACTIVE IS NULL THEN
3498 l_ctr_properties_rec.END_DATE_ACTIVE := l_old_ctr_properties_rec.END_DATE_ACTIVE;
3499 END IF;
3500 IF p_ctr_properties_rec.PROPERTY_LOV_TYPE = FND_API.G_MISS_CHAR THEN
3501 l_ctr_properties_rec.PROPERTY_LOV_TYPE := NULL;
3502 ELSIF p_ctr_properties_rec.PROPERTY_LOV_TYPE IS NULL THEN
3503 l_ctr_properties_rec.PROPERTY_LOV_TYPE := l_old_ctr_properties_rec.PROPERTY_LOV_TYPE;
3504 END IF;
3505 IF p_ctr_properties_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR THEN
3506 l_ctr_properties_rec.ATTRIBUTE1 := NULL;
3507 ELSIF p_ctr_properties_rec.ATTRIBUTE1 IS NULL THEN
3508 l_ctr_properties_rec.ATTRIBUTE1 := l_old_ctr_properties_rec.ATTRIBUTE1;
3509 END IF;
3510 IF p_ctr_properties_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR THEN
3511 l_ctr_properties_rec.ATTRIBUTE2 := NULL;
3512 ELSIF p_ctr_properties_rec.ATTRIBUTE2 IS NULL THEN
3513 l_ctr_properties_rec.ATTRIBUTE2 := l_old_ctr_properties_rec.ATTRIBUTE2;
3514 END IF;
3515 IF p_ctr_properties_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR THEN
3516 l_ctr_properties_rec.ATTRIBUTE3 := NULL;
3517 ELSIF p_ctr_properties_rec.ATTRIBUTE3 IS NULL THEN
3518 l_ctr_properties_rec.ATTRIBUTE3 := l_old_ctr_properties_rec.ATTRIBUTE3;
3519 END IF;
3520 IF p_ctr_properties_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR THEN
3521 l_ctr_properties_rec.ATTRIBUTE4 := NULL;
3522 ELSIF p_ctr_properties_rec.ATTRIBUTE4 IS NULL THEN
3523 l_ctr_properties_rec.ATTRIBUTE4 := l_old_ctr_properties_rec.ATTRIBUTE4;
3524 END IF;
3525 IF p_ctr_properties_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR THEN
3526 l_ctr_properties_rec.ATTRIBUTE5 := NULL;
3527 ELSIF p_ctr_properties_rec.ATTRIBUTE5 IS NULL THEN
3528 l_ctr_properties_rec.ATTRIBUTE5 := l_old_ctr_properties_rec.ATTRIBUTE5;
3529 END IF;
3530 IF p_ctr_properties_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR THEN
3531 l_ctr_properties_rec.ATTRIBUTE6 := NULL;
3532 ELSIF p_ctr_properties_rec.ATTRIBUTE6 IS NULL THEN
3533 l_ctr_properties_rec.ATTRIBUTE6 := l_old_ctr_properties_rec.ATTRIBUTE6;
3534 END IF;
3535 IF p_ctr_properties_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR THEN
3536 l_ctr_properties_rec.ATTRIBUTE7 := NULL;
3537 ELSIF p_ctr_properties_rec.ATTRIBUTE7 IS NULL THEN
3538 l_ctr_properties_rec.ATTRIBUTE7 := l_old_ctr_properties_rec.ATTRIBUTE7;
3539 END IF;
3540 IF p_ctr_properties_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR THEN
3541 l_ctr_properties_rec.ATTRIBUTE8 := NULL;
3542 ELSIF p_ctr_properties_rec.ATTRIBUTE8 IS NULL THEN
3543 l_ctr_properties_rec.ATTRIBUTE8 := l_old_ctr_properties_rec.ATTRIBUTE8;
3544 END IF;
3545 IF p_ctr_properties_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR THEN
3546 l_ctr_properties_rec.ATTRIBUTE9 := NULL;
3547 ELSIF p_ctr_properties_rec.ATTRIBUTE9 IS NULL THEN
3548 l_ctr_properties_rec.ATTRIBUTE9 := l_old_ctr_properties_rec.ATTRIBUTE9;
3549 END IF;
3550 IF p_ctr_properties_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR THEN
3551 l_ctr_properties_rec.ATTRIBUTE10 := NULL;
3552 ELSIF p_ctr_properties_rec.ATTRIBUTE10 IS NULL THEN
3553 l_ctr_properties_rec.ATTRIBUTE10 := l_old_ctr_properties_rec.ATTRIBUTE10;
3554 END IF;
3555 IF p_ctr_properties_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR THEN
3556 l_ctr_properties_rec.ATTRIBUTE11 := NULL;
3557 ELSIF p_ctr_properties_rec.ATTRIBUTE11 IS NULL THEN
3558 l_ctr_properties_rec.ATTRIBUTE11 := l_old_ctr_properties_rec.ATTRIBUTE11;
3559 END IF;
3560 IF p_ctr_properties_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR THEN
3561 l_ctr_properties_rec.ATTRIBUTE12 := NULL;
3562 ELSIF p_ctr_properties_rec.ATTRIBUTE12 IS NULL THEN
3563 l_ctr_properties_rec.ATTRIBUTE12 := l_old_ctr_properties_rec.ATTRIBUTE12;
3564 END IF;
3565 IF p_ctr_properties_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR THEN
3566 l_ctr_properties_rec.ATTRIBUTE13 := NULL;
3567 ELSIF p_ctr_properties_rec.ATTRIBUTE13 IS NULL THEN
3568 l_ctr_properties_rec.ATTRIBUTE13 := l_old_ctr_properties_rec.ATTRIBUTE13;
3569 END IF;
3570 IF p_ctr_properties_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR THEN
3571 l_ctr_properties_rec.ATTRIBUTE14 := NULL;
3572 ELSIF p_ctr_properties_rec.ATTRIBUTE14 IS NULL THEN
3573 l_ctr_properties_rec.ATTRIBUTE14 := l_old_ctr_properties_rec.ATTRIBUTE14;
3574 END IF;
3575 IF p_ctr_properties_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR THEN
3576 l_ctr_properties_rec.ATTRIBUTE15 := NULL;
3577 ELSIF p_ctr_properties_rec.ATTRIBUTE15 IS NULL THEN
3578 l_ctr_properties_rec.ATTRIBUTE15 := l_old_ctr_properties_rec.ATTRIBUTE15;
3579 END IF;
3580 IF p_ctr_properties_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR THEN
3581 l_ctr_properties_rec.ATTRIBUTE_CATEGORY := NULL;
3582 ELSIF p_ctr_properties_rec.ATTRIBUTE_CATEGORY IS NULL THEN
3583 l_ctr_properties_rec.ATTRIBUTE_CATEGORY := l_old_ctr_properties_rec.ATTRIBUTE_CATEGORY;
3584 END IF;
3585 IF p_ctr_properties_rec.created_from_ctr_prop_tmpl_id = FND_API.G_MISS_NUM THEN
3586 l_ctr_properties_rec.created_from_ctr_prop_tmpl_id := NULL;
3587 ELSIF p_ctr_properties_rec.created_from_ctr_prop_tmpl_id IS NULL THEN
3588 l_ctr_properties_rec.created_from_ctr_prop_tmpl_id := l_old_ctr_properties_rec.created_from_ctr_prop_tmpl_id;
3589 END IF;
3590 IF p_ctr_properties_rec.object_version_number = FND_API.G_MISS_NUM THEN
3591 l_ctr_properties_rec.object_version_number := NULL;
3592 ELSIF p_ctr_properties_rec.object_version_number IS NULL THEN
3593 l_ctr_properties_rec.object_version_number := l_old_ctr_properties_rec.object_version_number;
3594 END IF;
3595
3596 IF l_old_ctr_properties_rec.object_version_number <> nvl(l_ctr_properties_rec.object_version_number,0) THEN
3597 csi_ctr_gen_utility_pvt.put_line('Object version mismatch in update counter');
3598 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_OBJ_VER_MISMATCH');
3599 END IF;
3600
3601 -- do validation here
3602
3603 IF l_ctr_properties_rec.counter_id <> l_old_ctr_properties_rec.counter_id THEN
3604 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_NOT_UPDATABLE');
3605 END IF;
3606
3607 -- validate unique property name within counter
3608 Validate_Unique_ctrprop(l_ctr_properties_rec.NAME,l_ctr_properties_rec.COUNTER_ID,p_ctr_properties_rec.counter_property_id);
3609
3610 IF l_ctr_properties_rec.PROPERTY_DATA_TYPE <> l_old_ctr_properties_rec.PROPERTY_DATA_TYPE THEN
3611 OPEN prop_rdgs_cur(p_ctr_properties_rec.counter_property_id);
3612 FETCH prop_rdgs_cur into l_prop_rdgs_exist;
3613 CLOSE prop_rdgs_cur;
3614 IF l_prop_rdgs_exist is not null then
3615 -- Counter property readings exist for this counter property. You cannot
3616 -- change the property datatype to something different.
3617 csi_ctr_gen_utility_pvt.put_line('Ctr prop rdgs exist for this ctr prop. Cannot change property datatype...');
3618 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_PROP_DTYP_NOT_UPD');
3619 END IF;
3620 END IF;
3621
3622 -- validate property date type in char,number,date
3623 if l_ctr_properties_rec.PROPERTY_DATA_TYPE not in ('CHAR', 'NUMBER', 'DATE') then
3624 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_INV_PROP_DATA_TYPE');
3625 end if;
3626
3627 -- Validate start date
3628 IF l_ctr_properties_rec.START_DATE_ACTIVE IS NULL THEN
3629 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_STDATE_INVALID');
3630 ELSIF l_ctr_properties_rec.START_DATE_ACTIVE > sysdate THEN
3631 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_INVALID_START_DATE');
3632 END IF;
3633
3634 -- Validate values for data type
3635 Validate_Data_Type(l_ctr_properties_rec.PROPERTY_DATA_TYPE, l_ctr_properties_rec.default_value,
3636 l_ctr_properties_rec.minimum_value, l_ctr_properties_rec.maximum_value);
3637
3638 -- Validate uom
3639 if l_ctr_properties_rec.uom_code is not null then
3640 Validate_UOM(l_ctr_properties_rec.uom_code);
3641 end if;
3642
3643 -- call table handler here
3644
3645 CSI_COUNTER_PROPERTIES_PKG.update_row
3646 (
3647 p_counter_property_id => p_ctr_properties_rec.counter_property_id
3648 ,p_counter_id => p_ctr_properties_rec.counter_id
3649 ,p_property_data_type => p_ctr_properties_rec.property_data_type
3650 ,p_is_nullable => p_ctr_properties_rec.is_nullable
3651 ,p_default_value => p_ctr_properties_rec.default_value
3652 ,p_minimum_value => p_ctr_properties_rec.minimum_value
3653 ,p_maximum_value => p_ctr_properties_rec.maximum_value
3654 ,p_uom_code => p_ctr_properties_rec.uom_code
3655 ,p_start_date_active => p_ctr_properties_rec.start_date_active
3656 ,p_end_date_active => p_ctr_properties_rec.end_date_active
3657 ,p_object_version_number => p_ctr_properties_rec.object_version_number + 1
3658 ,p_SECURITY_GROUP_ID => null
3659 ,p_last_update_date => sysdate
3660 ,p_last_updated_by => FND_GLOBAL.USER_ID
3661 ,p_creation_date => p_ctr_properties_rec.creation_date
3662 ,p_created_by => p_ctr_properties_rec.created_by
3663 ,p_last_update_login => FND_GLOBAL.USER_ID
3664 ,p_attribute1 => p_ctr_properties_rec.attribute1
3665 ,p_attribute2 => p_ctr_properties_rec.attribute2
3666 ,p_attribute3 => p_ctr_properties_rec.attribute3
3667 ,p_attribute4 => p_ctr_properties_rec.attribute4
3668 ,p_attribute5 => p_ctr_properties_rec.attribute5
3669 ,p_attribute6 => p_ctr_properties_rec.attribute6
3670 ,p_attribute7 => p_ctr_properties_rec.attribute7
3671 ,p_attribute8 => p_ctr_properties_rec.attribute8
3672 ,p_attribute9 => p_ctr_properties_rec.attribute9
3673 ,p_attribute10 => p_ctr_properties_rec.attribute10
3674 ,p_attribute11 => p_ctr_properties_rec.attribute11
3675 ,p_attribute12 => p_ctr_properties_rec.attribute12
3676 ,p_attribute13 => p_ctr_properties_rec.attribute13
3677 ,p_attribute14 => p_ctr_properties_rec.attribute14
3678 ,p_attribute15 => p_ctr_properties_rec.attribute15
3679 ,p_attribute_category => p_ctr_properties_rec.attribute_category
3680 ,p_migrated_flag => p_ctr_properties_rec.migrated_flag
3681 ,p_property_lov_type => p_ctr_properties_rec.property_lov_type
3682 ,p_name => p_ctr_properties_rec.name
3683 ,p_description => p_ctr_properties_rec.description
3684 ,p_create_from_ctr_prop_tmpl_id => p_ctr_properties_rec.created_from_ctr_prop_tmpl_id
3685 );
3686 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3687 ROLLBACK TO update_ctr_property_pvt;
3688 RETURN;
3689 END IF;
3690
3691 -- End of API body
3692
3693 -- Standard check of p_commit.
3694 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
3695 COMMIT WORK;
3696 END IF;
3697
3698 EXCEPTION
3699 WHEN FND_API.G_EXC_ERROR THEN
3700 x_return_status := FND_API.G_RET_STS_ERROR ;
3701 ROLLBACK TO update_ctr_property_pvt;
3702 FND_MSG_PUB.Count_And_Get
3703 (p_count => x_msg_count,
3704 p_data => x_msg_data
3705 );
3706
3707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3709 ROLLBACK TO update_ctr_property_pvt;
3710 FND_MSG_PUB.Count_And_Get
3711 (p_count => x_msg_count,
3712 p_data => x_msg_data
3713 );
3714 WHEN OTHERS THEN
3715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3716 ROLLBACK TO update_ctr_property_pvt;
3717 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3718 THEN
3719 FND_MSG_PUB.Add_Exc_Msg
3720 (G_PKG_NAME,
3721 l_api_name
3722 );
3723 END IF;
3724 FND_MSG_PUB.Count_And_Get
3725 (p_count => x_msg_count,
3726 p_data => x_msg_data
3727 );
3728 END update_ctr_property;
3729
3730 --|---------------------------------------------------
3731 --| procedure name: update_ctr_associations
3732 --| description : procedure used to
3733 --| update counter associations
3734 --|---------------------------------------------------
3735
3736 PROCEDURE update_ctr_associations
3737 (
3738 p_api_version IN NUMBER
3739 ,p_commit IN VARCHAR2
3740 ,p_init_msg_list IN VARCHAR2
3741 ,p_validation_level IN NUMBER
3742 ,P_counter_associations_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.counter_associations_rec
3743 ,x_return_status OUT NOCOPY VARCHAR2
3744 ,x_msg_count OUT NOCOPY NUMBER
3745 ,x_msg_data OUT NOCOPY VARCHAR2
3746 )
3747 IS
3748 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CTR_ASSOCIATIONS';
3749 l_api_version CONSTANT NUMBER := 1.0;
3750 -- l_debug_level NUMBER;
3751 l_flag VARCHAR2(1) := 'N';
3752 l_msg_data VARCHAR2(2000);
3753 l_msg_index NUMBER;
3754 l_msg_count NUMBER;
3755 l_count NUMBER;
3756 l_return_message VARCHAR2(100);
3757
3758 l_old_counter_associations_rec CSI_CTR_DATASTRUCTURES_PUB.counter_associations_rec;
3759 l_counter_associations_rec CSI_CTR_DATASTRUCTURES_PUB.counter_associations_rec;
3760
3761 BEGIN
3762 -- Standard Start of API savepoint
3763 SAVEPOINT update_ctr_associations_pvt;
3764
3765 -- Standard call to check for call compatibility.
3766 IF NOT FND_API.Compatible_API_Call (l_api_version,
3767 p_api_version,
3768 l_api_name ,
3769 G_PKG_NAME )
3770 THEN
3771 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3772 END IF;
3773
3774 -- Initialize message list if p_init_msg_list is set to TRUE.
3775 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3776 FND_MSG_PUB.initialize;
3777 END IF;
3778
3779 -- Initialize API return status to success
3780 x_return_status := FND_API.G_RET_STS_SUCCESS;
3781
3782 -- Read the debug profiles values in to global variable 7197402
3783 CSI_CTR_GEN_UTILITY_PVT.read_debug_profiles;
3784
3785 -- Check the profile option debug_level for debug message reporting
3786 -- l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
3787
3788 -- If debug_level = 1 then dump the procedure name
3789 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 0) THEN
3790 csi_gen_utility_pvt.put_line( 'update_ctr_associations');
3791 END IF;
3792
3793 -- If the debug level = 2 then dump all the parameters values.
3794 IF (CSI_CTR_GEN_UTILITY_PVT.g_debug_level > 1) THEN
3795 csi_gen_utility_pvt.put_line( 'update_ctr_associations' ||
3796 p_api_version ||'-'||
3797 p_commit ||'-'||
3798 p_init_msg_list ||'-'||
3799 p_validation_level );
3800 csi_ctr_gen_utility_pvt.dump_counter_associations_rec(p_counter_associations_rec);
3801 END IF;
3802
3803 -- Start of API Body
3804 SELECT SOURCE_OBJECT_CODE
3805 , SOURCE_OBJECT_ID
3806 , COUNTER_ID
3807 , START_DATE_ACTIVE
3808 , END_DATE_ACTIVE
3809 , ATTRIBUTE1
3810 , ATTRIBUTE2
3811 , ATTRIBUTE3
3812 , ATTRIBUTE4
3813 , ATTRIBUTE5
3814 , ATTRIBUTE6
3815 , ATTRIBUTE7
3816 , ATTRIBUTE8
3817 , ATTRIBUTE9
3818 , ATTRIBUTE10
3819 , ATTRIBUTE11
3820 , ATTRIBUTE12
3821 , ATTRIBUTE13
3822 , ATTRIBUTE14
3823 , ATTRIBUTE15
3824 , ATTRIBUTE_CATEGORY
3825 , object_version_number
3826 , maint_organization_id
3827 , primary_failure_flag
3828 INTO l_old_counter_associations_rec.SOURCE_OBJECT_CODE
3829 , l_old_counter_associations_rec.SOURCE_OBJECT_ID
3830 , l_old_counter_associations_rec.COUNTER_ID
3831 , l_old_counter_associations_rec.START_DATE_ACTIVE
3832 , l_old_counter_associations_rec.END_DATE_ACTIVE
3833 , l_old_counter_associations_rec.ATTRIBUTE1
3834 , l_old_counter_associations_rec.ATTRIBUTE2
3835 , l_old_counter_associations_rec.ATTRIBUTE3
3836 , l_old_counter_associations_rec.ATTRIBUTE4
3837 , l_old_counter_associations_rec.ATTRIBUTE5
3838 , l_old_counter_associations_rec.ATTRIBUTE6
3839 , l_old_counter_associations_rec.ATTRIBUTE7
3840 , l_old_counter_associations_rec.ATTRIBUTE8
3841 , l_old_counter_associations_rec.ATTRIBUTE9
3842 , l_old_counter_associations_rec.ATTRIBUTE10
3843 , l_old_counter_associations_rec.ATTRIBUTE11
3844 , l_old_counter_associations_rec.ATTRIBUTE12
3845 , l_old_counter_associations_rec.ATTRIBUTE13
3846 , l_old_counter_associations_rec.ATTRIBUTE14
3847 , l_old_counter_associations_rec.ATTRIBUTE15
3848 , l_old_counter_associations_rec.ATTRIBUTE_CATEGORY
3849 , l_old_counter_associations_rec.object_version_number
3850 , l_old_counter_associations_rec.maint_organization_id
3851 , l_old_counter_associations_rec.primary_failure_flag
3852 FROM CSI_COUNTER_ASSOCIATIONS
3853 WHERE INSTANCE_ASSOCIATION_ID = P_counter_associations_rec.INSTANCE_ASSOCIATION_ID
3854 FOR UPDATE OF OBJECT_VERSION_NUMBER;
3855 IF SQL%NOTFOUND THEN
3856 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_ASSOC_INVALID');
3857 END IF;
3858
3859 l_counter_associations_rec := p_counter_associations_rec;
3860
3861 IF p_counter_associations_rec.SOURCE_OBJECT_CODE = FND_API.G_MISS_CHAR THEN
3862 l_counter_associations_rec.SOURCE_OBJECT_CODE := NULL;
3863 ELSIF p_counter_associations_rec.SOURCE_OBJECT_CODE IS NULL THEN
3864 l_counter_associations_rec.SOURCE_OBJECT_CODE := l_old_counter_associations_rec.SOURCE_OBJECT_CODE;
3865 END IF;
3866 IF p_counter_associations_rec.SOURCE_OBJECT_ID = FND_API.G_MISS_NUM THEN
3867 l_counter_associations_rec.SOURCE_OBJECT_ID := NULL;
3868 ELSIF p_counter_associations_rec.SOURCE_OBJECT_ID IS NULL THEN
3869 l_counter_associations_rec.SOURCE_OBJECT_ID := l_old_counter_associations_rec.SOURCE_OBJECT_ID;
3870 END IF;
3871 IF p_counter_associations_rec.COUNTER_ID = FND_API.G_MISS_NUM THEN
3872 l_counter_associations_rec.COUNTER_ID := NULL;
3873 ELSIF p_counter_associations_rec.COUNTER_ID IS NULL THEN
3874 l_counter_associations_rec.COUNTER_ID := l_old_counter_associations_rec.COUNTER_ID;
3875 END IF;
3876 IF p_counter_associations_rec.START_DATE_ACTIVE = FND_API.G_MISS_DATE THEN
3877 l_counter_associations_rec.START_DATE_ACTIVE := NULL;
3878 ELSIF p_counter_associations_rec.START_DATE_ACTIVE IS NULL THEN
3879 l_counter_associations_rec.START_DATE_ACTIVE := l_old_counter_associations_rec.START_DATE_ACTIVE;
3880 END IF;
3881 IF p_counter_associations_rec.END_DATE_ACTIVE = FND_API.G_MISS_DATE THEN
3882 l_counter_associations_rec.END_DATE_ACTIVE := NULL;
3883 ELSIF p_counter_associations_rec.END_DATE_ACTIVE IS NULL THEN
3884 l_counter_associations_rec.END_DATE_ACTIVE := l_old_counter_associations_rec.END_DATE_ACTIVE;
3885 END IF;
3886 IF p_counter_associations_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR THEN
3887 l_counter_associations_rec.ATTRIBUTE1 := NULL;
3888 ELSIF p_counter_associations_rec.ATTRIBUTE1 IS NULL THEN
3889 l_counter_associations_rec.ATTRIBUTE1 := l_old_counter_associations_rec.ATTRIBUTE1;
3890 END IF;
3891 IF p_counter_associations_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR THEN
3892 l_counter_associations_rec.ATTRIBUTE2 := NULL;
3893 ELSIF p_counter_associations_rec.ATTRIBUTE2 IS NULL THEN
3894 l_counter_associations_rec.ATTRIBUTE2 := l_old_counter_associations_rec.ATTRIBUTE2;
3895 END IF;
3896 IF p_counter_associations_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR THEN
3897 l_counter_associations_rec.ATTRIBUTE3 := NULL;
3898 ELSIF p_counter_associations_rec.ATTRIBUTE3 IS NULL THEN
3899 l_counter_associations_rec.ATTRIBUTE3 := l_old_counter_associations_rec.ATTRIBUTE3;
3900 END IF;
3901 IF p_counter_associations_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR THEN
3902 l_counter_associations_rec.ATTRIBUTE4 := NULL;
3903 ELSIF p_counter_associations_rec.ATTRIBUTE4 IS NULL THEN
3904 l_counter_associations_rec.ATTRIBUTE4 := l_old_counter_associations_rec.ATTRIBUTE4;
3905 END IF;
3906 IF p_counter_associations_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR THEN
3907 l_counter_associations_rec.ATTRIBUTE5 := NULL;
3908 ELSIF p_counter_associations_rec.ATTRIBUTE5 IS NULL THEN
3909 l_counter_associations_rec.ATTRIBUTE5 := l_old_counter_associations_rec.ATTRIBUTE5;
3910 END IF;
3911 IF p_counter_associations_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR THEN
3912 l_counter_associations_rec.ATTRIBUTE6 := NULL;
3913 ELSIF p_counter_associations_rec.ATTRIBUTE6 IS NULL THEN
3914 l_counter_associations_rec.ATTRIBUTE6 := l_old_counter_associations_rec.ATTRIBUTE6;
3915 END IF;
3916 IF p_counter_associations_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR THEN
3917 l_counter_associations_rec.ATTRIBUTE7 := NULL;
3918 ELSIF p_counter_associations_rec.ATTRIBUTE7 IS NULL THEN
3919 l_counter_associations_rec.ATTRIBUTE7 := l_old_counter_associations_rec.ATTRIBUTE7;
3920 END IF;
3921 IF p_counter_associations_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR THEN
3922 l_counter_associations_rec.ATTRIBUTE8 := NULL;
3923 ELSIF p_counter_associations_rec.ATTRIBUTE8 IS NULL THEN
3924 l_counter_associations_rec.ATTRIBUTE8 := l_old_counter_associations_rec.ATTRIBUTE8;
3925 END IF;
3926 IF p_counter_associations_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR THEN
3927 l_counter_associations_rec.ATTRIBUTE9 := NULL;
3928 ELSIF p_counter_associations_rec.ATTRIBUTE9 IS NULL THEN
3929 l_counter_associations_rec.ATTRIBUTE9 := l_old_counter_associations_rec.ATTRIBUTE9;
3930 END IF;
3931 IF p_counter_associations_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR THEN
3932 l_counter_associations_rec.ATTRIBUTE10 := NULL;
3933 ELSIF p_counter_associations_rec.ATTRIBUTE10 IS NULL THEN
3934 l_counter_associations_rec.ATTRIBUTE10 := l_old_counter_associations_rec.ATTRIBUTE10;
3935 END IF;
3936 IF p_counter_associations_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR THEN
3937 l_counter_associations_rec.ATTRIBUTE11 := NULL;
3938 ELSIF p_counter_associations_rec.ATTRIBUTE11 IS NULL THEN
3939 l_counter_associations_rec.ATTRIBUTE11 := l_old_counter_associations_rec.ATTRIBUTE11;
3940 END IF;
3941 IF p_counter_associations_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR THEN
3942 l_counter_associations_rec.ATTRIBUTE12 := NULL;
3943 ELSIF p_counter_associations_rec.ATTRIBUTE12 IS NULL THEN
3944 l_counter_associations_rec.ATTRIBUTE12 := l_old_counter_associations_rec.ATTRIBUTE12;
3945 END IF;
3946 IF p_counter_associations_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR THEN
3947 l_counter_associations_rec.ATTRIBUTE13 := NULL;
3948 ELSIF p_counter_associations_rec.ATTRIBUTE13 IS NULL THEN
3949 l_counter_associations_rec.ATTRIBUTE13 := l_old_counter_associations_rec.ATTRIBUTE13;
3950 END IF;
3951 IF p_counter_associations_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR THEN
3952 l_counter_associations_rec.ATTRIBUTE14 := NULL;
3953 ELSIF p_counter_associations_rec.ATTRIBUTE14 IS NULL THEN
3954 l_counter_associations_rec.ATTRIBUTE14 := l_old_counter_associations_rec.ATTRIBUTE14;
3955 END IF;
3956 IF p_counter_associations_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR THEN
3957 l_counter_associations_rec.ATTRIBUTE15 := NULL;
3958 ELSIF p_counter_associations_rec.ATTRIBUTE15 IS NULL THEN
3959 l_counter_associations_rec.ATTRIBUTE15 := l_old_counter_associations_rec.ATTRIBUTE15;
3960 END IF;
3961 IF p_counter_associations_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR THEN
3962 l_counter_associations_rec.ATTRIBUTE_CATEGORY := NULL;
3963 ELSIF p_counter_associations_rec.ATTRIBUTE_CATEGORY IS NULL THEN
3964 l_counter_associations_rec.ATTRIBUTE_CATEGORY := l_old_counter_associations_rec.ATTRIBUTE_CATEGORY;
3965 END IF;
3966 IF p_counter_associations_rec.object_version_number = FND_API.G_MISS_NUM THEN
3967 l_counter_associations_rec.object_version_number := NULL;
3968 ELSIF p_counter_associations_rec.object_version_number IS NULL THEN
3969 l_counter_associations_rec.object_version_number := l_old_counter_associations_rec.object_version_number;
3970 END IF;
3971
3972 IF p_counter_associations_rec.maint_organization_id = FND_API.G_MISS_NUM THEN
3973 l_counter_associations_rec.maint_organization_id := NULL;
3974 ELSIF p_counter_associations_rec.maint_organization_id IS NULL THEN
3975 l_counter_associations_rec.maint_organization_id := l_old_counter_associations_rec.maint_organization_id;
3976 END IF;
3977
3978 IF l_old_counter_associations_rec.object_version_number <> nvl(l_counter_associations_rec.object_version_number,0) THEN
3979 csi_ctr_gen_utility_pvt.put_line('Object version mismatch in update counter');
3980 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_OBJ_VER_MISMATCH');
3981 END IF;
3982
3983 IF p_counter_associations_rec.primary_failure_flag = FND_API.G_MISS_CHAR THEN
3984 l_counter_associations_rec.primary_failure_flag := NULL;
3985 ELSIF p_counter_associations_rec.primary_failure_flag IS NULL THEN
3986 l_counter_associations_rec.primary_failure_flag := l_old_counter_associations_rec.primary_failure_flag;
3987 END IF;
3988
3989 -- do validation here
3990 IF l_counter_associations_rec.counter_id <> l_old_counter_associations_rec.counter_id THEN
3991 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_CTR_ASC_NOT_UPDATABLE');
3992 END IF;
3993
3994 IF l_counter_associations_rec.SOURCE_OBJECT_CODE <> l_old_counter_associations_rec.SOURCE_OBJECT_CODE THEN
3995 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_SOC_ASC_NOT_UPDATABLE');
3996 END IF;
3997
3998 IF l_counter_associations_rec.SOURCE_OBJECT_ID <> l_old_counter_associations_rec.SOURCE_OBJECT_ID THEN
3999 csi_ctr_gen_utility_pvt.ExitWithErrMsg('CSI_API_SOI_ASC_NOT_UPDATABLE');
4000 END IF;
4001
4002 -- Validate start date
4003 IF l_counter_associations_rec.START_DATE_ACTIVE IS NULL THEN
4004 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_STDATE_INVALID');
4005 ELSIF l_counter_associations_rec.START_DATE_ACTIVE > sysdate THEN
4006 CSI_CTR_GEN_UTILITY_PVT.ExitWithErrMsg('CSI_API_CTR_INVALID_START_DATE');
4007 END IF;
4008
4009 -- call table handler here
4010 CSI_COUNTER_ASSOCIATIONS_PKG.UPDATE_ROW
4011 (
4012 p_INSTANCE_ASSOCIATION_ID => p_counter_associations_rec.INSTANCE_ASSOCIATION_ID
4013 ,p_SOURCE_OBJECT_CODE => p_counter_associations_rec.SOURCE_OBJECT_CODE
4014 ,p_SOURCE_OBJECT_ID => p_counter_associations_rec.SOURCE_OBJECT_ID
4015 ,p_OBJECT_VERSION_NUMBER => p_counter_associations_rec.OBJECT_VERSION_NUMBER + 1
4016 ,p_LAST_UPDATE_DATE => sysdate
4017 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
4018 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
4019 ,p_CREATION_DATE => p_counter_associations_rec.CREATION_DATE
4020 ,p_CREATED_BY => p_counter_associations_rec.CREATED_BY
4021 ,p_ATTRIBUTE1 => p_counter_associations_rec.ATTRIBUTE1
4022 ,p_ATTRIBUTE2 => p_counter_associations_rec.ATTRIBUTE2
4023 ,p_ATTRIBUTE3 => p_counter_associations_rec.ATTRIBUTE3
4024 ,p_ATTRIBUTE4 => p_counter_associations_rec.ATTRIBUTE4
4025 ,p_ATTRIBUTE5 => p_counter_associations_rec.ATTRIBUTE5
4026 ,p_ATTRIBUTE6 => p_counter_associations_rec.ATTRIBUTE6
4027 ,p_ATTRIBUTE7 => p_counter_associations_rec.ATTRIBUTE7
4028 ,p_ATTRIBUTE8 => p_counter_associations_rec.ATTRIBUTE8
4029 ,p_ATTRIBUTE9 => p_counter_associations_rec.ATTRIBUTE9
4030 ,p_ATTRIBUTE10 => p_counter_associations_rec.ATTRIBUTE10
4031 ,p_ATTRIBUTE11 => p_counter_associations_rec.ATTRIBUTE11
4032 ,p_ATTRIBUTE12 => p_counter_associations_rec.ATTRIBUTE12
4033 ,p_ATTRIBUTE13 => p_counter_associations_rec.ATTRIBUTE13
4034 ,p_ATTRIBUTE14 => p_counter_associations_rec.ATTRIBUTE14
4035 ,p_ATTRIBUTE15 => p_counter_associations_rec.ATTRIBUTE15
4036 ,p_ATTRIBUTE_CATEGORY => p_counter_associations_rec.ATTRIBUTE_CATEGORY
4037 ,p_SECURITY_GROUP_ID => p_counter_associations_rec.SECURITY_GROUP_ID
4038 ,p_MIGRATED_FLAG => p_counter_associations_rec.MIGRATED_FLAG
4039 ,p_COUNTER_ID => p_counter_associations_rec.COUNTER_ID
4040 ,p_START_DATE_ACTIVE => p_counter_associations_rec.START_DATE_ACTIVE
4041 ,p_END_DATE_ACTIVE => p_counter_associations_rec.END_DATE_ACTIVE
4042 ,p_MAINT_ORGANIZATION_ID => p_counter_associations_rec.MAINT_ORGANIZATION_ID
4043 ,p_PRIMARY_FAILURE_FLAG => p_counter_associations_rec.PRIMARY_FAILURE_FLAG
4044 );
4045 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4046 ROLLBACK TO update_ctr_associations_pvt;
4047 RETURN;
4048 END IF;
4049
4050 -- End of API body
4051 -- Standard check of p_commit.
4052 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
4053 COMMIT WORK;
4054 END IF;
4055
4056 EXCEPTION
4057 WHEN FND_API.G_EXC_ERROR THEN
4058 x_return_status := FND_API.G_RET_STS_ERROR ;
4059 ROLLBACK TO update_ctr_associations_pvt;
4060 FND_MSG_PUB.Count_And_Get
4061 (p_count => x_msg_count,
4062 p_data => x_msg_data
4063 );
4064
4065 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4067 ROLLBACK TO update_ctr_associations_pvt;
4068 FND_MSG_PUB.Count_And_Get
4069 (p_count => x_msg_count,
4070 p_data => x_msg_data
4071 );
4072 WHEN OTHERS THEN
4073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4074 ROLLBACK TO update_ctr_associations_pvt;
4075 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4076 THEN
4077 FND_MSG_PUB.Add_Exc_Msg
4078 (G_PKG_NAME,
4079 l_api_name
4080 );
4081 END IF;
4082 FND_MSG_PUB.Count_And_Get
4083 (p_count => x_msg_count,
4084 p_data => x_msg_data
4085 );
4086 END update_ctr_associations;
4087
4088 --|---------------------------------------------------
4089 --| procedure name: update_ctr_val_max_seq_no
4090 --| description : procedure used to update
4091 --| the ctr_val_max_seq_no for
4092 --| a particular counter
4093 --|---------------------------------------------------
4094
4095 PROCEDURE update_ctr_val_max_seq_no
4096 (
4097 p_api_version IN NUMBER
4098 ,p_commit IN VARCHAR2
4099 ,p_init_msg_list IN VARCHAR2
4100 ,p_validation_level IN NUMBER
4101 ,p_counter_id IN NUMBER
4102 ,px_ctr_val_max_seq_no IN OUT NOCOPY NUMBER
4103 ,x_return_status OUT NOCOPY VARCHAR2
4104 ,x_msg_count OUT NOCOPY NUMBER
4105 ,x_msg_data OUT NOCOPY VARCHAR2
4106 )
4107 IS
4108 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CTR_VAL_MAX_SEQ_NO';
4109 l_api_version CONSTANT NUMBER := 1.0;
4110
4111 l_old_ctr_val_max_seq_no NUMBER;
4112 l_old_object_version_number NUMBER;
4113 l_ctr_val_id_exist VARCHAR2(1) := 'N';
4114 l_new_value_timestamp DATE := NULL;
4115 l_new_reading_is_latest VARCHAR2(1) := 'N';
4116 BEGIN
4117 -- Standard Start of API savepoint
4118 SAVEPOINT update_ctr_val_max_seq_no;
4119
4120 -- Standard call to check for call compatibility.
4121 IF NOT FND_API.Compatible_API_Call (l_api_version,
4122 p_api_version,
4123 l_api_name ,
4124 G_PKG_NAME )
4125 THEN
4126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4127 END IF;
4128
4129 -- Initialize message list if p_init_msg_list is set to TRUE.
4130 IF FND_API.to_Boolean(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
4131 FND_MSG_PUB.initialize;
4132 END IF;
4133
4134 -- Initialize API return status to success
4135 x_return_status := FND_API.G_RET_STS_SUCCESS;
4136
4137 csi_ctr_gen_utility_pvt.read_debug_profiles;
4138
4139 -- If debug_level = 1 then dump the procedure name
4140 IF (csi_ctr_gen_utility_pvt.g_debug_level > 0) THEN
4141 csi_ctr_gen_utility_pvt.put_line( 'update_ctr_val_max_seq_no');
4142 END IF;
4143
4144 -- If the debug level = 2 then dump all the parameters values.
4145 IF (csi_ctr_gen_utility_pvt.g_debug_level > 1) THEN
4146 csi_ctr_gen_utility_pvt.put_line('update_ctr_val_max_seq_no' ||
4147 p_api_version ||'-'||
4148 p_commit ||'-'||
4149 p_init_msg_list ||'-'||
4150 p_validation_level);
4151 csi_ctr_gen_utility_pvt.put_line('p_counter_id : ' || p_counter_id);
4152 csi_ctr_gen_utility_pvt.put_line('px_ctr_val_max_seq_no : ' || px_ctr_val_max_seq_no);
4153 END IF;
4154
4155 -- Start of API Body
4156
4157 IF px_ctr_val_max_seq_no <= 0 THEN
4158 --px_ctr_val_max_seq_no = -1 means the ctr_val_max_seq_no column for this counter has never been updated
4159 --px_ctr_val_max_seq_no = 0 means there is currently no non-disabled reading for this counter
4160 --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
4161 px_ctr_val_max_seq_no := NULL;
4162 END IF;
4163
4164 SELECT ctr_val_max_seq_no, object_version_number
4165 INTO l_old_ctr_val_max_seq_no, l_old_object_version_number
4166 FROM csi_counters_b
4167 WHERE counter_id = p_counter_id;
4168
4169 IF (csi_ctr_gen_utility_pvt.g_debug_level > 1) THEN
4170 csi_ctr_gen_utility_pvt.put_line('l_old_ctr_val_max_seq_no : ' || l_old_ctr_val_max_seq_no);
4171 csi_ctr_gen_utility_pvt.put_line('l_old_object_version_number : ' || l_old_object_version_number);
4172 END IF;
4173
4174 IF (px_ctr_val_max_seq_no IS NOT NULL) AND
4175 (px_ctr_val_max_seq_no <> FND_API.G_MISS_NUM) THEN
4176 BEGIN
4177 SELECT 'Y', value_timestamp
4178 INTO l_ctr_val_id_exist, l_new_value_timestamp
4179 FROM csi_counter_readings
4180 WHERE counter_id = p_counter_id
4181 AND counter_value_id = px_ctr_val_max_seq_no
4182 AND NVL(disabled_flag, 'N') = 'N';
4183 EXCEPTION
4184 WHEN NO_DATA_FOUND THEN
4185 csi_ctr_gen_utility_pvt.put_line('Invalid parameter px_ctr_val_max_seq_no : '||px_ctr_val_max_seq_no);
4186 RAISE FND_API.G_EXC_ERROR;
4187 END;
4188
4189 IF (l_ctr_val_id_exist = 'Y') AND (px_ctr_val_max_seq_no <> l_old_ctr_val_max_seq_no) THEN
4190 IF (l_old_ctr_val_max_seq_no <= 0) THEN
4191 l_new_reading_is_latest := 'Y';
4192 ELSE --l_old_ctr_val_max_seq_no > 0
4193 BEGIN
4194 SELECT 'Y'
4195 INTO l_new_reading_is_latest
4196 FROM csi_counter_readings
4197 WHERE counter_id = p_counter_id
4198 AND counter_value_id = l_old_ctr_val_max_seq_no
4199 AND NVL(disabled_flag, 'N') = 'N'
4200 AND value_timestamp < l_new_value_timestamp;
4201 EXCEPTION
4202 WHEN NO_DATA_FOUND THEN
4203 l_new_reading_is_latest := 'N';
4204 END;
4205 END IF;
4206 ELSE --l_ctr_val_id_exist = 'N' or px_ctr_val_max_seq_no = l_old_ctr_val_max_seq_no
4207 l_new_reading_is_latest := 'N';
4208 END IF;
4209 ELSE --px_ctr_val_max_seq_no IS NULL or px_ctr_val_max_seq_no = FND_API.G_MISS_NUM
4210 --Need to figure out the latest valid counter reading
4211 px_ctr_val_max_seq_no := 0; --Default px_ctr_val_max_seq_no to 0
4212 BEGIN
4213 -- Bug 9283089
4214 SELECT COUNTER_VALUE_ID, VALUE_TIMESTAMP, 'Y', 'Y'
4215 INTO px_ctr_val_max_seq_no, l_new_value_timestamp,
4216 l_ctr_val_id_exist, l_new_reading_is_latest FROM (
4217 SELECT COUNTER_VALUE_ID, VALUE_TIMESTAMP FROM CSI_COUNTER_READINGS WHERE COUNTER_ID = p_counter_id AND
4218 NVL(DISABLED_FLAG, 'N') = 'N' ORDER BY VALUE_TIMESTAMP DESC) WHERE ROWNUM = 1;
4219
4220
4221 /* SELECT counter_value_id, value_timestamp, 'Y', 'Y'
4222 INTO px_ctr_val_max_seq_no, l_new_value_timestamp,
4223 l_ctr_val_id_exist, l_new_reading_is_latest
4224 FROM csi_counter_readings
4225 WHERE counter_id = p_counter_id
4226 AND value_timestamp =
4227 (SELECT MAX(value_timestamp) FROM csi_counter_readings
4228 WHERE counter_id = p_counter_id AND NVL(disabled_flag, 'N') = 'N');*/
4229
4230 EXCEPTION
4231 WHEN NO_DATA_FOUND THEN
4232 px_ctr_val_max_seq_no := 0;
4233 l_ctr_val_id_exist := 'N';
4234 l_new_value_timestamp := NULL;
4235 l_new_reading_is_latest := 'N';
4236 END;
4237 END IF;
4238
4239 IF (csi_ctr_gen_utility_pvt.g_debug_level > 1) THEN
4240 csi_ctr_gen_utility_pvt.put_line('l_ctr_val_id_exist : ' || l_ctr_val_id_exist);
4241 csi_ctr_gen_utility_pvt.put_line('l_new_reading_is_latest : ' || l_new_reading_is_latest);
4242 csi_ctr_gen_utility_pvt.put_line('px_ctr_val_max_seq_no : ' || px_ctr_val_max_seq_no);
4243 csi_ctr_gen_utility_pvt.put_line('l_new_value_timestamp : ' || l_new_value_timestamp);
4244 END IF;
4245
4246 IF ((l_ctr_val_id_exist = 'Y') AND (l_new_reading_is_latest = 'Y') AND
4247 (l_old_ctr_val_max_seq_no <> px_ctr_val_max_seq_no)) OR
4248 (l_old_ctr_val_max_seq_no <> 0 AND px_ctr_val_max_seq_no = 0) THEN
4249
4250 -- Bug 9283089
4251 IF NVL(px_ctr_val_max_seq_no, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
4252
4253 UPDATE CSI_COUNTERS_B SET CTR_VAL_MAX_SEQ_NO = px_ctr_val_max_seq_no,
4254 OBJECT_VERSION_NUMBER = l_old_object_version_number + 1,
4255 LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
4256 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
4257 WHERE COUNTER_ID = p_counter_id;
4258
4259 END IF;
4260 /*CSI_COUNTERS_PKG.update_row
4261 (
4262 p_counter_id => p_counter_id
4263 ,p_group_id => NULL
4264 ,p_counter_type => NULL
4265 ,p_initial_reading => NULL
4266 ,p_initial_reading_date => NULL
4267 ,p_tolerance_plus => NULL
4268 ,p_tolerance_minus => NULL
4269 ,p_uom_code => NULL
4270 ,p_derive_counter_id => NULL
4271 ,p_derive_function => NULL
4272 ,p_derive_property_id => NULL
4273 ,p_valid_flag => NULL
4274 ,p_formula_incomplete_flag => NULL
4275 ,p_formula_text => NULL
4276 ,p_rollover_last_reading => NULL
4277 ,p_rollover_first_reading => NULL
4278 ,p_usage_item_id => NULL
4279 ,p_ctr_val_max_seq_no => px_ctr_val_max_seq_no
4280 ,p_start_date_active => NULL
4281 ,p_end_date_active => NULL
4282 ,p_object_version_number => l_old_object_version_number + 1
4283 ,p_last_update_date => SYSDATE
4284 ,p_last_updated_by => FND_GLOBAL.USER_ID
4285 ,p_creation_date => NULL
4286 ,p_created_by => NULL
4287 ,p_last_update_login => FND_GLOBAL.USER_ID
4288 ,p_attribute1 => NULL
4289 ,p_attribute2 => NULL
4290 ,p_attribute3 => NULL
4291 ,p_attribute4 => NULL
4292 ,p_attribute5 => NULL
4293 ,p_attribute6 => NULL
4294 ,p_attribute7 => NULL
4295 ,p_attribute8 => NULL
4296 ,p_attribute9 => NULL
4297 ,p_attribute10 => NULL
4298 ,p_attribute11 => NULL
4299 ,p_attribute12 => NULL
4300 ,p_attribute13 => NULL
4301 ,p_attribute14 => NULL
4302 ,p_attribute15 => NULL
4303 ,p_attribute16 => NULL
4304 ,p_attribute17 => NULL
4305 ,p_attribute18 => NULL
4306 ,p_attribute19 => NULL
4307 ,p_attribute20 => NULL
4308 ,p_attribute21 => NULL
4309 ,p_attribute22 => NULL
4310 ,p_attribute23 => NULL
4311 ,p_attribute24 => NULL
4312 ,p_attribute25 => NULL
4313 ,p_attribute26 => NULL
4314 ,p_attribute27 => NULL
4315 ,p_attribute28 => NULL
4316 ,p_attribute29 => NULL
4317 ,p_attribute30 => NULL
4318 ,p_attribute_category => NULL
4319 ,p_migrated_flag => NULL
4320 ,p_customer_view => NULL
4321 ,p_direction => NULL
4322 ,p_filter_type => NULL
4323 ,p_filter_reading_count => NULL
4324 ,p_filter_time_uom => NULL
4325 ,p_estimation_id => NULL
4326 ,p_reading_type => NULL
4327 ,p_automatic_rollover => NULL
4328 ,p_default_usage_rate => NULL
4329 ,p_use_past_reading => NULL
4330 ,p_used_in_scheduling => NULL
4331 ,p_defaulted_group_id => NULL
4332 ,p_created_from_counter_tmpl_id => NULL
4333 ,p_security_group_id => NULL
4334 ,p_step_value => NULL
4335 ,p_name => NULL
4336 ,p_description => NULL
4337 ,p_time_based_manual_entry => NULL
4338 ,p_eam_required_flag => NULL
4339 ,p_comments => NULL
4340 );*/
4341 END IF;
4342 -- End of API body
4343
4344 -- Standard check of p_commit.
4345 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE)) THEN
4346 COMMIT WORK;
4347 END IF;
4348
4349 EXCEPTION
4350 WHEN FND_API.G_EXC_ERROR THEN
4351 x_return_status := FND_API.G_RET_STS_ERROR;
4352 ROLLBACK TO update_ctr_val_max_seq_no;
4353 FND_MSG_PUB.Count_And_Get
4354 (p_count => x_msg_count,
4355 p_data => x_msg_data
4356 );
4357 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4359 ROLLBACK TO update_ctr_val_max_seq_no;
4360 FND_MSG_PUB.Count_And_Get
4361 (p_count => x_msg_count,
4362 p_data => x_msg_data
4363 );
4364 WHEN OTHERS THEN
4365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4366 ROLLBACK TO update_ctr_val_max_seq_no;
4367 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4368 THEN
4369 FND_MSG_PUB.Add_Exc_Msg
4370 (G_PKG_NAME,
4371 l_api_name
4372 );
4373 END IF;
4374 FND_MSG_PUB.Count_And_Get
4375 (p_count => x_msg_count,
4376 p_data => x_msg_data
4377 );
4378 END update_ctr_val_max_seq_no;
4379
4380 END CSI_COUNTER_PVT;