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