1 PACKAGE BODY PER_MX_DATA_PUMP AS
2 /* $Header: hrmxdpmf.pkb 120.0 2005/05/31 01:28:48 appldev noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2004 Oracle India Pvt. Ltd. *
7 * IDC Hyderabad *
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, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : PER_MX_DATA_PUMP
22
23 Description : This package defines mapping functions for data pump.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- -------------------------------
29 23-JUL-2004 sdahiya 115.0 Created.
30 *****************************************************************************/
31
32 g_proc_name varchar2(30);
33
34 /*******************************************************************************
35 Name : get_tax_unit_id
36 Purpose : This function returns tax unit id for a given tax unit name under a
37 given business group.
38 *******************************************************************************/
39
40 FUNCTION GET_TAX_UNIT_ID(
41 p_tax_unit in varchar2,
42 p_business_group_id in number
43 ) RETURN NUMBER AS
44
45 CURSOR csr_tax_unit_id IS
46 SELECT hou.organization_id
47 FROM hr_all_organization_units hou,
48 hr_all_organization_units_tl houtl,
49 hr_organization_information hoi,
50 hr_organization_information hoi1
51 WHERE hou.organization_id = hoi.organization_id(+)
52 AND hou.organization_id = hoi1.organization_id
53 AND hou.organization_id = houtl.organization_id
54 AND hoi.org_information_context(+) = 'Employer Identification'
55 AND hoi1.org_information_context = 'CLASS'
56 AND hoi1.org_information1 = 'HR_LEGAL'
57 AND hoi1.org_information2 = 'Y'
58 AND houtl.language = userenv('LANG')
59 AND hou.business_group_id = p_business_group_id
60 AND hou.name = p_tax_unit;
61
62 l_org_id hr_all_organization_units.organization_id%type;
63 l_proc_name varchar2(100);
64 BEGIN
65
66 l_proc_name := g_proc_name||'GET_TAX_UNIT_ID';
67 hr_utility.trace('Entering '||l_proc_name);
68
69 OPEN csr_tax_unit_id;
70 FETCH csr_tax_unit_id INTO l_org_id;
71 CLOSE csr_tax_unit_id;
72
73 hr_utility.trace('Leaving '||l_proc_name);
74
75 RETURN (l_org_id);
76 EXCEPTION
77 WHEN others THEN
78 hr_data_pump.fail('GET_TAX_UNIT_ID',
79 sqlerrm,
80 p_tax_unit,
81 p_business_group_id);
82 END GET_TAX_UNIT_ID;
83
84
85 /*******************************************************************************
86 Name : get_work_schedule_id
87 Purpose : This function returns work schedule id for a given work schedule
88 under MX legislation.
89 *******************************************************************************/
90
91 FUNCTION GET_WORK_SCHEDULE (p_work_schedule varchar2) RETURN number IS
92 CURSOR csr_get_work_schedule IS
93 SELECT puc.user_column_id
94 FROM pay_user_columns puc,
95 pay_user_tables put
96 WHERE puc.user_table_id = put.user_table_id
97 AND puc.legislation_code = put.legislation_code
98 AND puc.user_column_name = p_work_schedule
99 AND put.user_table_name = 'COMPANY WORK SCHEDULES'
100 AND put.legislation_code = 'MX'
101 AND puc.business_group_id IS NULL;
102
103 l_user_column_id pay_user_columns.user_column_id%type;
104 l_proc_name varchar2(100);
105 BEGIN
106 l_proc_name := g_proc_name || 'GET_WORK_SCHEDULE';
107 hr_utility.trace('Entering '||l_proc_name);
108
109 OPEN csr_get_work_schedule;
110 FETCH csr_get_work_schedule INTO l_user_column_id;
111 CLOSE csr_get_work_schedule;
112
113 hr_utility.trace('Leaving '||l_proc_name);
114 RETURN (l_user_column_id);
115 EXCEPTION
116 WHEN others THEN
117 hr_data_pump.fail('GET_WORK_SCHEDULE',
118 sqlerrm,
119 p_work_schedule);
120 END GET_WORK_SCHEDULE;
121
122
123 BEGIN
124 g_proc_name := 'PER_MX_DATA_PUMP.';
125 END PER_MX_DATA_PUMP;