[Home] [Help]
PACKAGE BODY: APPS.FF_ARCHIVE_UTILS
Source
1 package body ff_archive_utils as
2 /* $Header: ffarcutl.pkb 115.1 2002/06/14 12:13:09 pkm ship $ */
3 -- /************************************************************************
4 --
5
6 --
7 -- Description : Package and procedure to build sql for payroll processes.
8 --
9 -- Change List
10 -- -----------
11 -- Date Name Vers Bug No Description
12 -- ----------- ---------- ----- ------- -----------------------------
13 -- 11-JUN-2002 pganguly 115.0 Created.
14 --
15 -- ************************************************************************/
16 -- begin
17
18 function get_tax_unit_name(p_context_value in varchar2) return varchar2 is
19
20 begin
21
22 declare
23 cursor cur_org_name is
24 select
25 hou.name
26 from
27 hr_organization_units hou
28 where
29 hou.organization_id = to_number(p_context_value);
30
31 l_org_name hr_organization_units.name%TYPE;
32
33 begin
34
35 open cur_org_name;
36 fetch cur_org_name
37 into l_org_name;
38 if cur_org_name%NOTFOUND then
39 close cur_org_name;
40 return p_context_value;
41 else
42 close cur_org_name;
43 return l_org_name;
44 end if;
45 end;
46
47 end get_tax_unit_name;
48
49 function get_context_value(p_legislation_code in varchar2,
50 p_context_name in varchar2,
51 p_context_value in varchar2) return varchar2 is
52 begin
53
54 declare
55
56 sql_curs number;
57 rows_processed integer;
58 statem varchar2(512);
59
60 l_return_value varchar2(100);
61
62 BEGIN
63
64 --hr_utility.trace_on(1,'ORACLE');
65
66 --statem := 'BEGIN ' || 'ff_archive_utils.' || p_legislation_code || '_'|| p_context_name || '(:p_context_value,:return_value); END;';
67
68 statem := 'select ff_archive_utils.'||p_legislation_code || '_' || p_context_name || '('''|| p_context_value||''') from dual';
69
70 hr_utility.trace('statem = ' || statem);
71 hr_utility.trace('length = ' || length(statem));
72
73 sql_curs := dbms_sql.open_cursor;
74
75 hr_utility.trace('sql_curs = ' || to_char(sql_curs));
76
77 dbms_sql.parse(sql_curs,
78 statem,
79 dbms_sql.v7);
80
81
82 dbms_sql.define_column(sql_curs, 1, l_return_value, 50);
83
84 hr_utility.trace('p_context_value = ' || p_context_value);
85 hr_utility.trace('l_return_value = ' || l_return_value);
86
87 rows_processed := dbms_sql.execute_and_fetch(sql_curs);
88
89 dbms_sql.column_value(sql_curs,1, l_return_value);
90
91 hr_utility.trace('rows_processed = ' || to_char(rows_processed));
92 hr_utility.trace('l_return_value = ' || l_return_value);
93
94 dbms_sql.close_cursor(sql_curs);
95
96 hr_utility.trace('l_return_value = ' || l_return_value);
97 return l_return_value;
98
99 EXCEPTION WHEN OTHERS THEN
100
101 if dbms_sql.is_open(sql_curs) then
102 dbms_sql.close_cursor(sql_curs);
103 end if;
104
105 if p_context_name = 'TAX_UNIT_ID' then
106
107 l_return_value := get_tax_unit_name(p_context_value);
108 return l_return_value;
109
110 else
111
112 l_return_value := p_context_value;
113 return l_return_value;
114
115 end if;
116
117 END;
118
119 end get_context_value;
120
121 function us_jurisdiction_code(p_context_value in varchar2)
122 return varchar2 is
123 begin
124
125 declare
126 l_jurisdiction_name varchar2(100);
127 l_ret_val varchar2(32000);
128 begin
129
130 hr_utility.trace('ff_archive_utils.us_jurisdiction_code');
131
132 l_jurisdiction_name := pay_us_employee_payslip_web.get_jurisdiction_name(
133 p_context_value);
134 hr_utility.trace('l_jurisdiction_name = ' || l_jurisdiction_name);
135 l_ret_val := (ltrim(rtrim(l_jurisdiction_name)) || '(' ||
136 ltrim(rtrim(p_context_value)) || ')');
137 hr_utility.trace('l_ret_val : '||l_ret_val);
138 return l_ret_val;
139 end;
140
141 end us_jurisdiction_code;
142
143 function get_legislation_code(p_business_group_id in number)
144 return varchar2 is
145 begin
146
147 declare
148
149 cursor cur_legislation_code is
150 select legislation_code
151 from per_business_groups
152 where business_group_id = p_business_group_id;
153
154 l_legislation_code per_business_groups.legislation_code%TYPE;
155
156 begin
157
158 open cur_legislation_code;
159 fetch cur_legislation_code
160 into l_legislation_code;
161 if cur_legislation_code%NOTFOUND then
162 l_legislation_code := null;
163 end if;
164 close cur_legislation_code;
165
166 return l_legislation_code;
167
168 end;
169
170 end get_legislation_code;
171
172 end ff_archive_utils;