[Home] [Help]
PACKAGE BODY: APPS.ZX_MIGRATE_UTIL
Source
1 PACKAGE BODY Zx_Migrate_Util AS
2 /* $Header: zxmigrateutilb.pls 120.15 2006/06/15 10:49:24 asengupt ship $ */
3
4 PG_DEBUG CONSTANT VARCHAR(1) default
5 NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6
7 l_multi_org_flag fnd_product_groups.multi_org_flag%type;
8 l_org_id NUMBER(15);
9
10
11 /*===========================================================================+
12 | Function: GET_TAX_REGIME |
13 | Description: This function returns tax regime code using tax type and |
14 | org_id in Tax Code. |
15 | |
16 | ARGUMENTS : p_org_id IN : Organization ID |
17 | p_tax_type IN : Tax Type |
18 | |
19 | History |
20 | 14-Sep-2004 Yoshimichi Konishi Modified the function to use get_country |
21 | procedure. |
22 | |
23 +===========================================================================*/
24 FUNCTION GET_TAX_REGIME (p_tax_type IN VARCHAR2,
25 p_org_id IN NUMBER) RETURN VARCHAR2
26 IS
27 l_country hr_locations_all.country%TYPE;
28 p_tax_regime_code VARCHAR2(30);
29
30 BEGIN
31
32 l_country := get_country(p_org_id);
33
34 IF l_country IS NOT NULL THEN
35 p_tax_regime_code := l_country || '-' || p_tax_type;
36 ELSE
37 p_tax_regime_code := p_tax_type;
38 END IF;
39
40 RETURN p_tax_regime_code;
41
42 END GET_TAX_REGIME ;
43
44 FUNCTION GET_TAX (p_tax_name IN VARCHAR2 ,
45 p_tax_type IN VARCHAR2 ) RETURN VARCHAR2
46 IS
47 p_tax VARCHAR2(50);
48 BEGIN
49 IF length(p_tax_name) = lengthb(p_tax_name) THEN
50 --
51 -- Strip numbers , space and special characters
52 --
53 -- numbers(0123456789), space( ), dash(-), underscore(_),
54 -- percent(%), comma(,), period(.), asterisk(*), at(@),
55 -- exclamation(!), pound(#), tilde(~), dollar($), carat(^),
56 -- ampasand(&), plus(+), equal(=), backslash(\), vertical(|),
57 -- colon(:), semi colon(;), quote('), double quote("),
58 -- question(?), slash(/)
59
60 -- If Tax contains only numbers and special characters then
61 -- tax = tax_type|| '-' ||name
62
63 p_tax := nvl(replace(translate(p_tax_name,'1234567890 -_%,.*@!#~$^&+=\|:;"?/''',
64 ' '),' ','')
65 ,p_tax_type || '-' || p_tax_name) ;
66
67
68
69 ELSE
70 -- Multi byte Tax will be converted to Tax Type || '-' || Tax .
71 p_tax := p_tax_type || '-' || p_tax_name;
72 END IF;
73
74 IF lengthb(p_tax)>30
75 THEN
76 p_tax:=substrb(p_tax,1,24)||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_MIG_TAX_S');
77 END IF;
78
79 RETURN p_tax;
80 END GET_TAX;
81
82 /*===========================================================================+
83 | Function: IS_INSTALLED |
84 | Description: This function returns true if the passed product code |
85 | is installed. |
86 | |
87 | ARGUMENTS : Product Code |
88 | |
89 | |
90 | History |
91 | 11-Aug-04 Venkat Initial Version |
92 | |
93 +===========================================================================*/
94
95
96 FUNCTION IS_INSTALLED(p_product_code IN Varchar2)
97 RETURN VARCHAR2 IS
98
99 l_status fnd_product_installations.STATUS%type;
100 l_db_status fnd_product_installations.DB_STATUS%type;
101 l_product_id Number;
102
103 BEGIN
104
105 arp_util_tax.debug( ' IS_INSTALLED .. (+) ' );
106
107 IF p_product_code = 'INV' THEN
108 l_product_id := 401;
109 ELSIF p_product_code = 'AP' THEN
110 l_product_id := 200;
111 ELSIF p_product_code = 'AR' THEN
112 l_product_id := 222;
113 END IF;
114
115 BEGIN
116 SELECT STATUS, DB_STATUS
117 INTO l_status, l_db_status
118 FROM
119 FND_PRODUCT_INSTALLATIONS
120 WHERE
121 APPLICATION_ID = l_product_id;
122 EXCEPTION
123 WHEN OTHERS THEN
124 arp_util_tax.debug('Error while getting status and db status value from FND_PRODUCT_INSTALLATIONS');
125 END;
126
127 arp_util_tax.debug( ' IS_INSTALLED .. (-) ' );
128
129 IF (nvl(l_status,'N') in ('I','S') or
130 nvl(l_db_status,'N') in ('I','S')) THEN
131 return 'Y';
132 ELSE
133 return 'N';
134 END IF;
135
136 END IS_INSTALLED;
137
138
139
140 /*===========================================================================+
141 | Procedure: ZX_UPDATE_LOOKUPS |
142 | Description: This Procedure updates the customization level |
143 | to system in pre-upgrade run mode and reverts back |
144 | in other mode. To lock lookups related to FC and Tax Def |
145 | so that user would not be able to create new codes and this |
146 | avoids need for synchronization. |
147 | |
148 | ARGUMENTS : |
149 | |
150 | MODIFICATION HISTORY |
151 | |
152 | 01-Mar-04 Ranjith Palani Created. |
153 +===========================================================================*/
154 PROCEDURE ZX_UPDATE_LOOKUPS(P_UPGRADE_MODE IN VARCHAR2) IS
155
156 BEGIN
157
158 arp_util_tax.debug('ZX_UPDATE_LOOKUPS (+) ' );
159
160 IF P_UPGRADE_MODE = 'PRE-UPGRADE' THEN
161
162 arp_util_tax.debug(' Updating fnd_lookup_types setting the customization level to System' );
163
164 UPDATE fnd_lookup_types
165 SET customization_level = 'S'
166 WHERE lookup_type in
167 ('JLCL_AP_DOCUMENT_TYPE',
168 'JGZZ_STATISTICAL_CODE',
169 'JEES_INVOICE_CATEGORY',
170 'JATW_DEDUCTIBLE_TYPE',
171 'JATW_GUI_TYPE',
172 -- Tax Def Lookups
173 'TAX TYPE' , 'JATW_GOVERNMENT_TAX_TYPE' , 'JLCL_TAX_CODE_CLASS'
174 );
175
176 ELSE
177
178 arp_util_tax.debug(' Updating fnd_lookup_types setting the customization level to Extensible..' );
179
180 UPDATE fnd_lookup_types
181 SET customization_level = 'U'
182 WHERE lookup_type in
183 ('JLCL_AP_DOCUMENT_TYPE',
184 'JGZZ_STATISTICAL_CODE',
185 'JEES_INVOICE_CATEGORY',
186 -- Tax Def Lookups
187 'JLCL_TAX_CODE_CLASS'
188 );
189
190 arp_util_tax.debug(' Updating fnd_lookup_types setting the customization level to user..' );
191
192 UPDATE fnd_lookup_types
193 SET customization_level = 'E'
194 WHERE lookup_type in
195 ('JATW_DEDUCTIBLE_TYPE',
196 'JATW_GUI_TYPE',
197 -- Tax Def Lookups
198 'TAX TYPE' , 'JATW_GOVERNMENT_TAX_TYPE'
199 );
200
201 END IF;
202
203 arp_util_tax.debug(' ZX_UPDATE_LOOKUPS (-) ' );
204
205 END ZX_UPDATE_LOOKUPS;
206
207
208 /*===========================================================================+
209 | Function: GET_COUNTRY |
210 | Description: This function returns country code. |
211 | |
212 | ARGUMENTS : p_org_id IN organization_id |
213 | |
214 | |
215 | History |
216 | 14-Sep-04 Yoshimichi Konishi Created |
217 | |
218 +===========================================================================*/
219
220 FUNCTION get_country(p_org_id IN NUMBER) RETURN VARCHAR2 IS
221
222 l_country hr_locations_all.country%TYPE;
223 l_style hr_locations_all.style%TYPE;
224 l_vat_country_code financials_system_params_all.vat_country_code%TYPE;
225
226 BEGIN
227 -- 1. Select country code from hr_location
228 BEGIN
229 /*Bug fix 5245448*/
230 if l_multi_org_flag = 'Y'
231 then
232 SELECT substr(loc.country,1,2),
233 substr(loc.style,1,2)
234 INTO l_country,
235 l_style
236 FROM hr_all_organization_units ou,
237 hr_organization_information oi,
238 hr_locations_all loc
239 WHERE ou.organization_id = oi.organization_id
240 AND ou.location_id = loc.location_id
241 AND oi.org_information_context = 'Operating Unit Information'
242 AND oi.organization_id = p_org_id;
243 else
244 SELECT substr(loc.country,1,2),
245 substr(loc.style,1,2)
246 INTO l_country,
247 l_style
248 FROM hr_all_organization_units ou,
249 hr_organization_information oi,
250 hr_locations_all loc
251 WHERE oi.organization_id = l_org_id
252 AND ou.organization_id = oi.organization_id
253 AND ou.location_id = loc.location_id
254 AND oi.org_information_context = 'Operating Unit Information';
255 end if;
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 l_country := NULL;
260 l_style := NULL;
261 END;
262
263 -- 2. Select default_country from AR System Options
264 -- ar_system_parameters_all.default_country is mandatory in UI
265 IF l_country IS NULL THEN
266 BEGIN
267 SELECT default_country
268 INTO l_country
269 FROM ar_system_parameters_all
270 WHERE decode(l_multi_org_flag,'N',l_org_id,org_id) = p_org_id
271 AND org_id <> -3113 ; --Bug Fix 5108463
272 EXCEPTION
273 WHEN OTHERS THEN
274 l_country := NULL;
275 l_style := NULL;
276 END;
277 END IF;
278
279 -- 3. Select vat_country_code from Financials System Options
280 -- Applies to the instance where only AP is installed.
281 IF l_country IS NULL THEN
282 BEGIN
283 SELECT vat_country_code
284 INTO l_vat_country_code
285 FROM financials_system_params_all
286 WHERE decode(l_multi_org_flag,'N',l_org_id,org_id) = p_org_id;
287 EXCEPTION
288 WHEN OTHERS THEN
289 l_country := NULL;
290 l_style := NULL;
291 END;
292 END IF;
293
294 -- 4. None of them above did not work to get country information
295 -- Use location style to get country code
296 IF l_country IS NULL THEN
297 IF l_style IS NOT NULL THEN
298 l_country := l_style;
299 ELSE
300 l_country := NULL;
301 END IF;
302 END IF;
303
304 RETURN l_country;
305
306 END;
307
308 /*===========================================================================+
309 | Function: GET_NEXT_SEQID |
310 | Description: This function returns next sequence value for a given |
311 | sequence name |
312 | ARGUMENTS : p_seq_name IN sequence name |
313 | |
314 | |
315 | History |
316 | 07-Oct-04 Arnab Sengupta Created |
317 | |
318 +===========================================================================*/
319
320
321 FUNCTION get_next_seqid(p_seq_name varchar2) return number is
322
323 seq_id number;
324
325 begin
326
327 EXECUTE IMMEDIATE 'SELECT '||p_seq_name||'.nextval from dual ' INTO seq_id;
328
329 return seq_id;
330
331 end get_next_seqid;
332
333 /*===========================================================================+
334 | Procedure : ZX_ALTER_RATES_SEQUENCE |
338 | |
335 | Description: This function is used to bump the zx_rates_b_s sequence |
336 | |
337 | ARGUMENTS : None |
339 | |
340 | History |
341 | 18-Jul-05 Arnab Sengupta Created |
342 | |
343 +===========================================================================*/
344
345
346 PROCEDURE zx_alter_rates_sequence IS
347
348
349 l_new_seq_id NUMBER;
350 l_rates_count NUMBER;
351
352 BEGIN
353
354 SELECT count(tax_rate_id) into l_rates_count from zx_rates_b where record_type_code = 'MIGRATED';
355
356 IF l_rates_count = 0
357 THEN
358
359 SELECT decode(sign(AP_TAX_CODES_S.nextval - AR_VAT_TAX_S.nextval),
360 '-1',AR_VAT_TAX_S.currval,
361 AP_TAX_CODES_S.currval)
362 INTO l_new_seq_id
363 FROM DUAL;
364
365
366 EXECUTE IMMEDIATE 'DROP SEQUENCE ZX.ZX_RATES_B_S';
367 EXECUTE IMMEDIATE 'CREATE SEQUENCE ZX.ZX_RATES_B_S START WITH '||l_new_seq_id;
368
369 END IF;
370
371
372 END;
373
374
375 BEGIN
376
377 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
378 FND_PRODUCT_GROUPS;
379
380 IF L_MULTI_ORG_FLAG = 'N' THEN
381
382 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
383
384 IF L_ORG_ID IS NULL THEN
385 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
386 END IF;
387 ELSE
388 L_ORG_ID := NULL;
389 END IF;
390
391
392
393 EXCEPTION
394 WHEN OTHERS THEN
395 arp_util_tax.debug('Exception in constructor of Migrate Util '||sqlerrm);
396
397
398 end Zx_Migrate_Util;