[Home] [Help]
PACKAGE BODY: APPS.PJM_PROJECT_PARAM_PUB
Source
1 PACKAGE BODY PJM_PROJECT_PARAM_PUB AS
2 /* $Header: PJMPPJPB.pls 120.1.12020000.3 2013/03/12 07:25:59 liawei ship $ */
3
4 --
5 -- Global Declarations
6 --
7 G_pkg_name VARCHAR2(30) := 'PJM_PROJECT_PARAM_PUB';
8
9 G_ap_installed BOOLEAN := NULL;
10 G_pa_installed BOOLEAN := NULL;
11 G_project_id NUMBER := NULL;
12 G_seiban_flag NUMBER := NULL;
13 G_proj_start_date DATE := NULL;
14 G_proj_comp_date DATE := NULL;
15 G_planning_group VARCHAR2(30) := NULL;
16 G_organization_id NUMBER := NULL;
17 G_proj_ctrl_level NUMBER := NULL;
18 G_cost_method NUMBER := NULL;
19 G_cost_group_id NUMBER := NULL;
20 G_eam_enabled VARCHAR2(1) := NULL;
21 G_transfer_ipv VARCHAR2(1) := NULL;
22 G_transfer_erv VARCHAR2(1) := NULL;
23 G_transfer_freight VARCHAR2(1) := NULL;
24 G_transfer_tax VARCHAR2(1) := NULL;
25 G_transfer_misc VARCHAR2(1) := NULL;
26 G_ipv_exp_type VARCHAR2(30) := NULL;
27 G_erv_exp_type VARCHAR2(30) := NULL;
28 G_freight_exp_type VARCHAR2(30) := NULL;
29 G_tax_exp_type VARCHAR2(30) := NULL;
30 G_misc_exp_type VARCHAR2(30) := NULL;
31 G_ppv_exp_type VARCHAR2(30) := NULL;
32 G_diritem_exp_type VARCHAR2(30) := NULL;
33
34 --
35 -- Private Functions and Procedures
36 --
37 FUNCTION app_install
38 ( P_appl_short_name IN VARCHAR2
39 ) RETURN BOOLEAN IS
40
41 l_status varchar2(1);
42 l_industry varchar2(1);
43 l_ora_schema varchar2(30);
44
45 BEGIN
46 --
47 -- Call FND routine to figure out installation status
48 --
49 -- If the license status is not 'I', Project Manufacturing is
50 -- not installed.
51 --
52 IF NOT ( FND_INSTALLATION.get_app_info
53 ( P_appl_short_name , l_status , l_industry , l_ora_schema ) ) THEN
54 RETURN FALSE;
55 END IF;
56
57 IF ( l_status <> 'I' ) then
58 RETURN FALSE;
59 END IF;
60 RETURN TRUE;
61
62 END app_install;
63
64
65 PROCEDURE default_values
66 ( P_param_data IN ParamRecType
67 , X_param_data OUT NOCOPY ParamRecType
68 , X_return_status OUT NOCOPY VARCHAR2
69 ) IS
70
71 CURSOR p ( X_project_id NUMBER ) IS
72 SELECT seiban_number_flag
73 , start_date
74 , completion_date
75 FROM pjm_projects_all_v
76 WHERE project_id = X_project_id;
77
78 CURSOR pg ( X_project_id NUMBER ) IS
79 SELECT planning_group
80 FROM pjm_project_parameters
81 WHERE project_id = X_project_id;
82
83 CURSOR o ( X_organization_id NUMBER ) IS
84 SELECT p.project_control_level
85 , m.default_cost_group_id
86 , m.primary_cost_method
87 , m.eam_enabled_flag
88 , p.transfer_ipv
89 , p.transfer_erv
90 , p.transfer_freight
91 , p.transfer_tax
92 , p.transfer_misc
93 , p.ipv_expenditure_type
94 , p.erv_expenditure_type
95 , p.freight_expenditure_type
96 , p.tax_expenditure_type
97 , p.misc_expenditure_type
98 , p.ppv_expenditure_type
99 , p.dir_item_expenditure_type
100 FROM pjm_org_parameters p
101 , mtl_parameters m
102 WHERE p.organization_id = X_organization_id
103 AND m.organization_id = p.organization_id;
104
105 CURSOR wac ( X_organization_id NUMBER ) IS
106 SELECT default_discrete_class
107 FROM wip_parameters wp
108 WHERE organization_id = X_organization_id
109 AND NOT ( P_param_data.cost_group_id is not null
110 AND NOT EXISTS (
111 SELECT null
112 FROM cst_cg_wip_acct_classes
113 WHERE cost_group_id = P_param_data.cost_group_id
114 AND organization_id = wp.organization_id
115 AND class_code = wp.default_discrete_class
116 )
117 );
118
119 BEGIN
120
121 X_return_status := FND_API.G_RET_STS_SUCCESS;
122
123 X_param_data := P_param_data;
124
125 IF ( G_ap_installed is null ) THEN
126 G_ap_installed := app_install( 'SQLAP' );
127 END IF;
128
129 IF ( G_pa_installed is null ) THEN
130 G_pa_installed := app_install( 'PA' );
131 END IF;
132
133 IF ( G_project_id is null
134 OR G_project_id <> X_param_data.project_id ) THEN
135
136 OPEN p ( X_param_data.project_id );
137 FETCH p INTO G_seiban_flag
138 , G_proj_start_date
139 , G_proj_comp_date;
140 CLOSE p;
141
142 IF ( G_seiban_flag is not null ) THEN
143 G_project_id := X_param_data.project_id;
144 OPEN pg ( G_project_id );
145 FETCH pg INTO G_planning_group;
146 CLOSE pg;
147 ELSE
148 FND_MESSAGE.set_name('PJM' , 'GEN-PROJ ID INVALID');
149 FND_MESSAGE.set_token('ID' , X_param_data.project_id);
150 FND_MSG_PUB.add;
151 X_return_status := FND_API.G_RET_STS_ERROR;
152 return;
153 END IF;
154
155 END IF;
156
157 IF ( G_organization_id is null
158 OR G_organization_id <> X_param_data.organization_id ) THEN
159
160 OPEN o ( X_param_data.organization_id );
161 FETCH o INTO G_proj_ctrl_level
162 , G_cost_group_id
163 , G_cost_method
164 , G_eam_enabled
165 , G_transfer_ipv
166 , G_transfer_erv
167 , G_transfer_freight
168 , G_transfer_tax
169 , G_transfer_misc
170 , G_ipv_exp_type
171 , G_erv_exp_type
172 , G_freight_exp_type
173 , G_tax_exp_type
174 , G_misc_exp_type
175 , G_ppv_exp_type
176 , G_diritem_exp_type
177 ;
178 CLOSE o;
179
180 IF ( G_proj_ctrl_level is not null ) THEN
181 G_organization_id := X_param_data.organization_id;
182 ELSE
183 FND_MESSAGE.set_name('PJM' , 'GEN-ORG ID INVALID');
184 FND_MESSAGE.set_token('ID' , X_param_data.organization_id);
185 FND_MSG_PUB.add;
186 X_return_status := FND_API.G_RET_STS_ERROR;
187 return;
188 END IF;
189
190 END IF;
191
192 IF ( X_param_data.cost_group_id is null ) THEN
193 X_param_data.cost_group_id := G_cost_group_id;
194 END IF;
195
196 IF ( X_param_data.wip_acct_class_code is null ) THEN
197 OPEN wac ( G_organization_id );
198 FETCH wac INTO X_param_data.wip_acct_class_code;
199 CLOSE wac;
200 END IF;
201
202 IF ( X_param_data.eam_acct_class_code is not null
203 AND G_eam_enabled = 'N' ) THEN
204 X_param_data.eam_acct_class_code := NULL;
205 END IF;
206
207 IF ( G_ap_installed AND G_pa_installed AND G_seiban_flag = 2 ) THEN
208
209 IF ( G_transfer_ipv = 'Y' ) THEN
210 X_param_data.ipv_expenditure_type :=
211 nvl( X_param_data.ipv_expenditure_type , G_ipv_exp_type );
212 ELSE
213 X_param_data.ipv_expenditure_type := NULL;
214 END IF;
215
216 IF ( G_transfer_erv = 'Y' ) THEN
217 X_param_data.erv_expenditure_type :=
218 nvl( X_param_data.erv_expenditure_type , G_erv_exp_type );
219 ELSE
220 X_param_data.erv_expenditure_type := NULL;
221 END IF;
222
223 IF ( G_transfer_freight = 'Y' ) THEN
224 X_param_data.freight_expenditure_type :=
225 nvl( X_param_data.freight_expenditure_type , G_freight_exp_type );
226 ELSE
227 X_param_data.freight_expenditure_type := NULL;
228 END IF;
229
230 IF ( G_transfer_tax = 'Y' ) THEN
231 X_param_data.tax_expenditure_type :=
232 nvl( X_param_data.tax_expenditure_type , G_tax_exp_type );
233 ELSE
234 X_param_data.tax_expenditure_type := NULL;
235 END IF;
236
237 IF ( G_transfer_misc = 'Y' ) THEN
238 X_param_data.misc_expenditure_type :=
239 nvl( X_param_data.misc_expenditure_type , G_misc_exp_type );
240 ELSE
241 X_param_data.misc_expenditure_type := NULL;
242 END IF;
243
244 IF ( G_cost_method = 1 ) THEN
245 X_param_data.ppv_expenditure_type :=
246 nvl( X_param_data.ppv_expenditure_type , G_ppv_exp_type );
247 ELSE
248 X_param_data.ppv_expenditure_type := NULL;
249 END IF;
250
251 IF ( G_eam_enabled = 'Y' ) THEN
252 X_param_data.dir_item_expenditure_type :=
253 nvl( X_param_data.dir_item_expenditure_type , G_diritem_exp_type );
254 ELSE
255 X_param_data.dir_item_expenditure_type := NULL;
256 END IF;
257
258 ELSE
259
260 X_param_data.ipv_expenditure_type := NULL;
261 X_param_data.erv_expenditure_type := NULL;
262 X_param_data.freight_expenditure_type := NULL;
263 X_param_data.tax_expenditure_type := NULL;
264 X_param_data.misc_expenditure_type := NULL;
265 X_param_data.ppv_expenditure_type := NULL;
266 X_param_data.dir_item_expenditure_type := NULL;
267
268 END IF;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 FND_MSG_PUB.add_exc_msg
273 ( p_pkg_name => G_pkg_name
274 , p_procedure_name => 'DEFAULT_VALUES' );
275 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276
277 END default_values;
278
279
280 PROCEDURE validate_data
281 ( P_api_name IN VARCHAR2
282 , P_param_data IN ParamRecType
283 , X_return_status OUT NOCOPY VARCHAR2
284 ) IS
285
286 CURSOR pp ( X_organization_id NUMBER
287 , X_project_id NUMBER ) IS
288 SELECT project_id
289 FROM pjm_project_parameters
290 WHERE organization_id = X_organization_id
291 AND project_id = X_project_id;
292 --Bug15985824 FP of 11i bug 13791115.
293 CURSOR cg ( X_organization_id NUMBER
294 , X_cost_group_id NUMBER ) IS
295 SELECT cost_group_id
296 FROM cst_cost_groups
297 WHERE organization_id = X_organization_id
298 AND cost_group_id = X_cost_group_id
299 UNION ALL
300 SELECT default_cost_group_id
301 FROM mtl_parameters
302 WHERE organization_id = X_organization_id
303 AND default_cost_group_id = X_cost_group_id
304 UNION ALL
305 SELECT cost_group_id
306 FROM CST_COST_GROUP_ACCOUNTS
307 WHERE organization_id = X_organization_id
308 AND cost_group_id = X_cost_group_id;
309
310
311
312 CURSOR wac ( X_class_code VARCHAR2
313 , X_organization_id NUMBER
314 , X_cost_group_id NUMBER
315 , X_class_type NUMBER ) IS
316 SELECT class_code
317 FROM wip_accounting_classes wac
318 WHERE class_code = X_class_code
319 AND organization_id = X_organization_id
320 AND class_type = X_class_type
321 AND NOT ( X_cost_group_id <> G_cost_group_id
322 AND NOT EXISTS (
323 SELECT null
324 FROM cst_cg_wip_acct_classes
325 WHERE cost_group_id = X_cost_group_id
326 AND organization_id = wac.organization_id
327 AND class_code = wac.class_code
328 )
329 );
330
331 CURSOR et ( X_expenditure_type VARCHAR2
332 , X_cost_element_id NUMBER ) IS
333 SELECT expenditure_type
334 FROM cst_proj_exp_types_val_v
335 WHERE expenditure_type = X_expenditure_type
336 AND cost_element_id = X_cost_element_id
337 AND trunc(sysdate)
338 BETWEEN sys_link_start_date
339 AND nvl(sys_link_end_date , trunc(sysdate))
340 AND trunc(sysdate)
341 BETWEEN exp_type_start_date
342 AND nvl(exp_type_end_date , trunc(sysdate));
343
344 pprec pp%rowtype;
345 cgrec cg%rowtype;
346 wacrec wac%rowtype;
347 etrec et%rowtype;
348
349 BEGIN
350
351 X_return_status := FND_API.G_RET_STS_SUCCESS;
352
353 --
354 -- Make sure record does not exist
355 --
356 IF ( P_api_name = 'CREATE_PROJECT_PARAMETER' ) THEN
357 OPEN pp ( P_param_data.organization_id , P_param_data.project_id );
358 FETCH pp INTO pprec;
359 CLOSE pp;
360 IF ( pprec.project_id is not null ) THEN
361 FND_MESSAGE.set_name('PJM' , 'GEN-PARAM RECORD EXISTS');
362 FND_MSG_PUB.add;
363 X_return_status := FND_API.G_RET_STS_ERROR;
364 return;
365 END IF;
366 END IF;
367
368 --
369 -- Make sure start / end dates are valid
370 --
371 IF ( P_param_data.start_date_active > G_proj_comp_date ) THEN
372 FND_MESSAGE.set_name('PJM' , 'FORM-PARAM START DATE INVALID');
373 FND_MESSAGE.set_token('DATE' , FND_DATE.date_to_displaydate(dateval=>G_proj_comp_date, calendar_aware=>2));
374 FND_MSG_PUB.add;
375 X_return_status := FND_API.G_RET_STS_ERROR;
376 END IF;
377
378 IF ( P_param_data.end_date_active < G_proj_start_date ) THEN
379 FND_MESSAGE.set_name('PJM' , 'FORM-PARAM END DATE INVALID');
380 FND_MESSAGE.set_token('DATE' , FND_DATE.date_to_displaydate(dateval=>G_proj_start_date, calendar_aware=>2));
381 FND_MSG_PUB.add;
382 X_return_status := FND_API.G_RET_STS_ERROR;
383 END IF;
384
385 IF ( P_param_data.start_date_active > P_param_data.end_date_active ) THEN
386 FND_MESSAGE.set_name('PJM' , 'FORM-INVALID EFFDATE PAIR');
387 FND_MSG_PUB.add;
388 X_return_status := FND_API.G_RET_STS_ERROR;
389 END IF;
390
391 --
392 -- Make sure the control level is not task if project is seiban
393 --
394 IF ( G_seiban_flag = 1 AND G_proj_ctrl_level = 2 ) THEN
395 FND_MESSAGE.set_name('PJM' , 'GEN-TASK CONTROL NO SEIBAN');
396 FND_MSG_PUB.add;
397 X_return_status := FND_API.G_RET_STS_ERROR;
398 END IF;
399
400 --
401 -- Make sure cost group is valid for the organization
402 --
403 IF ( P_param_data.cost_group_id is not null ) THEN
404 OPEN cg ( P_param_data.organization_id , P_param_data.cost_group_id );
405 FETCH cg INTO cgrec;
406 CLOSE cg;
407 IF ( cgrec.cost_group_id is null ) THEN
408 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
409 FND_MESSAGE.set_token('NAME' , 'TOKEN-COST GROUP' , TRUE);
410 FND_MESSAGE.set_token('VALUE' , P_param_data.cost_group_id);
411 FND_MSG_PUB.add;
412 X_return_status := FND_API.G_RET_STS_ERROR;
413 END IF;
414 END IF;
415
416 --
417 -- Make sure WIP accounting class is valid for the organization
418 --
419 IF ( P_param_data.wip_acct_class_code is not null ) THEN
420 OPEN wac ( P_param_data.wip_acct_class_code
421 , P_param_data.organization_id
422 , P_param_data.cost_group_id
423 , 1 );
424 FETCH wac INTO wacrec;
425 CLOSE wac;
426 IF ( wacrec.class_code is null ) THEN
427 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
428 FND_MESSAGE.set_token('NAME' , 'TOKEN-WIP ACCT CLASS' , TRUE);
429 FND_MESSAGE.set_token('VALUE' , P_param_data.wip_acct_class_code);
430 FND_MSG_PUB.add;
431 X_return_status := FND_API.G_RET_STS_ERROR;
432 END IF;
433 END IF;
434
435 --
436 -- Make sure EAM accounting class is valid for the organization
437 --
438 IF ( P_param_data.eam_acct_class_code is not null ) THEN
439 OPEN wac ( P_param_data.eam_acct_class_code
440 , P_param_data.organization_id
441 , P_param_data.cost_group_id
442 , 6 );
443 FETCH wac INTO wacrec;
444 CLOSE wac;
445 IF ( wacrec.class_code is null ) THEN
446 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
447 FND_MESSAGE.set_token('NAME' , 'TOKEN-EAM ACCT CLASS' , TRUE);
448 FND_MESSAGE.set_token('VALUE' , P_param_data.eam_acct_class_code);
449 FND_MSG_PUB.add;
450 X_return_status := FND_API.G_RET_STS_ERROR;
451 END IF;
452 END IF;
453
454 --
455 -- Make sure various expenditure types are valid
456 --
457 IF ( P_param_data.ipv_expenditure_type is not null ) THEN
458 OPEN et ( P_param_data.ipv_expenditure_type , 1 );
459 FETCH et INTO etrec;
460 CLOSE et;
461 IF ( etrec.expenditure_type is null ) THEN
462 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
463 FND_MESSAGE.set_token('NAME' , 'TOKEN-IPV EXPENDITURE TYPE' , TRUE);
464 FND_MESSAGE.set_token('VALUE' , P_param_data.ipv_expenditure_type);
465 FND_MSG_PUB.add;
466 X_return_status := FND_API.G_RET_STS_ERROR;
467 END IF;
468 END IF;
469
470 IF ( P_param_data.erv_expenditure_type is not null ) THEN
471 OPEN et ( P_param_data.erv_expenditure_type , 1 );
472 FETCH et INTO etrec;
473 CLOSE et;
474 IF ( etrec.expenditure_type is null ) THEN
475 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
476 FND_MESSAGE.set_token('NAME' , 'TOKEN-ERV EXPENDITURE TYPE' , TRUE);
477 FND_MESSAGE.set_token('VALUE' , P_param_data.erv_expenditure_type);
478 FND_MSG_PUB.add;
479 X_return_status := FND_API.G_RET_STS_ERROR;
480 END IF;
481 END IF;
482
483 IF ( P_param_data.freight_expenditure_type is not null ) THEN
484 OPEN et ( P_param_data.freight_expenditure_type , 1 );
485 FETCH et INTO etrec;
486 CLOSE et;
487 IF ( etrec.expenditure_type is null ) THEN
488 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
489 FND_MESSAGE.set_token('NAME' , 'TOKEN-FREIGHT EXPENDITURE TYPE' , TRUE);
490 FND_MESSAGE.set_token('VALUE' , P_param_data.freight_expenditure_type);
491 FND_MSG_PUB.add;
492 X_return_status := FND_API.G_RET_STS_ERROR;
493 END IF;
494 END IF;
495
496 IF ( P_param_data.tax_expenditure_type is not null ) THEN
497 OPEN et ( P_param_data.tax_expenditure_type , 1 );
498 FETCH et INTO etrec;
499 CLOSE et;
500 IF ( etrec.expenditure_type is null ) THEN
501 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
502 FND_MESSAGE.set_token('NAME' , 'TOKEN-TAX EXPENDITURE TYPE' , TRUE);
503 FND_MESSAGE.set_token('VALUE' , P_param_data.tax_expenditure_type);
504 FND_MSG_PUB.add;
505 X_return_status := FND_API.G_RET_STS_ERROR;
506 END IF;
507 END IF;
508
509 IF ( P_param_data.misc_expenditure_type is not null ) THEN
510 OPEN et ( P_param_data.misc_expenditure_type , 1 );
511 FETCH et INTO etrec;
512 CLOSE et;
513 IF ( etrec.expenditure_type is null ) THEN
514 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
515 FND_MESSAGE.set_token('NAME' , 'TOKEN-MISC EXPENDITURE TYPE' , TRUE);
516 FND_MESSAGE.set_token('VALUE' , P_param_data.misc_expenditure_type);
517 FND_MSG_PUB.add;
518 X_return_status := FND_API.G_RET_STS_ERROR;
519 END IF;
520 END IF;
521
522 IF ( P_param_data.ppv_expenditure_type is not null ) THEN
523 OPEN et ( P_param_data.ppv_expenditure_type , 1 );
524 FETCH et INTO etrec;
525 CLOSE et;
526 IF ( etrec.expenditure_type is null ) THEN
527 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
528 FND_MESSAGE.set_token('NAME' , 'TOKEN-PPV EXPENDITURE TYPE' , TRUE);
529 FND_MESSAGE.set_token('VALUE' , P_param_data.ppv_expenditure_type);
530 FND_MSG_PUB.add;
531 X_return_status := FND_API.G_RET_STS_ERROR;
532 END IF;
533 END IF;
534
535 IF ( P_param_data.dir_item_expenditure_type is not null ) THEN
536 OPEN et ( P_param_data.dir_item_expenditure_type , 4 );
537 FETCH et INTO etrec;
538 CLOSE et;
539 IF ( etrec.expenditure_type is null ) THEN
540 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
541 FND_MESSAGE.set_token('NAME' , 'TOKEN-DIRITEM EXPENDITURE TYPE' , TRUE);
542 FND_MESSAGE.set_token('VALUE' , P_param_data.dir_item_expenditure_type);
543 FND_MSG_PUB.add;
544 X_return_status := FND_API.G_RET_STS_ERROR;
545 END IF;
546 END IF;
547
548 --
549 -- Validate Descriptive Flexfield data
550 --
551 -- Bug 16471036 FP of Bug 15932014
552 -- change Flexfield initialization per bug 4038998
553 FND_FLEX_DESCVAL.set_context_value(P_param_data.attr_category);
554
555 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE1' , P_param_data.attr1 );
556
557 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE2' , P_param_data.attr2 );
558
559 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE3' , P_param_data.attr3 );
560
561 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE4' , P_param_data.attr4 );
562
563 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE5' , P_param_data.attr5 );
564
565 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE6' , P_param_data.attr6 );
566
567 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE7' , P_param_data.attr7 );
568
569 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE8' , P_param_data.attr8 );
570
571 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE9' , P_param_data.attr9 );
572
573 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE10' , P_param_data.attr10 );
574
575 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE11' , P_param_data.attr11 );
576
577 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE12' , P_param_data.attr12 );
578
579 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE13' , P_param_data.attr13 );
580
581 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE14' , P_param_data.attr14 );
582
583 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE15' , P_param_data.attr15 );
584
585 IF NOT FND_FLEX_DESCVAL.validate_desccols
586 ( appl_short_name => 'PJM'
587 , desc_flex_name => 'PJM_PROJECT_PARAMETERS'
588 , values_or_ids => 'I' ) THEN
589
590 FND_MSG_PUB.add_exc_msg
591 ( p_pkg_name => G_PKG_NAME
592 , p_procedure_name => 'VALIDATE_DATA'
593 , p_error_text => substr(FND_FLEX_DESCVAL.error_message, 1, 240 ));
594 X_return_status := FND_API.G_RET_STS_ERROR;
595
596 END IF;
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 FND_MSG_PUB.add_exc_msg
601 ( p_pkg_name => G_pkg_name
602 , p_procedure_name => 'VALIDATE_DATA' );
603 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
604
605 END validate_data;
606
607
608 --
609 -- Public Functions and Procedures
610 --
611 PROCEDURE create_project_parameter
612 ( P_api_version IN NUMBER
613 , P_init_msg_list IN VARCHAR2
614 , P_commit IN VARCHAR2
615 , X_return_status OUT NOCOPY VARCHAR2
616 , X_msg_count OUT NOCOPY NUMBER
617 , X_msg_data OUT NOCOPY VARCHAR2
618 , P_param_data IN ParamRecType
619 ) IS
620
621 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PROJECT_PARAMETER';
622 l_api_version CONSTANT NUMBER := 1.0;
623
624 l_param_data ParamRecType;
625 l_rowid VARCHAR2(30);
626 l_user_id NUMBER := FND_GLOBAL.user_id;
627 l_login_id NUMBER := FND_GLOBAL.login_id;
628
629 BEGIN
630 --
631 -- Standard Start of API savepoint
632 --
633 SAVEPOINT create_project_param;
634
635 --
636 -- Check API incompatibility
637 --
638 IF NOT FND_API.compatible_api_call( l_api_version
639 , P_api_version
640 , l_api_name
641 , G_pkg_name )
642 THEN
643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
644 END IF;
645
646 --
647 -- Initialize the message table if requested.
648 --
649 IF FND_API.to_boolean( P_init_msg_list ) THEN
650 FND_MSG_PUB.initialize;
651 END IF;
652
653 --
654 -- Set API return status to success
655 --
656 X_return_status := FND_API.G_RET_STS_SUCCESS;
657
658 --
659 -- Data Defaults
660 --
661 Default_Values( P_param_data => P_param_data
662 , X_param_data => l_param_data
663 , X_return_status => X_return_status );
664
665 IF ( X_return_status = FND_API.G_RET_STS_ERROR ) THEN
666 RAISE FND_API.G_EXC_ERROR;
667 ELSIF ( X_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
669 END IF;
670
671 --
672 -- Data Validation
673 --
674 Validate_Data( P_api_name => l_api_name
675 , P_param_data => l_param_data
676 , X_return_status => X_return_status );
677
678 IF ( X_return_status = FND_API.G_RET_STS_ERROR ) THEN
679 RAISE FND_API.G_EXC_ERROR;
680 ELSIF ( X_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
682 END IF;
683
684 --
685 -- Calling Table Handler for actual DML
686 --
687 PJM_PROJECT_PARAMS_PKG.insert_row
688 ( X_rowid => l_rowid
689 , X_project_id => l_param_data.project_id
690 , X_organization_id => l_param_data.organization_id
691 , X_seiban_number_flag => G_seiban_flag
692 , X_costing_group_id => l_param_data.cost_group_id
693 , X_planning_group => G_planning_group
694 , X_wip_acct_class_code => l_param_data.wip_acct_class_code
695 , X_eam_acct_class_code => l_param_data.eam_acct_class_code
696 , X_start_date_active => l_param_data.start_date_active
697 , X_end_date_active => l_param_data.end_date_active
698 , X_ipv_expenditure_type => l_param_data.ipv_expenditure_type
699 , X_erv_expenditure_type => l_param_data.erv_expenditure_type
700 , X_freight_expenditure_type => l_param_data.freight_expenditure_type
701 , X_tax_expenditure_type => l_param_data.tax_expenditure_type
702 , X_misc_expenditure_type => l_param_data.misc_expenditure_type
703 , X_ppv_expenditure_type => l_param_data.ppv_expenditure_type
704 , X_dir_item_expenditure_type => l_param_data.dir_item_expenditure_type
705 , X_attribute_category => l_param_data.attr_category
706 , X_attribute1 => l_param_data.attr1
707 , X_attribute2 => l_param_data.attr2
708 , X_attribute3 => l_param_data.attr3
709 , X_attribute4 => l_param_data.attr4
710 , X_attribute5 => l_param_data.attr5
711 , X_attribute6 => l_param_data.attr6
712 , X_attribute7 => l_param_data.attr7
713 , X_attribute8 => l_param_data.attr8
714 , X_attribute9 => l_param_data.attr9
715 , X_attribute10 => l_param_data.attr10
716 , X_attribute11 => l_param_data.attr11
717 , X_attribute12 => l_param_data.attr12
718 , X_attribute13 => l_param_data.attr13
719 , X_attribute14 => l_param_data.attr14
720 , X_attribute15 => l_param_data.attr15
721 , X_creation_date => sysdate
722 , X_created_by => l_user_id
723 , X_last_update_date => sysdate
724 , X_last_updated_by => l_user_id
725 , X_last_update_login => l_login_id
726 );
727
728 --
729 -- Stanard commit check
730 --
731 IF FND_API.to_boolean( p_commit ) THEN
732 commit work;
733 END IF;
734
735 --
736 -- Standard call to get message count and if count is 1, get message
737 -- info
738 --
739 FND_MSG_PUB.count_and_get( p_count => X_msg_count
740 , p_data => X_msg_data );
741
742 EXCEPTION
743 WHEN FND_API.G_EXC_ERROR THEN
744 ROLLBACK TO create_project_param;
745 X_Return_Status := FND_API.G_RET_STS_ERROR;
746 FND_MSG_PUB.count_and_get( p_count => X_msg_count
747 , p_data => X_msg_data );
748
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 ROLLBACK TO create_project_param;
751 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
752 FND_MSG_PUB.count_and_get( p_count => X_msg_count
753 , p_data => X_msg_data );
754
755 WHEN OTHERS THEN
756 ROLLBACK TO create_project_param;
757 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
758 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
759 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_pkg_name
760 , p_procedure_name => l_api_name);
761 END IF;
762 FND_MSG_PUB.count_and_get( p_count => X_msg_count
763 , p_data => X_msg_data );
764
765 END create_project_parameter;
766
767
768 PROCEDURE create_project_parameter
769 ( P_api_version IN NUMBER
770 , P_init_msg_list IN VARCHAR2
771 , P_commit IN VARCHAR2
772 , X_return_status OUT NOCOPY VARCHAR2
773 , X_msg_count OUT NOCOPY NUMBER
774 , X_msg_data OUT NOCOPY VARCHAR2
775 , P_param_data IN ParamTblType
776 ) IS
777
778 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PROJECT_PARAMETER';
779 l_api_version CONSTANT NUMBER := 1.0;
780
781 i NUMBER;
782
783 BEGIN
784 --
785 -- Standard Start of API savepoint
786 --
787 SAVEPOINT create_project_param;
788
789 --
790 -- Check API incompatibility
791 --
792 IF NOT FND_API.compatible_api_call( l_api_version
793 , P_api_version
797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794 , l_api_name
795 , G_pkg_name )
796 THEN
798 END IF;
799
800 --
801 -- Initialize the message table if requested.
802 --
803 IF FND_API.to_boolean( P_init_msg_list ) THEN
804 FND_MSG_PUB.initialize;
805 END IF;
806
807 --
808 -- Set API return status to success
809 --
810 X_return_status := FND_API.G_RET_STS_SUCCESS;
811
812 --
813 -- Loop through each record in the table and invoke the
814 -- single record API
815 --
816 IF ( P_param_data.count > 0 ) THEN
817 i := P_param_data.FIRST;
818 LOOP
819 Create_Project_Parameter
820 ( P_api_version => P_api_version
821 , P_init_msg_list => FND_API.G_FALSE
822 , P_commit => FND_API.G_FALSE
823 , X_return_status => X_return_status
824 , X_msg_count => X_msg_count
825 , X_msg_data => X_msg_data
826 , P_param_data => P_param_data(i) );
827 EXIT WHEN i = P_param_data.LAST;
828 i := P_param_data.NEXT(i);
829 END LOOP;
830 END IF;
831
832 --
833 -- Stanard commit check
834 --
835 IF FND_API.to_boolean( p_commit ) THEN
836 commit work;
837 END IF;
838
839 --
840 -- Standard call to get message count and if count is 1, get message
841 -- info
842 --
843 FND_MSG_PUB.count_and_get( p_count => X_msg_count
844 , p_data => X_msg_data );
845
846 EXCEPTION
847 WHEN FND_API.G_EXC_ERROR THEN
848 ROLLBACK TO create_project_param;
849 X_Return_Status := FND_API.G_RET_STS_ERROR;
850 FND_MSG_PUB.count_and_get( p_count => X_msg_count
851 , p_data => X_msg_data );
852
853 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
854 ROLLBACK TO create_project_param;
855 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
856 FND_MSG_PUB.count_and_get( p_count => X_msg_count
857 , p_data => X_msg_data );
858
859 WHEN OTHERS THEN
860 ROLLBACK TO create_project_param;
861 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
862 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
863 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_pkg_name
864 , p_procedure_name => l_api_name);
865 END IF;
866 FND_MSG_PUB.count_and_get( p_count => X_msg_count
867 , p_data => X_msg_data );
868
869 END create_project_parameter;
870
871
872 PROCEDURE update_planning_group
873 ( P_api_version IN NUMBER
874 , P_init_msg_list IN VARCHAR2
875 , P_commit IN VARCHAR2
876 , X_return_status OUT NOCOPY VARCHAR2
877 , X_msg_count OUT NOCOPY NUMBER
878 , X_msg_data OUT NOCOPY VARCHAR2
879 , P_project_id IN NUMBER
880 , P_planning_group IN VARCHAR2
881 ) IS
882
883 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PLANNING_GROUP';
884 l_api_version CONSTANT NUMBER := 1.0;
885
886 CURSOR pg IS
887 SELECT lookup_code
888 FROM fnd_common_lookups
889 WHERE application_id = 704
890 AND lookup_type = 'PLANNING_GROUP'
891 AND lookup_code = P_planning_group
892 AND sysdate BETWEEN nvl( start_date_active , sysdate - 1)
893 AND nvl( end_date_active , sysdate + 1)
894 AND nvl( enabled_flag , 'N' ) = 'Y';
895
896 l_planning_group VARCHAR2(30);
897
898 BEGIN
899 --
900 -- Standard Start of API savepoint
901 --
902 SAVEPOINT update_planning_group;
903
904 --
905 -- Check API incompatibility
906 --
907 IF NOT FND_API.compatible_api_call( l_api_version
908 , P_api_version
909 , l_api_name
910 , G_pkg_name )
911 THEN
912 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
913 END IF;
914
915 --
916 -- Initialize the message table if requested.
917 --
918 IF FND_API.to_boolean( P_init_msg_list ) THEN
919 FND_MSG_PUB.initialize;
920 END IF;
921
922 --
923 -- Set API return status to success
924 --
925 X_return_status := FND_API.G_RET_STS_SUCCESS;
926
927 --
928 -- Make sure planning group is valid
929 --
930 IF ( P_planning_group is not null ) THEN
931 OPEN pg; FETCH pg INTO l_planning_group; CLOSE pg;
932 IF ( l_planning_group is null ) THEN
933 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
934 FND_MESSAGE.set_token('NAME' , 'TOKEN-PLANNING GROUP' , TRUE);
935 FND_MESSAGE.set_token('VALUE' , P_planning_group);
936 FND_MSG_PUB.add;
937 RAISE FND_API.G_EXC_ERROR;
938 END IF;
939 END IF;
940
941 PJM_PROJECT_PARAMS_PKG.update_planning_group
942 ( X_project_id => P_project_id
943 , X_planning_group => P_planning_group );
944
945 --
946 -- Stanard commit check
947 --
948 IF FND_API.to_boolean( p_commit ) THEN
949 commit work;
950 END IF;
951
952 --
953 -- Standard call to get message count and if count is 1, get message
954 -- info
955 --
956 FND_MSG_PUB.count_and_get( p_count => X_msg_count
960 WHEN FND_API.G_EXC_ERROR THEN
957 , p_data => X_msg_data );
958
959 EXCEPTION
961 ROLLBACK TO update_planning_group;
962 X_Return_Status := FND_API.G_RET_STS_ERROR;
963 FND_MSG_PUB.count_and_get( p_count => X_msg_count
964 , p_data => X_msg_data );
965
966 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
967 ROLLBACK TO update_planning_group;
968 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
969 FND_MSG_PUB.count_and_get( p_count => X_msg_count
970 , p_data => X_msg_data );
971
972 WHEN OTHERS THEN
973 ROLLBACK TO update_planning_group;
974 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
975 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
976 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_pkg_name
977 , p_procedure_name => l_api_name);
978 END IF;
979 FND_MSG_PUB.count_and_get( p_count => X_msg_count
980 , p_data => X_msg_data );
981
982 END update_planning_group;
983
984 END PJM_PROJECT_PARAM_PUB;