[Home] [Help]
PACKAGE BODY: APPS.PJM_PROJECT_PARAM_PUB
Source
1 PACKAGE BODY PJM_PROJECT_PARAM_PUB AS
2 /* $Header: PJMPPJPB.pls 115.2 2004/03/29 22:08:35 elam noship $ */
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
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
305 CURSOR wac ( X_class_code VARCHAR2
306 , X_organization_id NUMBER
307 , X_cost_group_id NUMBER
308 , X_class_type NUMBER ) IS
309 SELECT class_code
310 FROM wip_accounting_classes wac
311 WHERE class_code = X_class_code
312 AND organization_id = X_organization_id
313 AND class_type = X_class_type
314 AND NOT ( X_cost_group_id <> G_cost_group_id
315 AND NOT EXISTS (
316 SELECT null
317 FROM cst_cg_wip_acct_classes
318 WHERE cost_group_id = X_cost_group_id
319 AND organization_id = wac.organization_id
320 AND class_code = wac.class_code
321 )
322 );
323
324 CURSOR et ( X_expenditure_type VARCHAR2
325 , X_cost_element_id NUMBER ) IS
326 SELECT expenditure_type
327 FROM cst_proj_exp_types_val_v
328 WHERE expenditure_type = X_expenditure_type
329 AND cost_element_id = X_cost_element_id
330 AND trunc(sysdate)
331 BETWEEN sys_link_start_date
332 AND nvl(sys_link_end_date , trunc(sysdate))
333 AND trunc(sysdate)
334 BETWEEN exp_type_start_date
335 AND nvl(exp_type_end_date , trunc(sysdate));
336
337 pprec pp%rowtype;
338 cgrec cg%rowtype;
339 wacrec wac%rowtype;
340 etrec et%rowtype;
341
342 BEGIN
343
344 X_return_status := FND_API.G_RET_STS_SUCCESS;
345
346 --
347 -- Make sure record does not exist
348 --
349 IF ( P_api_name = 'CREATE_PROJECT_PARAMETER' ) THEN
350 OPEN pp ( P_param_data.organization_id , P_param_data.project_id );
351 FETCH pp INTO pprec;
352 CLOSE pp;
353 IF ( pprec.project_id is not null ) THEN
354 FND_MESSAGE.set_name('PJM' , 'GEN-PARAM RECORD EXISTS');
355 FND_MSG_PUB.add;
356 X_return_status := FND_API.G_RET_STS_ERROR;
357 return;
358 END IF;
359 END IF;
360
361 --
362 -- Make sure start / end dates are valid
363 --
364 IF ( P_param_data.start_date_active > G_proj_comp_date ) THEN
365 FND_MESSAGE.set_name('PJM' , 'FORM-PARAM START DATE INVALID');
366 FND_MESSAGE.set_token('DATE' , FND_DATE.date_to_displaydate(G_proj_comp_date));
367 FND_MSG_PUB.add;
368 X_return_status := FND_API.G_RET_STS_ERROR;
369 END IF;
370
371 IF ( P_param_data.end_date_active < G_proj_start_date ) THEN
372 FND_MESSAGE.set_name('PJM' , 'FORM-PARAM END DATE INVALID');
373 FND_MESSAGE.set_token('DATE' , FND_DATE.date_to_displaydate(G_proj_start_date));
374 FND_MSG_PUB.add;
375 X_return_status := FND_API.G_RET_STS_ERROR;
376 END IF;
377
378 IF ( P_param_data.start_date_active > P_param_data.end_date_active ) THEN
379 FND_MESSAGE.set_name('PJM' , 'FORM-INVALID EFFDATE PAIR');
380 FND_MSG_PUB.add;
381 X_return_status := FND_API.G_RET_STS_ERROR;
382 END IF;
383
384 --
385 -- Make sure the control level is not task if project is seiban
386 --
387 IF ( G_seiban_flag = 1 AND G_proj_ctrl_level = 2 ) THEN
388 FND_MESSAGE.set_name('PJM' , 'GEN-TASK CONTROL NO SEIBAN');
389 FND_MSG_PUB.add;
390 X_return_status := FND_API.G_RET_STS_ERROR;
391 END IF;
392
393 --
394 -- Make sure cost group is valid for the organization
395 --
396 IF ( P_param_data.cost_group_id is not null ) THEN
397 OPEN cg ( P_param_data.organization_id , P_param_data.cost_group_id );
398 FETCH cg INTO cgrec;
399 CLOSE cg;
400 IF ( cgrec.cost_group_id is null ) THEN
401 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
402 FND_MESSAGE.set_token('NAME' , 'TOKEN-COST GROUP' , TRUE);
403 FND_MESSAGE.set_token('VALUE' , P_param_data.cost_group_id);
404 FND_MSG_PUB.add;
405 X_return_status := FND_API.G_RET_STS_ERROR;
406 END IF;
407 END IF;
408
409 --
410 -- Make sure WIP accounting class is valid for the organization
411 --
412 IF ( P_param_data.wip_acct_class_code is not null ) THEN
413 OPEN wac ( P_param_data.wip_acct_class_code
414 , P_param_data.organization_id
415 , P_param_data.cost_group_id
416 , 1 );
417 FETCH wac INTO wacrec;
418 CLOSE wac;
419 IF ( wacrec.class_code is null ) THEN
420 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
421 FND_MESSAGE.set_token('NAME' , 'TOKEN-WIP ACCT CLASS' , TRUE);
422 FND_MESSAGE.set_token('VALUE' , P_param_data.wip_acct_class_code);
423 FND_MSG_PUB.add;
424 X_return_status := FND_API.G_RET_STS_ERROR;
425 END IF;
426 END IF;
427
428 --
429 -- Make sure EAM accounting class is valid for the organization
430 --
431 IF ( P_param_data.eam_acct_class_code is not null ) THEN
432 OPEN wac ( P_param_data.eam_acct_class_code
433 , P_param_data.organization_id
434 , P_param_data.cost_group_id
435 , 6 );
436 FETCH wac INTO wacrec;
437 CLOSE wac;
438 IF ( wacrec.class_code is null ) THEN
439 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
440 FND_MESSAGE.set_token('NAME' , 'TOKEN-EAM ACCT CLASS' , TRUE);
441 FND_MESSAGE.set_token('VALUE' , P_param_data.eam_acct_class_code);
442 FND_MSG_PUB.add;
443 X_return_status := FND_API.G_RET_STS_ERROR;
444 END IF;
445 END IF;
446
447 --
448 -- Make sure various expenditure types are valid
449 --
450 IF ( P_param_data.ipv_expenditure_type is not null ) THEN
451 OPEN et ( P_param_data.ipv_expenditure_type , 1 );
452 FETCH et INTO etrec;
453 CLOSE et;
454 IF ( etrec.expenditure_type is null ) THEN
455 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
456 FND_MESSAGE.set_token('NAME' , 'TOKEN-IPV EXPENDITURE TYPE' , TRUE);
457 FND_MESSAGE.set_token('VALUE' , P_param_data.ipv_expenditure_type);
458 FND_MSG_PUB.add;
459 X_return_status := FND_API.G_RET_STS_ERROR;
460 END IF;
461 END IF;
462
463 IF ( P_param_data.erv_expenditure_type is not null ) THEN
464 OPEN et ( P_param_data.erv_expenditure_type , 1 );
465 FETCH et INTO etrec;
466 CLOSE et;
467 IF ( etrec.expenditure_type is null ) THEN
468 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
469 FND_MESSAGE.set_token('NAME' , 'TOKEN-ERV EXPENDITURE TYPE' , TRUE);
470 FND_MESSAGE.set_token('VALUE' , P_param_data.erv_expenditure_type);
471 FND_MSG_PUB.add;
472 X_return_status := FND_API.G_RET_STS_ERROR;
473 END IF;
474 END IF;
475
476 IF ( P_param_data.freight_expenditure_type is not null ) THEN
477 OPEN et ( P_param_data.freight_expenditure_type , 1 );
478 FETCH et INTO etrec;
479 CLOSE et;
480 IF ( etrec.expenditure_type is null ) THEN
481 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
482 FND_MESSAGE.set_token('NAME' , 'TOKEN-FREIGHT EXPENDITURE TYPE' , TRUE);
483 FND_MESSAGE.set_token('VALUE' , P_param_data.freight_expenditure_type);
484 FND_MSG_PUB.add;
485 X_return_status := FND_API.G_RET_STS_ERROR;
486 END IF;
487 END IF;
488
489 IF ( P_param_data.tax_expenditure_type is not null ) THEN
490 OPEN et ( P_param_data.tax_expenditure_type , 1 );
491 FETCH et INTO etrec;
492 CLOSE et;
493 IF ( etrec.expenditure_type is null ) THEN
494 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
495 FND_MESSAGE.set_token('NAME' , 'TOKEN-TAX EXPENDITURE TYPE' , TRUE);
496 FND_MESSAGE.set_token('VALUE' , P_param_data.tax_expenditure_type);
497 FND_MSG_PUB.add;
498 X_return_status := FND_API.G_RET_STS_ERROR;
499 END IF;
500 END IF;
501
502 IF ( P_param_data.misc_expenditure_type is not null ) THEN
503 OPEN et ( P_param_data.misc_expenditure_type , 1 );
504 FETCH et INTO etrec;
505 CLOSE et;
506 IF ( etrec.expenditure_type is null ) THEN
507 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
508 FND_MESSAGE.set_token('NAME' , 'TOKEN-MISC EXPENDITURE TYPE' , TRUE);
509 FND_MESSAGE.set_token('VALUE' , P_param_data.misc_expenditure_type);
510 FND_MSG_PUB.add;
511 X_return_status := FND_API.G_RET_STS_ERROR;
512 END IF;
513 END IF;
514
515 IF ( P_param_data.ppv_expenditure_type is not null ) THEN
516 OPEN et ( P_param_data.ppv_expenditure_type , 1 );
517 FETCH et INTO etrec;
518 CLOSE et;
519 IF ( etrec.expenditure_type is null ) THEN
520 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
521 FND_MESSAGE.set_token('NAME' , 'TOKEN-PPV EXPENDITURE TYPE' , TRUE);
522 FND_MESSAGE.set_token('VALUE' , P_param_data.ppv_expenditure_type);
523 FND_MSG_PUB.add;
524 X_return_status := FND_API.G_RET_STS_ERROR;
525 END IF;
526 END IF;
527
528 IF ( P_param_data.dir_item_expenditure_type is not null ) THEN
529 OPEN et ( P_param_data.dir_item_expenditure_type , 4 );
530 FETCH et INTO etrec;
531 CLOSE et;
532 IF ( etrec.expenditure_type is null ) THEN
533 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
534 FND_MESSAGE.set_token('NAME' , 'TOKEN-DIRITEM EXPENDITURE TYPE' , TRUE);
535 FND_MESSAGE.set_token('VALUE' , P_param_data.dir_item_expenditure_type);
536 FND_MSG_PUB.add;
537 X_return_status := FND_API.G_RET_STS_ERROR;
538 END IF;
539 END IF;
540
541 --
542 -- Validate Descriptive Flexfield data
543 --
544 FND_FLEX_DESCVAL.set_context_value(P_param_data.attr_category);
545 IF ( P_param_data.attr1 is not null ) THEN
546 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE1' , P_param_data.attr1 );
547 END IF;
548 IF ( P_param_data.attr2 is not null ) THEN
549 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE2' , P_param_data.attr2 );
550 END IF;
551 IF ( P_param_data.attr3 is not null ) THEN
552 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE3' , P_param_data.attr3 );
553 END IF;
554 IF ( P_param_data.attr4 is not null ) THEN
555 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE4' , P_param_data.attr4 );
556 END IF;
557 IF ( P_param_data.attr5 is not null ) THEN
558 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE5' , P_param_data.attr5 );
559 END IF;
560 IF ( P_param_data.attr6 is not null ) THEN
561 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE6' , P_param_data.attr6 );
562 END IF;
563 IF ( P_param_data.attr7 is not null ) THEN
564 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE7' , P_param_data.attr7 );
565 END IF;
566 IF ( P_param_data.attr8 is not null ) THEN
567 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE8' , P_param_data.attr8 );
568 END IF;
569 IF ( P_param_data.attr9 is not null ) THEN
570 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE9' , P_param_data.attr9 );
571 END IF;
572 IF ( P_param_data.attr10 is not null ) THEN
573 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE10' , P_param_data.attr10 );
574 END IF;
575 IF ( P_param_data.attr11 is not null ) THEN
576 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE11' , P_param_data.attr11 );
577 END IF;
578 IF ( P_param_data.attr12 is not null ) THEN
579 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE12' , P_param_data.attr12 );
580 END IF;
581 IF ( P_param_data.attr13 is not null ) THEN
582 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE13' , P_param_data.attr13 );
583 END IF;
584 IF ( P_param_data.attr14 is not null ) THEN
585 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE14' , P_param_data.attr14 );
586 END IF;
587 IF ( P_param_data.attr15 is not null ) THEN
588 FND_FLEX_DESCVAL.set_column_value( 'ATTRIBUTE15' , P_param_data.attr15 );
589 END IF;
590
591 IF NOT FND_FLEX_DESCVAL.validate_desccols
592 ( appl_short_name => 'PJM'
593 , desc_flex_name => 'PJM_PROJECT_PARAMETERS'
594 , values_or_ids => 'I' ) THEN
595
596 FND_MSG_PUB.add_exc_msg
597 ( p_pkg_name => G_PKG_NAME
598 , p_procedure_name => 'VALIDATE_DATA'
599 , p_error_text => FND_FLEX_DESCVAL.error_message );
600 X_return_status := FND_API.G_RET_STS_ERROR;
601
602 END IF;
603
604 EXCEPTION
605 WHEN OTHERS THEN
606 FND_MSG_PUB.add_exc_msg
607 ( p_pkg_name => G_pkg_name
608 , p_procedure_name => 'VALIDATE_DATA' );
609 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
610
611 END validate_data;
612
613
614 --
615 -- Public Functions and Procedures
616 --
617 PROCEDURE create_project_parameter
618 ( P_api_version IN NUMBER
619 , P_init_msg_list IN VARCHAR2
620 , P_commit IN VARCHAR2
621 , X_return_status OUT NOCOPY VARCHAR2
622 , X_msg_count OUT NOCOPY NUMBER
623 , X_msg_data OUT NOCOPY VARCHAR2
624 , P_param_data IN ParamRecType
625 ) IS
626
627 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PROJECT_PARAMETER';
628 l_api_version CONSTANT NUMBER := 1.0;
629
630 l_param_data ParamRecType;
631 l_rowid VARCHAR2(30);
632 l_user_id NUMBER := FND_GLOBAL.user_id;
633 l_login_id NUMBER := FND_GLOBAL.login_id;
634
635 BEGIN
636 --
637 -- Standard Start of API savepoint
638 --
639 SAVEPOINT create_project_param;
640
641 --
642 -- Check API incompatibility
643 --
644 IF NOT FND_API.compatible_api_call( l_api_version
645 , P_api_version
646 , l_api_name
647 , G_pkg_name )
648 THEN
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650 END IF;
651
652 --
653 -- Initialize the message table if requested.
654 --
655 IF FND_API.to_boolean( P_init_msg_list ) THEN
656 FND_MSG_PUB.initialize;
657 END IF;
658
659 --
660 -- Set API return status to success
661 --
662 X_return_status := FND_API.G_RET_STS_SUCCESS;
663
664 --
665 -- Data Defaults
666 --
667 Default_Values( P_param_data => P_param_data
668 , X_param_data => l_param_data
669 , X_return_status => X_return_status );
670
671 IF ( X_return_status = FND_API.G_RET_STS_ERROR ) THEN
672 RAISE FND_API.G_EXC_ERROR;
673 ELSIF ( X_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
675 END IF;
676
677 --
678 -- Data Validation
679 --
680 Validate_Data( P_api_name => l_api_name
681 , P_param_data => l_param_data
682 , X_return_status => X_return_status );
683
684 IF ( X_return_status = FND_API.G_RET_STS_ERROR ) THEN
685 RAISE FND_API.G_EXC_ERROR;
686 ELSIF ( X_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
687 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
688 END IF;
689
690 --
691 -- Calling Table Handler for actual DML
692 --
693 PJM_PROJECT_PARAMS_PKG.insert_row
694 ( X_rowid => l_rowid
695 , X_project_id => l_param_data.project_id
696 , X_organization_id => l_param_data.organization_id
697 , X_seiban_number_flag => G_seiban_flag
698 , X_costing_group_id => l_param_data.cost_group_id
699 , X_planning_group => G_planning_group
700 , X_wip_acct_class_code => l_param_data.wip_acct_class_code
701 , X_eam_acct_class_code => l_param_data.eam_acct_class_code
702 , X_start_date_active => l_param_data.start_date_active
703 , X_end_date_active => l_param_data.end_date_active
704 , X_ipv_expenditure_type => l_param_data.ipv_expenditure_type
705 , X_erv_expenditure_type => l_param_data.erv_expenditure_type
706 , X_freight_expenditure_type => l_param_data.freight_expenditure_type
707 , X_tax_expenditure_type => l_param_data.tax_expenditure_type
708 , X_misc_expenditure_type => l_param_data.misc_expenditure_type
709 , X_ppv_expenditure_type => l_param_data.ppv_expenditure_type
710 , X_dir_item_expenditure_type => l_param_data.dir_item_expenditure_type
711 , X_attribute_category => l_param_data.attr_category
712 , X_attribute1 => l_param_data.attr1
713 , X_attribute2 => l_param_data.attr2
714 , X_attribute3 => l_param_data.attr3
715 , X_attribute4 => l_param_data.attr4
716 , X_attribute5 => l_param_data.attr5
717 , X_attribute6 => l_param_data.attr6
718 , X_attribute7 => l_param_data.attr7
719 , X_attribute8 => l_param_data.attr8
720 , X_attribute9 => l_param_data.attr9
721 , X_attribute10 => l_param_data.attr10
722 , X_attribute11 => l_param_data.attr11
723 , X_attribute12 => l_param_data.attr12
724 , X_attribute13 => l_param_data.attr13
725 , X_attribute14 => l_param_data.attr14
726 , X_attribute15 => l_param_data.attr15
727 , X_creation_date => sysdate
728 , X_created_by => l_user_id
729 , X_last_update_date => sysdate
730 , X_last_updated_by => l_user_id
731 , X_last_update_login => l_login_id
732 );
733
734 --
735 -- Stanard commit check
736 --
737 IF FND_API.to_boolean( p_commit ) THEN
738 commit work;
739 END IF;
740
741 --
742 -- Standard call to get message count and if count is 1, get message
743 -- info
744 --
745 FND_MSG_PUB.count_and_get( p_count => X_msg_count
746 , p_data => X_msg_data );
747
748 EXCEPTION
749 WHEN FND_API.G_EXC_ERROR THEN
750 ROLLBACK TO create_project_param;
751 X_Return_Status := FND_API.G_RET_STS_ERROR;
752 FND_MSG_PUB.count_and_get( p_count => X_msg_count
753 , p_data => X_msg_data );
754
755 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
756 ROLLBACK TO create_project_param;
757 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
758 FND_MSG_PUB.count_and_get( p_count => X_msg_count
759 , p_data => X_msg_data );
760
761 WHEN OTHERS THEN
762 ROLLBACK TO create_project_param;
763 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
764 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
765 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_pkg_name
766 , p_procedure_name => l_api_name);
767 END IF;
768 FND_MSG_PUB.count_and_get( p_count => X_msg_count
769 , p_data => X_msg_data );
770
771 END create_project_parameter;
772
773
774 PROCEDURE create_project_parameter
775 ( P_api_version IN NUMBER
776 , P_init_msg_list IN VARCHAR2
777 , P_commit IN VARCHAR2
778 , X_return_status OUT NOCOPY VARCHAR2
779 , X_msg_count OUT NOCOPY NUMBER
780 , X_msg_data OUT NOCOPY VARCHAR2
781 , P_param_data IN ParamTblType
782 ) IS
783
784 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PROJECT_PARAMETER';
785 l_api_version CONSTANT NUMBER := 1.0;
786
787 i NUMBER;
788
789 BEGIN
790 --
791 -- Standard Start of API savepoint
792 --
793 SAVEPOINT create_project_param;
794
795 --
796 -- Check API incompatibility
797 --
798 IF NOT FND_API.compatible_api_call( l_api_version
799 , P_api_version
800 , l_api_name
801 , G_pkg_name )
802 THEN
803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
804 END IF;
805
806 --
807 -- Initialize the message table if requested.
808 --
809 IF FND_API.to_boolean( P_init_msg_list ) THEN
810 FND_MSG_PUB.initialize;
811 END IF;
812
813 --
814 -- Set API return status to success
815 --
816 X_return_status := FND_API.G_RET_STS_SUCCESS;
817
818 --
819 -- Loop through each record in the table and invoke the
820 -- single record API
821 --
822 IF ( P_param_data.count > 0 ) THEN
823 i := P_param_data.FIRST;
824 LOOP
825 Create_Project_Parameter
826 ( P_api_version => P_api_version
827 , P_init_msg_list => FND_API.G_FALSE
828 , P_commit => FND_API.G_FALSE
829 , X_return_status => X_return_status
830 , X_msg_count => X_msg_count
831 , X_msg_data => X_msg_data
832 , P_param_data => P_param_data(i) );
833 EXIT WHEN i = P_param_data.LAST;
834 i := P_param_data.NEXT(i);
835 END LOOP;
836 END IF;
837
838 --
839 -- Stanard commit check
840 --
841 IF FND_API.to_boolean( p_commit ) THEN
842 commit work;
843 END IF;
844
845 --
846 -- Standard call to get message count and if count is 1, get message
847 -- info
848 --
849 FND_MSG_PUB.count_and_get( p_count => X_msg_count
850 , p_data => X_msg_data );
851
852 EXCEPTION
853 WHEN FND_API.G_EXC_ERROR THEN
854 ROLLBACK TO create_project_param;
855 X_Return_Status := FND_API.G_RET_STS_ERROR;
856 FND_MSG_PUB.count_and_get( p_count => X_msg_count
857 , p_data => X_msg_data );
858
859 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
860 ROLLBACK TO create_project_param;
861 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
862 FND_MSG_PUB.count_and_get( p_count => X_msg_count
863 , p_data => X_msg_data );
864
865 WHEN OTHERS THEN
866 ROLLBACK TO create_project_param;
867 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
868 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
869 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_pkg_name
870 , p_procedure_name => l_api_name);
871 END IF;
872 FND_MSG_PUB.count_and_get( p_count => X_msg_count
873 , p_data => X_msg_data );
874
875 END create_project_parameter;
876
877
878 PROCEDURE update_planning_group
879 ( P_api_version IN NUMBER
880 , P_init_msg_list IN VARCHAR2
881 , P_commit IN VARCHAR2
882 , X_return_status OUT NOCOPY VARCHAR2
883 , X_msg_count OUT NOCOPY NUMBER
884 , X_msg_data OUT NOCOPY VARCHAR2
885 , P_project_id IN NUMBER
886 , P_planning_group IN VARCHAR2
887 ) IS
888
889 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PLANNING_GROUP';
890 l_api_version CONSTANT NUMBER := 1.0;
891
892 CURSOR pg IS
893 SELECT lookup_code
894 FROM fnd_common_lookups
895 WHERE application_id = 704
896 AND lookup_type = 'PLANNING_GROUP'
897 AND lookup_code = P_planning_group
898 AND sysdate BETWEEN nvl( start_date_active , sysdate - 1)
899 AND nvl( end_date_active , sysdate + 1)
900 AND nvl( enabled_flag , 'N' ) = 'Y';
901
902 l_planning_group VARCHAR2(30);
903
904 BEGIN
905 --
906 -- Standard Start of API savepoint
907 --
908 SAVEPOINT update_planning_group;
909
910 --
911 -- Check API incompatibility
912 --
913 IF NOT FND_API.compatible_api_call( l_api_version
914 , P_api_version
915 , l_api_name
916 , G_pkg_name )
917 THEN
918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
919 END IF;
920
921 --
922 -- Initialize the message table if requested.
923 --
924 IF FND_API.to_boolean( P_init_msg_list ) THEN
925 FND_MSG_PUB.initialize;
926 END IF;
927
928 --
929 -- Set API return status to success
930 --
931 X_return_status := FND_API.G_RET_STS_SUCCESS;
932
933 --
934 -- Make sure planning group is valid
935 --
936 IF ( P_planning_group is not null ) THEN
937 OPEN pg; FETCH pg INTO l_planning_group; CLOSE pg;
938 IF ( l_planning_group is null ) THEN
939 FND_MESSAGE.set_name('PJM' , 'GEN-INVALID VALUE');
940 FND_MESSAGE.set_token('NAME' , 'TOKEN-PLANNING GROUP' , TRUE);
941 FND_MESSAGE.set_token('VALUE' , P_planning_group);
942 FND_MSG_PUB.add;
943 RAISE FND_API.G_EXC_ERROR;
944 END IF;
945 END IF;
946
947 PJM_PROJECT_PARAMS_PKG.update_planning_group
948 ( X_project_id => P_project_id
949 , X_planning_group => P_planning_group );
950
951 --
952 -- Stanard commit check
953 --
954 IF FND_API.to_boolean( p_commit ) THEN
955 commit work;
956 END IF;
957
958 --
959 -- Standard call to get message count and if count is 1, get message
960 -- info
961 --
962 FND_MSG_PUB.count_and_get( p_count => X_msg_count
963 , p_data => X_msg_data );
964
965 EXCEPTION
966 WHEN FND_API.G_EXC_ERROR THEN
967 ROLLBACK TO update_planning_group;
968 X_Return_Status := FND_API.G_RET_STS_ERROR;
969 FND_MSG_PUB.count_and_get( p_count => X_msg_count
970 , p_data => X_msg_data );
971
972 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
973 ROLLBACK TO update_planning_group;
974 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
975 FND_MSG_PUB.count_and_get( p_count => X_msg_count
976 , p_data => X_msg_data );
977
978 WHEN OTHERS THEN
979 ROLLBACK TO update_planning_group;
980 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
981 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
982 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_pkg_name
983 , p_procedure_name => l_api_name);
984 END IF;
985 FND_MSG_PUB.count_and_get( p_count => X_msg_count
986 , p_data => X_msg_data );
987
988 END update_planning_group;
989
990 END PJM_PROJECT_PARAM_PUB;