[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_SHARED_PKG
Source
1 PACKAGE BODY JG_ZZ_SHARED_PKG AS
2 /* $Header: jgzzssab.pls 120.20.12020000.4 2012/08/03 06:51:39 abhijsar ship $ */
3
4 FUNCTION IS_GLOBALIZATION_ENABLED(p_country_code IN VARCHAR2)
5 RETURN VARCHAR2 IS
6 l_country_code varchar2(2);
7 l_disabled varchar2(10);
8 BEGIN
9 -- Assign the country code
10 l_country_code := p_country_code;
11
12 IF l_country_code IS NOT NULL THEN
13 BEGIN
17 AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE+1)) >= TRUNC(SYSDATE)
14 SELECT 'Yes' into l_disabled FROM FND_LOOKUPS
15 WHERE LOOKUP_TYPE = 'JG_DISABLE_GLOBALIZATION'
16 AND ENABLED_FLAG = 'Y'
18 AND TRUNC(START_DATE_ACTIVE) <= TRUNC(SYSDATE)
19 AND LOOKUP_CODE = UPPER(l_country_code);
20 EXCEPTION
21 WHEN TOO_MANY_ROWS THEN
22 l_disabled := 'Yes';
23 WHEN NO_DATA_FOUND THEN
24 l_disabled := NULL;
25 WHEN OTHERS THEN
26 l_disabled := NULL;
27 END;
28 END IF;
29
30 IF l_disabled = 'Yes' THEN
31 l_country_code := NULL;
32 ELSE
33 l_country_code := p_country_code;
34 END IF;
35
36 RETURN l_country_code;
37
38 END IS_GLOBALIZATION_ENABLED;
39
40 FUNCTION CHECK_CACHE(p_org_id IN NUMBER,
41 p_ledger_id IN NUMBER,
42 p_inv_org_id IN NUMBER,
43 p_resp_id IN NUMBER,
44 p_type IN VARCHAR2)
45 RETURN VARCHAR2 IS
46 BEGIN
47 IF p_org_id is NOT NULL THEN
48 IF p_type = 'COUNTRY' THEN
49 IF JG_ZZ_SHARED_PKG.p_country_tbl.exists('ORGID'||to_char(p_org_id)) THEN
50 return JG_ZZ_SHARED_PKG.p_country_tbl('ORGID'||to_char(p_org_id));
51 END IF;
52 ELSIF p_type = 'PRODUCT' THEN
53 IF JG_ZZ_SHARED_PKG.p_product_tbl.exists('ORGID'||to_char(p_org_id)) THEN
54 return JG_ZZ_SHARED_PKG.p_product_tbl('ORGID'||to_char(p_org_id));
55 END IF;
56 ELSIF p_type = 'APPL' THEN
57 IF JG_ZZ_SHARED_PKG.p_appl_tbl.exists('ORGID'||to_char(p_org_id)) THEN
58 return JG_ZZ_SHARED_PKG.p_appl_tbl('ORGID'||to_char(p_org_id));
59 END IF;
60 END IF;
61
62 ELSIF p_ledger_id is NOT NULL THEN
63 IF p_type = 'COUNTRY' THEN
64 IF JG_ZZ_SHARED_PKG.p_country_tbl.exists('LEID'||to_char(p_ledger_id)) THEN
65 return JG_ZZ_SHARED_PKG.p_country_tbl('LEID'||to_char(p_ledger_id));
66 END IF;
67 ELSIF p_type = 'PRODUCT' THEN
68 IF JG_ZZ_SHARED_PKG.p_product_tbl.exists('LEID'||to_char(p_ledger_id)) THEN
69 return JG_ZZ_SHARED_PKG.p_product_tbl('LEID'||to_char(p_ledger_id));
70 END IF;
71 ELSIF p_type = 'APPL' THEN
72 IF JG_ZZ_SHARED_PKG.p_appl_tbl.exists('LEID'||to_char(p_ledger_id)) THEN
73 return JG_ZZ_SHARED_PKG.p_appl_tbl('LEID'||to_char(p_ledger_id));
74 END IF;
75 END IF;
76
77 ELSIF p_inv_org_id is NOT NULL THEN
78 IF p_type = 'COUNTRY' THEN
79 IF JG_ZZ_SHARED_PKG.p_country_tbl.exists('INVID'||to_char(p_inv_org_id)) THEN
80 return JG_ZZ_SHARED_PKG.p_country_tbl('INVID'||to_char(p_inv_org_id));
81 END IF;
82 ELSIF p_type = 'PRODUCT' THEN
83 IF JG_ZZ_SHARED_PKG.p_product_tbl.exists('INVID'||to_char(p_inv_org_id)) THEN
84 return JG_ZZ_SHARED_PKG.p_product_tbl('INVID'||to_char(p_inv_org_id));
85 END IF;
86 ELSIF p_type = 'APPL' THEN
87 IF JG_ZZ_SHARED_PKG.p_appl_tbl.exists('INVID'||to_char(p_inv_org_id)) THEN
88 return JG_ZZ_SHARED_PKG.p_appl_tbl('INVID'||to_char(p_inv_org_id));
89 END IF;
90 END IF;
91
92 ELSIF p_resp_id is NOT NULL THEN
93 IF p_type = 'COUNTRY' THEN
94 IF JG_ZZ_SHARED_PKG.p_country_tbl.exists('RESPID'||to_char(p_resp_id)) THEN
95 return JG_ZZ_SHARED_PKG.p_country_tbl('RESPID'||to_char(p_resp_id));
96 END IF;
97 ELSIF p_type = 'PRODUCT' THEN
98 IF JG_ZZ_SHARED_PKG.p_product_tbl.exists('RESPID'||to_char(p_resp_id)) THEN
99 return JG_ZZ_SHARED_PKG.p_product_tbl('RESPID'||to_char(p_resp_id));
100 END IF;
101 ELSIF p_type = 'APPL' THEN
102 IF JG_ZZ_SHARED_PKG.p_appl_tbl.exists('RESPID'||to_char(p_resp_id)) THEN
103 return JG_ZZ_SHARED_PKG.p_appl_tbl('RESPID'||to_char(p_resp_id));
104 END IF;
105 END IF;
106
107 END IF;
108 return NULL;
109
110 END CHECK_CACHE;
111
112 PROCEDURE PUT_CACHE( p_org_id IN NUMBER,
113 p_ledger_id IN NUMBER,
114 p_inv_org_id IN NUMBER,
115 p_resp_id IN NUMBER,
116 p_type IN VARCHAR2,
117 p_value IN VARCHAR2)
118 IS
119 BEGIN
120 IF p_org_id is NOT NULL THEN
121 IF p_type = 'COUNTRY' THEN
122 JG_ZZ_SHARED_PKG.p_country_tbl('ORGID'||to_char(p_org_id)) := p_value;
123 ELSIF p_type = 'PRODUCT' THEN
124 JG_ZZ_SHARED_PKG.p_product_tbl('ORGID'||to_char(p_org_id)) := p_value;
125 ELSIF p_type = 'APPL' THEN
126 JG_ZZ_SHARED_PKG.p_appl_tbl('ORGID'||to_char(p_org_id)) := p_value;
127 END IF;
128
129 ELSIF p_ledger_id is NOT NULL THEN
130 IF p_type = 'COUNTRY' THEN
131 JG_ZZ_SHARED_PKG.p_country_tbl('LEID'||to_char(p_ledger_id)) := p_value;
132 ELSIF p_type = 'PRODUCT' THEN
133 JG_ZZ_SHARED_PKG.p_product_tbl('LEID'||to_char(p_ledger_id)) := p_value;
134 ELSIF p_type = 'APPL' THEN
135 JG_ZZ_SHARED_PKG.p_appl_tbl('LEID'||to_char(p_ledger_id)) := p_value;
136 END IF;
137
138 ELSIF p_inv_org_id is NOT NULL THEN
139 IF p_type = 'COUNTRY' THEN
140 JG_ZZ_SHARED_PKG.p_country_tbl('INVID'||to_char(p_inv_org_id)) := p_value;
141 ELSIF p_type = 'PRODUCT' THEN
142 JG_ZZ_SHARED_PKG.p_product_tbl('INVID'||to_char(p_inv_org_id)) := p_value;
143 ELSIF p_type = 'APPL' THEN
147 ELSIF p_resp_id is NOT NULL THEN
144 JG_ZZ_SHARED_PKG.p_appl_tbl('INVID'||to_char(p_inv_org_id)) := p_value;
145 END IF;
146
148 IF p_type = 'COUNTRY' THEN
149 JG_ZZ_SHARED_PKG.p_country_tbl('RESPID'||to_char(p_resp_id)) := p_value;
150 ELSIF p_type = 'PRODUCT' THEN
151 JG_ZZ_SHARED_PKG.p_product_tbl('RESPID'||to_char(p_resp_id)) := p_value;
152 ELSIF p_type = 'APPL' THEN
153 JG_ZZ_SHARED_PKG.p_appl_tbl('RESPID'||to_char(p_resp_id)) := p_value;
154 END IF;
155 END IF;
156
157 END PUT_CACHE;
158
159
160 FUNCTION GET_COUNTRY RETURN VARCHAR2 IS
161
162 l_country_code varchar2(2);
163 l_result VARCHAR2(10);
164 BEGIN
165
166 l_result := CHECK_CACHE(NULL,NULL,NULL,FND_GLOBAL.RESP_ID,'COUNTRY') ;
167 l_result := JG_ZZ_SHARED_PKG.IS_GLOBALIZATION_ENABLED(l_result);
168
169 IF l_result is NOT NULL THEN
170 return l_result;
171 END IF;
172 l_country_code := fnd_profile.value('JGZZ_COUNTRY_CODE');
173
174 JG_ZZ_SHARED_PKG.PUT_CACHE( p_org_id => NULL,
175 p_ledger_id => NULL,
176 p_inv_org_id => NULL,
177 p_resp_id => FND_GLOBAL.RESP_ID,
178 p_type => 'COUNTRY',
179 p_value => l_country_code);
180 l_country_code := JG_ZZ_SHARED_PKG.IS_GLOBALIZATION_ENABLED(l_country_code);
181
182 return l_country_code;
183
184 END get_country;
185
186 FUNCTION GET_COUNTRY ( p_org_id IN NUMBER,
187 p_ledger_id IN NUMBER DEFAULT NULL,
188 p_inv_org_id IN NUMBER DEFAULT NULL)
189 RETURN VARCHAR2 IS
190
191 l_return_status varchar2(100);
192 l_msg_count number;
193 l_global_cnt number;
194 l_msg_data varchar2(2000);
195 l_country_code varchar2(2);
196 l_country_tbl xle_utilities_grp.countrycode_tbl_type;
197 -- l_org_id_from_inv number(15); -- Bug 4963243
198 l_Inv_Le_info_rec XLE_BUSINESSINFO_GRP.inv_org_Rec_Type;
199 l_result VARCHAR2(100);
200 BEGIN
201
202 l_result := NULL;
203 l_result := CHECK_CACHE(p_org_id,p_ledger_id,p_inv_org_id,NULL,'COUNTRY');
204 l_result := JG_ZZ_SHARED_PKG.IS_GLOBALIZATION_ENABLED(l_result);
205
206 IF l_result is NOT NULL THEN
207 return l_result;
208 END IF;
209
210 IF p_org_id IS NOT NULL Then
211 xle_utilities_grp.get_fp_countrycode_ou (
212 p_api_version => 1.0,
213 p_init_msg_list => NULL,
214 p_commit => NULL,
215 x_return_status => l_return_status,
216 x_msg_count => l_msg_count,
217 x_msg_data => l_msg_data,
218 p_operating_unit => p_org_id,
219 x_country_code => l_country_code);
220
221 IF l_return_status <> FND_API.G_RET_STS_SUCCESS Then
222 l_country_code := NULL;
223 END IF;
224
225 ELSIF p_ledger_id IS NOT NULL Then
226 xle_utilities_grp.get_fp_countrycode_lid (
227 p_api_version => 1.0,
228 p_init_msg_list => NULL,
229 p_commit => NULL,
230 x_return_status => l_return_status,
231 x_msg_count => l_msg_count,
232 x_msg_data => l_msg_data,
233 p_ledger_id => p_ledger_id,
234 x_register_country_tbl => l_country_tbl);
235
236 IF l_return_status = FND_API.G_RET_STS_SUCCESS Then
237 -- check if there is only one globalization country supported by Jx products
238 -- and return it. If there is more than one globalization country, return NULL
239 l_global_cnt := 0;
240 FOR i IN l_country_tbl.first..l_country_tbl.last
241 LOOP
242 IF l_country_tbl(i).country_code IN ('CA', 'KR', 'SG', 'TH', 'TW',
243 'AU', 'IN', 'CN', 'SK', 'UA',
244 'BE', 'CH', 'CZ', 'DE', 'DK',
245 'ES', 'EX', 'FI', 'FR', 'GR',
246 'HU', 'IL', 'IT', 'NL', 'NO',
247 'PL', 'PT', 'SE', 'TR', 'RU',
248 'AR', 'BR', 'CL', 'CO', 'MX',
249 'VE', 'PE', 'RO', 'JP', 'KZ','HR',
250 'SI') THEN
251 l_global_cnt := l_global_cnt + 1;
252 l_country_code := l_country_tbl(i).country_code;
253 END IF;
254 END LOOP;
258 ELSE
255 IF l_global_cnt > 1 THEN
256 l_country_code := NULL;
257 END IF;
259 l_country_code := NULL;
260 END IF;
261
262 ELSIF p_inv_org_id IS NOT NULL Then
263
264 -- The following fix is superseded by Bug fix 4963243 given
265 -- at the end of this section
266
267 /*
268 -- Bug 4883010 (SCM), 4946442 (JG)
269 -- obtain org_id from inventory org id
270 -- then obtain country from org_id
271
272 Begin
273 SELECT
274 DECODE(FPG.MULTI_ORG_FLAG, 'Y',
275 DECODE(HOI2.ORG_INFORMATION_CONTEXT,
276 'Accounting Information',
277 TO_NUMBER(HOI2.ORG_INFORMATION3),
278 TO_NUMBER(NULL)
279 ),
280 TO_NUMBER(NULL)
281 )
282 INTO l_org_id_from_inv
283 FROM HR_ORGANIZATION_INFORMATION HOI1,
284 HR_ORGANIZATION_INFORMATION HOI2,
285 GL_SETS_OF_BOOKS GSOB,
286 FND_PRODUCT_GROUPS FPG
287 WHERE HOI1.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
288 AND HOI1.ORG_INFORMATION1 = 'INV'
289 AND HOI1.ORG_INFORMATION2 = 'Y'
290 AND ( HOI1.ORG_INFORMATION_CONTEXT || '') = 'CLASS'
291 AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
292 AND HOI2.ORG_INFORMATION1 = to_char(GSOB.set_of_books_id)
293 AND HOI1.ORGANIZATION_ID = p_inv_org_id;
294 Exception
295 when others then
296 l_org_id_from_inv := null;
297 End;
298
299
300 if l_org_id_from_inv is not null then
301 xle_utilities_grp.get_fp_countrycode_ou (
302 p_api_version => 1.0,
303 p_init_msg_list => NULL,
304 p_commit => NULL,
305 x_return_status => l_return_status,
306 x_msg_count => l_msg_count,
307 x_msg_data => l_msg_data,
308 p_operating_unit => l_org_id_from_inv,
309 x_country_code => l_country_code);
310 end if;
311 */
312
313 -- Bug fix 4963243
314
315 l_Inv_Le_info_rec.delete;
316 XLE_BUSINESSINFO_GRP.Get_InvOrg_Info(
317 x_return_status => l_return_status,
318 x_msg_data => l_msg_data,
319 P_InvOrg_ID => p_inv_org_id,
320 P_Le_ID => NULL,
321 P_Party_ID => NULL,
322 x_Inv_Le_info => l_Inv_Le_info_rec);
323
324 IF l_return_status <> FND_API.G_RET_STS_SUCCESS Then
328 END IF;
325 l_country_code := NULL;
326 ELSE
327 l_country_code := l_Inv_Le_info_rec(l_Inv_Le_info_rec.FIRST).COUNTRY;
329
330 END IF;
331 JG_ZZ_SHARED_PKG.PUT_CACHE( p_org_id => p_org_id,
332 p_ledger_id => p_ledger_id,
333 p_inv_org_id => p_inv_org_id,
334 p_resp_id => NULL,
335 p_type => 'COUNTRY',
336 p_value => l_country_code);
337
338 l_country_code := JG_ZZ_SHARED_PKG.IS_GLOBALIZATION_ENABLED(l_country_code);
339
340 return l_country_code;
341
342 END get_country;
343
344 FUNCTION GET_PRODUCT RETURN VARCHAR2 IS
345
346 l_product_code varchar2(2);
347 l_result VARCHAR2(100);
348 BEGIN
349
350 l_result := CHECK_CACHE(NULL,NULL,NULL,FND_GLOBAL.RESP_ID,'PRODUCT') ;
351 IF l_result is NOT NULL THEN
352 return l_result;
353 END IF;
354 l_product_code := fnd_profile.value('JGZZ_PRODUCT_CODE');
355 JG_ZZ_SHARED_PKG.PUT_CACHE( p_org_id => NULL,
356 p_ledger_id => NULL,
357 p_inv_org_id => NULL,
358 p_resp_id => FND_GLOBAL.RESP_ID,
359 p_type => 'PRODUCT',
360 p_value => l_product_code);
361 return l_product_code;
362
363 END get_product;
364
365 FUNCTION GET_PRODUCT ( p_org_id IN NUMBER,
366 p_ledger_id IN NUMBER DEFAULT NULL,
367 p_inv_org_id IN NUMBER DEFAULT NULL)
368 RETURN VARCHAR2 IS
369
370 l_country_code varchar2(2);
371 l_product_code varchar2(2);
372 l_result VARCHAR2(100);
373 BEGIN
374
375 l_result := NULL;
379 END IF;
376 l_result := CHECK_CACHE(p_org_id,p_ledger_id,p_inv_org_id,NULL,'PRODUCT');
377 IF l_result is NOT NULL THEN
378 return l_result;
380
381 IF p_org_id IS NOT NULL Then
382 l_country_code := get_country (p_org_id,
383 p_ledger_id);
384
385 IF l_country_code IS NOT NULL Then
386 IF l_country_code IN ('CA', 'KR', 'SG', 'TH', 'TW', 'AU','JP', 'IN', 'CN') THEN
387 l_product_code := 'JA';
388 ELSIF l_country_code in ('BE', 'CH', 'CZ', 'DE', 'DK',
389 'ES', 'EX', 'FI', 'FR', 'GR',
390 'HU', 'IL', 'IT', 'NL', 'NO',
391 'PL', 'PT', 'SE', 'TR', 'RU',
392 'SK', 'UA', 'RO','KZ','HR',
393 'SI') THEN
394 l_product_code := 'JE';
395 ELSIF l_country_code in ('AR', 'BR', 'CL','VE','PE', 'CO', 'MX') THEN
396 l_product_code := 'JL';
397 END IF;
398 END IF;
399 END IF;
400
401 JG_ZZ_SHARED_PKG.PUT_CACHE( p_org_id => p_org_id,
402 p_ledger_id => p_ledger_id,
403 p_inv_org_id => p_inv_org_id,
404 p_resp_id => NULL,
405 p_type => 'PRODUCT',
406 p_value => l_product_code);
407 return l_product_code;
408
409 END get_product;
410
411 FUNCTION GET_APPLICATION
412 RETURN VARCHAR2 IS
413
414 l_application fnd_profile_option_values.profile_option_value%type;
415 l_result VARCHAR2(100);
416 BEGIN
417
418 l_result := CHECK_CACHE(NULL,NULL,NULL,FND_GLOBAL.RESP_ID,'APPL') ;
419 IF l_result is NOT NULL THEN
420 return l_result;
421 END IF;
422 l_application := fnd_profile.value('JGZZ_APPL_SHORT_NAME');
423 JG_ZZ_SHARED_PKG.PUT_CACHE( p_org_id => NULL,
424 p_ledger_id => NULL,
425 p_inv_org_id => NULL,
426 p_resp_id => FND_GLOBAL.RESP_ID,
427 p_type => 'APPL',
428 p_value => l_application);
429 return l_application;
430
431 END get_application;
432
433 FUNCTION GET_APPLICATION ( p_curr_form_name IN VARCHAR2)
434 RETURN VARCHAR2 IS
435 l_application varchar2(10);
436 l_result VARCHAR2(100);
437 BEGIN
438
439 if p_curr_form_name in ('APXCUMSP', 'APXIISIM', 'APXINWKB', 'APXPAWKB',
440 'APXPMTCH', 'APXSPDPF', 'APXSUDCC', 'APXSUMBA',
441 'APXTADTC', 'APXTCERT', 'APXTRDRE', 'APXVDMVD',
442 'APXXXEER') then
443 l_application := 'SQLAP';
444 elsif p_curr_form_name in ('ARXAIEXP', 'ARXCUDCI', 'ARXMACPC', 'ARXPRGLP',
445 'ARXRWMAI', 'ARXSTDML', 'ARXSUDRC', 'ARXSUMRT',
446 'ARXSURMT', 'ARXSUVAT', 'ARXSYSPA', 'ARXTWMAI',
447 'RAXSUCTT', 'RAXSUMSC', 'RCVRCERC', 'RCVRCVRC') then
448 l_application := 'AR';
449 elsif p_curr_form_name in ('CEXCABMR') then
450 l_application := 'CE';
451 elsif p_curr_form_name in ('FAXASSET', 'FAXDPRUN', 'FAXMADDS', 'FAXMAREV',
452 'FAXSUBCT', 'FAXSUCAT') then
453 l_application := 'OFA';
454 elsif p_curr_form_name in ('FNDNLDCX','FNDSNASQ') then
455 l_application := 'FND';
456 elsif p_curr_form_name in ('GLXJEENT', 'GLXSTBKS') then
457 l_application := 'SQLGL';
458 elsif p_curr_form_name in ('INVIDITM', 'INVIDTMP', 'INVIVATT', 'INVIVCSU',
459 'INVPTRPR', 'INVSDFCR', 'INVSDOIO') then
460 l_application := 'INV';
461 elsif p_curr_form_name in ('OEXOEMCG', 'OEXOEMOE', 'OEXOEORD', 'OEXOETEL',
462 'OEXOEVOR', 'OEXORRSO', 'CSCCCCRC') then
463 l_application := 'ONT';
464 elsif p_curr_form_name in ('PERWSLOC') then
465 l_application := 'PER';
466 elsif p_curr_form_name in ('POXBWVRP', 'POXDOPRE', 'POXPOEPO', 'POXPOERL',
467 'POXRQERQ', 'POXSCERQ', 'POXSTDPO') then
468 l_application := 'PO';
469 elsif p_curr_form_name in ('WSHFDDPW', 'WSHFSCDL', 'WSHFSTRX', 'WSHFXCSM') then
470 l_application := 'WSH';
471 elsif p_curr_form_name in ('ZXTAXDETFACTORS', 'ZXTRLLINEDISTUI', 'ZXTRXSIM') then
472 l_application := 'ZX';
473 else
474 -- if the form is not part of the above list of
475 -- globalized forms, access application from system profile.
476 -- if the system profile is not defined, it returns null anyways.
477 l_result := CHECK_CACHE(NULL,NULL,NULL,FND_GLOBAL.RESP_ID,'APPL') ;
478 IF l_result is NOT NULL THEN
479 return l_result;
480 END IF;
481
482 l_application := fnd_profile.value('JGZZ_APPL_SHORT_NAME');
483 JG_ZZ_SHARED_PKG.PUT_CACHE( p_org_id => NULL,
484 p_ledger_id => NULL,
485 p_inv_org_id => NULL,
486 p_resp_id => FND_GLOBAL.RESP_ID,
487 p_type => 'APPL',
488 p_value => l_application);
489 end if;
490
491 RETURN l_application;
492
493 END get_application;
494
495 END JG_ZZ_SHARED_PKG;