[Home] [Help]
PACKAGE: APPS.PAY_HR_OTC_RETRIEVAL_INTERFACE
Source
1 PACKAGE pay_hr_otc_retrieval_interface AUTHID CURRENT_USER AS
2 /* $Header: pytshpri.pkh 120.5.12020000.2 2012/07/06 11:59:08 asrajago ship $ */
3 SUBTYPE max_varchar IS VARCHAR2 (4000);
4
5 SUBTYPE flag_varchar IS VARCHAR2 (1);
6
7 SUBTYPE proc_name IS VARCHAR2 (72);
8
9 SUBTYPE package_name IS VARCHAR2 (32);
10
11 TYPE batches_type_rec IS RECORD (
12 batch_id pay_batch_headers.batch_id%TYPE,
13 business_group_id pay_batch_headers.business_group_id%TYPE,
14 batch_reference pay_batch_headers.batch_reference%TYPE,
15 batch_name pay_batch_headers.batch_name%TYPE
16 );
17
18 -- Bug 12919783
19 TYPE NUMTABLE IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
20 g_batch_tab NUMTABLE;
21
22 -- Bug 13777315
23 g_IV_upgrade VARCHAR2(30);
24 g_IV_format VARCHAR2(30);
25
26
27 TYPE batches_type_table IS TABLE OF batches_type_rec
28 INDEX BY BINARY_INTEGER;
29
30 -- Bug 10323310
31 -- R12.2 Projects Payroll Integration
32 -- New Data types.
33
34 -- Type and variable used to record the details.
35 TYPE PAY_PA_LINK_TAB IS TABLE OF hxc_pay_pa_link_all%ROWTYPE INDEX BY BINARY_INTEGER;
36
37 g_pay_pa_link_tab PAY_PA_LINK_TAB;
38 g_old_pay_pa_link_tab PAY_PA_LINK_TAB;
39
40
41 -- Type to store the preference details for this Integration.
42 TYPE PREF_REC IS RECORD
43 (
44 intg_enabled VARCHAR2(5),
45 date_start DATE,
46 date_end DATE
47 );
48
49 TYPE PREF_TAB IS TABLE OF PREF_REC;
50
51 -- Type of TABLE of preferences.
52
53 TYPE EMP_PREF_TAB IS TABLE OF PREF_TAB INDEX BY BINARY_INTEGER;
54
55 g_emp_pref_tab EMP_PREF_TAB;
56
57 -- Global variables to store the start and end dates if any.
58 g_start_date DATE;
59 g_end_date DATE;
60
61
62
63 g_inclusive CONSTANT VARCHAR2 (2) := 'IN';
64 g_bee_retrieval_process CONSTANT hxc_retrieval_processes.NAME%TYPE
65 := 'BEE Retrieval Process';
66 g_insert_if_exist CONSTANT pay_batch_headers.action_if_exists%TYPE
67 := 'I';
68 g_time_store_batch_source CONSTANT pay_batch_headers.batch_source%TYPE
69 := 'Time Store';
70 g_max_message_size CONSTANT PLS_INTEGER := 2000;
71 g_trx_success CONSTANT hxc_transactions.status%TYPE
72 := 'SUCCESS';
73 g_trx_error CONSTANT hxc_transactions.status%TYPE
74 := 'ERRORS';
75 g_hxc_app_short_name CONSTANT fnd_application.application_short_name%TYPE
76 := 'HXC';
77 g_trx_detail_success_msg CONSTANT fnd_new_messages.message_name%TYPE
78 := 'HXC_HRPAY_RET_BEE_SUCCESS';
79 g_trx_success_msg CONSTANT fnd_new_messages.message_name%TYPE
80 := 'HXC_HRPAY_RET_PROC_SUCCESS';
81
82 FUNCTION retro_batch_suffix
83 RETURN VARCHAR2;
84
85 PROCEDURE set_retro_batch_suffix (p_retro_batch_suffix IN VARCHAR2);
86
87 PROCEDURE record_batch_info (p_batch_rec IN batches_type_rec);
88
89 PROCEDURE record_batch_info (
90 p_batch_id IN pay_batch_headers.batch_id%TYPE,
91 p_business_group_id IN pay_batch_headers.business_group_id%TYPE,
92 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
93 p_batch_name IN pay_batch_headers.batch_name%TYPE
94 );
95
96 FUNCTION batches_created
97 RETURN batches_type_table;
98
99 FUNCTION where_clause (
100 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
101 p_location_id IN per_all_assignments_f.location_id%TYPE,
102 p_payroll_id IN per_all_assignments_f.payroll_id%TYPE,
103 p_organization_id IN per_all_assignments_f.organization_id%TYPE,
104 p_person_id IN per_all_people_f.person_id%TYPE,
105 p_gre_id IN hr_soft_coding_keyflex.segment1%TYPE
106 )
107 RETURN VARCHAR2;
108
109 PROCEDURE process_otlr_employees (
110 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
111 p_session_date IN DATE,
112 p_start_date IN VARCHAR2, --hxc_time_building_blocks.start_time%TYPE,
113 p_end_date IN VARCHAR2, --hxc_time_building_blocks.stop_time%TYPE,
114 p_where_clause IN hxt_interface_utilities.max_varchar,
115 p_retrieval_transaction_code IN hxc_transactions.transaction_code%TYPE,
116 p_batch_ref IN pay_batch_headers.batch_reference%TYPE,
117 p_unique_params IN hxt_interface_utilities.max_varchar,
118 p_incremental IN hxt_interface_utilities.flag_varchar
119 DEFAULT 'Y', -- We don't allow this anymore so set to Y always
120 p_transfer_to_bee IN hxt_interface_utilities.flag_varchar
121 DEFAULT 'N', -- We don't allow this anymore so set to N always
122 p_no_otm IN OUT NOCOPY hxt_interface_utilities.flag_varchar
123 );
124
125 PROCEDURE extract_data_from_attr_tbl (
126 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
127 p_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
128 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
129 p_det_tbb_idx IN PLS_INTEGER,
130 p_cost_flex_id IN per_business_groups_perf.cost_allocation_structure%TYPE,
131 p_effective_date IN pay_element_types_f.effective_start_date%TYPE,
132 p_attr_tbl_idx IN OUT NOCOPY PLS_INTEGER,
133 p_bee_rec IN OUT NOCOPY hxt_interface_utilities.bee_rec,
134 p_is_old IN BOOLEAN DEFAULT FALSE
135 );
136
137 PROCEDURE bee_batch_line (
138 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
139 p_tbb_rec IN hxc_generic_retrieval_pkg.r_building_blocks,
140 p_det_tbb_idx IN PLS_INTEGER,
141 p_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
142 p_attr_tbl_idx IN OUT NOCOPY PLS_INTEGER,
143 p_bee_rec OUT NOCOPY hxt_interface_utilities.bee_rec,
144 p_cost_flex_id IN per_business_groups_perf.cost_allocation_structure%TYPE,
145 p_is_old IN BOOLEAN DEFAULT FALSE
146 );
147
148 FUNCTION batch_name (
149 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
150 p_bg_id IN pay_batch_headers.business_group_id%TYPE
151 )
152 RETURN pay_batch_headers.batch_name%TYPE;
153
154 FUNCTION create_batch_header (
155 p_batch_name IN pay_batch_headers.batch_name%TYPE,
156 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
157 p_batch_source IN pay_batch_headers.batch_source%TYPE
158 DEFAULT g_time_store_batch_source,
159 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
160 p_session_date IN DATE,
161 p_det_tbb_idx IN PLS_INTEGER
162 )
163 RETURN pay_batch_headers.batch_id%TYPE;
164
165 FUNCTION create_batch_header (
166 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
167 p_batch_source IN pay_batch_headers.batch_source%TYPE
168 DEFAULT g_time_store_batch_source,
169 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
170 p_session_date IN DATE,
171 p_det_tbb_idx IN PLS_INTEGER
172 )
173 RETURN pay_batch_headers.batch_id%TYPE;
174
175 -- Bug 9494444
176 -- Added new parameter for marking retro lines.
177 PROCEDURE add_to_batch (
178 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
179 p_batch_id IN OUT NOCOPY pay_batch_headers.batch_id%TYPE,
180 p_det_tbb_idx IN PLS_INTEGER,
181 p_batch_sequence IN OUT NOCOPY pay_batch_lines.batch_sequence%TYPE,
182 p_batch_lines IN OUT NOCOPY PLS_INTEGER,
183 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
184 p_session_date IN DATE,
185 p_effective_date IN DATE,
186 p_bee_rec IN hxt_interface_utilities.bee_rec,
187 p_is_retro IN BOOLEAN DEFAULT FALSE
188 );
189
190 PROCEDURE transfer_to_hr_payroll (
191 errbuf OUT NOCOPY VARCHAR2,
192 retcode OUT NOCOPY NUMBER,
193 p_bg_id IN NUMBER,
194 p_session_date IN VARCHAR2,
195 p_start_date IN VARCHAR2,
196 p_end_date IN VARCHAR2,
197 p_start_batch_id IN NUMBER DEFAULT NULL,
198 p_end_batch_id IN NUMBER DEFAULT NULL,
199 p_gre_id IN NUMBER DEFAULT NULL,
200 p_organization_id IN NUMBER DEFAULT NULL,
201 p_location_id IN NUMBER DEFAULT NULL,
202 p_payroll_id IN NUMBER DEFAULT NULL,
203 p_person_id IN NUMBER DEFAULT NULL,
204 p_retrieval_transaction_code IN VARCHAR2,
205 p_batch_selection IN VARCHAR2 DEFAULT NULL,
206 p_is_old IN VARCHAR2 DEFAULT NULL,
207 p_old_batch_ref IN VARCHAR2 DEFAULT NULL,
208 p_new_batch_ref IN VARCHAR2 DEFAULT NULL,
209 p_new_specified IN VARCHAR2 DEFAULT NULL,
210 p_status_in_bee IN VARCHAR2,
211 p_otlr_to_bee IN VARCHAR2,
212 p_since_date IN VARCHAR2
213 );
214
215 PROCEDURE make_adjustments_bee(p_batch_ref IN VARCHAR2,
216 p_bg_id IN NUMBER,
217 p_session_date IN DATE
218 ) ;
219
220
221 PROCEDURE make_adjustments_otm( p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
222 p_batch_ref IN VARCHAR2) ;
223
224 TYPE r_bb_details IS
225 RECORD (
226 bb_id NUMBER,
227 ovn NUMBER,
228 type VARCHAR2(30),
229 measure NUMBER,
230 start_time DATE,
231 stop_time DATE,
232 parent_bb_id NUMBER,
233 scope VARCHAR2(30),
234 resource_type VARCHAR2(30),
235 comment_text VARCHAR2(2000),
236 uom VARCHAR2(30),
237 changed VARCHAR2(1),
238 deleted VARCHAR2(1)
239 );
240
241 TYPE r_attr_info IS
242 RECORD (
243 attribute_category VARCHAR2(30),
244 attribute1 VARCHAR2(150),
245 attribute2 VARCHAR2(150),
246 attribute3 VARCHAR2(150),
247 attribute4 VARCHAR2(150),
248 attribute5 VARCHAR2(150),
249 attribute6 VARCHAR2(150),
250 attribute7 VARCHAR2(150),
251 attribute8 VARCHAR2(150),
252 attribute9 VARCHAR2(150),
253 attribute10 VARCHAR2(150),
254 attribute11 VARCHAR2(150),
255 attribute12 VARCHAR2(150),
256 attribute13 VARCHAR2(150),
257 attribute14 VARCHAR2(150),
258 attribute15 VARCHAR2(150),
259 attribute16 VARCHAR2(150),
260 attribute17 VARCHAR2(150),
261 attribute18 VARCHAR2(150),
262 attribute19 VARCHAR2(150),
263 attribute20 VARCHAR2(150),
264 attribute21 VARCHAR2(150),
265 attribute22 VARCHAR2(150),
266 attribute23 VARCHAR2(150),
267 attribute24 VARCHAR2(150),
268 attribute25 VARCHAR2(150),
269 attribute26 VARCHAR2(150),
270 attribute27 VARCHAR2(150),
271 attribute28 VARCHAR2(150),
272 attribute29 VARCHAR2(150),
273 attribute30 VARCHAR2(150),
274 bb_id NUMBER,
275 bb_ovn NUMBER,
276 bld_blk_info_type_id NUMBER
277 );
278
279 TYPE table_attr_info IS TABLE OF r_attr_info;
280 TYPE table_bb_details IS TABLE OF r_bb_details;
281
282
283 t_attr_info table_attr_info;
284 t_bb_details table_bb_details;
285 t_detail_blocks hxc_generic_retrieval_pkg.t_building_blocks;
286 t_dtl_attributes hxc_generic_retrieval_pkg.t_time_attribute;
287
288
289 -- Bug 10323310
290 -- R12.2 Projects Payroll Integration
291 -- New procedures.
292
293 PROCEDURE record_pay_pa_details ( p_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
294 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
295 p_det_tbb_idx IN PLS_INTEGER,
296 p_is_old IN BOOLEAN DEFAULT FALSE );
297
298 PROCEDURE cache_preferences ( p_resource_id IN NUMBER,
299 p_start_date IN DATE,
300 p_end_date IN DATE);
301
302 FUNCTION chk_intg_enabled(p_resource_id IN NUMBER,
303 p_date IN DATE)
304 RETURN BOOLEAN;
305
306
307 -- Bug 12919783
308 PROCEDURE mark_batches_for_rollback;
309
310 END pay_hr_otc_retrieval_interface;