1 PACKAGE pqp_budget_maintenance AS
2 /* $Header: pqabvmaintain.pkh 120.2.12010000.1 2008/07/28 11:07:34 appldev ship $ */
3 TYPE t_indexed_dates IS TABLE OF DATE
4 INDEX BY BINARY_INTEGER;
5
6 TYPE t_asg_set_amnds IS TABLE OF hr_assignment_set_amendments.include_or_exclude%TYPE
7 INDEX BY BINARY_INTEGER;
8
9 g_tab_asg_set_amnds t_asg_set_amnds;
10 -- configuration value constant
11
12 c_abvm_maintenance CONSTANT pqp_configuration_values.pcv_information_category%TYPE
13 := 'PQP_ABVM_MAINTENANCE';
14 c_abvm_definition CONSTANT pqp_configuration_values.pcv_information_category%TYPE
15 := 'PQP_ABVM_DEFINITION';
16 c_abvm_fte_additional CONSTANT pqp_configuration_values.pcv_information_category%TYPE
17 := 'PQP_ABVM_FTE_ADDITIONAL';
18
19 -- Procedure to get legislative parameters for payroll action
20
21 PROCEDURE GET_PARAMETER_LIST(
22 p_pay_action_id IN NUMBER
23 ,p_parameter_list OUT NOCOPY VARCHAR2
24 );
25
26 -- Function to get the value of a legislative parameter
27 FUNCTION get_parameter_value(
28 p_string IN VARCHAR2
29 ,p_parameter_list IN VARCHAR2
30 )
31 RETURN VARCHAR2;
32
33 -- Procedure to get assignment set details
34 PROCEDURE get_asg_set_details(
35 p_assignment_set_id IN NUMBER
36 ,p_formula_id OUT NOCOPY NUMBER
37 ,p_tab_asg_set_amnds OUT NOCOPY t_asg_set_amnds
38 );
39
40 -- Procedure for range cursor
41 PROCEDURE range_cursor(
42 p_pay_action_id IN NUMBER
43 ,p_sqlstr OUT NOCOPY VARCHAR2
44 );
45
46 -- Function to check whether an assignment exists in an assignment set
47 FUNCTION chk_is_asg_in_asg_set(
48 p_assignment_id IN NUMBER
49 ,p_formula_id IN NUMBER
50 ,p_tab_asg_set_amnds IN t_asg_set_amnds
51 ,p_effective_date IN DATE
52 )
53 RETURN VARCHAR2;
54
55 -- Function for assignment action creation
56 PROCEDURE action_creation(
57 p_pay_action_id IN NUMBER
58 ,p_start_person IN NUMBER
59 ,p_end_person IN NUMBER
60 ,p_chunk IN NUMBER
61 );
62
63 -- Procedure for archive data
64 PROCEDURE archive_data(
65 p_assignment_action_id IN NUMBER
66 ,p_effective_date IN DATE
67 );
68
69 -- Procedure for deinitialise
70 PROCEDURE deinitialization_code(p_pay_action_id IN NUMBER);
71
72 --
73 -- Maintain_ABV_For_Assignment
74 -- Maintain assignment budget values for a single assignment
75 PROCEDURE maintain_abv_for_assignment(
76 p_uom IN VARCHAR2
77 ,p_assignment_id IN NUMBER
78 ,p_business_group_id IN NUMBER
79 ,p_effective_date IN DATE
80 ,p_action IN VARCHAR2
81 );
82
83 -- Get_Impact_Dates
84 -- Get the set of dates on which to calculate budget value
85 PROCEDURE get_event_dates(
86 p_uom IN VARCHAR2
87 ,p_assignment_id IN NUMBER
88 ,p_business_group_id IN NUMBER
89 ,p_event_dates_source IN VARCHAR2
90 ,p_event_group_id IN NUMBER
91 ,p_custom_function IN VARCHAR2
92 ,p_effective_date IN DATE
93 ,p_impact_dates IN OUT NOCOPY t_indexed_dates
94 );
95
96 -- Get_Change_Dates_From_DTI
97 -- Obtains dates returned by datetrack interpreter
98 PROCEDURE get_change_dates_from_dti(
99 p_assignment_id IN NUMBER
100 ,p_business_group_id IN NUMBER
101 ,p_event_group_id IN NUMBER
102 ,p_calculation_date IN DATE
103 ,p_impact_dates IN OUT NOCOPY t_indexed_dates
104 );
105
106 -- Execute_Custom_Function
107 -- Construct the dynamic query to execute a user provided custom
108 -- function or the inbuilt custom function to obtain impact dates
109 PROCEDURE execute_custom_function(
110 p_uom IN VARCHAR2
111 ,p_assignment_id IN NUMBER
112 ,p_business_group_id IN NUMBER
113 ,p_custom_function IN VARCHAR2
114 ,p_effective_date IN DATE
115 ,p_event_dates IN OUT NOCOPY pqp_table_of_dates
116 );
117
118 -- Get_FTE_Event_Dates
119 -- Inbuilt custom function to return impact dates using search and
120 -- compare for FTE budget value only
121 PROCEDURE get_fte_event_dates(
122 p_uom IN VARCHAR2
123 ,p_assignment_id IN NUMBER
124 ,p_business_group_id IN NUMBER
125 ,p_effective_date IN DATE
126 ,p_event_dates IN OUT NOCOPY pqp_table_of_dates
127 );
128
129 --
130 -- Calculate_ABV
131 -- Calculate ABV based on seeded GB specific formula
132 -- or using user provided formula
133 PROCEDURE update_value_for_event_dates(
134 p_uom IN VARCHAR2
135 ,p_assignment_id IN NUMBER
136 ,p_business_group_id IN NUMBER
137 ,p_formula_id IN NUMBER
138 ,p_action IN VARCHAR2
139 ,p_effective_date IN DATE
140 );
141
142 -- Update_And_Store_ABV
143 -- Write calculated Budget Value to database
144 PROCEDURE update_and_store_abv(
145 p_uom IN VARCHAR2
146 ,p_assignment_id IN NUMBER
147 ,p_business_group_id IN NUMBER
148 ,p_abv_value IN NUMBER
149 ,p_action IN VARCHAR2
150 ,p_effective_date IN DATE
151 );
152
153 -- Csr_Get_Configuration_Data
154 -- Obtain Configuration data
155 CURSOR csr_get_configuration_data(
156 p_uom VARCHAR2
157 ,p_business_group_id NUMBER
158 ,p_legislation_code VARCHAR2
159 ,p_information_category VARCHAR2
160 )
161 IS
162 SELECT *
163 FROM pqp_configuration_values
164 WHERE pcv_information_category = p_information_category
165 AND pcv_information1 = p_uom
166 AND ( (business_group_id = p_business_group_id)
167 OR ( business_group_id IS NULL
168 AND legislation_code = p_legislation_code
169 )
170 OR (business_group_id IS NULL AND legislation_code IS NULL)
171 )
172 ORDER BY 1 DESC;
173
174 -- Load_Cache
175 -- Obtains the configuration value data returning the data in the order
176 -- Business_Group = P_Business_Group
177 -- Business_Group IS NULL AND Legislation_Code = 'GB'
178 -- Business_Group IS NULL AND Legislation_Code = NULL
179 -- where multiple rows exist
180 PROCEDURE load_cache(
181 p_uom IN VARCHAR2
182 ,p_business_group_id IN NUMBER
183 ,p_legislation_code IN VARCHAR2
184 ,p_information_category IN VARCHAR2
185 ,p_configuration_data IN OUT NOCOPY csr_get_configuration_data%ROWTYPE
186 );
187 --
188 --
189
190 END pqp_budget_maintenance;