1 PACKAGE BODY CSI_CTR_USAGE_FORECAST_PKG as
2 /* $Header: csitcufb.pls 120.0 2005/06/10 14:17:24 rktow noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_CTR_USAGE_FORECAST_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csitcufb.pls';
6
7 PROCEDURE Insert_Row(
8 px_INSTANCE_FORECAST_ID IN OUT NOCOPY NUMBER
9 ,p_COUNTER_ID NUMBER
10 ,p_USAGE_RATE NUMBER
11 ,p_USE_PAST_READING NUMBER
12 ,p_ACTIVE_START_DATE DATE
13 ,p_ACTIVE_END_DATE DATE
14 ,p_OBJECT_VERSION_NUMBER NUMBER
15 ,p_LAST_UPDATE_DATE DATE
16 ,p_LAST_UPDATED_BY NUMBER
17 ,p_CREATION_DATE DATE
18 ,p_CREATED_BY NUMBER
19 ,p_LAST_UPDATE_LOGIN NUMBER
20 ) IS
21
22 CURSOR C1 IS
23 SELECT CSI_COUNTER_USAGE_FORECAST_S.nextval
24 FROM dual;
25 BEGIN
26 IF (px_INSTANCE_FORECAST_ID IS NULL) OR (px_INSTANCE_FORECAST_ID = FND_API.G_MISS_NUM) THEN
27 OPEN C1;
28 FETCH C1 INTO px_INSTANCE_FORECAST_ID;
29 CLOSE C1;
30 END IF;
31
32 INSERT INTO CSI_COUNTER_USAGE_FORECAST(
33 INSTANCE_FORECAST_ID
34 ,COUNTER_ID
35 ,USAGE_RATE
36 ,USE_PAST_READING
37 ,ACTIVE_START_DATE
38 ,ACTIVE_END_DATE
39 ,OBJECT_VERSION_NUMBER
40 ,LAST_UPDATE_DATE
41 ,LAST_UPDATED_BY
42 ,CREATION_DATE
43 ,CREATED_BY
44 ,LAST_UPDATE_LOGIN
45 )
46 VALUES(
47 px_INSTANCE_FORECAST_ID
48 ,decode(p_COUNTER_ID, FND_API.G_MISS_NUM, NULL, p_COUNTER_ID)
49 ,decode(p_USAGE_RATE, FND_API.G_MISS_NUM, NULL, p_USAGE_RATE)
50 ,decode(p_USE_PAST_READING, FND_API.G_MISS_NUM, NULL, p_USE_PAST_READING)
51 ,decode(p_ACTIVE_START_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_ACTIVE_START_DATE)
52 ,decode(p_ACTIVE_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_ACTIVE_END_DATE)
53 ,decode(p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
54 ,decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
55 ,decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
56 ,decode(p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
57 ,decode(p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
58 ,decode(p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
59 );
60 END Insert_Row;
61
62 PROCEDURE Update_Row(
63 p_INSTANCE_FORECAST_ID NUMBER
64 ,p_COUNTER_ID NUMBER
65 ,p_USAGE_RATE NUMBER
66 ,p_USE_PAST_READING NUMBER
67 ,p_ACTIVE_START_DATE DATE
68 ,p_ACTIVE_END_DATE DATE
69 ,p_OBJECT_VERSION_NUMBER NUMBER
70 ,p_LAST_UPDATE_DATE DATE
71 ,p_LAST_UPDATED_BY NUMBER
72 ,p_CREATION_DATE DATE
73 ,p_CREATED_BY NUMBER
74 ,p_LAST_UPDATE_LOGIN NUMBER
75 ) IS
76 BEGIN
77 UPDATE CSI_COUNTER_USAGE_FORECAST
78 SET
79 COUNTER_ID = decode(p_COUNTER_ID, NULL, COUNTER_ID, FND_API.G_MISS_NUM, NULL, p_COUNTER_ID)
80 ,USAGE_RATE = decode(p_USAGE_RATE, NULL, USAGE_RATE, FND_API.G_MISS_NUM, NULL, p_USAGE_RATE)
81 ,USE_PAST_READING = decode(p_USE_PAST_READING, NULL, USE_PAST_READING, FND_API.G_MISS_NUM, NULL, p_USE_PAST_READING)
82 ,ACTIVE_START_DATE = decode(p_ACTIVE_START_DATE, NULL, ACTIVE_START_DATE, FND_API.G_MISS_DATE, NULL, p_ACTIVE_START_DATE)
83 ,ACTIVE_END_DATE = decode(p_ACTIVE_END_DATE, NULL, ACTIVE_END_DATE, FND_API.G_MISS_DATE, NULL, p_ACTIVE_END_DATE)
84 ,OBJECT_VERSION_NUMBER = decode(p_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER)
85 ,LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE, NULL, LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE)
86 ,LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, NULL, LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
87 ,CREATION_DATE = decode(p_CREATION_DATE, NULL, CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE)
88 ,CREATED_BY = decode(p_CREATED_BY, NULL, CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
89 ,LAST_UPDATE_LOGIN = decode(p_LAST_UPDATE_LOGIN, NULL, LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
90 WHERE INSTANCE_FORECAST_ID = p_INSTANCE_FORECAST_ID;
91
92 If (SQL%NOTFOUND) then
93 RAISE NO_DATA_FOUND;
94 End If;
95 END Update_Row;
96
97 PROCEDURE Lock_Row(
98 p_INSTANCE_FORECAST_ID NUMBER
99 ,p_COUNTER_ID NUMBER
100 ,p_USAGE_RATE NUMBER
101 ,p_USE_PAST_READING NUMBER
102 ,p_ACTIVE_START_DATE DATE
103 ,p_ACTIVE_END_DATE DATE
104 ,p_OBJECT_VERSION_NUMBER NUMBER
105 ,p_LAST_UPDATE_DATE DATE
106 ,p_LAST_UPDATED_BY NUMBER
107 ,p_CREATION_DATE DATE
108 ,p_CREATED_BY NUMBER
109 ,p_LAST_UPDATE_LOGIN NUMBER
110 ) IS
111
112 CURSOR C1 IS
113 SELECT *
114 FROM CSI_COUNTER_USAGE_FORECAST
115 WHERE INSTANCE_FORECAST_ID = p_INSTANCE_FORECAST_ID
116 FOR UPDATE of INSTANCE_FORECAST_ID NOWAIT;
117 Recinfo C1%ROWTYPE;
118 BEGIN
119 OPEN C1;
120 FETCH C1 INTO Recinfo;
121 IF (C1%NOTFOUND) THEN
122 CLOSE C1;
123 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
124 APP_EXCEPTION.RAISE_EXCEPTION;
125 END IF;
126 CLOSE C1;
127
128 IF (
129 (Recinfo.INSTANCE_FORECAST_ID = p_INSTANCE_FORECAST_ID)
130 AND ((Recinfo.COUNTER_ID = p_COUNTER_ID) OR ((Recinfo.COUNTER_ID IS NULL) AND (p_COUNTER_ID IS NULL)))
131 AND ((Recinfo.USAGE_RATE = p_USAGE_RATE) OR ((Recinfo.USAGE_RATE IS NULL) AND (p_USAGE_RATE IS NULL)))
132 AND ((Recinfo.USE_PAST_READING = p_USE_PAST_READING) OR ((Recinfo.USE_PAST_READING IS NULL) AND (p_USE_PAST_READING IS NULL)))
133 AND ((Recinfo.ACTIVE_START_DATE = p_ACTIVE_START_DATE) OR ((Recinfo.ACTIVE_START_DATE IS NULL) AND (p_ACTIVE_START_DATE IS NULL)))
134 AND ((Recinfo.ACTIVE_END_DATE = p_ACTIVE_END_DATE) OR ((Recinfo.ACTIVE_END_DATE IS NULL) AND (p_ACTIVE_END_DATE IS NULL)))
135 AND ((Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER) OR ((Recinfo.OBJECT_VERSION_NUMBER IS NULL) AND (p_OBJECT_VERSION_NUMBER IS NULL)))
136 AND ((Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE) OR ((Recinfo.LAST_UPDATE_DATE IS NULL) AND (p_LAST_UPDATE_DATE IS NULL)))
137 AND ((Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY) OR ((Recinfo.LAST_UPDATED_BY IS NULL) AND (p_LAST_UPDATED_BY IS NULL)))
138 AND ((Recinfo.CREATION_DATE = p_CREATION_DATE) OR ((Recinfo.CREATION_DATE IS NULL) AND (p_CREATION_DATE IS NULL)))
139 AND ((Recinfo.CREATED_BY = p_CREATED_BY) OR ((Recinfo.CREATED_BY IS NULL) AND (p_CREATED_BY IS NULL)))
140 AND ((Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN) OR ((Recinfo.LAST_UPDATE_LOGIN IS NULL) AND (p_LAST_UPDATE_LOGIN IS NULL)))
141 ) THEN
142 return;
143 ELSE
144 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
145 APP_EXCEPTION.RAISE_EXCEPTION;
146 END IF;
147 END Lock_Row;
148
149 PROCEDURE Delete_Row(
150 p_INSTANCE_FORECAST_ID NUMBER
151 ) IS
152 BEGIN
153 DELETE FROM CSI_COUNTER_USAGE_FORECAST
154 WHERE INSTANCE_FORECAST_ID = p_INSTANCE_FORECAST_ID;
155 IF (SQL%NOTFOUND) then
156 RAISE NO_DATA_FOUND;
157 END IF;
158 END Delete_Row;
159
160 End CSI_CTR_USAGE_FORECAST_PKG;
[Home] [Help]