1 PACKAGE pqp_gb_psi_hrs_history
2 -- /* $Header: pqpgbpsihrs.pkh 120.0.12000000.2 2007/02/13 13:52:25 mseshadr noship $ */
3 AUTHID CURRENT_USER AS
4 --
5 -- Debug Variables.
6 --
7 g_proc_name VARCHAR2(61) := 'pqp_gb_psi_hrs_history.';
8 g_legislation_code per_business_groups.legislation_code%TYPE := 'GB';
9 g_debug BOOLEAN := hr_utility.debug_enabled;
10 g_business_group_id NUMBER;
11 g_effective_date DATE;
12 g_extract_type VARCHAR2(100);
13 g_cutover_date DATE;
14 g_ext_dfn_id NUMBER;
15 g_paypoint VARCHAR2(10);
16 g_active_asg_sts_id NUMBER;
17 g_terminate_asg_sts_id NUMBER;
18 g_event_counter NUMBER;
19 g_assignment_id NUMBER;
20 g_nested_level NUMBER(5) := pqp_utilities.g_nested_level;
21 g_person_id NUMBER;
22 g_start_date DATE;
23 g_end_date DATE;
24 g_ft_hours NUMBER;
25 g_pt_hours NUMBER;
26 g_hours_type VARCHAR2(10);
27
28 TYPE r_element_details IS RECORD(
29 element_type_id NUMBER
30 ,input_value_name pay_input_values_f.NAME%TYPE
31 ,input_value_id NUMBER
32 );
33
34 TYPE r_ele_ent_details IS RECORD(
35 element_entry_id NUMBER
36 ,effective_start_date DATE
37 ,effective_end_date DATE
38 ,element_type_id NUMBER
39 );
40
41 TYPE r_asg_details IS RECORD(
42 person_id NUMBER
43 ,effective_start_date DATE
44 ,effective_end_date DATE
45 ,assignment_number per_all_assignments_f.assignment_number%TYPE
46 ,primary_flag per_all_assignments_f.primary_flag%TYPE
47 ,normal_hours per_all_assignments_f.normal_hours%TYPE
48 ,assignment_status_type_id NUMBER
49 ,employment_category per_all_assignments_f.employment_category%TYPE
50 );
51
52 TYPE r_lookup_code IS RECORD(
53 lookup_code hr_lookups.lookup_code%TYPE
54 ,meaning hr_lookups.meaning%TYPE
55 );
56
57 -- cursor to fetch dated table info
58 CURSOR csr_get_dated_table_info(c_table_name VARCHAR2)
59 IS
60 SELECT dated_table_id, table_name, surrogate_key_name
61 FROM pay_dated_tables
62 WHERE table_name = c_table_name;
63
64 TYPE t_dated_table IS TABLE OF csr_get_dated_table_info%ROWTYPE
65 INDEX BY BINARY_INTEGER;
66
67 -- Cursor to get event group info
68 CURSOR csr_get_event_group_info(c_event_group VARCHAR2)
69 IS
70 SELECT event_group_id, event_group_name, event_group_type
71 FROM pay_event_groups
72 WHERE event_group_name = c_event_group
73 AND (
74 (business_group_id = g_business_group_id)
75 OR (
76 business_group_id IS NULL
77 AND (
78 legislation_code IS NULL
79 OR legislation_code = g_legislation_code
80 )
81 )
82 );
83
84 TYPE t_event_group IS TABLE OF csr_get_event_group_info%ROWTYPE
85 INDEX BY BINARY_INTEGER;
86
87 -- TYPE t_config_values IS TABLE OF r_config_values
88 -- INDEX BY BINARY_INTEGER;
89
90 TYPE t_number IS TABLE OF NUMBER
91 INDEX BY BINARY_INTEGER;
92
93 TYPE t_element_details IS TABLE OF r_element_details
94 INDEX BY BINARY_INTEGER;
95
96 TYPE t_varchar2 IS TABLE OF VARCHAR2(150)
97 INDEX BY BINARY_INTEGER;
98
99 TYPE t_lookups IS TABLE OF r_lookup_code
100 INDEX BY BINARY_INTEGER;
101
102 g_tab_event_map_cv pqp_utilities.t_config_values;
103 g_tab_pen_sch_map_cv pqp_utilities.t_config_values;
104 g_tab_emp_typ_map_cv pqp_utilities.t_config_values;
105 g_tab_asg_status t_number;
106 g_tab_pen_ele_ids t_element_details;
107 g_tab_prs_dfn_cv pqp_utilities.t_config_values;
108 g_tab_event_desc_lov t_lookups;
109 g_person_dtl per_all_people_f%ROWTYPE;
110 g_assignment_dtl per_all_assignments_f%ROWTYPE;
111 g_tab_dated_table t_dated_table;
112 g_tab_pay_proc_evnts ben_ext_person.t_detailed_output_table;
113 g_tab_event_group t_event_group;
114 g_prev_pay_proc_evnts ben_ext_person.t_detailed_output_tab_rec;
115
116 -- cursor to fetch the asg status type id details
117 CURSOR csr_get_asg_sts_dtls(c_per_system_status VARCHAR2)
118 IS
119 SELECT assignment_status_type_id, default_flag, active_flag, primary_flag
120 ,user_status, pay_system_status, per_system_status
121 FROM per_assignment_status_types
122 WHERE per_system_status = c_per_system_status
123 AND (
124 (business_group_id = g_business_group_id)
125 OR (
126 business_group_id IS NULL
127 AND (
128 legislation_code IS NULL
129 OR legislation_code = g_legislation_code
130 )
131 )
132 );
133
134 -- Cursor to get abv details
135 CURSOR csr_abv_dtls (c_assignment_id NUMBER
136 ,c_effective_date DATE
137 )
138 IS
139 SELECT assignment_budget_value_id
140 ,assignment_id
141 ,effective_start_date
142 ,effective_end_date
143 ,unit
144 ,value
145 FROM per_assignment_budget_values_f
146 WHERE assignment_id = c_assignment_id
147 AND unit = 'FTE'
148 AND c_effective_date BETWEEN effective_start_date
149 AND effective_end_date;
150
151 -- Debug
152 PROCEDURE DEBUG(
153 p_trace_message IN VARCHAR2
154 ,p_trace_location IN NUMBER DEFAULT NULL
155 );
156
157 -- Debug_Enter
158 PROCEDURE debug_enter(
159 p_proc_name IN VARCHAR2
160 ,p_trace_on IN VARCHAR2 DEFAULT NULL
161 );
162
163 -- Debug_Exit
164 PROCEDURE debug_exit(
165 p_proc_name IN VARCHAR2
166 ,p_trace_off IN VARCHAR2 DEFAULT NULL
167 );
168
169 -- Debug Others
170 PROCEDURE debug_others(
171 p_proc_name IN VARCHAR2
172 ,p_proc_step IN NUMBER DEFAULT NULL
173 );
174
175 -- Part Time Hours History Cutover Criteria
176 FUNCTION chk_hrs_cutover_criteria(
177 p_business_group_id IN NUMBER
178 ,p_effective_date IN DATE
179 ,p_assignment_id IN NUMBER
180 )
181 RETURN VARCHAR2;
182
183 -- Part Time Hours History Periodic Criteria
184 FUNCTION chk_hrs_periodic_criteria(
185 p_business_group_id IN NUMBER
186 ,p_effective_date IN DATE
187 ,p_assignment_id IN NUMBER
188 )
189 RETURN VARCHAR2;
190
191 -- Part Time Hours History Data
192 FUNCTION get_hrs_history_data(
193 p_business_group_id IN NUMBER
194 ,p_effective_date IN DATE
195 ,p_assignment_id IN NUMBER
196 ,p_rule_parameter IN VARCHAR2
197 )
198 RETURN VARCHAR2;
199
200 -- Part Time Hours History Post Process
201 FUNCTION hrs_history_post_process(p_ext_rslt_id IN NUMBER)
202 RETURN VARCHAR2;
203 END pqp_gb_psi_hrs_history;
[Home] [Help]