[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_CONTROL_PKG
Source
1 PACKAGE BODY ZX_TCM_CONTROL_PKG AS
2 /* $Header: zxccontrolb.pls 120.93 2011/11/08 07:02:20 ssohal 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(30) := 'ZX.PLSQL.ZX_TCM_CONTROL_PKG.';
13 -- Logging Infra
14
15 C_TAX_REGIME_CODE_DUMMY CONSTANT VARCHAR2(30) := '@#$%^&';
16 C_TAX_DUMMY CONSTANT VARCHAR2(30) := '@#$%^&';
17 C_JURISDICTION_CODE_DUMMY CONSTANT VARCHAR2(30) := '@#$%^&';
18
19 PROCEDURE GET_FISCAL_CLASSIFICATION(
20 p_fsc_rec IN OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_FISCAL_CLASS_INFO_REC,
21 p_return_status OUT NOCOPY VARCHAR2) IS
22
23 /* ------------------------------------------------------------------------------
24
25 A Procedure to return Fiscal Classifications allocated to a Party,Party Site,
26 Product or Transaction
27 Parameter p_fsc_rec is a record structure.
28
29 */
30
31 l_status varchar2(1);
32 l_db_status varchar2(1);
33 l_reg_fscType_flag varchar2(1);
34 l_inventory_set varchar2(1);
35 l_category_set_id mtl_category_sets_b.category_set_id%type;
36 l_category_id mtl_categories_b.category_id%type;
37 l_structure_id mtl_category_sets_b.structure_id%type;
38 l_allocated_flag varchar2(15);
39 l_tca_class_category_code hz_class_categories.class_category%type;
40 l_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type;
41 l_party_id zx_party_tax_profile.party_id%type;
42 l_table_owner zx_fc_types_b.Owner_Table_Code%type;
43 l_table_id zx_fc_types_b.owner_id_char%type;
44 l_class_code fnd_lookup_values.lookup_code%type;
45 l_classification_type_code zx_fc_types_b.classification_type_code%type;
46 l_effective_from date;
47 l_effective_to date;
48 l_Party_Type_Code zx_party_tax_profile.Party_Type_Code%type;
49 l_le_status varchar2(30);
50 l_le_other_fc_status varchar2(30);
51 l_xle_legal_entity xle_utilities_grp.Legal_Entity_Tbl_Type;
52 l_xle_establishment xle_utilities_grp.Establishment_Tbl_Type;
53
54 l_RETURN_STATUS VARCHAR2(30);
55 l_MSG_COUNT NUMBER(15);
56 l_MSG_DATA VARCHAR2(30);
57
58
59 -- Logging Infra:
60 l_procedure_name CONSTANT VARCHAR2(30) := 'get_fiscal_classification';
61 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
62
63 l_ptp_id NUMBER;
64
65 -- Validate if Fiscal Type has been associated to regime
66 cursor c_regime_assoc is
67 select 'Y'
68 from zx_fc_types_reg_assoc
69 where classification_type_code = p_fsc_rec.classification_type
70 and tax_regime_code = p_fsc_rec.tax_regime_code;
71
72 -- Check also for regimes above.
73 cursor c_parent_regime_assoc is
74 select unique 'Y'
75 from zx_fc_types_reg_assoc
76 where classification_type_code = p_fsc_rec.classification_type
77 and tax_regime_code IN (
78 select regime_code
79 from zx_regime_relations
80 connect by prior parent_regime_code = regime_code
81 start with regime_code = p_fsc_rec.tax_regime_code );
82
83 /* Cursors for Inventory Categories related */
84 -- get category set id
85 cursor c_inventory_set is
86 Select owner_id_num
87 from zx_fc_types_b
88 where classification_type_code = p_fsc_rec.classification_type;
89
90 -- get structure id for the category set
91 cursor c_inventory_structure is
92 select structure_id
93 from mtl_category_sets_b
94 where category_set_id = l_category_set_id;
95
96 -- get the category id for the category code
97 cursor c_category is
98 select mtl.category_id
99 from mtl_categories_b_kfv mtl,
100 fnd_id_flex_structures flex
101 where mtl.structure_id = l_structure_id
102 and flex.ID_FLEX_NUM = mtl.STRUCTURE_ID
103 and flex.APPLICATION_ID = 401
104 and flex.ID_FLEX_CODE = 'MCAT'
105 and replace (mtl.concatenated_segments,flex.concatenated_segment_delimiter,'')= p_fsc_rec.condition_value;
106
107
108 -- get the allocation
109 cursor c_item_category is
110 select 'ALLOCATED'
111 from mtl_item_categories
112 where category_set_id = l_category_set_id
113 and category_id = l_category_id
114 and organization_id = p_fsc_rec.item_org_id
115 and inventory_item_id = p_fsc_rec.classified_entity_id;
116
117 -- get allocation for a child when rule is on a parent level
118 cursor c_item_category_child is
119 select 'ALLOCATED'
120 from mtl_item_categories mit
121 where mit.category_set_id = l_category_set_id
122 and mit.organization_id = p_fsc_rec.item_org_id
123 and mit.inventory_item_id = p_fsc_rec.classified_entity_id
124 and exists (
125 select mtl.category_id
126 from mtl_categories_b_kfv mtl,
127 fnd_id_flex_structures flex,
128 ( select start_position, num_characters
129 from zx_fc_types_b
130 where classification_type_code = p_fsc_rec.classification_type) fc
131 where mtl.structure_id = l_structure_id
132 and flex.ID_FLEX_NUM = mtl.STRUCTURE_ID
133 and flex.APPLICATION_ID = 401
134 and flex.ID_FLEX_CODE = 'MCAT'
135 and mtl.category_id = mit.category_id
136 and substr(replace (mtl.concatenated_segments,flex.concatenated_segment_delimiter,''),fc.start_position,fc.num_characters) = p_fsc_rec.condition_value);
137
138 /* Cursors for TCA Classification related */
139
140 -- get TCA Class Category id
141 cursor c_tca_class_category is
142 Select owner_id_char
143 from zx_fc_types_b
144 where classification_type_code = p_fsc_rec.classification_type;
145
146 cursor c_party_tax_profile_id is
147 Select party_id ,Party_Type_Code
148 from zx_party_tax_profile
149 where party_tax_profile_id = p_fsc_rec.classified_entity_id;
150
151 cursor c_class_code is
152 select class_code
153 from hz_class_code_denorm
154 where class_category = l_tca_class_category_code
155 and concat_class_code = p_fsc_rec.condition_value;
156
157 cursor c_party_category is
158 select 'ALLOCATED',start_date_active,end_date_active
159 from hz_code_assignments
160 where class_category = l_tca_class_category_code
161 and class_code = l_class_code
162 and owner_table_name = l_table_owner
163 and owner_table_id = l_table_id
164 and p_fsc_rec.tax_determine_date between start_date_active and nvl(end_date_active,p_fsc_rec.tax_determine_date);
165
166 -- Begin Bug Fix 5528805
167
168 cursor c_party_category_multi_level is
169 select 'ALLOCATED',start_date_active,end_date_active
170 from hz_code_assignments
171 where class_category = l_tca_class_category_code
172 and owner_table_name = l_table_owner
173 and owner_table_id = l_table_id
174 and p_fsc_rec.tax_determine_date between start_date_active and nvl(end_date_active,p_fsc_rec.tax_determine_date)
175 and class_code in (select class_code from hz_class_code_denorm
176 where class_category = l_tca_class_category_code
177 and SUBSTR(concat_class_code , 0, LENGTH(p_fsc_rec.condition_value)) = p_fsc_rec.condition_value
178 );
179
180 l_c_party_category_not_found VARCHAR2(1) := 'N';
181
182 -- End Bug Fix 5528805
183
184 cursor c_pty_fc_assgn_exists is
185 select 'EXISTS'
186 from hz_code_assignments
187 where class_category = l_tca_class_category_code
188 and owner_table_name = l_table_owner
189 and owner_table_id = l_table_id
190 and p_fsc_rec.tax_determine_date between start_date_active and nvl(end_date_active,p_fsc_rec.tax_determine_date)
191 and rownum = 1;
192
193 -- Cursors used for Transaction Fiscal Classification
194 cursor c_classification_type_code is
195 Select classification_type_code
196 from zx_fc_types_b
197 where classification_type_code = p_fsc_rec.classification_type;
198
199 /*
200 Cursor c_trxbizcat_fiscalclass is
201 select 'ALLOCATED',effective_from, effective_to
202 from zx_fc_codes_categ_assoc
203 where classification_type_code = NVL(FcType.OWNER_ID_CHAR,p_fsc_rec.classification_type)
204 and FcType
205 and Classification_Code_Concat = p_fsc_rec.condition_value
206 and trans_business_categ_type_code = 'TRX_BUSINESS_CATEGORY'
207 and Trans_Business_Categ_Concat = p_fsc_rec.event_class_code
208 and p_fsc_rec.tax_determine_date between effective_from and nvl(effective_to,p_fsc_rec.tax_determine_date);
209 */
210
211 Cursor c_trxbizcat_fiscalclass is
212 select 'ALLOCATED',assoc.effective_from, assoc.effective_to
213 from zx_fc_codes_categ_assoc assoc,
214 zx_fc_types_b fctypes,
215 zx_fc_codes_denorm_b denorm
216 where assoc.classification_type_code = nvl(fctypes.owner_id_char,p_fsc_rec.classification_type)
217 and fctypes.classification_type_code = p_fsc_rec.classification_type
218 and assoc.Classification_Code_Concat = p_fsc_rec.condition_value
219 and assoc.trans_business_categ_type_code = 'TRX_BUSINESS_CATEGORY'
220 and denorm.CONCAT_CLASSIF_CODE = p_fsc_rec.condition_value
221 and denorm.CLASSIFICATION_CODE_LEVEL = fctypes.CLASSIFICATION_TYPE_LEVEL_CODE
222 and denorm.LANGUAGE = USERENV('LANG')
223 and assoc.Trans_Business_Categ_Concat = p_fsc_rec.event_class_code
224 and p_fsc_rec.tax_determine_date between assoc.effective_from and nvl(assoc.effective_to,p_fsc_rec.tax_determine_date);
225
226
227 -- Traverse to check for association to higher levels on Trx Biz Category
228 /*
229 Cursor c_parent_trxbizcat_fiscalclass is
230 select 'ALLOCATED',effective_from, effective_to
231 from zx_fc_codes_categ_assoc
232 where classification_type_code = p_fsc_rec.classification_type
233 and Classification_Code_Concat = p_fsc_rec.condition_value
234 and trans_business_categ_type_code = 'TRX_BUSINESS_CATEGORY'
235 and instr(p_fsc_rec.event_class_code,Trans_Business_Categ_Concat) <> 0
236 and p_fsc_rec.tax_determine_date between effective_from and nvl(effective_to,p_fsc_rec.tax_determine_date);
237 */
238
239 Cursor c_parent_trxbizcat_fiscalclass is
240 select 'ALLOCATED',assoc.effective_from, assoc.effective_to
241 from zx_fc_codes_categ_assoc assoc,
242 zx_fc_types_b fctypes,
243 zx_fc_codes_denorm_b denorm
244 where assoc.classification_type_code = nvl(fctypes.owner_id_char,p_fsc_rec.classification_type)
245 and fctypes.classification_type_code = p_fsc_rec.classification_type
246 and assoc.Classification_Code_Concat = p_fsc_rec.condition_value
247 and denorm.CONCAT_CLASSIF_CODE = p_fsc_rec.condition_value
248 and denorm.CLASSIFICATION_CODE_LEVEL = fctypes.CLASSIFICATION_TYPE_LEVEL_CODE
249 and denorm.LANGUAGE = USERENV('LANG')
250 and assoc.trans_business_categ_type_code = 'TRX_BUSINESS_CATEGORY'
251 and instr(p_fsc_rec.event_class_code,assoc.Trans_Business_Categ_Concat ) <> 0
252 and p_fsc_rec.tax_determine_date between assoc.effective_from and nvl(assoc.effective_to,p_fsc_rec.tax_determine_date);
253
254
255
256 -- Cursor for LE information
257 CURSOR c_xle_activity_code IS
258 SELECT 'ALLOCATED', le_effective_from, le_effective_to
259 FROM xle_firstparty_information_v
260 WHERE party_id = l_party_id
261 AND activity_category = l_tca_class_category_code
262 AND activity_code = l_class_code
263 -- AND p_fsc_rec.tax_determine_date between le_effective_from and nvl(le_effective_to,p_fsc_rec.tax_determine_date);
264 AND p_fsc_rec.tax_determine_date between NVL(le_effective_from,p_fsc_rec.tax_determine_date) and nvl(le_effective_to,p_fsc_rec.tax_determine_date);
265
266 CURSOR c_xle_fc_assgn_exists IS
267 SELECT 'EXISTS'
268 FROM xle_firstparty_information_v
269 WHERE party_id = l_party_id
270 AND activity_category = l_tca_class_category_code
271 -- AND p_fsc_rec.tax_determine_date between le_effective_from and nvl(le_effective_to,p_fsc_rec.tax_determine_date)
272 AND p_fsc_rec.tax_determine_date between NVL(le_effective_from,p_fsc_rec.tax_determine_date) and nvl(le_effective_to,p_fsc_rec.tax_determine_date)
273 AND rownum = 1;
274
275 BEGIN
276 -- Logging Infra: Setting up runtime level
277 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
278
279 -- Logging Infra: Procedure level
280 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
281 l_log_msg := l_procedure_name||'(+)';
282 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
283 END IF;
284
285 -- Logging Infra: YK: 3/5: Break point
286 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
287 l_log_msg := 'B: p_fsc_rec.classification_category='|| p_fsc_rec.classification_category;
288 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
289 END IF;
290 --Bug fix 4774215 Case 1.Return Status must be initialized.
291 P_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
292 --End of Bug fix 4774215 Case 1.
293 -- arp_util_tax.debug('classification_type = ' || p_fsc_rec.classification_type);
294 -- arp_util_tax.debug('regime_code = ' || p_fsc_rec.tax_regime_code);
295
296 -- Validate parameters
297
298 IF p_fsc_rec.classification_category = 'PRODUCT_FISCAL_CLASS' THEN
299 -- 1. Validate the Fiscal Classification Type has been associated to the regime or to parent above
300 -- 2. Derive the Inventory Category classification.
301 -- 3. Get the Item Category for the Product within the given organization
302
303 -- Check if inventory is installed
304 IF zx_global_Structures_pkg.g_inventory_installed_flag IS NULL THEN
305 BEGIN
306 SELECT STATUS, DB_STATUS
307 INTO l_status, l_db_status
308 FROM fnd_product_installations
309 WHERE APPLICATION_ID = '401';
310 EXCEPTION
311 WHEN OTHERS THEN
312 -- Logging Infra: YK: 3/5
313 -- The following original code is commented out.
314 -- NULL;
315
316 -- Logging Infra: YK: 3/5: Statement level
317 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
318 l_log_msg := 'E: EXC: OTHERS: select fnd_product_installations: '|| SQLCODE||': '||SQLERRM;
319 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
320 END IF;
321 END;
322
323 IF (nvl(l_status,'N') = 'N' or nvl(l_db_status,'N') = 'N') THEN
324 zx_global_Structures_pkg.g_inventory_installed_flag := 'N';
325 ELSE
326 zx_global_Structures_pkg.g_inventory_installed_flag := 'Y';
327 END IF;
328 END IF; -- check inventory installed
329
330 IF zx_global_Structures_pkg.g_inventory_installed_flag = 'N' THEN
331 --p_return_status:=FND_API.G_RET_STS_ERROR;
332 --p_error_buffer:='Inventory is not enabled';
333 fnd_message.set_name('ZX','ZX_INV_NOT_ENABLED');
334 p_fsc_rec.fsc_code:= null;
335
336 -- Logging Infra: YK: 3/5: Statement level: "E" means "E"rror
337 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
338 l_log_msg := 'E: SEL fnd_product_installations: inventory not enabled: l_status='|| l_status ||
339 ', l_db_status=' || l_db_status;
340 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
341 END IF;
342
343 --return;
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346
347 OPEN c_regime_assoc;
348 FETCH c_regime_assoc INTO l_reg_fscType_flag;
349
350 -- Logging Infra: YK: 3/5: Break point
351 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
352 l_log_msg := 'B: CUR: c_regime_assoc: fetched: l_reg_fsctype_flag=' || l_reg_fsctype_flag;
353 FND_LOG.STRING(G_LEVEL_STATEMENT,
354 G_MODULE_NAME || l_procedure_name,
355 l_log_msg);
356 END IF;
357
358 --1:
359 IF c_regime_assoc%notfound THEN
360 close c_regime_assoc;
361
362 OPEN c_parent_regime_assoc;
363 FETCH c_parent_regime_assoc INTO l_reg_fscType_flag;
364
365 -- Logging Infra: YK: 3/5: Break point
366 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
367 l_log_msg := 'B: CUR: c_parent_regime_assoc: fetched: l_reg_fsctype_flag=' || l_reg_fsctype_flag;
368 FND_LOG.STRING(G_LEVEL_STATEMENT,
369 G_MODULE_NAME || l_procedure_name,
370 l_log_msg);
371 END IF;
372
373 IF c_parent_regime_assoc%NOTFOUND THEN
374 p_return_status:= FND_API.G_RET_STS_SUCCESS;
375 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
376 CLOSE c_parent_regime_assoc;
377
378 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
379 l_log_msg := 'Regime for the given Fiscal Type is not valid ';
380 FND_LOG.STRING(G_LEVEL_STATEMENT,
381 G_MODULE_NAME || l_procedure_name,
382 l_log_msg);
383 END IF;
384 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
385 RETURN;
386 END IF;
387 ELSE
388 CLOSE c_regime_assoc;
389 END IF;
390
391 -- 2:
392 OPEN c_inventory_set;
393 FETCH c_inventory_set INTO l_category_set_id;
394
395 -- Logging Infra: YK: 3/5: Break point l_category_set_id
396 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
397 l_log_msg := 'B: CUR: c_inventory_set: fetched: l_category_set_id='||l_category_set_id;
398 FND_LOG.STRING(G_LEVEL_STATEMENT,
399 G_MODULE_NAME || l_procedure_name,
400 l_log_msg);
401 END IF;
402
403 IF c_inventory_set%NOTFOUND THEN
404 p_return_status:= FND_API.G_RET_STS_SUCCESS;
405 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
406 --p_error_buffer:='Fiscal Type Code does not exits';
407 --fnd_message.set_name('ZX','ZX_FC_TYPE_NOT_EXIST');
408 CLOSE c_inventory_set;
409 --return;
410 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
411 l_log_msg :='Fiscal Type Code does not exits';
412 FND_LOG.STRING(G_LEVEL_STATEMENT,
413 G_MODULE_NAME || l_procedure_name,
414 l_log_msg);
415 END IF;
416 RETURN;
417 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
418 ELSE
419 CLOSE c_inventory_set;
420 END IF;
421
422 IF l_category_set_id IS NULL THEN
423 p_return_status:= FND_API.G_RET_STS_SUCCESS;
424 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
425 --p_error_buffer:='Foreign Key broken:Fiscal Type Code does not have Inventory Category Set associated';
426
427 -- Logging Infra: YK: 3/5: Error l_category_set_id is null
428 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
429 l_log_msg := 'E: l_category_set_id is null: classification_type_code='|| p_fsc_rec.classification_type;
430 FND_LOG.STRING(G_LEVEL_STATEMENT,
431 G_MODULE_NAME || l_procedure_name,
432 l_log_msg);
433 END IF;
434 RETURN;
435 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
436 END IF;
437
438 OPEN c_inventory_structure;
439 FETCH c_inventory_structure INTO l_structure_id;
440
441 IF c_inventory_structure%NOTFOUND THEN
442 p_return_status:= FND_API.G_RET_STS_SUCCESS; -- bugfix 9783153
443 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
444 --p_error_buffer:='Foreign Key broken: Inventory Structure ID not found';
445 CLOSE c_inventory_structure;
446
447 -- Logging Infra: YK: 3/5: c_inventory_structure notfound
448 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
449 l_log_msg := 'E: CUR: c_inventory_structure: notfound: category_set_id='|| l_category_set_id;
450 FND_LOG.STRING(G_LEVEL_STATEMENT,
451 G_MODULE_NAME || l_procedure_name,
452 l_log_msg);
453 END IF;
454 RETURN;
455 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
456 ELSE
457 CLOSE c_inventory_structure;
458 END IF;
459
460 -- 3:
461 -- Get the Category Id for the Category code
462 -- Get the allocation.
463 OPEN c_category;
464 FETCH c_category INTO l_category_id;
465
466 -- Logging Infra: YK: 3/5: Break point l_category_id
467 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
468 l_log_msg := 'B: CUR: c_category: fetched: l_category_id=' || l_category_id;
469 FND_LOG.STRING(G_LEVEL_STATEMENT,
470 G_MODULE_NAME || l_procedure_name,
471 l_log_msg);
472 END IF;
473
474 IF c_category%NOTFOUND THEN
475 --p_error_buffer:= 'Fiscal Classification Code does not have an equivalent Item Category Code';
476 fnd_message.set_name('ZX','ZX_ITEM_CAT_NOT_EXIST');
477 p_return_status:= FND_API.G_RET_STS_SUCCESS;
478 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
479 CLOSE c_category;
480 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
481 l_log_msg := 'S: CUR: c_category: notfound';
482 FND_LOG.STRING(G_LEVEL_STATEMENT,
483 G_MODULE_NAME || l_procedure_name,
484 l_log_msg);
485 END IF;
486 RETURN;
487 --RAISE FND_API.G_EXC_ERROR;
488 ELSE
489 CLOSE c_category;
490 END IF;
491
492 OPEN c_item_category;
493 FETCH c_item_category INTO l_allocated_flag;
494
495 IF c_item_category%NOTFOUND THEN
496 CLOSE c_item_category;
497
498 OPEN c_item_category_child;
499 FETCH c_item_category_child INTO l_allocated_flag;
500
501 IF c_item_category_child%NOTFOUND THEN
502 fnd_message.set_name('ZX','ZX_FC_NOT_ALLOC_ENTITY_ID');
503 p_return_status:= FND_API.G_RET_STS_SUCCESS;
504 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
505 CLOSE c_item_category_child;
506 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
507 l_log_msg := 'S: CUR: c_item_category: notfound: category_set_id='|| l_category_set_id ||
508 ', category_id='|| l_category_id ||
509 ', organization_id='|| p_fsc_rec.item_org_id;
510 FND_LOG.STRING(G_LEVEL_STATEMENT,
511 G_MODULE_NAME || l_procedure_name,
512 l_log_msg);
513 END IF;
514 RETURN;
515 --RAISE FND_API.G_EXC_ERROR;
516 ELSE
517 p_fsc_rec.fsc_code:= p_fsc_rec.condition_value;
518 CLOSE c_item_category_child;
519 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
520 l_log_msg := 'S: CUR: c_item_category: found: category_set_id='|| l_category_set_id ||
521 ', category_id='|| l_category_id ||
522 ', organization_id='|| p_fsc_rec.item_org_id;
523 FND_LOG.STRING(G_LEVEL_STATEMENT,
524 G_MODULE_NAME || l_procedure_name,
525 l_log_msg);
526 END IF;
527 END IF; -- parent query
528 ELSE
529 -- Logging Infra: YK: 3/5: Break point l_category_id
530 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
531 l_log_msg := 'B: CUR: c_item_category: fetched: l_allocated_flag=' || l_allocated_flag;
532 FND_LOG.STRING(G_LEVEL_STATEMENT,
533 G_MODULE_NAME || l_procedure_name,
534 l_log_msg);
535 END IF;
536 --p_return_status:=FND_API.G_RET_STS_SUCCESS;
537 --p_error_buffer:='Fiscal Code is allocated to the Entity ID';
538 p_fsc_rec.fsc_code:= p_fsc_rec.condition_value;
539 CLOSE c_item_category;
540 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
541 l_log_msg := 'S: CUR: c_item_category: found: category_set_id='|| l_category_set_id ||
542 ', category_id='|| l_category_id ||
543 ', organization_id='|| p_fsc_rec.item_org_id;
544 FND_LOG.STRING(G_LEVEL_STATEMENT,
545 G_MODULE_NAME || l_procedure_name,
546 l_log_msg);
547 END IF;
548 END IF;
549
550 ELSIF (p_fsc_rec.classification_category = 'PARTY_FISCAL_CLASS' OR
551 p_fsc_rec.classification_category = 'LEGAL_PARTY_FISCAL_CLASS') THEN
552 -- 1. Validate the Fiscal Classification Type has been associated to the regime or parent above
553 -- 2. Derive the TCA Classification Category
554 -- 3. Get the Party ID , Party Type for the PTP ID
555 -- 4. If First Party Entity then Get LE information using xle view
556 -- In not in LE, then check for Classifications Association in TCA model. When not found, If another
557 -- classification code is associated, then return NULL. Otherwise, return G_MISS_CHAR.
558 --
559 -- 5. If Supplier or Supplier Site Entity then navigate to TCA model. When not found, If another classification
560 -- code is associated, then return NULL. Otherwise, return G_MISS_CHAR.
561
562 -- 1:
563 Open c_regime_assoc;
564 fetch c_regime_assoc into l_reg_fscType_flag;
565
566 -- Logging Infra: YK: 3/5: Break point
567 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
568 l_log_msg := 'B: CUR: c_regime_assoc: fetched: l_reg_fsctype_flag=' || l_reg_fsctype_flag;
569 FND_LOG.STRING(G_LEVEL_STATEMENT,
570 G_MODULE_NAME || l_procedure_name,
571 l_log_msg);
572 END IF;
573
574 if c_regime_assoc%notfound then
575 close c_regime_assoc;
576
577 Open c_parent_regime_assoc;
578 fetch c_parent_regime_assoc into l_reg_fscType_flag;
579
580 -- Logging Infra: YK: 3/5: Break point c_regime_assoc not found
581 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
582 l_log_msg := 'B: CUR: c_parent_regime_assoc: notfound: tax_regime=' || p_fsc_rec.tax_regime_code ||
583 ', classification_type='||p_fsc_rec.classification_type;
584 FND_LOG.STRING(G_LEVEL_STATEMENT,
585 G_MODULE_NAME || l_procedure_name,
586 l_log_msg);
587 END IF;
588
589 if c_parent_regime_assoc%notfound then
590 p_return_status:=FND_API.G_RET_STS_SUCCESS;
591 --p_error_buffer:='Regime for the given Fiscal Type is not valid ';
592 fnd_message.set_name('ZX','ZX_REGIME_NOT_VALID');
593 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
594 close c_parent_regime_assoc;
595 --return;
596 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
597 l_log_msg :='B: CUR: c_parent_regime_assoc: notfound';
598 FND_LOG.STRING(G_LEVEL_STATEMENT,
599 G_MODULE_NAME || l_procedure_name,
600 l_log_msg);
601 END IF;
602 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
603 return;
604 end if;
605 else
606 close c_regime_assoc;
607 end if;
608
609 -- 2:
610 Open c_tca_class_category;
611 fetch c_tca_class_category into l_tca_class_category_code;
612
613 -- Logging Infra: YK: 3/3: Break point l_tca_class_category_code
614 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
615 l_log_msg := 'B: CUR: c_tca_class_category: fetched: l_tca_class_category_code=' || l_tca_class_category_code;
616 FND_LOG.STRING(G_LEVEL_STATEMENT,
617 G_MODULE_NAME || l_procedure_name,
618 l_log_msg);
619 END IF;
620
621 if c_tca_class_category%notfound then
622 p_return_status:=FND_API.G_RET_STS_SUCCESS;
623 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
624 --p_error_buffer:='Fiscal Type Code does not exits';
625 --fnd_message.set_name('ZX','ZX_FC_TYPE_NOT_EXIST');
626 close c_tca_class_category;
627 --return;
628 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
629 l_log_msg :='Fiscal Type Code does not exits';
630 FND_LOG.STRING(G_LEVEL_STATEMENT,
631 G_MODULE_NAME || l_procedure_name,
632 l_log_msg);
633 END IF;
634 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635 return;
636 else
637 close c_tca_class_category;
638 end if;
639
640 if l_tca_class_category_code is null then
641 p_return_status:=FND_API.G_RET_STS_SUCCESS;
642 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
643 --p_error_buffer:='Foreign Key broken:Fiscal Type Code does not have Class Category associated';
644 --fnd_message.set_name('ZX','ZX_FC_INV_CAT_NOT_EXIST');
645 -- Logging Infra: YK: 3/5: Error l_tca_class_category_code is null
646 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
647 l_log_msg := 'E: CUR: c_tca_class_category: l_tca_class_category_code is null: ' ||
648 'classification_type_code='|| p_fsc_rec.classification_type;
649 FND_LOG.STRING(G_LEVEL_STATEMENT,
650 G_MODULE_NAME || l_procedure_name,
651 l_log_msg);
652 END IF;
653 return;
654 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
655 end if;
656
657 -- 3:
658 --Bug 5373773
659 IF p_fsc_rec.classification_category = 'LEGAL_PARTY_FISCAL_CLASS' THEN
660 -- for party fiscal classification, le id passed in
661 -- and the party_type code is known as FIRST_PARTY
662
663 select party_id INTO l_party_id
664 from xle_entity_profiles
665 where legal_entity_id = p_fsc_rec.classified_entity_id;
666
667 l_Party_Type_Code:= 'FIRST_PARTY';
668
669 ELSE -- for party fiscal classification, ptp passed in
670 Open c_party_tax_profile_id;
671 fetch c_party_tax_profile_id into l_party_id, l_Party_Type_Code;
672
673 -- Logging Infra: YK: 3/5: Break point l_party_id, l_Party_Type_Code
674 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
675 l_log_msg := 'B: CUR: c_party_tax_profile_id: fetched: l_party_id=' || l_party_id ||
676 ', l_party_type_code='|| l_party_type_code;
677 FND_LOG.STRING(G_LEVEL_STATEMENT,
678 G_MODULE_NAME || l_procedure_name,
679 l_log_msg);
680 END IF;
681
682 if c_party_tax_profile_id%notfound then
683 --p_return_status:=FND_API.G_RET_STS_ERROR;
684 --p_error_buffer:='Party Tax Profile ID not found';
685 fnd_message.set_name('ZX','ZX_PTP_ID_NOT_EXIST');
686 p_fsc_rec.fsc_code:= null;
687 close c_party_tax_profile_id;
688 --return;
689 RAISE FND_API.G_EXC_ERROR;
690 else
691 close c_party_tax_profile_id;
692 end if;
693 END IF; -- 5373773 lxzhang
694
695 -- Get the actual Classification Code (without parent concatenated).
696 Open c_class_code;
697 fetch c_class_code into l_class_code;
698
699 -- Logging Infra: YK: 3/5: Break point l_class_code
700 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
701 l_log_msg := 'B: CUR: c_class_code: fetched: l_class_code=' || l_class_code;
702 FND_LOG.STRING(G_LEVEL_STATEMENT,
703 G_MODULE_NAME || l_procedure_name,
704 l_log_msg);
705 END IF;
706
707 if c_class_code%notfound then
708 p_fsc_rec.fsc_code := FND_API.G_MISS_CHAR;
709 p_return_status:=FND_API.G_RET_STS_SUCCESS;
710 --p_error_buffer:='Parameter value does not have a corresponding Fiscal Code';
711 fnd_message.set_name('ZX','ZX_FC_CODE_PARAM_NOT_EXIST');
712 close c_class_code;
713 return;
714 --RAISE FND_API.G_EXC_ERROR;
715 else
716 close c_class_code;
717 end if;
718
719 -- 4: Supplier or Supplier Site
720 if l_Party_Type_Code <> 'FIRST_PARTY' THEN -- for third party or site
721 l_table_owner := 'ZX_PARTY_TAX_PROFILE';
722 l_table_id:= p_fsc_rec.classified_entity_id;
723
724 if p_fsc_rec.condition_value is not null THEN -- bugfix for handling NULL/ NOT NULL OPERATOR where condition value is null
725 Open c_party_category;
726 fetch c_party_category into l_allocated_flag,l_effective_from, l_effective_to;
727
728 -- Logging Infra: YK: 3/5: Break point
729 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
730 l_log_msg := 'B: CUR: c_party_category: fetched: l_allocated_flag_=' || l_allocated_flag ||
731 ', l_effective_from=' || l_effective_from ||
732 ', l_effective_to=' || l_effective_to;
733 FND_LOG.STRING(G_LEVEL_STATEMENT,
734 G_MODULE_NAME || l_procedure_name,
735 l_log_msg);
736 END IF;
737
738 -- Begin Bug Fix 5528805
739
740 if c_party_category%notfound then
741
742 Open c_party_category_multi_level;
743 fetch c_party_category_multi_level into l_allocated_flag,l_effective_from, l_effective_to;
744
745 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
746 l_log_msg := 'B: CUR: c_party_category_multi_level: fetched: l_allocated_flag_=' || l_allocated_flag ||
747 ', l_effective_from=' || l_effective_from ||
748 ', l_effective_to=' || l_effective_to;
749 FND_LOG.STRING(G_LEVEL_STATEMENT,
750 G_MODULE_NAME || l_procedure_name,
751 l_log_msg);
752 END IF;
753
754
755 if c_party_category_multi_level%notfound then
756 l_c_party_category_not_found := 'Y';
757 end if;
758
759 Close c_party_category_multi_level;
760
761 end if;
762
763 if l_c_party_category_not_found = 'Y' then
764 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
765 p_fsc_rec.effective_from := null;
766 p_fsc_rec.effective_to := null;
767 else
768 p_fsc_rec.fsc_code:= p_fsc_rec.condition_value;
769 p_fsc_rec.effective_from :=l_effective_from;
770 p_fsc_rec.effective_to :=l_effective_to;
771
772 end if;
773 close c_party_category;
774
775 else -- bugfix: condition value is null case
776
777 -- End Bug Fix 5528805
778
779 Open c_pty_fc_assgn_exists;
780 fetch c_pty_fc_assgn_exists into l_allocated_flag;
781
782 IF c_pty_fc_assgn_exists%NOTFOUND THEN
783 p_fsc_rec.fsc_code := FND_API.G_MISS_CHAR;
784 -- Logging Infra: YK: 3/5: c_party_category notfound
785 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
786 l_log_msg := 'S: c_pty_fc_assgn_exists: notfound: class_category=' || l_tca_class_category_code||
787 ', class_code='|| l_class_code ||
788 ', owner_table_name=' || l_table_owner ||
789 ', owner_table_id=' || l_table_id ||
790 ', p_fsc_rec.tax_determine_date=' || p_fsc_rec.tax_determine_date;
791 FND_LOG.STRING(G_LEVEL_STATEMENT,
792 G_MODULE_NAME || l_procedure_name,
793 l_log_msg);
794 END IF;
795 ELSE
796 p_fsc_rec.fsc_code:= p_fsc_rec.condition_value;
797 -- Logging Infra: YK: 3/5: c_party_category notfound
798 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
799 l_log_msg := 'S: c_pty_fc_assgn_exists: found: class_category=' || l_tca_class_category_code||
800 ', class_code='|| l_class_code ||
801 ', owner_table_name=' || l_table_owner ||
802 ', owner_table_id=' || l_table_id ||
803 ', p_fsc_rec.tax_determine_date=' || p_fsc_rec.tax_determine_date;
804 FND_LOG.STRING(G_LEVEL_STATEMENT,
805 G_MODULE_NAME || l_procedure_name,
806 l_log_msg);
807 END IF;
808 END IF;
809 close c_pty_fc_assgn_exists;
810
811 p_fsc_rec.effective_from :=l_effective_from;
812 p_fsc_rec.effective_to :=l_effective_to;
813 end if; -- p_fsc_rec.condition_value is not null
814
815 else
816 -- Call Legal Entity API for Party
817 -- If no Classfication in LE, check for "Tax Classifications" in the FC-TCA model
818
819 l_table_owner := 'HZ_PARTIES';
820 l_table_id:= l_party_id;
821
822 -- Start : Code commented for Bug#7010655
823 /*
824 --5373773
825 BEGIN
826 SELECT party_tax_profile_id INTO l_ptp_id
827 FROM zx_party_tax_profile
828 where party_id = l_party_id
829 and party_type_code ='FIRST_PARTY';
830 EXCEPTION WHEN OTHERS THEN
831 l_ptp_id := null;
832 END;
833 --5373773 end
834 */
835 -- End : Code commented for Bug#7010655
836
837 l_ptp_id := p_fsc_rec.classified_entity_id; -- Added for Bug#7010655
838
839 Open c_xle_activity_code;
840 fetch c_xle_activity_code into l_allocated_flag,l_effective_from, l_effective_to;
841
842 -- Logging Infra: YK: 3/5: Break point
843 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
844 l_log_msg := 'B: CUR: c_xle_activity_code: fetched: l_allocated_flag_=' || l_allocated_flag ||
845 ', l_effective_from=' || l_effective_from ||
846 ', l_effective_to=' || l_effective_to;
847 FND_LOG.STRING(G_LEVEL_STATEMENT,
848 G_MODULE_NAME || l_procedure_name,
849 l_log_msg);
850 END IF;
851
852 if c_xle_activity_code%notfound then
853 l_le_status := 'NOT_FOUND';
854 close c_xle_activity_code;
855
856 -- Logging Infra: YK: 3/5: c_party_category notfound
857 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
858 l_log_msg := 'S: c_xle_activity_code: notfound: class_category=' || l_tca_class_category_code||
859 ', class_code='|| l_class_code ||
860 ', p_fsc_rec.tax_determine_date=' || p_fsc_rec.tax_determine_date;
861 FND_LOG.STRING(G_LEVEL_STATEMENT,
862 G_MODULE_NAME || l_procedure_name,
863 l_log_msg);
864 END IF;
865
866 ELSE
867 l_le_status := 'FOUND';
868 p_fsc_rec.fsc_code:= p_fsc_rec.condition_value;
869 p_fsc_rec.effective_from :=l_effective_from;
870 p_fsc_rec.effective_to :=l_effective_to;
871 close c_xle_activity_code;
872 end if;
873
874 -- Logging Infra: YK: 3/5/2004: Open issue
875 -- After calling legal entitity API for party list output value here...
876 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
877 l_log_msg := 'B: l_table_owner=' || l_table_owner ||
878 ', l_table_id=' || l_table_id ||
879 ', l_le_status=' || l_le_status;
880 FND_LOG.STRING(G_LEVEL_STATEMENT,
881 G_MODULE_NAME || l_procedure_name,
882 l_log_msg);
883 END IF;
884
885 if l_le_status = 'NOT_FOUND' then
886 l_table_owner := 'ZX_PARTY_TAX_PROFILE';
887 l_table_id := l_ptp_id;
888
889 if p_fsc_rec.condition_value is not null then
890 Open c_party_category;
891 fetch c_party_category into l_allocated_flag,l_effective_from, l_effective_to;
892
893 -- YK: 3/3: Break point: may not be necessary
894 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
895 l_log_msg := 'B: CUR: c_party_category: fetched: l_allocated_flag=' || l_allocated_flag ||
896 ', l_effective_from=' || l_effective_from ||
897 ', l_effective_to=' || l_effective_to;
898 FND_LOG.STRING(G_LEVEL_STATEMENT,
899 G_MODULE_NAME || l_procedure_name,
900 l_log_msg);
901 END IF;
902
903 -- Begin Bug Fix 5528805
904
905 if c_party_category%notfound then
906
907 Open c_party_category_multi_level;
908 fetch c_party_category_multi_level into l_allocated_flag,l_effective_from, l_effective_to;
909
910 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
911 l_log_msg := 'B: CUR: c_party_category_multi_level: fetched: l_allocated_flag_=' || l_allocated_flag ||
912 ', l_effective_from=' || l_effective_from ||
913 ', l_effective_to=' || l_effective_to;
914 FND_LOG.STRING(G_LEVEL_STATEMENT,
915 G_MODULE_NAME || l_procedure_name,
916 l_log_msg);
917 END IF;
918
919
920 if c_party_category_multi_level%notfound then
921 l_c_party_category_not_found := 'Y';
922 end if;
923
924 Close c_party_category_multi_level;
925
926 end if;
927
928 if l_c_party_category_not_found = 'Y' then
929 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
930 p_fsc_rec.effective_from := null;
931 p_fsc_rec.effective_to := null;
932 else
933 p_fsc_rec.fsc_code:= p_fsc_rec.condition_value;
934 p_fsc_rec.effective_from :=l_effective_from;
935 p_fsc_rec.effective_to :=l_effective_to;
936 end if;
937 close c_party_category;
938
939 else -- p_fsc_rec.condition_value is null
940
941 l_table_owner := 'HZ_PARTIES';
942 l_table_id := l_party_id;
943 open c_xle_fc_assgn_exists;
944 fetch c_xle_fc_assgn_exists into l_allocated_flag;
945
946 if c_xle_fc_assgn_exists%notfound then
947 l_le_other_fc_status := 'NOT_FOUND';
948 else
949 l_le_other_fc_status := 'FOUND';
950 end if;
951
952 close c_xle_fc_assgn_exists;
953
954 if l_le_other_fc_status = 'FOUND' then
955
956 p_fsc_rec.fsc_code:= p_fsc_rec.condition_value;
957 p_fsc_rec.effective_from := l_effective_from;
958 p_fsc_rec.effective_to := l_effective_to;
959 -- Logging Infra: YK: 3/4: c_party_category notfound
960 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
961 l_log_msg := 'S: CUR: c_party_category: notfound: other FC found in LE table: class_category=' || l_tca_class_category_code||
962 ', class_code='|| l_class_code ||
963 ', owner_table_name=' || l_table_owner ||
964 ', owner_table_id=' || l_table_id ||
965 ', p_fsc_rec.tax_determine_date=' || p_fsc_rec.tax_determine_date;
966 FND_LOG.STRING(G_LEVEL_STATEMENT,
967 G_MODULE_NAME || l_procedure_name,
968 l_log_msg);
969 END IF;
970
971 elsif l_le_other_fc_status = 'NOT_FOUND' then
972
973 l_table_owner := 'ZX_PARTY_TAX_PROFILE';
974 l_table_id := l_ptp_id;
975 Open c_pty_fc_assgn_exists;
976 fetch c_pty_fc_assgn_exists into l_allocated_flag;
977 IF c_pty_fc_assgn_exists%NOTFOUND THEN
978 p_fsc_rec.fsc_code := FND_API.G_MISS_CHAR;
979 -- Logging Infra: YK: 3/4: c_party_category notfound
980 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
981 l_log_msg := 'S: CUR: c_party_category: notfound: class_category=' || l_tca_class_category_code||
982 ', class_code='|| l_class_code ||
983 ', owner_table_name=' || l_table_owner ||
984 ', owner_table_id=' || l_table_id ||
985 ', p_fsc_rec.tax_determine_date=' || p_fsc_rec.tax_determine_date;
986 FND_LOG.STRING(G_LEVEL_STATEMENT,
987 G_MODULE_NAME || l_procedure_name,
988 l_log_msg);
989 END IF;
990 ELSE
991 p_fsc_rec.fsc_code:= p_fsc_rec.condition_value;
992 p_fsc_rec.effective_from := l_effective_from;
993 p_fsc_rec.effective_to := l_effective_to;
994 -- Logging Infra: YK: 3/4: c_party_category notfound
995 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
996 l_log_msg := 'S: CUR: c_party_category: notfound: other FC found in HZ table: class_category=' || l_tca_class_category_code||
997 ', class_code='|| l_class_code ||
998 ', owner_table_name=' || l_table_owner ||
999 ', owner_table_id=' || l_table_id ||
1000 ', p_fsc_rec.tax_determine_date=' || p_fsc_rec.tax_determine_date;
1001 FND_LOG.STRING(G_LEVEL_STATEMENT,
1002 G_MODULE_NAME || l_procedure_name,
1003 l_log_msg);
1004 END IF;
1005 END IF;
1006 close c_pty_fc_assgn_exists;
1007 end if; -- end check for l_le_other_fc_status
1008 end if; -- end check for p_fsc_rec.condition_value is not null
1009
1010 end if; -- l_le_status = 'NOT_FOUND'
1011
1012 end if; -- l_Party_Type_Code ='THIRD_PARTY_SITE' OR l_Party_Type_Code = 'THIRD_PARTY'
1013
1014 elsif p_fsc_rec.classification_category = 'TRX_FISCAL_CLASS' then
1015 -- 1. Validate Fiscal Type exists
1016 -- 2. Validate the Fiscal Classification Type has been associated to the regime
1017 -- 3. Get the Classification code for the passed in Concatenated code.
1018 -- 4. Get the fiscal code associated to the business category.
1019
1020 Open c_classification_type_code;
1021 fetch c_classification_type_code into l_classification_type_code;
1022
1023 -- Logging Infra: YK: 3/5: Break point: l_classification_type_code
1024 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1025 l_log_msg := 'B: CUR: c_classification_type_code: fetched: l_classification_type_code=' || l_classification_type_code;
1026 FND_LOG.STRING(G_LEVEL_STATEMENT,
1027 G_MODULE_NAME || l_procedure_name,
1028 l_log_msg);
1029 END IF;
1030
1031 if c_classification_type_code%notfound then
1032 --p_error_buffer:='Fiscal Type Code does not exists';
1033 --fnd_message.set_name('ZX','ZX_FC_TYPE_NOT_EXIST');
1034 close c_classification_type_code;
1035
1036 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1037 l_log_msg := 'Fiscal Type Code does not exists';
1038 FND_LOG.STRING(G_LEVEL_STATEMENT,
1039 G_MODULE_NAME || l_procedure_name,
1040 l_log_msg);
1041 END IF;
1042 p_return_status:=FND_API.G_RET_STS_SUCCESS;
1043 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
1044 RETURN;
1045 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1046 else
1047 close c_classification_type_code;
1048 end if;
1049
1050
1051 Open c_regime_assoc;
1052 fetch c_regime_assoc into l_reg_fscType_flag;
1053
1054 -- Logging Infra: YK: 3/5: Break point
1055 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1056 l_log_msg := 'B: CUR: c_regime_assoc: fetched: l_reg_fsctype_flag=' || l_reg_fsctype_flag;
1057 FND_LOG.STRING(G_LEVEL_STATEMENT,
1058 G_MODULE_NAME || l_procedure_name,
1059 l_log_msg);
1060 END IF;
1061
1062 if c_regime_assoc%notfound then
1063 close c_regime_assoc;
1064 Open c_parent_regime_assoc;
1065 fetch c_parent_regime_assoc into l_reg_fscType_flag;
1066
1067 -- Logging Infra: YK: 3/5: Break point
1068 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1069 l_log_msg := 'B: CUR: c_parent_regime_assoc: fetched: l_reg_fsctype_flag=' || l_reg_fsctype_flag;
1070 FND_LOG.STRING(G_LEVEL_STATEMENT,
1071 G_MODULE_NAME || l_procedure_name,
1072 l_log_msg);
1073 END IF;
1074
1075
1076 if c_parent_regime_assoc%notfound then
1077 --p_error_buffer:='Regime for the given Fiscal Type is not valid ';
1078 --fnd_message.set_name('ZX','ZX_REGIME_NOT_VALID');
1079 close c_parent_regime_assoc;
1080 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1081 l_log_msg := 'Regime for the given Fiscal Type is not valid ';
1082 FND_LOG.STRING(G_LEVEL_STATEMENT,
1083 G_MODULE_NAME || l_procedure_name,
1084 l_log_msg);
1085 END IF;
1086 p_return_status:=FND_API.G_RET_STS_SUCCESS;
1087 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
1088 return;
1089 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1090 end if;
1091 else
1092 close c_regime_assoc;
1093 end if;
1094
1095
1096 Open c_trxbizcat_fiscalclass;
1097 fetch c_trxbizcat_fiscalclass into l_allocated_flag,l_effective_from, l_effective_to;
1098
1099 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1100 l_log_msg := 'B: CUR: c_trxbizcat_fiscalclass: fetched: l_allocated_flag=' || l_allocated_flag ||
1101 ', l_effective_from=' || l_effective_from ||
1102 ', l_effective_to=' || l_effective_to;
1103 FND_LOG.STRING(G_LEVEL_STATEMENT,
1104 G_MODULE_NAME || l_procedure_name,
1105 l_log_msg);
1106 END IF;
1107
1108 if c_trxbizcat_fiscalclass%notfound then
1109 -- Check if association has been to a parent Trx Biz Categgory
1110 close c_trxbizcat_fiscalclass;
1111
1112 Open c_parent_trxbizcat_fiscalclass;
1113 fetch c_parent_trxbizcat_fiscalclass into l_allocated_flag,l_effective_from, l_effective_to;
1114
1115 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1116 l_log_msg := 'B: CUR: c_parent_trxbizcat_fiscalclass: fetched: l_allocated_flag=' || l_allocated_flag ||
1117 ', l_effective_from=' || l_effective_from ||
1118 ', l_effective_to=' || l_effective_to;
1119 FND_LOG.STRING(G_LEVEL_STATEMENT,
1120 G_MODULE_NAME || l_procedure_name,
1121 l_log_msg);
1122 END IF;
1123
1124 if c_parent_trxbizcat_fiscalclass%notfound then
1125 p_fsc_rec.fsc_code:= FND_API.G_MISS_CHAR;
1126 p_fsc_rec.effective_from := null;
1127 p_fsc_rec.effective_to := null;
1128 -- fnd_message.set_name('ZX','ZX_FC_NOT_ALLOC_ENTITY_ID');
1129 -- RAISE FND_API.G_EXC_ERROR;
1130 else
1131 p_fsc_rec.effective_from :=l_effective_from;
1132 p_fsc_rec.effective_to :=l_effective_to;
1133 p_fsc_rec.fsc_code:= p_fsc_rec.condition_value;
1134 end if;
1135 close c_parent_trxbizcat_fiscalclass;
1136 else
1137 p_fsc_rec.effective_from :=l_effective_from;
1138 p_fsc_rec.effective_to :=l_effective_to;
1139 p_fsc_rec.fsc_code:= p_fsc_rec.condition_value;
1140 close c_trxbizcat_fiscalclass;
1141 end if;
1142
1143 else
1144 --p_error_buffer:='Classification Category not supported by this procedure';
1145 --fnd_message.set_name('ZX','ZX_FC_CATEG_NOT_SUPPORTED');
1146
1147 -- Logging Infra: YK: 3/5:
1148 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1149 l_log_msg := 'E: unspported category: p_fsc_rec.classification_category=' || p_fsc_rec.classification_category;
1150 FND_LOG.STRING(G_LEVEL_STATEMENT,
1151 G_MODULE_NAME || l_procedure_name,
1152 l_log_msg);
1153 END IF;
1154
1155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1156 end if;
1157
1158 -- Logging Infra: YK: 3/5: Put output value here
1159 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1160 l_log_msg := 'R: p_fsc_rec.effective_from=' ||l_effective_from ||
1161 ', p_fsc_rec.effective_to=' ||l_effective_to ||
1162 ', p_fsc_rec.fsc_code=' || p_fsc_rec.condition_value;
1163 l_log_msg := l_log_msg || 'get_fiscal_classification(-)';
1164 FND_LOG.STRING(G_LEVEL_STATEMENT,
1165 G_MODULE_NAME || l_procedure_name,
1166 l_log_msg);
1167 END IF;
1168
1169 EXCEPTION
1170 WHEN INVALID_CURSOR THEN
1171 p_return_status := FND_API.G_RET_STS_ERROR;
1172 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1173 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1174
1175 if c_regime_assoc%isopen then close c_regime_assoc; end if;
1176 if c_parent_regime_assoc%isopen then close c_parent_regime_assoc; end if;
1177 if c_inventory_set%isopen then close c_inventory_set; end if;
1178 if c_inventory_structure%isopen then close c_inventory_structure; end if;
1179 if c_category%isopen then close c_category; end if;
1180 if c_item_category%isopen then close c_item_category;end if;
1181 if c_item_category_child%isopen then close c_item_category;end if;
1182 if c_tca_class_category%isopen then close c_tca_class_category; end if;
1183 if c_party_tax_profile_id%isopen then close c_party_tax_profile_id; end if;
1184 if c_class_code%isopen then close c_class_code; end if;
1185 if c_party_category%isopen then close c_party_category; end if;
1186 if c_party_category_multi_level%isopen then close c_party_category_multi_level; end if;
1187 if c_classification_type_code%isopen then close c_classification_type_code; end if;
1188 if c_trxbizcat_fiscalclass%isopen then close c_trxbizcat_fiscalclass; end if;
1189
1190 -- Logging Infra: YK: 3/5:
1191 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1192 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
1193 END IF;
1194
1195 WHEN FND_API.G_EXC_ERROR THEN
1196 p_return_status := FND_API.G_RET_STS_ERROR;
1197 if c_regime_assoc%isopen then close c_regime_assoc; end if;
1198 if c_parent_regime_assoc%isopen then close c_parent_regime_assoc; end if;
1199 if c_inventory_set%isopen then close c_inventory_set; end if;
1200 if c_inventory_structure%isopen then close c_inventory_structure; end if;
1201 if c_category%isopen then close c_category; end if;
1202 if c_item_category%isopen then close c_item_category; end if;
1203 if c_item_category_child%isopen then close c_item_category;end if;
1204 if c_tca_class_category%isopen then close c_tca_class_category; end if;
1205 if c_party_tax_profile_id%isopen then close c_party_tax_profile_id; end if;
1206 if c_class_code%isopen then close c_class_code; end if;
1207 if c_party_category%isopen then close c_party_category; end if;
1208 if c_party_category_multi_level%isopen then close c_party_category_multi_level; end if;
1209 if c_classification_type_code%isopen then close c_classification_type_code; end if;
1210 if c_trxbizcat_fiscalclass%isopen then close c_trxbizcat_fiscalclass; end if;
1211
1212 -- Logging Infra: YK: 3/5:
1213 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1214 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
1215 END IF;
1216
1217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1218 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1219 if c_regime_assoc%isopen then close c_regime_assoc; end if;
1220 if c_parent_regime_assoc%isopen then close c_parent_regime_assoc; end if;
1221 if c_inventory_set%isopen then close c_inventory_set; end if;
1222 if c_inventory_structure%isopen then close c_inventory_structure; end if;
1223 if c_category%isopen then close c_category; end if;
1224 if c_item_category%isopen then close c_item_category; end if;
1225 if c_item_category_child%isopen then close c_item_category;end if;
1226 if c_tca_class_category%isopen then close c_tca_class_category; end if;
1227 if c_party_tax_profile_id%isopen then close c_party_tax_profile_id; end if;
1228 if c_class_code%isopen then close c_class_code; end if;
1229 if c_party_category%isopen then close c_party_category; end if;
1230 if c_party_category_multi_level%isopen then close c_party_category_multi_level; end if;
1231 if c_classification_type_code%isopen then close c_classification_type_code; end if;
1232 if c_trxbizcat_fiscalclass%isopen then close c_trxbizcat_fiscalclass; end if;
1233
1234 WHEN OTHERS THEN
1235 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1236 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1237 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1238
1239 if c_regime_assoc%isopen then close c_regime_assoc; end if;
1240 if c_parent_regime_assoc%isopen then close c_parent_regime_assoc; end if;
1241 if c_inventory_set%isopen then close c_inventory_set; end if;
1242 if c_inventory_structure%isopen then close c_inventory_structure; end if;
1243 if c_category%isopen then close c_category; end if;
1244 if c_item_category%isopen then close c_item_category; end if;
1245 if c_item_category_child%isopen then close c_item_category;end if;
1246 if c_tca_class_category%isopen then close c_tca_class_category; end if;
1247 if c_party_tax_profile_id%isopen then close c_party_tax_profile_id; end if;
1248 if c_class_code%isopen then close c_class_code; end if;
1249 if c_party_category%isopen then close c_party_category; end if;
1250 if c_party_category_multi_level%isopen then close c_party_category_multi_level; end if;
1251 if c_classification_type_code%isopen then close c_classification_type_code; end if;
1252 if c_trxbizcat_fiscalclass%isopen then close c_trxbizcat_fiscalclass; end if;
1253
1254 -- Logging Infra: YK: 3/5:
1255 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1256 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,SQLCODE || ': ' || SQLERRM);
1257 END IF;
1258
1259 END GET_FISCAL_CLASSIFICATION;
1260
1261
1262 Procedure GET_PROD_TRX_CATE_VALUE (
1263 p_fsc_cat_rec IN OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_CATEGORY_CODE_INFO_REC,
1264 p_return_status OUT NOCOPY VARCHAR2)
1265 IS
1266
1267 cursor c_classification_code is
1268 select Classification_Code,
1269 classification_code_level,
1270 segment1,
1271 segment2,
1272 segment3,
1273 segment4,
1274 segment5,
1275 effective_from,
1276 effective_to
1277 from Zx_Fc_Codes_Denorm_B
1278 where Classification_Type_Categ_Code = p_fsc_cat_rec.classification_category
1279 and classification_type_code = p_fsc_cat_rec.classification_type
1280 and concat_classif_code = p_fsc_cat_rec.parameter_value
1281 and p_fsc_cat_rec.tax_determine_date between effective_from and nvl(effective_to, p_fsc_cat_rec.tax_determine_date);
1282
1283 cursor c_delimiter is
1284 select delimiter
1285 from zx_fc_types_b
1286 where Classification_Type_Categ_Code = p_fsc_cat_rec.classification_category
1287 and classification_type_code = p_fsc_cat_rec.classification_type;
1288
1289 l_Classification_Code zx_fc_codes_b.Classification_Code%type;
1290 l_segment1 Zx_Fc_Codes_Denorm_B.segment1%type;
1291 l_segment2 Zx_Fc_Codes_Denorm_B.segment1%type;
1292 l_segment3 Zx_Fc_Codes_Denorm_B.segment1%type;
1293 l_segment4 Zx_Fc_Codes_Denorm_B.segment1%type;
1294 l_segment5 Zx_Fc_Codes_Denorm_B.segment1%type;
1295 l_effective_from date;
1296 l_effective_to date;
1297 l_classification_code_level Zx_Fc_Codes_Denorm_B.classification_code_level%type;
1298 l_delimiter zx_fc_types_b.delimiter%type;
1299 l_unconcatenated_code Zx_Fc_Codes_Denorm_B.concat_classif_code%type;
1300
1301 -- Logging Infra
1302 l_procedure_name CONSTANT VARCHAR2(30) := 'get_prod_trx_cate_value';
1303 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1304
1305 BEGIN
1306 -- Logging Infra: 3/5: YK: Setting up runtime level
1307 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1308
1309 -- Logging Infra: Procedure level
1310 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1311 l_log_msg := l_procedure_name||'(+)';
1312 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1313 END IF;
1314
1315 -- arp_util_tax.debug('in GET_PROD_TRX_CATE_VALUE');
1316 -- need to hard code return value here
1317 p_return_status := FND_API.G_RET_STS_SUCCESS;
1318
1319 if p_fsc_cat_rec.classification_category = 'TRX_GENERIC_CLASSIFICATION' or
1320 p_fsc_cat_rec.classification_category = 'PRODUCT_GENERIC_CLASSIFICATION' or
1321 p_fsc_cat_rec.classification_category = 'DOCUMENT'then
1322
1323 Open c_delimiter;
1324 fetch c_delimiter into l_delimiter;
1325
1326 -- Logging Infra: YK: 3/5: Break point
1327 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1328 l_log_msg := 'B: CUR: c_delimiter: fetched: l_delimiter=' || l_delimiter;
1329 FND_LOG.STRING(G_LEVEL_STATEMENT,
1330 G_MODULE_NAME || l_procedure_name,
1331 l_log_msg);
1332 END IF;
1333
1334 if c_delimiter%notfound then
1335 --p_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1336 --p_error_buffer:='Classification Type does not exist';
1337 --fnd_message.set_name('ZX','ZX_FC_TYPE_NOT_EXIST');
1338 close c_delimiter;
1339
1340 -- Logging Infra: YK: 3/5: c_delimiter notfound
1341 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1342 l_log_msg := 'E: CUR: c_delimiter: notfound: Classification_Type_Categ_Code='
1343 || p_fsc_cat_rec.classification_category ||
1344 ', classification_type_code=' || p_fsc_cat_rec.classification_type;
1345 FND_LOG.STRING(G_LEVEL_STATEMENT,
1346 G_MODULE_NAME || l_procedure_name,
1347 l_log_msg);
1348 END IF;
1349 --return;
1350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1351 else
1352 close c_delimiter;
1353 end if;
1354
1355 if p_fsc_cat_rec.parameter_value IS NOT NULL then
1356 -- YK: 3/5 What if l_delimiter is NULL?
1357
1358 Open c_classification_code;
1359 fetch c_classification_code into
1360 l_Classification_Code,
1361 l_classification_code_level,
1362 l_segment1,
1363 l_segment2,
1364 l_segment3,
1365 l_segment4,
1366 l_segment5,
1367 l_effective_from,
1368 l_effective_to;
1369
1370 -- Logging Infra: YK: 3/5: Break point
1371 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1372 l_log_msg := 'B: CUR: c_classification_code: fetched: l_classiciation_code=' || l_classification_code ||
1373 ', l_classification_code_level=' || l_classification_code_level ||
1374 ', l_segment1=' || l_segment1 ||
1375 ', l_segment2=' || l_segment2 ||
1376 ', l_segment3=' || l_segment3 ||
1377 ', l_segment4=' || l_segment4 ||
1378 ', l_segment5=' || l_segment5;
1379 FND_LOG.STRING(G_LEVEL_STATEMENT,
1380 G_MODULE_NAME || l_procedure_name,
1381 l_log_msg);
1382 END IF;
1383
1384 if c_classification_code%notfound then
1385 p_fsc_cat_rec.condition_value := Null;
1386 p_fsc_cat_rec.effective_from := Null;
1387 p_fsc_cat_rec.effective_to := Null;
1388 p_return_status:=FND_API.G_RET_STS_SUCCESS;
1389 close c_classification_code;
1390
1391 -- Logging Infra: YK: 3/5: c_classification_code notfound
1392 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1393 l_log_msg := 'S: CUR: c_classification_code: notfound: Classification_Type_Categ_Code='
1394 || p_fsc_cat_rec.classification_category ||
1395 ', classification_type_code=' || p_fsc_cat_rec.classification_type ||
1396 ', concat_classif_code=' || p_fsc_cat_rec.parameter_value ||
1397 ', p_fsc_cat_rec.tax_determine_date=' || p_fsc_cat_rec.tax_determine_date;
1398 FND_LOG.STRING(G_LEVEL_STATEMENT,
1399 G_MODULE_NAME || l_procedure_name,
1400 l_log_msg);
1401 END IF;
1402 --return;
1403 RAISE FND_API.G_EXC_ERROR;
1404
1405 else
1406 if p_fsc_cat_rec.condition_subclass='1' then
1407 p_fsc_cat_rec.condition_value :=l_segment1;
1408
1409 elsif p_fsc_cat_rec.condition_subclass='2' then
1410 p_fsc_cat_rec.condition_value :=l_segment1 || l_delimiter || l_segment2;
1411
1412 elsif p_fsc_cat_rec.condition_subclass='3' then
1413 p_fsc_cat_rec.condition_value :=l_segment1 || l_delimiter || l_segment2 || l_delimiter || l_segment3;
1414
1415 elsif p_fsc_cat_rec.condition_subclass='4' then
1416 p_fsc_cat_rec.condition_value := l_segment1 || l_delimiter || l_segment2 || l_delimiter || l_segment3 || l_delimiter || l_segment4;
1417
1418 elsif p_fsc_cat_rec.condition_subclass='5' then
1419 p_fsc_cat_rec.condition_value :=l_segment1 || l_delimiter || l_segment2 || l_delimiter || l_segment3 || l_delimiter || l_segment4 || l_delimiter || l_segment5;
1420
1421 end if;
1422
1423 p_fsc_cat_rec.effective_from :=l_effective_from;
1424 p_fsc_cat_rec.effective_to :=l_effective_to;
1425 p_return_status:=FND_API.G_RET_STS_SUCCESS;
1426 --p_error_buffer:='Classification Code found';
1427 close c_classification_code;
1428
1429 -- Logging Infra: YK:3/5: Break point for l_delimiter
1430 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1431 l_log_msg := 'B: p_fsc_cat_rec.condition_subclass=' || p_fsc_cat_rec.condition_subclass ||
1432 ', p_fsc_cat_rec.condition_value=' || p_fsc_cat_rec.condition_value ||
1433 ', p_fsc_cat_rec.effective_from=' || p_fsc_cat_rec.effective_from ||
1434 ', p_fsc_cat_rec.effective_to=' || p_fsc_cat_rec.effective_to;
1435 FND_LOG.STRING(G_LEVEL_STATEMENT,
1436 G_MODULE_NAME || l_procedure_name,
1437 l_log_msg);
1438 END IF;
1439
1440
1441 end if;
1442 else
1443 -- value is null
1444 p_fsc_cat_rec.condition_value := null;
1445 p_fsc_cat_rec.effective_from := Null;
1446 p_fsc_cat_rec.effective_to := Null;
1447 p_return_status:=FND_API.G_RET_STS_SUCCESS;
1448
1449 end if;
1450
1451 else
1452 --p_return_status:=FND_API.G_RET_STS_ERROR;
1453 --p_error_buffer:='Classification Category not supported by this procedure';
1454 --fnd_message.set_name('ZX','ZX_FC_CATEG_NOT_SUPPORTED');
1455
1456 -- Logging Infra: YK: 3/5:
1457 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1458 l_log_msg := 'E: unspported category: p_fsc_cat_rec.classification_category= '
1459 || p_fsc_cat_rec.classification_category;
1460 FND_LOG.STRING(G_LEVEL_STATEMENT,
1461 G_MODULE_NAME || l_procedure_name,
1462 l_log_msg);
1463 END IF;
1464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1465
1466 end if;
1467
1468 -- Logging Infra: YK: 3/5: Procedure level message
1469 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1470 l_log_msg := 'E: unspported category: p_fsc_cat_rec.classification_category= '
1471 || p_fsc_cat_rec.classification_category;
1472 l_log_msg := l_log_msg || ' get_prod_trx_cate_value(-)';
1473 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1474 G_MODULE_NAME || l_procedure_name, l_log_msg);
1475 END IF;
1476 EXCEPTION
1477 WHEN INVALID_CURSOR THEN
1478 p_return_status := FND_API.G_RET_STS_ERROR;
1479 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1480 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1481
1482 if c_classification_code%isopen then close c_classification_code; end if;
1483 if c_delimiter%isopen then close c_delimiter; end if;
1484
1485 -- Logging Infra: YK: 3/5:
1486 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1487 FND_LOG.STRING(G_LEVEL_STATEMENT,
1488 G_MODULE_NAME || l_procedure_name,
1489 SQLCODE || ': ' || SQLERRM);
1490 END IF;
1491
1492 WHEN FND_API.G_EXC_ERROR THEN
1493 p_fsc_cat_rec.condition_value := Null;
1494 p_fsc_cat_rec.effective_from := Null;
1495 p_fsc_cat_rec.effective_to := Null;
1496
1497 if c_classification_code%isopen then close c_classification_code; end if;
1498 if c_delimiter%isopen then close c_delimiter; end if;
1499
1500 -- Logging Infra: YK: 3/5:
1501 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1502 FND_LOG.STRING(G_LEVEL_STATEMENT,
1503 G_MODULE_NAME || l_procedure_name,
1504 SQLCODE || ': ' || SQLERRM);
1505 END IF;
1506
1507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1508 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1509 p_fsc_cat_rec.condition_value := Null;
1510 p_fsc_cat_rec.effective_from := Null;
1511 p_fsc_cat_rec.effective_to := Null;
1512
1513 if c_classification_code%isopen then close c_classification_code; end if;
1514 if c_delimiter%isopen then close c_delimiter; end if;
1515
1516 WHEN OTHERS THEN
1517 p_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
1521 if c_classification_code%isopen then close c_classification_code; end if;
1522 if c_delimiter%isopen then close c_delimiter; end if;
1523
1524 -- Logging Infra: YK: 3/5:
1525 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1526 FND_LOG.STRING(G_LEVEL_STATEMENT,
1527 G_MODULE_NAME || l_procedure_name,
1528 SQLCODE || ': ' || SQLERRM);
1529 END IF;
1530
1531 END GET_PROD_TRX_CATE_VALUE;
1532
1533 /********************************************************************************
1534 * Private Procedures Specification *
1535 ********************************************************************************/
1536
1537 /********************************************************************************
1538 * *
1539 * Name : Do_Get_Registration_Tax *
1540 * Purpose : Get Tax Registration Information of Tax Regime, Tax, Jurisdiction *
1541 * level. *
1542 * *
1543 ********************************************************************************/
1544 PROCEDURE Do_Get_Registration_Tax
1545 (p_ptp_id IN zx_party_tax_profile.party_tax_profile_id%TYPE,
1546 p_tax_regime_code IN ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
1547 p_tax IN ZX_TAXES_B.TAX%TYPE,
1548 p_jurisdiction_code IN ZX_JURISDICTIONS_B.TAX_JURISDICTION_CODE%TYPE,
1549 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
1550 x_get_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
1551 x_return_status OUT NOCOPY VARCHAR2,
1552 x_ret_record_level OUT NOCOPY VARCHAR2,
1553 p_getone OUT NOCOPY NUMBER);
1554
1555 /*******************************************************************************
1556 * *
1557 * Name : Do_Get_Sup_Site *
1558 * Purpose : Get Tax Registration Information from sup sites *
1559 * *
1560 *******************************************************************************/
1561
1562 PROCEDURE Do_Get_Sup_Site
1563 (p_party_tax_profile_id IN NUMBER,
1564 p_account_id IN ZX_REGISTRATIONS.ACCOUNT_ID%TYPE,
1565 p_account_site_id IN ZX_REGISTRATIONS.ACCOUNT_SITE_ID%TYPE,
1566 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
1567 x_get_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
1568 x_return_status OUT NOCOPY VARCHAR2,
1569 x_ret_record_level OUT NOCOPY VARCHAR2,
1570 p_getone OUT NOCOPY NUMBER);
1571
1572 /********************************************************************************
1573 * *
1574 * Name : Do_Get_Reg_Site_Uses *
1575 * Purpose : Get Tax Registration Information of Sites. *
1576 * *
1577 ********************************************************************************/
1578 PROCEDURE Do_Get_Reg_Site_Uses
1579 (p_party_tax_profile_id IN NUMBER,
1580 p_account_id IN ZX_REGISTRATIONS.ACCOUNT_ID%TYPE,
1581 p_account_site_id IN ZX_REGISTRATIONS.ACCOUNT_SITE_ID%TYPE,
1582 p_site_use_id IN HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID%TYPE,
1583 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
1584 x_get_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
1585 x_return_status OUT NOCOPY VARCHAR2,
1586 x_ret_record_level OUT NOCOPY VARCHAR2,
1587 p_getone OUT NOCOPY NUMBER);
1588
1589 /********************************************************************************
1590 * *
1591 * Name : Do_Get_Registration_Accts *
1592 * Purpose : Get Tax Registration Information of Accounts. *
1593 * *
1594 ********************************************************************************/
1595 PROCEDURE Do_Get_Registration_Accts
1596 (p_party_tax_profile_id IN NUMBER,
1597 p_account_id IN ZX_REGISTRATIONS.ACCOUNT_ID%TYPE,
1598 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
1599 x_get_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
1600 x_return_status OUT NOCOPY VARCHAR2,
1601 x_ret_record_level OUT NOCOPY VARCHAR2,
1602 p_getone OUT NOCOPY NUMBER);
1603
1604 /********************************************************************************
1605 * *
1606 * Name : Do_Get_Registration_Party *
1607 * Purpose : Get Tax Registration Information of Party. *
1608 * *
1609 ********************************************************************************/
1610 PROCEDURE Do_Get_Registration_Party
1611 (p_party_tax_profile_id IN NUMBER,
1612 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
1613 x_get_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
1614 x_return_status OUT NOCOPY VARCHAR2,
1615 x_ret_record_level OUT NOCOPY VARCHAR2,
1616 p_getone OUT NOCOPY NUMBER);
1617
1618 /********************************************************************************
1619 * Private Procedures *
1620 ********************************************************************************/
1621
1622 /********************************************************************************
1623 * *
1624 * Name : Do_Get_Registration_Tax *
1625 * Purpose : Get Tax Registration Information of Tax Regime, Tax, Jurisdiction *
1626 * level. *
1627 * *
1628 ********************************************************************************/
1629 PROCEDURE Do_Get_Registration_Tax
1630 (p_ptp_id IN zx_party_tax_profile.party_tax_profile_id%TYPE,
1631 p_tax_regime_code IN ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
1632 p_tax IN ZX_TAXES_B.TAX%TYPE,
1633 p_jurisdiction_code IN ZX_JURISDICTIONS_B.TAX_JURISDICTION_CODE%TYPE,
1634 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
1635 x_get_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
1636 x_return_status OUT NOCOPY VARCHAR2,
1637 x_ret_record_level OUT NOCOPY VARCHAR2,
1638 p_getone OUT NOCOPY NUMBER)
1639 IS
1640
1641 -----------------------------
1642 -- Local variables definition
1643 -----------------------------
1644 l_ptp_type_code zx_party_tax_profile.party_type_code%type;
1645
1646 -- Logging Infra
1647 l_procedure_name CONSTANT VARCHAR2(30) := 'Do_Get_Registration_Tax';
1648 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1649
1650 -----------------------------------------------------------------
1651 -- Get the Tax Registration Information
1652 -- Tax Registration should be valid on the Tax Determination Date
1653 -----------------------------------------------------------------
1654
1655 CURSOR c_get_registration_tax (c_party_tax_profile_id NUMBER,
1656 c_tax_regime_code ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
1657 c_tax ZX_TAXES_B.TAX%TYPE,
1658 c_jurisdiction_code ZX_JURISDICTIONS_B.TAX_JURISDICTION_CODE%TYPE,
1659 c_tax_determine_date ZX_LINES.TAX_DETERMINE_DATE%TYPE)
1660 IS
1661 SELECT 'N' dummy_flag,
1662 reg.Registration_id,
1663 reg.Registration_Type_Code,
1664 reg.Registration_Number,
1665 reg.Validation_Rule,
1666 reg.Tax_Authority_id,
1667 reg.Rep_Tax_Authority_id,
1668 reg.Coll_Tax_Authority_id,
1669 reg.Rounding_Rule_Code,
1670 reg.Tax_Jurisdiction_Code,
1671 reg.Self_Assess_Flag,
1672 reg.Registration_Status_Code,
1673 reg.Registration_Source_Code,
1674 reg.Registration_Reason_Code,
1675 reg.Party_Tax_Profile_id,
1676 reg.Tax,
1677 reg.Tax_Regime_Code,
1678 reg.Inclusive_Tax_Flag,
1679 -- reg.Has_Tax_Exemptions_Flag,
1680 reg.Effective_From,
1681 reg.Effective_To,
1682 reg.Rep_Party_Tax_Name,
1683 reg.Legal_Registration_id,
1684 reg.Default_Registration_Flag,
1685 reg.Bank_id,
1686 reg.Bank_Branch_id,
1687 reg.Bank_Account_Num,
1688 reg.Legal_Location_id,
1689 reg.Record_Type_Code,
1690 reg.Request_id,
1691 reg.Program_Application_id,
1692 reg.Program_id,
1693 reg.Program_Login_id,
1694 reg.ACCOUNT_SITE_ID,
1695 -- reg.Site_Use_id,
1696 null, -- reg.Geo_Type_Classification_Code,
1697 reg.ACCOUNT_ID,
1698 reg.tax_classification_code,
1699 reg.attribute1,
1700 reg.attribute2,
1701 reg.attribute3,
1702 reg.attribute4,
1703 reg.attribute5,
1704 reg.attribute6,
1705 reg.attribute7,
1706 reg.attribute8,
1707 reg.attribute9,
1708 reg.attribute10,
1709 reg.attribute11,
1710 reg.attribute12,
1711 reg.attribute13,
1712 reg.attribute14,
1713 reg.attribute15,
1714 reg.attribute_category,
1715 ptp.party_type_code,
1716 ptp.supplier_flag,
1717 ptp.customer_flag,
1718 ptp.site_flag,
1719 ptp.process_for_applicability_flag,
1720 ptp.rounding_level_code,
1721 ptp.withholding_start_date,
1722 ptp.allow_awt_flag,
1723 ptp.use_le_as_subscriber_flag,
1724 ptp.legal_establishment_flag,
1725 ptp.first_party_le_flag,
1726 ptp.reporting_authority_flag,
1727 ptp.collecting_authority_flag,
1728 ptp.provider_type_code,
1729 ptp.create_awt_dists_type_code,
1730 ptp.create_awt_invoices_type_code,
1731 ptp.allow_offset_tax_flag,
1732 ptp.effective_from_use_le,
1733 ptp.party_id,
1734 ptp.rep_registration_number
1735 FROM zx_registrations reg,
1736 zx_party_tax_profile ptp
1737 WHERE reg.party_tax_profile_id = c_party_tax_profile_id
1738 AND reg.party_tax_profile_id = ptp.party_tax_profile_id
1739 AND nvl(reg.tax_regime_code,1) = nvl(c_tax_regime_code,1)
1740 AND nvl(reg.tax,nvl(c_tax,1)) = nvl(c_tax,1)
1741 AND nvl(reg.tax_jurisdiction_code,nvl(c_jurisdiction_code,1)) = nvl(c_jurisdiction_code,1)
1742 AND c_tax_determine_date >= reg.effective_from
1743 AND (c_tax_determine_date <= reg.effective_to OR reg.effective_to IS NULL);
1744
1745 -- dario1
1746 CURSOR c_ptp_type (c_ptp_id number) IS
1747 SELECT party_type_code
1748 FROM zx_party_tax_profile
1749 WHERE party_tax_profile_id = c_ptp_id;
1750
1751 -- variables for caching
1752 L_TAX_REGIME_CODE ZX_REGIMES_B.TAX_REGIME_CODE%TYPE;
1753 L_TAX ZX_TAXES_B.TAX%TYPE;
1754 L_JURISDICTION_CODE ZX_JURISDICTIONS_B.TAX_JURISDICTION_CODE%TYPE;
1755 L_INDEX BINARY_INTEGER;
1756 DUMMY_REGISTRATION_REC ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC;
1757
1758 BEGIN
1759
1760 -- Logging Infra: Setting up runtime level
1761 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1762
1763 -- Initialize return status
1764 x_return_status := FND_API.G_RET_STS_SUCCESS;
1765
1766 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1767 l_log_msg := l_procedure_name||'(+)';
1768 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1769 END IF;
1770
1771
1772 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1773 l_log_msg := 'Parameters ';
1774 l_log_msg := l_log_msg||' p_ptp_id: '||to_char(p_ptp_id);
1775 l_log_msg := l_log_msg||' p_tax_regime_code: '||p_tax_regime_code;
1776 l_log_msg := l_log_msg||' p_tax: '||p_tax;
1777 l_log_msg := l_log_msg||' p_jurisdiction_code: '||p_jurisdiction_code;
1778 l_log_msg := l_log_msg||' p_tax_determine_date: '||to_char(p_tax_determine_date,'DD-MON-RRRR');
1779 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1780 END IF;
1781
1782 l_tax_regime_code := nvl(p_tax_regime_code, c_tax_regime_code_dummy);
1783 l_tax := nvl(p_tax, c_tax_dummy);
1784 l_jurisdiction_code := nvl(p_jurisdiction_code, c_jurisdiction_code_dummy);
1785
1786 l_index := DBMS_UTILITY.get_hash_value(to_char(p_ptp_id)||l_tax_regime_code||l_tax||l_jurisdiction_code,1,8192);
1787
1788 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1789 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'l_index = '||l_index);
1790 END IF;
1791
1792 IF ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl.exists(l_index)
1793 AND ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl(l_index).party_tax_profile_id = p_ptp_id THEN -- found in cache
1794
1795 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1796 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'Record found in cache for l_index = '||l_index||
1797 ' Dummy_flag = '||ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl(l_index).dummy_flag);
1798 END IF;
1799
1800 IF ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl(l_index).dummy_flag = 'Y' then
1801
1802 p_getone:=0;
1803
1804 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1805 l_log_msg := 'The same combination already searched previously with an unsuccessful hit' ;
1806 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1807 END IF;
1808
1809 ELSE
1810
1811 x_get_registration_rec := ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl(l_index);
1812
1813 -- check the date effectiveness of the record found in cahce
1814 IF p_tax_determine_date >= x_get_registration_rec.effective_from
1815 AND p_tax_determine_date <= nvl(x_get_registration_rec.effective_to,p_tax_determine_date)
1816 THEN
1817
1818 p_getone:=1;
1819 -- Bug#5520167- set status to SUCCESS as a record
1820 -- is found in cache structure and not dummy
1821 --
1822 x_return_status := FND_API.G_RET_STS_SUCCESS;
1823
1824
1825 IF x_get_registration_rec.tax_jurisdiction_code is NOT NULL then
1826 x_ret_record_level := 'JURISDICTION';
1827 ELSIF x_get_registration_rec.tax_jurisdiction_code is NULL
1828 AND x_get_registration_rec.tax is NOT NULL then
1829 x_ret_record_level := 'TAX';
1830 ELSIF x_get_registration_rec.tax_regime_code is NOT NULL
1831 AND x_get_registration_rec.tax is NULL
1832 AND x_get_registration_rec.tax_jurisdiction_code is NULL then
1833 x_ret_record_level := 'TAX_REGIME';
1834 ELSE
1835 x_ret_record_level := 'NULL_REGIME';
1836 END IF;
1837 END IF;
1838 END IF; -- dummy flag
1839
1840 ELSE -- not found in cache
1841
1842 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1843 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'Record NOT found in cache for l_index = '||l_index);
1844 END IF;
1845
1846 p_getone := 0;
1847 -- If the parameters 'Tax' and 'Jurisdiction' are null, and 'Regime' is not null then look for Regime evel.
1848 IF (p_tax_regime_code IS NOT NULL AND p_tax IS NULL
1849 AND p_jurisdiction_code IS NULL) THEN
1850 x_ret_record_level := 'TAX_REGIME';
1851 OPEN c_get_registration_tax(p_ptp_id
1852 ,p_tax_regime_code
1853 ,NULL
1854 ,NULL
1855 ,p_tax_determine_date);
1856 -- If the parameters for Jurisdiction is null, and Regime and Tax are not null then look for Tax level.
1857 ELSIF (p_tax_regime_code IS NOT NULL AND p_tax IS NOT NULL
1858 AND p_jurisdiction_code IS NULL) THEN
1859 x_ret_record_level := 'TAX';
1860 OPEN c_get_registration_tax(p_ptp_id
1861 ,p_tax_regime_code
1862 ,p_tax
1863 ,NULL
1864 ,p_tax_determine_date);
1865 -- If the parameters for Jurisdiction, Regime, and Tax are not null then look for Jurisdiction level.
1866 ELSIF (p_tax_regime_code IS NOT NULL AND p_tax IS NOT NULL
1867 AND p_jurisdiction_code IS NOT NULL) THEN
1868 x_ret_record_level := 'JURISDICTION';
1869 OPEN c_get_registration_tax(p_ptp_id
1870 ,p_tax_regime_code
1871 ,p_tax
1872 ,p_jurisdiction_code
1873 ,p_tax_determine_date);
1874
1875 ELSIF (p_tax_regime_code IS NULL AND p_tax IS NULL
1876 AND p_jurisdiction_code IS NULL) THEN
1877 x_ret_record_level := 'NULL_REGIME';
1878 OPEN c_get_registration_tax(p_ptp_id
1879 ,NULL
1880 ,NULL
1881 ,NULL
1882 ,p_tax_determine_date);
1883
1884 END IF; -- Check p tax regime code is not null and tax and jur are null
1885
1886 FETCH c_get_registration_tax INTO x_get_registration_rec;
1887 -- Got registration
1888 IF c_get_registration_tax%NOTFOUND THEN
1889 p_getone := 0;
1890 -- Bug#5520167- set flag to upper case Y
1891 DUMMY_REGISTRATION_REC.DUMMY_FLAG := 'Y';
1892
1893 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1894 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'Creating a dummy Record for l_index = '||l_index);
1895 END IF;
1896
1897
1898 -- This assignment statement is to mark that we already searched the database for
1899 -- this combination of ptp_id, tax_regime_code, Tax, tax_jurisdiction_code and
1900 -- did not find a record. When we look for a registration record for the same
1901 -- combination for another transaction, we will avoid unnecessary hit against
1902 -- the databse just because a record did not exist in the cache.
1903
1904
1905 ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl(l_index) := DUMMY_REGISTRATION_REC;
1906
1907 ELSE
1908 p_getone:=1;
1909 x_return_status := FND_API.G_RET_STS_SUCCESS;
1910
1911 l_index := DBMS_UTILITY.get_hash_value(to_char(p_ptp_id)||
1912 nvl(x_get_registration_rec.tax_regime_code,c_tax_regime_code_dummy)||
1913 nvl(x_get_registration_rec.tax,c_tax_dummy)||
1914 nvl(x_get_registration_rec.tax_jurisdiction_code,c_jurisdiction_code_dummy),1,8192);
1915
1916 ZX_GLOBAL_STRUCTURES_PKG.g_registration_info_tbl(l_index) := x_get_registration_rec;
1917
1918 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1919 l_log_msg := 'Keys in the Index: ';
1920 l_log_msg := l_log_msg||' p_ptp_id: '||to_char(p_ptp_id);
1921 l_log_msg := l_log_msg||' tax_regime_code: '||nvl(x_get_registration_rec.tax_regime_code,c_tax_regime_code_dummy);
1922 l_log_msg := l_log_msg||' p_tax: '||nvl(x_get_registration_rec.tax,c_tax_dummy);
1923 l_log_msg := l_log_msg||' p_jurisdiction_code: '||nvl(x_get_registration_rec.tax_jurisdiction_code,c_jurisdiction_code_dummy);
1924 l_log_msg := l_log_msg||' l_index: '||to_char(l_index);
1925 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1926 END IF;
1927
1928
1929 END IF; -- Got registrations
1930 CLOSE c_get_registration_tax;
1931
1932 END IF; -- found in cache
1933
1934 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1935 l_log_msg := l_procedure_name||'(-)';
1936 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1937 END IF;
1938 -- Fails
1939 EXCEPTION
1940 WHEN NO_DATA_FOUND THEN
1941 NULL;
1942 WHEN OTHERS THEN
1943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1944 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1945 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1946
1947 -- Logging Infra: Statement level
1948 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1949 l_log_msg := 'Error Message: '||SQLERRM;
1950 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1951 END IF;
1952 -- Logging Infra: Statement level
1953 END Do_Get_Registration_Tax;
1954
1955 /*******************************************************************************
1956 * *
1957 * Name : Do_Get_Sup_Site *
1958 * Purpose : Get Tax Registration Information from sup sites *
1959 * *
1960 *******************************************************************************/
1961 PROCEDURE Do_Get_Sup_Site
1962 (p_party_tax_profile_id IN NUMBER,
1963 p_account_id IN ZX_REGISTRATIONS.ACCOUNT_ID%TYPE,
1964 p_account_site_id IN ZX_REGISTRATIONS.ACCOUNT_SITE_ID%TYPE,
1965 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
1966 x_get_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
1967 x_return_status OUT NOCOPY VARCHAR2,
1968 x_ret_record_level OUT NOCOPY VARCHAR2,
1969 p_getone OUT NOCOPY NUMBER)
1970 IS
1971
1972 -----------------------------
1973 -- Local variables definition
1974 -----------------------------
1975 l_ptp_id NUMBER;
1976 l_ptp_type_code VARCHAR2(30);
1977
1978 l_ap_tax_rounding_rule VARCHAR2(10);
1979 l_tax_rounding_level VARCHAR2(10);
1980 l_auto_tax_calc_flag ap_supplier_sites_all.auto_tax_calc_flag%TYPE;
1981 l_vat_code ap_supplier_sites_all.vat_code%TYPE;
1982 l_vat_registration_num ap_supplier_sites_all.vat_registration_num%TYPE;
1983
1984 -- Added for Bug#12980272
1985 l_inclusive_tax_flag ap_supplier_sites_all.amount_includes_tax_flag%TYPE;
1986
1987 -- Logging Infra
1988 l_procedure_name CONSTANT VARCHAR2(30) := 'Do_Get_Sup_Site';
1989 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1990
1991 -----------------------------------------------------------------
1992 -- Get the Tax Registration Information
1993 -- Tax Registration should be valid on the Tax Determination Date
1994 -----------------------------------------------------------------
1995 CURSOR c_supplier_ptp
1996 IS
1997 SELECT
1998 DECODE(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST',NULL) tax_rounding_rule
1999 ,DECODE(NVL(povs.Auto_Tax_Calc_Flag,'Y'),'N','N','Y') Auto_Tax_Calc_Flag
2000 ,povs.VAT_Code
2001 ,povs.VAT_Registration_Num
2002 ,DECODE(povs.Auto_Tax_Calc_Flag,
2003 'L','LINE',
2004 'H','HEADER',
2005 'T','HEADER',
2006 NULL) tax_rounding_level
2007 ,povs.amount_includes_tax_flag -- Added for Bug#12980272
2008 FROM ap_supplier_sites_all povs
2009 WHERE povs.vendor_id = p_account_id
2010 AND povs.vendor_site_id = p_account_site_id;
2011
2012 l_supp_site_info_rec ZX_GLOBAL_STRUCTURES_PKG.supp_site_info_rec_type;
2013
2014 BEGIN
2015
2016 -- Logging Infra: Setting up runtime level
2017 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2018
2019 -- Initialize return status
2020 x_return_status := FND_API.G_RET_STS_SUCCESS;
2021
2022 -- Logging Infra: Procedure level
2023 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2024 l_log_msg := l_procedure_name||'(+)';
2025 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2026 END IF;
2027
2028 -- Logging Infra: Statement level
2029 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2030 l_log_msg := 'Parameters ';
2031 l_log_msg := l_log_msg||' p_party_tax_profile_id: '||to_char(p_party_tax_profile_id);
2032 l_log_msg := l_log_msg||' p_account_site_id: '||p_account_site_id;
2033 l_log_msg := l_log_msg||' p_tax_determine_date: '||to_char(p_tax_determine_date,'DD-MON-RRRR');
2034 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2035 END IF;
2036 -- Logging Infra: Statement level
2037
2038 IF ZX_GLOBAL_STRUCTURES_PKG.g_supp_site_info_tbl.exists(p_account_site_id) THEN
2039
2040 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2041 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,
2042 'Vendor site record found in cache for vendor site id:'||to_char(p_account_site_id));
2043 END IF;
2044
2045 l_supp_site_info_rec := ZX_GLOBAL_STRUCTURES_PKG.g_supp_site_info_tbl(p_account_site_id);
2046
2047 x_get_registration_rec.Rounding_Rule_Code := l_supp_site_info_rec.TAX_ROUNDING_RULE;
2048 x_get_registration_rec.process_for_applicability_flag := l_supp_site_info_rec.Auto_Tax_Calc_Flag;
2049 x_get_registration_rec.tax_classification_code := l_supp_site_info_rec.VAT_CODE;
2050 x_get_registration_rec.Rounding_level_Code := l_supp_site_info_rec.TAX_ROUNDING_LEVEL;
2051 x_get_registration_rec.Registration_number := l_supp_site_info_rec.VAT_REGISTRATION_NUM;
2052
2053 p_getone:=1;
2054
2055 ELSE
2056
2057 OPEN c_supplier_ptp;
2058 FETCH c_supplier_ptp INTO l_ap_tax_rounding_rule
2059 ,l_auto_tax_calc_flag
2060 ,l_vat_code
2061 ,l_vat_registration_num
2062 ,l_tax_rounding_level
2063 ,l_inclusive_tax_flag; -- Added for Bug#12980272
2064 -- Got registration
2065 IF c_supplier_ptp%NOTFOUND THEN
2066 p_getone := 0;
2067 ELSE
2068 p_getone:=1;
2069 x_return_status := FND_API.G_RET_STS_SUCCESS;
2070 -- Pupulate the stucture
2071 x_get_registration_rec.Rounding_Rule_Code := l_ap_tax_rounding_rule;
2072 x_get_registration_rec.process_for_applicability_flag := l_auto_tax_calc_flag;
2073 x_get_registration_rec.tax_classification_code := l_vat_code;
2074 x_get_registration_rec.Registration_number := l_vat_registration_num;
2075
2076 ZX_GLOBAL_STRUCTURES_PKG.g_supp_site_info_tbl(p_account_site_id).TAX_ROUNDING_RULE :=
2077 l_ap_tax_rounding_rule;
2078 ZX_GLOBAL_STRUCTURES_PKG.g_supp_site_info_tbl(p_account_site_id).Auto_Tax_Calc_Flag :=
2079 l_auto_tax_calc_flag;
2080 ZX_GLOBAL_STRUCTURES_PKG.g_supp_site_info_tbl(p_account_site_id).VAT_CODE := l_vat_code;
2081 ZX_GLOBAL_STRUCTURES_PKG.g_supp_site_info_tbl(p_account_site_id).TAX_ROUNDING_LEVEL :=
2082 l_tax_rounding_level;
2083 ZX_GLOBAL_STRUCTURES_PKG.g_supp_site_info_tbl(p_account_site_id).VAT_REGISTRATION_NUM :=
2084 l_vat_registration_num;
2085
2086 -- Added for Bug#12980272
2087 ZX_GLOBAL_STRUCTURES_PKG.g_supp_site_info_tbl(p_account_site_id).AMOUNT_INCLUDES_TAX_FLAG :=
2088 l_inclusive_tax_flag;
2089
2090 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2091 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,
2092 'AUTO_TAX_CALC_FLAG = '||
2093 ZX_GLOBAL_STRUCTURES_PKG.g_supp_site_info_tbl(p_account_site_id).AUTO_TAX_CALC_FLAG||
2094 ', AMOUNT_INCLUDES_TAX_FLAG = '||
2095 ZX_GLOBAL_STRUCTURES_PKG.g_supp_site_info_tbl(p_account_site_id).AMOUNT_INCLUDES_TAX_FLAG);
2096 END IF;
2097
2098 END IF; -- Got registrations
2099
2100 CLOSE c_supplier_ptp;
2101 -- Logging Infra: Procedure level
2102 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2103 l_log_msg := l_procedure_name||'(-)';
2104 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2105 END IF;
2106 -- Fails
2107 END IF;
2108 EXCEPTION
2109 WHEN NO_DATA_FOUND THEN
2110 NULL;
2111 WHEN OTHERS THEN
2112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2113 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
2114 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
2115
2116 -- Logging Infra: Statement level
2117 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2118 l_log_msg := 'Error Message: '||SQLERRM;
2119 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2120 END IF;
2121 -- Logging Infra: Statement level
2122 END Do_Get_Sup_Site;
2123
2124 /********************************************************************************
2125 * *
2126 * Name : Do_Get_Reg_Site_Uses *
2127 * Purpose : Get Tax Registration Information of Accounts. *
2128 * *
2129 ********************************************************************************/
2130 PROCEDURE Do_Get_Reg_Site_Uses
2131 (p_party_tax_profile_id IN NUMBER,
2132 p_account_id IN ZX_REGISTRATIONS.ACCOUNT_ID%TYPE,
2133 p_account_site_id IN ZX_REGISTRATIONS.ACCOUNT_SITE_ID%TYPE,
2134 p_site_use_id IN HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID%TYPE,
2135 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
2136 x_get_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
2137 x_return_status OUT NOCOPY VARCHAR2,
2138 x_ret_record_level OUT NOCOPY VARCHAR2,
2139 p_getone OUT NOCOPY NUMBER)
2140 IS
2141
2142 -----------------------------
2143 -- Local variables definition
2144 -----------------------------
2145 l_ptp_id NUMBER;
2146 l_ptp_type_code VARCHAR2(30);
2147
2148 -- Logging Infra
2149 l_procedure_name CONSTANT VARCHAR2(30) := 'Do_Get_Reg_Site_Uses';
2150 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2151
2152 -----------------------------------------------------------------
2153 -- Get the Tax Registration Information
2154 -- Tax Registration should be valid on the Tax Determination Date
2155 -----------------------------------------------------------------
2156 CURSOR c_site_uses
2157 IS
2158 SELECT
2159 csu.Tax_Reference
2160 ,nvl(csu.Tax_Code,caa.tax_code) tax_code
2161 ,nvl(csu.Tax_Rounding_rule,caa.tax_rounding_rule) tax_rounding_rule
2162 ,nvl(csu.tax_header_level_flag, caa.tax_header_level_flag) tax_header_level_flag
2163 ,csu.Tax_Classification
2164 FROM hz_cust_site_uses_all csu
2165 ,hz_cust_acct_sites cas
2166 ,hz_cust_accounts caa
2167 WHERE csu.site_use_id = p_site_use_id
2168 AND csu.cust_acct_site_id = p_account_site_id
2169 AND csu.cust_acct_site_id = cas.cust_acct_site_id
2170 AND cas.cust_account_id = caa.cust_account_id
2171 AND caa.cust_account_id = p_account_id;
2172
2173 BEGIN
2174
2175 -- Logging Infra: Setting up runtime level
2176 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2177
2178 -- Initialize return status
2179 x_return_status := FND_API.G_RET_STS_SUCCESS;
2180
2181 -- Logging Infra: Procedure level
2182 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2183 l_log_msg := l_procedure_name||'(+)';
2184 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2185 END IF;
2186
2187 -- Logging Infra: Statement level
2188 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2189 l_log_msg := 'Parameters ';
2190 l_log_msg := l_log_msg||' p_party_tax_profile_id: '||to_char(p_party_tax_profile_id);
2191 l_log_msg := l_log_msg||' p_account_site_id: '||p_account_site_id;
2192 l_log_msg := l_log_msg||' p_site_use_id: '||p_site_use_id;
2193 l_log_msg := l_log_msg||' p_tax_determine_date: '||to_char(p_tax_determine_date,'DD-MON-RRRR');
2194 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2195 END IF;
2196 -- Logging Infra: Statement level
2197 p_getone := 0;
2198
2199 IF ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl.exists(p_site_use_id) then
2200 x_get_registration_rec.Rounding_Rule_Code:=
2201 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).tax_rounding_rule;
2202 x_get_registration_rec.rounding_level_code :=
2203 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).tax_header_level_flag;
2204 x_get_registration_rec.tax_classification_code :=
2205 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).tax_code;
2206 x_get_registration_rec.geo_type_classification_code:=
2207 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).Tax_Classification;
2208
2209 -- bug#6438009: populate Registration_number
2210 x_get_registration_rec.Registration_number :=
2211 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).tax_reference;
2212
2213 p_getone:=1;
2214
2215 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2216 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'Site Use information found in cache');
2217 END IF;
2218 ELSE
2219 For my_reg IN c_site_uses Loop
2220 -- Got registration
2221 p_getone:=1;
2222 x_return_status := FND_API.G_RET_STS_SUCCESS;
2223
2224 -- Populate the stucture
2225 x_get_registration_rec.Rounding_Rule_Code:= my_reg.tax_rounding_rule;
2226 x_get_registration_rec.rounding_level_code := my_reg.tax_header_level_flag;
2227 x_get_registration_rec.tax_classification_code := my_reg.tax_code;
2228 x_get_registration_rec.geo_type_classification_code:= my_reg.Tax_Classification;
2229
2230 -- bug#6438009: populate Registration_number
2231 x_get_registration_rec.Registration_number := my_reg.tax_reference;
2232
2233 -- Populate the cache
2234 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).site_use_id := p_site_use_id;
2235 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).tax_rounding_rule:= my_reg.tax_rounding_rule;
2236 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).tax_header_level_flag := my_reg.tax_header_level_flag;
2237 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).tax_code := my_reg.tax_code;
2238 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).Tax_Classification:= my_reg.Tax_Classification;
2239 ZX_GLOBAL_STRUCTURES_PKG.g_cust_site_use_info_tbl(p_site_use_id).tax_reference := my_reg.tax_reference;
2240
2241 return;
2242 END LOOP;
2243 END IF;
2244
2245 -- Logging Infra: Procedure level
2246 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2247 l_log_msg := l_procedure_name||'(-)';
2248 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2249 END IF;
2250 -- Fails
2251 EXCEPTION
2252 WHEN NO_DATA_FOUND THEN
2253 NULL;
2254 WHEN OTHERS THEN
2255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2256 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
2257 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
2258
2259 -- Logging Infra: Statement level
2260 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2261 l_log_msg := 'Error Message: '||SQLERRM;
2262 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2263 END IF;
2264 -- Logging Infra: Statement level
2265 END Do_Get_Reg_Site_Uses;
2266
2267 /********************************************************************************
2268 * *
2269 * Name : Do_Get_Registration_Accts *
2270 * Purpose : Get Tax Registration Information of Accounts. *
2271 * *
2272 ********************************************************************************/
2273 PROCEDURE Do_Get_Registration_Accts
2274 (p_party_tax_profile_id IN NUMBER,
2275 p_account_id IN ZX_REGISTRATIONS.ACCOUNT_ID%TYPE,
2276 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
2277 x_get_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
2278 x_return_status OUT NOCOPY VARCHAR2,
2279 x_ret_record_level OUT NOCOPY VARCHAR2,
2280 p_getone OUT NOCOPY NUMBER)
2281 IS
2282 -----------------------------
2283 -- Local variables definition
2284 -----------------------------
2285 l_ptp_id NUMBER;
2286 l_ptp_type_code VARCHAR2(30);
2287
2288 -- Logging Infra
2289 l_procedure_name CONSTANT VARCHAR2(30) := 'Do_Get_Registration_Accts';
2290 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2291 -----------------------------------------------------------------
2292 -- Get the Tax Registration Information
2293 -- Tax Registration should be valid on the Tax Determination Date
2294 -----------------------------------------------------------------
2295 CURSOR c_customer_account
2296 IS
2297 SELECT
2298 caa.tax_code tax_code
2299 ,caa.tax_rounding_rule tax_rounding_rule
2300 ,caa.tax_header_level_flag tax_header_level_flag
2301 FROM
2302 hz_cust_accounts caa
2303 WHERE caa.cust_account_id = p_account_id;
2304
2305 BEGIN
2306
2307 -- Logging Infra: Setting up runtime level
2308 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2309
2310 -- Initialize return status
2311 x_return_status := FND_API.G_RET_STS_SUCCESS;
2312
2313 -- Logging Infra: Procedure level
2314 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2315 l_log_msg := l_procedure_name||'(+)';
2316 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2317 END IF;
2318
2319 -- Logging Infra: Statement level
2320 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2321 l_log_msg := 'Parameters ';
2322 l_log_msg := l_log_msg||' p_party_tax_profile_id: '||to_char(p_party_tax_profile_id);
2323 l_log_msg := l_log_msg||' p_account_id: '||p_account_id;
2324 l_log_msg := l_log_msg||' p_tax_determine_date: '||to_char(p_tax_determine_date,'DD-MON-RRRR');
2325 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2326 END IF;
2327 -- Logging Infra: Statement level
2328 p_getone := 0;
2329
2330 IF ZX_GLOBAL_STRUCTURES_PKG.g_cust_acct_info_tbl.exists(p_account_id) THEN
2331 x_get_registration_rec.Rounding_Rule_Code:=
2332 ZX_GLOBAL_STRUCTURES_PKG.g_cust_acct_info_tbl(p_account_id).tax_rounding_rule;
2333 x_get_registration_rec.rounding_level_code :=
2334 ZX_GLOBAL_STRUCTURES_PKG.g_cust_acct_info_tbl(p_account_id).tax_header_level_flag;
2335 x_get_registration_rec.tax_classification_code :=
2336 ZX_GLOBAL_STRUCTURES_PKG.g_cust_acct_info_tbl(p_account_id).tax_code;
2337 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2338 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'Cust Account information found in cache');
2339 END IF;
2340 ELSE
2341 For my_reg IN c_customer_account Loop
2342 -- Got registration
2343 p_getone:=1;
2344 x_return_status := FND_API.G_RET_STS_SUCCESS;
2345 -- Pupulate the stucture
2346 x_get_registration_rec.Rounding_Rule_Code:= my_reg.tax_rounding_rule;
2347 x_get_registration_rec.rounding_level_code := my_reg.tax_header_level_flag;
2348 x_get_registration_rec.tax_classification_code := my_reg.tax_code;
2349
2350 ZX_GLOBAL_STRUCTURES_PKG.g_cust_acct_info_tbl(p_account_id).CUST_ACCOUNT_ID := p_account_id;
2351 ZX_GLOBAL_STRUCTURES_PKG.g_cust_acct_info_tbl(p_account_id).TAX_CODE := my_reg.tax_code;
2352 ZX_GLOBAL_STRUCTURES_PKG.g_cust_acct_info_tbl(p_account_id).TAX_ROUNDING_RULE := my_reg.tax_rounding_rule;
2353 ZX_GLOBAL_STRUCTURES_PKG.g_cust_acct_info_tbl(p_account_id).TAX_HEADER_LEVEL_FLAG := my_reg.tax_header_level_flag;
2354 return;
2355 END LOOP;
2356 END IF;
2357
2358 -- Logging Infra: Procedure level
2359 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2360 l_log_msg := l_procedure_name||'(-)';
2361 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2362 END IF;
2363 -- Fails
2364 EXCEPTION
2365 WHEN NO_DATA_FOUND THEN
2366 NULL;
2367 WHEN OTHERS THEN
2368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2369 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
2370 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
2371
2372 -- Logging Infra: Statement level
2373 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2374 l_log_msg := 'Error Message: '||SQLERRM;
2375 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2376 END IF;
2377 -- Logging Infra: Statement level
2378 END Do_Get_Registration_Accts;
2379
2380 /********************************************************************************
2381 * *
2382 * Name : Do_Get_Registration_Party *
2383 * Purpose : Get Tax Registration Information of Party. *
2384 * *
2385 ********************************************************************************/
2386 PROCEDURE Do_Get_Registration_Party
2387 (p_party_tax_profile_id IN NUMBER,
2388 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
2389 x_get_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
2390 x_return_status OUT NOCOPY VARCHAR2,
2391 x_ret_record_level OUT NOCOPY VARCHAR2,
2392 p_getone OUT NOCOPY NUMBER)
2393 IS
2394
2395 l_tbl_index BINARY_INTEGER;
2396
2397 -- Logging Infra
2398 l_procedure_name CONSTANT VARCHAR2(30) := 'Do_Get_Registration_Party';
2399 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2400
2401
2402 BEGIN
2403
2404 -- Logging Infra: Setting up runtime level
2405 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2406
2407 -- Initialize return status
2408 x_return_status := FND_API.G_RET_STS_SUCCESS;
2409
2410 -- Logging Infra: Procedure level
2411 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2412 l_log_msg := l_procedure_name||'(+)';
2413 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2414 END IF;
2415
2416 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2417 l_log_msg := 'Parameters ';
2418 l_log_msg := l_log_msg||' p_party_tax_profile_id: '||to_char(p_party_tax_profile_id);
2419 l_log_msg := l_log_msg||' p_tax_determine_date: '||to_char(p_tax_determine_date,'DD-MON-RRRR');
2420 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2421 END IF;
2422
2423 p_getone := 0;
2424
2425 IF NOT ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL.exists(p_party_tax_profile_id) then
2426
2427 ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO(
2428 P_PARTY_TAX_PROFILE_ID => p_party_tax_profile_id,
2429 X_TBL_INDEX => l_tbl_index,
2430 X_RETURN_STATUS => x_return_status);
2431
2432 END IF;
2433
2434
2435 IF ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL.exists(p_party_tax_profile_id) THEN
2436
2437 p_getone := 1;
2438
2439 x_get_registration_rec.party_type_code :=
2440 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(p_party_tax_profile_id).party_type_code;
2441 x_get_registration_rec.process_for_applicability_flag:=
2442 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(p_party_tax_profile_id).process_for_applicability_flag;
2443 x_get_registration_rec.rounding_level_code:=
2444 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(p_party_tax_profile_id).rounding_level_code;
2445 x_get_registration_rec.withholding_start_date:=
2446 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(p_party_tax_profile_id).withholding_start_date;
2447 x_get_registration_rec.allow_awt_flag:=
2448 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(p_party_tax_profile_id).allow_awt_flag;
2449 x_get_registration_rec.use_le_as_subscriber_flag:=
2450 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(p_party_tax_profile_id).use_le_as_subscriber_flag;
2451 x_get_registration_rec.allow_offset_tax_flag:=
2452 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(p_party_tax_profile_id).allow_offset_tax_flag;
2453 x_get_registration_rec.party_id:=
2454 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(p_party_tax_profile_id).party_id;
2455
2456 -- bug#6438009: populate rep_registration_number
2457 x_get_registration_rec.rep_registration_number :=
2458 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(p_party_tax_profile_id).rep_registration_number;
2459
2460 ELSE
2461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2462
2463 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2464 l_log_msg := 'Party Tax Profile Id is not valid: '||p_party_tax_profile_id;
2465 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2466 END IF;
2467
2468 END IF;
2469
2470 -- Logging Infra: Procedure level
2471 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2472 l_log_msg := l_procedure_name||'(-)';
2473 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
2474 END IF;
2475 -- Fails
2476 EXCEPTION
2477 WHEN NO_DATA_FOUND THEN
2478 NULL;
2479 WHEN OTHERS THEN
2480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2481 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
2482 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
2483
2484 -- Logging Infra: Statement level
2485 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2486 l_log_msg := 'Error Message: '||SQLERRM;
2487 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2488 END IF;
2489 -- Logging Infra: Statement level
2490 END Do_Get_Registration_Party;
2491
2492 /********************************************************************************
2493 * Public Procedures *
2494 ********************************************************************************/
2495
2496 /********************************************************************************
2497 * *
2498 * Name : Get_Tax_Registration *
2499 * Purpose : Get_Tax_Registration the following procedure return the *
2500 * registrations details for a given party. Registrations can be *
2501 * retrieved at 3 levels: Regimes, Taxes, or Jurisdictions. *
2502 * Also for migrated rows regime, tax, and jurisdictions fields will *
2503 * be null. As per bug 4286280. If there are not true registrations *
2504 * the API will look in account site uses and/or accounts to get *
2505 * tax registration information *
2506 ********************************************************************************/
2507
2508 Procedure Get_Tax_Registration(
2509 p_parent_ptp_id IN zx_party_tax_profile.party_tax_profile_id%TYPE,
2510 p_site_ptp_id IN zx_party_tax_profile.party_tax_profile_id%TYPE,
2511 p_account_Type_Code IN zx_registrations.account_type_code%TYPE,
2512 p_tax_determine_date IN ZX_LINES.TAX_DETERMINE_DATE%TYPE,
2513 p_tax IN ZX_TAXES_B.TAX%TYPE,
2514 p_tax_regime_code IN ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
2515 p_jurisdiction_code IN ZX_JURISDICTIONS_B.TAX_JURISDICTION_CODE%TYPE,
2516 p_account_id IN ZX_REGISTRATIONS.ACCOUNT_ID%TYPE,
2517 p_account_site_id IN ZX_REGISTRATIONS.ACCOUNT_SITE_ID%TYPE,
2518 p_site_use_id IN HZ_CUST_SITE_USES_ALL.SITE_USE_ID%TYPE,
2519 p_zx_registration_rec OUT NOCOPY ZX_TCM_CONTROL_PKG.ZX_REGISTRATION_INFO_REC,
2520 p_ret_record_level OUT NOCOPY VARCHAR2,
2521 p_return_status OUT NOCOPY VARCHAR2) IS
2522
2523 l_ptp_id NUMBER;
2524 l_getone NUMBER;
2525 l_ptp_type_code VARCHAR2(30);
2526
2527 -- Logging Infra
2528 l_procedure_name CONSTANT VARCHAR2(30) := 'Get_Tax_Registration';
2529 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2530
2531 BEGIN
2532
2533 -- Logging Infra: Setting up runtime level
2534 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2535
2536 -- Logging Infra: Procedure level
2537 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2538 l_log_msg := l_procedure_name||'(+)';
2539 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2540 END IF;
2541
2542 -- Logging Infra: Statement level
2543 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2544 l_log_msg := 'Parameters ';
2545 l_log_msg := l_log_msg||' p_parent_ptp_id: '||to_char(p_parent_ptp_id)||', ';
2546 l_log_msg := l_log_msg||' p_site_ptp_id: '||to_char(p_site_ptp_id)||', ';
2547 l_log_msg := l_log_msg||' p_tax_determine_date: '||to_char(p_tax_determine_date,'DD-MON-RRRR')||', ';
2548 l_log_msg := l_log_msg||' p_tax: '||p_tax||', ';
2549 l_log_msg := l_log_msg||' p_tax_regime_code: '||p_tax_regime_code||', ';
2550 l_log_msg := l_log_msg||' p_jurisdiction_code: '||p_jurisdiction_code||', ';
2551 l_log_msg := l_log_msg||' p_account_site_id: '||to_char(p_account_site_id)||', ';
2552 l_log_msg := l_log_msg||' p_site_use_id: '||to_char(p_site_use_id)||', ';
2553 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2554 END IF;
2555 -- Logging Infra: Statement level
2556
2557 -- Initialize Return Status and Error Buffer
2558 --
2559 p_return_status := FND_API.G_RET_STS_SUCCESS;
2560 l_getone := 0;
2561 --
2562 -- Always PTP_ID parameter can NOT be NULL
2563 --
2564 IF p_parent_ptp_id IS NULL THEN -- if 1
2565 -- Bug 4939819 - Return error only if the third party account id is also NULL.
2566 -- PTP setup is not mandatory for third parties.
2567 IF p_account_id IS NULL THEN
2568 p_return_status := FND_API.G_RET_STS_ERROR;
2569 p_ret_record_level := NULL;
2570 return;
2571 END IF;
2572 ELSE -- if 1
2573 -- Checking for true registrations
2574 -- IF ((p_tax_regime_code IS NOT NULL) OR (p_tax IS NOT NULL)
2575 -- OR (p_jurisdiction_code IS NOT NULL)) THEN -- if2
2576 IF (p_site_ptp_id is null) and (p_account_id is NULL) and (p_account_site_id IS NULL) THEN -- HQ
2577 -- Checking for etb registrations at HQ only
2578 Do_Get_Registration_Tax(p_parent_ptp_id
2579 ,p_tax_regime_code
2580 ,p_tax
2581 ,p_jurisdiction_code
2582 ,p_tax_determine_date
2583 ,p_zx_registration_rec
2584 ,p_return_status
2585 ,p_ret_record_level
2586 ,l_getone);
2587 IF l_getone = 1 THEN
2588 return;
2589 END IF;
2590 END IF; -- HQ
2591
2592 -- Get tax registrations from the site
2593 IF p_site_ptp_id is not null THEN -- if3
2594 Do_Get_Registration_Tax(p_site_ptp_id
2595 ,p_tax_regime_code
2596 ,p_tax
2597 ,p_jurisdiction_code
2598 ,p_tax_determine_date
2599 ,p_zx_registration_rec
2600 ,p_return_status
2601 ,p_ret_record_level
2602 ,l_getone);
2603 IF l_getone = 1 THEN -- if4
2604 return;
2605 ELSE -- if4 Trying to get a true registration for the parent
2606 Do_Get_Registration_Tax(p_parent_ptp_id
2607 ,p_tax_regime_code
2608 ,p_tax
2609 ,p_jurisdiction_code
2610 ,p_tax_determine_date
2611 ,p_zx_registration_rec
2612 ,p_return_status
2613 ,p_ret_record_level
2614 ,l_getone);
2615 IF l_getone = 1 THEN -- if5
2616 return;
2617 END IF; -- if5
2618 END IF; -- if4
2619 END IF; -- if3 p_site_ptp_id id not null
2620 -- Check if we can get the registration information from ap_supplier_sites_all
2621 -- or hz_cust_accounts or hz_cust_site_uses
2622 --
2623 IF (p_account_id is not NULL) and (p_account_site_id IS NOT NULL) THEN -- if6
2624 -- Check party type code
2625 IF p_account_type_code = 'SUPPLIER' THEN -- if7
2626 -- Get supplier information from ap_suppliers-sites
2627 Do_Get_Sup_Site(p_parent_ptp_id
2628 ,p_account_id
2629 ,p_account_site_id
2630 ,p_tax_determine_date
2631 ,p_zx_registration_rec
2632 ,p_return_status
2633 ,p_ret_record_level
2634 ,l_getone);
2635 IF l_getone = 1 THEN -- if8
2636 return;
2637 End IF; -- if8
2638
2639 ELSIF p_account_type_code = 'CUSTOMER' THEN -- if7
2640 -- Check if account site use parameter is not null
2641 IF p_site_use_id IS NOT NULL THEN -- if9
2642 Do_Get_Reg_Site_Uses(p_parent_ptp_id
2643 ,p_account_id
2644 ,p_account_site_id
2645 ,p_site_use_id
2646 ,p_tax_determine_date
2647 ,p_zx_registration_rec
2648 ,p_return_status
2649 ,p_ret_record_level
2650 ,l_getone);
2651
2652 IF l_getone = 1 THEN -- if10
2653 return;
2654 -- Get registration at account level
2655 ElSE -- if10
2656 Do_Get_Registration_Accts(p_parent_ptp_id
2657 ,p_account_id
2658 ,p_tax_determine_date
2659 ,p_zx_registration_rec
2660 ,p_return_status
2661 ,p_ret_record_level
2662 ,l_getone);
2663 IF l_getone = 1 THEN -- if11
2664 return;
2665 End IF; --if11
2666 End If; -- if10 getone
2667 End IF;-- if9 p_site_use_id is not null
2668 END IF; -- if7 p_account_type
2669 END IF; -- if6 p_account_id is not null
2670 -- Get Registration information at ptp level
2671 IF l_getone = 0 Then -- if12
2672 IF p_site_ptp_id is not null THEN --if13
2673 -- Get registration infomation from the site
2674 Do_Get_Registration_Party(p_site_ptp_id
2675 ,p_tax_determine_date
2676 ,p_zx_registration_rec
2677 ,p_return_status
2678 ,p_ret_record_level
2679 ,l_getone);
2680 IF l_getone = 1 THEN --if14
2681 return;
2682 ELSE -- if14
2683 -- get registration information from the parent
2684 Do_Get_Registration_Party(p_parent_ptp_id
2685 ,p_tax_determine_date
2686 ,p_zx_registration_rec
2687 ,p_return_status
2688 ,p_ret_record_level
2689 ,l_getone);
2690 IF l_getone = 1 THEN -- if15
2691 return;
2692 ELSE -- if15 getone
2693 p_return_status := FND_API.G_RET_STS_ERROR;
2694 p_ret_record_level := NULL;
2695
2696 -- Logging Infra: Procedure level
2697 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN -- if16
2698 l_log_msg := l_procedure_name||'Get Tax Registration did not find any record';
2699 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg);
2700 END IF; --if16
2701 END IF; -- if15 getone
2702 END IF; -- if14 getone site level
2703 END IF; -- if13
2704 END IF; -- if12
2705 -- END IF; --if2
2706 END IF; --if1
2707 -- Logging Infra: Procedure level
2708 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2709 l_log_msg := l_procedure_name||'(-)';
2710 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END', l_log_msg);
2711 END IF;
2712
2713 EXCEPTION
2714 WHEN NO_DATA_FOUND THEN
2715 NULL;
2716 WHEN OTHERS THEN
2717 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2718 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
2719 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
2720
2721 -- Logging Infra: Statement level
2722 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2723 l_log_msg := 'Error Message: '||SQLERRM;
2724 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2725 END IF;
2726 -- Logging Infra: Statement level
2727
2728 END Get_Tax_Registration;
2729
2730
2731 PROCEDURE INITIALIZE_LTE (p_return_status OUT NOCOPY VARCHAR2) IS
2732
2733 l_procedure_name CONSTANT VARCHAR2(30) := 'Initialize LTE';
2734 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2735 l_category_set mtl_category_sets_b.Category_set_ID%TYPE;
2736 l_fc_id ZX_FC_CODES_B.CLASSIFICATION_ID%TYPE;
2737
2738 BEGIN
2739 -- Logging Infra: Setting up runtime level
2740 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2741
2742 -- Initialize return status
2743 p_return_status := FND_API.G_RET_STS_SUCCESS;
2744
2745 -- Logging Infra: Procedure level
2746 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2747 l_log_msg := l_procedure_name||'(+)';
2748 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2749 END IF;
2750
2751 -- Logging Infra: Statement level
2752 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2753 l_log_msg := 'Parameters : None';
2754 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2755 END IF;
2756 -- Logging Infra: Statement level
2757
2758 IF (zx_fc_migrate_pkg.Is_Country_Installed(7004, 'jlbrloc') or
2759 zx_fc_migrate_pkg.Is_Country_Installed(7004, 'jlarloc') or
2760 zx_fc_migrate_pkg.Is_Country_Installed(7004, 'jlcoloc')
2761 )THEN
2762
2763 -- Insert the FISCAL CLASSIFICATION CODE for LTE in level one
2764 zx_fc_migrate_pkg.FIRST_LEVEL_FC_CODE_INSERT('PRODUCT_CATEGORY','FISCAL CLASSIFICATION CODE',
2765 'Fiscal Classification Code',NULL,l_fc_id);
2766
2767 IF Zx_Migrate_Util.IS_INSTALLED('INV') = 'Y' THEN
2768
2769 zx_fc_migrate_pkg.Create_Category_Set ('FISCAL_CLASSIFICATION',
2770 'Fiscal Classification',
2771 'FISCAL_CLASSIFICATION',
2772 'Fiscal Classification');
2773
2774 SELECT Category_set_ID
2775 INTO l_category_set
2776 FROM mtl_category_sets
2777 WHERE Category_Set_Name = 'FISCAL_CLASSIFICATION';
2778
2779 -- Call a common procedure to create FC Types
2780 zx_fc_migrate_pkg.FC_TYPE_INSERT('FISCAL_CLASSIFICATION','Fiscal Classification Code',l_category_set);
2781
2782 -- Call Country Defaults
2783 zx_fc_migrate_pkg.country_default;
2784
2785 -- Create Regime Association for 'FISCAL CLASSIFICATION CODE'
2786 arp_util_tax.debug( 'Creating the regime association to fiscal type.. ');
2787
2788 INSERT ALL INTO
2789 ZX_FC_TYPES_REG_ASSOC
2790 (Tax_regime_code,
2791 classification_type_code,
2792 effective_FROM,
2793 effective_to,
2794 record_type_code,
2795 created_by,
2796 creation_date,
2797 last_updated_by,
2798 last_update_date,
2799 last_update_login,
2800 classif_regime_id,
2801 object_version_number)
2802 VALUES
2803 (TAX_REGIME_CODE,
2804 'FISCAL_CLASSIFICATION',
2805 SYSDATE,
2806 NULL,
2807 'MIGRATED',
2808 fnd_global.user_id,
2809 SYSDATE,
2810 fnd_global.user_id,
2811 SYSDATE,
2812 FND_GLOBAL.CONC_LOGIN_ID,
2813 zx_fc_types_reg_assoc_s.nextval,
2814 1)
2815 SELECT unique tax_regime_code
2816 FROM zx_rates_b
2817 WHERE content_owner_id in
2818 (SELECT unique org_id
2819 FROM zx_product_options_all
2820 WHERE application_id = 222
2821 and tax_method_code='LTE');
2822
2823 END IF; -- Check for Inventory installed
2824
2825 END IF; -- Countries installed.
2826
2827 -- Logging Infra: Procedure level
2828 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2829 l_log_msg := l_procedure_name||'(-)';
2830 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END', l_log_msg);
2831 END IF;
2832
2833
2834 EXCEPTION WHEN OTHERS THEN
2835 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2836 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
2837 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
2838
2839 -- Logging Infra: Statement level
2840 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2841 l_log_msg := 'Error Message: '||SQLERRM;
2842 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2843 END IF;
2844 -- Logging Infra: Statement level
2845
2846 END INITIALIZE_LTE;
2847
2848 END ZX_TCM_CONTROL_PKG;