[Home] [Help]
PACKAGE BODY: APPS.PAY_DB_LOCALISATION_PKG
Source
1 package body pay_db_localisation_pkg as
2 /* $Header: pylocaln.pkb 115.3 99/09/06 08:20:51 porting ship $ */
3 --
4 /*
5 /*
6 ******************************************************************
7 * *
8 * Copyright (C) 1989 Oracle Corporation UK Ltd., *
9 * Richmond, England. *
10 * *
11 * All rights reserved. *
12 * *
13 * This material has been provided pursuant to an agreement *
14 * containing restrictions on its use. The material is also *
15 * protected by copyright law. No part of this material may *
16 * be copied or distributed, transmitted or transcribed, in *
17 * any form or by any means, electronic, mechanical, magnetic, *
18 * manual, or otherwise, or disclosed to third parties without *
19 * the express written permission of Oracle Corporation UK Ltd, *
20 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
21 * England. *
22 * *
23 ******************************************************************
24 --
25 Name : pay_db_localisation_pkg
26
27 Description :
28
29 Uses : n/a
30 Used By : n/a
31
32 Test List
33 ---------
34 Procedure Name Date Test Id Status
35 +----------------------------+----------+-----------+-------+--------------+
36 +----------------------------+----------+-----------+-------+--------------+
37 --
38 Change List
39 -----------
40 Date Name Vers Bug No Description
41 ---- ---- ---- ------ -----------
42 26-Nov-92 J.S.Hobbs 3.0 First Created.
43 20-Jan-93 J.S.Hobbs 3.1 brought uo to new standards.
44 16-Feb-93 J.S.Hobbs 3.2 Added create_BF_localisation
45 16-Mar-93 J.S.Hobbs 3.3 Added create_payments_balance.
46 01-Apr-93 J.S.Hobbs 3.4 Tidied up package.
47 17-JUN-93 A.McGhee 3.5 Removed FORMULA_ID column
48 from pay_payment_types
49 insert.
50 04-AUG-93 J.S.Hobbs 3.6 Added extra column for
51 inserting into element
52 classifications ie.
53 COSTABLE_FLAG.
54 05-Aug-93 J.S.Hobbs 3.7 Chnaged insert into
55 PER_ASSIGNMENT_INFO_TYPES so that
56 ACTIVE_INACTIVE_FLAG is set
57 correctly.
58 16-Sep-93 M Kaddir 4.1 Removed SQL which creates
59 PAY VALUE translation
60 29-Sep-93 J.S.Hobbs 4.4 / B203 Removed
61 3.18 create_name_translations as
62 this table has been dropped
63 and replaced with a lookup
64 type.
65 29-SEP-93 M. Callaghan 4.5 Not null column:
66 assignment_remuneration_flag
67 added for pay_balance_types.
68 03-NOV-93 C.Swan 4.6 Added setting of
69 MULTIPLE_OCCURENCES_FLAG on
70 insertion of
71 PER_ASSIGNMENT_INFO_TYPES.
72 05-OCT-94 R.Fine 40.10 Renamed package to
73 pay_db_localisation_pkg
74 21-OCT-94 R.Fine 40.11 Changed calls to renamed
75 packages:
76 pyautogn is now pay_autogn;
77 pygbatgn is now pay_gbatgn.
78 21-Apr-99 A.Mills 115.2 875795 Changed legislation rule_type
79 'L' rule_modes to DD/MM
80 format.
81 06-Sept-99 sbilling 115.3 Changed call to insert_row()
82 */
83 --
84 ---------------------------- create_payments_balance ------------------------
85 /*
86 NAME
87 create_payments_balance
88 DESCRIPTION
89 Creates a payments balance for a legislation. This is to be used in the
90 creation of organization payment methods.
91 NOTES
92 */
93 --
94 PROCEDURE create_payments_balance(p_legislation_code varchar2) is
95 --
96 balance_type number;
97 balance_dimension number;
98 v_rowid varchar2(100);
99 --
100 begin
101 --
102 -- Get the id of the payments dimension for the legislation.
103 select bd.balance_dimension_id
104 into balance_dimension
105 from pay_balance_dimensions bd
106 where bd.legislation_code = p_legislation_code
107 and upper(bd.dimension_name) = upper('Payments');
108 --
109 --
110 pay_balance_types_pkg.insert_row(
111 X_ROWID => v_rowid,
112 X_BALANCE_TYPE_ID => balance_type,
113 X_BUSINESS_GROUP_ID => NULL,
114 X_LEGISLATION_CODE => p_legislation_code,
115 X_CURRENCY_CODE => 'GBP',
116 X_ASSIGNMENT_REMUNERATION_FLAG => 'Y',
117 X_BALANCE_NAME => p_legislation_code || ' Payments Balance',
118 -- --
119 X_BASE_BALANCE_NAME => p_legislation_code || ' Payments Balance',
120 -- --
121 X_BALANCE_UOM => 'M',
122 X_COMMENTS => NULL,
123 X_LEGISLATION_SUBGROUP => NULL,
124 X_REPORTING_NAME => NULL,
125 X_ATTRIBUTE_CATEGORY => NULL,
126 X_ATTRIBUTE1 => NULL,
127 X_ATTRIBUTE2 => NULL,
128 X_ATTRIBUTE3 => NULL,
129 X_ATTRIBUTE4 => NULL,
130 X_ATTRIBUTE5 => NULL,
131 X_ATTRIBUTE6 => NULL,
132 X_ATTRIBUTE7 => NULL,
133 X_ATTRIBUTE8 => NULL,
134 X_ATTRIBUTE9 => NULL,
135 X_ATTRIBUTE10 => NULL,
136 X_ATTRIBUTE11 => NULL,
137 X_ATTRIBUTE12 => NULL,
138 X_ATTRIBUTE13 => NULL,
139 X_ATTRIBUTE14 => NULL,
143 X_ATTRIBUTE18 => NULL,
140 X_ATTRIBUTE15 => NULL,
141 X_ATTRIBUTE16 => NULL,
142 X_ATTRIBUTE17 => NULL,
144 X_ATTRIBUTE19 => NULL,
145 X_ATTRIBUTE20 => NULL
146 );
147 --
148 -- Create a defined balance for the legislation using the payments
149 -- dimension.
150 insert into pay_defined_balances
151 (DEFINED_BALANCE_ID,
152 BALANCE_TYPE_ID,
153 BALANCE_DIMENSION_ID,
154 FORCE_LATEST_BALANCE_FLAG,
155 LEGISLATION_CODE)
156 select
157 pay_defined_balances_s.nextval,
158 balance_type,
159 balance_dimension,
160 'Y',
161 p_legislation_code
162 from sys.dual;
163 --
164 END create_payments_balance;
165 --
166 --------------------------- create_BF_localisation ---------------------------
167 /*
168 NAME
169 DESCRIPTION
170 NOTES
171 */
172 --
173 PROCEDURE create_BF_localisation is
174 --
175 begin
176 --
177 -- +=====================================================================+
178 -- | Insert leg rules: |
179 -- +=====================================================================+
180 --
181 -- Time periods are not independent.
182 insert into pay_legislation_rules
183 (LEGISLATION_CODE, RULE_TYPE, RULE_MODE)
184 values
185 ('BF', 'I', 'N');
186 --
187 -- The legislative start date is 6th April.
188 insert into pay_legislation_rules
189 (LEGISLATION_CODE, RULE_TYPE, RULE_MODE)
190 values
191 ('BF', 'L', '06/04');
192 --
193 -- +==================================================================+
194 -- | Insert Element Classfications (no building blocks yet !) |
195 -- +==================================================================+
196 --
197 -- This section creates the typical set of startup element
198 -- classifications for the UK. The primary classifications
199 -- created reflect those in R9:
200 --
201 -- Non Payment
202 -- Direct Payment
203 -- Earnings
204 -- Employer Charges
205 -- Pre-Tax Deductions
206 -- Tax Deductions
207 -- Voluntary Deductions
208 --
209 -- Further to this, the following sub classifications are
210 -- added for the Earnings primary classification:
211 --
212 -- PAYE
213 -- NI Employee
214 -- NI Employer
215 --
216 --
217 declare
218 class_id number;
219 earnings_class number;
220 function do_insert(l_classification_name varchar2,
221 l_description varchar2,
222 l_costing_debit_or_credit varchar2,
223 l_default_high_priority number,
224 l_default_low_priority number,
225 l_default_priority number,
226 l_distributable_over_flag varchar2,
230 new_id number;
227 l_non_payments_flag varchar2,
228 l_parent_id number,
229 l_costable_flag varchar2) return number is
231 begin
232 insert into pay_element_classifications
233 (classification_id,
234 business_group_id,
235 legislation_code,
236 classification_name,
237 description,
238 legislation_subgroup,
239 costing_debit_or_credit,
240 default_high_priority,
241 default_low_priority,
242 default_priority,
243 distributable_over_flag,
244 non_payments_flag,
245 parent_classification_id,
246 costable_flag)
247 values
248 (pay_element_classifications_s.nextval,
249 NULL,
250 'BF',
251 l_classification_name,
252 l_description,
253 NULL,
254 l_costing_debit_or_credit,
255 l_default_high_priority,
256 l_default_low_priority,
257 l_default_priority,
258 l_distributable_over_flag,
259 l_non_payments_flag,
260 l_parent_id,
261 l_costable_flag);
262 --
263 select pay_element_classifications_s.currval
264 into new_id
265 from dual;
266 --
267 return new_id;
268 end do_insert;
269 --
270 begin
271 class_id := do_insert( 'Non Payment',
272 'Used for element types which should not result in a payment',
273 NULL, 1000, 1, 500, 'N', 'Y', NULL, 'N');
274 --
275 class_id := do_insert( 'Direct Payment',
276 'Used for element types which result in a direct payment',
277 'D', 2000, 1001, 1500, 'N', 'N', NULL, 'Y');
278 --
279 -- insert EARNINGS classification and it's sub classifications
280 --
281 earnings_class := do_insert( 'Earnings',
282 'Used for element types which constitute earnings',
283 'D', 3000, 2001, 2500, 'Y', 'N', NULL, 'Y');
284 --
285 class_id := do_insert( 'PAYE',
286 'Pay As You Earn',
287 'D', NULL, NULL, NULL, 'N', 'N', earnings_class, 'Y');
288 --
289 class_id := do_insert( 'NI Employee',
290 'Employee NI',
291 'D', NULL, NULL, NULL, 'N', 'N', earnings_class, 'Y');
292 --
293 class_id := do_insert( 'NI Employer',
294 'Employer NI',
295 'D', NULL, NULL, NULL, 'N', 'N', earnings_class, 'Y');
296 --
297 class_id := do_insert( 'Employer Charges',
298 'Used for employer charges eg. Employer''s NI',
299 'D', 4000, 3001, 3500, 'N', 'N', NULL, 'Y');
300 --
301 class_id := do_insert( 'Pre-Tax Deductions',
302 NULL,
303 'C', 5000, 4001, 4500, 'N', 'N', NULL, 'Y');
304 --
305 class_id := do_insert( 'Tax Deductions',
306 'Used for tax deductions eg. PAYE and NI',
307 'C', 6000, 5001, 5500, 'N', 'N', NULL, 'Y');
308 --
309 class_id := do_insert( 'Voluntary Deductions',
310 NULL,
311 'C', 7000, 6001, 6500, 'N', 'N', NULL, 'Y');
312 end;
313 --
314 --
315 -- +==================================================================+
316 -- | Insert Balance Types (no building blocks yet !) |
317 -- +==================================================================+
318 --
319 declare
320 procedure do_insert(l_balance_name varchar2,
321 l_balance_uom varchar2,
322 l_assign_remun varchar2) is
323 begin
324 insert into pay_balance_types
325 (balance_type_id,
326 assignment_remuneration_flag,
327 balance_name,
328 balance_uom,
329 legislation_code)
330 values
331 (pay_balance_types_s.nextval,
332 l_assign_remun,
333 l_balance_name,
334 l_balance_uom,
335 'BF');
336 end;
337 begin
338 do_insert( 'Employee NI-able Earnings', 'M', 'N');
339 do_insert( 'Employer NI-able Earnings', 'M', 'N');
340 do_insert( 'Net Earnings', 'M', 'Y');
341 do_insert( 'Total Deductions', 'M', 'N');
342 end;
343 --
344 INSERT INTO PER_ASSIGNMENT_INFO_TYPES
345 (INFORMATION_TYPE
346 ,ACTIVE_INACTIVE_FLAG
347 ,MULTIPLE_OCCURENCES_FLAG
348 ,DESCRIPTION
349 ,LEGISLATION_CODE
350 ,REQUEST_ID
351 ,PROGRAM_APPLICATION_ID
352 ,PROGRAM_ID
353 ,PROGRAM_UPDATE_DATE
354 ,LAST_UPDATE_DATE
355 ,LAST_UPDATED_BY
356 ,LAST_UPDATE_LOGIN
357 ,CREATED_BY
358 ,CREATION_DATE)
359 select 'BF_ASS_INFO'
360 ,'Y'
361 ,'N'
362 ,'Burkina Faso Assignment Extra Details'
363 ,'BF'
364 ,null
365 ,null
366 ,null
367 ,null
368 ,null
369 ,null
370 ,null
371 ,0
372 ,sysdate
373 from dual;
374 --
375 INSERT INTO PAY_PAYMENT_TYPES
379 ,CATEGORY
376 (PAYMENT_TYPE_ID
377 ,TERRITORY_CODE
378 ,CURRENCY_CODE
380 ,PAYMENT_TYPE_NAME
381 ,ALLOW_AS_DEFAULT
382 ,DESCRIPTION
383 ,PRE_VALIDATION_REQUIRED
384 ,VALIDATION_DAYS
385 ,VALIDATION_VALUE
386 ,LAST_UPDATE_DATE
387 ,LAST_UPDATED_BY
388 ,LAST_UPDATE_LOGIN
389 ,CREATED_BY
390 ,CREATION_DATE)
391 select PAY_PAYMENT_TYPES_S.NEXTVAL
392 ,null
393 ,'GBP'
394 ,'CA'
395 ,'BF_PAYMENT'
396 ,'Y'
397 ,'Burkina Faso Cash Payments in Pounds Sterling'
398 ,null
399 ,null
400 ,null
401 ,null
402 ,null
403 ,null
404 ,0
405 ,sysdate
406 from dual;
407 --
408 -- +==================================================================+
409 -- | Insert Balance Dimensions and their FF routes |
410 -- | The procedure db_autogen.build_bf_dimensions is |
411 -- | automatically generated using the balances.c file |
412 -- +==================================================================+
413 --
414 -- This creates temporary balance dimensions for the legislation.
415 pay_autogn.insert_bf_dimensions;
416 --
417 -- Create payments balance for legislation
418 pay_db_localisation_pkg.create_payments_balance('BF');
419 --
420 -- +==================================================================+
421 -- | E X C E P T I O N H A N D L I N G |
422 -- +==================================================================+
423 --
424 end create_BF_localisation;
425 --
426 --
427 --------------------------- create_GB_localisation ---------------------------
428 /*
429 NAME
430 DESCRIPTION
431 NOTES
432 */
433 --
434 PROCEDURE create_GB_localisation is
435 --
436 begin
437 --
438 -- +=====================================================================+
439 -- | Insert leg rules: |
440 -- +=====================================================================+
441 --
442 -- Time periods are not independent.
443 insert into pay_legislation_rules
444 (LEGISLATION_CODE, RULE_TYPE, RULE_MODE)
445 values
446 ('GB', 'I', 'N');
447 --
448 -- The legislative start date is 6th April.
449 insert into pay_legislation_rules
450 (LEGISLATION_CODE, RULE_TYPE, RULE_MODE)
451 values
452 ('GB', 'L', '06/04');
453 --
454 -- +==================================================================+
455 -- | Insert Element Classfications (no building blocks yet !) |
456 -- +==================================================================+
457 --
461 --
458 -- This section creates the typical set of startup element
459 -- classifications for the GB. The primary classifications
460 -- created reflect those in R9:
462 -- Non Payment
463 -- Direct Payment
464 -- Earnings
465 -- Employer Charges
466 -- Pre-Tax Deductions
467 -- Tax Deductions
468 -- Voluntary Deductions
469 --
470 -- Further to this, the following sub classifications are
471 -- added for the Earnings primary classification:
472 --
473 -- PAYE
474 -- NI Employee
475 -- NI Employer
476 --
477 --
478 declare
479 class_id number;
480 earnings_class number;
481 function do_insert(l_classification_name varchar2,
482 l_description varchar2,
483 l_costing_debit_or_credit varchar2,
484 l_default_high_priority number,
485 l_default_low_priority number,
486 l_default_priority number,
487 l_distributable_over_flag varchar2,
488 l_non_payments_flag varchar2,
489 l_parent_id number) return number is
490 new_id number;
491 begin
492 insert into pay_element_classifications
493 (classification_id,
494 business_group_id,
495 legislation_code,
496 classification_name,
497 description,
498 legislation_subgroup,
499 costing_debit_or_credit,
500 default_high_priority,
501 default_low_priority,
502 default_priority,
503 distributable_over_flag,
504 non_payments_flag,
505 parent_classification_id)
506 values
507 (pay_element_classifications_s.nextval,
508 NULL,
509 'GB',
510 l_classification_name,
511 l_description,
512 NULL,
513 l_costing_debit_or_credit,
514 l_default_high_priority,
515 l_default_low_priority,
516 l_default_priority,
517 l_distributable_over_flag,
518 l_non_payments_flag,
519 l_parent_id);
520 --
521 select pay_element_classifications_s.currval
522 into new_id
523 from dual;
524 --
525 return new_id;
526 end do_insert;
527 --
528 begin
529 class_id := do_insert( 'Non Payment',
530 'Used for element types which should not result in a payment',
531 NULL, 1000, 1, 500, 'N', 'Y', NULL);
532 --
533 class_id := do_insert( 'Direct Payment',
534 'Used for element types which result in a direct payment',
535 'D', 2000, 1001, 1500, 'N', 'N', NULL);
536 --
537 -- insert EARNINGS classification and it's sub classifications
538 --
539 earnings_class := do_insert( 'Earnings',
540 'Used for element types which constitute earnings',
541 'D', 3000, 2001, 2500, 'Y', 'N', NULL);
542 --
543 class_id := do_insert( 'PAYE',
544 'Pay As You Earn',
545 NULL, NULL, NULL, NULL, 'N', 'N', earnings_class);
546 --
547 class_id := do_insert( 'NI Employee',
548 'Employee NI',
549 NULL, NULL, NULL, NULL, 'N', 'N', earnings_class);
550 --
551 class_id := do_insert( 'NI Employer',
552 'Employer NI',
553 NULL, NULL, NULL, NULL, 'N', 'N', earnings_class);
554 --
555 class_id := do_insert( 'Employer Charges',
556 'Used for employer charges eg. Employer''s NI',
557 'D', 4000, 3001, 3500, 'N', 'N', NULL);
558 --
559 class_id := do_insert( 'Pre-Tax Deductions',
560 NULL,
561 'C', 5000, 4001, 4500, 'N', 'N', NULL);
562 --
563 class_id := do_insert( 'Tax Deductions',
564 'Used for tax deductions eg. PAYE and NI',
565 'C', 6000, 5001, 5500, 'N', 'N', NULL);
566 --
567 class_id := do_insert( 'Voluntary Deductions',
568 NULL,
569 'C', 7000, 6001, 6500, 'N', 'N', NULL);
570 end;
571 --
572 --
573 -- +==================================================================+
574 -- | Insert Balance Types (no building blocks yet !) |
575 -- +==================================================================+
576 --
577 declare
578 procedure do_insert(l_balance_name varchar2,
579 l_balance_uom varchar2,
580 l_assign_remun varchar2) is
581 begin
582 insert into pay_balance_types
583 (balance_type_id,
584 assignment_remuneration_flag,
585 balance_name,
586 balance_uom,
587 legislation_code)
588 values
589 (pay_balance_types_s.nextval,
590 l_assign_remun,
591 l_balance_name,
592 l_balance_uom,
593 'GB');
594 end;
595 begin
596 do_insert( 'Employee NI-able Earnings', 'M', 'N');
597 do_insert( 'Employer NI-able Earnings', 'M', 'N');
598 do_insert( 'Net Earnings', 'M', 'Y');
599 do_insert( 'Total Deductions', 'M', 'N');
600 end;
601 --
602 -- +==================================================================+
603 -- | Insert Balance Dimensions and their FF routes |
604 -- | The procedure db_autogen.build_uk_dimensions is |
605 -- | automatically generated using the balances.c file |
606 -- +==================================================================+
607 --
608 -- This is a copy of db_autogen.build_uk_dimensions for use in
609 -- creating temporary balance dimensions for the legislation.
610 pay_gbatgn.insert_gb_dimensions;
611 --
612 -- Create payments balance for legislation
613 pay_db_localisation_pkg.create_payments_balance('GB');
614 --
615 -- +==================================================================+
616 -- | E X C E P T I O N H A N D L I N G |
617 -- +==================================================================+
618 --
619 end create_GB_localisation;
620 --
621 end pay_db_localisation_pkg;