[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_EXT_SERVICES_PUB
Source
1 package body ZX_TCM_EXT_SERVICES_PUB AS
2 /* $Header: zxpservb.pls 120.32.12010000.4 2008/11/12 12:42:04 spasala ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(40) := 'ZX.PLSQL.ZX_TCM_EXT_SERVICES_PUB.';
13
14 PROCEDURE get_fc_country_def_cache_info (
15 p_country_code IN fnd_territories.territory_code%TYPE,
16 p_classification_type IN varchar2,
17 x_classification_rec OUT NOCOPY ZX_GLOBAL_STRUCTURES_PKG.fc_country_def_val_rec_type,
18 x_found_in_cache OUT NOCOPY BOOLEAN,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_error_buffer OUT NOCOPY VARCHAR2);
21
22 PROCEDURE set_fc_country_def_cache_info(
23 p_country_code IN fnd_territories.territory_code%TYPE,
24 p_classification_type IN varchar2,
25 p_classification_code IN varchar2);
26
27 FUNCTION is_territory_code_valid(p_country_code IN VARCHAR2)
28 RETURN BOOLEAN;
29
30 Procedure GET_DEFAULT_STATUS_RATES(
31 p_tax_regime_code IN ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
32 p_tax IN ZX_TAXES_B.TAX%TYPE,
33 p_date IN DATE,
34 p_tax_status_code OUT NOCOPY ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
35 p_tax_rate_code OUT NOCOPY ZX_RATES_B.TAX_RATE_CODE%TYPE,
36 P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
37
38 /*
39
40 A Procedure to return Default Status code and Rate code for an effective date
41 given a Tax Regime Code and Tax
42
43 */
44
45 -- Logging Infra:
46 l_procedure_name CONSTANT VARCHAR2(30) := 'get_default_status_rates';
47 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
48
49 -- get default status code
50 CURSOR c_default_status IS
51 SELECT tax_status_code
52 FROM zx_sco_status
53 WHERE tax_regime_code = p_tax_regime_code
54 AND tax = p_tax
55 AND default_status_flag = 'Y'
56 AND p_date >= default_flg_effective_from
57 AND (p_date <= default_flg_effective_to OR default_flg_effective_to IS NULL);
58
59
60 -- get default rate code
61 CURSOR c_default_rate(c_status_code zx_status_b.tax_status_code%TYPE) IS
62 SELECT tax_rate_code
63 FROM zx_sco_rates
64 WHERE tax_regime_code = p_tax_regime_code
65 AND tax = p_tax
66 AND tax_status_code = c_status_code
67 AND active_flag = 'Y'
68 AND default_rate_flag = 'Y'
69 AND p_date >= default_flg_effective_from
70 AND (p_date <= default_flg_effective_to OR default_flg_effective_to IS NULL);
71
72
73
74 BEGIN
75 -- Logging Infra: Setting up runtime level
76 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
77
78 -- Logging Infra: Procedure level
79 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
80 l_log_msg := l_procedure_name||'(+)';
81 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
82 END IF;
83
84
85 --
86 -- Initialize Return Status and Error Buffer
87 --
88
89 p_return_status := FND_API.G_RET_STS_SUCCESS;
90
91 -- Logging Infra: YK: 3/10: Break point
92 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
93 l_log_msg := 'B: input params: p_tax_regime_code=' || p_tax_regime_code ||
94 ', p_tax=' || p_tax ||
95 ', p_date=' || p_date;
96
97 FND_LOG.STRING(G_LEVEL_STATEMENT,
98 G_MODULE_NAME || l_procedure_name,
99 l_log_msg);
100 END IF;
101
102 IF p_tax_regime_code is NULL OR p_tax is NULL THEN
103 --p_return_status:=FND_API.G_RET_STS_ERROR;
104 --p_error_buffer:='One or more of the parameters are not entered';
105 fnd_message.set_name('ZX','ZX_PARAM_NOT_SET');
106 -- Logging Infra: YK: 3/10:
107 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
108 l_log_msg := 'E: wrong input params: p_tax_regime_code is null or p_tax is null';
109 FND_LOG.STRING(G_LEVEL_STATEMENT,
110 G_MODULE_NAME || l_procedure_name,
111 l_log_msg);
112 END IF;
113 --RETURN;
114 RAISE FND_API.G_EXC_ERROR;
115 ELSE
116 OPEN c_default_status;
117 FETCH c_default_status INTO p_tax_status_code;
118
119 -- Logging Infra: YK: 3/10: Break point
120 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
121 l_log_msg := 'B: CUR: c_default_status: fetched: p_tax_status_code=' || p_tax_status_code;
122
123 FND_LOG.STRING(G_LEVEL_STATEMENT,
124 G_MODULE_NAME || l_procedure_name,
125 l_log_msg);
126 END IF;
127
128 IF c_default_status%found THEN
129 OPEN c_default_rate(p_tax_status_code);
130 FETCH c_default_rate into p_tax_rate_code;
131
132 -- Logging Infra: YK: 3/10: Break point
133 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
134 l_log_msg := 'B: CUR: c_default_rate: fetched: p_tax_rate_code=' || p_tax_rate_code;
135 FND_LOG.STRING(G_LEVEL_STATEMENT,
136 G_MODULE_NAME || l_procedure_name,
137 l_log_msg);
138 END IF;
139
140 p_return_status := FND_API.G_RET_STS_SUCCESS;
141 --p_error_buffer := 'Default Tax Status found';
142
143 IF c_default_rate%FOUND THEN
144 --p_return_status := FND_API.G_RET_STS_SUCCESS;
145 --p_error_buffer := 'Default Tax Status and Rate found';
146
147 -- Logging Infra: YK: 3/10: Break point
148 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
149 l_log_msg := 'B: CUR: c_default_rate: found';
150 FND_LOG.STRING(G_LEVEL_STATEMENT,
151 G_MODULE_NAME || l_procedure_name,
152 l_log_msg);
153 END IF;
154 ELSE
155 -- Logging Infra: YK: 3/10: Break point
156 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
157 l_log_msg := 'B: CUR: c_default_rate: notfound';
158 FND_LOG.STRING(G_LEVEL_STATEMENT,
159 G_MODULE_NAME || l_procedure_name,
160 l_log_msg);
161 END IF;
162 END IF;
163
164 CLOSE c_default_rate;
165
166 ELSIF c_default_status%notfound THEN
167 --p_return_status := FND_API.G_RET_STS_SUCCESS;
168 --p_error_buffer := 'No Default values exist for the given Tax Regime Code and Tax';
169 --fnd_message.set_name('ZX','ZX_DEFAULT_VALUE_NOT_EXIST');
170
171 -- Logging Infra: YK: 3/10: Break point
172 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
173 l_log_msg := 'B: CUR: c_default_status: notfound';
174 FND_LOG.STRING(G_LEVEL_STATEMENT,
175 G_MODULE_NAME || l_procedure_name,
176 l_log_msg);
177 END IF;
178 --RAISE FND_API.G_EXC_ERROR;
179 END IF;
180
181 CLOSE c_default_status;
182
183 END IF;
184
185 -- Logging Infra: YK: 3/10: Put output value here
186 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
187 l_log_msg := 'R: p_tax_status_code=' || p_tax_status_code ||
188 ', p_tax_rate_code=' || p_tax_rate_code;
189 FND_LOG.STRING(G_LEVEL_STATEMENT,
190 G_MODULE_NAME || l_procedure_name,
191 l_log_msg);
192 END IF;
193
194 -- Logging Infra: Procedure level
195 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
196 l_log_msg := l_procedure_name||'(-)';
197 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
198 END IF;
199
200 EXCEPTION
201 WHEN INVALID_CURSOR THEN
202 p_return_status := FND_API.G_RET_STS_ERROR;
203 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
204 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
205 IF c_default_rate%ISOPEN THEN CLOSE c_default_rate; end if;
206 IF c_default_status%ISOPEN THEN CLOSE c_default_status; end if;
207
208 -- Logging Infra: YK: 3/10:
209 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
210 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
211 END IF;
212
213 WHEN FND_API.G_EXC_ERROR THEN
214 p_return_status := FND_API.G_RET_STS_ERROR;
215 IF c_default_rate%ISOPEN THEN CLOSE c_default_rate; end if;
216 IF c_default_status%ISOPEN THEN CLOSE c_default_status; end if;
217 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
218 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
219 END IF;
220
221
222 WHEN OTHERS THEN
223 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
225 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
226
227 IF c_default_rate%ISOPEN THEN CLOSE c_default_rate; end if;
228 IF c_default_status%ISOPEN THEN CLOSE c_default_status; end if;
229
230 -- Logging Infra: YK: 3/10:
231 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
232 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
233 END IF;
234
235 END GET_DEFAULT_STATUS_RATES;
236
237
238 Procedure GET_DEFAULT_CLASSIF_CODE(
239 p_fiscal_type_code IN ZX_FC_TYPES_B.CLASSIFICATION_TYPE_CODE%TYPE,
240 p_country_code IN FND_TERRITORIES.TERRITORY_CODE%TYPE,
241 p_application_id IN ZX_EVNT_CLS_MAPPINGS.APPLICATION_ID%TYPE,
242 p_entity_code IN ZX_EVNT_CLS_MAPPINGS.ENTITY_CODE%TYPE,
243 p_event_class_code IN ZX_EVNT_CLS_MAPPINGS.EVENT_CLASS_CODE%TYPE,
244 p_source_event_class_code IN ZX_EVNT_CLS_MAPPINGS.EVENT_CLASS_CODE%TYPE,
245 p_item_id IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE,
246 p_org_id IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE,
247 p_default_code OUT NOCOPY VARCHAR2,
248 P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
249
250 /*
251
252 A Procedure to return Default values for Fiscal Classifications on to Transactions
253 given a Country Code
254
255 */
256
257 l_country_code FND_TERRITORIES.TERRITORY_CODE%TYPE;
258 l_intended_use ZX_FC_COUNTRY_DEFAULTS.INTENDED_USE_DEFAULT%TYPE;
259 l_product_category_code VARCHAR2(240);
260 l_tax_event_class_code ZX_EVNT_CLS_MAPPINGS.TAX_EVENT_CLASS_CODE%TYPE;
261 l_owner_table ZX_FC_TYPES_B.OWNER_TABLE_CODE%TYPE;
262 l_owner_id_num ZX_FC_TYPES_B.OWNER_ID_NUM%TYPE;
263 l_category_id MTL_ITEM_CATEGORIES.CATEGORY_ID%TYPE;
264 l_category_code varchar2(200);
265 l_product_type varchar2(200);
266 l_status varchar2(1);
267 l_db_status varchar2(1);
268 l_classif_code ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE;
269 l_classif_code_1 ZX_FC_CODES_B.CLASSIFICATION_CODE%TYPE;
270 l_intrcmp_code zx_evnt_cls_mappings.intrcmp_tx_evnt_cls_code%TYPE;
271 l_def_intrcmp_code ZX_EVNT_CLS_OPTIONS.DEF_INTRCMP_TRX_BIZ_CATEGORY%TYPE;
272 l_category_set ZX_FC_COUNTRY_DEFAULTS.PRIMARY_INVENTORY_CATEGORY_SET%TYPE;
273
274 -- Logging Infra:
275 l_procedure_name CONSTANT VARCHAR2(30) := 'get_default_classif_code';
276 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
277
278 -- get country defaults
279 CURSOR c_country_default IS
280 SELECT intended_use_default, product_categ_default, primary_inventory_category_set
281 FROM zx_fc_country_defaults
282 WHERE country_code = p_country_code;
283
284 /*
285 -- get default Intended Use
286 CURSOR c_default_Intended_Use IS
287 SELECT intended_use_default
288 FROM zx_fc_country_defaults
289 WHERE country_code = p_country_code;
290
291 -- get default Product Category
292 CURSOR c_default_Product_Category IS
293 SELECT product_categ_default
294 FROM zx_fc_country_defaults
295 WHERE country_code = p_country_code;
296 */
297
298 /* Bug 5102996 no need to issue query against zx_evnt_cls_mappings or
299 zx_event_cls_options. These are cached in zx_global_Structures_pkg.g_event_class_rec
300 -- get default Transaction Business Category
301 CURSOR c_trx_biz_cat IS
302 SELECT tax_event_class_code, intrcmp_tx_evnt_cls_code
303 FROM zx_evnt_cls_mappings
304 WHERE application_id = p_application_id
305 AND entity_code = p_entity_code
306 AND event_class_code = p_event_class_code;
307
308 CURSOR c_intrcmp_code IS
309 SELECT DEF_INTRCMP_TRX_BIZ_CATEGORY
310 FROM ZX_EVNT_CLS_OPTIONS
311 WHERE event_class_code = p_event_class_code
312 AND FIRST_PTY_ORG_ID = p_org_id;
313 */
314
315 -- Get the Model use for Intended Use
316 CURSOR c_model_Intended_use IS
317 SELECT owner_table_code,owner_id_num
318 FROM zx_fc_types_b
319 WHERE classification_type_code ='INTENDED_USE';
320
321 CURSOR c_item_category IS
322 SELECT category_id
323 FROM mtl_item_categories
324 WHERE category_set_id = l_owner_id_num
325 AND organization_id = p_org_id
326 AND inventory_item_id = p_item_id;
327
328 CURSOR c_category_code IS
329 SELECT REPLACE (mtl.concatenated_segments,flex.concatenated_segment_delimiter,'')
330 FROM MTL_CATEGORIES_B_KFV mtl,
331 FND_ID_FLEX_STRUCTURES flex,
332 MTL_CATEGORY_SETS_B mcs
333 WHERE mtl.structure_id = mcs.structure_id
334 AND mcs.category_set_id = l_owner_id_num
335 AND flex.ID_FLEX_NUM = mtl.STRUCTURE_ID
336 AND flex.APPLICATION_ID = 401
337 AND flex.ID_FLEX_CODE = 'MCAT'
338 AND mtl.category_id = l_category_id;
339
340 -- Get Default Product Type
341 CURSOR c_product_type IS
342 SELECT F.LOOKUP_CODE
343 FROM FND_LOOKUPS F,
344 MTL_SYSTEM_ITEMS_B I
345 WHERE F.LOOKUP_TYPE= 'ZX_PRODUCT_TYPE'
346 AND I.INVENTORY_ITEM_ID = p_item_id
347 AND I.ORGANIZATION_ID = p_org_id
348 AND F.LOOKUP_CODE = DECODE (I.CONTRACT_ITEM_TYPE_CODE,
349 'SERVICE','SERVICES',
350 'WARRANTY','SERVICES',
351 'USAGE','SERVICES',
352 'SUBSCRIPTION','GOODS',
353 'GOODS');
354
355 -- get default values for User Defined / Document Subtype
356 CURSOR c_classification_code (c_classification_type in varchar2) IS
357 SELECT classification_code
358 FROM zx_fc_codes_denorm_b
359 WHERE classification_type_code = c_classification_type
360 AND country_code = p_country_code
361 AND LANGUAGE = userenv('LANG');
362
363 CURSOR c_delimiter IS
364 SELECT delimiter
365 FROM zx_fc_types_b
366 WHERE classification_type_code ='TRX_BUSINESS_CATEGORY';
367
368 l_fc_country_def_val_rec ZX_GLOBAL_STRUCTURES_PKG.fc_country_def_val_rec_type;
369 l_found_in_cache boolean;
370 l_tbl_index binary_integer;
371 l_return_status VARCHAR2(80);
372 l_error_buffer VARCHAR2(200);
373 g_delimiter zx_fc_types_b.delimiter%type;
374
375
376 BEGIN
377 -- Logging Infra: Setting up runtime level
378 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
379
380 -- Logging Infra: Procedure level
381 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
382 l_log_msg := l_procedure_name||'(+)';
383 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
384 END IF;
385
386 -- Logging Infra: YK: 3/10: Break point
387 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
388 l_log_msg := 'B: input params: p_fiscal_type_code=' || p_fiscal_type_code ||
389 ', p_country_code=' || p_country_code ||
390 ', p_application_id=' || p_application_id ||
391 ', p_entity_code=' || p_entity_code ||
392 ', p_event_class_code=' || p_event_class_code ||
393 ', p_item_id=' || p_item_id ||
394 ', p_org_id=' || p_org_id;
395 FND_LOG.STRING(G_LEVEL_STATEMENT,
396 G_MODULE_NAME || l_procedure_name,
397 l_log_msg);
398 END IF;
399
400 --
401 -- Initialize Return Status and Error Buffer
402 --
403 p_default_code:= Null;
404 p_return_status := FND_API.G_RET_STS_SUCCESS;
405 l_classif_code :=null;
406
407 OPEN c_delimiter;
408 FETCH c_delimiter INTO g_delimiter;
409 CLOSE c_delimiter;
410
411 IF p_country_code is NULL THEN
412 p_return_status:=FND_API.G_RET_STS_ERROR;
413 --p_error_buffer:='One or more of the parameters are not entered';
414 fnd_message.set_name('ZX','ZX_PARAM_NOT_SET');
415 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
416 l_log_msg := 'E: p_country_code is null';
417 FND_LOG.STRING(G_LEVEL_STATEMENT,
418 G_MODULE_NAME || l_procedure_name,
419 l_log_msg);
420 END IF;
421
422 RETURN;
423 -- RAISE FND_API.G_EXC_ERROR;
424 ELSE
425
426 IF NOT is_territory_code_valid(p_country_code) then
427 p_return_status := FND_API.G_RET_STS_ERROR;
428 --p_error_buffer := 'Invalid Country Code: '||p_country_code;
429 fnd_message.set_name('ZX','ZX_COUNTRY_CODE_INVALID');
430 -- Logging Infra: YK: 3/10: Break point
431 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
432 l_log_msg := 'Invalid Country Code: '||p_country_code;
433 FND_LOG.STRING(G_LEVEL_STATEMENT,
434 G_MODULE_NAME || l_procedure_name,
435 l_log_msg);
436 END IF;
437 RETURN;
438 END IF;
439
440 END IF;
441
442 IF p_fiscal_type_code ='INTENDED_USE' then
443
444 -- try to locate in cache first.
445 l_found_in_cache := FALSE;
446 get_fc_country_def_cache_info (
447 p_country_code => l_country_code,
448 p_classification_type => 'INTENDED_USE',
449 x_classification_rec => l_fc_country_def_val_rec,
450 x_found_in_cache => l_found_in_cache,
451 x_return_status => l_return_status,
452 x_error_buffer => l_error_buffer);
453
454 IF l_found_in_cache then
455 p_default_code := l_fc_country_def_val_rec.fc_default_value;
456 ELSE
457
458 IF ZX_GLOBAL_STRUCTURES_PKG.g_intended_use_owner_tbl_info.owner_table_code is not NULL then
459 -- model intended use found in cache
460
461 l_owner_table := ZX_GLOBAL_STRUCTURES_PKG.g_intended_use_owner_tbl_info.owner_table_code;
462 l_owner_id_num := ZX_GLOBAL_STRUCTURES_PKG.g_intended_use_owner_tbl_info.owner_id_num;
463
464 ELSE
465 OPEN c_model_intended_use;
466 FETCH c_model_intended_use into l_owner_table, l_owner_id_num;
467
468 ZX_GLOBAL_STRUCTURES_PKG.g_intended_use_owner_tbl_info.owner_table_code := l_owner_table ;
469 ZX_GLOBAL_STRUCTURES_PKG.g_intended_use_owner_tbl_info.owner_id_num := l_owner_id_num;
470
471 -- Logging Infra: YK: 3/10: Break point
472 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
473 l_log_msg := 'B: CUR: c_model_intended_use: fetched: l_owner_table=' || l_owner_table ||
474 ', l_owner_id_num=' || l_owner_id_num;
475 FND_LOG.STRING(G_LEVEL_STATEMENT,
476 G_MODULE_NAME || l_procedure_name,
477 l_log_msg);
478 END IF;
479
480 IF c_model_intended_use%NOTFOUND then
481 p_return_status := FND_API.G_RET_STS_ERROR;
482 --p_error_buffer := 'Seeded Fiscal Classification Type is missing';
483 fnd_message.set_name('ZX','ZX_FC_TYPE_NOT_EXIST');
484 CLOSE c_model_intended_use;
485
486 -- Logging Infra: YK: 3/10: Break point
487 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
488 l_log_msg := 'E: CUR: c_model_intended_use: notfound';
489 FND_LOG.STRING(G_LEVEL_STATEMENT,
490 G_MODULE_NAME || l_procedure_name,
491 l_log_msg);
492 END IF;
493 return;
494 -- RAISE FND_API.G_EXC_ERROR;
495 END IF;
496
497 CLOSE c_model_intended_use;
498 END IF; -- model intended use found in cache
499
500 IF l_owner_table = 'ZX_FC_TYPES_B' then
501
502 OPEN c_country_default;
503 FETCH c_country_default into l_Intended_Use, l_product_category_code, l_category_set;
504
505 -- Logging Infra: YK: 3/10: Break point
506 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
507 l_log_msg := 'B: CUR: c_country_default: fetched: l_intended_use=' || l_intended_use;
508 FND_LOG.STRING(G_LEVEL_STATEMENT,
509 G_MODULE_NAME || l_procedure_name,
510 l_log_msg);
511 END IF;
512
513 p_default_code := l_Intended_Use;
514
515 set_fc_country_def_cache_info(
516 p_country_code => l_country_code,
517 p_classification_type => 'INTENDED_USE',
518 p_classification_code => l_intended_use);
519
520 set_fc_country_def_cache_info(
521 p_country_code => l_country_code,
522 p_classification_type => 'PRODUCT_CATEGORY',
523 p_classification_code => l_product_category_code);
524
525 set_fc_country_def_cache_info(
526 p_country_code => l_country_code,
527 p_classification_type => 'PRIMARY_CATEGORY_SET',
528 p_classification_code => l_category_set);
529
530
531 CLOSE c_country_default;
532
533 -- Logging Infra: YK: What should be the return status for this condition?
534 -- p_return_status?
535 -- p_error_buffer?
536
537 -- Logging Infra: YK: 3/10: Break point: Assuming this is successful condition
538 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
539 l_log_msg := 'S: p_default_code=' || p_default_code;
540 FND_LOG.STRING(G_LEVEL_STATEMENT,
541 G_MODULE_NAME || l_procedure_name,
542 l_log_msg);
543 END IF;
544
545 ELSE
546 IF l_owner_table is NOT NULL THEN
547 OPEN c_item_category;
548 FETCH c_item_category into l_category_id;
549
550 -- Logging Infra: YK: 3/10: Break point
551 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
552 l_log_msg := 'B: CUR: c_item_category: fetched: l_category_id=' || l_category_id;
553 FND_LOG.STRING(G_LEVEL_STATEMENT,
554 G_MODULE_NAME || l_procedure_name,
555 l_log_msg);
556 END IF;
557
558 IF c_item_category%rowcount>1 THEN
559 p_default_code := NULL;
560 -- p_return_status := FND_API.G_RET_STS_ERROR;
561 -- p_error_buffer := 'Many categories assigned';
562 fnd_message.set_name('ZX','ZX_MANY_CATEG_ASSIGNED');
563 CLOSE c_item_category;
564
565 -- Logging Infra: YK: 3/10: Break point:
566 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
567 l_log_msg := 'E: CUR: c_item_category: rowcount > 1: category_set_id=' || l_owner_id_num ||
568 ', organization_id=' || p_org_id ||
569 ', inventory_item_id='|| p_item_id;
570 FND_LOG.STRING(G_LEVEL_STATEMENT,
571 G_MODULE_NAME || l_procedure_name,
572 l_log_msg);
573 END IF;
574 RETURN;
575 -- RAISE FND_API.G_EXC_ERROR;
576 END IF;
577
578 CLOSE c_item_category;
579
580 OPEN c_category_code;
581 FETCH c_category_code into l_category_code;
582
583 -- Logging Infra: YK: 3/11: Break point
584 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
585 l_log_msg := 'B: CUR: c_category_code: fetched: l_category_code=' || l_category_code;
586 FND_LOG.STRING(G_LEVEL_STATEMENT,
587 G_MODULE_NAME || l_procedure_name,
588 l_log_msg);
589 END IF;
590
591 IF c_category_code%NOTFOUND THEN
592 p_default_code := NULL;
593 --p_return_status := FND_API.G_RET_STS_ERROR;
594 --p_error_buffer := 'Category Code does not exists';
595 fnd_message.set_name('ZX','ZX_ITEM_CAT_NOT_EXIST');
596 CLOSE c_category_code;
597
598 -- Logging Infra: YK: 3/10: Break point:
599 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
600 l_log_msg := 'E: CUR: c_category_code: notfound: category_set_id=' || l_owner_id_num ||
601 ', category_id=' || l_category_id;
602 FND_LOG.STRING(G_LEVEL_STATEMENT,
603 G_MODULE_NAME || l_procedure_name,
604 l_log_msg);
605 END IF;
606
607 RETURN;
608 -- RAISE FND_API.G_EXC_ERROR;
609 END IF;
610
611 p_default_code := l_category_code;
612
613 -- set the value in cache
614 set_fc_country_def_cache_info(
615 p_country_code => l_country_code,
616 p_classification_type => 'INTENDED_USE',
617 p_classification_code => l_category_code);
618
619 CLOSE c_category_code;
620
621 -- YK: 3/11: What if l_category_code is NULL?
622 END IF;
623
624 END IF; --owner table
625 END IF; -- found in cache
626
627 ELSIF p_fiscal_type_code ='PRODUCT_CATEGORY' then
628
629 -- try to locate in cache first.
630 l_found_in_cache := FALSE;
631 get_fc_country_def_cache_info (
632 p_country_code => l_country_code,
633 p_classification_type => 'PRODUCT_CATEGORY',
634 x_classification_rec => l_fc_country_def_val_rec,
635 x_found_in_cache => l_found_in_cache,
636 x_return_status => l_return_status,
637 x_error_buffer => l_error_buffer);
638
639 IF l_found_in_cache then
640 p_default_code := l_fc_country_def_val_rec.fc_default_value;
641 ELSE
642
643 OPEN c_country_default;
644 FETCH c_country_default into l_intended_use, l_product_category_code, l_category_set;
645
646 -- Logging Infra: YK: 3/11: Break point
647 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
648 l_log_msg := 'B: CUR: c_country_default: fetched: l_product_category_code=' || l_product_category_code;
649 FND_LOG.STRING(G_LEVEL_STATEMENT,
650 G_MODULE_NAME || l_procedure_name,
651 l_log_msg);
652 END IF;
653
654 -- YK: 3/11: What if c_country_default returned notfound?
655
656 p_default_code := l_product_category_code;
657
658 -- set the value in cache
659
660 set_fc_country_def_cache_info(
661 p_country_code => l_country_code,
662 p_classification_type => 'INTENDED_USE',
663 p_classification_code => l_intended_use);
664
665 set_fc_country_def_cache_info(
666 p_country_code => l_country_code,
667 p_classification_type => 'PRODUCT_CATEGORY',
668 p_classification_code => l_product_category_code);
669
670 set_fc_country_def_cache_info(
671 p_country_code => l_country_code,
672 p_classification_type => 'PRIMARY_CATEGORY_SET',
673 p_classification_code => l_category_set);
674
675 CLOSE c_country_default;
676 END IF; -- found in cache
677
678 ELSIF p_fiscal_type_code ='PRODUCT_TYPE' then
679
680 -- try to locate in cache first.
681 l_found_in_cache := FALSE;
682 get_fc_country_def_cache_info (
683 p_country_code => l_country_code,
684 p_classification_type => 'PRODUCT_TYPE',
685 x_classification_rec => l_fc_country_def_val_rec,
686 x_found_in_cache => l_found_in_cache,
687 x_return_status => l_return_status,
688 x_error_buffer => l_error_buffer);
689
690 IF l_found_in_cache then
691 p_default_code := l_fc_country_def_val_rec.fc_default_value;
692
693 ELSE
694
695 OPEN c_product_type;
696 FETCH c_product_type into l_product_type;
697
698 -- Logging Infra: Break point
699 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
700 l_log_msg := 'B: CUR: c_product_type: fetched: l_product_type=' || l_product_type;
701 FND_LOG.STRING(G_LEVEL_STATEMENT,
702 G_MODULE_NAME || l_procedure_name,
703 l_log_msg);
704 END IF;
705
706 p_default_code := l_product_type;
707
708 -- set the value in cache
709
710 set_fc_country_def_cache_info(
711 p_country_code => l_country_code,
712 p_classification_type => 'PRODUCT_TYPE',
713 p_classification_code => l_product_type);
714
715 CLOSE c_product_type;
716
717 END IF; -- found in cache
718
719 ELSIF p_fiscal_type_code ='USER_DEFINED' then
720
721 -- try to locate in cache first.
722 l_found_in_cache := FALSE;
723 get_fc_country_def_cache_info (
724 p_country_code => l_country_code,
725 p_classification_type => 'USER_DEFINED',
726 x_classification_rec => l_fc_country_def_val_rec,
727 x_found_in_cache => l_found_in_cache,
728 x_return_status => l_return_status,
729 x_error_buffer => l_error_buffer);
730
731 IF l_found_in_cache then
732 p_default_code := l_fc_country_def_val_rec.fc_default_value;
733
734 ELSE
735
736 OPEN c_classification_code('USER_DEFINED' );
737 FETCH c_classification_code into l_classif_code;
738 FETCH c_classification_code into l_classif_code_1; --Bug fix 5343842
739 /*The second fetch is used to check if the cursor returned more than one row
740 If yes then do not default the classification code */
741 IF c_classification_code%FOUND then
742 l_classif_code := NULL;
743 END IF;
744
745 -- Logging Infra: Break point
746 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
747 l_log_msg := 'B: CUR: c_user_defined: fetched: l_classif_code=' || l_classif_code;
748 FND_LOG.STRING(G_LEVEL_STATEMENT,
749 G_MODULE_NAME || l_procedure_name,
750 l_log_msg);
751 END IF;
752
753 IF l_classif_code is not null then
754 p_default_code := l_classif_code;
755
756 -- set the value in cache
757
758 set_fc_country_def_cache_info(
759 p_country_code => l_country_code,
760 p_classification_type => 'USER_DEFINED',
761 p_classification_code => l_classif_code);
762
763 end if;
764
765 CLOSE c_classification_code;
766 END IF; -- found in cache
767
768 ELSIF p_fiscal_type_code ='DOCUMENT_SUBTYPE' then
769
770 -- try to locate in cache first.
771 l_found_in_cache := FALSE;
772 get_fc_country_def_cache_info (
773 p_country_code => l_country_code,
774 p_classification_type => 'DOCUMENT_SUBYPE',
775 x_classification_rec => l_fc_country_def_val_rec,
776 x_found_in_cache => l_found_in_cache,
777 x_return_status => l_return_status,
778 x_error_buffer => l_error_buffer);
779
780 IF l_found_in_cache then
781 p_default_code := l_fc_country_def_val_rec.fc_default_value;
782
783 ELSE
784
785 OPEN c_classification_code('DOCUMENT_SUBTYPE');
786 FETCH c_classification_code into l_classif_code;
787 FETCH c_classification_code into l_classif_code_1; --Bug fix 5343842
788 /*The second fetch is used to check if the cursor returned more than one row
789 If yes then do not default the classification code */
790 IF c_classification_code%FOUND then
791 l_classif_code := NULL;
792 END IF;
793
794
795
796 -- Logging Infra: Break point
797 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
798 l_log_msg := 'B: CUR: c_document_subtype : fetched: l_classif_code =' || l_classif_code;
799 FND_LOG.STRING(G_LEVEL_STATEMENT,
800 G_MODULE_NAME || l_procedure_name,
801 l_log_msg);
802 END IF;
803
804 IF l_classif_code is not null then
805 p_default_code := l_classif_code;
806
807 -- set the value in cache
808 set_fc_country_def_cache_info(
809 p_country_code => l_country_code,
810 p_classification_type => 'DOCUMENT_SUBTYPE',
811 p_classification_code => l_classif_code);
812
813 END IF;
814
815 CLOSE c_classification_code;
816 END IF; -- found in cache
817
818 ELSIF p_fiscal_type_code ='TRX_BUSINESS_CATEGORY' then
819
820 l_tax_event_class_code := ZX_GLOBAL_STRUCTURES_PKG.g_event_class_Rec.tax_event_class_code;
821 l_intrcmp_code := ZX_GLOBAL_STRUCTURES_PKG.g_event_class_Rec.intrcmp_tx_evnt_cls_code;
822
823 -- Logging Infra: YK: 3/11: Break point
824 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
825 l_log_msg := 'B: CUR: c_trx_biz_cat: fetched: l_tax_event_class_code=' || l_tax_event_class_code;
826 FND_LOG.STRING(G_LEVEL_STATEMENT,
827 G_MODULE_NAME || l_procedure_name,
828 l_log_msg);
829 END IF;
830
831
832 IF substr(p_source_event_class_code,1,5) = 'TRADE' THEN
833 p_default_code := l_tax_event_class_code || g_delimiter || 'TRADE_MGT';
834 RETURN;
835 END IF;
836
837 IF l_intrcmp_code IS NOT NULL THEN
838 l_def_intrcmp_code := ZX_GLOBAL_STRUCTURES_PKG.g_event_class_Rec.DEF_INTRCMP_TRX_BIZ_CATEGORY;
839 p_default_code := l_def_intrcmp_code;
840 ELSE
841 p_default_code := l_tax_event_class_code;
842 END IF;
843
844
845 END IF; -- p_fiscal_type_code
846
847
848 -- Logging Infra: YK: 3/11: Put output value here
849 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
850 l_log_msg := 'R: p_default_code=' || p_default_code;
851 FND_LOG.STRING(G_LEVEL_STATEMENT,
852 G_MODULE_NAME || l_procedure_name,
853 l_log_msg);
854 END IF;
855
856 -- Logging Infra: YK: 3/11: Procedure level
857 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
858 l_log_msg := l_procedure_name||'(-)';
859 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
860 END IF;
861
862 EXCEPTION
863 WHEN INVALID_CURSOR THEN
864 p_return_status := FND_API.G_RET_STS_ERROR;
865 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
866 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
867
868 IF c_country_default%isopen THEN CLOSE c_country_default; END IF;
869 IF c_model_Intended_use%isopen THEN CLOSE c_model_Intended_use; END IF;
870 IF c_item_category%isopen THEN CLOSE c_item_category; END IF;
871 IF c_category_code%isopen THEN CLOSE c_category_code; END IF;
872
873 -- Logging Infra: YK: 3/12:
874 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
875 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
876 END IF;
877
878 WHEN FND_API.G_EXC_ERROR THEN
879 p_return_status := FND_API.G_RET_STS_ERROR;
880 IF c_country_default%isopen THEN CLOSE c_country_default; END IF;
881 IF c_model_Intended_use%isopen THEN CLOSE c_model_Intended_use; END IF;
882 IF c_item_category%isopen THEN CLOSE c_item_category; END IF;
883 IF c_category_code%isopen THEN CLOSE c_category_code; END IF;
884
885
886 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
887 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
888 END IF;
889
890
891 WHEN OTHERS THEN
892 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
893 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
894 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
895
896 IF c_country_default%isopen THEN CLOSE c_country_default; END IF;
897 IF c_model_Intended_use%isopen THEN CLOSE c_model_Intended_use; END IF;
898 IF c_item_category%isopen THEN CLOSE c_item_category; END IF;
899 IF c_category_code%isopen THEN CLOSE c_category_code; END IF;
900
901 -- Logging Infra: YK: 3/12:
902 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
903 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
904 END IF;
905
906 END GET_DEFAULT_CLASSIF_CODE;
907
908
909 Procedure GET_DEFAULT_PRODUCT_CLASSIF(
910 p_country_code IN FND_TERRITORIES.TERRITORY_CODE%TYPE,
911 p_item_id IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE,
912 p_org_id IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE,
913 p_default_code OUT NOCOPY VARCHAR2,
914 P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
915
916 /*
917
918 A Procedure to return Default values for Fiscal Classifications on to Transactions
919 given a Country Code
920
921 */
922
923 l_country_code FND_TERRITORIES.TERRITORY_CODE%TYPE;
924 l_category_set ZX_FC_COUNTRY_DEFAULTS.PRIMARY_INVENTORY_CATEGORY_SET%TYPE;
925 l_category_id MTL_ITEM_CATEGORIES.CATEGORY_ID%TYPE;
926 l_intended_use ZX_FC_COUNTRY_DEFAULTS.INTENDED_USE_DEFAULT%TYPE;
927 l_product_category_code VARCHAR2(240);
928 l_category_code varchar2(200);
929
930 l_status varchar2(1);
931 l_db_status varchar2(1);
932 L_TBL_INDEX binary_integer;
933
934 -- Logging Infra:
935 l_procedure_name CONSTANT VARCHAR2(30) := 'get_default_product_classif';
936 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
937
938 -- Get default Product Fiscal Classification
939
940 CURSOR c_country_default IS
941 SELECT intended_use_default, product_categ_default, primary_inventory_category_set
942 FROM zx_fc_country_defaults
943 WHERE country_code = p_country_code;
944
945 CURSOR c_item_category IS
946 SELECT category_id
947 FROM mtl_item_categories
948 WHERE category_set_id = l_category_set
949 AND organization_id = p_org_id
950 AND inventory_item_id = p_item_id;
951
952 CURSOR c_default_category IS
953 SELECT default_category_id
954 FROM mtl_category_sets_b
955 WHERE category_set_id = l_category_set;
956
957 CURSOR c_category_code IS
958 SELECT REPLACE (mtl.concatenated_segments,flex.concatenated_segment_delimiter,'')
959 FROM MTL_CATEGORIES_B_KFV mtl,
960 FND_ID_FLEX_STRUCTURES flex,
961 MTL_CATEGORY_SETS_B mcs
962 WHERE mtl.structure_id = mcs.structure_id
963 AND mcs.category_set_id = l_category_set
964 AND flex.ID_FLEX_NUM = mtl.STRUCTURE_ID
965 AND flex.APPLICATION_ID = 401
966 AND flex.ID_FLEX_CODE = 'MCAT'
967 AND mtl.category_id = l_category_id;
968
969
970
971
972 BEGIN
973 -- Logging Infra: Setting up runtime level
974 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
975
976 -- Logging Infra: Procedure level
977 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
978 l_log_msg := l_procedure_name||'(+)';
979 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
980 END IF;
981
982 -- Logging Infra: YK: 3/12: Break point
983 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
984 l_log_msg := 'B: input params: p_country_code=' || p_country_code ||
985 ', p_item_id =' || p_item_id ||
986 ', p_org_id=' || p_org_id;
987 FND_LOG.STRING(G_LEVEL_STATEMENT,
988 G_MODULE_NAME || l_procedure_name,
989 l_log_msg);
990 END IF;
991
992 --
993 -- Initialize Return Status and Error Buffer
994 --
995 p_default_code:= Null;
996 p_return_status := FND_API.G_RET_STS_SUCCESS;
997
998 IF p_country_code is NULL THEN
999 p_return_status:=FND_API.G_RET_STS_ERROR;
1000 --p_error_buffer:='One or more of the parameters are not entered';
1001 fnd_message.set_name('ZX','ZX_PARAM_NOT_SET');
1002
1003 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1004 l_log_msg := 'E: p_country_code is null';
1005 FND_LOG.STRING(G_LEVEL_STATEMENT,
1006 G_MODULE_NAME || l_procedure_name,
1007 l_log_msg);
1008 END IF;
1009 RETURN;
1010 --RAISE FND_API.G_EXC_ERROR;
1011 ELSE
1012
1013 IF NOT is_territory_code_valid(p_country_code) then
1014 p_return_status := FND_API.G_RET_STS_ERROR;
1015 --p_error_buffer := 'Invalid Country Code: '||p_country_code;
1016 fnd_message.set_name('ZX','ZX_COUNTRY_CODE_INVALID');
1017 -- Logging Infra: YK: 3/10: Break point
1018 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1019 l_log_msg := 'Invalid Country Code: '||p_country_code;
1020 FND_LOG.STRING(G_LEVEL_STATEMENT,
1021 G_MODULE_NAME || l_procedure_name,
1022 l_log_msg);
1023 END IF;
1024 RETURN;
1025 END IF;
1026 OPEN c_country_default;
1027 FETCH c_country_default into l_intended_use, l_product_category_code,l_category_set;
1028
1029 -- Logging Infra: YK: 3/12: Break point
1030 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1031 l_log_msg := 'B: CUR: c_country_default: fetched: l_category_set=' || l_category_set;
1032 FND_LOG.STRING(G_LEVEL_STATEMENT,
1033 G_MODULE_NAME || l_procedure_name,
1034 l_log_msg);
1035 END IF;
1036
1037 IF c_country_default%NOTFOUND then
1038 -- p_return_status := FND_API.G_RET_STS_ERROR;
1039 --p_error_buffer := 'No defaults have been defined for the country';
1040 fnd_message.set_name('ZX','ZX_COUNTRY_DEFFAULTS_NOT_EXIST');
1041 CLOSE c_country_default;
1042
1043 -- Logging Infra: YK: 3/12: Break point
1044 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1045 l_log_msg := 'B: CUR: c_country_default: notfound: country_code=' || p_country_code;
1046 FND_LOG.STRING(G_LEVEL_STATEMENT,
1047 G_MODULE_NAME || l_procedure_name,
1048 l_log_msg);
1049 END IF;
1050 return;
1051 -- RAISE FND_API.G_EXC_ERROR;
1052
1053 ELSE
1054 -- data found. Store in cache.
1055
1056 set_fc_country_def_cache_info(
1057 p_country_code => l_country_code,
1058 p_classification_type => 'INTENDED_USE',
1059 p_classification_code => l_intended_use);
1060
1061 set_fc_country_def_cache_info(
1062 p_country_code => l_country_code,
1063 p_classification_type => 'PRODUCT_CATEGORY',
1064 p_classification_code => l_product_category_code);
1065
1066 set_fc_country_def_cache_info(
1067 p_country_code => l_country_code,
1068 p_classification_type => 'PRIMARY_CATEGORY_SET',
1069 p_classification_code => l_category_set);
1070
1071 END IF;
1072
1073 CLOSE c_country_default;
1074
1075 IF l_category_set is NOT NULL THEN
1076 OPEN c_item_category;
1077 FETCH c_item_category into l_category_id;
1078
1079 -- Logging Infra: YK: 3/12: Break point
1080 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1081 l_log_msg := 'B: CUR: c_item_categoryt: fetched: l_category_id=' || l_category_id;
1082 FND_LOG.STRING(G_LEVEL_STATEMENT,
1083 G_MODULE_NAME || l_procedure_name,
1084 l_log_msg);
1085 END IF;
1086
1087 IF c_item_category%rowcount>1 THEN
1088 p_default_code := NULL;
1089 -- p_return_status := FND_API.G_RET_STS_ERROR;
1090 --p_error_buffer := 'Many categories assigned under same Category Set';
1091 fnd_message.set_name('ZX','ZX_MANY_CATEG_ASSIGNED');
1092 CLOSE c_item_category;
1093
1094 -- Logging Infra: YK: 3/12: Break point:
1095 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1096 l_log_msg := 'E: CUR: c_item_category: rowcount > 1: category_set_id=' || l_category_set ||
1097 ', organization_id=' || p_org_id ||
1098 ', inventory_item_id='|| p_item_id;
1099 FND_LOG.STRING(G_LEVEL_STATEMENT,
1100 G_MODULE_NAME || l_procedure_name,
1101 l_log_msg);
1102 END IF;
1103
1104 RETURN;
1105 --RAISE FND_API.G_EXC_ERROR;
1106 END IF;
1107
1108 IF c_item_category%notfound THEN
1109 CLOSE c_item_category;
1110 -- Get default value from the Category Set
1111 OPEN c_default_category;
1112 FETCH c_default_category into l_category_id;
1113
1114 -- Logging Infra: YK: 3/12: Break point
1115 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1116 l_log_msg := 'B: CUR: c_default_category: fetched: l_category_id=' || l_category_id;
1117 FND_LOG.STRING(G_LEVEL_STATEMENT,
1118 G_MODULE_NAME || l_procedure_name,
1119 l_log_msg);
1120 END IF;
1121
1122 IF c_default_category%notfound THEN
1123 CLOSE c_default_category ;
1124 p_default_code := NULL;
1125 -- p_return_status := FND_API.G_RET_STS_ERROR;
1126 --p_error_buffer := 'No default value could be derived';
1127 fnd_message.set_name('ZX','ZX_NO_DEFAULT_DERIVED');
1128
1129 -- Logging Infra: YK: 3/12: Break point:
1130 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1131 l_log_msg := 'E: CUR: c_default_category: notfound: category_set_id=' || l_category_set;
1132 FND_LOG.STRING(G_LEVEL_STATEMENT,
1133 G_MODULE_NAME || l_procedure_name,
1134 l_log_msg);
1135 END IF;
1136 return;
1137 --RAISE FND_API.G_EXC_ERROR;
1138 END IF;
1139 CLOSE c_default_category ;
1140 END IF;
1141
1142 CLOSE c_item_category;
1143
1144 OPEN c_category_code;
1145 FETCH c_category_code into l_category_code;
1146
1147 -- Logging Infra: YK: 3/12: Break point
1148 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1149 l_log_msg := 'B: CUR: c__category_code: fetched: l_category_code=' || l_category_code;
1150 FND_LOG.STRING(G_LEVEL_STATEMENT,
1151 G_MODULE_NAME || l_procedure_name,
1152 l_log_msg);
1153 END IF;
1154
1155 IF c_category_code%NOTFOUND THEN
1156 p_default_code := NULL;
1157 --p_return_status := FND_API.G_RET_STS_ERROR;
1158 --p_error_buffer := 'Classification Category Code does not exists';
1159 fnd_message.set_name('ZX','ZX_FC_CATEG_NOT_EXIST');
1160
1161 CLOSE c_category_code;
1162
1163 -- Logging Infra: YK: 3/12: Break point:
1164 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1165 l_log_msg := 'E: CUR: c_category_code: notfound: category_set_id=' || l_category_set ||
1166 ', category_id=' || l_category_id;
1167 FND_LOG.STRING(G_LEVEL_STATEMENT,
1168 G_MODULE_NAME || l_procedure_name,
1169 l_log_msg);
1170 END IF;
1171
1172 RETURN;
1173 -- RAISE FND_API.G_EXC_ERROR;
1174 END IF;
1175
1176 p_default_code := l_category_code;
1177 CLOSE c_category_code;
1178
1179 END IF; -- l_category_set
1180
1181 END IF;
1182
1183 -- Logging Infra: YK: 3/12: Put output value here
1184 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1185 l_log_msg := 'R: p_default_code=' || p_default_code;
1186 FND_LOG.STRING(G_LEVEL_STATEMENT,
1187 G_MODULE_NAME || l_procedure_name,
1188 l_log_msg);
1189 END IF;
1190
1191 -- Logging Infra: YK: 3/12: Procedure level
1192 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1193 l_log_msg := l_procedure_name||'(-)';
1194 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1195 END IF;
1196
1197 EXCEPTION
1198 WHEN INVALID_CURSOR THEN
1199 p_return_status := FND_API.G_RET_STS_ERROR;
1200 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1201 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1202
1203 IF c_country_default%ISOPEN THEN CLOSE c_country_default; END IF;
1204 IF c_item_category%ISOPEN THEN CLOSE c_item_category; END IF;
1205 IF c_default_category%ISOPEN THEN CLOSE c_default_category; END IF;
1206 IF c_category_code%ISOPEN THEN CLOSE c_category_code; END IF;
1207
1208 -- Logging Infra: YK: 3/12
1209 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1210 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
1211 END IF;
1212
1213 WHEN FND_API.G_EXC_ERROR THEN
1214 p_return_status := FND_API.G_RET_STS_ERROR;
1215 IF c_country_default%ISOPEN THEN CLOSE c_country_default; END IF;
1216 IF c_item_category%ISOPEN THEN CLOSE c_item_category; END IF;
1217 IF c_default_category%ISOPEN THEN CLOSE c_default_category; END IF;
1218 IF c_category_code%ISOPEN THEN CLOSE c_category_code; END IF;
1219
1220
1221 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1222 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
1223 END IF;
1224
1225
1226 WHEN OTHERS THEN
1227 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1228 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1229 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1230
1231 IF c_country_default%ISOPEN THEN CLOSE c_country_default; END IF;
1232 IF c_item_category%ISOPEN THEN CLOSE c_item_category; END IF;
1233 IF c_default_category%ISOPEN THEN CLOSE c_default_category; END IF;
1234 IF c_category_code%ISOPEN THEN CLOSE c_category_code; END IF;
1235
1236 -- Logging Infra: YK: 3/12
1237 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1238 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
1239 END IF;
1240
1241 END GET_DEFAULT_PRODUCT_CLASSIF;
1242
1243
1244 /*==============================================================================+
1245 | Function: ZX_GET_PROD_CATEG |
1246 | Description: This function returns passed product fc if inventory is installed |
1247 | If not , then return the passed product category |
1248 | Classification migration |
1249 +=============================================================================*/
1250
1251 FUNCTION ZX_GET_PROD_CATEG (p_product_category IN OUT NOCOPY VARCHAR2,
1252 p_product_fc IN OUT NOCOPY VARCHAR2,
1253 p_country_code IN VARCHAR2) RETURN VARCHAR2 IS
1254
1255 Cursor c_prod_category is
1256 Select product_categ_default
1257 From ZX_FC_COUNTRY_DEFAULTS
1258 Where country_code = p_country_code;
1259
1260 l_product_categ_default zx_fc_country_defaults.product_categ_default%TYPE;
1261 BEGIN
1262
1263 arp_util_tax.debug(' ZX_GET_PROD_CATEG .. (+) ' );
1264
1265 If IS_INV_INSTALLED then
1266
1267 return(p_product_fc);
1268 Else
1269 open c_prod_category;
1270 fetch c_prod_category into l_product_categ_default;
1271 if c_prod_category%notfound then
1272 close c_prod_category;
1273 return(null);
1274 else
1275 close c_prod_category;
1276 return(l_product_categ_default);
1277 end if;
1278 End if;
1279
1280 arp_util_tax.debug(' ZX_GET_PROD_CATEG .. (-) ' );
1281
1282 END ZX_GET_PROD_CATEG;
1283
1284 /*===========================================================================+
1285 | Function: IS_INV_INSTALLED |
1286 | Description: This function returns true if inventory is installed |
1287 | This API is again used by other procedures in Fiscal |
1288 | Classification migration |
1289 | |
1290 | |
1291 | |
1292 | |
1293 | ARGUMENTS : |
1294 | |
1295 | |
1296 | NOTES |
1297 | |
1298 | |
1299 | |
1300 | History |
1301 | zmohiudd Created |
1302 | |
1303 | |
1304 +===========================================================================*/
1305
1306
1307 FUNCTION IS_INV_INSTALLED RETURN BOOLEAN IS
1308
1309 l_status fnd_product_installations.STATUS%type;
1310 l_db_status fnd_product_installations.DB_STATUS%type;
1311
1312
1313 BEGIN
1314
1315 arp_util_tax.debug(' IS_INV_INSTALLED .. (+) ' );
1316
1317 SELECT STATUS, DB_STATUS
1318 INTO l_status, l_db_status
1319 FROM fnd_product_installations
1320 WHERE APPLICATION_ID = '401';
1321
1322 IF (nvl(l_status,'N') = 'N' or nvl(l_db_status,'N') = 'N') THEN
1323 return FALSE;
1324 ELSE
1325 return TRUE;
1326 END IF;
1327 arp_util_tax.debug(' IS_INV_INSTALLED .. (-) ' );
1328
1329 END IS_INV_INSTALLED ;
1330
1331 /**************************************************************************
1332 * *
1333 * Name : Get_Default_Tax_Reg *
1334 * Purpose : Returns the Default Registration Number for a Given Party *
1335 * Logic : In case there is tax registration mark as default *
1336 * the function will return the registration number *
1337 * associated to that record. Second case the function will *
1338 * look for the registration row with null regime *
1339 * (migrated records) *
1340 * Parameters : P_Party_ID ------------ P_Party_Type *
1341 * Party_Id Third Party *
1342 * Party_Site_Id Third Party Site *
1343 * Party_ID Establishments *
1344 * *
1345 * P_Effective_Date Default Sysdate *
1346 * *
1347 * *
1348 **************************************************************************/
1349 FUNCTION Get_Default_Tax_Reg
1350 (P_Party_ID IN zx_party_tax_profile.party_id%Type,
1351 P_Party_Type IN zx_party_tax_profile.party_type_code%Type,
1352 P_Effective_Date IN zx_registrations.effective_from%Type,
1353 x_return_status OUT NOCOPY VARCHAR2
1354 )
1355 RETURN Varchar2
1356 IS
1357 -- Logging Infra
1358 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Default_Tax_Reg';
1359 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1360 l_effective_date zx_registrations.effective_from%Type;
1361 --p_error_buffer varchar2(200);
1362
1363 Cursor Default_Reg IS
1364 Select NVL(reg.registration_number, PTP.rep_registration_number) registration_number
1365 From zx_registrations reg
1366 ,zx_party_tax_profile ptp
1367 Where ptp.party_id = p_party_id
1368 AND ptp.party_type_code = p_party_type
1369 AND ptp.party_tax_profile_id = reg.party_tax_profile_id
1370 AND reg.default_registration_flag = 'Y'
1371 AND l_effective_date >= effective_from
1372 AND (l_effective_date <= effective_to OR effective_to IS NULL);
1373
1374 Cursor Reporting_Reg IS
1375 Select REP_REGISTRATION_NUMBER
1376 From zx_party_tax_profile ptp
1377 Where ptp.party_id = p_party_id
1378 AND ptp.party_type_code = p_party_type;
1379
1380 Begin
1381 -- Logging Infra: Setting up runtime level
1382 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1383
1384 -- Logging Infra: Procedure level
1385 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1386 l_log_msg := l_procedure_name||'(+)';
1387 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1388 END IF;
1389
1390 -- Logging Infra: Statement level
1391 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1392 l_log_msg := 'Parameters ';
1393 l_log_msg := l_log_msg||'P_Party_Id: '||to_char(p_party_id);
1394 l_log_msg := l_log_msg||'P_Party_Type: '||p_party_type;
1395 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1396 END IF;
1397 -- Logging Infra: Statement level
1398
1399 -- Initialize Return Status and Error Buffer
1400 --
1401 x_return_status := FND_API.G_RET_STS_SUCCESS;
1402
1403 -- Set initial value for effective date in case it comes null
1404 IF p_effective_date is null Then
1405 l_Effective_Date:= sysdate;
1406 Else
1407 l_Effective_Date:= p_effective_date;
1408 End if;
1409 --
1410 -- Always Party_ID and Party_Type parameters cannot be NULL
1411 --
1412 IF P_Party_Id IS NULL OR P_Party_Type IS NULL THEN
1413
1414 -- Logging Infra: Statement level
1415 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1416 l_log_msg := 'Parameter P_Party_ID and/or Party_Type are null ';
1417 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1418 END IF;
1419 -- Logging Infra: Statement level
1420
1421 --x_return_status := FND_API.G_RET_STS_ERROR;
1422 --return (NULL);
1423 fnd_message.set_name('ZX','ZX_PTP_ID_NOT_EXIST');
1424 RAISE FND_API.G_EXC_ERROR;
1425
1426 ELSE
1427 -- Try Default Registration First
1428 For Regis IN Default_Reg Loop
1429 Return (Regis.registration_number);
1430 END LOOP;
1431
1432 -- Checking at PTP level
1433 For Regis IN Reporting_Reg Loop
1434 Return (Regis.rep_registration_number);
1435 END LOOP;
1436 END IF;
1437
1438 -- Logging Infra: Procedure level
1439 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1440 l_log_msg := l_procedure_name||'(-)';
1441 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END', l_log_msg);
1442 END IF;
1443 return(null);
1444 EXCEPTION
1445 WHEN NO_DATA_FOUND THEN
1446 /*
1447 --Return(Null);
1448 x_return_status := FND_API.G_RET_STS_ERROR;
1449 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1450 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1451
1452 -- Logging Infra: Statement level
1453 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1454 l_log_msg := 'NO DATA FOUND EXCEP - Error Message: '||SQLERRM;
1455 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1456 END IF;
1457 -- Logging Infra: Statement level
1458 */
1459 NULL;
1460 WHEN INVALID_CURSOR THEN
1461 x_return_status := FND_API.G_RET_STS_ERROR;
1462 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1463 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1464
1465 WHEN FND_API.G_EXC_ERROR THEN
1466 x_return_status := FND_API.G_RET_STS_ERROR;
1467 -- Logging Infra: Statement level
1468 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1469 l_log_msg := 'NO DATA FOUND EXCEP - Error Message: '||SQLERRM;
1470 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1471 END IF;
1472
1473 WHEN OTHERS THEN
1474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1475 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1476 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1477 -- Logging Infra: Statement level
1478 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1479 l_log_msg := 'Error Message: '||SQLERRM;
1480 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1481 END IF;
1482 -- Logging Infra: Statement level
1483
1484 End Get_Default_Tax_Reg;
1485
1486
1487
1488 FUNCTION get_le_from_tax_registration
1489 (
1490 x_return_status OUT NOCOPY VARCHAR2,
1491 p_registration_num IN ZX_REGISTRATIONS.Registration_Number%type,
1492 p_effective_date IN ZX_REGISTRATIONS.effective_from%type,
1493 p_country IN ZX_PARTY_TAX_PROFILE.Country_code%type
1494 ) RETURN Number IS
1495 l_legal_entity_id NUMBER;
1496 BEGIN
1497 IF p_registration_num IS NOT NULL THEN
1498 SELECT distinct xle.legal_entity_id
1499 INTO l_legal_entity_id
1500 from zx_registrations tr, zx_party_tax_profile ptp, xle_etb_profiles xle
1501 where tr.registration_number = p_registration_num
1502 and tr.party_tax_profile_id = ptp.party_tax_profile_id
1503 and ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
1504 and ptp.party_id = xle.party_id;
1505 ELSE
1506 x_return_status := FND_API.G_RET_STS_ERROR;
1507 FND_MESSAGE.Set_Name ('ZX','ZX_REG_NUM_MANDATORY');
1508 END IF;
1509 return l_legal_entity_id;
1510 EXCEPTION WHEN NO_DATA_FOUND THEN
1511 x_return_status := FND_API.G_RET_STS_ERROR;
1512 FND_MESSAGE.Set_Name ('ZX','ZX_REG_LE_NOT_FOUND');
1513 --
1514 WHEN TOO_MANY_ROWS THEN
1515 x_return_status := FND_API.G_RET_STS_ERROR;
1516 FND_MESSAGE.Set_Name ('ZX','ZX_REG_MANY_LEGAL_ENTITY');
1517 --
1518 END get_le_from_tax_registration;
1519
1520 PROCEDURE get_fc_country_def_cache_info (
1521 p_country_code IN fnd_territories.territory_code%TYPE,
1522 p_classification_type IN varchar2,
1523 x_classification_rec OUT NOCOPY ZX_GLOBAL_STRUCTURES_PKG.fc_country_def_val_rec_type,
1524 x_found_in_cache OUT NOCOPY BOOLEAN,
1525 x_return_status OUT NOCOPY VARCHAR2,
1526 x_error_buffer OUT NOCOPY VARCHAR2) is
1527
1528 l_index BINARY_INTEGER;
1529
1530 BEGIN
1531
1532 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1533 x_return_status := FND_API.G_RET_STS_SUCCESS;
1534 x_found_in_cache := FALSE;
1535
1536 IF (g_level_statement >= g_current_runtime_level ) THEN
1537 FND_LOG.STRING(g_level_statement, 'ZX.PLSQL.ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info.BEGIN',
1538 'ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info(+)');
1539 END IF;
1540
1541
1542 l_index := dbms_utility.get_hash_value(
1543 p_country_code||p_classification_type,
1544 1,
1545 8192);
1546 --
1547 -- first check if the status info is available from the cache
1548 --
1549
1550 IF ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL.EXISTS(l_index)
1551 THEN
1552 IF (g_level_statement >= g_current_runtime_level ) THEN
1553 FND_LOG.STRING(g_level_statement,
1554 'ZX.PLSQL.LSQL.ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info',
1555 'Default Classification type '||p_classification_type||
1556 ' for country code '||p_country_code||' from cache, at index = ' || to_char(l_index));
1557 END IF;
1558 x_found_in_cache := TRUE;
1559 x_classification_rec := ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL(l_index);
1560
1561 ELSE
1562 IF (g_level_statement >= g_current_runtime_level ) THEN
1563 FND_LOG.STRING(g_level_statement,
1564 'ZX.PLSQL.LSQL.ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info',
1565 'Default Classification type '||p_classification_type||
1566 ' for country code '||p_country_code||' not found in cache ');
1567 END IF;
1568
1569 END IF;
1570
1571 IF (g_level_statement >= g_current_runtime_level ) THEN
1572 FND_LOG.STRING(g_level_statement, 'ZX.PLSQL.ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info.BEGIN',
1573 'ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info(-)');
1574 END IF;
1575
1576 END get_fc_country_def_cache_info;
1577
1578 PROCEDURE set_fc_country_def_cache_info(
1579 p_country_code IN fnd_territories.territory_code%TYPE,
1580 p_classification_type IN varchar2,
1581 p_classification_code IN varchar2)
1582 is
1583 l_tbl_index binary_integer;
1584 BEGIN
1585
1586 -- set the value in cache
1587 l_tbl_index := dbms_utility.get_hash_value(
1588 p_country_code||p_classification_type,
1589 1,
1590 8192);
1591
1592 ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL(l_tbl_index).country_code := p_country_code;
1593 ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL(l_tbl_index).fc_type := p_classification_type;
1594 ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL(l_tbl_index).fc_default_value := p_classification_code;
1595
1596 END set_fc_country_def_cache_info;
1597
1598 FUNCTION is_territory_code_valid(p_country_code IN VARCHAR2)
1599 RETURN BOOLEAN is
1600 l_country_index binary_integer;
1601 l_territory_code fnd_territories.territory_code%type;
1602 BEGIN
1603 l_country_index := dbms_utility.get_hash_value(P_COUNTRY_CODE, 1, 8192);
1604 IF ZX_GLOBAL_STRUCTURES_PKG.G_TERRITORY_TBL.exists(l_country_index) then
1605 RETURN TRUE;
1606 ELSE
1607 BEGIN
1608 select TERRITORY_CODE into l_territory_code
1609 FROM FND_TERRITORIES
1610 WHERE TERRITORY_CODE = p_country_code;
1611
1612 ZX_GLOBAL_STRUCTURES_PKG.G_TERRITORY_TBL(l_country_index) := l_territory_code;
1613 return TRUE;
1614
1615 EXCEPTION
1616 WHEN NO_DATA_FOUND then
1617 return FALSE;
1618 END;
1619 END IF;
1620 END is_territory_code_valid;
1621
1622 END ZX_TCM_EXT_SERVICES_PUB;