[Home] [Help]
PACKAGE BODY: APPS.HR_LEG_INSTALLATION_PKG
Source
1 package body hr_leg_installation_pkg as
2 /* $Header: hrlegins.pkb 120.2 2012/01/09 11:16:26 vmaripal ship $*/
3 --
4 /*
5 Copyright (c) Oracle Corporation 1993,1994,1995. All rights reserved
6
7 /*
8
9 Name : hrlegins.pkb
10 Description : procedures required for installation of legislations
11 Author : T.Battoo
12 Date Created : 19-May-1999
13
14 Change List
15 -----------
16 Date Name Vers Bug No Description
17 +-----------+--------------+--------+---------+-----------------------+
18 17-MAR-2004 D.Vickers 115.5 Bug 3513091. User user_
19 as called with virtual apps
20 28-JUN-2001 D.Vickers 115.3 correct ghr status
21 19-May-1999 T.Battoo 115.0 created
22 */
23
24 procedure insert_row(p_application_short_name varchar2,
25 p_legislation_code varchar2,
26 p_status varchar2,
27 p_action varchar2,
28 p_pi_steps_exist varchar2,
29 p_view_name varchar2,
30 p_created_by varchar2,
31 p_creation_date date,
32 p_last_update_login varchar2,
33 p_last_update_date date,
34 p_last_updated_by varchar2) is
35 begin
36 INSERT INTO hr_legislation_installations
37 (application_short_name,
38 legislation_code,
39 status,
40 action,
41 pi_steps_exist,
42 view_name,
43 created_by,
44 creation_date,
45 last_update_login,
46 last_update_date,
47 last_updated_by)
48 SELECT
49 p_application_short_name,
50 p_legislation_code,
51 p_status,
52 p_action,
53 p_pi_steps_exist,
54 p_view_name,
55 p_created_by,
56 p_creation_date,
57 p_last_update_login,
58 p_last_update_date,
59 p_last_updated_by
60 from dual
61 WHERE not exists (select 1 from hr_legislation_installations
62 where application_short_name=p_application_short_name
63 and nvl(p_legislation_code,'x')=nvl(legislation_code,'x'));
64 end;
65
66 procedure update_row(p_application_short_name varchar2,
67 p_legislation_code varchar2,
68 p_status varchar2,
69 p_action varchar2,
70 p_created_by varchar2,
71 p_creation_date date,
72 p_last_update_login varchar2,
73 p_last_update_date date,
74 p_last_updated_by varchar2) is
75 begin
76
77 UPDATE hr_legislation_installations
78 SET status=p_status,
79 action=p_action,
80 created_by=p_created_by,
81 creation_date=p_creation_date,
82 last_update_login=p_last_update_login,
83 last_update_date=p_last_update_date,
84 last_updated_by=p_last_updated_by
85 WHERE application_short_name=p_application_short_name
86 and nvl(p_legislation_code,'x')=nvl(legislation_code,'x');
87
88 end;
89
90
91 procedure drop_view(p_product varchar2,p_legislation varchar2) is
92 l_view_name varchar2(256);
93 view_exists varchar2(256);
94 statem varchar2(256);
95 sql_curs number;
96 rows_processed integer;
97 begin
98 select view_name
99 into l_view_name
100 from hr_legislation_installations
101 where application_short_name=p_product
102 and nvl(p_legislation,'x')=nvl(legislation_code,'x');
103
104
105 -- check to see if view exists --
106 select view_name
107 into view_exists
108 from user_views
109 where view_name = l_view_name
110 and rownum=1;
111
112 statem := 'DROP VIEW ' || l_view_name ;
113 sql_curs := dbms_sql.open_cursor;
114 dbms_sql.parse(sql_curs,
115 statem,
116 dbms_sql.v7);
117 rows_processed := dbms_sql.execute(sql_curs);
118 dbms_sql.close_cursor(sql_curs);
119
120
121 exception
122 when no_data_found then return;
123
124 end;
125
126
127 procedure create_view(p_product varchar2,p_legislation varchar2) is
128 l_view_name varchar2(256);
129 statem varchar2(256);
130 sql_curs number;
131 rows_processed integer;
132 begin
133
134 select view_name
135 into l_view_name
136 from hr_legislation_installations
137 where application_short_name=p_product
138 and nvl(p_legislation,'x')=nvl(legislation_code,'x');
139
140 statem := 'CREATE OR REPLACE FORCE VIEW ' || l_view_name ||'(product_implemented) AS SELECT ''product_implemented'' from dual';
141 sql_curs := dbms_sql.open_cursor;
142 dbms_sql.parse(sql_curs,
143 statem,
144 dbms_sql.v7);
145 rows_processed := dbms_sql.execute(sql_curs);
146 dbms_sql.close_cursor(sql_curs);
147
148 exception
149 when no_data_found then return;
150 end;
151
152 procedure check_existing_data is
153 pay_installed number := 0;
154 hr_installed number := 0;
155 ghr_installed number := 0;
156 school_data_installed number:=0;
157 no_tax_rules integer;
158 l_leg_code varchar2(256);
159
160 cursor legislations is
161 select distinct legislation_code
162 from pay_element_classifications
163 where legislation_code in ('GB','US','JP');
164 begin
165 --set hr_installed variable
166 select count(*)
167 into hr_installed
168 from fnd_product_installations
169 where application_id=800 and status='I';
170 --set pay_installed variable
171 select count(*)
172 into pay_installed
173 from fnd_product_installations
174 where application_id=801 and status='I';
175 --set ghr_installed variable
176 select count(*)
177 into ghr_installed
178 from fnd_product_installations
179 where application_id=8301 and status='I';
180 --
181 -- set ghr row
182 if ghr_installed <> 0 then
183 update_row('GHR', 'US', 'I', NULL,NULL,NULL,NULL,NULL,NULL);
184 end if;
185 if hr_installed <> 0 then
186 update_row('PER',NULL,'I',NULL,NULL,NULL,NULL,NULL,NULL);
187 for leg_codes in legislations loop
188 update_row('PER',leg_codes.legislation_code,'I',NULL,NULL,NULL,NULL,NULL,NULL);
189 -- school data views
190 if pay_installed=1 then
191 update_row('PAY',leg_codes.legislation_code,'I',NULL,NULL,NULL,NULL,NULL,NULL);
192 end if;
193 if leg_codes.legislation_code in ('US','GB') then
194 school_data_installed:=0;
195 select count(*)
196 into school_data_installed
197 from per_establishments
198 where rownum=1;
199 if school_data_installed <> 0 then
200 update_row('CM',leg_codes.legislation_code,'I',NULL,NULL,NULL,NULL,NULL,NULL);
201 end if;
202 end if;
203 end loop;
204 end if;
205 end;
206
207 procedure set_existing_data is
208 cursor installed_data is
209 select application_short_name,legislation_code
210 from hr_legislation_installations
211 where status='I' ;
212 begin
213 for installed_leg in installed_data loop
214 create_view(installed_leg.application_short_name,installed_leg.legislation_code);
215 end loop;
216 end;
217
218 procedure drop_pubsyn(p_syn_name in varchar2)
219 is
220 s_curs number;
221 l_statement varchar2(1000);
222 l_ret_row integer;
223 begin
224 l_statement := 'drop public synonym '||p_syn_name;
225 s_curs := dbms_sql.open_cursor;
226 dbms_sql.parse(s_curs,
227 l_statement,
228 dbms_sql.v7);
229 l_ret_row := dbms_sql.execute(s_curs);
230 dbms_sql.close_cursor(s_curs);
231 exception
232 when others then return;
233 end drop_pubsyn;
234
235 end;