[Home] [Help]
PACKAGE BODY: APPS.PJI_SETUP_PKG
Source
1 PACKAGE BODY Pji_Setup_Pkg AS
2 /* $Header: PJIUT04B.pls 120.2 2005/12/06 18:04:23 appldev noship $ */
3
4 ----------------------------------------------------------------------------------------------------------------
5 -- API : pji_sys_settings_update_row
6 -- Description : This procedure validates and updates PJI_SYSTEM_SETTINGS Table.
7 ----------------------------------------------------------------------------------------------------------------
8 PROCEDURE pji_sys_settings_update_row (p_organization_structure IN VARCHAR2,
9 p_org_structure_version IN VARCHAR2,
10 p_dflt_prjpip_period_type IN VARCHAR2,
11 p_dflt_prjpip_as_of_date IN VARCHAR2,
12 p_dflt_prjpip_cycle IN VARCHAR2,
13 p_dflt_prjbab_period_type IN VARCHAR2,
14 p_dflt_prjbab_as_of_date IN VARCHAR2,
15 p_dflt_prjbab_cycle IN VARCHAR2,
16 p_dflt_resutl_period_type IN VARCHAR2,
17 p_dflt_resutl_as_of_date IN VARCHAR2,
18 p_dflt_resutl_cycle IN VARCHAR2,
19 p_dflt_resavl_period_type IN VARCHAR2,
20 p_dflt_resavl_as_of_date IN VARCHAR2,
21 p_dflt_resavl_cycle IN VARCHAR2,
22 p_dflt_respln_period_type IN VARCHAR2,
23 p_dflt_respln_as_of_date IN VARCHAR2,
24 p_dflt_respln_cycle IN VARCHAR2,
25 p_dflt_prjhlt_period_type IN VARCHAR2,
26 p_dflt_prjhlt_as_of_date IN VARCHAR2,
27 p_dflt_prjhlt_cycle IN VARCHAR2,
28 p_dflt_prjact_period_type IN VARCHAR2,
29 p_dflt_prjact_as_of_date IN VARCHAR2,
30 p_dflt_prjact_cycle IN VARCHAR2,
31 p_dflt_prjprf_period_type IN VARCHAR2,
32 p_dflt_prjprf_as_of_date IN VARCHAR2,
33 p_dflt_prjprf_cycle IN VARCHAR2,
34 p_dflt_prjcst_period_type IN VARCHAR2,
35 p_dflt_prjcst_as_of_date IN VARCHAR2,
36 p_dflt_prjcst_cycle IN VARCHAR2,
37 p_pa_period_flag IN VARCHAR2,
38 p_gl_period_flag IN VARCHAR2,
39 p_conversion_ratio_days IN NUMBER,
40 p_book_to_bill_days IN NUMBER,
41 p_dso_days IN NUMBER,
42 p_dormant_backlog_days IN NUMBER,
43 p_cost_budget_type IN VARCHAR2,
44 p_cost_budget_conv_rule IN VARCHAR2,
45 p_revenue_budget_type IN VARCHAR2,
46 p_revenue_budget_conv_rule IN VARCHAR2,
47 p_cost_forecast_type IN VARCHAR2,
48 p_cost_forecast_conv_rule IN VARCHAR2,
49 p_revenue_forecast_type IN VARCHAR2,
50 p_revenue_forecast_conv_rule IN VARCHAR2,
51 p_report_cost_type IN VARCHAR2,
52 p_report_labor_units IN VARCHAR2,
53 p_rolling_weeks IN NUMBER,
54 p_config_proj_perf_flag IN VARCHAR2,
55 p_config_cost_flag IN VARCHAR2,
56 p_config_profit_flag IN VARCHAR2,
57 p_config_util_flag IN VARCHAR2,
58 p_cost_fp_type_id IN NUMBER,
59 p_revenue_fp_type_id IN NUMBER,
60 p_cost_forecast_fp_type_id IN NUMBER,
61 p_revenue_forecast_fp_type_id IN NUMBER,
62 p_global_curr2_flag IN VARCHAR2,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_error_message_code OUT NOCOPY VARCHAR2
65 ) IS
66
67
68 l_dflt_prjpip_cycle_id NUMBER ;
69 l_dflt_prjbab_cycle_id NUMBER ;
70 l_dflt_resutl_cycle_id NUMBER ;
71 l_dflt_resavl_cycle_id NUMBER ;
72 l_dflt_respln_cycle_id NUMBER ;
73 l_dflt_prjhlt_cycle_id NUMBER ;
74 l_dflt_prjact_cycle_id NUMBER ;
75 l_dflt_prjprf_cycle_id NUMBER ;
76 l_dflt_prjcst_cycle_id NUMBER ;
77
78
79
80 l_cost_budget_type_code VARCHAR2(30) ;
81 l_revenue_budget_type_code VARCHAR2(30) ;
82 l_cost_forecast_type_code VARCHAR2(30) ;
83 l_revenue_forecast_type_code VARCHAR2(30) ;
84
85 l_organization_structure_id NUMBER ;
86 l_org_structure_version_id NUMBER ;
87
88 l_return_status VARCHAR2(30);
89 l_error_message_code VARCHAR2(30);
90
91 l_created_by NUMBER;
92 l_last_updated_by NUMBER;
93 l_creation_date DATE;
94 l_last_update_date DATE;
95 l_last_update_login NUMBER;
96
97 BEGIN
98 -- need to check status, force user to use adjust if necessary
99
100 IF (p_organization_structure IS NULL) THEN
101
102 x_return_status := 'E';
103 x_error_message_code := 'PJI_ORG_STRUCT_NULL' ;
104 RETURN ;
105
106 ELSIF ( p_org_structure_version IS NULL) THEN
107
108 x_return_status := 'E';
109 x_error_message_code := 'PJI_ORG_STRUCT_VER_NULL' ;
110 RETURN ;
111
112 ELSIF ( p_dflt_prjpip_period_type IS NULL OR
113 p_dflt_prjpip_as_of_date IS NULL OR
114 p_dflt_prjbab_period_type IS NULL OR
115 p_dflt_prjbab_as_of_date IS NULL OR
116 p_dflt_resutl_period_type IS NULL OR
117 p_dflt_resutl_as_of_date IS NULL OR
118 p_dflt_resavl_period_type IS NULL OR
119 p_dflt_resavl_as_of_date IS NULL OR
120 p_dflt_respln_period_type IS NULL OR
121 p_dflt_respln_as_of_date IS NULL OR
122 p_dflt_prjhlt_period_type IS NULL OR
123 p_dflt_prjhlt_as_of_date IS NULL OR
124 p_dflt_prjact_period_type IS NULL OR
125 p_dflt_prjact_as_of_date IS NULL OR
126 p_dflt_prjprf_period_type IS NULL OR
127 p_dflt_prjprf_as_of_date IS NULL OR
128 p_dflt_prjcst_period_type IS NULL OR
129 p_dflt_prjcst_as_of_date IS NULL ) THEN
130
131 x_return_status := 'E';
132 x_error_message_code := 'PJI_REP_DEFAULTS_NULL' ;
133 RETURN ;
134
135 ELSIF ( p_conversion_ratio_days IS NULL) OR (p_conversion_ratio_days <= 0 )THEN
136
137 x_return_status := 'E';
138 x_error_message_code := 'PJI_CONV_RATIO_DAYS_NULL' ;
139 RETURN ;
140
141 ELSIF ( p_book_to_bill_days IS NULL) OR (p_book_to_bill_days <= 0 )THEN
142
143 x_return_status := 'E';
144 x_error_message_code := 'PJI_BOOK_TO_BILL_DAYS_NULL' ;
145 RETURN ;
146
147 ELSIF ( p_dso_days IS NULL) OR (p_dso_days <= 0 )THEN
148
149 x_return_status := 'E';
150 x_error_message_code := 'PJI_DSO_DAYS_NULL' ;
151 RETURN ;
152
153 ELSIF ( p_dormant_backlog_days IS NULL) OR (p_dormant_backlog_days <= 0 )THEN
154
155 x_return_status := 'E';
156 x_error_message_code := 'PJI_DOR_BACKLOG_NULL' ;
157 RETURN ;
158 ELSIF ( p_rolling_weeks IS NULL) OR (p_rolling_weeks <= 0 )THEN
159
160 x_return_status := 'E';
161 x_error_message_code := 'PJI_ROLL_WEEK_NULL' ;
162 RETURN ;
163
164 ELSIF ( p_cost_budget_type IS NULL OR
165 p_cost_budget_conv_rule IS NULL OR
166 p_revenue_budget_type IS NULL OR
167 p_revenue_budget_conv_rule IS NULL OR
168 p_cost_forecast_type IS NULL OR
169 p_cost_forecast_conv_rule IS NULL OR
170 p_revenue_forecast_type IS NULL OR
171 p_revenue_forecast_conv_rule IS NULL ) THEN
172
173 x_return_status := 'E';
174 x_error_message_code := 'PJI_DOR_BACKLOG_NULL' ;
175 RETURN ;
176
177 END IF;
178
179 --
180 -- Validate organization Structure
181 --
182
183 Check_Org_structure
184 ( p_Org_structure => p_organization_structure
185 ,x_Org_structure_id => l_organization_structure_id
186 ,x_return_status => l_return_status
187 ,x_error_message_code => l_error_message_code) ;
188
189 IF (l_return_status <> 'S') THEN
190
191 x_return_status := 'E';
192 x_error_message_code := l_error_message_code ;
193 RETURN ;
194
195 END IF;
196
197 --
198 -- Validate Organization Structure Version
199 --
200
201 Check_Org_structure_Version
202 ( p_Org_structure_version => p_org_structure_version
203 ,p_Org_structure_id => l_organization_structure_id
204 ,x_Org_structure_version_id => l_org_structure_version_id
205 ,x_return_status => l_return_status
206 ,x_error_message_code => l_error_message_code );
207
208 IF (l_return_status <> 'S') THEN
209
210 x_return_status := 'E';
211 x_error_message_code := l_error_message_code ;
212 RETURN ;
213
214 END IF;
215
216 --
217 -- Validate Cost Budget Type
218 --
219 Check_Budget_Type
220 ( p_budget_type => p_cost_budget_type
221 ,p_amount_type_code => 'C'
222 ,x_budget_type_code => l_cost_budget_type_code
223 ,x_return_status => l_return_status
224 ,x_error_message_code => l_error_message_code) ;
225
226 IF (l_return_status <> 'S') THEN
227
228 x_return_status := 'E';
229 IF l_error_message_code = 'PA_BUDGET_TYPE_INVALID' THEN
230 x_error_message_code := 'PJI_COST_BUDGET_TYPE_INVALID' ;
231
232 ELSIF l_error_message_code = 'PA_BUDGET_TYPE_AMBIGUOUS' THEN
233 x_error_message_code := 'PJI_COST_BUDGET_TYPE_AMBIGUOUS' ;
234
235 END IF ;
236 RETURN ;
237
238 END IF;
239
240
241 --
242 -- Validate Revenue Budget Type
243 --
244 Check_Budget_Type
245 ( p_budget_type => p_revenue_budget_type
246 ,p_amount_type_code => 'R'
247 ,x_budget_type_code => l_revenue_budget_type_code
248 ,x_return_status => l_return_status
249 ,x_error_message_code => l_error_message_code) ;
250
251 IF (l_return_status <> 'S') THEN
252
253 x_return_status := 'E';
254 IF l_error_message_code = 'PA_BUDGET_TYPE_INVALID' THEN
255 x_error_message_code := 'PJI_REV_BUDGET_TYPE_INVALID' ;
256
257 ELSIF l_error_message_code = 'PA_BUDGET_TYPE_AMBIGUOUS' THEN
258 x_error_message_code := 'PJI_REV_BUDGET_TYPE_AMBIGUOUS' ;
259
260 END IF ;
261 RETURN ;
262
263 END IF;
264
265
266 --
267 -- Validate Cost Forecast Type
268 --
269 Check_Budget_Type
270 ( p_budget_type => p_cost_forecast_type
271 ,p_amount_type_code => 'C'
272 ,x_budget_type_code => l_cost_forecast_type_code
273 ,x_return_status => l_return_status
274 ,x_error_message_code => l_error_message_code) ;
275
276 IF (l_return_status <> 'S') THEN
277
278 x_return_status := 'E';
279 IF l_error_message_code = 'PA_BUDGET_TYPE_INVALID' THEN
280 x_error_message_code := 'PJI_COST_FORECAST_TYPE_INVALID' ;
281
282 ELSIF l_error_message_code = 'PA_BUDGET_TYPE_AMBIGUOUS' THEN
283 x_error_message_code := 'PJI_COST_FORECAST_TYPE_AMBIG' ;
284
285 END IF ;
286 RETURN ;
287
288 END IF;
289
290 --
291 -- Validate Revenue Forecast Type
292 --
293 Check_Budget_Type
294 ( p_budget_type => p_revenue_forecast_type
295 ,p_amount_type_code => 'R'
296 ,x_budget_type_code => l_revenue_forecast_type_code
297 ,x_return_status => l_return_status
298 ,x_error_message_code => l_error_message_code) ;
299
300 IF (l_return_status <> 'S') THEN
301
302 x_return_status := 'E';
303 IF l_error_message_code = 'PA_BUDGET_TYPE_INVALID' THEN
304 x_error_message_code := 'PJI_REV_FORECAST_TYPE_INVALID' ;
305
306 ELSIF l_error_message_code = 'PA_BUDGET_TYPE_AMBIGUOUS' THEN
307 x_error_message_code := 'PJI_REV_FORECAST_TYPE_AMBIG' ;
308
309 END IF ;
310 RETURN ;
311
312 END IF;
313
314
315 l_created_by := Fnd_Profile.value('USER_ID');
316 l_last_updated_by := Fnd_Profile.value('USER_ID');
317 l_creation_date := SYSDATE;
318 l_last_update_date := SYSDATE;
319 l_last_update_login := Fnd_Profile.value('USER_ID');
320
321 --
322 -- Keep track of the changes(Audit).
323 --
324
325 pji_insert_events_log (
326 p_organization_structure_id => l_organization_structure_id,
327 p_org_structure_version_id => l_org_structure_version_id,
328 p_dflt_prjpip_period_type => p_dflt_prjpip_period_type,
329 p_dflt_prjpip_as_of_date => p_dflt_prjpip_as_of_date,
330 p_dflt_prjbab_period_type => p_dflt_prjbab_period_type,
331 p_dflt_prjbab_as_of_date => p_dflt_prjbab_as_of_date,
332 p_dflt_resutl_period_type => p_dflt_resutl_period_type,
333 p_dflt_resutl_as_of_date => p_dflt_resutl_as_of_date,
334 p_dflt_resavl_period_type => p_dflt_resavl_period_type,
335 p_dflt_resavl_as_of_date => p_dflt_resavl_as_of_date,
336 p_dflt_respln_period_type => p_dflt_respln_period_type,
337 p_dflt_respln_as_of_date => p_dflt_respln_as_of_date,
338 p_dflt_prjhlt_period_type => p_dflt_prjhlt_period_type,
339 p_dflt_prjhlt_as_of_date => p_dflt_prjhlt_as_of_date,
340 p_dflt_prjact_period_type => p_dflt_prjact_period_type,
341 p_dflt_prjact_as_of_date => p_dflt_prjact_as_of_date,
342 p_dflt_prjprf_period_type => p_dflt_prjprf_period_type,
343 p_dflt_prjprf_as_of_date => p_dflt_prjprf_as_of_date,
344 p_dflt_prjcst_period_type => p_dflt_prjcst_period_type,
345 p_dflt_prjcst_as_of_date => p_dflt_prjcst_as_of_date,
346 p_pa_period_flag => p_pa_period_flag,
347 p_gl_period_flag => p_gl_period_flag,
348 p_conversion_ratio_days => p_conversion_ratio_days,
349 p_book_to_bill_days => p_book_to_bill_days,
350 p_dso_days => p_dso_days,
351 p_dormant_backlog_days => p_dormant_backlog_days,
352 p_cost_budget_type_code => l_cost_budget_type_code,
353 p_cost_budget_conv_rule => p_cost_budget_conv_rule,
354 p_revenue_budget_type_code => l_revenue_budget_type_code,
355 p_revenue_budget_conv_rule => p_revenue_budget_conv_rule,
356 p_cost_forecast_type_code => l_cost_forecast_type_code,
357 p_cost_forecast_conv_rule => p_cost_forecast_conv_rule,
358 p_revenue_forecast_type_code => l_revenue_forecast_type_code,
359 p_revenue_forecast_conv_rule => p_revenue_forecast_conv_rule,
360 p_report_cost_type => p_report_cost_type,
361 p_report_labor_units => p_report_labor_units,
362 p_rolling_weeks => p_rolling_weeks,
363 p_config_proj_perf_flag => p_config_proj_perf_flag,
364 p_config_cost_flag => p_config_cost_flag,
365 p_config_profit_flag => p_config_profit_flag,
366 p_config_util_flag => p_config_util_flag,
367 p_cost_fp_type_id => p_cost_fp_type_id,
368 p_revenue_fp_type_id => p_revenue_fp_type_id,
369 p_cost_forecast_fp_type_id => p_cost_forecast_fp_type_id,
370 p_revenue_forecast_fp_type_id => p_revenue_forecast_fp_type_id,
371 p_global_curr2_flag => p_global_curr2_flag,
372 x_return_status => x_return_status,
373 x_error_message_code => x_error_message_code);
374
375
376 IF (x_return_status <> 'S') THEN
377
378 x_return_status := 'E';
379 x_error_message_code := 'PJI_ERROR_CREATING_LOG' ;
380 RETURN ;
381
382 END IF;
383
384
385 --
386 -- Apply the changes.
387 --
388
389 UPDATE pji_system_settings
390 SET last_update_date = l_last_update_date,
391 last_updated_by = l_last_updated_by,
392 last_update_login = l_last_update_login,
393 organization_structure_id = l_organization_structure_id,
394 org_structure_version_id = l_org_structure_version_id,
395 dflt_prjpip_period_type = p_dflt_prjpip_period_type,
396 dflt_prjpip_as_of_date = p_dflt_prjpip_as_of_date,
397 dflt_prjbab_period_type = p_dflt_prjbab_period_type,
398 dflt_prjbab_as_of_date = p_dflt_prjbab_as_of_date,
399 dflt_resutl_period_type = p_dflt_resutl_period_type,
400 dflt_resutl_as_of_date = p_dflt_resutl_as_of_date,
401 dflt_resavl_period_type = p_dflt_resavl_period_type,
402 dflt_resavl_as_of_date = p_dflt_resavl_as_of_date,
403 dflt_respln_period_type = p_dflt_respln_period_type,
404 dflt_respln_as_of_date = p_dflt_respln_as_of_date,
405 dflt_prjhlt_period_type = p_dflt_prjhlt_period_type,
406 dflt_prjhlt_as_of_date = p_dflt_prjhlt_as_of_date,
407 dflt_prjact_period_type = p_dflt_prjact_period_type,
408 dflt_prjact_as_of_date = p_dflt_prjact_as_of_date,
409 dflt_prjprf_period_type = p_dflt_prjprf_period_type,
410 dflt_prjprf_as_of_date = p_dflt_prjprf_as_of_date,
411 dflt_prjcst_period_type = p_dflt_prjcst_period_type,
412 dflt_prjcst_as_of_date = p_dflt_prjcst_as_of_date,
413 pa_period_flag = p_pa_period_flag,
414 gl_period_flag = p_gl_period_flag,
415 conversion_ratio_days = p_conversion_ratio_days,
416 book_to_bill_days = p_book_to_bill_days,
417 dso_days = p_dso_days,
418 dormant_backlog_days = p_dormant_backlog_days,
419 cost_budget_type_code = l_cost_budget_type_code,
420 cost_budget_conv_rule = p_cost_budget_conv_rule,
421 revenue_budget_type_code = l_revenue_budget_type_code,
422 revenue_budget_conv_rule = p_revenue_budget_conv_rule,
423 cost_forecast_type_code = l_cost_forecast_type_code,
424 cost_forecast_conv_rule = p_cost_forecast_conv_rule,
425 revenue_forecast_type_code = l_revenue_forecast_type_code,
426 revenue_forecast_conv_rule = p_revenue_forecast_conv_rule,
427 report_cost_type = p_report_cost_type,
428 report_labor_units = p_report_labor_units,
429 rolling_weeks = p_rolling_weeks,
430 config_proj_perf_flag = p_config_proj_perf_flag,
431 config_cost_flag = p_config_cost_flag,
432 config_profit_flag = p_config_profit_flag,
433 config_util_flag = p_config_util_flag,
434 cost_fp_type_id = p_cost_fp_type_id,
435 revenue_fp_type_id = p_revenue_fp_type_id,
436 cost_forecast_fp_type_id = p_cost_forecast_fp_type_id,
437 revenue_forecast_fp_type_id = p_revenue_forecast_fp_type_id,
438 global_curr2_flag = p_global_curr2_flag;
439
440
441 x_return_status := 'S';
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 -- Handle the exception.
446 x_return_status := 'U';
447 x_error_message_code := SQLERRM ;
448 END pji_sys_settings_update_row;
449
450 ----------------------------------------------------------------------------------------------------------------
451 -- API : pji_mt_pip_update_row
452 -- Description : This procedure validates and updates Project Probability Buckets.
453 -- Parameters :
454 -- IN :p_name
455 -- p_seq
456 -- p_bucket_set_code
457 -- p_default_flag
458 -- p_from_value
459 -- p_to_value
460 -- OUT NOCOPY :x_return_status - Return status.
461 -- x_error_message_code - Return Error Code.
462 ----------------------------------------------------------------------------------------------------------------
463 PROCEDURE pji_mt_pip_update_row (p_name IN VARCHAR2,
464 p_seq IN NUMBER,
465 p_bucket_set_code IN VARCHAR2,
466 p_default_flag IN VARCHAR2,
467 p_from_value IN NUMBER,
468 p_to_value IN NUMBER,
469 x_return_status OUT NOCOPY VARCHAR2,
470 x_error_message_code OUT NOCOPY VARCHAR2
471 ) IS
472
473
474 l_created_by NUMBER;
475 l_last_updated_by NUMBER;
476 l_creation_date DATE;
477 l_last_update_date DATE;
478 l_last_update_login NUMBER;
479
480 BEGIN
481 -- need to check status, force user to use adjust if necessary
482
483 IF ( p_from_value < 0 OR p_from_value > 100 ) OR
484 ( p_to_value < 0 OR p_to_value > 100 ) THEN
485
486 x_return_status := 'E';
487 x_error_message_code := 'PJI_PIP_BUCKET_RANGE_INVAL' ;
488 RETURN ;
489
490 ELSIF p_from_value > p_to_value THEN
491
492 x_return_status := 'E';
493 x_error_message_code := 'PJI_PIP_START_END_RANGE_INVAL' ;
494 RETURN ;
495
496 ELSIF p_from_value IS NULL OR p_to_value IS NULL THEN
497
498 x_return_status := 'E';
499 x_error_message_code := 'PJI_PIP_BUCKET_RANGE_NULL' ;
500 RETURN ;
501
502
503 END IF;
504
505 l_created_by := Fnd_Profile.value('USER_ID');
506 l_last_updated_by := Fnd_Profile.value('USER_ID');
507 l_creation_date := SYSDATE;
508 l_last_update_date := SYSDATE;
509 l_last_update_login := Fnd_Profile.value('USER_ID');
510
511 UPDATE pji_mt_buckets
512 SET name = p_name,
513 last_update_date = l_last_update_date,
514 seq = p_seq,
515 bucket_set_code = p_bucket_set_code,
516 default_flag = p_default_flag,
517 from_value = p_from_value,
518 to_value = p_to_value
519 WHERE bucket_set_code = p_bucket_set_code
520 AND seq = p_seq;
521
522 x_return_status := 'S';
523
524 EXCEPTION
525 WHEN OTHERS THEN
526 x_return_status := 'U';
527 x_error_message_code := SQLERRM ;
528 RAISE ;
529
530 END pji_mt_pip_update_row;
531
532 ----------------------------------------------------------------------------------------------------------------
533 -- API : pji_mt_res_avl_dur_update_row
534 -- Description : This procedure validates and updates Resource Availability Buckets.
535 -- Parameters :
536 -- IN :p_name
537 -- p_seq
538 -- p_bucket_set_code
539 -- p_default_flag
540 -- p_from_value
541 -- p_to_value
542 -- OUT NOCOPY :x_return_status - Return status.
543 -- x_error_message_code - Return Error Code.
544 ----------------------------------------------------------------------------------------------------------------
545 PROCEDURE pji_mt_res_avl_dur_update_row (p_name IN VARCHAR2,
546 p_seq IN NUMBER,
547 p_bucket_set_code IN VARCHAR2,
548 p_default_flag IN VARCHAR2,
549 p_from_value IN NUMBER,
550 p_to_value IN NUMBER,
551 x_return_status OUT NOCOPY VARCHAR2,
552 x_error_message_code OUT NOCOPY VARCHAR2
553 ) IS
554
555
556 l_created_by NUMBER;
557 l_last_updated_by NUMBER;
558 l_creation_date DATE;
559 l_last_update_date DATE;
560 l_last_update_login NUMBER;
561
562 BEGIN
563 -- need to check status, force user to use adjust if necessary
564
565 IF ( p_from_value < 1 ) OR
566 ( NVL(p_to_value,1000) < 2 ) THEN
567
568 x_return_status := 'E';
569 x_error_message_code := 'PJI_AVL_RES_BUCKET_RANGE_INVAL' ;
570 RETURN ;
571
572 ELSIF p_from_value > p_to_value THEN
573
574 x_return_status := 'E';
575 x_error_message_code := 'PJI_AVL_RES_ST_END_RANGE_INVAL' ;
576 RETURN ;
577
578 ELSIF (p_from_value IS NULL OR p_from_value = '') THEN
579
580 x_return_status := 'E';
581 x_error_message_code := 'PJI_AVL_RES_BUCKET_RANGE_NULL' ;
582 RETURN ;
583
584 END IF;
585
586 l_created_by := Fnd_Profile.value('USER_ID');
587 l_last_updated_by := Fnd_Profile.value('USER_ID');
588 l_creation_date := SYSDATE;
589 l_last_update_date := SYSDATE;
590 l_last_update_login := Fnd_Profile.value('USER_ID');
591
592 UPDATE pji_mt_buckets
593 SET name = p_name,
594 last_update_date = l_last_update_date,
595 seq = p_seq,
596 bucket_set_code = p_bucket_set_code,
597 default_flag = p_default_flag,
598 from_value = p_from_value,
599 to_value = p_to_value
600 WHERE bucket_set_code = p_bucket_set_code
601 AND seq = p_seq;
602
603 x_return_status := 'S';
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 x_return_status := 'U';
608 x_error_message_code := SQLERRM ;
609 RAISE ;
610
611 END pji_mt_res_avl_dur_update_row;
612
613 ----------------------------------------------------------------------------------------------------------------
614 -- API : pji_mt_dls_update_row
615 -- Description : This procedure validates and updates the Deal Size Buckets.
616 -- Parameters :
617 -- IN :p_name
618 -- p_seq
619 -- p_bucket_set_code
620 -- p_default_flag
621 -- p_from_value
622 -- p_to_value
623 -- OUT NOCOPY :x_return_status - Return status.
624 -- x_error_message_code - Return Error Code.
625 ----------------------------------------------------------------------------------------------------------------
626 PROCEDURE pji_mt_dls_update_row (p_name IN VARCHAR2,
627 p_seq IN NUMBER,
628 p_bucket_set_code IN VARCHAR2,
629 p_default_flag IN VARCHAR2,
630 p_from_value IN NUMBER,
631 p_to_value IN NUMBER,
632 x_return_status OUT NOCOPY VARCHAR2,
633 x_error_message_code OUT NOCOPY VARCHAR2
634 ) IS
635
636
637 l_created_by NUMBER;
638 l_last_updated_by NUMBER;
639 l_creation_date DATE;
640 l_last_update_date DATE;
641 l_last_update_login NUMBER;
642
643 BEGIN
644 -- need to check status, force user to use adjust if necessary
645
646 IF ( p_from_value < 0 ) OR
647 ( p_to_value < 0 ) THEN
648
649 x_return_status := 'E';
650 x_error_message_code := 'PJI_DLS_RANGE_INVAL' ;
651 RETURN ;
652
653 ELSIF p_from_value > p_to_value THEN
654
655 x_return_status := 'E';
656 x_error_message_code := 'PJI_START_END_RANGE_INVAL' ;
657 RETURN ;
658
659 ELSIF p_from_value IS NULL THEN
660
661 x_return_status := 'E';
662 x_error_message_code := 'PJI_DLS_START_RANGE_NULL' ;
663 RETURN ;
664
665
666 END IF;
667
668 l_created_by := Fnd_Profile.value('USER_ID');
669 l_last_updated_by := Fnd_Profile.value('USER_ID');
670 l_creation_date := SYSDATE;
671 l_last_update_date := SYSDATE;
672 l_last_update_login := Fnd_Profile.value('USER_ID');
673
674 UPDATE pji_mt_buckets
675 SET name = p_name,
676 last_update_date = l_last_update_date,
677 seq = p_seq,
678 bucket_set_code = p_bucket_set_code,
679 default_flag = p_default_flag,
680 from_value = p_from_value,
681 to_value = p_to_value
682 WHERE bucket_set_code = p_bucket_set_code
683 AND seq = p_seq;
684
685 x_return_status := 'S';
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 x_return_status := 'U';
690 x_error_message_code := SQLERRM ;
691 RAISE ;
692
693 END pji_mt_dls_update_row;
694
695 ----------------------------------------------------------------------------------------------------------------
696 -- API : pji_mt_avl_update_row
697 -- Description : This procedure validates and updates the availability Thresholds.
698 -- Parameters :
699 -- IN :p_name
700 -- p_seq
701 -- p_bucket_set_code
702 -- p_default_flag
703 -- p_from_value
704 -- p_to_value
705 -- OUT NOCOPY :x_return_status - Return status.
706 -- x_error_message_code - Return Error Code.
707 ----------------------------------------------------------------------------------------------------------------
708 PROCEDURE pji_mt_avl_update_row (p_name IN VARCHAR2,
709 p_seq IN NUMBER,
710 p_bucket_set_code IN VARCHAR2,
711 p_default_flag IN VARCHAR2,
712 p_from_value IN NUMBER,
713 p_to_value IN NUMBER,
714 x_return_status OUT NOCOPY VARCHAR2,
715 x_error_message_code OUT NOCOPY VARCHAR2
716 ) IS
717
718
719 l_created_by NUMBER;
720 l_last_updated_by NUMBER;
721 l_creation_date DATE;
722 l_last_update_date DATE;
723 l_last_update_login NUMBER;
724
725 BEGIN
726 -- need to check status, force user to use adjust if necessary
727
728 IF ( p_to_value < 0 ) THEN
729
730 x_return_status := 'E';
731 x_error_message_code := 'PJI_AVL_RANGE_INVAL' ;
732 RETURN ;
733
734 ELSIF p_to_value IS NULL THEN
735
736 x_return_status := 'E';
737 x_error_message_code := 'PJI_AVL_START_RANGE_NULL' ;
738 RETURN ;
739
740
741 END IF;
742
743 l_created_by := Fnd_Profile.value('USER_ID');
744 l_last_updated_by := Fnd_Profile.value('USER_ID');
745 l_creation_date := SYSDATE;
746 l_last_update_date := SYSDATE;
747 l_last_update_login := Fnd_Profile.value('USER_ID');
748
749 UPDATE pji_mt_buckets
750 SET name = p_name,
751 last_update_date = l_last_update_date,
752 seq = p_seq,
753 bucket_set_code = p_bucket_set_code,
754 default_flag = p_default_flag,
755 from_value = p_from_value,
756 to_value = p_to_value
757 WHERE bucket_set_code = p_bucket_set_code
758 AND seq = p_seq;
759
760 x_return_status := 'S';
761
762 EXCEPTION
763 WHEN OTHERS THEN
764 x_return_status := 'U';
765 x_error_message_code := SQLERRM ;
766 RAISE ;
767
768 END pji_mt_avl_update_row;
769
770 ----------------------------------------------------------------------------------------------------------------
771 -- API : pji_validate_bucket_ranges
772 -- Description : This procedure validates the updated bucket range.
773 -- Parameters :
774 -- OUT NOCOPY : x_return_status - Return status.
775 -- x_error_message_code - Return Error Code.
776 ----------------------------------------------------------------------------------------------------------------
777 PROCEDURE pji_validate_bucket_ranges (x_return_status OUT NOCOPY VARCHAR2,
778 x_error_message_code OUT NOCOPY VARCHAR2
779 ) IS
780
781
782
783 CURSOR C1 (c_bucket_set_code IN VARCHAR2) IS
784 SELECT 1 FROM DUAL
785 WHERE EXISTS (
786 SELECT 'X'
787 FROM pji_mt_buckets a,
788 pji_mt_buckets b
789 WHERE a.bucket_set_code = b.bucket_set_code
790 AND a.bucket_set_code = c_bucket_set_code
791 AND a.from_value <= b.to_value
792 AND b.from_value < b.to_value
793 AND NVL(a.to_value, 99999999999999999999999) > b.to_value );
794
795 CURSOR C2 (c_bucket_set_code IN VARCHAR2) IS
796 SELECT 2 FROM DUAL
797 WHERE EXISTS (
798 SELECT 'X'
799 FROM pji_mt_buckets a,
800 pji_mt_buckets b
801 WHERE a.bucket_set_code = b.bucket_set_code
802 AND a.bucket_set_code = c_bucket_set_code
803 AND (b.from_value - a.to_value) > 1
804 AND NVL(b.to_value, 99999999999999999999999) > b.from_value
805 AND a.to_value > a.from_value
806 AND NOT EXISTS ( SELECT 'X'
807 FROM pji_mt_buckets c
808 WHERE c.from_value > a.to_value
809 AND c.to_value < b.from_value ));
810
811 CURSOR C3 IS
812 SELECT 3 FROM DUAL
813 WHERE EXISTS (SELECT 'X'
814 FROM pji_mt_buckets a,
815 pji_mt_buckets b
816 WHERE a.bucket_set_code = b.bucket_set_code
817 AND a.bucket_set_code = 'PJI_RESOURCE_AVAILABILITY'
818 AND a.seq > b.seq
819 AND b.to_value > a.to_value) ;
820
821
822
823 l_check_overlap NUMBER ;
824 l_check_gaps NUMBER ;
825 l_check_avl_overlap NUMBER ;
826 l_check_min_probability NUMBER ;
827 l_check_max_probability NUMBER ;
828
829 BEGIN
830
831 --
832 -- Validation for Probability buckets
833 --
834
835 OPEN C1 ('PJI_PIPELINE_PROBABILITY') ;
836 FETCH C1 INTO l_check_overlap ;
837 CLOSE C1 ;
838
839 IF l_check_overlap = 1 THEN
840
841 x_return_status := 'E' ;
842 x_error_message_code := 'PJI_PIP_BUCKETS_OVERLAP' ;
843 RETURN ;
844
845 ELSE
846
847 OPEN C2 ('PJI_PIPELINE_PROBABILITY') ;
848 FETCH C2 INTO l_check_gaps ;
849 CLOSE C2 ;
850
851 IF l_check_gaps = 2 THEN
852
853 x_return_status := 'E' ;
854 x_error_message_code := 'PJI_PIP_BUCKETS_GAPS' ;
855 RETURN ;
856 END IF;
857 END IF ;
858 x_return_status := 'S' ;
859
860 SELECT MIN(a.from_value), MAX(a.to_value)
861 INTO l_check_min_probability, l_check_max_probability
862 FROM pji_mt_buckets a
863 WHERE a.bucket_set_code = 'PJI_PIPELINE_PROBABILITY' ;
864
865 IF l_check_min_probability > 0 OR l_check_max_probability < 100 THEN
866
867 x_return_status := 'E' ;
868 x_error_message_code := 'PJI_PROBABILITY_GAPS' ;
869 RETURN ;
870 END IF;
871
872 --
873 -- Validation for Deal Size buckets
874 --
875
876 OPEN C1 ('PJI_PIPELINE_DEAL_SIZE') ;
877 FETCH C1 INTO l_check_overlap ;
878 CLOSE C1 ;
879
880 IF l_check_overlap = 1 THEN
881
882 x_return_status := 'E' ;
883 x_error_message_code := 'PJI_DLS_BUCKETS_OVERLAP' ;
884 RETURN ;
885
886 ELSE
887
888 OPEN C2 ('PJI_PIPELINE_DEAL_SIZE') ;
889 FETCH C2 INTO l_check_gaps ;
890 CLOSE C2 ;
891
892 IF l_check_gaps = 2 THEN
893
894 x_return_status := 'E' ;
895 x_error_message_code := 'PJI_DLS_BUCKETS_GAPS' ;
896 RETURN ;
897 END IF;
898 END IF ;
899 x_return_status := 'S' ;
900
901 OPEN C3 ;
902 FETCH C3 INTO l_check_avl_overlap ;
903 CLOSE C3 ;
904
905 IF l_check_avl_overlap = 3 THEN
906
907 x_return_status := 'E' ;
908 x_error_message_code := 'PJI_AVL_BUCKETS_OVERLAP' ;
909 RETURN ;
910
911 END IF;
912
913 --
914 -- Validation for Available Resource Duration buckets
915 --
916 OPEN C1 ('PJI_RES_AVL_DAYS') ;
917 FETCH C1 INTO l_check_overlap ;
918 CLOSE C1 ;
919
920 IF l_check_overlap = 1 THEN
921
922 x_return_status := 'E' ;
923 x_error_message_code := 'PJI_AVL_RES_BUCKET_OVERLAP' ;
924 RETURN ;
925
926 ELSE
927
928 OPEN C2 ('PJI_RES_AVL_DAYS') ;
929 FETCH C2 INTO l_check_gaps ;
930 CLOSE C2 ;
931
932 IF l_check_gaps = 2 THEN
933
934 x_return_status := 'E' ;
935 x_error_message_code := 'PJI_AVL_RES_GAP' ;
936 RETURN ;
937 END IF;
938 END IF ;
939 x_return_status := 'S' ;
940
941 SELECT MIN(a.from_value), MIN(NVL(a.to_value,1000))
942 INTO l_check_min_probability, l_check_max_probability
943 FROM pji_mt_buckets a
944 WHERE a.bucket_set_code = 'PJI_RES_AVL_DAYS' ;
945
946 IF l_check_min_probability <> 1 OR l_check_max_probability < 2 THEN
947
948 x_return_status := 'E' ;
949 x_error_message_code := 'PJI_AVL_RES_INVAL' ;
950 RETURN ;
951 END IF;
952
953 EXCEPTION
954 WHEN OTHERS THEN
955 x_return_status := 'U';
956 RAISE ;
957
958 END pji_validate_bucket_ranges;
959
960
961 ----------------------------------------------------------------------------------------------------------------
962 -- API : pji_insert_events_log
963 -- Description : This procedure creates the audit record.
964 ----------------------------------------------------------------------------------------------------------------
965 PROCEDURE pji_insert_events_log (
966 p_organization_structure_id IN NUMBER,
967 p_org_structure_version_id IN NUMBER,
968 p_dflt_prjpip_period_type IN VARCHAR2,
969 p_dflt_prjpip_as_of_date IN VARCHAR2,
970 p_dflt_prjbab_period_type IN VARCHAR2,
971 p_dflt_prjbab_as_of_date IN VARCHAR2,
972 p_dflt_resutl_period_type IN VARCHAR2,
973 p_dflt_resutl_as_of_date IN VARCHAR2,
974 p_dflt_resavl_period_type IN VARCHAR2,
975 p_dflt_resavl_as_of_date IN VARCHAR2,
976 p_dflt_respln_period_type IN VARCHAR2,
977 p_dflt_respln_as_of_date IN VARCHAR2,
978 p_dflt_prjhlt_period_type IN VARCHAR2,
979 p_dflt_prjhlt_as_of_date IN VARCHAR2,
980 p_dflt_prjact_period_type IN VARCHAR2,
981 p_dflt_prjact_as_of_date IN VARCHAR2,
982 p_dflt_prjprf_period_type IN VARCHAR2,
983 p_dflt_prjprf_as_of_date IN VARCHAR2,
984 p_dflt_prjcst_period_type IN VARCHAR2,
985 p_dflt_prjcst_as_of_date IN VARCHAR2,
986 p_pa_period_flag IN VARCHAR2,
987 p_gl_period_flag IN VARCHAR2,
988 p_conversion_ratio_days IN VARCHAR2,
989 p_book_to_bill_days IN NUMBER,
990 p_dso_days IN NUMBER,
991 p_dormant_backlog_days IN NUMBER,
992 p_cost_budget_type_code IN VARCHAR2,
993 p_cost_budget_conv_rule IN VARCHAR2,
994 p_revenue_budget_type_code IN VARCHAR2,
995 p_revenue_budget_conv_rule IN VARCHAR2,
996 p_cost_forecast_type_code IN VARCHAR2,
997 p_cost_forecast_conv_rule IN VARCHAR2,
998 p_revenue_forecast_type_code IN VARCHAR2,
999 p_revenue_forecast_conv_rule IN VARCHAR2,
1000 p_report_cost_type IN VARCHAR2,
1001 p_report_labor_units IN VARCHAR2,
1002 p_rolling_weeks IN NUMBER,
1003 p_config_proj_perf_flag IN VARCHAR2,
1004 p_config_cost_flag IN VARCHAR2,
1005 p_config_profit_flag IN VARCHAR2,
1006 p_config_util_flag IN VARCHAR2,
1007 p_cost_fp_type_id IN NUMBER,
1008 p_revenue_fp_type_id IN NUMBER,
1009 p_cost_forecast_fp_type_id IN NUMBER,
1010 p_revenue_forecast_fp_type_id IN NUMBER,
1011 p_global_curr2_flag IN VARCHAR2,
1012 x_return_status OUT NOCOPY VARCHAR2,
1013 x_error_message_code OUT NOCOPY VARCHAR2) IS
1014
1015 CURSOR Cur_PjiSysSettings IS
1016 SELECT *
1017 FROM pji_system_settings ;
1018
1019 Cur_PjiRowtype Cur_PjiSysSettings%ROWTYPE ;
1020
1021 TYPE EventAttribute IS RECORD (
1022 attribute_name VARCHAR2(30),
1023 attribute_old_value VARCHAR2(30),
1024 attribute_new_value VARCHAR2(30));
1025
1026 TYPE EventAttribTabTyp_Rec IS TABLE OF EventAttribute INDEX BY BINARY_INTEGER;
1027
1028 EventAttribTabTyp EventAttribTabTyp_Rec;
1029
1030
1031 l_pji_rowid VARCHAR2(1000) := NULL ;
1032 l_pji_event_id NUMBER := NULL ;
1033 i NUMBER := 1;
1034
1035 l_pji_sys_char_rec Pa_Plsql_Datatypes.IdTabTyp;
1036 l_pji_sys_num_rec Pa_Plsql_Datatypes.Char30TabTyp;
1037
1038 BEGIN
1039
1040 OPEN Cur_PjiSysSettings ;
1041 FETCH Cur_PjiSysSettings INTO Cur_PjiRowtype ;
1042 CLOSE Cur_PjiSysSettings ;
1043
1044
1045
1046 IF Cur_PjiRowtype.organization_structure_id <> p_organization_structure_id THEN
1047 -- Assign the attributes
1048 EventAttribTabTyp(i).attribute_name := 'ORGANIZATION_STRUCTURE_ID';
1049 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.organization_structure_id;
1050 EventAttribTabTyp(i).attribute_new_value := p_organization_structure_id;
1051 --Increment the index
1052 i := i + 1;
1053 END IF;
1054
1055 IF Cur_PjiRowtype.org_structure_version_id <> p_org_structure_version_id THEN
1056 -- Assign the attributes
1057 EventAttribTabTyp(i).attribute_name := 'ORG_STRUCTURE_VERSION_ID';
1058 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.org_structure_version_id;
1059 EventAttribTabTyp(i).attribute_new_value := p_org_structure_version_id;
1060 --Increment the index
1061 i := i + 1;
1062 END IF;
1063
1064 IF Cur_PjiRowtype.dflt_prjpip_period_type <> p_dflt_prjpip_period_type THEN
1065 -- Assign the attributes
1066 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJPIP_PERIOD_TYPE';
1067 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjpip_period_type;
1068 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjpip_period_type;
1069 --Increment the index
1070 i := i + 1;
1071 END IF;
1072
1073 IF Cur_PjiRowtype.dflt_prjpip_as_of_date <> p_dflt_prjpip_as_of_date THEN
1074 -- Assign the attributes
1075 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJPIP_AS_OF_DATE';
1076 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjpip_as_of_date;
1077 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjpip_as_of_date;
1078 --Increment the index
1079 i := i + 1;
1080 END IF;
1081
1082 IF Cur_PjiRowtype.dflt_prjbab_period_type <> p_dflt_prjbab_period_type THEN
1083 -- Assign the attributes
1084 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJBAB_PERIOD_TYPE';
1085 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjbab_period_type;
1086 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjbab_period_type;
1087 --Increment the index
1088 i := i + 1;
1089 END IF;
1090
1091 IF Cur_PjiRowtype.dflt_prjbab_as_of_date <> p_dflt_prjbab_as_of_date THEN
1092 -- Assign the attributes
1093 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJBAB_AS_OF_DATE';
1094 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjbab_as_of_date;
1095 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjbab_as_of_date;
1096 --Increment the index
1097 i := i + 1;
1098 END IF;
1099
1100
1101 IF Cur_PjiRowtype.dflt_resutl_period_type <> p_dflt_resutl_period_type THEN
1102 -- Assign the attributes
1103 EventAttribTabTyp(i).attribute_name := 'DFLT_RESUTL_PERIOD_TYPE';
1104 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_resutl_period_type;
1105 EventAttribTabTyp(i).attribute_new_value := p_dflt_resutl_period_type;
1106 --Increment the index
1107 i := i + 1;
1108 END IF;
1109
1110
1111 IF Cur_PjiRowtype.dflt_resutl_as_of_date <> p_dflt_resutl_as_of_date THEN
1112 -- Assign the attributes
1113 EventAttribTabTyp(i).attribute_name := 'DFLT_RESUTL_AS_OF_DATE';
1114 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_resutl_as_of_date;
1115 EventAttribTabTyp(i).attribute_new_value := p_dflt_resutl_as_of_date;
1116 --Increment the index
1117 i := i + 1;
1118 END IF;
1119
1120
1121 IF Cur_PjiRowtype.dflt_resavl_period_type <> p_dflt_resavl_period_type THEN
1122 -- Assign the attributes
1123 EventAttribTabTyp(i).attribute_name := 'DFLT_RESAVL_PERIOD_TYPE';
1124 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_resavl_period_type;
1125 EventAttribTabTyp(i).attribute_new_value := p_dflt_resavl_period_type;
1126 --Increment the index
1127 i := i + 1;
1128 END IF;
1129
1130 IF Cur_PjiRowtype.dflt_resavl_as_of_date <> p_dflt_resavl_as_of_date THEN
1131 -- Assign the attributes
1132 EventAttribTabTyp(i).attribute_name := 'DFLT_RESAVL_AS_OF_DATE';
1133 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_resavl_as_of_date;
1134 EventAttribTabTyp(i).attribute_new_value := p_dflt_resavl_as_of_date;
1135 --Increment the index
1136 i := i + 1;
1137 END IF;
1138
1139
1140 IF Cur_PjiRowtype.dflt_respln_period_type <> p_dflt_respln_period_type THEN
1141 -- Assign the attributes
1142 EventAttribTabTyp(i).attribute_name := 'DFLT_RESPLN_PERIOD_TYPE';
1143 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_respln_period_type;
1144 EventAttribTabTyp(i).attribute_new_value := p_dflt_respln_period_type;
1145 --Increment the index
1146 i := i + 1;
1147 END IF;
1148
1149
1150 IF Cur_PjiRowtype.dflt_respln_as_of_date <> p_dflt_respln_as_of_date THEN
1151 -- Assign the attributes
1152 EventAttribTabTyp(i).attribute_name := 'DFLT_RESPLN_AS_OF_DATE';
1153 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_respln_as_of_date;
1154 EventAttribTabTyp(i).attribute_new_value := p_dflt_respln_as_of_date;
1155 --Increment the index
1156 i := i + 1;
1157 END IF;
1158
1159 IF Cur_PjiRowtype.dflt_prjhlt_period_type <> p_dflt_prjhlt_period_type THEN
1160 -- Assign the attributes
1161 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJHLT_PERIOD_TYPE';
1162 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjhlt_period_type;
1163 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjhlt_period_type;
1164 --Increment the index
1165 i := i + 1;
1166 END IF;
1167
1168 IF Cur_PjiRowtype.dflt_prjhlt_as_of_date <> p_dflt_prjhlt_as_of_date THEN
1169 -- Assign the attributes
1170 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJHLT_AS_OF_DATE';
1171 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjhlt_as_of_date;
1172 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjhlt_as_of_date;
1173 --Increment the index
1174 i := i + 1;
1175 END IF;
1176
1177
1178 IF Cur_PjiRowtype.dflt_prjact_period_type <> p_dflt_prjact_period_type THEN
1179 -- Assign the attributes
1180 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJACT_PERIOD_TYPE';
1181 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjact_period_type;
1182 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjact_period_type;
1183 --Increment the index
1184 i := i + 1;
1185 END IF;
1186
1187 IF Cur_PjiRowtype.dflt_prjact_as_of_date <> p_dflt_prjact_as_of_date THEN
1188 -- Assign the attributes
1189 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJACT_AS_OF_DATE';
1190 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjact_as_of_date;
1191 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjact_as_of_date;
1192 --Increment the index
1193 i := i + 1;
1194 END IF;
1195
1196
1197
1198 IF Cur_PjiRowtype.dflt_prjprf_period_type <> p_dflt_prjprf_period_type THEN
1199 -- Assign the attributes
1200 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJPRF_PERIOD_TYPE';
1201 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjprf_period_type;
1202 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjprf_period_type ;
1203 --Increment the index
1204 i := i + 1;
1205 END IF;
1206
1207 IF Cur_PjiRowtype.dflt_prjprf_as_of_date <> p_dflt_prjprf_as_of_date THEN
1208 -- Assign the attributes
1209 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJPRF_AS_OF_DATE';
1210 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjprf_as_of_date;
1211 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjprf_as_of_date;
1212 --Increment the index
1213 i := i + 1;
1214 END IF;
1215
1216
1217 IF Cur_PjiRowtype.dflt_prjcst_period_type <> p_dflt_prjcst_period_type THEN
1218 -- Assign the attributes
1219 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJCST_PERIOD_TYPE';
1220 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjcst_period_type;
1221 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjcst_period_type;
1222 --Increment the index
1223 i := i + 1;
1224 END IF;
1225
1226 IF Cur_PjiRowtype.dflt_prjcst_as_of_date <> p_dflt_prjcst_as_of_date THEN
1227 -- Assign the attributes
1228 EventAttribTabTyp(i).attribute_name := 'DFLT_PRJCST_AS_OF_DATE';
1229 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dflt_prjcst_as_of_date;
1230 EventAttribTabTyp(i).attribute_new_value := p_dflt_prjcst_as_of_date;
1231 --Increment the index
1232 i := i + 1;
1233 END IF;
1234
1235
1236 IF Cur_PjiRowtype.pa_period_flag <> p_pa_period_flag THEN
1237 -- Assign the attributes
1238 EventAttribTabTyp(i).attribute_name := 'PA_PERIOD_FLAG';
1239 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.pa_period_flag;
1240 EventAttribTabTyp(i).attribute_new_value := p_pa_period_flag;
1241 --Increment the index
1242 i := i + 1;
1243 END IF;
1244
1245 IF Cur_PjiRowtype.gl_period_flag <> p_gl_period_flag THEN
1246 -- Assign the attributes
1247 EventAttribTabTyp(i).attribute_name := 'GL_PERIOD_FLAG';
1248 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.gl_period_flag;
1249 EventAttribTabTyp(i).attribute_new_value := p_gl_period_flag;
1250 --Increment the index
1251 i := i + 1;
1252 END IF;
1253
1254 IF Cur_PjiRowtype.rolling_weeks <> p_rolling_weeks THEN
1255 -- Assign the attributes
1256 EventAttribTabTyp(i).attribute_name := 'ROLLING_WEEKS';
1257 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.rolling_weeks;
1258 EventAttribTabTyp(i).attribute_new_value := p_rolling_weeks;
1259 --Increment the index
1260 i := i + 1;
1261 END IF;
1262
1263 IF Cur_PjiRowtype.conversion_ratio_days <> p_conversion_ratio_days THEN
1264 -- Assign the attributes
1265 EventAttribTabTyp(i).attribute_name := 'CONVERSION_RATIO_DAYS';
1266 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.conversion_ratio_days;
1267 EventAttribTabTyp(i).attribute_new_value := p_conversion_ratio_days;
1268 --Increment the index
1269 i := i + 1;
1270 END IF;
1271
1272 IF Cur_PjiRowtype.book_to_bill_days <> p_book_to_bill_days THEN
1273 -- Assign the attributes
1274 EventAttribTabTyp(i).attribute_name := 'BOOK_TO_BILL_DAYS';
1275 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.book_to_bill_days;
1276 EventAttribTabTyp(i).attribute_new_value := p_book_to_bill_days;
1277 --Increment the index
1278 i := i + 1;
1279 END IF;
1280
1281 IF Cur_PjiRowtype.dso_days <> p_dso_days THEN
1282 -- Assign the attributes
1283 EventAttribTabTyp(i).attribute_name := 'DSO_DAYS';
1284 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dso_days;
1285 EventAttribTabTyp(i).attribute_new_value := p_dso_days;
1286 --Increment the index
1287 i := i + 1;
1288 END IF;
1289
1290 IF Cur_PjiRowtype.dormant_backlog_days <> p_dormant_backlog_days THEN
1291 -- Assign the attributes
1292 EventAttribTabTyp(i).attribute_name := 'DORMANT_BACKLOG_DAYS';
1293 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.dormant_backlog_days;
1294 EventAttribTabTyp(i).attribute_new_value := p_dormant_backlog_days;
1295 --Increment the index
1296 i := i + 1;
1297 END IF;
1298
1299 IF Cur_PjiRowtype.cost_budget_type_code <> p_cost_budget_type_code THEN
1300 -- Assign the attributes
1301 EventAttribTabTyp(i).attribute_name := 'COST_BUDGET_TYPE_CODE';
1302 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.cost_budget_type_code;
1303 EventAttribTabTyp(i).attribute_new_value := p_cost_budget_type_code;
1304 --Increment the index
1305 i := i + 1;
1306 END IF;
1307
1308
1309 IF Cur_PjiRowtype.cost_budget_conv_rule <> p_cost_budget_conv_rule THEN
1310 -- Assign the attributes
1311 EventAttribTabTyp(i).attribute_name := 'COST_BUDGET_CONV_RULE';
1312 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.cost_budget_conv_rule;
1313 EventAttribTabTyp(i).attribute_new_value := p_cost_budget_conv_rule;
1314 --Increment the index
1315 i := i + 1;
1316 END IF;
1317
1318
1319 IF Cur_PjiRowtype.revenue_budget_type_code <> p_revenue_budget_type_code THEN
1320 -- Assign the attributes
1321 EventAttribTabTyp(i).attribute_name := 'REVENUE_BUDGET_TYPE_CODE';
1322 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.revenue_budget_type_code;
1323 EventAttribTabTyp(i).attribute_new_value := p_revenue_budget_type_code;
1324 --Increment the index
1325 i := i + 1;
1326 END IF;
1327
1328 IF Cur_PjiRowtype.revenue_budget_conv_rule <> p_revenue_budget_conv_rule THEN
1329 -- Assign the attributes
1330 EventAttribTabTyp(i).attribute_name := 'REVENUE_BUDGET_CONV_RULE';
1331 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.revenue_budget_conv_rule;
1332 EventAttribTabTyp(i).attribute_new_value := p_revenue_budget_conv_rule;
1333 --Increment the index
1334 i := i + 1;
1335 END IF;
1336
1337 IF Cur_PjiRowtype.cost_forecast_type_code <> p_cost_forecast_type_code THEN
1338 -- Assign the attributes
1339 EventAttribTabTyp(i).attribute_name := 'COST_FORECAST_TYPE_CODE';
1340 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.cost_forecast_type_code;
1341 EventAttribTabTyp(i).attribute_new_value := p_cost_forecast_type_code;
1342 --Increment the index
1343 i := i + 1;
1344 END IF;
1345
1346 IF Cur_PjiRowtype.cost_forecast_conv_rule <> p_cost_forecast_conv_rule THEN
1347 -- Assign the attributes
1348 EventAttribTabTyp(i).attribute_name := 'COST_FORECAST_CONV_RULE';
1349 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.cost_forecast_conv_rule;
1350 EventAttribTabTyp(i).attribute_new_value := p_cost_forecast_conv_rule;
1351 --Increment the index
1352 i := i + 1;
1353 END IF;
1354
1355 IF Cur_PjiRowtype.revenue_forecast_type_code <> p_revenue_forecast_type_code THEN
1356 -- Assign the attributes
1357 EventAttribTabTyp(i).attribute_name := 'REVENUE_FORECAST_TYPE_CODE';
1358 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.revenue_forecast_type_code;
1359 EventAttribTabTyp(i).attribute_new_value := p_revenue_forecast_type_code;
1360 --Increment the index
1361 i := i + 1;
1362 END IF;
1363
1364 IF Cur_PjiRowtype.revenue_forecast_conv_rule <> p_revenue_forecast_conv_rule THEN
1365 -- Assign the attributes
1366 EventAttribTabTyp(i).attribute_name := 'REVENUE_FORECAST_CONV_RULE';
1367 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.revenue_forecast_conv_rule;
1368 EventAttribTabTyp(i).attribute_new_value := p_revenue_forecast_conv_rule;
1369 --Increment the index
1370 i := i + 1;
1371 END IF;
1372
1373 IF Cur_PjiRowtype.report_cost_type <> p_report_cost_type THEN
1374 -- Assign the attributes
1375 EventAttribTabTyp(i).attribute_name := 'REPORT_COST_TYPE';
1376 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.report_cost_type;
1377 EventAttribTabTyp(i).attribute_new_value := p_report_cost_type;
1378 --Increment the index
1379 i := i + 1;
1380 END IF;
1381
1382 IF Cur_PjiRowtype.report_labor_units <> p_report_labor_units THEN
1383 -- Assign the attributes
1384 EventAttribTabTyp(i).attribute_name := 'REPORT_LABOR_UNITS';
1385 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.report_labor_units;
1386 EventAttribTabTyp(i).attribute_new_value := p_report_labor_units;
1387 --Increment the index
1388 i := i + 1;
1389 END IF;
1390
1391 IF Cur_PjiRowtype.config_proj_perf_flag <> p_config_proj_perf_flag THEN
1392 -- Assign the attributes
1393 EventAttribTabTyp(i).attribute_name := 'CONFIG_PROJ_PERF_FLAG';
1394 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.config_proj_perf_flag;
1395 EventAttribTabTyp(i).attribute_new_value := p_config_proj_perf_flag;
1396 --Increment the index
1397 i := i + 1;
1398 END IF;
1399
1400 IF Cur_PjiRowtype.config_cost_flag <> p_config_cost_flag THEN
1401 -- Assign the attributes
1402 EventAttribTabTyp(i).attribute_name := 'CONFIG_COST_FLAG';
1403 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.config_cost_flag;
1404 EventAttribTabTyp(i).attribute_new_value := p_config_cost_flag;
1405 --Increment the index
1406 i := i + 1;
1407 END IF;
1408
1409 IF Cur_PjiRowtype.config_profit_flag <> p_config_profit_flag THEN
1410 -- Assign the attributes
1411 EventAttribTabTyp(i).attribute_name := 'CONFIG_PROFIT_FLAG';
1412 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.config_profit_flag;
1413 EventAttribTabTyp(i).attribute_new_value := p_config_profit_flag;
1414 --Increment the index
1415 i := i + 1;
1416 END IF;
1417
1418 IF Cur_PjiRowtype.config_util_flag <> p_config_util_flag THEN
1419 -- Assign the attributes
1420 EventAttribTabTyp(i).attribute_name := 'CONFIG_UTIL_FLAG';
1421 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.config_util_flag;
1422 EventAttribTabTyp(i).attribute_new_value := p_config_util_flag;
1423 --Increment the index
1424 i := i + 1;
1425 END IF;
1426
1427 IF Cur_PjiRowtype.cost_fp_type_id <> p_cost_fp_type_id THEN
1428 -- Assign the attributes
1429 EventAttribTabTyp(i).attribute_name := 'COST_FP_TYPE_ID';
1430 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.cost_fp_type_id;
1431 EventAttribTabTyp(i).attribute_new_value := p_cost_fp_type_id;
1432 --Increment the index
1433 i := i + 1;
1434 END IF;
1435
1436 IF Cur_PjiRowtype.revenue_fp_type_id <> p_revenue_fp_type_id THEN
1437 -- Assign the attributes
1438 EventAttribTabTyp(i).attribute_name := 'REVENUE_FP_TYPE_ID';
1439 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.revenue_fp_type_id;
1440 EventAttribTabTyp(i).attribute_new_value := p_revenue_fp_type_id;
1441 --Increment the index
1442 i := i + 1;
1443 END IF;
1444
1445 IF Cur_PjiRowtype.cost_forecast_fp_type_id <> p_cost_forecast_fp_type_id THEN
1446 -- Assign the attributes
1447 EventAttribTabTyp(i).attribute_name := 'COST_FORECAST_FP_TYPE_ID';
1448 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.cost_forecast_fp_type_id;
1449 EventAttribTabTyp(i).attribute_new_value := p_cost_forecast_fp_type_id;
1450 --Increment the index
1451 i := i + 1;
1452 END IF;
1453
1454 IF Cur_PjiRowtype.revenue_forecast_fp_type_id <> p_revenue_forecast_fp_type_id THEN
1455 -- Assign the attributes
1456 EventAttribTabTyp(i).attribute_name := 'REVENUE_FORECAST_FP_TYPE_ID';
1457 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.revenue_forecast_fp_type_id;
1458 EventAttribTabTyp(i).attribute_new_value := p_revenue_forecast_fp_type_id;
1459 --Increment the index
1460 i := i + 1;
1461 END IF;
1462
1463 IF Cur_PjiRowtype.global_curr2_flag <> p_global_curr2_flag THEN
1464 -- Assign the attributes
1465 EventAttribTabTyp(i).attribute_name := 'GLOBAL_CURR2_FLAG';
1466 EventAttribTabTyp(i).attribute_old_value := Cur_PjiRowtype.global_curr2_flag;
1467 EventAttribTabTyp(i).attribute_new_value := p_global_curr2_flag;
1468 --Increment the index
1469 i := i + 1;
1470 END IF;
1471
1472
1473
1474 FOR ROWS IN 1..i-1 LOOP
1475
1476 l_pji_rowid := NULL;
1477 l_pji_event_id := NULL;
1478
1479 --Should the call be in I/U mode ?
1480 Pa_Pji_Proj_Events_Log_Pkg.Insert_Row(
1481 X_ROW_ID => l_pji_rowid
1482 ,X_EVENT_ID => l_pji_event_id
1483 ,X_EVENT_TYPE => 'PJI_SETUP_CHANGE'
1484 ,X_EVENT_OBJECT => EventAttribTabTyp(ROWS).attribute_name
1485 ,X_OPERATION_TYPE => 'U' -- update mode
1486 ,X_STATUS => 'X' --NULL
1487 ,X_ATTRIBUTE_CATEGORY => NULL
1488 ,X_ATTRIBUTE1 => EventAttribTabTyp(ROWS).attribute_new_value
1489 ,X_ATTRIBUTE2 => EventAttribTabTyp(ROWS).attribute_old_value
1490 ,X_ATTRIBUTE3 => NULL
1491 ,X_ATTRIBUTE4 => NULL
1492 ,X_ATTRIBUTE5 => NULL
1493 ,X_ATTRIBUTE6 => NULL
1494 ,X_ATTRIBUTE7 => NULL
1495 ,X_ATTRIBUTE8 => NULL
1496 ,X_ATTRIBUTE9 => NULL
1497 ,X_ATTRIBUTE10 => NULL
1498 ,X_ATTRIBUTE11 => NULL
1499 ,X_ATTRIBUTE12 => NULL
1500 ,X_ATTRIBUTE13 => NULL
1501 ,X_ATTRIBUTE14 => NULL
1502 ,X_ATTRIBUTE15 => NULL
1503 ,X_ATTRIBUTE16 => NULL
1504 ,X_ATTRIBUTE17 => NULL
1505 ,X_ATTRIBUTE18 => NULL
1506 ,X_ATTRIBUTE19 => NULL
1507 ,X_ATTRIBUTE20 => NULL
1508 );
1509
1510 END LOOP;
1511
1512 END pji_insert_events_log ;
1513
1514
1515 ----------------------------------------------------------------------------------------------------------------
1516 -- API : Check_Org_structure
1517 -- Description : This procedure validates and returns a valid organization structure id.
1518 -- Parameters :
1519 -- IN :p_Org_structure - Organization Structure Name.
1520 -- OUT NOCOPY :x_Org_structure_id - Organization Structure Id.
1521 -- x_return_status - Return status.
1522 -- x_error_message_code - Return Error Code.
1523 ----------------------------------------------------------------------------------------------------------------
1524 PROCEDURE Check_Org_structure
1525 ( p_Org_structure IN VARCHAR2
1526 ,x_Org_structure_id OUT NOCOPY VARCHAR2
1527 ,x_return_status OUT NOCOPY VARCHAR2
1528 ,x_error_message_code OUT NOCOPY VARCHAR2) IS
1529
1530
1531 l_current_id NUMBER ;
1532 l_num_ids NUMBER := 0;
1533 CURSOR c_ids IS
1534 SELECT s.organization_structure_id
1535 FROM per_organization_structures s
1536 WHERE s.name = p_Org_structure ;
1537
1538
1539
1540 BEGIN
1541 IF (p_Org_structure IS NULL) THEN
1542 -- Return a null ID since the name is null.
1543 x_Org_structure_id := NULL;
1544
1545 ELSE
1546 -- Find the ID which matches the Name passed
1547 OPEN c_ids;
1548 LOOP
1549 FETCH c_ids INTO l_current_id;
1550 EXIT WHEN c_ids%NOTFOUND;
1551 END LOOP;
1552 l_num_ids := c_ids%ROWCOUNT;
1553 CLOSE c_ids;
1554
1555 IF (l_num_ids = 0) THEN
1556 -- No IDs for name
1557 RAISE NO_DATA_FOUND;
1558 ELSIF (l_num_ids = 1) THEN
1559 -- Since there is only one ID for the name use it.
1560 x_Org_structure_id := l_current_id;
1561 ELSIF (l_num_ids > 1 ) THEN
1562 -- More than one ID for the name and none of the IDs matched
1563 -- the ID passed in.
1564 RAISE TOO_MANY_ROWS;
1565 END IF;
1566 END IF; -- end if for p_Org_structure IS NULL
1567 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1568
1569 EXCEPTION
1570 WHEN NO_DATA_FOUND THEN
1571 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1572 x_error_message_code := 'PJI_ORG_STRUCTURE_INVALID';
1573 x_Org_structure_id := NULL;
1574 WHEN TOO_MANY_ROWS THEN
1575 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1576 x_error_message_code := 'PJI_ORG_STRUCTURE_AMBIGUOUS';
1577 x_Org_structure_id := NULL;
1578 WHEN OTHERS THEN
1579 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1580 x_Org_structure_id := NULL;
1581 RAISE ;
1582
1583 END Check_Org_structure;
1584
1585 ----------------------------------------------------------------------------------------------------------------
1586 -- API : Check_Org_structure_Version
1587 -- Description : This procedure validates and returns a valid organization structure version id.
1588 -- Parameters :
1589 -- IN :p_Org_structure_version - Organization Structure Version Name.
1590 -- p_Org_structure_id - Organization Structure Id.
1591 -- OUT NOCOPY :x_Org_structure_version_id - Organization Structure Version Id.
1592 -- x_return_status - Return status.
1593 -- x_error_message_code - Return Error Code.
1594 ----------------------------------------------------------------------------------------------------------------
1595 PROCEDURE Check_Org_structure_Version
1596 ( p_Org_structure_version IN VARCHAR2
1597 ,p_Org_structure_id IN NUMBER
1598 ,x_Org_structure_version_id OUT NOCOPY VARCHAR2
1599 ,x_return_status OUT NOCOPY VARCHAR2
1600 ,x_error_message_code OUT NOCOPY VARCHAR2) IS
1601
1602
1603 l_current_id NUMBER ;
1604 l_num_ids NUMBER := 0;
1605 CURSOR c_ids IS
1606 SELECT v.org_structure_version_id
1607 FROM per_org_structure_versions v
1608 WHERE v.organization_structure_id = p_Org_structure_id
1609 AND v.version_number = p_Org_structure_version ;
1610
1611
1612
1613 BEGIN
1614 IF (p_Org_structure_version IS NULL) THEN
1615 -- Return a null ID since the name is null.
1616 x_Org_structure_version_id := NULL;
1617
1618 ELSE
1619 -- Find the ID which matches the Name passed
1620 OPEN c_ids;
1621 LOOP
1622 FETCH c_ids INTO l_current_id;
1623 EXIT WHEN c_ids%NOTFOUND;
1624 END LOOP;
1625 l_num_ids := c_ids%ROWCOUNT;
1626 CLOSE c_ids;
1627
1628 IF (l_num_ids = 0) THEN
1629 -- No IDs for name
1630 RAISE NO_DATA_FOUND;
1631 ELSIF (l_num_ids = 1) THEN
1632 -- Since there is only one ID for the name use it.
1633 x_Org_structure_version_id := l_current_id;
1634 ELSIF (l_num_ids > 1 ) THEN
1635 -- More than one ID for the name and none of the IDs matched
1636 -- the ID passed in.
1637 RAISE TOO_MANY_ROWS;
1638 END IF;
1639 END IF; -- end if for p_Org_structure_version IS NULL
1640 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1641
1642 EXCEPTION
1643 WHEN NO_DATA_FOUND THEN
1644 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1645 x_error_message_code := 'PJI_ORG_STRUCTURE_VER_INVALID';
1646 x_Org_structure_version_id := NULL;
1647 WHEN TOO_MANY_ROWS THEN
1648 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1649 x_error_message_code := 'PJI_ORG_STRUCT_VER_AMBIGUOUS';
1650 x_Org_structure_version_id := NULL;
1651 WHEN OTHERS THEN
1652 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1653 x_Org_structure_version_id := NULL;
1654 RAISE ;
1655
1656 END Check_Org_structure_Version;
1657
1658 ----------------------------------------------------------------------------------------------------------------
1659 -- API : Check_Budget_Type
1660 -- Description : This procedure checks and returns a valid budget type code.
1661 -- Parameters :
1662 -- IN :p_budget_type - Budget Type.
1663 -- p_amount_type_code - Amount Type Code (Rev or Cost)
1664 -- OUT NOCOPY :x_budget_type_code
1665 -- x_return_status - Return status.
1666 -- x_error_message_code - Return Error Code.
1667 ----------------------------------------------------------------------------------------------------------------
1668 PROCEDURE Check_Budget_Type
1669 ( p_budget_type IN VARCHAR2
1670 ,p_amount_type_code IN VARCHAR2
1671 ,x_budget_type_code OUT NOCOPY VARCHAR2
1672 ,x_return_status OUT NOCOPY VARCHAR2
1673 ,x_error_message_code OUT NOCOPY VARCHAR2) IS
1674
1675
1676 l_current_id VARCHAR2(100);
1677 l_num_ids NUMBER := 0;
1678 CURSOR c_ids IS
1679 SELECT bt.budget_type_code
1680 FROM pa_budget_types bt
1681 WHERE bt.budget_type = p_budget_type
1682 AND bt.budget_amount_code = p_amount_type_code ;
1683
1684
1685
1686 BEGIN
1687 IF (p_budget_type IS NULL) THEN
1688 -- Return a null ID since the name is null.
1689 x_budget_type_code := NULL;
1690
1691 ELSE
1692 -- Find the ID which matches the Name passed
1693 OPEN c_ids;
1694 LOOP
1695 FETCH c_ids INTO l_current_id;
1696 EXIT WHEN c_ids%NOTFOUND;
1697 END LOOP;
1698 l_num_ids := c_ids%ROWCOUNT;
1699 CLOSE c_ids;
1700
1701 IF (l_num_ids = 0) THEN
1702 -- No IDs for name
1703 RAISE NO_DATA_FOUND;
1704 ELSIF (l_num_ids = 1) THEN
1705 -- Since there is only one ID for the name use it.
1706 x_budget_type_code := l_current_id;
1707 ELSIF (l_num_ids > 1 ) THEN
1708 -- More than one ID for the name and none of the IDs matched
1709 -- the ID passed in.
1710 RAISE TOO_MANY_ROWS;
1711 END IF;
1712 END IF; -- end if for p_budget_type IS NULL
1713 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1714
1715 EXCEPTION
1716 WHEN NO_DATA_FOUND THEN
1717 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1718 x_error_message_code := 'PA_BUDGET_TYPE_INVALID';
1719 x_budget_type_code := NULL;
1720 WHEN TOO_MANY_ROWS THEN
1721 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1722 x_error_message_code := 'PA_BUDGET_TYPE_AMBIGUOUS';
1723 x_budget_type_code := NULL;
1724 WHEN OTHERS THEN
1725 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1726 x_budget_type_code := NULL;
1727 RAISE ;
1728
1729 END Check_Budget_Type;
1730
1731 PROCEDURE Derive_Summarization_Flags
1732 ( x_base_summary_flag OUT NOCOPY VARCHAR2
1733 ,x_intelligence_flag OUT NOCOPY VARCHAR2
1734 ,x_performance_flag OUT NOCOPY VARCHAR2) IS
1735 BEGIN
1736 IF Pji_Process_Util.SUMMARIZATION_STARTED('STAGE1_EXTR') = 'NOT_STARTED' THEN
1737 x_base_summary_flag := 'N';
1738 ELSE
1739 x_base_summary_flag := 'Y';
1740 END IF;
1741
1742 IF Pji_Process_Util.SUMMARIZATION_STARTED('STAGE2_PJI') = 'NOT_STARTED' THEN
1743 x_intelligence_flag := 'N';
1744 ELSE
1745 x_intelligence_flag := 'Y';
1746 END IF;
1747
1748 IF Pji_Process_Util.SUMMARIZATION_STARTED('STAGE3_PJP') = 'NOT_STARTED' THEN
1749 x_performance_flag := 'N';
1750 ELSE
1751 x_performance_flag := 'Y';
1752 END IF;
1753 END Derive_Summarization_Flags;
1754
1755 END Pji_Setup_Pkg;