[Home] [Help]
PACKAGE BODY: APPS.PJM_PROJECT_PARAMS_PKG
Source
1 PACKAGE BODY PJM_PROJECT_PARAMS_PKG AS
2 /* $Header: PJMPPRMB.pls 115.2 2002/12/09 08:13:57 alaw noship $ */
3
4 PROCEDURE insert_row
5 ( X_ROWID IN OUT NOCOPY VARCHAR2
6 , X_PROJECT_ID IN NUMBER
7 , X_ORGANIZATION_ID IN NUMBER
8 , X_SEIBAN_NUMBER_FLAG IN NUMBER
9 , X_COSTING_GROUP_ID IN NUMBER
10 , X_PLANNING_GROUP IN VARCHAR2
11 , X_WIP_ACCT_CLASS_CODE IN VARCHAR2
12 , X_EAM_ACCT_CLASS_CODE IN VARCHAR2
13 , X_START_DATE_ACTIVE IN DATE
14 , X_END_DATE_ACTIVE IN DATE
15 , X_IPV_EXPENDITURE_TYPE IN VARCHAR2
16 , X_ERV_EXPENDITURE_TYPE IN VARCHAR2
17 , X_FREIGHT_EXPENDITURE_TYPE IN VARCHAR2
18 , X_TAX_EXPENDITURE_TYPE IN VARCHAR2
19 , X_MISC_EXPENDITURE_TYPE IN VARCHAR2
20 , X_PPV_EXPENDITURE_TYPE IN VARCHAR2
21 , X_DIR_ITEM_EXPENDITURE_TYPE IN VARCHAR2
22 , X_ATTRIBUTE_CATEGORY IN VARCHAR2
23 , X_ATTRIBUTE1 IN VARCHAR2
24 , X_ATTRIBUTE2 IN VARCHAR2
25 , X_ATTRIBUTE3 IN VARCHAR2
26 , X_ATTRIBUTE4 IN VARCHAR2
27 , X_ATTRIBUTE5 IN VARCHAR2
28 , X_ATTRIBUTE6 IN VARCHAR2
29 , X_ATTRIBUTE7 IN VARCHAR2
30 , X_ATTRIBUTE8 IN VARCHAR2
31 , X_ATTRIBUTE9 IN VARCHAR2
32 , X_ATTRIBUTE10 IN VARCHAR2
33 , X_ATTRIBUTE11 IN VARCHAR2
34 , X_ATTRIBUTE12 IN VARCHAR2
35 , X_ATTRIBUTE13 IN VARCHAR2
36 , X_ATTRIBUTE14 IN VARCHAR2
37 , X_ATTRIBUTE15 IN VARCHAR2
38 , X_CREATION_DATE IN DATE
39 , X_CREATED_BY IN NUMBER
40 , X_LAST_UPDATE_DATE IN DATE
41 , X_LAST_UPDATED_BY IN NUMBER
42 , X_LAST_UPDATE_LOGIN IN NUMBER
43 ) IS
44
45 CURSOR c IS
46 SELECT rowid FROM pjm_project_parameters
47 WHERE project_id = X_project_id
48 AND organization_id = X_organization_id
49 ;
50
51 BEGIN
52
53 INSERT INTO pjm_project_parameters
54 ( project_id
55 , organization_id
56 , creation_date
57 , created_by
58 , last_update_date
59 , last_updated_by
60 , last_update_login
61 , seiban_number_flag
62 , costing_group_id
63 , planning_group
64 , wip_acct_class_code
65 , eam_acct_class_code
66 , start_date_active
67 , end_date_active
68 , ipv_expenditure_type
69 , erv_expenditure_type
70 , freight_expenditure_type
71 , taX_expenditure_type
72 , misc_expenditure_type
73 , ppv_expenditure_type
74 , dir_item_expenditure_type
75 , attribute_category
76 , attribute1
77 , attribute2
78 , attribute3
79 , attribute4
80 , attribute5
81 , attribute6
82 , attribute7
83 , attribute8
84 , attribute9
85 , attribute10
86 , attribute11
87 , attribute12
88 , attribute13
89 , attribute14
90 , attribute15
91 ) values
92 ( X_project_id
93 , X_organization_id
94 , X_creation_date
95 , X_created_by
96 , X_last_update_date
97 , X_last_updated_by
98 , X_last_update_login
99 , X_seiban_number_flag
100 , X_costing_group_id
101 , X_planning_group
102 , X_wip_acct_class_code
103 , X_eam_acct_class_code
104 , X_start_date_active
105 , X_end_date_active
106 , X_ipv_expenditure_type
107 , X_erv_expenditure_type
108 , X_freight_expenditure_type
109 , X_taX_expenditure_type
110 , X_misc_expenditure_type
111 , X_ppv_expenditure_type
112 , X_dir_item_expenditure_type
113 , X_attribute_category
114 , X_attribute1
115 , X_attribute2
116 , X_attribute3
117 , X_attribute4
118 , X_attribute5
119 , X_attribute6
120 , X_attribute7
121 , X_attribute8
122 , X_attribute9
123 , X_attribute10
124 , X_attribute11
125 , X_attribute12
126 , X_attribute13
127 , X_attribute14
128 , X_attribute15
129 );
130
131 OPEN c;
132 FETCH c INTO X_ROWID;
133 IF (c%notfound) THEN
134 CLOSE c;
135 RAISE no_data_found;
136 END IF;
137 CLOSE c;
138
139 END insert_row;
140
141
142 PROCEDURE lock_row
143 ( X_PROJECT_ID IN NUMBER
144 , X_ORGANIZATION_ID IN NUMBER
145 , X_COSTING_GROUP_ID IN NUMBER
146 , X_WIP_ACCT_CLASS_CODE IN VARCHAR2
147 , X_EAM_ACCT_CLASS_CODE IN VARCHAR2
148 , X_START_DATE_ACTIVE IN DATE
149 , X_END_DATE_ACTIVE IN DATE
150 , X_IPV_EXPENDITURE_TYPE IN VARCHAR2
151 , X_ERV_EXPENDITURE_TYPE IN VARCHAR2
152 , X_FREIGHT_EXPENDITURE_TYPE IN VARCHAR2
153 , X_TAX_EXPENDITURE_TYPE IN VARCHAR2
154 , X_MISC_EXPENDITURE_TYPE IN VARCHAR2
155 , X_PPV_EXPENDITURE_TYPE IN VARCHAR2
156 , X_DIR_ITEM_EXPENDITURE_TYPE IN VARCHAR2
157 , X_ATTRIBUTE_CATEGORY IN VARCHAR2
158 , X_ATTRIBUTE1 IN VARCHAR2
159 , X_ATTRIBUTE2 IN VARCHAR2
160 , X_ATTRIBUTE3 IN VARCHAR2
161 , X_ATTRIBUTE4 IN VARCHAR2
162 , X_ATTRIBUTE5 IN VARCHAR2
163 , X_ATTRIBUTE6 IN VARCHAR2
164 , X_ATTRIBUTE7 IN VARCHAR2
165 , X_ATTRIBUTE8 IN VARCHAR2
166 , X_ATTRIBUTE9 IN VARCHAR2
167 , X_ATTRIBUTE10 IN VARCHAR2
168 , X_ATTRIBUTE11 IN VARCHAR2
169 , X_ATTRIBUTE12 IN VARCHAR2
170 , X_ATTRIBUTE13 IN VARCHAR2
171 , X_ATTRIBUTE14 IN VARCHAR2
172 , X_ATTRIBUTE15 IN VARCHAR2
173 ) IS
174
175 CURSOR c IS
176 SELECT costing_group_id
177 , wip_acct_class_code
178 , eam_acct_class_code
179 , start_date_active
180 , end_date_active
181 , ipv_expenditure_type
182 , erv_expenditure_type
183 , freight_expenditure_type
184 , taX_expenditure_type
185 , misc_expenditure_type
186 , ppv_expenditure_type
187 , dir_item_expenditure_type
188 , attribute_category
189 , attribute1
190 , attribute2
191 , attribute3
192 , attribute4
193 , attribute5
194 , attribute6
195 , attribute7
196 , attribute8
197 , attribute9
198 , attribute10
199 , attribute11
200 , attribute12
201 , attribute13
202 , attribute14
203 , attribute15
204 FROM pjm_project_parameters
205 WHERE project_id = X_project_id
206 AND organization_id = X_organization_id
207 FOR UPDATE OF project_id NOWAIT;
208
209 recinfo c%rowtype;
210
211 BEGIN
212
213 OPEN c;
214 FETCH c INTO recinfo;
215 IF (c%notfound) THEN
216 CLOSE c;
217 FND_MESSAGE.set_name('FND', 'FORM_RECORD_DELETED');
218 APP_EXCEPTION.RAISE_EXCEPTION;
219 END IF;
220 CLOSE c;
221
222 IF ( ( ( recinfo.costing_group_id = X_costing_group_id )
223 OR ( ( recinfo.costing_group_id IS NULL )
224 AND ( X_costing_group_id IS NULL ) ) )
225 AND ( ( recinfo.wip_acct_class_code = X_wip_acct_class_code )
226 OR ( ( recinfo.wip_acct_class_code IS NULL )
227 AND ( X_wip_acct_class_code IS NULL ) ) )
228 AND ( ( recinfo.eam_acct_class_code = X_eam_acct_class_code )
229 OR ( ( recinfo.eam_acct_class_code IS NULL )
230 AND ( X_eam_acct_class_code IS NULL ) ) )
231 AND ( ( recinfo.ipv_expenditure_type = X_ipv_expenditure_type )
232 OR ( ( recinfo.ipv_expenditure_type IS NULL )
233 AND ( X_ipv_expenditure_type IS NULL ) ) )
234 AND ( ( recinfo.erv_expenditure_type = X_erv_expenditure_type )
235 OR ( ( recinfo.erv_expenditure_type IS NULL )
236 AND ( X_erv_expenditure_type IS NULL ) ) )
237 AND ( ( recinfo.freight_expenditure_type = X_freight_expenditure_type )
238 OR ( ( recinfo.freight_expenditure_type IS NULL )
239 AND ( X_freight_expenditure_type IS NULL ) ) )
240 AND ( ( recinfo.tax_expenditure_type = X_tax_expenditure_type )
241 OR ( ( recinfo.tax_expenditure_type IS NULL )
242 AND ( X_tax_expenditure_type IS NULL ) ) )
243 AND ( ( recinfo.misc_expenditure_type = X_misc_expenditure_type )
244 OR ( ( recinfo.misc_expenditure_type IS NULL )
245 AND ( X_misc_expenditure_type IS NULL ) ) )
246 AND ( ( recinfo.ppv_expenditure_type = X_ppv_expenditure_type )
247 OR ( ( recinfo.ppv_expenditure_type IS NULL )
248 AND ( X_ppv_expenditure_type IS NULL ) ) )
249 AND ( ( recinfo.dir_item_expenditure_type = X_dir_item_expenditure_type )
250 OR ( ( recinfo.dir_item_expenditure_type IS NULL )
251 AND ( X_dir_item_expenditure_type IS NULL ) ) )
252 AND ( ( recinfo.start_date_active = X_start_date_active )
253 OR ( ( recinfo.start_date_active IS NULL )
254 AND ( X_start_date_active IS NULL ) ) )
255 AND ( ( recinfo.end_date_active = X_end_date_active )
256 OR ( ( recinfo.end_date_active IS NULL )
257 AND ( X_end_date_active IS NULL ) ) )
258 AND ( ( recinfo.attribute_category = X_attribute_category )
259 OR ( ( recinfo.attribute_category IS NULL )
260 AND ( X_attribute_category IS NULL ) ) )
261 AND ( ( recinfo.attribute1 = X_attribute1 )
262 OR ( ( recinfo.attribute1 IS NULL )
263 AND ( X_attribute1 IS NULL ) ) )
264 AND ( ( recinfo.attribute2 = X_attribute2 )
265 OR ( ( recinfo.attribute2 IS NULL )
266 AND ( X_attribute2 IS NULL ) ) )
267 AND ( ( recinfo.attribute3 = X_attribute3 )
268 OR ( ( recinfo.attribute3 IS NULL )
269 AND ( X_attribute3 IS NULL ) ) )
270 AND ( ( recinfo.attribute4 = X_attribute4 )
271 OR ( ( recinfo.attribute4 IS NULL )
272 AND ( X_attribute4 IS NULL ) ) )
273 AND ( ( recinfo.attribute5 = X_attribute5 )
274 OR ( ( recinfo.attribute5 IS NULL )
275 AND ( X_attribute5 IS NULL ) ) )
276 AND ( ( recinfo.attribute6 = X_attribute6 )
277 OR ( ( recinfo.attribute6 IS NULL )
278 AND ( X_attribute6 IS NULL ) ) )
279 AND ( ( recinfo.attribute7 = X_attribute7 )
280 OR ( ( recinfo.attribute7 IS NULL )
281 AND ( X_attribute7 IS NULL ) ) )
282 AND ( ( recinfo.attribute8 = X_attribute8 )
283 OR ( ( recinfo.attribute8 IS NULL )
284 AND ( X_attribute8 IS NULL ) ) )
285 AND ( ( recinfo.attribute9 = X_attribute9 )
286 OR ( ( recinfo.attribute9 IS NULL )
287 AND ( X_attribute9 IS NULL ) ) )
288 AND ( ( recinfo.attribute10 = X_attribute10 )
289 OR ( ( recinfo.attribute10 IS NULL )
290 AND ( X_attribute10 IS NULL ) ) )
291 AND ( ( recinfo.attribute11 = X_attribute11 )
292 OR ( ( recinfo.attribute11 IS NULL )
293 AND ( X_attribute11 IS NULL ) ) )
294 AND ( ( recinfo.attribute12 = X_attribute12 )
295 OR ( ( recinfo.attribute12 IS NULL )
296 AND ( X_attribute12 IS NULL ) ) )
297 AND ( ( recinfo.attribute13 = X_attribute13 )
298 OR ( ( recinfo.attribute13 IS NULL )
299 AND ( X_attribute13 IS NULL ) ) )
300 AND ( ( recinfo.attribute14 = X_attribute14 )
301 OR ( ( recinfo.attribute14 IS NULL )
302 AND ( X_attribute14 IS NULL ) ) )
303 AND ( ( recinfo.attribute15 = X_attribute15 )
304 OR ( ( recinfo.attribute15 IS NULL )
305 AND ( X_attribute15 IS NULL ) ) )
306 ) THEN
307 NULL;
308 ELSE
309 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
310 APP_EXCEPTION.RAISE_EXCEPTION;
311 END IF;
312
313 RETURN;
314
315 END lock_row;
316
317
318 PROCEDURE update_row
319 ( X_PROJECT_ID IN NUMBER
320 , X_ORGANIZATION_ID IN NUMBER
321 , X_COSTING_GROUP_ID IN NUMBER
322 , X_WIP_ACCT_CLASS_CODE IN VARCHAR2
323 , X_EAM_ACCT_CLASS_CODE IN VARCHAR2
324 , X_START_DATE_ACTIVE IN DATE
325 , X_END_DATE_ACTIVE IN DATE
326 , X_IPV_EXPENDITURE_TYPE IN VARCHAR2
327 , X_ERV_EXPENDITURE_TYPE IN VARCHAR2
328 , X_FREIGHT_EXPENDITURE_TYPE IN VARCHAR2
329 , X_TAX_EXPENDITURE_TYPE IN VARCHAR2
330 , X_MISC_EXPENDITURE_TYPE IN VARCHAR2
331 , X_PPV_EXPENDITURE_TYPE IN VARCHAR2
332 , X_DIR_ITEM_EXPENDITURE_TYPE IN VARCHAR2
333 , X_ATTRIBUTE_CATEGORY IN VARCHAR2
334 , X_ATTRIBUTE1 IN VARCHAR2
335 , X_ATTRIBUTE2 IN VARCHAR2
336 , X_ATTRIBUTE3 IN VARCHAR2
337 , X_ATTRIBUTE4 IN VARCHAR2
338 , X_ATTRIBUTE5 IN VARCHAR2
339 , X_ATTRIBUTE6 IN VARCHAR2
340 , X_ATTRIBUTE7 IN VARCHAR2
341 , X_ATTRIBUTE8 IN VARCHAR2
342 , X_ATTRIBUTE9 IN VARCHAR2
343 , X_ATTRIBUTE10 IN VARCHAR2
344 , X_ATTRIBUTE11 IN VARCHAR2
345 , X_ATTRIBUTE12 IN VARCHAR2
346 , X_ATTRIBUTE13 IN VARCHAR2
347 , X_ATTRIBUTE14 IN VARCHAR2
348 , X_ATTRIBUTE15 IN VARCHAR2
349 , X_LAST_UPDATE_DATE IN DATE
350 , X_LAST_UPDATED_BY IN NUMBER
351 , X_LAST_UPDATE_LOGIN IN NUMBER
352 ) IS
353 BEGIN
354
355 UPDATE pjm_project_parameters
356 SET costing_group_id = X_costing_group_id
357 , wip_acct_class_code = X_wip_acct_class_code
358 , eam_acct_class_code = X_eam_acct_class_code
359 , ipv_expenditure_type = X_ipv_expenditure_type
360 , erv_expenditure_type = X_erv_expenditure_type
361 , freight_expenditure_type = X_freight_expenditure_type
362 , taX_expenditure_type = X_taX_expenditure_type
363 , misc_expenditure_type = X_misc_expenditure_type
364 , ppv_expenditure_type = X_ppv_expenditure_type
365 , dir_item_expenditure_type = X_dir_item_expenditure_type
366 , start_date_active = X_start_date_active
367 , end_date_active = X_end_date_active
368 , attribute_category = X_attribute_category
369 , attribute1 = X_attribute1
370 , attribute2 = X_attribute2
371 , attribute3 = X_attribute3
372 , attribute4 = X_attribute4
373 , attribute5 = X_attribute5
374 , attribute6 = X_attribute6
375 , attribute7 = X_attribute7
376 , attribute8 = X_attribute8
377 , attribute9 = X_attribute9
378 , attribute10 = X_attribute10
379 , attribute11 = X_attribute11
380 , attribute12 = X_attribute12
381 , attribute13 = X_attribute13
382 , attribute14 = X_attribute14
383 , attribute15 = X_attribute15
384 , last_update_date = X_last_update_date
385 , last_updated_by = X_last_updated_by
386 , last_update_login = X_last_update_login
387 WHERE project_id = X_project_id
388 AND organization_id = X_organization_id;
389
390 IF (sql%notfound ) THEN
391 RAISE no_data_found;
392 END IF;
393
394 END update_row;
395
396
397 PROCEDURE delete_row
398 ( X_PROJECT_ID IN NUMBER
399 , X_ORGANIZATION_ID IN NUMBER
400 ) IS
401 BEGIN
402
403 DELETE FROM pjm_project_parameters
404 WHERE project_id = X_project_id
405 AND organization_id = X_organization_id;
406
407 IF (sql%notfound ) THEN
408 RAISE no_data_found;
409 END IF;
410
411 END delete_row;
412
413
414 PROCEDURE update_planning_group
415 ( X_PROJECT_ID IN NUMBER
416 , X_PLANNING_GROUP IN VARCHAR2
417 ) IS
418
419 --
420 -- Making this procedure as AUTONOMOUS transaction
421 --
422 -- pragma autonomous_transaction;
423
424 CURSOR pp IS
425 SELECT organization_id
426 FROM pjm_project_parameters
427 WHERE project_id = X_project_id
428 FOR UPDATE OF planning_group NOWAIT;
429
430 BEGIN
431
432 FOR pprec IN pp LOOP
433
434 UPDATE pjm_project_parameters
435 SET planning_group = X_planning_group
436 , last_update_date = sysdate
437 , last_updated_by = fnd_global.user_id
438 , last_update_login = fnd_global.login_id
439 WHERE project_id = X_project_id
440 AND organization_id = pprec.organization_id;
441
442 END LOOP;
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 raise;
447
448 END update_planning_group;
449
450 END PJM_PROJECT_PARAMS_PKG;