1 package BEN_CWB_SUMMARY_PKG as
2 /* $Header: bencwbsm.pkh 120.1.12010000.1 2008/07/29 12:07:53 appldev ship $ */
3 -- --------------------------------------------------------------------------
4 -- |--------------------------< update_or_insert >---------------------------|
5 -- --------------------------------------------------------------------------
6 -- Description
7 -- This procedure tries the update the record in tst_cwb_summary for the
8 -- given combination. If the record is already locked, a new record will be
9 -- inserted with status "P". If there exists no records with the given
10 -- combination, a new record will be inserted with status null.
11 --
12 procedure update_or_insert (p_sum_rec in ben_cwb_summary%rowtype);
13 --
14 -- --------------------------------------------------------------------------
15 -- |----------------------< consolidate_summary_rec >------------------------|
16 -- --------------------------------------------------------------------------
17 -- Description
18 -- This procedure consolidates the summary records for a given person
19 --
20 procedure consolidate_summary_rec(p_person_id in number);
21 --
22 -- --------------------------------------------------------------------------
23 -- |--------------------< consolidate_summary_rec_all >----------------------|
24 -- --------------------------------------------------------------------------
25 -- Description
26 -- This procedure consolidates the summary records for all persons having
27 -- split rows in ben_cwb_summary
28 procedure consolidate_summary_rec_all;
29 --
30 -- --------------------------------------------------------------------------
31 -- |----------------------< refresh_summary_group_pl >-----------------------|
32 -- --------------------------------------------------------------------------
33 -- Description
34 -- This procedure calculates the summary for all the persons for a group
35 -- plan.
36 --
37 procedure refresh_summary_group_pl(p_group_pl_id in number
38 ,p_lf_evt_ocrd_dt in date);
39 --
40 -- --------------------------------------------------------------------------
41 -- |-----------------------< refresh_summary_persons >-----------------------|
42 -- --------------------------------------------------------------------------
43 -- Description
44 -- This procedure calculates the summary for all persons with -1 as
45 -- person id in ben_cwb_person_info
46 --
47 procedure refresh_summary_persons(p_group_pl_id in number
48 ,p_lf_evt_ocrd_dt in date);
49 --
50 -- --------------------------------------------------------------------------
51 -- |--------------------< update_or_insert_pl_sql_tab >----------------------|
52 -- --------------------------------------------------------------------------
53 -- Description
54 -- This procedure stores the given summary values in a pl/sql table.
55 -- save_pl_sql_tab needs to be called finally to save the values in the table
56 -- into ben_cwb_summary
57 procedure update_or_insert_pl_sql_tab
58 (p_group_per_in_ler_id in number
59 ,p_group_pl_id in number
60 ,p_group_oipl_id in number
61 ,p_elig_count_direct in number default null
62 ,p_elig_count_all in number default null
63 ,p_emp_recv_count_direct in number default null
64 ,p_emp_recv_count_all in number default null
65 ,p_elig_sal_val_direct in number default null
66 ,p_elig_sal_val_all in number default null
67 ,p_ws_val_direct in number default null
68 ,p_ws_val_all in number default null
69 ,p_ws_bdgt_val_direct in number default null
70 ,p_ws_bdgt_val_all in number default null
71 ,p_ws_bdgt_iss_val_direct in number default null
72 ,p_ws_bdgt_iss_val_all in number default null
73 ,p_bdgt_val_direct in number default null
74 ,p_bdgt_iss_val_direct in number default null
75 ,p_stat_sal_val_direct in number default null
76 ,p_stat_sal_val_all in number default null
77 ,p_oth_comp_val_direct in number default null
78 ,p_oth_comp_val_all in number default null
79 ,p_tot_comp_val_direct in number default null
80 ,p_tot_comp_val_all in number default null
81 ,p_rec_val_direct in number default null
82 ,p_rec_val_all in number default null
83 ,p_rec_mn_val_direct in number default null
84 ,p_rec_mn_val_all in number default null
85 ,p_rec_mx_val_direct in number default null
86 ,p_rec_mx_val_all in number default null
87 ,p_misc1_val_direct in number default null
88 ,p_misc1_val_all in number default null
89 ,p_misc2_val_direct in number default null
90 ,p_misc2_val_all in number default null
91 ,p_misc3_val_direct in number default null
92 ,p_misc3_val_all in number default null
93 ,p_person_id in number default null
94 ,p_lf_evt_ocrd_dt in date default null);
95 --
96 -- --------------------------------------------------------------------------
97 -- |---------------------------< save_pl_sql_tab >---------------------------|
98 -- --------------------------------------------------------------------------
99 -- Description
100 -- This procedures updates the data in g_summary_tab to
101 -- ben_cwb_summary table.
102 --
103 procedure save_pl_sql_tab;
104 --
105 -- --------------------------------------------------------------------------
106 -- |--------------------------< delete_pl_sql_tab >--------------------------|
107 -- --------------------------------------------------------------------------
108 -- Description
109 -- This procedures deletes the data in g_summary_tab
110 --
111 procedure delete_pl_sql_tab;
112 --
113 -- --------------------------------------------------------------------------
114 -- |-------------------< update_summary_on_reassignment >--------------------|
115 -- --------------------------------------------------------------------------
116 -- Description
117 -- This procedure updates the summary values in the hierarchy when
118 -- employee is re-assigned.
119 procedure update_summary_on_reassignment(p_old_mgr_per_in_ler_id in number
120 ,p_new_mgr_per_in_ler_id in number
121 ,p_emp_per_in_ler_id in number);
122 --
123 -- --------------------------------------------------------------------------
124 -- |----------------------< delete_summary_group_pl >------------------------|
125 -- --------------------------------------------------------------------------
126 -- Description
127 -- This procedure removes the summary records from the summary table for
128 -- a given group_pl_id and lf_evt_ocrd_dt. This will called from the backout
129 -- process.
130 procedure delete_summary_group_pl(p_group_pl_id number
131 ,p_lf_evt_ocrd_dt date);
132 --
133 -- --------------------------------------------------------------------------
134 -- |----------------< upd_summary_on_elig_sal_change >--------------------|
135 -- --------------------------------------------------------------------------
136 procedure upd_summary_on_elig_sal_change(p_group_per_in_ler_id in number
137 ,p_elig_sal_change in number);
138
139 --
140 -- --------------------------------------------------------------------------
141 -- |--------------------------< clean_budget_data >-------------------------|
142 -- --------------------------------------------------------------------------
143 procedure clean_budget_data(p_per_in_ler_id in number
144 ,p_lvl_up in number default null);
145 --
146 --
147 -- --------------------------------------------------------------------------
148 -- |-------------------< refresh_summary_all_plans >-----------------------|
149 -- --------------------------------------------------------------------------
150 -- Description: This procedure refreshes all plans that have cpi records
151 -- with -1 person_ids.
152 --
153 procedure refresh_summary_all_plans;
154 --
155 end BEN_CWB_SUMMARY_PKG;
156
[Home] [Help]