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