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