[Home] [Help]
PACKAGE BODY: APPS.CN_SEASONALITIES_PVT
Source
1 PACKAGE BODY cn_seasonalities_pvt AS
2 -- $Header: cnvseasb.pls 115.4 2002/11/21 21:18:17 hlchen ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'cn_seasonalities_pvt';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvseasb.pls';
6
7
8 -- Start of comments
9 -- API name : Update_Seasonalities
10 -- Type : Private.
11 -- Function :
12 -- Pre-reqs : None.
13 -- Parameters :
14 -- IN : p_api_version IN NUMBER Required
15 -- p_init_msg_list IN VARCHAR2 Optional
16 -- Default = FND_API.G_FALSE
17 -- p_commit IN VARCHAR2 Optional
18 -- Default = FND_API.G_FALSE
19 -- p_validation_level IN NUMBER Optional
20 -- Default = FND_API.G_VALID_LEVEL_FULL
21 -- p_seasonalities_rec_type IN seasonalities_rec_type
22 -- OUT : x_return_status OUT VARCHAR2(1)
23 -- x_msg_count OUT NUMBER
24 -- x_msg_data OUT VARCHAR2(2000)
25 --
26 -- Version : Current version 1.0
27 --
28 --
29 --
30 -- Notes : This procedure uses the table handler CN_SEAS_SCHEDULES_PKG
31 -- to update rows into CN_SEAS_SCHEDULES after some validations.
32 --
33 -- End of comments
34
35 PROCEDURE Update_Seasonalities
36 ( p_api_version IN NUMBER ,
37 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
38 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
39 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
40 p_seasonalities_rec_type IN seasonalities_rec_type,
41 x_return_status OUT NOCOPY VARCHAR2 ,
42 x_msg_count OUT NOCOPY NUMBER ,
43 x_msg_data OUT NOCOPY VARCHAR2
44 ) IS
45 l_api_name CONSTANT VARCHAR2(30) := 'Update_Seasonalities';
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_error_code NUMBER;
48 l_count NUMBER;
49 l_validation_status VARCHAR2(30);
50 l_rec cn_seas_schedules_PVT.seas_schedules_rec_type;
51 l_return_status VARCHAR2(1);
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(2000);
54
55 BEGIN
56 --DBMS_OUTPUT.PUT_LINE('Update in progress');
57 -- Standard Start of API savepoint
58 SAVEPOINT Update_Seasonalities;
59 -- Standard call to check for call compatibility.
60 IF NOT FND_API.compatible_api_call
61 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
62 THEN
63 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
64 END IF;
65
66 -- Initialize message list if p_init_msg_list is set to TRUE.
67 IF FND_API.to_Boolean( p_init_msg_list ) THEN
68 FND_MSG_PUB.initialize;
69 END IF;
70 -- Initialize API return status to success
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72 l_validation_status := 'INVALID';
73 -- API body
74 -- FIRST IS TO MAKE THE CN_SEAS_SCHEDULE_TABLE INVALID
75 -- Fetch the record.
76
77
78 SELECT OBJECT_VERSION_NUMBER,NAME,DESCRIPTION INTO l_rec.object_version_number,l_rec.name,l_rec.description
79 FROM CN_SEAS_SCHEDULES WHERE SEAS_SCHEDULE_ID = p_seasonalities_rec_type.SEAS_SCHEDULE_ID;
80
81 l_rec.seas_schedule_id := p_seasonalities_rec_type.SEAS_SCHEDULE_ID;
82 l_rec.validation_status := l_validation_status;
83
84 --DBMS_OUTPUT.PUT_LINE('About to call CN_SEAS_SCHEDULES');
85 cn_seas_schedules_PVT.Update_Seas_Schedule
86 ( p_api_version => 1.0,
87 p_init_msg_list => FND_API.G_FALSE,
88 p_commit => FND_API.G_FALSE,
89 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
90 p_seas_schedules_rec_type => l_rec,
91 x_return_status => l_return_status ,
92 x_msg_count => l_msg_count,
93 x_msg_data => l_msg_data
94 );
95 --DBMS_OUTPUT.PUT_LINE('Call CN_SEAS_SCHEDULES completed validating the output');
96 IF l_return_status <> 'S' THEN
97 --DBMS_OUTPUT.PUT_LINE('Updation on CN_SEAS_SCHEDULES Failed');
98 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
99 FND_MESSAGE.SET_NAME ('CN' , 'CN_SEAS_SCH_UPD_FAIL');
100 FND_MSG_PUB.Add;
101 END IF;
102 RAISE FND_API.G_EXC_ERROR ;
103 END IF;
104
105 CN_SEASONALITIES_pkg.update_row
106 (
107 P_SEASONALITY_ID => p_seasonalities_rec_type.seasonality_id,
108 P_SEAS_SCHEDULE_ID => p_seasonalities_rec_type.SEAS_SCHEDULE_ID,
109 p_CAL_PER_INT_TYPE_ID => FND_API.G_MISS_NUM,
110 P_PERIOD_ID => FND_API.G_MISS_NUM,
111 P_PCT_SEASONALITY => p_seasonalities_rec_type.pct_seasonality,
112 P_OBJECT_VERSION_NUMBER => p_seasonalities_rec_type.OBJECT_VERSION_NUMBER
113 );
114
115
116
117 -- End of API body.
118 << end_Update_Seasonalities >>
119 NULL;
120
121 -- Standard check of p_commit.
122 IF FND_API.To_Boolean( p_commit ) THEN
123 COMMIT WORK;
124 END IF;
125 -- Standard call to get message count and if count is 1, get message info.
126 FND_MSG_PUB.Count_And_Get
127 (
128 p_count => x_msg_count ,
129 p_data => x_msg_data ,
130 p_encoded => FND_API.G_FALSE
131 );
132
133 EXCEPTION
134 WHEN FND_API.G_EXC_ERROR THEN
135 ROLLBACK TO Update_Seasonalities ;
136 x_return_status := FND_API.G_RET_STS_ERROR ;
137 FND_MSG_PUB.Count_And_Get
138 (
139 p_count => x_msg_count ,
140 p_data => x_msg_data ,
141 p_encoded => FND_API.G_FALSE
142 );
143
144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
145 ROLLBACK TO Update_Seasonalities ;
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
147 FND_MSG_PUB.Count_And_Get
148 (
149 p_count => x_msg_count ,
150 p_data => x_msg_data ,
151 p_encoded => FND_API.G_FALSE
152 );
153 WHEN OTHERS THEN
154 ROLLBACK TO Update_Seasonalities ;
155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
156 l_error_code := SQLCODE;
157 IF l_error_code = -54 THEN
158 x_return_status := FND_API.G_RET_STS_ERROR ;
159 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
160 THEN
161 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOCK_FAIL');
162 FND_MSG_PUB.Add;
163 END IF;
164 ELSE
165 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
166 THEN
167 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
168 END IF;
169 END IF;
170 FND_MSG_PUB.Count_And_Get
171 (
172 p_count => x_msg_count ,
173 p_data => x_msg_data ,
174 p_encoded => FND_API.G_FALSE
175 );
176 END Update_Seasonalities;
177
178
179 -- Start of comments
180 -- API name : Validate_Seasonalities
181 -- Type : Private.
182 -- Function :
183 -- Pre-reqs : None.
184 -- Parameters :
185 -- IN : p_api_version IN NUMBER Required
186 -- p_init_msg_list IN VARCHAR2 Optional
187 -- Default = FND_API.G_FALSE
188 -- p_commit IN VARCHAR2 Optional
189 -- Default = FND_API.G_FALSE
190 -- p_validation_level IN NUMBER Optional
191 -- Default = FND_API.G_VALID_LEVEL_FULL
192 -- p_seas_schedule_id IN NUMBER
193 -- OUT : x_return_status OUT VARCHAR2(1)
194 -- x_msg_count OUT NUMBER
195 -- x_msg_data OUT VARCHAR2(2000)
196 --
197 -- Version : Current version 1.0
198 --
199 --
200 --
201 -- Notes : This procedure uses the table handler CN_SEAS_SCHEDULES_PKG
202 -- to update rows into CN_SEAS_SCHEDULES after some validations.
203 --
204 -- End of comments
205
206 PROCEDURE Validate_Seasonalities
207 ( p_api_version IN NUMBER ,
208 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
209 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
210 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
211 p_seas_schedule_rec_type IN cp_seas_schedules_rec_type,
212 x_return_status OUT NOCOPY VARCHAR2 ,
213 x_msg_count OUT NOCOPY NUMBER ,
214 x_msg_data OUT NOCOPY VARCHAR2
215 ) IS
216 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Seasonalities';
217 l_api_version CONSTANT NUMBER := 1.0;
218 l_error_code NUMBER;
219 l_seas_sch_id NUMBER;
220 l_rec cn_seas_schedules_PVT.seas_schedules_rec_type;
221 l_return_status VARCHAR2(1);
222 l_msg_count NUMBER;
223 l_msg_data VARCHAR2(2000);
224 l_sum NUMBER;
225
226 CURSOR pct_seasonality_cur(p_seas_sch_id NUMBER) IS
227 SELECT PCT_SEASONALITY FROM CN_SEASONALITIES
228 WHERE seas_schedule_id = p_seas_sch_id;
229 BEGIN
230 --DBMS_OUTPUT.PUT_LINE('Delete in progress');
231 -- Standard Start of API savepoint
232 SAVEPOINT Validate_Seasonalities;
233 -- Standard call to check for call compatibility.
234 IF NOT FND_API.compatible_api_call
235 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
236 THEN
237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
238 END IF;
239
240 -- Initialize message list if p_init_msg_list is set to TRUE.
241 IF FND_API.to_Boolean( p_init_msg_list ) THEN
242 FND_MSG_PUB.initialize;
243 END IF;
244 -- Initialize API return status to success
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246
247 -- API body
248 l_sum := 0;
249
250
251 FOR l_seas_pct IN pct_seasonality_cur(p_seas_schedule_rec_type.seas_schedule_id) LOOP
252 l_sum := l_sum + l_seas_pct.pct_seasonality;
253 END LOOP;
254
255 -- DBMS_OUTPUT.PUT_LINE('SUM IS : ' || l_sum);
256
257 IF (l_sum = 100) THEN
258 --DBMS_OUTPUT.PUT_LINE('SUM IS(IF) : ' || l_sum);
259 l_rec.seas_schedule_id := p_seas_schedule_rec_type.SEAS_SCHEDULE_ID;
260 l_rec.validation_status := 'VALID';
261 l_rec.object_version_number := p_seas_schedule_rec_type.object_version_number;
262 SELECT NAME,DESCRIPTION,PERIOD_YEAR,START_DATE,END_DATE INTO l_rec.name,l_rec.description,
263 l_rec.period_year,l_rec.start_date,L_rec.end_date from cn_seas_schedules
264 where seas_schedule_id = p_seas_schedule_rec_type.SEAS_SCHEDULE_ID;
265
266 cn_seas_schedules_PVT.Update_Seas_Schedule
267 ( p_api_version => 1.0,
268 p_init_msg_list => FND_API.G_FALSE,
269 p_commit => FND_API.G_FALSE,
270 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
271 p_seas_schedules_rec_type => l_rec,
272 x_return_status => l_return_status ,
273 x_msg_count => l_msg_count,
274 x_msg_data => l_msg_data
275 );
276
277 --DBMS_OUTPUT.PUT_LINE('STATUS IS ' || x_return_status);
278
279 END IF;
280
281 -- End of API body.
282 << end_Validate_Seasonalities >>
283 NULL;
284
285 -- Standard check of p_commit.
286 IF FND_API.To_Boolean( p_commit ) THEN
287 COMMIT WORK;
288 END IF;
289 -- Standard call to get message count and if count is 1, get message info.
290 FND_MSG_PUB.Count_And_Get
291 (
292 p_count => x_msg_count ,
293 p_data => x_msg_data ,
294 p_encoded => FND_API.G_FALSE
295 );
296
297 EXCEPTION
298 WHEN FND_API.G_EXC_ERROR THEN
299 ROLLBACK TO Validate_Seasonalities ;
300 x_return_status := FND_API.G_RET_STS_ERROR ;
301 FND_MSG_PUB.Count_And_Get
302 (
303 p_count => x_msg_count ,
304 p_data => x_msg_data ,
305 p_encoded => FND_API.G_FALSE
306 );
307
308 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
309 ROLLBACK TO Validate_Seasonalities ;
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
311 FND_MSG_PUB.Count_And_Get
312 (
313 p_count => x_msg_count ,
314 p_data => x_msg_data ,
315 p_encoded => FND_API.G_FALSE
316 );
317 WHEN OTHERS THEN
318 ROLLBACK TO Validate_Seasonalities ;
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
320 l_error_code := SQLCODE;
321 IF l_error_code = -54 THEN
322 x_return_status := FND_API.G_RET_STS_ERROR ;
323 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
324 THEN
325 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOCK_FAIL');
326 FND_MSG_PUB.Add;
327 END IF;
328 ELSE
329 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
330 THEN
331 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
332 END IF;
333 END IF;
334 FND_MSG_PUB.Count_And_Get
335 (
336 p_count => x_msg_count ,
337 p_data => x_msg_data ,
338 p_encoded => FND_API.G_FALSE
339 );
340 END Validate_Seasonalities;
341
342
343 END cn_seasonalities_pvt;