[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.44.12020000.2 2012/07/23 13:01:46 srajapar noship $ */
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_UNEXP_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 l_index BINARY_INTEGER;
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_source_event_class_code=' || p_source_event_class_code ||
394 ', p_item_id=' || p_item_id ||
395 ', p_org_id=' || p_org_id;
396 FND_LOG.STRING(G_LEVEL_STATEMENT,
397 G_MODULE_NAME || l_procedure_name,
398 l_log_msg);
399 END IF;
400
401 --
402 -- Initialize Return Status and Error Buffer
403 --
404 p_default_code:= Null;
405 p_return_status := FND_API.G_RET_STS_SUCCESS;
406 l_classif_code :=null;
407 /* Bug 7483584 ,7519329*/
408 l_country_code := p_country_code;
409
410 OPEN c_delimiter;
411 FETCH c_delimiter INTO g_delimiter;
412 CLOSE c_delimiter;
413
414 IF p_country_code is NULL THEN
415 p_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
416 --p_error_buffer:='One or more of the parameters are not entered';
417 --fnd_message.set_name('ZX','ZX_PARAM_NOT_SET');
418 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
419 l_log_msg := 'E: p_country_code is null';
420 FND_LOG.STRING(G_LEVEL_STATEMENT,
421 G_MODULE_NAME || l_procedure_name,
422 l_log_msg);
423 END IF;
424
425 RETURN;
426 -- RAISE FND_API.G_EXC_ERROR;
427 ELSE
428
429 IF NOT is_territory_code_valid(p_country_code) then
430 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
431 --p_error_buffer := 'Invalid Country Code: '||p_country_code;
432 --fnd_message.set_name('ZX','ZX_COUNTRY_CODE_INVALID');
433 -- Logging Infra: YK: 3/10: Break point
434 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
435 l_log_msg := 'Invalid Country Code: '||p_country_code;
436 FND_LOG.STRING(G_LEVEL_STATEMENT,
437 G_MODULE_NAME || l_procedure_name,
438 l_log_msg);
439 END IF;
440 RETURN;
441 END IF;
442
443 END IF;
444
445 IF p_fiscal_type_code ='INTENDED_USE' then
446
447 -- try to locate in cache first.
448 l_found_in_cache := FALSE;
449 --Bug 11891533, modifying cache structure
450
451 /* get_fc_country_def_cache_info (
452 p_country_code => l_country_code,
453 p_classification_type => 'INTENDED_USE',
454 x_classification_rec => l_fc_country_def_val_rec,
455 x_found_in_cache => l_found_in_cache,
456 x_return_status => l_return_status,
457 x_error_buffer => l_error_buffer); */
458
459 l_index := dbms_utility.get_hash_value(
460 p_org_id||p_item_id||p_fiscal_type_code,
461 1,
462 8192);
463
464 -- Bug#13806759 : Changed the caching table name for caching Intended Use --
465 IF ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL.EXISTS(l_index) AND
466 ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL(l_index).ORG_ID = p_org_id THEN
467 p_default_code := ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL(l_index).FC_DEFAULT_VALUE;
468
469 --Bug 11891533
470 /* IF l_found_in_cache then
471 p_default_code := l_fc_country_def_val_rec.fc_default_value; */
472
473 ELSE
474
475 IF ZX_GLOBAL_STRUCTURES_PKG.g_intended_use_owner_tbl_info.owner_table_code is not NULL then
476 -- model intended use found in cache
477 l_owner_table := ZX_GLOBAL_STRUCTURES_PKG.g_intended_use_owner_tbl_info.owner_table_code;
478 l_owner_id_num := ZX_GLOBAL_STRUCTURES_PKG.g_intended_use_owner_tbl_info.owner_id_num;
479
480 ELSE
481 OPEN c_model_intended_use;
482 FETCH c_model_intended_use INTO l_owner_table, l_owner_id_num;
483
484 ZX_GLOBAL_STRUCTURES_PKG.g_intended_use_owner_tbl_info.owner_table_code := l_owner_table ;
485 ZX_GLOBAL_STRUCTURES_PKG.g_intended_use_owner_tbl_info.owner_id_num := l_owner_id_num;
486
487 -- Logging Infra: YK: 3/10: Break point
488 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
489 l_log_msg := 'B: CUR: c_model_intended_use: fetched: l_owner_table=' || l_owner_table ||
490 ', l_owner_id_num=' || l_owner_id_num;
491 FND_LOG.STRING(G_LEVEL_STATEMENT,
492 G_MODULE_NAME || l_procedure_name,
493 l_log_msg);
494 END IF;
495
496 IF c_model_intended_use%NOTFOUND then
497 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
498 --p_error_buffer := 'Seeded Fiscal Classification Type is missing';
499 --fnd_message.set_name('ZX','ZX_FC_TYPE_NOT_EXIST');
500 CLOSE c_model_intended_use;
501
502 -- Logging Infra: YK: 3/10: Break point
503 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
504 l_log_msg := 'E: CUR: c_model_intended_use: notfound';
505 FND_LOG.STRING(G_LEVEL_STATEMENT,
506 G_MODULE_NAME || l_procedure_name,
507 l_log_msg);
508 END IF;
509 RETURN;
510 -- RAISE FND_API.G_EXC_ERROR;
511 END IF;
512
513 CLOSE c_model_intended_use;
514 END IF; -- model intended use found in cache
515
516 IF l_owner_table = 'ZX_FC_TYPES_B' THEN
517
518 OPEN c_country_default;
519 FETCH c_country_default INTO l_Intended_Use, l_product_category_code, l_category_set;
520
521 -- Logging Infra: YK: 3/10: Break point
522 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
523 l_log_msg := 'B: CUR: c_country_default: fetched: l_intended_use=' || l_intended_use;
524 FND_LOG.STRING(G_LEVEL_STATEMENT,
525 G_MODULE_NAME || l_procedure_name,
526 l_log_msg);
527 END IF;
528
529 p_default_code := l_Intended_Use;
530
531 -- Bug 11891533
532 /* set_fc_country_def_cache_info(
533 p_country_code => l_country_code,
534 p_classification_type => 'INTENDED_USE',
535 p_classification_code => l_intended_use); */
536
537 ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL(l_index).ORG_ID := p_org_id;
538 ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL(l_index).FC_ITEM_ID := p_item_id;
539 ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL(l_index).FC_TYPE := p_fiscal_type_code;
540 ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL(l_index).FC_DEFAULT_VALUE := l_Intended_Use;
541
542 set_fc_country_def_cache_info(
543 p_country_code => l_country_code,
544 p_classification_type => 'PRODUCT_CATEGORY',
545 p_classification_code => l_product_category_code);
546
547 set_fc_country_def_cache_info(
548 p_country_code => l_country_code,
549 p_classification_type => 'PRIMARY_CATEGORY_SET',
550 p_classification_code => l_category_set);
551
552
553 CLOSE c_country_default;
554
555 -- Logging Infra: YK: What should be the return status for this condition?
556 -- p_return_status?
557 -- p_error_buffer?
558
559 -- Logging Infra: YK: 3/10: Break point: Assuming this is successful condition
560 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
561 l_log_msg := 'S: p_default_code=' || p_default_code;
562 FND_LOG.STRING(G_LEVEL_STATEMENT,
563 G_MODULE_NAME || l_procedure_name,
564 l_log_msg);
565 END IF;
566
567 ELSE
568 IF l_owner_table IS NOT NULL THEN
569 OPEN c_item_category;
570 FETCH c_item_category INTO l_category_id;
571
572 -- Logging Infra: YK: 3/10: Break point
573 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
574 l_log_msg := 'B: CUR: c_item_category: fetched: l_category_id=' || l_category_id;
575 FND_LOG.STRING(G_LEVEL_STATEMENT,
576 G_MODULE_NAME || l_procedure_name,
577 l_log_msg);
578 END IF;
579
580 IF c_item_category%rowcount>1 THEN
581 p_default_code := NULL;
582 -- p_return_status := FND_API.G_RET_STS_ERROR;
583 -- p_error_buffer := 'Many categories assigned';
584 --Commented the code to set the message for Bug#9485527
585 --fnd_message.set_name('ZX','ZX_MANY_CATEG_ASSIGNED');
586 CLOSE c_item_category;
587
588 -- Logging Infra: YK: 3/10: Break point:
589 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
590 l_log_msg := 'E: CUR: c_item_category: rowcount > 1: category_set_id=' || l_owner_id_num ||
591 ', organization_id=' || p_org_id ||
592 ', inventory_item_id='|| p_item_id;
593 FND_LOG.STRING(G_LEVEL_STATEMENT,
594 G_MODULE_NAME || l_procedure_name,
595 l_log_msg);
596 END IF;
597 RETURN;
598 -- RAISE FND_API.G_EXC_ERROR;
599 END IF;
600
601 CLOSE c_item_category;
602
603 OPEN c_category_code;
604 FETCH c_category_code into l_category_code;
605
606 -- Logging Infra: YK: 3/11: Break point
607 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
608 l_log_msg := 'B: CUR: c_category_code: fetched: l_category_code=' || l_category_code;
609 FND_LOG.STRING(G_LEVEL_STATEMENT,
610 G_MODULE_NAME || l_procedure_name,
611 l_log_msg);
612 END IF;
613
614 IF c_category_code%NOTFOUND THEN
615 p_default_code := NULL;
616 --p_return_status := FND_API.G_RET_STS_ERROR;
617 --p_error_buffer := 'Category Code does not exists';
618 --Commented the code to set the message for Bug#9485527
619 --fnd_message.set_name('ZX','ZX_ITEM_CAT_NOT_EXIST');
620 CLOSE c_category_code;
621
622 -- Logging Infra: YK: 3/10: Break point:
623 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
624 l_log_msg := 'E: CUR: c_category_code: notfound: category_set_id=' || l_owner_id_num ||
625 ', category_id=' || l_category_id;
626 FND_LOG.STRING(G_LEVEL_STATEMENT,
627 G_MODULE_NAME || l_procedure_name,
628 l_log_msg);
629 END IF;
630
631 RETURN;
632 -- RAISE FND_API.G_EXC_ERROR;
633 END IF;
634
635 p_default_code := l_category_code;
636
637 -- bug 11891533
638 /*-- set the value in cache
639 set_fc_country_def_cache_info(
640 p_country_code => l_country_code,
641 p_classification_type => 'INTENDED_USE',
642 p_classification_code => l_category_code); */
643
644 ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL(l_index).ORG_ID := p_org_id;
645 ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL(l_index).FC_ITEM_ID := p_item_id;
646 ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL(l_index).FC_TYPE := p_fiscal_type_code;
647 ZX_GLOBAL_STRUCTURES_PKG.ITEM_INTENDED_USE_VAL_TBL(l_index).FC_DEFAULT_VALUE := l_category_code;
648
649 CLOSE c_category_code;
650
651 -- YK: 3/11: What if l_category_code is NULL?
652 END IF; -- owner table NOT NULL
653 END IF; -- owner table
654 END IF; -- found in cache
655
656 ELSIF p_fiscal_type_code ='PRODUCT_CATEGORY' then
657
658 -- try to locate in cache first.
659 l_found_in_cache := FALSE;
660 get_fc_country_def_cache_info (
661 p_country_code => l_country_code,
662 p_classification_type => 'PRODUCT_CATEGORY',
663 x_classification_rec => l_fc_country_def_val_rec,
664 x_found_in_cache => l_found_in_cache,
665 x_return_status => l_return_status,
666 x_error_buffer => l_error_buffer);
667
668 IF l_found_in_cache then
669 p_default_code := l_fc_country_def_val_rec.fc_default_value;
670 ELSE
671
672 OPEN c_country_default;
673 FETCH c_country_default into l_intended_use, l_product_category_code, l_category_set;
674
675 -- Logging Infra: YK: 3/11: Break point
676 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
677 l_log_msg := 'B: CUR: c_country_default: fetched: l_product_category_code=' || l_product_category_code;
678 FND_LOG.STRING(G_LEVEL_STATEMENT,
679 G_MODULE_NAME || l_procedure_name,
680 l_log_msg);
681 END IF;
682
683 -- YK: 3/11: What if c_country_default returned notfound?
684
685 p_default_code := l_product_category_code;
686
687 -- set the value in cache
688 -- bug 11891533
689 /* set_fc_country_def_cache_info(
690 p_country_code => l_country_code,
691 p_classification_type => 'INTENDED_USE',
692 p_classification_code => l_intended_use); */
693
694 set_fc_country_def_cache_info(
695 p_country_code => l_country_code,
696 p_classification_type => 'PRODUCT_CATEGORY',
697 p_classification_code => l_product_category_code);
698
699 set_fc_country_def_cache_info(
700 p_country_code => l_country_code,
701 p_classification_type => 'PRIMARY_CATEGORY_SET',
702 p_classification_code => l_category_set);
703
704 CLOSE c_country_default;
705 END IF; -- found in cache
706
707 ELSIF p_fiscal_type_code ='PRODUCT_TYPE' then
708
709 -- try to locate in cache first.
710 l_index := dbms_utility.get_hash_value(
711 p_org_id||p_item_id||p_fiscal_type_code,
712 1,
713 8192);
714
715 IF ZX_GLOBAL_STRUCTURES_PKG.ITEM_PRODUCT_TYPE_VAL_TBL.EXISTS(l_index)
716 AND ZX_GLOBAL_STRUCTURES_PKG.ITEM_PRODUCT_TYPE_VAL_TBL(l_index).ORG_ID = p_org_id then
717 p_default_code := ZX_GLOBAL_STRUCTURES_PKG.ITEM_PRODUCT_TYPE_VAL_TBL(l_index).FC_DEFAULT_VALUE;
718 ELSE
719 OPEN c_product_type;
720 FETCH c_product_type into l_product_type;
721
722 -- Logging Infra: Break point
723 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
724 l_log_msg := 'B: CUR: c_product_type: fetched: l_product_type=' || l_product_type;
725 FND_LOG.STRING(G_LEVEL_STATEMENT,
726 G_MODULE_NAME || l_procedure_name,
727 l_log_msg);
728 END IF;
729
730 p_default_code := l_product_type;
731
732 -- set the value in cache
733 ZX_GLOBAL_STRUCTURES_PKG.ITEM_PRODUCT_TYPE_VAL_TBL(l_index).ORG_ID := p_org_id;
734 ZX_GLOBAL_STRUCTURES_PKG.ITEM_PRODUCT_TYPE_VAL_TBL(l_index).FC_ITEM_ID := p_item_id;
735 ZX_GLOBAL_STRUCTURES_PKG.ITEM_PRODUCT_TYPE_VAL_TBL(l_index).FC_TYPE := p_fiscal_type_code;
736 ZX_GLOBAL_STRUCTURES_PKG.ITEM_PRODUCT_TYPE_VAL_TBL(l_index).FC_DEFAULT_VALUE := l_product_type;
737
738 CLOSE c_product_type;
739
740 END IF; -- found in cache
741
742 ELSIF p_fiscal_type_code ='USER_DEFINED' then
743
744 -- try to locate in cache first.
745 l_found_in_cache := FALSE;
746 get_fc_country_def_cache_info (
747 p_country_code => l_country_code,
748 p_classification_type => 'USER_DEFINED',
749 x_classification_rec => l_fc_country_def_val_rec,
750 x_found_in_cache => l_found_in_cache,
751 x_return_status => l_return_status,
752 x_error_buffer => l_error_buffer);
753
754 IF l_found_in_cache then
755 p_default_code := l_fc_country_def_val_rec.fc_default_value;
756
757 ELSE
758
759 OPEN c_classification_code('USER_DEFINED' );
760 FETCH c_classification_code into l_classif_code;
761 FETCH c_classification_code into l_classif_code_1; --Bug fix 5343842
762 /*The second fetch is used to check if the cursor returned more than one row
763 If yes then do not default the classification code */
764 IF c_classification_code%FOUND then
765 l_classif_code := NULL;
766 END IF;
767
768 -- Logging Infra: Break point
769 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
770 l_log_msg := 'B: CUR: c_user_defined: fetched: l_classif_code=' || l_classif_code;
771 FND_LOG.STRING(G_LEVEL_STATEMENT,
772 G_MODULE_NAME || l_procedure_name,
773 l_log_msg);
774 END IF;
775
776 IF l_classif_code is not null then
777 p_default_code := l_classif_code;
778
779 -- set the value in cache
780
781 set_fc_country_def_cache_info(
782 p_country_code => l_country_code,
783 p_classification_type => 'USER_DEFINED',
784 p_classification_code => l_classif_code);
785
786 end if;
787
788 CLOSE c_classification_code;
789 END IF; -- found in cache
790
791 ELSIF p_fiscal_type_code ='DOCUMENT_SUBTYPE' then
792
793 -- try to locate in cache first.
794 l_found_in_cache := FALSE;
795 get_fc_country_def_cache_info (
796 p_country_code => l_country_code,
797 p_classification_type => 'DOCUMENT_SUBTYPE',
798 x_classification_rec => l_fc_country_def_val_rec,
799 x_found_in_cache => l_found_in_cache,
800 x_return_status => l_return_status,
801 x_error_buffer => l_error_buffer);
802
803 IF l_found_in_cache then
804 p_default_code := l_fc_country_def_val_rec.fc_default_value;
805
806 ELSE
807
808 OPEN c_classification_code('DOCUMENT_SUBTYPE');
809 FETCH c_classification_code into l_classif_code;
810 FETCH c_classification_code into l_classif_code_1; --Bug fix 5343842
811 /*The second fetch is used to check if the cursor returned more than one row
812 If yes then do not default the classification code */
813 IF c_classification_code%FOUND then
814 l_classif_code := NULL;
815 END IF;
816
817
818
819 -- Logging Infra: Break point
820 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
821 l_log_msg := 'B: CUR: c_document_subtype : fetched: l_classif_code =' || l_classif_code;
822 FND_LOG.STRING(G_LEVEL_STATEMENT,
823 G_MODULE_NAME || l_procedure_name,
824 l_log_msg);
825 END IF;
826
827 IF l_classif_code is not null then
828 p_default_code := l_classif_code;
829
830 -- set the value in cache
831 set_fc_country_def_cache_info(
832 p_country_code => l_country_code,
833 p_classification_type => 'DOCUMENT_SUBTYPE',
834 p_classification_code => l_classif_code);
835
836 END IF;
837
838 CLOSE c_classification_code;
839 END IF; -- found in cache
840
841 ELSIF p_fiscal_type_code ='TRX_BUSINESS_CATEGORY' then
842
843 l_tax_event_class_code := ZX_GLOBAL_STRUCTURES_PKG.g_event_class_Rec.tax_event_class_code;
844 l_intrcmp_code := ZX_GLOBAL_STRUCTURES_PKG.g_event_class_Rec.intrcmp_tx_evnt_cls_code;
845
846 -- Logging Infra: YK: 3/11: Break point
847 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
848 l_log_msg := 'B: CUR: c_trx_biz_cat: fetched: l_tax_event_class_code=' || l_tax_event_class_code;
849 FND_LOG.STRING(G_LEVEL_STATEMENT,
850 G_MODULE_NAME || l_procedure_name,
851 l_log_msg);
852 END IF;
853
854
855 IF substr(p_source_event_class_code,1,5) = 'TRADE' THEN
856 p_default_code := l_tax_event_class_code || g_delimiter || 'TRADE_MGT';
857 ELSIF p_source_event_class_code = 'INTERCOMPANY_TRX' THEN
858 IF ZX_GLOBAL_STRUCTURES_PKG.g_event_class_Rec.DEF_INTRCMP_TRX_BIZ_CATEGORY IS NOT NULL
859 AND l_intrcmp_code IS NOT NULL THEN
860 l_def_intrcmp_code := ZX_GLOBAL_STRUCTURES_PKG.g_event_class_Rec.DEF_INTRCMP_TRX_BIZ_CATEGORY;
861 p_default_code := l_def_intrcmp_code;
862 ELSE
863 p_default_code := l_tax_event_class_code;
864 END IF;
865 ELSE
866 p_default_code := l_tax_event_class_code;
867 END IF;
868
869 -- Modified to Populate TBC for R12 created COTO's as def_intrcmp_trx_biz_category is always NULL
870 -- and so TBC will default to NULL.
871 --IF l_intrcmp_code IS NOT NULL THEN
872 -- IF ZX_GLOBAL_STRUCTURES_PKG.g_event_class_Rec.DEF_INTRCMP_TRX_BIZ_CATEGORY IS NOT NULL THEN
873 -- l_def_intrcmp_code := ZX_GLOBAL_STRUCTURES_PKG.g_event_class_Rec.DEF_INTRCMP_TRX_BIZ_CATEGORY;
874 -- p_default_code := l_def_intrcmp_code;
875 -- ELSE
876 -- p_default_code := l_tax_event_class_code;
877 -- END IF;
878 --ELSE
879 -- p_default_code := l_tax_event_class_code;
880 --END IF;
881
882
883 END IF; -- p_fiscal_type_code
884
885
886 -- Logging Infra: YK: 3/11: Put output value here
887 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
888 l_log_msg := 'R: p_default_code=' || p_default_code;
889 FND_LOG.STRING(G_LEVEL_STATEMENT,
890 G_MODULE_NAME || l_procedure_name,
891 l_log_msg);
892 END IF;
893
894 -- Logging Infra: YK: 3/11: Procedure level
895 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
896 l_log_msg := l_procedure_name||'(-)';
897 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
898 END IF;
899
900 EXCEPTION
901 WHEN INVALID_CURSOR THEN
902 p_return_status := FND_API.G_RET_STS_ERROR;
903 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
904 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
905
906 IF c_country_default%isopen THEN CLOSE c_country_default; END IF;
907 IF c_model_Intended_use%isopen THEN CLOSE c_model_Intended_use; END IF;
908 IF c_item_category%isopen THEN CLOSE c_item_category; END IF;
909 IF c_category_code%isopen THEN CLOSE c_category_code; END IF;
910
911 -- Logging Infra: YK: 3/12:
912 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
913 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
914 END IF;
915
916 WHEN FND_API.G_EXC_ERROR THEN
917 p_return_status := FND_API.G_RET_STS_ERROR;
918 IF c_country_default%isopen THEN CLOSE c_country_default; END IF;
919 IF c_model_Intended_use%isopen THEN CLOSE c_model_Intended_use; END IF;
920 IF c_item_category%isopen THEN CLOSE c_item_category; END IF;
921 IF c_category_code%isopen THEN CLOSE c_category_code; END IF;
922
923
924 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
925 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
926 END IF;
927
928
929 WHEN OTHERS THEN
930 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
931 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
932 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
933
934 IF c_country_default%isopen THEN CLOSE c_country_default; END IF;
935 IF c_model_Intended_use%isopen THEN CLOSE c_model_Intended_use; END IF;
936 IF c_item_category%isopen THEN CLOSE c_item_category; END IF;
937 IF c_category_code%isopen THEN CLOSE c_category_code; END IF;
938
939 -- Logging Infra: YK: 3/12:
940 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
941 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
942 END IF;
943
944 END GET_DEFAULT_CLASSIF_CODE;
945
946
947 Procedure GET_DEFAULT_PRODUCT_CLASSIF(
948 p_country_code IN FND_TERRITORIES.TERRITORY_CODE%TYPE,
949 p_item_id IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE,
950 p_org_id IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE,
951 p_default_code OUT NOCOPY VARCHAR2,
952 P_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
953
954 /*
955
956 A Procedure to return Default values for Fiscal Classifications on to Transactions
957 given a Country Code
958
959 */
960
961 l_country_code FND_TERRITORIES.TERRITORY_CODE%TYPE;
962 l_category_set ZX_FC_COUNTRY_DEFAULTS.PRIMARY_INVENTORY_CATEGORY_SET%TYPE;
963 l_category_id MTL_ITEM_CATEGORIES.CATEGORY_ID%TYPE;
964 l_intended_use ZX_FC_COUNTRY_DEFAULTS.INTENDED_USE_DEFAULT%TYPE;
965 l_product_category_code VARCHAR2(240);
966 l_category_code varchar2(200);
967
968 l_status varchar2(1);
969 l_db_status varchar2(1);
970 L_TBL_INDEX binary_integer;
971
972 -- Logging Infra:
973 l_procedure_name CONSTANT VARCHAR2(30) := 'get_default_product_classif';
974 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
975
976 -- Get default Product Fiscal Classification
977
978 CURSOR c_country_default IS
979 SELECT intended_use_default, product_categ_default, primary_inventory_category_set
980 FROM zx_fc_country_defaults
981 WHERE country_code = p_country_code;
982
983 CURSOR c_item_category IS
984 SELECT category_id
985 FROM mtl_item_categories
986 WHERE category_set_id = l_category_set
987 AND organization_id = p_org_id
988 AND inventory_item_id = p_item_id;
989
990 CURSOR c_default_category IS
991 SELECT default_category_id
992 FROM mtl_category_sets_b
993 WHERE category_set_id = l_category_set;
994
995 CURSOR c_category_code IS
996 SELECT REPLACE (mtl.concatenated_segments,flex.concatenated_segment_delimiter,'')
997 FROM MTL_CATEGORIES_B_KFV mtl,
998 FND_ID_FLEX_STRUCTURES flex,
999 MTL_CATEGORY_SETS_B mcs
1000 WHERE mtl.structure_id = mcs.structure_id
1001 AND mcs.category_set_id = l_category_set
1002 AND flex.ID_FLEX_NUM = mtl.STRUCTURE_ID
1003 AND flex.APPLICATION_ID = 401
1004 AND flex.ID_FLEX_CODE = 'MCAT'
1005 AND mtl.category_id = l_category_id;
1006
1007
1008
1009
1010 BEGIN
1011 -- Logging Infra: Setting up runtime level
1012 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1013
1014 -- Logging Infra: Procedure level
1015 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1016 l_log_msg := l_procedure_name||'(+)';
1017 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1018 END IF;
1019
1020 -- Logging Infra: YK: 3/12: Break point
1021 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1022 l_log_msg := 'B: input params: p_country_code=' || p_country_code ||
1023 ', p_item_id =' || p_item_id ||
1024 ', p_org_id=' || p_org_id;
1025 FND_LOG.STRING(G_LEVEL_STATEMENT,
1026 G_MODULE_NAME || l_procedure_name,
1027 l_log_msg);
1028 END IF;
1029
1030 --
1031 -- Initialize Return Status and Error Buffer
1032 --
1033 p_default_code:= Null;
1034 p_return_status := FND_API.G_RET_STS_SUCCESS;
1035
1036 IF p_country_code is NULL THEN
1037 p_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1038 --p_error_buffer:='One or more of the parameters are not entered';
1039 --fnd_message.set_name('ZX','ZX_PARAM_NOT_SET');
1040
1041 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1042 l_log_msg := 'E: p_country_code is null';
1043 FND_LOG.STRING(G_LEVEL_STATEMENT,
1044 G_MODULE_NAME || l_procedure_name,
1045 l_log_msg);
1046 END IF;
1047 RETURN;
1048 --RAISE FND_API.G_EXC_ERROR;
1049 ELSE
1050
1051 IF NOT is_territory_code_valid(p_country_code) then
1052 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053 --p_error_buffer := 'Invalid Country Code: '||p_country_code;
1054 --fnd_message.set_name('ZX','ZX_COUNTRY_CODE_INVALID');
1055 -- Logging Infra: YK: 3/10: Break point
1056 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1057 l_log_msg := 'Invalid Country Code: '||p_country_code;
1058 FND_LOG.STRING(G_LEVEL_STATEMENT,
1059 G_MODULE_NAME || l_procedure_name,
1060 l_log_msg);
1061 END IF;
1062 RETURN;
1063 END IF;
1064 OPEN c_country_default;
1065 FETCH c_country_default into l_intended_use, l_product_category_code,l_category_set;
1066
1067 -- Logging Infra: YK: 3/12: Break point
1068 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1069 l_log_msg := 'B: CUR: c_country_default: fetched: l_category_set=' || l_category_set;
1070 FND_LOG.STRING(G_LEVEL_STATEMENT,
1071 G_MODULE_NAME || l_procedure_name,
1072 l_log_msg);
1073 END IF;
1074
1075 IF c_country_default%NOTFOUND then
1076 -- p_return_status := FND_API.G_RET_STS_ERROR;
1077 --p_error_buffer := 'No defaults have been defined for the country';
1078 --Commented the code to set the message for Bug#9485527
1079 --fnd_message.set_name('ZX','ZX_COUNTRY_DEFFAULTS_NOT_EXIST');
1080 CLOSE c_country_default;
1081
1082 -- Logging Infra: YK: 3/12: Break point
1083 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1084 l_log_msg := 'B: CUR: c_country_default: notfound: country_code=' || p_country_code;
1085 FND_LOG.STRING(G_LEVEL_STATEMENT,
1086 G_MODULE_NAME || l_procedure_name,
1087 l_log_msg);
1088 END IF;
1089 return;
1090 -- RAISE FND_API.G_EXC_ERROR;
1091
1092 ELSE
1093 -- data found. Store in cache.
1094
1095 set_fc_country_def_cache_info(
1096 p_country_code => l_country_code,
1097 p_classification_type => 'INTENDED_USE',
1098 p_classification_code => l_intended_use);
1099
1100 set_fc_country_def_cache_info(
1101 p_country_code => l_country_code,
1102 p_classification_type => 'PRODUCT_CATEGORY',
1103 p_classification_code => l_product_category_code);
1104
1105 set_fc_country_def_cache_info(
1106 p_country_code => l_country_code,
1107 p_classification_type => 'PRIMARY_CATEGORY_SET',
1108 p_classification_code => l_category_set);
1109
1110 END IF;
1111
1112 CLOSE c_country_default;
1113
1114 IF l_category_set is NOT NULL THEN
1115 OPEN c_item_category;
1116 FETCH c_item_category into l_category_id;
1117
1118 -- Logging Infra: YK: 3/12: Break point
1119 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1120 l_log_msg := 'B: CUR: c_item_categoryt: fetched: l_category_id=' || l_category_id;
1121 FND_LOG.STRING(G_LEVEL_STATEMENT,
1122 G_MODULE_NAME || l_procedure_name,
1123 l_log_msg);
1124 END IF;
1125
1126 IF c_item_category%rowcount>1 THEN
1127 p_default_code := NULL;
1128 -- p_return_status := FND_API.G_RET_STS_ERROR;
1129 --p_error_buffer := 'Many categories assigned under same Category Set';
1130 --Commented the code to set the message for Bug#9485527
1131 --fnd_message.set_name('ZX','ZX_MANY_CATEG_ASSIGNED');
1132 CLOSE c_item_category;
1133
1134 -- Logging Infra: YK: 3/12: Break point:
1135 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1136 l_log_msg := 'E: CUR: c_item_category: rowcount > 1: category_set_id=' || l_category_set ||
1137 ', organization_id=' || p_org_id ||
1138 ', inventory_item_id='|| p_item_id;
1139 FND_LOG.STRING(G_LEVEL_STATEMENT,
1140 G_MODULE_NAME || l_procedure_name,
1141 l_log_msg);
1142 END IF;
1143
1144 RETURN;
1145 --RAISE FND_API.G_EXC_ERROR;
1146 END IF;
1147
1148 IF c_item_category%notfound THEN
1149 -- CLOSE c_item_category; --bug#9855187 --
1150 -- Get default value from the Category Set
1151 OPEN c_default_category;
1152 FETCH c_default_category into l_category_id;
1153
1154 -- Logging Infra: YK: 3/12: Break point
1155 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1156 l_log_msg := 'B: CUR: c_default_category: fetched: l_category_id=' || l_category_id;
1157 FND_LOG.STRING(G_LEVEL_STATEMENT,
1158 G_MODULE_NAME || l_procedure_name,
1159 l_log_msg);
1160 END IF;
1161
1162 IF c_default_category%notfound THEN
1163 CLOSE c_default_category ;
1164 CLOSE c_item_category;
1165 p_default_code := NULL;
1166 -- p_return_status := FND_API.G_RET_STS_ERROR;
1167 --p_error_buffer := 'No default value could be derived';
1168 --Commented the code to set the message for Bug#9485527
1169 --fnd_message.set_name('ZX','ZX_NO_DEFAULT_DERIVED');
1170
1171 -- Logging Infra: YK: 3/12: Break point:
1172 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1173 l_log_msg := 'E: CUR: c_default_category: notfound: category_set_id=' || l_category_set;
1174 FND_LOG.STRING(G_LEVEL_STATEMENT,
1175 G_MODULE_NAME || l_procedure_name,
1176 l_log_msg);
1177 END IF;
1178 return;
1179 --RAISE FND_API.G_EXC_ERROR;
1180 END IF;
1181 CLOSE c_default_category ;
1182 END IF;
1183
1184 CLOSE c_item_category;
1185
1186 OPEN c_category_code;
1187 FETCH c_category_code into l_category_code;
1188
1189 -- Logging Infra: YK: 3/12: Break point
1190 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1191 l_log_msg := 'B: CUR: c__category_code: fetched: l_category_code=' || l_category_code;
1192 FND_LOG.STRING(G_LEVEL_STATEMENT,
1193 G_MODULE_NAME || l_procedure_name,
1194 l_log_msg);
1195 END IF;
1196
1197 IF c_category_code%NOTFOUND THEN
1198 p_default_code := NULL;
1199 --p_return_status := FND_API.G_RET_STS_ERROR;
1200 --p_error_buffer := 'Classification Category Code does not exists';
1201 --Commented the code to set the message for Bug#9485527
1202 --fnd_message.set_name('ZX','ZX_FC_CATEG_NOT_EXIST');
1203
1204 CLOSE c_category_code;
1205
1206 -- Logging Infra: YK: 3/12: Break point:
1207 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1208 l_log_msg := 'E: CUR: c_category_code: notfound: category_set_id=' || l_category_set ||
1209 ', category_id=' || l_category_id;
1210 FND_LOG.STRING(G_LEVEL_STATEMENT,
1211 G_MODULE_NAME || l_procedure_name,
1212 l_log_msg);
1213 END IF;
1214
1215 RETURN;
1216 -- RAISE FND_API.G_EXC_ERROR;
1217 END IF;
1218
1219 p_default_code := l_category_code;
1220 CLOSE c_category_code;
1221
1222 END IF; -- l_category_set
1223
1224 END IF;
1225
1226 -- Logging Infra: YK: 3/12: Put output value here
1227 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1228 l_log_msg := 'R: p_default_code=' || p_default_code;
1229 FND_LOG.STRING(G_LEVEL_STATEMENT,
1230 G_MODULE_NAME || l_procedure_name,
1231 l_log_msg);
1232 END IF;
1233
1234 -- Logging Infra: YK: 3/12: Procedure level
1235 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1236 l_log_msg := l_procedure_name||'(-)';
1237 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1238 END IF;
1239
1240 EXCEPTION
1241 WHEN INVALID_CURSOR THEN
1242 p_return_status := FND_API.G_RET_STS_ERROR;
1243 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1244 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1245
1246 IF c_country_default%ISOPEN THEN CLOSE c_country_default; END IF;
1247 IF c_item_category%ISOPEN THEN CLOSE c_item_category; END IF;
1248 IF c_default_category%ISOPEN THEN CLOSE c_default_category; END IF;
1249 IF c_category_code%ISOPEN THEN CLOSE c_category_code; END IF;
1250
1251 -- Logging Infra: YK: 3/12
1252 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1253 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
1254 END IF;
1255
1256 WHEN FND_API.G_EXC_ERROR THEN
1257 p_return_status := FND_API.G_RET_STS_ERROR;
1258 IF c_country_default%ISOPEN THEN CLOSE c_country_default; END IF;
1259 IF c_item_category%ISOPEN THEN CLOSE c_item_category; END IF;
1260 IF c_default_category%ISOPEN THEN CLOSE c_default_category; END IF;
1261 IF c_category_code%ISOPEN THEN CLOSE c_category_code; END IF;
1262
1263
1264 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1265 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
1266 END IF;
1267
1268
1269 WHEN OTHERS THEN
1270 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1271 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1272 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1273
1274 IF c_country_default%ISOPEN THEN CLOSE c_country_default; END IF;
1275 IF c_item_category%ISOPEN THEN CLOSE c_item_category; END IF;
1276 IF c_default_category%ISOPEN THEN CLOSE c_default_category; END IF;
1277 IF c_category_code%ISOPEN THEN CLOSE c_category_code; END IF;
1278
1279 -- Logging Infra: YK: 3/12
1280 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1281 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
1282 END IF;
1283
1284 END GET_DEFAULT_PRODUCT_CLASSIF;
1285
1286
1287 /*==============================================================================+
1288 | Function: ZX_GET_PROD_CATEG |
1289 | Description: This function returns passed product fc if inventory is installed |
1290 | If not , then return the passed product category |
1291 | Classification migration |
1292 +=============================================================================*/
1293
1294 FUNCTION ZX_GET_PROD_CATEG (p_product_category IN OUT NOCOPY VARCHAR2,
1295 p_product_fc IN OUT NOCOPY VARCHAR2,
1296 p_country_code IN VARCHAR2) RETURN VARCHAR2 IS
1297
1298 Cursor c_prod_category is
1299 Select product_categ_default
1300 From ZX_FC_COUNTRY_DEFAULTS
1301 Where country_code = p_country_code;
1302
1303 l_product_categ_default zx_fc_country_defaults.product_categ_default%TYPE;
1304 BEGIN
1305
1306 arp_util_tax.debug(' ZX_GET_PROD_CATEG .. (+) ' );
1307
1308 If IS_INV_INSTALLED then
1309
1310 return(p_product_fc);
1311 Else
1312 open c_prod_category;
1313 fetch c_prod_category into l_product_categ_default;
1314 if c_prod_category%notfound then
1315 close c_prod_category;
1316 return(null);
1317 else
1318 close c_prod_category;
1319 return(l_product_categ_default);
1320 end if;
1321 End if;
1322
1323 arp_util_tax.debug(' ZX_GET_PROD_CATEG .. (-) ' );
1324
1325 END ZX_GET_PROD_CATEG;
1326
1327 /*===========================================================================+
1328 | Function: IS_INV_INSTALLED |
1329 | Description: This function returns true if inventory is installed |
1330 | This API is again used by other procedures in Fiscal |
1331 | Classification migration |
1332 | |
1333 | |
1334 | |
1335 | |
1336 | ARGUMENTS : |
1337 | |
1338 | |
1339 | NOTES |
1340 | |
1341 | |
1342 | |
1343 | History |
1344 | zmohiudd Created |
1345 | |
1346 | |
1347 +===========================================================================*/
1348
1349
1350 FUNCTION IS_INV_INSTALLED RETURN BOOLEAN IS
1351
1352 l_status fnd_product_installations.STATUS%type;
1353 l_db_status fnd_product_installations.DB_STATUS%type;
1354
1355
1356 BEGIN
1357
1358 arp_util_tax.debug(' IS_INV_INSTALLED .. (+) ' );
1359
1360 SELECT STATUS, DB_STATUS
1361 INTO l_status, l_db_status
1362 FROM fnd_product_installations
1363 WHERE APPLICATION_ID = '401';
1364
1365 IF (nvl(l_status,'N') = 'N' or nvl(l_db_status,'N') = 'N') THEN
1366 return FALSE;
1367 ELSE
1368 return TRUE;
1369 END IF;
1370 arp_util_tax.debug(' IS_INV_INSTALLED .. (-) ' );
1371
1372 END IS_INV_INSTALLED ;
1373
1374 /**************************************************************************
1375 * *
1376 * Name : Get_Default_Tax_Reg *
1377 * Purpose : Returns the Default Registration Number for a Given Party *
1378 * Logic : In case there is tax registration mark as default *
1379 * the function will return the registration number *
1380 * associated to that record. Second case the function will *
1381 * look for the registration row with null regime *
1382 * (migrated records) *
1383 * Parameters : P_Party_ID ------------ P_Party_Type *
1384 * Party_Id Third Party *
1385 * Party_Site_Id Third Party Site *
1386 * Party_ID Establishments *
1387 * *
1388 * P_Effective_Date Default Sysdate *
1389 * *
1390 * *
1391 **************************************************************************/
1392 FUNCTION Get_Default_Tax_Reg
1393 (P_Party_ID IN zx_party_tax_profile.party_id%Type,
1394 P_Party_Type IN zx_party_tax_profile.party_type_code%Type,
1395 P_Effective_Date IN zx_registrations.effective_from%Type,
1396 x_return_status OUT NOCOPY VARCHAR2
1397 )
1398 RETURN Varchar2
1399 IS
1400 -- Logging Infra
1401 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Default_Tax_Reg';
1402 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1403 l_effective_date zx_registrations.effective_from%Type;
1404 --p_error_buffer varchar2(200);
1405
1406 Cursor Default_Reg IS
1407 Select NVL(reg.registration_number, PTP.rep_registration_number) registration_number
1408 From zx_registrations reg
1409 ,zx_party_tax_profile ptp
1410 Where ptp.party_id = p_party_id
1411 AND ptp.party_type_code = p_party_type
1412 AND ptp.party_tax_profile_id = reg.party_tax_profile_id
1413 AND reg.default_registration_flag = 'Y'
1414 AND l_effective_date >= effective_from
1415 AND (l_effective_date <= effective_to OR effective_to IS NULL);
1416
1417 Cursor Reporting_Reg IS
1418 Select REP_REGISTRATION_NUMBER
1419 From zx_party_tax_profile ptp
1420 Where ptp.party_id = p_party_id
1421 AND ptp.party_type_code = p_party_type;
1422
1423 Begin
1424 -- Logging Infra: Setting up runtime level
1425 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1426
1427 -- Logging Infra: Procedure level
1428 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1429 l_log_msg := l_procedure_name||'(+)';
1430 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1431 END IF;
1432
1433 -- Logging Infra: Statement level
1434 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1435 l_log_msg := 'Parameters ';
1436 l_log_msg := l_log_msg||'P_Party_Id: '||to_char(p_party_id);
1437 l_log_msg := l_log_msg||'P_Party_Type: '||p_party_type;
1438 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1439 END IF;
1440 -- Logging Infra: Statement level
1441
1442 -- Initialize Return Status and Error Buffer
1443 --
1444 x_return_status := FND_API.G_RET_STS_SUCCESS;
1445
1446 -- Set initial value for effective date in case it comes null
1447 IF p_effective_date is null Then
1448 l_Effective_Date:= sysdate;
1449 Else
1450 l_Effective_Date:= p_effective_date;
1451 End if;
1452 --
1453 -- Always Party_ID and Party_Type parameters cannot be NULL
1454 --
1455 IF P_Party_Id IS NULL OR P_Party_Type IS NULL THEN
1456
1457 -- Logging Infra: Statement level
1458 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1459 l_log_msg := 'Parameter P_Party_ID and/or Party_Type are null ';
1460 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1461 END IF;
1462 -- Logging Infra: Statement level
1463
1464 --x_return_status := FND_API.G_RET_STS_ERROR;
1465 --return (NULL);
1466 fnd_message.set_name('ZX','ZX_PTP_ID_NOT_EXIST');
1467 RAISE FND_API.G_EXC_ERROR;
1468
1469 ELSE
1470 -- Try Default Registration First
1471 For Regis IN Default_Reg Loop
1472 Return (Regis.registration_number);
1473 END LOOP;
1474
1475 -- Checking at PTP level
1476 For Regis IN Reporting_Reg Loop
1477 Return (Regis.rep_registration_number);
1478 END LOOP;
1479 END IF;
1480
1481 -- Logging Infra: Procedure level
1482 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1483 l_log_msg := l_procedure_name||'(-)';
1484 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END', l_log_msg);
1485 END IF;
1486 return(null);
1487 EXCEPTION
1488 WHEN NO_DATA_FOUND THEN
1489 /*
1490 --Return(Null);
1491 x_return_status := FND_API.G_RET_STS_ERROR;
1492 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1493 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1494
1495 -- Logging Infra: Statement level
1496 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1497 l_log_msg := 'NO DATA FOUND EXCEP - Error Message: '||SQLERRM;
1498 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1499 END IF;
1500 -- Logging Infra: Statement level
1501 */
1502 NULL;
1503 WHEN INVALID_CURSOR THEN
1504 x_return_status := FND_API.G_RET_STS_ERROR;
1505 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1506 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1507
1508 WHEN FND_API.G_EXC_ERROR THEN
1509 x_return_status := FND_API.G_RET_STS_ERROR;
1510 -- Logging Infra: Statement level
1511 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1512 l_log_msg := 'NO DATA FOUND EXCEP - Error Message: '||SQLERRM;
1513 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1514 END IF;
1515
1516 WHEN OTHERS THEN
1517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1518 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1519 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1520 -- Logging Infra: Statement level
1521 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1522 l_log_msg := 'Error Message: '||SQLERRM;
1523 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1524 END IF;
1525 -- Logging Infra: Statement level
1526
1527 End Get_Default_Tax_Reg;
1528
1529
1530
1531 FUNCTION get_le_from_tax_registration
1532 (
1533 x_return_status OUT NOCOPY VARCHAR2,
1534 p_registration_num IN ZX_REGISTRATIONS.Registration_Number%type,
1535 p_effective_date IN ZX_REGISTRATIONS.effective_from%type,
1536 p_country IN ZX_PARTY_TAX_PROFILE.Country_code%type
1537 ) RETURN Number IS
1538 l_legal_entity_id NUMBER;
1539 BEGIN
1540 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1541 IF p_registration_num IS NOT NULL THEN
1542 SELECT distinct xle.legal_entity_id
1543 INTO l_legal_entity_id
1544 from zx_registrations tr, zx_party_tax_profile ptp, xle_etb_profiles xle
1545 where tr.registration_number = p_registration_num
1546 and tr.party_tax_profile_id = ptp.party_tax_profile_id
1547 and ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
1548 and ptp.party_id = xle.party_id;
1549 ELSE
1550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1551 --FND_MESSAGE.Set_Name ('ZX','ZX_REG_NUM_MANDATORY');
1552 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1553 FND_LOG.STRING(G_LEVEL_STATEMENT,
1554 G_MODULE_NAME || 'get_le_from_tax_registration',
1555 'Registration Number is mandatory but it is null.');
1556 END IF;
1557 END IF;
1558 return l_legal_entity_id;
1559 EXCEPTION WHEN NO_DATA_FOUND THEN
1560 x_return_status := FND_API.G_RET_STS_ERROR;
1561 FND_MESSAGE.Set_Name ('ZX','ZX_REG_LE_NOT_FOUND');
1562 --
1563 WHEN TOO_MANY_ROWS THEN
1564 x_return_status := FND_API.G_RET_STS_ERROR;
1565 FND_MESSAGE.Set_Name ('ZX','ZX_REG_MANY_LEGAL_ENTITY');
1566 --
1567 END get_le_from_tax_registration;
1568
1569 PROCEDURE get_fc_country_def_cache_info (
1570 p_country_code IN fnd_territories.territory_code%TYPE,
1571 p_classification_type IN varchar2,
1572 x_classification_rec OUT NOCOPY ZX_GLOBAL_STRUCTURES_PKG.fc_country_def_val_rec_type,
1573 x_found_in_cache OUT NOCOPY BOOLEAN,
1574 x_return_status OUT NOCOPY VARCHAR2,
1575 x_error_buffer OUT NOCOPY VARCHAR2) is
1576
1577 l_index BINARY_INTEGER;
1578
1579 BEGIN
1580
1581 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1582 x_return_status := FND_API.G_RET_STS_SUCCESS;
1583 x_found_in_cache := FALSE;
1584
1585 IF (g_level_statement >= g_current_runtime_level ) THEN
1586 FND_LOG.STRING(g_level_statement, 'ZX.PLSQL.ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info.BEGIN',
1587 'ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info(+)');
1588 END IF;
1589
1590
1591 l_index := dbms_utility.get_hash_value(
1592 p_country_code||p_classification_type,
1593 1,
1594 8192);
1595 --
1596 -- first check if the status info is available from the cache
1597 --
1598
1599 IF ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL.EXISTS(l_index)
1600 THEN
1601 IF (g_level_statement >= g_current_runtime_level ) THEN
1602 FND_LOG.STRING(g_level_statement,
1603 'ZX.PLSQL.LSQL.ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info',
1604 'Default Classification type '||p_classification_type||
1605 ' for country code '||p_country_code||' from cache, at index = ' || to_char(l_index));
1606 END IF;
1607 x_found_in_cache := TRUE;
1608 x_classification_rec := ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL(l_index);
1609
1610 ELSE
1611 IF (g_level_statement >= g_current_runtime_level ) THEN
1612 FND_LOG.STRING(g_level_statement,
1613 'ZX.PLSQL.LSQL.ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info',
1614 'Default Classification type '||p_classification_type||
1615 ' for country code '||p_country_code||' not found in cache ');
1616 END IF;
1617
1618 END IF;
1619
1620 IF (g_level_statement >= g_current_runtime_level ) THEN
1621 FND_LOG.STRING(g_level_statement, 'ZX.PLSQL.ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info.BEGIN',
1622 'ZX_TCM_EXT_SERVICES_PUB.get_fc_country_def_cache_info(-)');
1623 END IF;
1624
1625 END get_fc_country_def_cache_info;
1626
1627 PROCEDURE set_fc_country_def_cache_info(
1628 p_country_code IN fnd_territories.territory_code%TYPE,
1629 p_classification_type IN varchar2,
1630 p_classification_code IN varchar2)
1631 is
1632 l_tbl_index binary_integer;
1633 BEGIN
1634
1635 -- set the value in cache
1636 l_tbl_index := dbms_utility.get_hash_value(
1637 p_country_code||p_classification_type,
1638 1,
1639 8192);
1640
1641 ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL(l_tbl_index).country_code := p_country_code;
1642 ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL(l_tbl_index).fc_type := p_classification_type;
1643 ZX_GLOBAL_STRUCTURES_PKG.FC_COUNTRY_DEF_VAL_TBL(l_tbl_index).fc_default_value := p_classification_code;
1644
1645 END set_fc_country_def_cache_info;
1646
1647 FUNCTION is_territory_code_valid(p_country_code IN VARCHAR2)
1648 RETURN BOOLEAN is
1649 l_country_index binary_integer;
1650 l_territory_code fnd_territories.territory_code%type;
1651 BEGIN
1652 l_country_index := dbms_utility.get_hash_value(P_COUNTRY_CODE, 1, 8192);
1653 IF ZX_GLOBAL_STRUCTURES_PKG.G_TERRITORY_TBL.exists(l_country_index) then
1654 RETURN TRUE;
1655 ELSE
1656 BEGIN
1657 select TERRITORY_CODE into l_territory_code
1658 FROM FND_TERRITORIES
1659 WHERE TERRITORY_CODE = p_country_code;
1660
1661 ZX_GLOBAL_STRUCTURES_PKG.G_TERRITORY_TBL(l_country_index) := l_territory_code;
1662 return TRUE;
1663
1664 EXCEPTION
1665 WHEN NO_DATA_FOUND then
1666 return FALSE;
1667 END;
1668 END IF;
1669 END is_territory_code_valid;
1670
1671 END ZX_TCM_EXT_SERVICES_PUB;