1 PACKAGE PA_ALLOC_RUN AUTHID CURRENT_USER AS
2 /* $Header: PAXALRNS.pls 120.1 2005/08/10 13:37:21 dlanka noship $ */
3
4
5 -- ------------------------------------------------------------
6 -- allocation_run: Main procedure for Allocation Run process
7 -- Called through a report.
8 -- ------------------------------------------------------------
9 PROCEDURE allocation_run( p_process_mode IN VARCHAR2
10 , p_debug_mode IN VARCHAR2
11 ,p_run_mode IN VARCHAR2 DEFAULT 'G'
12 , p_rule_id IN NUMBER
13 , p_run_period IN VARCHAR2 DEFAULT NULL
14 , p_expnd_item_date IN DATE DEFAULT NULL
15 , x_run_id OUT NOCOPY NUMBER
16 , x_retcode OUT NOCOPY VARCHAR2
17 , x_errbuf OUT NOCOPY VARCHAR2 );
18 G_alloc_run_id NUMBER;
19 -- ---------------------------------------------------------------
20 -- check_last_run_status: Checks the status of the rule being run.
21 -- Returns Mode= DRAFT or RELEASE,run_id,
22 -- prev_run_id to the calling function
23 -- ---------------------------------------------------------------
24 PROCEDURE check_last_run_status( p_rule_id IN NUMBER
25 , x_run_id IN OUT NOCOPY NUMBER
26 , x_mode OUT NOCOPY VARCHAR2 );
27
28 -- ----------------------------------------------------------------
29 -- ins_alloc_exceptions: Inserts a row into PA_ALLOC_EXCEPTIONS
30 -- table with the passed paramter values
31 -- ----------------------------------------------------------------
32 PROCEDURE ins_alloc_exceptions( p_rule_id IN NUMBER
33 , p_run_id IN NUMBER
34 , p_creation_date IN DATE
35 , p_created_by IN NUMBER
36 , p_last_updated_date IN DATE
37 , p_last_updated_by IN NUMBER
38 , p_last_update_login IN NUMBER
39 , p_level_code IN VARCHAR2
40 , p_exception_type IN VARCHAR2
41 , p_project_id IN NUMBER
42 , p_task_id IN NUMBER
43 , p_exception_code IN VARCHAR2 );
44
45 PROCEDURE alloc_errors ( p_rule_id IN NUMBER
46 , p_run_id IN NUMBER
47 , p_level IN VARCHAR2
48 , p_type IN VARCHAR2
49 , p_mesg_code IN VARCHAR2
50 , p_fatal_err IN BOOLEAN DEFAULT FALSE
51 , p_insert_flag IN VARCHAR2 DEFAULT 'Y'
52 , p_project_id IN NUMBER DEFAULT NULL
53 , p_task_id IN NUMBER DEFAULT NULL );
54
55 -- ----------------------------------------------------------------
56 -- validate_rule: Validates the following for the passed in rule_id
57 -- and run_id:
58 -- 1. Date efffectivity of rule
59 -- 2. Source Line definitions( PA and GL lines )
60 -- 3. Target Line definitions
61 -- 4. Offset definitions (if any)
62 -- 5. Basis definitions (if any)
63 -- 6. Date effectivity of Exp Types (offset/target)
64 -- 7. Expenditure Orgs (offset/target)
65 -- ----------------------------------------------------------------
66 PROCEDURE validate_rule( p_rule_id IN NUMBER
67 , p_run_id IN NUMBER
68 , p_start_date_active IN DATE
69 , p_end_date_active IN DATE
70 , p_source_extn_flag IN VARCHAR2
71 , p_target_extn_flag IN VARCHAR2
72 , p_target_exp_type_class IN VARCHAR2
73 , p_target_exp_org_id IN NUMBER
74 , p_target_exp_type IN VARCHAR2
75 , p_offset_exp_type_class IN VARCHAR2
76 , p_offset_exp_org_id IN NUMBER
77 , p_offset_exp_type IN VARCHAR2
78 , p_offset_method IN VARCHAR2
79 , p_offset_project_id IN NUMBER
80 , p_offset_task_id IN NUMBER
81 , p_basis_method IN VARCHAR2
82 , p_basis_amount_type IN VARCHAR2
83 , p_basis_balance_category IN VARCHAR2
84 , p_bas_budget_type_code IN VARCHAR2
85 , p_bas_bdgt_entry_mthd_code IN VARCHAR2
86 , p_basis_balance_type IN VARCHAR2
87 , p_org_id IN NUMBER
88 , p_fixed_amount IN NUMBER
89 , p_expnd_item_date IN DATE );
90
91 -- -----------------------------------------------------------------
92 -- insert_alloc_run_sources: Inserts a row into PA_ALLOC_RUN_SOURCES
93 -- table with the passed in param values
94 -- -----------------------------------------------------------------
95 PROCEDURE insert_alloc_run_sources( p_rule_id IN NUMBER
96 , p_run_id IN NUMBER
97 , p_line_num IN NUMBER
98 , p_project_id IN NUMBER
99 , p_task_id IN NUMBER
100 , p_exclude_flag IN VARCHAR2
101 , p_creation_date IN DATE
102 , p_created_by IN NUMBER
103 , p_last_update_date IN DATE
107
104 , p_last_updated_by IN NUMBER
105 , p_last_update_login IN NUMBER );
106
108 -- ----------------------------------------------------------------
109 -- exclude_curr_proj_task: Returns 0 if passed in project_id and task_id
110 -- need to be excluded, else returns 1
111 -- p_type = 'SRC' or 'TRG'
112 -- ----------------------------------------------------------------
113 FUNCTION exclude_curr_proj_task( p_run_id IN NUMBER
114 , p_type IN VARCHAR2
115 , p_project_id IN NUMBER
116 , p_task_id IN NUMBER ) RETURN NUMBER;
117
118
119 -- -------------------------------------------------------------------
120 -- populate_run_sources: Explodes the source_lines and source client
121 -- extension (if any), of the passed in
122 -- rule_id, upto project and lowest level tasks
123 -- and then populates PA_ALLOC_RUN_SOURCES table
124 -- --------------------------------------------------------------------
125 PROCEDURE populate_run_sources( p_rule_id IN NUMBER
126 , p_run_id IN NUMBER
127 , p_resource_list_id IN NUMBER
128 , p_source_clnt_extn_flag IN VARCHAR2
129 /* FP.M : Allocation Impact */
130 , p_alloc_resource_struct_type In Varchar2
131 , p_rbs_version_id In Number
132 );
133
134
135 -- -------------------------------------------------------------------
136 -- insert_alloc_run_targets: Inserts a row in PA_ALLOC_RUN_TARGETS
137 -- table with the passed in paramter values
138 -- -------------------------------------------------------------------
139 PROCEDURE insert_alloc_run_targets( p_rule_id IN NUMBER
140 , p_run_id IN NUMBER
141 , p_line_num IN NUMBER
142 , p_project_id IN NUMBER
143 , p_task_id IN NUMBER
144 , p_line_percent IN NUMBER
145 , p_exclude_flag IN VARCHAR2
146 , p_creation_date IN DATE
147 , p_created_by IN NUMBER
148 , p_last_update_date IN DATE
149 , p_last_updated_by IN NUMBER
150 , p_last_update_login IN NUMBER
151 , p_bas_method IN VARCHAR2
152 , p_dup_targets_flag IN VARCHAR2 );
153
154
155 -- ----------------------------------------------------------------
156 -- populate_run_targets:
157 -- ----------------------------------------------------------------
158 PROCEDURE populate_run_targets( p_rule_id IN NUMBER
159 , p_run_id IN NUMBER
160 , p_basis_method IN VARCHAR2
161 , p_bas_budget_type_code IN VARCHAR2
162 , p_bas_budget_entry_method_code IN VARCHAR2
163 , p_resource_list_id IN NUMBER
164 , p_trgt_client_extn IN VARCHAR2
165 , p_dup_targets_flag IN VARCHAR2
166 , p_expnd_item_date IN DATE
167 , p_limit_target_projects_code IN VARCHAR2
168 , x_basis_method OUT NOCOPY VARCHAR2
169 /* FP.M : Allocation Impact */
170 , p_basis_resource_struct_type in varchar2
171 , p_rbs_version_id in Number
172 );
173
174
175 -- ----------------------------------------------------------------
176 -- calculate_src_GL_amounts:
177 -- ----------------------------------------------------------------
178 PROCEDURE calculate_src_GL_amounts( p_rule_id IN NUMBER
179 , p_run_id IN NUMBER
180 , p_run_period IN VARCHAR2
181 , p_amount_type IN VARCHAR2
182 , x_gl_src_amount OUT NOCOPY NUMBER
183 );
184
185
186 -- ----------------------------------------------------------------
187 -- insert_alloc_run_GL_det:
188 -- ----------------------------------------------------------------
189 PROCEDURE insert_alloc_run_GL_det( p_run_id IN NUMBER
190 , p_rule_id IN NUMBER
191 , p_line_num IN NUMBER
192 , p_source_ccid IN NUMBER
193 , p_subtract_flag IN VARCHAR2
194 , p_creation_date IN DATE
195 , p_created_by IN NUMBER
196 , p_last_update_date IN DATE
197 , p_last_updated_by IN NUMBER
198 , p_last_update_login IN NUMBER
199 , p_source_percent IN NUMBER
200 , p_amount IN NUMBER
201 , p_eligible_amount IN NUMBER );
202
203 -- ------------------------------------------------------------------
204 -- get_trg_line_proj_task_count:
205 -- ------------------------------------------------------------------
206
207 FUNCTION get_trg_line_proj_task_count( p_run_id IN NUMBER
208 , p_line_num IN NUMBER ) RETURN NUMBER;
209
210 -- ------------------------------------------------------------------
211 -- get_sunk_cost:
212 -- ------------------------------------------------------------------
213 PROCEDURE get_sunk_cost( p_rule_id IN NUMBER
214 , p_run_id IN NUMBER
215 , p_fiscal_year IN NUMBER
216 , p_quarter_num IN NUMBER
217 , p_period_num IN NUMBER
218 , p_amount_type IN VARCHAR2
219 , x_src_sunk_cost OUT NOCOPY NUMBER
220 , x_tgt_sunk_cost OUT NOCOPY NUMBER
221 , p_src_proj_id IN NUMBER ) ;
222
223 -- -----------------------------------------------------------------
224 -- get_previous_alloc_amnt:
225 -- -----------------------------------------------------------------
226 FUNCTION get_previous_alloc_amnt( p_rule_id IN NUMBER
227 , p_run_id IN NUMBER
228 , p_project_id IN NUMBER
229 , p_task_id IN NUMBER
230 , p_quarter_num IN NUMBER
231 , p_fiscal_year IN NUMBER
232 , p_period_num IN NUMBER
233 , p_type IN VARCHAR2
234 , p_amount_type IN VARCHAR2 ) RETURN NUMBER;
235
236 -- ---------------------------------------------------------------------
237 -- insert_alloc_txn_details:
238 -- ---------------------------------------------------------------------
239 PROCEDURE insert_alloc_txn_details(x_alloc_txn_id IN OUT NOCOPY NUMBER /* Added for PA.L */
240 , p_run_id IN NUMBER
241 , p_rule_id IN NUMBER
242 , p_transaction_type IN VARCHAR2
243 , p_fiscal_year IN NUMBER
244 , p_quarter_num IN NUMBER
245 , p_period_num IN NUMBER
246 , p_run_period IN VARCHAR2
247 , p_line_num IN NUMBER
248 , p_project_id IN NUMBER
249 , p_task_id IN NUMBER
250 , p_expenditure_type IN VARCHAR2
251 , p_total_allocation IN NUMBER
252 , p_previous_allocation IN NUMBER
253 , p_current_allocation IN NUMBER
254 /* PA.L:Added for Capitalized Interest */
255 , p_EXPENDITURE_ID IN NUMBER DEFAULT NULL
256 , p_EXPENDITURE_ITEM_ID IN NUMBER DEFAULT NULL
257 , p_CINT_SOURCE_TASK_ID IN NUMBER DEFAULT NULL
258 , p_CINT_EXP_ORG_ID IN NUMBER DEFAULT NULL
259 , p_CINT_RATE_MULTIPLIER IN NUMBER DEFAULT NULL
260 , p_CINT_PRIOR_BASIS_AMT IN NUMBER DEFAULT NULL
261 , p_CINT_CURRENT_BASIS_AMT IN NUMBER DEFAULT NULL
262 , p_REJECTION_CODE IN VARCHAR2 DEFAULT NULL
263 , p_STATUS_CODE IN VARCHAR2 DEFAULT NULL
264 , p_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL
265 , p_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL
266 , p_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL
267 , p_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL
268 , p_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL
269 , p_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL
270 , p_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL
271 , p_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL
272 , p_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL
276 );
273 , p_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL
274 , p_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL
275 /* PA.L : end */
277
278 -- -------------------------------------------------------------------
279 -- create_target_txns:
280 -- -------------------------------------------------------------------
281 PROCEDURE create_target_txns( p_rule_id IN NUMBER
282 , p_run_id IN NUMBER
283 , p_type IN VARCHAR2
284 , p_fiscal_year IN NUMBER
285 , p_quarter_num IN NUMBER
286 , p_period_num IN NUMBER
287 , p_run_period IN VARCHAR2
288 , p_expenditure_type IN VARCHAR2
289 , p_allocation_method IN VARCHAR2
290 , p_basis_method IN VARCHAR2
291 , p_amount_type IN VARCHAR2
292 , p_pool_amount IN NUMBER
293 , x_curr_alloc_amount OUT NOCOPY NUMBER ) ;
294
295 -- -------------------------------------------------------------------
296 -- create_offset_txns:
297 -- -------------------------------------------------------------------
298 PROCEDURE create_offset_txns( p_rule_id IN NUMBER
299 , p_run_id IN NUMBER
300 , p_type IN VARCHAR2
301 , p_fiscal_year IN NUMBER
302 , p_quarter_num IN NUMBER
303 , p_period_num IN NUMBER
304 , p_run_period IN VARCHAR2
305 , p_expenditure_type IN VARCHAR2
306 , p_allocation_method IN VARCHAR2
307 , p_offset_method IN VARCHAR2
308 , p_offset_project_id IN NUMBER
309 , p_offset_task_id IN NUMBER
310 , p_amount_type IN VARCHAR2
311 , p_pool_amount IN NUMBER
312 , p_allocated_amount IN NUMBER ) ;
313
314 -- -------------------------------------------------------------------
315 -- allocate_remnant:
316 -- -------------------------------------------------------------------
317 PROCEDURE allocate_remnant( p_run_id IN NUMBER
318 , p_act_alloc_amount IN NUMBER
319 , x_remnant_amount OUT NOCOPY NUMBER );
320
321 -- -------------------------------------------------------------------
322 -- insert_alloc_runs:
323 -- -------------------------------------------------------------------
324 PROCEDURE insert_alloc_runs( x_run_id IN OUT NOCOPY NUMBER /* Modified as IN OUT for capint */
325 , p_rule_id IN NUMBER
326 , p_run_period IN VARCHAR2
327 , p_expnd_item_date IN DATE
328 , p_creation_date IN DATE
329 , p_created_by IN NUMBER
330 , p_last_update_date IN DATE
331 , p_last_updated_by IN NUMBER
332 , p_last_update_login IN NUMBER
333 , p_pool_percent IN NUMBER
334 , p_period_type IN VARCHAR2
335 , p_source_amount_type IN VARCHAR2
336 , p_source_balance_category IN VARCHAR2
337 , p_source_balance_type IN VARCHAR2
338 , p_alloc_resource_list_id IN NUMBER
339 , p_auto_release_flag IN VARCHAR2
340 , p_allocation_method IN VARCHAR2
341 , p_imp_with_exception IN VARCHAR2
342 , p_dup_targets_flag IN VARCHAR2
343 , p_target_exp_type_class IN VARCHAR2
344 , p_target_exp_org_id IN NUMBER
345 , p_target_exp_type IN VARCHAR2
346 , p_target_cost_type IN VARCHAR2
347 , p_offset_exp_type_class IN VARCHAR2
348 , p_offset_exp_org_id IN NUMBER
349 , p_offset_exp_type IN VARCHAR2
350 , p_offset_cost_type IN VARCHAR2
351 , p_offset_method IN VARCHAR2
352 , p_offset_project_id IN NUMBER
353 , p_offset_task_id IN NUMBER
354 , p_run_status IN VARCHAR2
355 , p_basis_method IN VARCHAR2
356 , p_basis_relative_period IN NUMBER
357 , p_basis_amount_type IN VARCHAR2
358 , p_basis_balance_category IN VARCHAR2
359 , p_basis_budget_type_code IN VARCHAR2
360 , p_basis_balance_type IN VARCHAR2
361 , p_basis_resource_list_id IN NUMBER
362 , p_fiscal_year IN NUMBER
363 , p_quarter IN NUMBER
364 , p_period_num IN VARCHAR2
365 , p_target_exp_group IN VARCHAR2
366 , p_offset_exp_group IN VARCHAR2
367 , p_total_pool_amount IN NUMBER
368 , p_allocated_amount IN NUMBER
369 , p_reversal_date IN DATE
370 , p_draft_request_id IN NUMBER
371 , p_draft_request_date IN DATE
372 , p_release_request_id IN NUMBER
373 , p_release_request_date IN DATE
374 , p_denom_currency_code IN VARCHAR2
375 , p_fixed_amount IN NUMBER
376 , p_rev_target_exp_group IN VARCHAR2
377 , p_rev_offset_exp_group IN VARCHAR2
378 , p_org_id IN NUMBER
379 , p_limit_target_projects_code IN VARCHAR2
380 , p_CINT_RATE_NAME IN VARCHAR2 default NULL
381 /* FP.M : Allocation Impact : 3512552 */
382 , p_ALLOC_RESOURCE_STRUCT_TYPE IN Varchar2 default NULL
383 , p_BASIS_RESOURCE_STRUCT_TYPE IN Varchar2 default NULL
384 , p_ALLOC_RBS_VERSION IN Number default NULL
385 , p_BASIS_RBS_VERSION IN Number default NULL
386
387 );
388
389
390 -- -------------------------------------------------------------------
391 -- The procedures above were done by msiddiqu
392 -- The procedures below were done by sesivara
393 -- -------------------------------------------------------------------
394
395
396 TYPE SRC_RLM_RECORD IS RECORD (
397 resource_list_member_id NUMBER ,
398 resource_percent NUMBER
399 ) ;
400 TYPE SRC_RLM_TABTYPE IS TABLE OF SRC_RLM_RECORD
401 INDEX BY BINARY_INTEGER ;
402
403 -- -------------------------------------------------------------------
404 -- Init_who_cols
405 -- -------------------------------------------------------------------
406 procedure Init_who_cols ;
407
408 -- -------------------------------------------------------------------
409 -- get_fiscalyear_quarter
410 -- -------------------------------------------------------------------
411 /* Procedure : get_fiscalyear_quarter()
412 Purpose : For a given run_period_type (PA/GL) and run_period, this procedure will get
413 period_type, period_set_name ( calender) , period_year ( Fiscal Year), quarter
414 period_num and end date of the run period.
415 Created : 27-JUL-98 Sesivara
416 */
417
418 Procedure get_fiscalyear_quarter( p_run_period_type IN VARCHAR2 ,
419 p_run_period IN VARCHAR2 ,
420 x_period_type OUT NOCOPY VARCHAR2 ,
421 x_period_set_name OUT NOCOPY VARCHAR2 ,
422 x_period_year OUT NOCOPY NUMBER ,
423 x_quarter OUT NOCOPY NUMBER ,
424 x_period_num OUT NOCOPY NUMBER ,
425 x_run_period_end_date OUT NOCOPY DATE ) ;
426
427 -- -------------------------------------------------------------------
428 -- populate_RLM_table
429 -- -------------------------------------------------------------------
430 Procedure populate_RLM_table( p_rule_id IN NUMBER,
431 p_run_id IN NUMBER,
432 p_type IN VARCHAR2,
433 p_resource_list_id IN NUMBER ,
434 /* FP.M : Allocation Impact Bug # 3512552 */
435 p_resource_struct_type in Varchar2 ,
436 p_rbs_version_id In Number ,
437 p_basis_category In Varchar2
438 );
439
440
441
442 -- -------------------------------------------------------------------
443 -- get_amttype_start_date
444 -- -------------------------------------------------------------------
445 PROCEDURE get_amttype_start_date( p_amt_type IN VARCHAR2,
446 p_period_type IN VARCHAR2 ,
447 p_period_set_name IN VARCHAR2 ,
448 p_run_period_end_date IN DATE,
449 p_quarter_num IN NUMBER,
450 p_period_year IN NUMBER,
451 p_period IN VARCHAR2 ,
452 x_start_date OUT NOCOPY DATE ) ;
453
454 -- -------------------------------------------------------------------
455 -- get_alloc_amount
456 -- -------------------------------------------------------------------
457 PROCEDURE get_alloc_amount( p_amt_type IN VARCHAR2,
458 p_bal_type IN VARCHAR2,
459 p_run_period_type IN VARCHAR2,
460 p_project_id IN NUMBER ,
461 p_task_id IN NUMBER ,
462 p_rlm_id IN NUMBER ,
463 p_period IN VARCHAR2,
464 p_period_type IN VARCHAR2 ,
465 p_peiod_set_name IN VARCHAR2 ,
466 p_period_year IN NUMBER ,
467 p_quarter IN NUMBER ,
468 p_run_period_end_date IN DATE ,
469 p_amttype_start_date IN DATE ,
470 x_amount OUT NOCOPY NUMBER ) ;
471
472 -- -------------------------------------------------------------------
473 -- cal_amounts_from_projects
474 -- -------------------------------------------------------------------
475 PROCEDURE cal_amounts_from_projects(p_rule_id IN NUMBER,
476 p_run_id IN NUMBER,
477 p_run_period_type IN VARCHAR2,
478 p_run_amount_type IN VARCHAR2,
479 p_run_period IN VARCHAR2,
480 p_bal_type IN VARCHAR2,
481 p_resource_list_id IN NUMBER ,
482 p_pool_percent IN NUMBER ,
483 p_fixed_amount IN NUMBER ,
484 x_proj_pool_amount OUT NOCOPY NUMBER ,
485 /* FP.M : Allocation Impact Bug # 3512552 */
486 p_source_resource_struct_type in Varchar2,
487 p_source_rbs_version_id In Number
488 );
489
490 -- -------------------------------------------------------------------
491 -- insert_alloc_run_src_det
492 -- -------------------------------------------------------------------
493 PROCEDURE insert_alloc_run_src_det( p_rule_id IN NUMBER
494 , p_run_id IN NUMBER
495 , p_line_num IN NUMBER
496 , p_project_id IN NUMBER
497 , p_task_id IN NUMBER
498 , p_rlm_id IN NUMBER
499 , p_amount IN NUMBER
500 , p_resource_percent IN NUMBER
501 , p_eligible_amount IN NUMBER
502 , p_creation_date IN DATE
503 , p_created_by IN NUMBER
504 , p_last_update_date IN DATE
505 , p_last_updated_by IN NUMBER
506 , p_last_update_login IN NUMBER) ;
507
508 -- -------------------------------------------------------------------
509 -- get_relative_period_name
510 -- -------------------------------------------------------------------
511 Procedure get_relative_period_name( p_period_set_name IN VARCHAR2,
512 p_period_type IN VARCHAR2,
513 p_run_period_end_date IN DATE,
514 p_run_period IN VARCHAR2,
515 p_relative_period IN NUMBER,
516 x_rel_period_name OUT NOCOPY VARCHAR2 ) ;
517
518 -- -------------------------------------------------------------------
522 , p_run_id IN NUMBER
519 -- insert_alloc_run_basis_det
520 -- -------------------------------------------------------------------
521 PROCEDURE insert_alloc_run_basis_det( p_rule_id IN NUMBER
523 , p_line_num IN NUMBER
524 , p_project_id IN NUMBER
525 , p_task_id IN NUMBER
526 , p_rlm_id IN NUMBER
527 , p_amount IN NUMBER
528 , p_basis_percent IN NUMBER
529 , p_line_percent IN NUMBER
530 , p_creation_date IN DATE
531 , p_created_by IN NUMBER
532 , p_last_update_date IN DATE
533 , p_last_updated_by IN NUMBER
534 , p_last_update_login IN NUMBER) ;
535
536 -- -------------------------------------------------------------------
537 -- cal_proj_basis_amounts
538 -- -------------------------------------------------------------------
539 PROCEDURE cal_proj_basis_amounts(p_rule_id IN NUMBER,
540 p_run_id IN NUMBER,
541 p_run_period_type IN VARCHAR2,
542 p_run_period IN VARCHAR2,
543 p_basis_method IN OUT NOCOPY VARCHAR2, -- verify
544 p_basis_amt_type IN VARCHAR2,
545 P_basis_bal_type IN VARCHAR2,
546 P_basis_rel_period IN NUMBER,
547 p_basis_category IN VARCHAR2,
548 p_basis_RL_id IN NUMBER ,
549 p_budget_type_code IN VARCHAR2,
550 x_proj_pool_amount OUT NOCOPY NUMBER ,
551 /* FP.M : Allocation Impact : Bug# 3512552 */
552 p_basis_resource_struct_type in Varchar2 ,
553 p_basis_rbs_version_id in number
554 ) ;
555
556 -- -------------------------------------------------------------------
557 -- get_budget_amounts
558 -- -------------------------------------------------------------------
559 /***PROCEDURE get_budget_amounts( p_run_period_type IN VARCHAR2,
560 *** p_bal_type IN VARCHAR2,
561 *** p_project_id IN NUMBER ,
562 *** p_task_id IN NUMBER ,
563 *** p_rl_id IN NUMBER ,
564 *** p_rlm_id IN NUMBER ,
565 *** p_budget_type_code IN VARCHAR2,
569 *** commented for bug 2619977 */
566 *** p_start_date IN DATE ,
567 *** p_end_date IN DATE ,
568 *** x_amount OUT NUMBER ) ;
570 -- -------------------------------------------------------------------
571 -- clean_up_targets_for_actuals
572 -- -------------------------------------------------------------------
573 PROCEDURE clean_up_targets_for_actuals(
574 p_run_id IN NUMBER,
575 p_rule_id IN NUMBER,
576 p_amt_type IN VARCHAR2,
577 p_run_period_type IN VARCHAR2,
578 p_period IN VARCHAR2,
579 p_run_period_end_date IN DATE ,
580 p_amttype_start_date IN DATE,
581 p_basis_method IN OUT NOCOPY VARCHAR2 -- verify
582 ) ;
583
584 -- -------------------------------------------------------------------
585 -- Release_alloc_txns
586 -- -------------------------------------------------------------------
587 PROCEDURE Release_alloc_txns( p_rule_id IN NUMBER
588 ,p_run_id IN NUMBER
589 , x_retcode OUT NOCOPY VARCHAR2
590 , x_errbuf OUT NOCOPY VARCHAR2
591 ) ;
592
593 -- -------------------------------------------------------------------
594 -- Reverse_alloc_txns
595 -- -------------------------------------------------------------------
596 PROCEDURE Reverse_alloc_txns( p_rule_id IN NUMBER
597 ,p_run_id IN NUMBER
598 ,p_tgt_exp_group IN VARCHAR2
599 ,p_off_exp_group IN VARCHAR2
600 ,x_retcode OUT NOCOPY NUMBER
601 ,x_errbuf OUT NOCOPY VARCHAR2
602 ) ;
603 -- -------------------------------------------------------------------
604 -- Delete_alloc_txns
605 -- -------------------------------------------------------------------
606 PROCEDURE Delete_alloc_txns( p_rule_id IN NUMBER
607 ,p_run_id IN NUMBER) ;
608
609 -- -------------------------------------------------------------------
610 -- lock_rule
611 -- -------------------------------------------------------------------
612 PROCEDURE lock_rule(p_rule_id IN NUMBER
613 ,p_run_id IN NUMBER ) ;
614
615 -- -------------------------------------------------------------------
616 -- unlock_rule
617 -- -------------------------------------------------------------------
618 PROCEDURE unlock_rule(p_rule_id IN NUMBER
619 ,p_run_id IN NUMBER ) ;
620
621 -- ------------------------------------------------------------
622 -- insert_missing_costs
623 -- ------------------------------------------------------------
624 PROCEDURE insert_missing_costs( p_run_id IN NUMBER
625 , p_type_code IN VARCHAR2
626 , p_project_id IN NUMBER
627 , p_task_id IN NUMBER
628 , p_amount IN NUMBER );
629
630
631 --------------------------------------------------------------------------
632 --Function: Is_src_project_valid
633 --Purpose: validating source project_id returned from source client extension
634 ----------------------------------------------------------------------------
635 FUNCTION Is_src_project_valid(p_project_id IN NUMBER) RETURN VARCHAR2 ;
636
637 --------------------------------------------------------------------------
638 --Function: Is_src_task_valid
639 --Purpose: validating source task_id returned from source client extension
640 ----------------------------------------------------------------------------
641
642 FUNCTION Is_src_task_valid(p_project_id IN NUMBER,p_task_id IN NUMBER) RETURN VARCHAR2 ;
643
644 --------------------------------------------------------------------------
645 --Function: Is_tgt_project_valid
646 --Purpose: validating target project_id returned from target client extension
647 ----------------------------------------------------------------------------
648 FUNCTION Is_tgt_project_valid(p_project_id IN NUMBER) RETURN VARCHAR2 ;
649
650 --------------------------------------------------------------------------
651 --Function: Is_tgt_task_valid
652 --Purpose: validating target task_id returned from target client extension
653 ----------------------------------------------------------------------------
654 FUNCTION Is_tgt_task_valid(p_project_id IN NUMBER,p_task_id IN NUMBER) RETURN VARCHAR2;
655
656 --------------------------------------------------------------------------
657 --Function: Is_offset_project_valid
658 --Purpose: validating offset project_id returned from offset client extension
659 ----------------------------------------------------------------------------
660 FUNCTION Is_offset_project_valid(p_project_id IN NUMBER) RETURN VARCHAR2 ;
661
662 --------------------------------------------------------------------------
663 --Function: Is_offset_task_valid
664 --Purpose: validating offset task_id returned from offset client extension
665 ----------------------------------------------------------------------------
666 FUNCTION Is_offset_task_valid(p_project_id IN NUMBER,p_task_id IN NUMBER) RETURN VARCHAR2;
667
668 --------------------------------------------------------------------------
669 --Function: build_src_sql
670 --Purpose: build dynamic sql for sources
671 ----------------------------------------------------------------------------
672 Procedure build_src_sql( p_project_org_id IN NUMBER
673 ,p_project_type IN VARCHAR2
674 ,p_task_org_id IN NUMBER
675 ,p_service_type IN VARCHAR2
676 ,p_class_category IN VARCHAR2
677 ,p_class_code IN VARCHAR2
678 ,p_project_id IN NUMBER
679 ,p_task_id IN NUMBER
680 ,x_sql_str OUT NOCOPY VARCHAR2 ) ;
681
682 --------------------------------------------------------------------------
683 --Function: Build_tgt_sql
684 --Purpose: build dynamic sql for targets
685 ----------------------------------------------------------------------------
686 Procedure Build_tgt_sql( p_project_org_id IN NUMBER
687 ,p_project_type IN VARCHAR2
688 ,p_task_org_id IN NUMBER
689 ,p_service_type IN VARCHAR2
690 ,p_class_category IN VARCHAR2
691 ,p_class_code IN VARCHAR2
692 ,p_project_id IN NUMBER
693 ,p_task_id IN NUMBER
694 ,p_billable_only_flag IN VARCHAR2
695 ,p_expnd_item_date IN DATE
696 ,p_limit_target_projects_code IN VARCHAR2
697 ,x_sql_str OUT NOCOPY VARCHAR2 ) ;
698
699 --------------------------------------------------------------------------
700 --Function: Delete_alloc_run
701 --Purpose: Delete allocation run give a rule_id
702 ----------------------------------------------------------------------------
703 PROCEDURE Delete_alloc_run(
704 errbuf OUT NOCOPY VARCHAR2,
705 retcode OUT NOCOPY VARCHAR2,
706 p_rule_id IN NUMBER
707 );
708
709 --------------------------------------------------------------------------
710 --Procedure: insert_alloc_basis_resource
711 --Purpose: inserts resource related actuals data into pa_alloc_run_basis_det table.
712 ----------------------------------------------------------------------------
713
714 PROCEDURE insert_alloc_basis_resource(
715 p_run_id IN NUMBER,
716 p_rule_id IN NUMBER,
717 p_resource_list_id IN NUMBER,
718 p_amt_type IN VARCHAR2,
719 p_bal_type IN VARCHAR2,
720 p_run_period_type IN VARCHAR2,
721 p_period IN VARCHAR2,
722 p_run_period_end_date IN DATE ,
723 p_amttype_start_date IN DATE ,
724 -- FP.M : Allocation Impact
725 p_resource_struct_type in Varchar2,
726 p_rbs_version_id In Varchar2
727 );
728
729 --This procedure deletes the source details for each capital interest transaction. This procedure will
730 --be called from delete_alloc_run api when the DELETE button is pressed to delete a capital interest
731 --batch
732 PROCEDURE delete_cint_source_dets
733 ( p_run_id IN pa_alloc_runs_all.run_id%TYPE
734 ,x_return_status OUT NOCOPY VARCHAR2
735 ,x_msg_count OUT NOCOPY NUMBER
736 ,x_msg_data OUT NOCOPY VARCHAR2
737 );
738
739 --Added this procedure for Capital Project Enhancement. This procedure releases a capitalized interest run
740 --This procedure is called from PA_CAP_INT_PVT. Generate_cap_interest when release button is pressed on the
741 --Allocation form(when form is accessed in the context of capitalized interest) or when auto release flag is
742 --passed Y
743 PROCEDURE release_capint_txns
744 ( p_run_id IN pa_alloc_runs_all.run_id%TYPE
745 ,x_return_status OUT NOCOPY VARCHAR2
746 ,x_msg_count OUT NOCOPY NUMBER
747 ,x_msg_data OUT NOCOPY VARCHAR2
748 );
749
750
751 -- ==========================================================================
752 /* PROCEDURE : insert_alloc_source_resource
753 Purpose : To insert data into pa_alloc_run_source_det table for each resource
754 for each task which has some data available in summarization.
755 Separate inserts are written for each type of amt_type
756 (FYTD,qtd,itd and ptd).
757 Created : 16-JAN-02 Manokuma
758 Modified: 24-JAN-03 Tarun for bug 2757875
759 */
760 -- ==========================================================================
761 PROCEDURE insert_alloc_source_resource(
762 p_run_id IN NUMBER,
763 p_rule_id IN NUMBER,
764 p_resource_list_id IN NUMBER,
765 p_amt_type IN VARCHAR2,
766 p_bal_type IN VARCHAR2,
767 p_run_period_type IN VARCHAR2,
768 p_period IN VARCHAR2,
769 p_run_period_end_date IN DATE ,
770 p_amttype_start_date IN DATE ,
771 /* FP.M : Allocation Impact */
772 p_resource_struct_type in Varchar2,
773 p_rbs_version_id in Number
774 );
775
776 END PA_ALLOC_RUN;
[Home] [Help]