[Home] [Help]
PACKAGE BODY: APPS.PJM_SEIBAN_PUB
Source
1 PACKAGE BODY PJM_SEIBAN_PUB AS
2 /* $Header: PJMPSBNB.pls 120.2 2006/02/20 17:54:08 yliou ship $ */
3
4 --
5 -- Global Declarations
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) :='PJM_SEIBAN_PUB';
8
9 --
10 -- Functions and Procedures
11 --
12 PROCEDURE Validate_Data
13 ( P_seiban_number IN VARCHAR2
14 , P_seiban_name IN VARCHAR2
15 , P_operating_unit IN NUMBER
16 , P_planning_group IN VARCHAR2
17 , P_DFF IN DescFlexRecType
18 , X_return_status OUT NOCOPY VARCHAR2
19 ) IS
20
21 CURSOR v1 IS
22 SELECT s.project_id
23 FROM ( SELECT project_id , project_number FROM pjm_seiban_numbers
24 UNION ALL
25 SELECT project_id , segment1 FROM pa_projects_all ) s
26 WHERE s.project_number = P_seiban_number;
27
28 CURSOR v2 IS
29 SELECT s.project_id
30 FROM ( SELECT project_id , project_name FROM pjm_seiban_numbers
31 UNION ALL
32 SELECT project_id , name FROM pa_projects_all ) s
33 WHERE s.project_name = P_seiban_name;
34
35 CURSOR v3 IS
36 SELECT meaning
37 FROM fnd_common_lookups
38 WHERE application_id = 704
39 AND lookup_type = 'PLANNING_GROUP'
40 AND lookup_code = P_planning_group
41 AND sysdate BETWEEN nvl( start_date_active , sysdate - 1)
42 AND nvl( end_date_active , sysdate + 1)
43 AND nvl( enabled_flag , 'N' ) = 'Y';
44
45 CURSOR v4 IS
46 select organization_id from HR_ORGANIZATION_INFORMATION
47 where ORG_INFORMATION_CONTEXT||'' = 'CLASS'
48 AND ORG_INFORMATION1 = 'OPERATING_UNIT'
49 AND ORG_INFORMATION2 = 'Y'
50 AND organization_id = P_operating_unit;
51
52 v1rec v1%rowtype;
53 v2rec v2%rowtype;
54 v3rec v3%rowtype;
55 v4rec v4%rowtype;
56
57 i NUMBER;
58
59 BEGIN
60
61 X_return_status := FND_API.G_RET_STS_SUCCESS;
62
63 OPEN v1; FETCH v1 INTO v1rec; CLOSE v1;
64 OPEN v2; FETCH v2 INTO v2rec; CLOSE v2;
65 OPEN v3; FETCH v3 INTO v3rec; CLOSE v3;
66 OPEN v4; FETCH v4 INTO v4rec; CLOSE v4;
67
68 --
69 -- Check for existing project / seiban with same number or name
70 --
71 IF ( v1rec.project_id = v2rec.project_id ) THEN
72
73 FND_MESSAGE.set_name('PJM' , 'GEN-SEIBAN EXISTS');
74 FND_MSG_PUB.add;
75 X_return_status := FND_API.G_RET_STS_ERROR;
76
77 ELSE
78
79 IF ( v1rec.project_id is not null ) THEN
80 FND_MESSAGE.set_name('PJM' , 'FORM-DUPLICATE PROJECT NUM');
81 FND_MSG_PUB.add;
82 X_return_status := FND_API.G_RET_STS_ERROR;
83 END IF;
84
85 IF ( v2rec.project_id is not null ) THEN
86 FND_MESSAGE.set_name('PJM' , 'FORM-DUPLICATE PROJECT NAME');
87 FND_MSG_PUB.add;
88 X_return_status := FND_API.G_RET_STS_ERROR;
89 END IF;
90
91 END IF;
92
93 --
94 -- Make sure value for planning group is valid
95 --
96 IF ( P_planning_group is not null and v3rec.meaning is null ) THEN
97
98 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
99 FND_MESSAGE.set_token('NAME' , 'TOKEN-PLANNING GROUP' , TRUE);
100 FND_MESSAGE.set_token('VALUE' , P_planning_group);
101 FND_MSG_PUB.add;
102 X_return_status := FND_API.G_RET_STS_ERROR;
103
104 END IF;
105
106 --
107 -- Make sure value for operating unit is valid
108 --
109 IF ( P_operating_unit is not null and v4rec.organization_id is null ) THEN
110
111 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
112 FND_MESSAGE.set_token('NAME' , 'TOKEN-OPERATING UNIT' , TRUE);
113 FND_MESSAGE.set_token('VALUE' , P_operating_unit);
114 FND_MSG_PUB.add;
115 X_return_status := FND_API.G_RET_STS_ERROR;
116
117 END IF;
118
119 --
120 -- Validate Descriptive Flexfield data
121 --
122 FND_FLEX_DESCVAL.set_context_value(P_DFF.Category);
123
124 -- bug 4038998
125 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE1' , P_DFF.Attr1 );
126 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE2' , P_DFF.Attr2 );
127 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE3' , P_DFF.Attr3 );
128 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE4' , P_DFF.Attr4 );
129 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE5' , P_DFF.Attr5 );
130 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE6' , P_DFF.Attr6 );
131 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE7' , P_DFF.Attr7 );
132 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE8' , P_DFF.Attr8 );
133 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE9' , P_DFF.Attr9 );
134 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE10' , P_DFF.Attr10 );
135 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE11' , P_DFF.Attr11 );
136 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE12' , P_DFF.Attr12 );
137 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE13' , P_DFF.Attr13 );
138 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE14' , P_DFF.Attr14 );
139 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE15' , P_DFF.Attr15 );
140
141
142 IF NOT FND_FLEX_DESCVAL.validate_desccols
143 ( appl_short_name => 'PJM'
144 , desc_flex_name => 'PJM_SEIBAN_NUMBERS'
145 , values_or_ids => 'I' ) THEN
146
147 FND_MSG_PUB.add_exc_msg
148 ( p_pkg_name => G_PKG_NAME
149 , p_procedure_name => 'VALIDATE_DATA'
150 , p_error_text => FND_FLEX_DESCVAL.error_message );
151 X_return_status := FND_API.G_RET_STS_ERROR;
152
153 END IF;
154
155 EXCEPTION
156 WHEN OTHERS THEN
157 FND_MSG_PUB.add_exc_msg
158 ( p_pkg_name => G_PKG_NAME
159 , p_procedure_name => 'VALIDATE_DATA' );
160 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
161
162 END Validate_Data;
163
164
165 PROCEDURE Create_Seiban
166 ( P_api_version IN NUMBER
167 , P_init_msg_list IN VARCHAR2
168 , P_commit IN VARCHAR2
169 , X_return_status OUT NOCOPY VARCHAR2
170 , X_msg_count OUT NOCOPY NUMBER
171 , X_msg_data OUT NOCOPY VARCHAR2
172 , P_seiban_number IN VARCHAR2
173 , P_seiban_name IN VARCHAR2
174 , P_operating_unit IN NUMBER
175 , P_planning_group IN VARCHAR2
176 , P_DFF IN DescFlexRecType
177 , P_org_list IN OrgTblType
178 , X_project_id OUT NOCOPY NUMBER
179 ) IS
180
181 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SEIBAN';
182 l_api_version CONSTANT NUMBER := 1.0;
183
184 user_id NUMBER;
185 login_id NUMBER;
186 l_project_id NUMBER;
187 l_cost_group_id NUMBER; -- add for bug 4316660
188 l_param_data PJM_PROJECT_PARAM_PUB.ParamRecType;
189 i NUMBER;
190
191 BEGIN
192 --
193 -- Standard Start of API savepoint
194 --
195 SAVEPOINT create_seiban;
196
197 user_id := FND_GLOBAL.user_id;
198 login_id := FND_GLOBAL.login_id;
199
200 --
201 -- Check API incompatibility
202 --
203 IF NOT FND_API.compatible_api_call( l_api_version
204 , P_api_version
205 , l_api_name
206 , G_PKG_NAME )
207 THEN
208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
209 END IF;
210
211 --
212 -- Initialize the message table if requested.
213 --
214 IF FND_API.to_boolean( P_init_msg_list ) THEN
215 FND_MSG_PUB.initialize;
216 END IF;
217
218 --
219 -- Set API return status to success
220 --
221 X_return_status := FND_API.G_RET_STS_SUCCESS;
222
223 --
224 -- Data Validation
225 --
226 Validate_Data
227 ( P_seiban_number => P_seiban_number
228 , P_seiban_name => P_seiban_name
229 , P_operating_unit => P_operating_unit
230 , P_planning_group => P_planning_group
231 , P_DFF => P_DFF
232 , X_return_status => X_return_status
233 );
234
235 --
236 -- If anything happens, abort API
237 --
238 IF ( X_return_status = FND_API.G_RET_STS_ERROR ) THEN
239 RAISE FND_API.G_EXC_ERROR;
240 ELSIF ( X_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 END IF;
243
244 SELECT pa_projects_s.nextval INTO l_project_id FROM dual;
245
246 INSERT INTO pjm_seiban_numbers
247 ( project_id
248 , project_number
249 , project_name
250 , operating_unit
251 , attribute_category
252 , attribute1
253 , attribute2
254 , attribute3
255 , attribute4
256 , attribute5
257 , attribute6
258 , attribute7
259 , attribute8
260 , attribute9
261 , attribute10
262 , attribute11
263 , attribute12
264 , attribute13
265 , attribute14
266 , attribute15
267 , last_update_date
268 , last_updated_by
269 , creation_date
270 , created_by
271 , last_update_login
272 ) VALUES
273 ( l_project_id
274 , P_seiban_number
275 , P_seiban_name
276 , P_operating_unit
277 , P_DFF.Category
278 , P_DFF.Attr1
279 , P_DFF.Attr2
280 , P_DFF.Attr3
281 , P_DFF.Attr4
282 , P_DFF.Attr5
283 , P_DFF.Attr6
284 , P_DFF.Attr7
285 , P_DFF.Attr8
286 , P_DFF.Attr9
287 , P_DFF.Attr10
288 , P_DFF.Attr11
289 , P_DFF.Attr12
290 , P_DFF.Attr13
291 , P_DFF.Attr14
292 , P_DFF.Attr15
293 , sysdate
294 , login_id
295 , sysdate
296 , user_id
297 , login_id
298 );
299
300 IF ( P_org_list.count > 0 ) THEN
301
302 i := P_org_list.FIRST;
303
304 LOOP
305 -- bug fix 4316660
306 SELECT nvl( P_org_list(i).cost_group_id , default_cost_group_id )
307 INTO l_cost_group_id
308 FROM mtl_parameters
309 WHERE organization_id = P_org_list(i).organization_id;
310 -- end bug 4316660
311
312 l_param_data.project_id := l_project_id;
313 l_param_data.organization_id := P_org_list(i).organization_id;
314 l_param_data.cost_group_id := l_cost_group_id; -- add for bug 4316660
315 l_param_data.wip_acct_class_code := P_org_list(i).wip_acct_class_code;
316 l_param_data.start_date_active := P_org_list(i).start_date_active;
317 l_param_data.end_date_active := P_org_list(i).end_date_active;
318 l_param_data.attr_category := P_org_list(i).attr_category;
319 l_param_data.attr1 := P_org_list(i).attr1;
320 l_param_data.attr2 := P_org_list(i).attr2;
321 l_param_data.attr3 := P_org_list(i).attr3;
322 l_param_data.attr4 := P_org_list(i).attr4;
323 l_param_data.attr5 := P_org_list(i).attr5;
324 l_param_data.attr6 := P_org_list(i).attr6;
325 l_param_data.attr7 := P_org_list(i).attr7;
326 l_param_data.attr8 := P_org_list(i).attr8;
327 l_param_data.attr9 := P_org_list(i).attr9;
328 l_param_data.attr10 := P_org_list(i).attr10;
329 l_param_data.attr11 := P_org_list(i).attr11;
330 l_param_data.attr12 := P_org_list(i).attr12;
331 l_param_data.attr13 := P_org_list(i).attr13;
332 l_param_data.attr14 := P_org_list(i).attr14;
333 l_param_data.attr15 := P_org_list(i).attr15;
334
335 PJM_PROJECT_PARAM_PUB.create_project_parameter
336 ( P_api_version => P_api_version
337 , P_init_msg_list => FND_API.G_FALSE
338 , P_commit => FND_API.G_FALSE
339 , X_return_status => X_return_status
340 , X_msg_count => X_msg_count
341 , X_msg_data => X_msg_data
342 , P_param_data => l_param_data );
343
344 IF ( X_return_status = FND_API.G_RET_STS_ERROR ) THEN
345 RAISE FND_API.G_EXC_ERROR;
346 ELSIF ( X_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
348 END IF;
349
350
351 EXIT WHEN i = P_org_list.LAST;
352 i := P_org_list.NEXT(i);
353 END LOOP;
354
355 PJM_PROJECT_PARAM_PUB.update_planning_group
356 ( P_api_version => P_api_version
357 , P_init_msg_list => FND_API.G_FALSE
358 , P_commit => FND_API.G_FALSE
359 , X_return_status => X_return_status
360 , X_msg_count => X_msg_count
361 , X_msg_data => X_msg_data
362 , P_project_id => l_project_id
363 , P_planning_group => P_planning_group );
364
365 IF ( X_return_status = FND_API.G_RET_STS_ERROR ) THEN
366 RAISE FND_API.G_EXC_ERROR;
367 ELSIF ( X_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
369 END IF;
370
371
372 END IF;
373
374 --
375 -- Stanard commit check
376 --
377 IF FND_API.to_boolean( p_commit ) THEN
378 commit work;
379 END IF;
380
381 --
382 -- Standard call to get message count and if count is 1, get message
383 -- info
384 --
385 FND_MSG_PUB.count_and_get( p_count => X_Msg_Count
386 , p_data => X_Msg_Data );
387
388 X_project_id := l_project_id;
389
390 EXCEPTION
391 WHEN FND_API.G_EXC_ERROR THEN
392 ROLLBACK TO create_seiban;
393 X_Return_Status := FND_API.G_RET_STS_ERROR;
394 FND_MSG_PUB.count_and_get( p_count => X_Msg_Count
395 , p_data => X_Msg_Data );
396
397 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
398 ROLLBACK TO create_seiban;
399 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
400 FND_MSG_PUB.count_and_get( p_count => X_Msg_Count
401 , p_data => X_Msg_Data );
402
403 WHEN OTHERS THEN
404 ROLLBACK TO create_seiban;
405 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
406 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
407 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME
408 , p_procedure_name => l_api_name);
409 END IF;
410 FND_MSG_PUB.count_and_get( p_count => X_Msg_Count
411 , p_data => X_Msg_Data );
412
413 END Create_Seiban;
414
415 END PJM_SEIBAN_PUB;