[Home] [Help]
PACKAGE: APPS.PAY_US_DB_PER_SETUP
Source
1 PACKAGE pay_us_db_per_setup AUTHID CURRENT_USER AS
2 /* $Header: pyusuelt.pkh 115.2 99/07/17 06:47:19 porting ship $
3 ******************************************************************
4 * *
5 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
6 * Chertsey, England. *
7 * *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation UK Ltd, *
17 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
18 * England. *
19 * *
20 ****************************************************************** */
21 /*
22 Name : pay_us_db_per_setup (HEADER)
23
24 Description : This package declares procedures required to
25 create all 'set up' entities in Personnel for US
26 payroll testing.
27 That is:
28
29 Business Groups,
30 HR Organizations,
31 Legal Companies,
32 Positions,
33 Jobs,
34 Grades,
35 Change List
36 -----------
37 Version Date Author ER/CR No. Description of Change
38 -------+---------+----------+---------+--------------------------
39 ( History of pesutupd.pkh, ie. db_per_setup :-
40 70.0 19-NOV-92 SZWILLIA Date Created
41 70.1 21-DEC-92 SZWILLIA Added Person Building Blocks
42 70.2 04-JAN-93 SZWILLIA Create Applicant added.
43 70.3 11-JAN-93 SZWILLIA Corrected date defaulting
44 70.4 11-JAN-93 SZWILLIA Moved person and assignment
45 procedures to db_per_additional
46 70.5 11-JAN-93 SZWILLIA Changed create_business_group to
47 accept structure names not
48 numbers
49 70.6 04-MAR-93 SZWILLIA Changed parameters to be correct
50 format for DATEs
51 70.7 09-MAR-93 SZWILLIA Made insert_org_information public.
52 70.8 11-MAR-93 NKHAn Added 'exit' to the enD )
53
54 *** 05-AUG-93 us_pesutupd.pkh created, ie. copied from pesutupd.pkh ***
55 40.0 05-AUG-93 MSWANSON Date us_pesutupd.pkh created, ie.
56 copied pesutupd.pkh and
57 altered for US testing.
58 40.1 08-APR-93 AKELLY New parameters added to create_pay_ legal_company
59 to allow insert of 'Federal Tax Rules' and 'Work
60 Schedule' ddf info. Added new procedure
61 create_company_state_rules.
62 40.2 19-APR-94 Add scl dets to create_pay_legal_company.
63 ****
64 40.0 31-MAY-94 MGILMORE Renamed.
65 40.1 03-JUL-94 AROUSSEL Tidyied up for 10G install
66 40.2 01-MAR-95 MSWANSON Add/Change for EEO and VETS100 system
67 test data creation:
68 - create_est_organization,
69 - create_eeo_hierarchy,
70 - create_eeo_hierarchy_version,
71 - create_eeo_hierarchy_element.
72 40.3 28-Sep-95 Akelly Added new functions INSERT_WC_FUND,
73 INSERT_WC_RATE, CREATE_WC_CARRIER and
74 CREATE_US_LOCATION
75 40.4 01-Nov-95 JThuring Removed error checking from end of script
76 115.2 14-May-1999 mmillmor multi radix fix to positions
77 ================================================================= */
78 --
79 --
80 FUNCTION insert_organization_unit
81 ( P_GROUP VARCHAR2
82 ,P_NAME VARCHAR2
83 ,P_BUSINESS_GROUP_ID NUMBER
84 ,P_COST_ALLOCATION_KEYFLEX_ID NUMBER
85 ,P_LOCATION_ID NUMBER
86 ,P_SOFT_CODING_KEYFLEX_ID NUMBER
87 ,P_DATE_FROM DATE
88 ,P_DATE_TO DATE
89 ,P_INTERNAL_EXTERNAL_FLAG VARCHAR2
90 ,P_INTERNAL_ADDRESS_LINE VARCHAR2
91 ,P_TYPE VARCHAR2
92 ) return NUMBER;
93 --
94 --
95 FUNCTION create_business_group
96 ( P_GROUP VARCHAR2 DEFAULT 'Y'
97 ,P_NAME VARCHAR2
98 ,P_DATE_FROM DATE
99 ,P_DATE_TO DATE DEFAULT null
100 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
101 ,P_DEFAULT_START_TIME VARCHAR2 DEFAULT '08:00'
102 ,P_DEFAULT_END_TIME VARCHAR2 DEFAULT '17:30'
103 ,P_WORKING_HOURS VARCHAR2 DEFAULT '37.5'
104 ,P_FREQUENCY VARCHAR2 DEFAULT 'W'
105 ,P_SHORT_NAME VARCHAR2
106 ,P_METHOD_OF_GENERATION_EMP_NUM VARCHAR2 DEFAULT 'A'
107 ,P_METHOD_OF_GENERATION_APL_NUM VARCHAR2 DEFAULT 'A'
108 ,P_GRADE_STRUCTURE VARCHAR2 DEFAULT 'Grade Flexfield'
109 ,P_PEOPLE_GROUP_STRUCTURE VARCHAR2
110 DEFAULT 'People Group Flexfield'
111 ,P_JOB_STRUCTURE VARCHAR2 DEFAULT 'Job Flexfield'
112 ,P_COST_ALLOCATION_STRUCTURE VARCHAR2
113 DEFAULT 'Cost Allocation Flexfield'
114 ,P_POSITION_STRUCTURE VARCHAR2 DEFAULT 'Position Flexfield'
115 ,P_LEGISLATION_CODE VARCHAR2 DEFAULT 'US'
116 ,P_CURRENCY_CODE VARCHAR2 DEFAULT 'USD'
117 ,P_FISCAL_YEAR_START VARCHAR2 DEFAULT null
118 ,P_ASSIGNMENT_STATUS_1 VARCHAR2 DEFAULT null
119 ,P_ASSIGNMENT_STATUS_2 VARCHAR2 DEFAULT null
120 ,P_EMPLOYMENT_CATEGORY_1 VARCHAR2 DEFAULT null
121 ,P_EMPLOYMENT_CATEGORY_2 VARCHAR2 DEFAULT null
122 ) return NUMBER;
123
124 --
125 --
126 FUNCTION create_per_organization
127 ( P_NAME VARCHAR2
128 ,P_BUSINESS_GROUP VARCHAR2
129 ,P_DATE_FROM DATE
130 ,P_DATE_TO DATE DEFAULT null
131 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
132 ,P_DEFAULT_START_TIME VARCHAR2 DEFAULT '08:00'
133 ,P_DEFAULT_END_TIME VARCHAR2 DEFAULT '17:30'
134 ,P_WORKING_HOURS VARCHAR2 DEFAULT '37.5'
135 ,P_FREQUENCY VARCHAR2 DEFAULT 'W'
136 ,P_INTERNAL_EXTERNAL_FLAG VARCHAR2 DEFAULT 'INT'
137 ,P_TYPE VARCHAR2 DEFAULT null
138 ,P_LOCATION_ID NUMBER DEFAULT null
139 ) return NUMBER;
140 --
141 --
142 FUNCTION create_est_organization
143 ( P_NAME VARCHAR2
144 ,P_BUSINESS_GROUP VARCHAR2
145 ,P_DATE_FROM DATE
146 ,P_DATE_TO DATE DEFAULT null
147 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
148 ,P_INTERNAL_EXTERNAL_FLAG VARCHAR2 DEFAULT 'INT'
149 ,P_TYPE VARCHAR2 DEFAULT null
150 ,P_LOCATION_ID NUMBER DEFAULT null
151 ,P_EEO1_UNIT_NUMBER NUMBER DEFAULT null
152 ,P_VETS100_UNIT_NUMBER NUMBER DEFAULT null
153 ,P_REPORTING_NAME VARCHAR2 DEFAULT null
154 ,P_VETS100_REPORTING_NAME VARCHAR2 DEFAULT null
155 ,P_SIC NUMBER DEFAULT null
156 ,P_ACTIVITY_LINE1 VARCHAR2 DEFAULT null
157 ,P_ACTIVITY_LINE2 VARCHAR2 DEFAULT null
158 ,P_ACTIVITY_LINE3 VARCHAR2 DEFAULT null
159 ,P_ACTIVITY_LINE4 VARCHAR2 DEFAULT null
160 ,P_APPRENTICES_EMPLOYED VARCHAR2 DEFAULT null
161 ) return NUMBER;
162 --
163 --
164 FUNCTION create_pay_legal_company
165 ( P_NAME VARCHAR2
166 ,P_BUSINESS_GROUP VARCHAR2
167 ,P_DATE_FROM DATE
168 ,P_DATE_TO DATE DEFAULT null
169 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
170 ,P_LOCATION_ID NUMBER DEFAULT null
171 ,P_COMPANY_FEDERAL_IDENTIFIER VARCHAR2 DEFAULT null
172 ,P_NACHA_COMPANY_NAME VARCHAR2 DEFAULT null
173 ,P_NACHA_IDENTIFIER VARCHAR2 DEFAULT null
174 ,P_NACHA_DISCRETIONARY_CODE VARCHAR2 DEFAULT null
175 ,P_SS_SELF_ADJUST_METHOD VARCHAR2 DEFAULT null
176 ,P_MED_SELF_ADJUST_METHOD VARCHAR2 DEFAULT null
177 ,P_FUTA_SELF_ADJUST_METHOD VARCHAR2 DEFAULT null
178 ,P_TYPE_OF_EMPLOYMENT VARCHAR2 DEFAULT null
179 ,P_TAX_GROUP VARCHAR2 DEFAULT null
180 ,P_SUPPLEMENTAL_CALC_METHOD VARCHAR2 DEFAULT null
181 ,P_WORK_SCHEDULE_TABLE VARCHAR2 DEFAULT 'COMPANY WORK SCHEDULES'
182 ,P_WORK_SCHEDULE_NAME VARCHAR2 DEFAULT null
183 ,P_EEO1_UNIT_NUMBER VARCHAR2 DEFAULT null
184 ,P_VETS100_UNIT_NUMBER NUMBER DEFAULT null
185 ,P_REPORTING_NAME VARCHAR2 DEFAULT null
186 ,P_VETS100_REPORTING_NAME VARCHAR2 DEFAULT null
187 ,P_SIC VARCHAR2 DEFAULT null
188 ,P_ACTIVITY_LINE1 VARCHAR2 DEFAULT null
189 ,P_ACTIVITY_LINE2 VARCHAR2 DEFAULT null
190 ,P_ACTIVITY_LINE3 VARCHAR2 DEFAULT null
191 ,P_ACTIVITY_LINE4 VARCHAR2 DEFAULT null
192 ,P_APPRENTICES_EMPLOYED VARCHAR2 DEFAULT null
193 ,P_EEO1_IDENTIFICATION_NUMBER VARCHAR2 DEFAULT null
194 ,P_VETS100_COMPANY_NUMBER VARCHAR2 DEFAULT null
195 ,P_DUN_AND_BRADSTREET_NUMBER VARCHAR2 DEFAULT null
196 ,P_GRE_REPORTING_NAME VARCHAR2 DEFAULT null
197 ,P_AFFILIATED VARCHAR2 DEFAULT null
198 ,P_GOVERNMENT_CONTRACTOR VARCHAR2 DEFAULT null
199 ,P_ORG_TYPE VARCHAR2 DEFAULT null
200 ) return NUMBER;
201 --
202 --
203 FUNCTION create_eeo_hierarchy
204 (p_hierarchy_name VARCHAR2
205 ,p_business_group_id NUMBER
206 ,p_primary_structure_flag VARCHAR2
207 ) RETURN NUMBER;
208 --
209 --
210 FUNCTION create_eeo_hierarchy_version
211 (p_business_group_id NUMBER
212 ,p_date_from VARCHAR2
213 ,p_organization_structure_id NUMBER
214 ,p_version_number NUMBER
215 ) return NUMBER;
216 --
217 --
218 FUNCTION create_eeo_hierarchy_element
219 (p_business_group_id NUMBER
220 ,p_organization_id_parent NUMBER
221 ,p_org_structure_version_id NUMBER
222 ,p_organization_id_child NUMBER
223 ) RETURN NUMBER;
224 --
225 --
226 PROCEDURE create_company_state_rules
227 (p_legal_company_id IN NUMBER
228 ,p_state_code IN VARCHAR2
229 ,p_sui_company_state_id IN VARCHAR2
230 ,p_sit_company_state_id IN VARCHAR2
231 ,p_sui_self_adjust_method IN VARCHAR2 DEFAULT null
232 ,p_sdi_self_adjust_method IN VARCHAR2 DEFAULT null
233 ,p_sui_er_experience_rate_1 IN VARCHAR2 DEFAULT null
234 ,p_sui_er_experience_rate_2 IN VARCHAR2 DEFAULT null
235 ,p_wc_carrier_name IN VARCHAR2 DEFAULT null
236 ,p_employers_liability_rate IN VARCHAR2 DEFAULT null
237 ,p_experience_modification_rate IN VARCHAR2 DEFAULT null
238 ,p_premium_discount_rate IN VARCHAR2 DEFAULT null
239 );
240 --
241 --
242 FUNCTION create_job
243 (p_default VARCHAR2 DEFAULT 'Y'
244 ,p_name VARCHAR2
245 ,p_business_group VARCHAR2
246 ,p_date_from DATE
247 ,p_date_to DATE DEFAULT null
248 ,p_segment1 VARCHAR2 DEFAULT null
249 ,p_segment2 VARCHAR2 DEFAULT null
250 ,p_segment3 VARCHAR2 DEFAULT null
251 ,p_segment4 VARCHAR2 DEFAULT null
252 ,p_segment5 VARCHAR2 DEFAULT null
253 ,p_segment6 VARCHAR2 DEFAULT null
254 ,p_segment7 VARCHAR2 DEFAULT null
255 ,p_segment8 VARCHAR2 DEFAULT null
256 ,p_segment9 VARCHAR2 DEFAULT null
257 ,p_segment10 VARCHAR2 DEFAULT null
258 ,p_segment11 VARCHAR2 DEFAULT null
259 ,p_segment12 VARCHAR2 DEFAULT null
260 ,p_segment13 VARCHAR2 DEFAULT null
261 ,p_segment14 VARCHAR2 DEFAULT null
262 ,p_segment15 VARCHAR2 DEFAULT null
263 ,p_segment16 VARCHAR2 DEFAULT null
264 ,p_segment17 VARCHAR2 DEFAULT null
265 ,p_segment18 VARCHAR2 DEFAULT null
266 ,p_segment19 VARCHAR2 DEFAULT null
267 ,p_segment20 VARCHAR2 DEFAULT null
268 ,p_segment21 VARCHAR2 DEFAULT null
269 ,p_segment22 VARCHAR2 DEFAULT null
270 ,p_segment23 VARCHAR2 DEFAULT null
271 ,p_segment24 VARCHAR2 DEFAULT null
272 ,p_segment25 VARCHAR2 DEFAULT null
273 ,p_segment26 VARCHAR2 DEFAULT null
274 ,p_segment27 VARCHAR2 DEFAULT null
275 ,p_segment28 VARCHAR2 DEFAULT null
276 ,p_segment29 VARCHAR2 DEFAULT null
277 ,p_segment30 VARCHAR2 DEFAULT null
278 ,p_context VARCHAR2 DEFAULT null
279 ,p_eeo_category VARCHAR2 DEFAULT null
280 ) return NUMBER;
281 --
282 --
283 FUNCTION create_position
284 (p_default VARCHAR2 DEFAULT 'Y'
285 ,p_name VARCHAR2
286 ,p_business_group VARCHAR2
287 ,p_date_effective DATE
288 ,p_date_end DATE DEFAULT null
289 ,p_job VARCHAR2
290 ,p_organization VARCHAR2
291 ,p_location VARCHAR2 DEFAULT null
292 ,p_time_normal_start VARCHAR2 DEFAULT '08:00'
293 ,p_time_normal_finish VARCHAR2 DEFAULT '17:30'
294 ,p_working_hours NUMBER DEFAULT 37.5
295 ,p_frequency VARCHAR2 DEFAULT 'W'
296 ,p_probation_period VARCHAR2 DEFAULT null
297 ,p_probation_units VARCHAR2 DEFAULT null
298 ,p_relief_position VARCHAR2 DEFAULT null
299 ,p_replacement_required VARCHAR2 DEFAULT 'N'
300 ,p_successor_position VARCHAR2 DEFAULT null
301 ,p_segment1 VARCHAR2 DEFAULT null
302 ,p_segment2 VARCHAR2 DEFAULT null
303 ,p_segment3 VARCHAR2 DEFAULT null
304 ,p_segment4 VARCHAR2 DEFAULT null
305 ,p_segment5 VARCHAR2 DEFAULT null
306 ,p_segment6 VARCHAR2 DEFAULT null
307 ,p_segment7 VARCHAR2 DEFAULT null
311 ,p_segment11 VARCHAR2 DEFAULT null
308 ,p_segment8 VARCHAR2 DEFAULT null
309 ,p_segment9 VARCHAR2 DEFAULT null
310 ,p_segment10 VARCHAR2 DEFAULT null
312 ,p_segment12 VARCHAR2 DEFAULT null
313 ,p_segment13 VARCHAR2 DEFAULT null
314 ,p_segment14 VARCHAR2 DEFAULT null
315 ,p_segment15 VARCHAR2 DEFAULT null
316 ,p_segment16 VARCHAR2 DEFAULT null
317 ,p_segment17 VARCHAR2 DEFAULT null
318 ,p_segment18 VARCHAR2 DEFAULT null
319 ,p_segment19 VARCHAR2 DEFAULT null
320 ,p_segment20 VARCHAR2 DEFAULT null
321 ,p_segment21 VARCHAR2 DEFAULT null
322 ,p_segment22 VARCHAR2 DEFAULT null
323 ,p_segment23 VARCHAR2 DEFAULT null
324 ,p_segment24 VARCHAR2 DEFAULT null
325 ,p_segment25 VARCHAR2 DEFAULT null
326 ,p_segment26 VARCHAR2 DEFAULT null
327 ,p_segment27 VARCHAR2 DEFAULT null
328 ,p_segment28 VARCHAR2 DEFAULT null
329 ,p_segment29 VARCHAR2 DEFAULT null
330 ,p_segment30 VARCHAR2 DEFAULT null
331 ,p_comments LONG DEFAULT NULL
332 ) return NUMBER ;
333 --
334 --
335 FUNCTION create_grade
336 (p_default VARCHAR2 DEFAULT 'Y'
337 ,p_name VARCHAR2
338 ,p_business_group VARCHAR2
339 ,p_date_from DATE
340 ,p_date_to DATE DEFAULT null
341 ,p_sequence VARCHAR2 DEFAULT null
342 ,p_segment1 VARCHAR2 DEFAULT null
343 ,p_segment2 VARCHAR2 DEFAULT null
344 ,p_segment3 VARCHAR2 DEFAULT null
345 ,p_segment4 VARCHAR2 DEFAULT null
346 ,p_segment5 VARCHAR2 DEFAULT null
347 ,p_segment6 VARCHAR2 DEFAULT null
348 ,p_segment7 VARCHAR2 DEFAULT null
349 ,p_segment8 VARCHAR2 DEFAULT null
350 ,p_segment9 VARCHAR2 DEFAULT null
351 ,p_segment10 VARCHAR2 DEFAULT null
352 ,p_segment11 VARCHAR2 DEFAULT null
353 ,p_segment12 VARCHAR2 DEFAULT null
354 ,p_segment13 VARCHAR2 DEFAULT null
355 ,p_segment14 VARCHAR2 DEFAULT null
356 ,p_segment15 VARCHAR2 DEFAULT null
357 ,p_segment16 VARCHAR2 DEFAULT null
358 ,p_segment17 VARCHAR2 DEFAULT null
359 ,p_segment18 VARCHAR2 DEFAULT null
360 ,p_segment19 VARCHAR2 DEFAULT null
361 ,p_segment20 VARCHAR2 DEFAULT null
362 ,p_segment21 VARCHAR2 DEFAULT null
363 ,p_segment22 VARCHAR2 DEFAULT null
364 ,p_segment23 VARCHAR2 DEFAULT null
365 ,p_segment24 VARCHAR2 DEFAULT null
366 ,p_segment25 VARCHAR2 DEFAULT null
367 ,p_segment26 VARCHAR2 DEFAULT null
368 ,p_segment27 VARCHAR2 DEFAULT null
369 ,p_segment28 VARCHAR2 DEFAULT null
370 ,p_segment29 VARCHAR2 DEFAULT null
371 ,p_segment30 VARCHAR2 DEFAULT null
372 ) return NUMBER;
373 --
374 FUNCTION INSERT_WC_FUND( P_BUSINESS_GROUP_ID NUMBER,
375 P_CARRIER_ID NUMBER,
376 P_LOCATION_ID NUMBER DEFAULT NULL,
377 P_STATE_CODE VARCHAR2) return NUMBER;
378 --
379 FUNCTION insert_wc_rate
380 (p_fund_id NUMBER
381 ,p_business_group_id NUMBER
382 ,p_rate NUMBER
383 ) return NUMBER;
384 --
385 FUNCTION create_us_location
386 ( p_location_code VARCHAR2
387 ,p_address_line_1 VARCHAR2
388 ,p_address_line_2 VARCHAR2 default null
389 ,p_address_line_3 VARCHAR2 default null
390 ,p_town_or_city VARCHAR2 default null
391 ,p_county VARCHAR2 default null
392 ,p_state VARCHAR2 default null
393 ,p_zip_code VARCHAR2 default null
394 ,p_telephone VARCHAR2 default null
395 ,p_fax VARCHAR2 default null
396 ) return NUMBER;
397 --
398 FUNCTION create_wc_carrier
399 ( P_NAME VARCHAR2
400 ,P_BUSINESS_GROUP VARCHAR2
401 ,P_DATE_FROM DATE
402 ,P_DATE_TO DATE DEFAULT null
403 ,P_INTERNAL_ADDRESS_LINE VARCHAR2 DEFAULT null
404 ,P_INTERNAL_EXTERNAL_FLAG VARCHAR2 DEFAULT 'INT'
405 ,P_TYPE VARCHAR2 DEFAULT null
406 ,P_LOCATION_ID NUMBER DEFAULT null
407 ) return NUMBER;
408 --
409 --
410 end pay_us_db_per_setup;