[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_PTP_PKG
Source
1 PACKAGE BODY ZX_TCM_PTP_PKG AS
2 /* $Header: zxcptpb.pls 120.43 2009/06/12 00:06:09 hongliu ship $ */
3
4 -- Logging Infra
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_TCM_PTP_PKG';
6 G_CURRENT_RUNTIME_LEVEL NUMBER;
7 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
8 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
9 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
10 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
11 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
12 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
13 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.PLSQL.ZX_TCM_PTP_PKG';
14
15 TYPE l_ptp_id_rec_type IS RECORD(
16 le_id NUMBER,
17 party_type_code VARCHAR2(240),
18 ptp_id NUMBER
19 );
20
21 TYPE l_ptp_id_tbl_type IS TABLE OF l_ptp_id_rec_type INDEX BY BINARY_INTEGER;
22 l_ptp_id_tbl l_ptp_id_tbl_type;
23
24 /* ========================================================================
25 Procedure: GET_PTP
26 Objective: Retrieve the Party Tax Profile for a given Party
27 There is an special treatment for Establishments.
28 Establishments need to be mapped thru the Legal Associations
29 model, following this logic:
30 1. First Try to get the information from the Location + LE
31 2. Next try Location + Party_ID + LE
32 -- Legal Entity has not provided support for case2 yet
33 3. Next try OU/Inv Org + LE
34 For First Party Legal Entity, since Party Tax Profile
35 stores the Party_ID and the parameter brings the legal
36 entity id it is necessary to get the Party_ID from xle
37 entity profiles.
38 For all other parties the procedure will retrieve the
39 PTP Id directly from Party Tax Profiles.
40 Assumption: Legal Entity will be always not null
41 In Parameters: p_le_id - Legal Entity ID
42 p_party_id - Party ID
43 p_party_type_code - Party Type
44 p_inventory_loc - Location ID
45 OUTPUT Parameters: p_ptp_id - Party Tax Profile ID
46 p_return_status - Success is p_ptp_id is not null
47 ======================================================================== */
48
49 PROCEDURE GET_PTP(
50 p_party_id IN NUMBER,
51 p_Party_Type_Code IN VARCHAR2,
52 p_le_id IN NUMBER,
53 p_inventory_loc IN NUMBER,
54 p_ptp_id OUT NOCOPY NUMBER,
55 p_return_status OUT NOCOPY VARCHAR2)
56 IS
57 CURSOR c_get_ptp_id
58 IS
59 SELECT party_tax_profile_id
60 FROM zx_party_tax_profile
61 WHERE party_id = p_party_id
62 AND Party_Type_Code = p_party_type_code;
63
64 CURSOR c_get_ptp_ou (Biz_Entity Number, p_construct VARCHAR2, p_type VARCHAR2, p_context VARCHAR2) IS
65 SELECT ptp.party_tax_profile_id ptp_id
66 INTO p_ptp_id
67 FROM xle_tax_associations rel
68 ,zx_party_tax_profile ptp
69 ,xle_etb_profiles etb
70 WHERE rel.legal_construct_id = etb.establishment_id
71 AND etb.party_id = ptp.party_id
72 /* added the below condition for Bug 4878175 */
73 AND ptp.party_type_code = p_party_type_code
74 AND rel.entity_id = Biz_Entity
75 AND rel.legal_parent_id = p_le_id
76 AND rel.LEGAL_CONSTRUCT = p_construct
77 AND rel.entity_type = p_type
78 AND rel.context = p_context
79 AND rel.effective_from <= sysdate
80 AND nvl(rel.effective_to,sysdate+1) >= sysdate;
81
82 CURSOR c_get_ptp_inv_org (Biz_Entity Number, p_construct VARCHAR2, p_type VARCHAR2, p_context VARCHAR2) IS
83 SELECT ptp.party_tax_profile_id ptp_id
84 INTO p_ptp_id
85 FROM xle_tax_associations rel
86 ,zx_party_tax_profile ptp
87 ,xle_etb_profiles etb
88 WHERE rel.legal_construct_id = etb.establishment_id
89 AND etb.party_id = ptp.party_id
90 /* added the below condition for Bug 4878175 */
91 AND ptp.party_type_code = p_party_type_code
92 AND rel.entity_id = Biz_Entity
93 AND rel.legal_parent_id = p_le_id
94 AND rel.LEGAL_CONSTRUCT = p_construct
95 AND rel.entity_type = p_type
96 AND rel.context = p_context
97 AND rel.effective_from <= sysdate
98 AND nvl(rel.effective_to,sysdate+1) >= sysdate;
99
100 CURSOR c_get_ptp_stl (p_construct VARCHAR2, p_type VARCHAR2, p_context VARCHAR2) IS
101 SELECT ptp.party_tax_profile_id ptp_id
102 INTO p_ptp_id
103 FROM xle_tax_associations rel
104 ,zx_party_tax_profile ptp
105 ,xle_etb_profiles etb
106 WHERE rel.legal_construct_id = etb.establishment_id
107 AND etb.party_id = ptp.party_id
108 /* added the below condition for Bug 4878175 */
109 AND ptp.party_type_code = p_party_type_code
110 AND rel.entity_id = p_inventory_loc
111 AND rel.legal_parent_id = p_le_id
112 AND rel.LEGAL_CONSTRUCT = p_construct
113 AND rel.entity_type in (p_type)
114 AND rel.context = p_context
115 AND rel.effective_from <= sysdate
116 AND nvl(rel.effective_to,sysdate+1) >= sysdate;
117
118 CURSOR c_get_ptp_invloc (p_construct VARCHAR2, p_type VARCHAR2, p_context VARCHAR2) IS
119 SELECT ptp.party_tax_profile_id ptp_id
120 INTO p_ptp_id
121 FROM xle_tax_associations rel
122 ,zx_party_tax_profile ptp
123 ,xle_etb_profiles etb
124 WHERE rel.legal_construct_id = etb.establishment_id
125 AND etb.party_id = ptp.party_id
126 /* added the below condition for Bug 4878175 */
127 AND ptp.party_type_code = p_party_type_code
128 AND rel.entity_id = p_inventory_loc
129 AND rel.legal_parent_id = p_le_id
130 AND rel.LEGAL_CONSTRUCT = p_construct
131 AND rel.entity_type in (p_type)
132 AND rel.context = p_context
133 AND rel.effective_from <= sysdate
134 AND nvl(rel.effective_to,sysdate+1) >= sysdate;
135
136 l_Unique Integer;
137 l_tbl_index binary_integer;
138
139 -- bug8568734
140 CURSOR get_le_name_csr IS
141 SELECT xle.name
142 FROM xle_entity_profiles xle
143 WHERE xle.legal_entity_id = p_le_id;
144 l_party_name VARCHAR2(240);
145
146 -- Logging Infra
147 l_procedure_name CONSTANT VARCHAR2(30) := '.GET_PTP ';
148 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
149
150 BEGIN
151 --------------------------------------------------------------------------------------
152 -- Logging Infra: Setting up runtime level
153 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
154
155 -- Logging Infra: Statement level
156 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
157 l_log_msg := l_procedure_name||' (+) ';
158 l_log_msg := l_log_msg ||'
159 Parameters '||
160 ' p_party_id: '||to_char(p_party_id)||
161 ' p_Party_Type_Code: '||p_Party_Type_Code||
162 ' p_le_id: '||to_char(p_le_id)||
163 ' p_inventory_loc: '||to_char(p_inventory_loc)||' ';
164 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
165 END IF;
166 -- Logging Infra: Statement level
167 --------------------------------------------------------------------------------------
168
169 -- Checking parameters are not null
170 IF (p_party_type_code is null) THEN
171 -- bug 8569734
172 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173
174 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
175 l_log_msg := 'party type is null for party_id ' || to_char(p_party_id);
176 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name, l_log_msg);
177 END IF;
178 --FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_MESSAGE');
179 RETURN;
180 END IF; -- Null parameters
181
182 -- Initialize P_Ptp_ID
183 p_ptp_id := null;
184
185 IF P_Party_Type_Code = 'LEGAL_ESTABLISHMENT' Then -- If0
186
187 -- First Try to get the information from the Location + LE
188 -- Location id is comming in the parameter p_inventory_loc
189 IF p_inventory_loc IS NOT NULL THEN --If1
190 l_unique := 0;
191 For etb in c_get_ptp_stl ('ESTABLISHMENT', 'SHIP_TO_LOCATION', 'TAX_CALCULATION')LOOP
192 p_ptp_id := etb.ptp_id;
193 l_unique := l_unique + 1;
194 End Loop;
195
196 IF (p_ptp_id IS NOT NULL) THEN
197 IF l_unique = 1 THEN
198 ----------------------------------------------------------------------------------
199 -- Logging Infra: Statement level
200 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
201 l_log_msg := 'Getting PTP from Location and LE.PTP_ID: '||to_char(p_ptp_id)||' ';
202 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
203 END IF;
204 -- Logging Infra: Statement level
205 ----------------------------------------------------------------------------------
206
207 p_return_status := FND_API.G_RET_STS_SUCCESS;
208 RETURN;
209 ELSE
210 /*Reinitialize p_ptp_id */
211 p_ptp_id := NULL;
212 END IF; -- l_unique check
213 END IF; -- p_ptp_id IS NOT NULL
214 END IF; --If1
215 -- Next Try to get the information from the InvLoc + LE
216 -- Location id is comming in the parameter p_inventory_loc
217 IF p_inventory_loc IS NOT NULL THEN --If2
218 l_unique := 0;
219 For etb in c_get_ptp_invloc ('ESTABLISHMENT', 'INVENTORY_LOCATION', 'TAX_CALCULATION') LOOP
220 p_ptp_id := etb.ptp_id;
221 l_unique := l_unique + 1;
222 End Loop;
223
224 IF (p_ptp_id IS NOT NULL) THEN
225 IF l_unique = 1 THEN
226 ---------------------------------------------------------------------------------
227 -- Logging Infra: Statement level
228 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
229 l_log_msg := 'Getting PTP from Location and LE.PTP_ID: '||to_char(p_ptp_id)||' ';
230 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
231 END IF;
232 -- Logging Infra: Statement level
233 ---------------------------------------------------------------------------------
234
235 p_return_status := FND_API.G_RET_STS_SUCCESS;
236 RETURN;
237 ELSE
238 /*Reinitialize p_ptp_id */
239 p_ptp_id := NULL;
240 END IF; -- l_unique check
241 END IF; -- p_ptp_id IS NOT NULL
242 END IF; --If2
243 --
244 -- Next try to get the establishment from the LE + INV Org
245 IF (p_party_id is not null) THEN -- If3
246 l_unique := 0;
247 For etb in c_get_ptp_inv_org (p_party_id, 'ESTABLISHMENT', 'INVENTORY_ORGANIZATION', 'TAX_CALCULATION') LOOP
248 p_ptp_id := etb.ptp_id;
249 l_unique := l_unique + 1;
250 End Loop;
251
252 IF (p_ptp_id IS NOT NULL) THEN
253 IF l_unique = 1 THEN
254 ---------------------------------------------------------------------------------
255 -- Logging Infra: Statement level
256 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
257 l_log_msg := 'Getting PTP from Inv Organization and LE.PTP_ID: '||to_char(p_ptp_id)||' ';
258 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
259 END IF;
260 -- Logging Infra: Statement level
261 ---------------------------------------------------------------------------------
262
263 p_return_status := FND_API.G_RET_STS_SUCCESS;
264 RETURN;
265 ELSE
266 /*Reinitialize p_ptp_id */
267 p_ptp_id := NULL;
268 END IF; -- l_unique check
269 END IF; -- p_ptp_id IS NOT NULL
270 END IF; -- If3
271
272 -- Next try to get the establishment from the LE + OU
273 IF (p_party_id is not null) THEN -- If4
274 l_unique := 0;
275 For etb in c_get_ptp_ou (p_party_id, 'ESTABLISHMENT', 'OPERATING_UNIT', 'TAX_CALCULATION') LOOP
276 p_ptp_id := etb.ptp_id;
277 l_unique := l_unique + 1;
278 End Loop;
279
280 IF (p_ptp_id IS NOT NULL) THEN
281 IF(l_unique = 1) THEN
282 ---------------------------------------------------------------------------------
283 -- Logging Infra: Statement level
284 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
285 l_log_msg := 'Getting PTP from Operating Unit and LE.PTP_ID: '||to_char(p_ptp_id)||' ';
286 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
287 END IF;
288 -- Logging Infra: Statement level
289 ---------------------------------------------------------------------------------
290
291 p_return_status := FND_API.G_RET_STS_SUCCESS;
292 RETURN;
293 ELSE
294 /*Reinitialize p_ptp_id */
295 p_ptp_id := NULL;
296 END IF; -- l_unique check
297 END IF; -- p_ptp_id IS NOT NULL
298 END IF; -- If4
299
300 IF (p_ptp_id IS NULL) THEN -- If5
301 ---------------------------------------------------------------------------------
302 -- Logging Infra: Statement level
303 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
304 l_log_msg := 'There is not an establishment associated. Returning without error.';
305 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
306 END IF;
307 ---------------------------------------------------------------------------------
308 p_return_status := FND_API.G_RET_STS_SUCCESS;
309 END IF; -- If5
310 -- END P_Party_Type_Code = 'LEGAL_ESTABLISHMENT'
311
312 -- Checking First Party
313 ELSIF P_Party_Type_Code = 'FIRST_PARTY' Then
314 -- Getting first party PTP
315 SELECT ptp.party_tax_profile_id
316 INTO p_ptp_id
317 FROM zx_party_tax_profile ptp, xle_entity_profiles xle
318 WHERE xle.legal_entity_id = p_le_id
319 AND ptp.party_id = xle.party_id
320 AND ptp.Party_Type_Code = p_party_type_code;
321
322 -- Set Status Parameter to Success
323 p_return_status := FND_API.G_RET_STS_SUCCESS;
324
325 ---------------------------------------------------------------------------------
326 -- Logging Infra: Statement level
327 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
328 l_log_msg := 'Getting PTP First Party: '||to_char(p_ptp_id)||' ';
329 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
330 END IF;
331 -- Logging Infra: Statement level
332 ---------------------------------------------------------------------------------
333
334 -- Other Party Types
335 ELSE
336
337 GET_PARTY_TAX_PROF_INFO(
338 P_PARTY_ID => p_party_id,
339 P_PARTY_TYPE_CODE => p_party_type_code,
340 X_TBL_INDEX => l_tbl_index,
341 X_RETURN_STATUS => p_return_status);
342
343 IF l_tbl_index is NULL THEN
344
345 -- Bug 4939819 - Return without error if party tax profile setup is not found for
346 -- party of type 'THIRD_PARTY' as it is not mandatory to have PTP setup
347 -- for THIRD_PARTY party type. Return ptp id as NULL.
348
349 IF (P_Party_Type_Code = 'THIRD_PARTY') OR (P_Party_Type_Code = 'THIRD_PARTY_SITE') Then
350 ---------------------------------------------------------------------------------
351 -- Logging Infra: Statement level
352 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
353 l_log_msg := 'Get PTP is not able to return a row for the given party and party type code.
354 Returning with success. Party_id: '
355 ||p_party_type_code||', '||to_char(p_party_id)||' ';
356 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name, l_log_msg);
357 END IF;
358 ---------------------------------------------------------------------------------
359 p_ptp_id := NULL;
360 p_return_status := FND_API.G_RET_STS_SUCCESS;
361 ELSE
362 ---------------------------------------------------------------------------------
363 -- Logging Infra: Statement level
364 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
365 l_log_msg := 'Get PTP is not able to return a row for the given party and party type code.
366 Returning with error. Party_id: '
367 ||p_party_type_code||', '||to_char(p_party_id)||' ';
368 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name, l_log_msg);
369 END IF;
370 ---------------------------------------------------------------------------------
371 -- Bug 4512462
372 FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
373 FND_MESSAGE.SET_TOKEN('PARTY_TYPE', p_party_type_code);
374 p_return_status := FND_API.G_RET_STS_ERROR;
375 END IF;
376 --close c_get_ptp_id;
377 ELSE
378
379 p_ptp_id := ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl(l_tbl_index).party_tax_profile_id;
380
381 -- Logging Infra: Statement level
382 ---------------------------------------------------------------------------------
383 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
384 l_log_msg := 'Getting PTP For Other Party Types: '||p_party_type_code||', '||to_char(p_ptp_id)||' ';
385 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
386 END IF;
387 -- Logging Infra: Statement level
388 ---------------------------------------------------------------------------------
389 p_return_status := FND_API.G_RET_STS_SUCCESS;
390 --close c_get_ptp_id;
391 END IF;
392 END IF; -- End IF for Party Types
393 ---------------------------------------------------------------------------------
394 -- Logging Infra: Procedure level
395 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
396 l_log_msg := l_procedure_name||' (-) ';
397 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.END ', l_log_msg);
398 END IF;
399 ---------------------------------------------------------------------------------
400
401 EXCEPTION
402 WHEN NO_DATA_FOUND THEN
403 -- Logging Infra: Statement level
404 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
405 l_log_msg := 'Get PTP is not able to return a row for the given party. Returning with error. Party_id: '
406 ||p_party_type_code||', '||to_char(p_party_id)||' ';
407 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name, l_log_msg);
408 END IF;
409
410 -- Bug 4512462
411 FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
412 FND_MESSAGE.SET_TOKEN('PARTY_TYPE', p_party_type_code);
413
414 -- bug 8568734
415 IF P_Party_Type_Code = 'FIRST_PARTY' THEN
416 OPEN get_le_name_csr;
417 FETCH get_le_name_csr INTO l_party_name;
418 CLOSE get_le_name_csr;
419 END IF;
420 FND_MESSAGE.SET_TOKEN('PARTY_NAME', l_party_name);
421 p_return_status := FND_API.G_RET_STS_ERROR;
422 WHEN INVALID_CURSOR THEN
423 --bug 8568734
424 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
425 --FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
426 --FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
427 -- Logging Infra: Statement level
428 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
429 l_log_msg := 'Error Message: '||SQLERRM;
430 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
431 END IF;
432 WHEN OTHERS THEN
433 -- bug 8568734
434 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 --FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
436 --FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
437
438 -- Logging Infra: Statement level
439 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
440 l_log_msg := 'Error Message: '||SQLERRM;
441 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
442 END IF;
443 -- Logging Infra: Statement level
444
445 END GET_PTP;
446
447 /* ======================================================================
448 Procedure: GET_PTP_HQ
449 Objective: Retrieve the Party Tax Profile for the HQ Establishment
450 of a given Legal Entity.
451 Assumption: Any Legal Entity will have only one HQ Establishment
452 In Parameters: p_le_id - Legal Entity ID
453 OUTPUT Parameters: p_ptp_id - Party Tax Profile ID
454 p_return_status - Success is p_ptp_id is not null
455 ====================================================================== */
456 PROCEDURE GET_PTP_HQ(
457 p_le_id IN xle_entity_profiles.legal_entity_id%TYPE,
458 p_ptp_id OUT NOCOPY zx_party_tax_profile.party_tax_profile_id%TYPE,
459 p_return_status OUT NOCOPY VARCHAR2)
460 IS
461 CURSOR c_get_ptp_id_hq
462 IS
463 SELECT party_tax_profile_id
464 FROM zx_party_tax_profile ptp,
465 xle_etb_profiles xlep
466 WHERE ptp.party_id = xlep.party_id
467 AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
468 AND xlep.legal_entity_id = p_le_id
469 AND xlep.main_establishment_flag = 'Y';
470
471 -- Logging Infra
472 l_procedure_name CONSTANT VARCHAR2(30) := '.GET_PTP_HQ';
473 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
474
475 l_ptp_id_indx BINARY_INTEGER;
476
477 -- bug8568734
478 CURSOR get_le_name_csr IS
479 SELECT xle.name
480 FROM xle_entity_profiles xle
481 WHERE xle.legal_entity_id = p_le_id;
482 l_party_name VARCHAR2(240);
483
484 BEGIN
485 ---------------------------------------------------------------------------------
486 -- Logging Infra: Setting up runtime level
487 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
488
489 -- Logging Infra: Statement level
490 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
491 l_log_msg := l_procedure_name||'(+)';
492 l_log_msg := l_log_msg||'
493 Parameters: ';
494 l_log_msg := l_log_msg||'p_le_id: '||to_char(p_le_id);
495 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
496 END IF;
497 -- Logging Infra: Statement level
498 ---------------------------------------------------------------------------------
499 l_ptp_id_indx := dbms_utility.get_hash_value('LEGAL_ESTABLISHMENT'||to_char(p_le_id), 1, 8192);
500
501 IF l_ptp_id_tbl.EXISTS(l_ptp_id_indx) AND l_ptp_id_tbl(l_ptp_id_indx).le_id = p_le_id
502 AND l_ptp_id_tbl(l_ptp_id_indx).party_type_code = 'LEGAL_ESTABLISHMENT' THEN
503 p_ptp_id := l_ptp_id_tbl(l_ptp_id_indx).ptp_id;
504 ELSE
505 For myrec IN c_get_ptp_id_hq Loop
506 p_ptp_id := myrec.party_tax_profile_id;
507 End Loop;
508
509 l_ptp_id_tbl(l_ptp_id_indx).le_id := p_le_id;
510 l_ptp_id_tbl(l_ptp_id_indx).party_type_code := 'LEGAL_ESTABLISHMENT';
511 l_ptp_id_tbl(l_ptp_id_indx).ptp_id := p_ptp_id;
512 END IF;
513
514 IF P_PTP_ID is null Then
515 p_return_status := FND_API.G_RET_STS_ERROR;
516 FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
517 -- bug 8568734
518 OPEN get_le_name_csr;
519 FETCH get_le_name_csr INTO l_party_name;
520 CLOSE get_le_name_csr;
521 FND_MESSAGE.SET_TOKEN('PARTY_TYPE', 'LEGAL_ESTABLISHMENT');
522 FND_MESSAGE.SET_TOKEN('PARTY_NAME', 'l_party_name');
523
524 ELSE
525 p_return_status := FND_API.G_RET_STS_SUCCESS;
526 END IF;
527
528 ---------------------------------------------------------------------------------
529 -- Logging Infra: Statement level
530 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
531 l_log_msg := 'PTP for HQ Establlishment: '||to_char(p_ptp_id);
532 l_log_msg := l_log_msg||'
533 '||l_procedure_name||'(-)';
534 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
535 END IF;
536 -- Logging Infra: Statement level
537 ---------------------------------------------------------------------------------
538 EXCEPTION
539 WHEN INVALID_CURSOR THEN
540 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
541 --FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
542 --FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
543 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
544 l_log_msg := 'Error Message: '||SQLERRM;
545 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
546 END IF;
547
548 WHEN OTHERS THEN
549 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 --FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
551 --FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
552
553 -- Logging Infra: Statement level
554 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
555 l_log_msg := 'Error Message: '||SQLERRM;
556 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
557 END IF;
558 -- Logging Infra: Statement level
559
560 END GET_PTP_HQ;
561
562 PROCEDURE GET_TAX_SUBSCRIBER(
563 p_le_id IN NUMBER,
564 p_org_id IN NUMBER,
565 p_ptp_id OUT NOCOPY NUMBER,
566 p_return_status OUT NOCOPY VARCHAR2)
567 IS
568 l_chk_le_flg VARCHAR2(1);
569
570 CURSOR c_get_ptp IS
571 SELECT Use_Le_As_Subscriber_Flag, party_tax_profile_id
572 FROM zx_party_tax_profile
573 WHERE party_id = p_org_id
574 AND Party_Type_Code = 'OU';
575
576 CURSOR c_ptp_of_le IS
577 SELECT ptp.party_tax_profile_id
578 FROM zx_party_tax_profile ptp,
579 xle_entity_profiles xle
580 WHERE xle.legal_entity_id = p_le_id
581 AND ptp.party_id = xle.party_id
582 AND ptp.Party_Type_Code = 'FIRST_PARTY';
583
584 -- Logging Infra
585 l_procedure_name CONSTANT VARCHAR2(30) := '.GET_TAX_SUBSCRIBER';
586 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
587 l_tbl_index BINARY_INTEGER;
588
589 -- BUG 8568734
590 CURSOR get_ou_name_csr IS
591 SELECT name
592 FROM hr_all_organization_units
593 WHERE organization_id = p_org_id;
594
595 CURSOR get_le_name_csr IS
596 SELECT name
597 FROM xle_entity_profiles xle
598 WHERE xle.legal_entity_id = p_le_id;
599
600 l_party_name VARCHAR2(240);
601
602 BEGIN
603 ---------------------------------------------------------------------------------
604 -- Logging Infra: Setting up runtime level
605 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
606
607 -- Logging Infra: Statement level
608 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
609 l_log_msg := l_procedure_name||'(+)';
610 l_log_msg := l_log_msg|| '
611 Parameters:
612 p_le_id: '||to_char(p_le_id)||' '
613 ||'p_org_id: '||to_char(p_org_id);
614 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
615 END IF;
616 -- Logging Infra: Statement level
617 ---------------------------------------------------------------------------------
618
619 --
620 -- Always LE_ID parameter can not be NULL
621 --
622 IF p_le_id IS NULL THEN
623 p_ptp_id := NULL;
624
625 -- bug 8568734
626 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627 -- Logging Infra: Statement level
628 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
629 l_log_msg := 'Legal Entity Id is NULL';
630 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
631 END IF;
632 -- FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
633
634 --
635 -- Case: Get parameter LE_ID (Return PTP ID of LE)
636 --
637 ELSIF p_le_id IS NOT NULL AND p_org_id IS NULL THEN
638
639 Open c_ptp_of_le;
640 Fetch c_ptp_of_le into p_ptp_id;
641
642 IF c_ptp_of_le%NOTFOUND THEN
643 p_ptp_id := NULL;
644 p_return_status := FND_API.G_RET_STS_ERROR;
645 FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
646
647 OPEN get_le_name_csr;
648 FETCH get_le_name_csr INTO l_party_name;
649 CLOSE get_le_name_csr;
650
651 FND_MESSAGE.SET_TOKEN('PARTY_TYPE', 'LEGAL_ENTITY');
652 FND_MESSAGE.SET_TOKEN('PARTY_NAME', l_party_name);
653 close c_ptp_of_le;
654 ELSE
655 p_return_status := FND_API.G_RET_STS_SUCCESS;
656 END IF;
657
658 --
659 -- Case: Both parameter P_LE_ID and P_ORG_ID are not NULL
660 --
661 ELSIF p_le_id IS NOT NULL AND p_org_id IS NOT NULL THEN
662
663 --
664 -- Check the existence of 'Use LE as Subscriber Flag'
665 --
666
667 --Open c_get_ptp;
668 --fetch c_get_ptp into l_chk_le_flg, p_ptp_id;
669
670 GET_PARTY_TAX_PROF_INFO(
671 P_PARTY_ID => p_org_id,
672 P_PARTY_TYPE_CODE => 'OU',
673 X_TBL_INDEX => l_tbl_index,
674 X_RETURN_STATUS => p_return_status);
675
676 IF l_tbl_index is NOT NULL then
677 p_ptp_id := ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl(l_tbl_index).party_tax_profile_id;
678
679 IF NOT ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL.exists(p_ptp_id) THEN
680
681 GET_PARTY_TAX_PROF_INFO(
682 P_PARTY_TAX_PROFILE_ID => p_ptp_id,
683 X_TBL_INDEX => l_tbl_index,
684 X_RETURN_STATUS => p_return_status);
685 END IF;
686 l_chk_le_flg := ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(p_ptp_id).use_le_as_subscriber_flag;
687
688 END IF;
689
690 IF l_tbl_index is NOT NULL THEN
691 --
692 -- When 'Use LE as Susriber Flag' is 'N' OR NULL
693 --
694
695 IF l_chk_le_flg = 'N' OR l_chk_le_flg IS NULL THEN
696
697 IF p_ptp_id IS NOT NULL THEN
698 p_return_status := FND_API.G_RET_STS_SUCCESS;
699 --close c_get_ptp;
700 ELSE
701 p_ptp_id := NULL;
702 p_return_status := FND_API.G_RET_STS_ERROR;
703 FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
704 -- bug 8568734
705 OPEN get_ou_name_csr;
706 FETCH get_ou_name_csr INTO l_party_name;
707 CLOSE get_ou_name_csr;
708 FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
709 FND_MESSAGE.SET_TOKEN('PARTY_TYPE', 'OU');
710 FND_MESSAGE.SET_TOKEN('PARTY_NAME', l_party_name);
711 --close c_get_ptp;
712 END IF;
713
714 --
715 -- Return PTP_ID of LE when 'Use LE as Susriber Flag' is 'Y'
716 --
717 ELSIF l_chk_le_flg = 'Y' THEN
718
719 Open c_ptp_of_le;
720 Fetch c_ptp_of_le into p_ptp_id;
721
722 IF c_ptp_of_le%NOTFOUND THEN
723 p_ptp_id := NULL;
724 p_return_status := FND_API.G_RET_STS_ERROR;
725 FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
726 FND_MESSAGE.SET_TOKEN('PARTY_TYPE', 'LEGAL_ENTITY');
727
728 OPEN get_le_name_csr;
729 FETCH get_le_name_csr INTO l_party_name;
730 CLOSE get_le_name_csr;
731
732 FND_MESSAGE.SET_TOKEN('PARTY_NAME', l_party_name);
733 close c_ptp_of_le;
734 ELSE
735 p_return_status := FND_API.G_RET_STS_SUCCESS;
736 close c_ptp_of_le;
737 END IF;
738
739 ---------------------------------------------------------------------------------
740 -- Logging Infra: Statement level
741 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
742 l_log_msg := 'Getting PTP from LE_ID: '||to_char(p_ptp_id)||' ';
743 l_log_msg := l_log_msg||' l_chk_le_flg = Y';
744 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
745 END IF;
746 -- Logging Infra: Statement level
747 ---------------------------------------------------------------------------------
748
749 ELSE
750 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
751 -- FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
752 ---------------------------------------------------------------------------------
753 -- Logging Infra: Statement level
754 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
755 l_log_msg := 'Invalid l_chk_le_flg';
756 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
757 END IF;
758 -- Logging Infra: Statement level
759 ---------------------------------------------------------------------------------
760 END IF;
761
762 ELSE
763 Open c_ptp_of_le;
764 Fetch c_ptp_of_le into p_ptp_id;
765
766 IF c_ptp_of_le%NOTFOUND THEN
767 p_ptp_id := NULL;
768 p_return_status := FND_API.G_RET_STS_ERROR;
769 FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
770 -- bug 8568734
771 OPEN get_le_name_csr;
772 FETCH get_le_name_csr INTO l_party_name;
773 CLOSE get_le_name_csr;
774 FND_MESSAGE.SET_TOKEN('PARTY_TYPE', 'LEGAL_ENTITY');
775 FND_MESSAGE.SET_TOKEN('PARTY_NAME', l_party_name);
776 close c_ptp_of_le;
777 ELSE
778 p_return_status := FND_API.G_RET_STS_SUCCESS;
779 close c_ptp_of_le;
780 END IF;
781
782 ---------------------------------------------------------------------------------
783 -- Logging Infra: Statement level
784 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
785 l_log_msg := 'Getting PTP from LE_ID: '||to_char(p_ptp_id)||' ';
786 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
787 END IF;
788 -- Logging Infra: Statement level
789 ---------------------------------------------------------------------------------
790
791 END IF;
792 ELSE
793 p_ptp_id := NULL;
794 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 ---------------------------------------------------------------------------------
796 -- Logging Infra: Statement level
797 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
798 l_log_msg := 'Invalid p_le_id, p_org_id';
799 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
800 END IF;
801 -- Logging Infra: Statement level
802 ---------------------------------------------------------------------------------
803 --FND_MESSAGE.SET_NAME('ZX', 'ZX_PARTY_NOT_EXISTS');
804
805 END IF;
806 ---------------------------------------------------------------------------------
807 -- Logging Infra: Procedure level
808 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
809 l_log_msg := l_procedure_name||'(-)';
810 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
811 END IF;
812 ---------------------------------------------------------------------------------
813
814 EXCEPTION
815 WHEN INVALID_CURSOR THEN
816 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
817 --FND_MESSAGE.SET_NAME ('ZX','ZX_GENERIC_MESSAGE');
818 --FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', SQLERRM);
819 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
820 l_log_msg := 'Error Message: '||SQLERRM;
821 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
822 END IF;
823 WHEN OTHERS THEN
824 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
825 --FND_MESSAGE.SET_NAME ('ZX','ZX_GENERIC_MESSAGE');
826 --FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', SQLERRM);
827
828 -- Logging Infra: Statement level
829 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
830 l_log_msg := 'Error Message: '||SQLERRM;
831 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
832 END IF;
833 -- Logging Infra: Statement level
834
835 END GET_TAX_SUBSCRIBER;
836
837 /* ======================================================================
838 Procedure: GET_LOCATION_ID
839 Objective: Retrieve the location_id associated to a given
840 organization.
841 Assumption:Not all Organizations have a location attached.
842 In Parameters: p_org_id - organization ID
843 OUTPUT Parameters: p_location_id
844 p_return_status - Success is p_ptp_id is not null
845 ====================================================================== */
846 PROCEDURE GET_LOCATION_ID(
847 p_org_id IN NUMBER,
848 p_location_id OUT NOCOPY NUMBER,
849 p_return_status OUT NOCOPY VARCHAR2)
850 IS
851 CURSOR c_get_location_id IS
852 SELECT location_id, name
853 FROM hr_all_organization_units
854 WHERE organization_id = p_org_id;
855
856 -- Logging Infra
857 l_procedure_name CONSTANT VARCHAR2(30) := '.GET_LOCATION_ID';
858 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
859
860 l_party_name VARCHAR2(240);
861
862 BEGIN
863 --
864 -- Get the location_id of internal_organization_id
865 --
866 --------------------------------------------------------------------------------
867 -- Logging Infra: Setting up runtime level
868 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
869
870 -- Logging Infra: Statement level
871 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
872 l_log_msg := l_procedure_name||'(+)';
873 l_log_msg := l_log_msg||
874 'Parameters: ';
875 l_log_msg := l_log_msg||'p_org_id: '||to_char(p_org_id);
876 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
877 END IF;
878 -- Logging Infra: Statement level
879 --------------------------------------------------------------------------------
880
881 open c_get_location_id;
882 fetch c_get_location_id into p_location_id, l_party_name;
883
884 IF c_get_location_id%NOTFOUND THEN
885 --------------------------------------------------------------------------------
886 -- Logging Infra: Statement level
887 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
888 l_log_msg := 'No data found - The organization has not been defined ';
889 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
890 END IF;
891 -- Logging Infra: Statement level
892 --------------------------------------------------------------------------------
893 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
894 --FND_MESSAGE.SET_NAME('ZX', 'ZX_LOCATION_NOT_EXIST');
895 close c_get_location_id;
896 ELSIF p_location_id IS NULL THEN
897 --------------------------------------------------------------------------------
898 -- Logging Infra: Statement level
899 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
900 l_log_msg := 'There is not location associated to the Organization ID ';
901 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
902 END IF;
903 -- Logging Infra: Statement level
904 --------------------------------------------------------------------------------
905 p_return_status := FND_API.G_RET_STS_ERROR;
906 FND_MESSAGE.SET_NAME('ZX', 'ZX_LOCATION_NOT_EXIST');
907 FND_MESSAGE.SET_TOKEN('BU', l_party_name);
908 close c_get_location_id;
909 ELSE
910 p_return_status := FND_API.G_RET_STS_SUCCESS;
911 close c_get_location_id;
912
913 --------------------------------------------------------------------------------
914 -- Logging Infra: Statement level
915 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
916 l_log_msg := 'Getting Location Id for the OU: '||to_char(p_location_id);
917 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
918 END IF;
919 -- Logging Infra: Statement level
920 --------------------------------------------------------------------------------
921
922 END IF;
923 --------------------------------------------------------------------------------
924 -- Logging Infra: Procedure level
925 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
926 l_log_msg := l_procedure_name||'(-)';
927 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
928 END IF;
929 --------------------------------------------------------------------------------
930
931 EXCEPTION
932 WHEN INVALID_CURSOR THEN
933 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
934 --FND_MESSAGE.SET_NAME ('ZX','ZX_GENERIC_MESSAGE');
935 --FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', SQLERRM);
936 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
937 l_log_msg := 'Error Message: '||SQLERRM;
938 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
939 END IF;
940
941 WHEN OTHERS THEN
942 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
943 --FND_MESSAGE.SET_NAME ('ZX','ZX_GENERIC_MESSAGE');
944 --FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', SQLERRM);
945
946 -----------------------------------------------------------------------------
947 -- Logging Infra: Statement level
948 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
949 l_log_msg := 'Error Message: '||SQLERRM;
950 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
951 END IF;
952 -- Logging Infra: Statement level
953 -----------------------------------------------------------------------------
954
955 END GET_LOCATION_ID;
956
957 /* =============================================================================
958 Procedure: CHECK_TAX_REGISTRATIONS
959 Objective: Retrieve the Tax Registrations for a given Legal Registrations ID
960 Assumption: Check the existence of the tax registration which is from legal registration.
961 In Parameters: p_api_version - Standard required IN parameter.
962 p_le_reg_id - Legal Registrations ID
963 ============================================================================= */
964
965 Procedure CHECK_TAX_REGISTRATIONS(
966 p_api_version IN NUMBER,
967 p_le_reg_id IN NUMBER,
968 x_return_status OUT NOCOPY VARCHAR2)
969 IS
970
971 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_TAX_REGISTRATIONS';
972 l_api_version CONSTANT NUMBER := 1.0;
973
974 l_tax_registration_id ZX_REGISTRATIONS.REGISTRATION_ID%TYPE;
975
976 -- Logging Infra
977 l_procedure_name CONSTANT VARCHAR2(30) := '.CHECK_TAX_REGISTRATIONS';
978 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
979
980 CURSOR c_get_reg_id
981 IS
982 SELECT registration_id
983 FROM zx_registrations
984 WHERE legal_registration_id = p_le_reg_id;
985
986 BEGIN
987
988 -- Logging Infra: Setting up runtime level
989 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
990
991 -- Logging Infra: Statement level
992 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
993 l_log_msg := l_procedure_name||'(+)';
994 l_log_msg := l_log_msg||
995 'Parameters: ';
996 l_log_msg := l_log_msg||'p_le_reg_id: '||to_char(p_le_reg_id);
997 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
998 END IF;
999 -- Logging Infra: Statement level
1000
1001
1002 /*--------------------------------------------------+
1003 | Standard start of API savepoint |
1004 +--------------------------------------------------*/
1005 SAVEPOINT Check_Tax_Registrations_PVT;
1006
1007 /*--------------------------------------------------+
1008 | Standard call to check for call compatibility |
1009 +--------------------------------------------------*/
1010
1011 IF NOT FND_API.Compatible_API_Call(
1012 l_api_version,
1013 p_api_version,
1014 l_api_name,
1015 G_PKG_NAME
1016 ) THEN
1017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1018 END IF;
1019
1020 /*-----------------------------------------+
1021 | Initialize return status to SUCCESS |
1022 +-----------------------------------------*/
1023
1024 x_return_status := FND_API.G_RET_STS_SUCCESS;
1025
1026
1027 Open c_get_reg_id;
1028 Fetch c_get_reg_id into l_tax_registration_id;
1029
1030 IF c_get_reg_id%NOTFOUND THEN
1031 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1032 -- FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_MESSAGE');
1033 close c_get_reg_id;
1034
1035 -- Logging Infra: Statement level
1036 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1037 l_log_msg := 'There is no tax registrations which is from legal registrations.';
1038 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1039 END IF;
1040 -- Logging Infra: Statement level
1041
1042 ELSE
1043 x_return_status := FND_API.G_RET_STS_SUCCESS;
1044 close c_get_reg_id;
1045 END IF;
1046
1047 -- Logging Infra: Procedure level
1048 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1049 l_log_msg := l_procedure_name||'-)';
1050 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1051 END IF;
1052
1053 EXCEPTION
1054 WHEN FND_API.G_EXC_ERROR THEN
1055 ROLLBACK TO Check_Tax_Registrations_PVT;
1056 x_return_status := FND_API.G_RET_STS_ERROR ;
1057
1058 IF ( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
1059 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name,'');
1060 END IF;
1061
1062 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1063 ROLLBACK TO Check_Tax_Registrations_PVT;
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1065
1066 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1067 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,'');
1068 END IF;
1069
1070 WHEN INVALID_CURSOR THEN
1071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1072 --FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_MESSAGE');
1073 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1074 l_log_msg := 'Error Message: '||SQLERRM;
1075 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1076 END IF;
1077
1078 WHEN OTHERS THEN
1079 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1080 --FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_MESSAGE');
1081
1082 -- Logging Infra: Statement level
1083 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1084 l_log_msg := 'Error Message: '||SQLERRM;
1085 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1086 END IF;
1087 -- Logging Infra: Statement level
1088
1089 END CHECK_TAX_REGISTRATIONS;
1090
1091 /* =============================================================================
1092 Procedure: SYNC_TAX_REGISTRATIONS
1093 Objective: Syncronize Tax Registrations after update the Legal Registrations.
1094 Assumption:
1095 Case 1: Legal Registrations number and validation digit have updated.
1096 Update existing tax registrations with end date and create new row.
1097 Case 2: Legal Registrations has updated with end date and created new one.
1098 Update existing tax registrations with end date and create new row.
1099 In Parameters: p_api_version : Required standard IN parameter
1100 p_le_old_reg_id : Prior Legal Registrations ID = Legal Registrations
1101 ID in Tax Registrations.
1102 p_le_old_date : NULL (Case 1)
1103 NOT NULL (Case 2)
1104 p_le_new_erg_id : NULL (Case 1)
1105 NOT NULL (Case 2)
1106 p_le_new_reg_num: New Legal Registration Number
1107
1108 ============================================================================= */
1109
1110 Procedure SYNC_TAX_REGISTRATIONS(
1111 p_api_version IN NUMBER,
1112 p_le_old_reg_id IN NUMBER,
1113 p_le_old_end_date IN DATE,
1114 p_le_new_reg_id IN NUMBER,
1115 p_le_new_reg_num IN VARCHAR2,
1116 x_return_status OUT NOCOPY VARCHAR2)
1117 IS
1118
1119 l_api_name CONSTANT VARCHAR2(30) := 'SYNC_TAX_REGISTRATIONS';
1120 l_api_version CONSTANT NUMBER := 1.0;
1121
1122 l_tax_registrations_rec ZX_REGISTRATIONS%ROWTYPE;
1123
1124 -- Find a tax registration which is from Legal Registrations
1125
1126 CURSOR c_find_reg_num
1127 IS
1128 SELECT
1129 registration_id,
1130 registration_type_code,
1131 registration_number,
1132 validation_rule,
1133 tax_authority_id,
1134 rep_tax_authority_id,
1135 coll_Tax_authority_id,
1136 rounding_rule_code,
1137 tax_jurisdiction_code,
1138 self_assess_flag,
1139 registration_status_code,
1140 registration_source_code,
1141 registration_reason_code,
1142 party_tax_profile_id,
1143 tax,
1144 tax_regime_code,
1145 inclusive_tax_flag,
1146 has_tax_exemptions_flag,
1147 effective_from,
1148 effective_to,
1149 rep_party_tax_name,
1150 legal_registration_id,
1151 default_registration_flag,
1152 bank_id,
1153 bank_branch_id,
1154 bank_account_num,
1155 legal_location_id,
1156 record_type_code,
1157 request_id,
1158 program_application_id,
1159 program_id,
1160 program_login_id,
1161 account_id,
1162 account_site_id,
1163 --site_use_id,
1164 --geo_type_classification_code,
1165 attribute1,
1166 attribute2,
1167 attribute3,
1168 attribute4,
1169 attribute5,
1170 attribute6,
1171 attribute7,
1172 attribute8,
1173 attribute9 ,
1174 attribute10,
1175 attribute11,
1176 attribute12,
1177 attribute13,
1178 attribute14,
1179 attribute15,
1180 attribute_category
1181 FROM zx_registrations
1182 WHERE legal_registration_id = p_le_old_reg_id;
1183
1184 -- Logging Infra
1185 l_procedure_name CONSTANT VARCHAR2(30) := 'SYNC_TAX_REGISTRATIONS';
1186 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1187
1188 BEGIN
1189
1190 -- Logging Infra: Setting up runtime level
1191 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1192
1193 -- Logging Infra: Statement level
1194 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1195 l_log_msg := l_procedure_name||'(+)';
1196 l_log_msg := l_log_msg||
1197 ' Parameters '||
1198 'p_le_old_reg_id: '||to_char(p_le_old_reg_id)||
1199 'p_le_new_reg_id: '||to_char(p_le_new_reg_id)||
1200 'p_le_new_reg_num: '||p_le_new_reg_num;
1201 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1202 END IF;
1203 -- Logging Infra: Statement level
1204
1205 /*--------------------------------------------------+
1206 | Standard start of API savepoint |
1207 +--------------------------------------------------*/
1208 SAVEPOINT Sync_Tax_Registrations_PVT;
1209
1210 /*--------------------------------------------------+
1211 | Standard call to check for call compatibility |
1212 +--------------------------------------------------*/
1213
1214 IF NOT FND_API.Compatible_API_Call(
1215 l_api_version,
1216 p_api_version,
1217 l_api_name,
1218 G_PKG_NAME
1219 ) THEN
1220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1221 END IF;
1222
1223
1224 /*-----------------------------------------+
1225 | Initialize return status to SUCCESS |
1226 +-----------------------------------------*/
1227
1228 x_return_status := FND_API.G_RET_STS_SUCCESS;
1229
1230
1231 OPEN c_find_reg_num;
1232 LOOP
1233
1234 FETCH c_find_reg_num INTO
1235 l_tax_registrations_rec.registration_id,
1236 l_tax_registrations_rec.registration_type_code,
1237 l_tax_registrations_rec.registration_number,
1238 l_tax_registrations_rec.validation_rule,
1239 l_tax_registrations_rec.tax_authority_id,
1240 l_tax_registrations_rec.rep_tax_authority_id,
1241 l_tax_registrations_rec.coll_Tax_authority_id,
1242 l_tax_registrations_rec.rounding_rule_code,
1243 l_tax_registrations_rec.tax_jurisdiction_code,
1244 l_tax_registrations_rec.self_assess_flag,
1245 l_tax_registrations_rec.registration_status_code,
1246 l_tax_registrations_rec.registration_source_code,
1247 l_tax_registrations_rec.registration_reason_code,
1248 l_tax_registrations_rec.party_tax_profile_id,
1249 l_tax_registrations_rec.tax,
1250 l_tax_registrations_rec.tax_regime_code,
1251 l_tax_registrations_rec.inclusive_tax_flag,
1252 l_tax_registrations_rec.has_tax_exemptions_flag,
1253 l_tax_registrations_rec.effective_from,
1254 l_tax_registrations_rec.effective_to,
1255 l_tax_registrations_rec.rep_party_tax_name,
1256 l_tax_registrations_rec.legal_registration_id,
1257 l_tax_registrations_rec.default_registration_flag,
1258 l_tax_registrations_rec.bank_id,
1259 l_tax_registrations_rec.bank_branch_id,
1260 l_tax_registrations_rec.bank_account_num,
1261 l_tax_registrations_rec.legal_location_id,
1262 l_tax_registrations_rec.record_type_code,
1263 l_tax_registrations_rec.request_id,
1264 l_tax_registrations_rec.program_application_id,
1265 l_tax_registrations_rec.program_id,
1266 l_tax_registrations_rec.program_login_id,
1267 l_tax_registrations_rec.account_id,
1268 l_tax_registrations_rec.account_site_id,
1269 --l_tax_registrations_rec.site_use_id,
1270 --l_tax_registrations_rec.geo_type_classification_code,
1271 l_tax_registrations_rec.attribute1,
1272 l_tax_registrations_rec.attribute2,
1273 l_tax_registrations_rec.attribute3,
1274 l_tax_registrations_rec.attribute4,
1275 l_tax_registrations_rec.attribute5,
1276 l_tax_registrations_rec.attribute6,
1277 l_tax_registrations_rec.attribute7,
1278 l_tax_registrations_rec.attribute8,
1279 l_tax_registrations_rec.attribute9 ,
1280 l_tax_registrations_rec.attribute10,
1281 l_tax_registrations_rec.attribute11,
1282 l_tax_registrations_rec.attribute12,
1283 l_tax_registrations_rec.attribute13,
1284 l_tax_registrations_rec.attribute14,
1285 l_tax_registrations_rec.attribute15,
1286 l_tax_registrations_rec.attribute_category;
1287
1288 EXIT WHEN c_find_reg_num%NOTFOUND;
1289
1290 IF c_find_reg_num%ROWCOUNT = 0 Then
1291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1292 -- FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_MESSAGE');
1293 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1294 l_log_msg := 'c_find_reg_num%ROWCOUNT = 0';
1295 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1296 END IF;
1297
1298 EXIT;
1299 CLOSE c_find_reg_num;
1300
1301 ELSE
1302 x_return_status := FND_API.G_RET_STS_SUCCESS;
1303
1304 IF p_le_new_reg_id IS NULL and p_le_old_end_date IS NULL THEN
1305 update ZX_REGISTRATIONS set
1306 effective_to = SYSDATE
1307 where registration_id = l_tax_registrations_rec.registration_id;
1308
1309 insert into ZX_REGISTRATIONS (
1310 registration_id,
1311 registration_type_code,
1312 registration_number,
1313 validation_rule,
1314 tax_authority_id,
1315 rep_tax_authority_id,
1316 coll_Tax_authority_id,
1317 rounding_rule_code,
1318 tax_jurisdiction_code,
1319 self_assess_flag,
1320 registration_status_code,
1321 registration_source_code,
1322 registration_reason_code,
1323 party_tax_profile_id,
1324 tax,
1325 tax_regime_code,
1326 inclusive_tax_flag,
1327 has_tax_exemptions_flag,
1328 effective_from,
1329 effective_to,
1330 rep_party_tax_name,
1331 legal_registration_id,
1332 default_registration_flag,
1333 bank_id,
1334 bank_branch_id,
1335 bank_account_num,
1336 legal_location_id,
1337 record_type_code,
1338 request_id,
1339 program_application_id,
1340 program_id,
1341 program_login_id,
1342 account_id,
1343 account_site_id,
1344 --site_use_id,
1345 --geo_type_classification_code,
1346 attribute1,
1347 attribute2,
1348 attribute3,
1349 attribute4,
1350 attribute5,
1351 attribute6,
1352 attribute7,
1353 attribute8,
1354 attribute9 ,
1355 attribute10,
1356 attribute11,
1357 attribute12,
1358 attribute13,
1359 attribute14,
1360 attribute15,
1361 attribute_category,
1362 creation_date,
1363 created_by,
1364 last_update_date,
1365 last_updated_by,
1366 last_update_login
1367 ) values (
1368 Zx_Registrations_S.nextval,
1369 l_tax_registrations_rec.registration_type_code,
1370 p_le_new_reg_num,
1371 l_tax_registrations_rec.validation_rule,
1372 l_tax_registrations_rec.tax_authority_id,
1373 l_tax_registrations_rec.rep_tax_authority_id,
1374 l_tax_registrations_rec.coll_Tax_authority_id,
1375 l_tax_registrations_rec.rounding_rule_code,
1376 l_tax_registrations_rec.tax_jurisdiction_code,
1377 l_tax_registrations_rec.self_assess_flag,
1378 l_tax_registrations_rec.registration_status_code,
1379 l_tax_registrations_rec.registration_source_code,
1380 l_tax_registrations_rec.registration_reason_code,
1381 l_tax_registrations_rec.party_tax_profile_id,
1382 l_tax_registrations_rec.tax,
1383 l_tax_registrations_rec.tax_regime_code,
1384 l_tax_registrations_rec.inclusive_tax_flag,
1385 l_tax_registrations_rec.has_tax_exemptions_flag,
1386 SYSDATE + 1,
1387 NULL,
1388 l_tax_registrations_rec.rep_party_tax_name,
1389 l_tax_registrations_rec.legal_registration_id,
1390 l_tax_registrations_rec.default_registration_flag,
1391 l_tax_registrations_rec.bank_id,
1392 l_tax_registrations_rec.bank_branch_id,
1393 l_tax_registrations_rec.bank_account_num,
1394 l_tax_registrations_rec.legal_location_id,
1395 l_tax_registrations_rec.record_type_code,
1396 fnd_global.conc_request_id,
1397 fnd_global.prog_appl_id,
1398 fnd_global.conc_program_id,
1399 fnd_global.conc_login_id,
1400 l_tax_registrations_rec.account_id,
1401 l_tax_registrations_rec.account_site_id,
1402 --l_tax_registrations_rec.site_use_id,
1403 --l_tax_registrations_rec.geo_type_classification_code,
1404 l_tax_registrations_rec.attribute1,
1405 l_tax_registrations_rec.attribute2,
1406 l_tax_registrations_rec.attribute3,
1407 l_tax_registrations_rec.attribute4,
1408 l_tax_registrations_rec.attribute5,
1409 l_tax_registrations_rec.attribute6,
1410 l_tax_registrations_rec.attribute7,
1411 l_tax_registrations_rec.attribute8,
1412 l_tax_registrations_rec.attribute9 ,
1413 l_tax_registrations_rec.attribute10,
1414 l_tax_registrations_rec.attribute11,
1415 l_tax_registrations_rec.attribute12,
1416 l_tax_registrations_rec.attribute13,
1417 l_tax_registrations_rec.attribute14,
1418 l_tax_registrations_rec.attribute15,
1419 l_tax_registrations_rec.attribute_category,
1420 SYSDATE + 1,
1421 fnd_global.user_id,
1422 SYSDATE + 1,
1423 fnd_global.user_id,
1424 fnd_global.conc_login_id);
1425
1426 ELSE
1427
1428 update ZX_REGISTRATIONS set
1429 effective_to = p_le_old_end_date
1430 where registration_id = l_tax_registrations_rec.registration_id;
1431
1432 insert into ZX_REGISTRATIONS (
1433 registration_id,
1434 registration_type_code,
1435 registration_number,
1436 validation_rule,
1437 tax_authority_id,
1438 rep_tax_authority_id,
1439 coll_Tax_authority_id,
1440 rounding_rule_code,
1441 tax_jurisdiction_code,
1442 self_assess_flag,
1443 registration_status_code,
1444 registration_source_code,
1445 registration_reason_code,
1446 party_tax_profile_id,
1447 tax,
1448 tax_regime_code,
1449 inclusive_tax_flag,
1450 has_tax_exemptions_flag,
1451 effective_from,
1452 effective_to,
1453 rep_party_tax_name,
1454 legal_registration_id,
1455 default_registration_flag,
1456 bank_id,
1457 bank_branch_id,
1458 bank_account_num,
1459 legal_location_id,
1460 record_type_code,
1461 request_id,
1462 program_application_id,
1463 program_id,
1464 program_login_id,
1465 account_id,
1466 account_site_id,
1467 --site_use_id,
1468 --geo_type_classification_code,
1469 attribute1,
1470 attribute2,
1471 attribute3,
1472 attribute4,
1473 attribute5,
1474 attribute6,
1475 attribute7,
1476 attribute8,
1477 attribute9 ,
1478 attribute10,
1479 attribute11,
1480 attribute12,
1481 attribute13,
1482 attribute14,
1483 attribute15,
1484 attribute_category,
1485 creation_date,
1486 created_by,
1487 last_update_date,
1488 last_updated_by,
1489 last_update_login
1490 ) values (
1491 Zx_Registrations_S.nextval,
1492 l_tax_registrations_rec.registration_type_code,
1493 p_le_new_reg_num,
1494 l_tax_registrations_rec.validation_rule,
1495 l_tax_registrations_rec.tax_authority_id,
1496 l_tax_registrations_rec.rep_tax_authority_id,
1497 l_tax_registrations_rec.coll_Tax_authority_id,
1498 l_tax_registrations_rec.rounding_rule_code,
1499 l_tax_registrations_rec.tax_jurisdiction_code,
1500 l_tax_registrations_rec.self_assess_flag,
1501 l_tax_registrations_rec.registration_status_code,
1502 l_tax_registrations_rec.registration_source_code,
1503 l_tax_registrations_rec.registration_reason_code,
1504 l_tax_registrations_rec.party_tax_profile_id,
1505 l_tax_registrations_rec.tax,
1506 l_tax_registrations_rec.tax_regime_code,
1507 l_tax_registrations_rec.inclusive_tax_flag,
1508 l_tax_registrations_rec.has_tax_exemptions_flag,
1509 p_le_old_end_date + 1,
1510 NULL,
1511 l_tax_registrations_rec.rep_party_tax_name,
1512 p_le_new_reg_id,
1513 l_tax_registrations_rec.default_registration_flag,
1514 l_tax_registrations_rec.bank_id,
1515 l_tax_registrations_rec.bank_branch_id,
1516 l_tax_registrations_rec.bank_account_num,
1517 l_tax_registrations_rec.legal_location_id,
1518 l_tax_registrations_rec.record_type_code,
1519 fnd_global.conc_request_id,
1520 fnd_global.prog_appl_id,
1521 fnd_global.conc_program_id,
1522 fnd_global.conc_login_id,
1523 l_tax_registrations_rec.account_id,
1524 l_tax_registrations_rec.account_site_id,
1525 --l_tax_registrations_rec.site_use_id,
1526 --l_tax_registrations_rec.geo_type_classification_code,
1527 l_tax_registrations_rec.attribute1,
1528 l_tax_registrations_rec.attribute2,
1529 l_tax_registrations_rec.attribute3,
1530 l_tax_registrations_rec.attribute4,
1531 l_tax_registrations_rec.attribute5,
1532 l_tax_registrations_rec.attribute6,
1533 l_tax_registrations_rec.attribute7,
1534 l_tax_registrations_rec.attribute8,
1535 l_tax_registrations_rec.attribute9 ,
1536 l_tax_registrations_rec.attribute10,
1537 l_tax_registrations_rec.attribute11,
1538 l_tax_registrations_rec.attribute12,
1539 l_tax_registrations_rec.attribute13,
1540 l_tax_registrations_rec.attribute14,
1541 l_tax_registrations_rec.attribute15,
1542 l_tax_registrations_rec.attribute_category,
1543 p_le_old_end_date + 1,
1544 fnd_global.user_id,
1545 p_le_old_end_date + 1,
1546 fnd_global.user_id,
1547 fnd_global.conc_login_id);
1548 END IF;
1549
1550 END IF;
1551
1552 END LOOP;
1553
1554 CLOSE c_find_reg_num;
1555
1556 -- Logging Infra: Procedure level
1557 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1558 l_log_msg := l_procedure_name||'(-)';
1559 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1560 END IF;
1561
1562 EXCEPTION
1563
1564 WHEN FND_API.G_EXC_ERROR THEN
1565 ROLLBACK TO Sync_Tax_Registrations_PVT;
1566 x_return_status := FND_API.G_RET_STS_ERROR ;
1567
1568 IF ( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
1569 FND_LOG.STRING(G_LEVEL_ERROR,G_MODULE_NAME||l_api_name,'');
1570 END IF;
1571
1572 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1573 ROLLBACK TO Sync_Tax_Registrations_PVT;
1574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1575
1576 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1577 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_api_name,'');
1578 END IF;
1579
1580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1581 --FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_MESSAGE');
1582 -- Logging Infra: Statement level
1583 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1584 l_log_msg := 'Error Message: '||SQLERRM;
1585 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name, l_log_msg);
1586 END IF;
1587 WHEN OTHERS THEN
1588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1589 --FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_MESSAGE');
1590 -- Logging Infra: Statement level
1591 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1592 l_log_msg := 'Error Message: '||SQLERRM;
1593 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name, l_log_msg);
1594 END IF;
1595
1596 END SYNC_TAX_REGISTRATIONS;
1597
1598 Procedure GET_PARTY_TAX_PROF_INFO(
1599 P_PARTY_ID IN NUMBER,
1600 P_PARTY_TYPE_CODE IN ZX_PARTY_TAX_PROFILE.PARTY_TYPE_CODE%TYPE,
1601 X_TBL_INDEX OUT NOCOPY BINARY_INTEGER,
1602 X_RETURN_STATUS OUT NOCOPY VARCHAR2)
1603 IS
1604
1605 CURSOR c_party_tax_prof_info
1606 IS
1607 SELECT
1608 ptp.party_tax_profile_id,
1609 ptp.party_id,
1610 ptp.party_type_code,
1611 ptp.supplier_flag,
1612 ptp.customer_flag,
1613 ptp.site_flag,
1614 ptp.process_for_applicability_flag,
1615 ptp.rounding_level_code,
1616 ptp.withholding_start_date,
1617 ptp.allow_awt_flag,
1618 ptp.use_le_as_subscriber_flag,
1619 ptp.legal_establishment_flag,
1620 ptp.first_party_le_flag,
1621 ptp.reporting_authority_flag,
1622 ptp.collecting_authority_flag,
1623 ptp.provider_type_code,
1624 ptp.create_awt_dists_type_code,
1625 ptp.create_awt_invoices_type_code,
1626 ptp.allow_offset_tax_flag,
1627 ptp.effective_from_use_le,
1628 ptp.rep_registration_number,
1629 ptp.rounding_rule_code
1630 FROM zx_party_tax_profile ptp
1631 WHERE ptp.party_id = p_party_id
1632 AND ptp.party_type_code = p_party_type_code;
1633
1634 l_tbl_index binary_integer;
1635 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1636
1637 BEGIN
1638
1639 x_return_status := FND_API.G_RET_STS_SUCCESS;
1640 l_tbl_index := dbms_utility.get_hash_value(p_party_type_code||'$'||to_char(p_party_id), 1, 8192);
1641
1642 IF (ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl.exists(l_tbl_index)
1643 AND ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl(l_tbl_index).party_type_code = p_party_type_code
1644 AND ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl(l_tbl_index).party_id = p_party_id)
1645
1646 THEN
1647
1648 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1649 l_log_msg := 'Party Tax Profile Id info for party_id: '||to_char(p_party_id)||' and party_type_code '
1650 ||p_party_type_code||' found in cache at index: '||to_char(l_tbl_index);
1651 FND_LOG.STRING(G_LEVEL_STATEMENT,'ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO', l_log_msg);
1652 END IF;
1653
1654 X_TBL_INDEX := l_tbl_index;
1655 RETURN;
1656 ELSE
1657
1658 For ptp IN c_party_tax_prof_info Loop
1659
1660 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).party_tax_profile_id := ptp.party_tax_profile_id;
1661 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).party_id := ptp.party_id;
1662 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).party_type_code := ptp.party_type_code;
1663 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).supplier_flag := ptp.supplier_flag;
1664 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).customer_flag := ptp.customer_flag;
1665 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).site_flag := ptp.site_flag;
1666 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).process_for_applicability_flag := ptp.process_for_applicability_flag;
1667 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).rounding_level_code := ptp.rounding_level_code;
1668 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).withholding_start_date := ptp.withholding_start_date;
1669 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).allow_awt_flag := ptp.allow_awt_flag;
1670 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).use_le_as_subscriber_flag := ptp.use_le_as_subscriber_flag;
1671 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).legal_establishment_flag := ptp.legal_establishment_flag;
1672 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).first_party_le_flag := ptp.first_party_le_flag;
1673 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).reporting_authority_flag := ptp.reporting_authority_flag;
1674 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).collecting_authority_flag := ptp.collecting_authority_flag;
1675 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).provider_type_code := ptp.provider_type_code;
1676 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).create_awt_dists_type_code := ptp.create_awt_dists_type_code;
1677 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).create_awt_invoices_type_code := ptp.create_awt_invoices_type_code;
1678 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).allow_offset_tax_flag := ptp.allow_offset_tax_flag;
1679 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).effective_from_use_le := ptp.effective_from_use_le;
1680 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).rep_registration_number := ptp.rep_registration_number;
1681 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).rounding_rule_code := ptp.rounding_rule_code;
1682
1683
1684 ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl(l_tbl_index).party_id := ptp.party_id;
1685 ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl(l_tbl_index).party_type_code := ptp.party_type_code;
1686 ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl(l_tbl_index).party_tax_profile_id := ptp.party_tax_profile_id;
1687
1688 X_TBL_INDEX := l_tbl_index;
1689 exit;
1690 END LOOP;
1691 END IF;
1692
1693
1694 EXCEPTION
1695 WHEN no_data_found then
1696 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1697 l_log_msg := 'No Data found in ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO. Still returning with success';
1698 FND_LOG.STRING(G_LEVEL_PROCEDURE,'ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO', l_log_msg);
1699 END IF;
1700 X_TBL_INDEX := NULL;
1701 WHEN others then
1702 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1703 l_log_msg := 'Unexpected error in ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO: '||SQLCODE||' ; '||SQLERRM;
1704 FND_LOG.STRING(G_LEVEL_UNEXPECTED,'ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO', l_log_msg);
1705 END IF;
1706
1707 END GET_PARTY_TAX_PROF_INFO;
1708
1709
1710 Procedure GET_PARTY_TAX_PROF_INFO(
1711 P_PARTY_TAX_PROFILE_ID IN NUMBER,
1712 X_TBL_INDEX OUT NOCOPY BINARY_INTEGER,
1713 X_RETURN_STATUS OUT NOCOPY VARCHAR2)
1714 IS
1715
1716 CURSOR c_party_tax_prof_info
1717 IS
1718 SELECT
1719 ptp.party_tax_profile_id,
1720 ptp.party_id,
1721 ptp.party_type_code,
1722 ptp.supplier_flag,
1723 ptp.customer_flag,
1724 ptp.site_flag,
1725 ptp.process_for_applicability_flag,
1726 ptp.rounding_level_code,
1727 ptp.withholding_start_date,
1728 ptp.allow_awt_flag,
1729 ptp.use_le_as_subscriber_flag,
1730 ptp.legal_establishment_flag,
1731 ptp.first_party_le_flag,
1732 ptp.reporting_authority_flag,
1733 ptp.collecting_authority_flag,
1734 ptp.provider_type_code,
1735 ptp.create_awt_dists_type_code,
1736 ptp.create_awt_invoices_type_code,
1737 ptp.allow_offset_tax_flag,
1738 ptp.effective_from_use_le,
1739 ptp.rep_registration_number,
1740 ptp.rounding_rule_code
1741 FROM zx_party_tax_profile ptp
1742 WHERE party_tax_profile_id = p_party_tax_profile_id;
1743
1744 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1745 l_tbl_index binary_integer;
1746
1747 BEGIN
1748
1749 x_return_status := FND_API.G_RET_STS_SUCCESS;
1750
1751 IF ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL.exists(p_party_tax_profile_id)
1752 THEN
1753
1754 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1755 l_log_msg := 'Party Tax Profile Id info found in cache ';
1756 FND_LOG.STRING(G_LEVEL_STATEMENT,'ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO', l_log_msg);
1757 END IF;
1758
1759 X_TBL_INDEX := p_party_tax_profile_id;
1760 RETURN;
1761 ELSE
1762
1763 For ptp IN c_party_tax_prof_info Loop
1764
1765 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).party_tax_profile_id := ptp.party_tax_profile_id;
1766 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).party_id := ptp.party_id;
1767 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).party_type_code := ptp.party_type_code;
1768 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).supplier_flag := ptp.supplier_flag;
1769 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).customer_flag := ptp.customer_flag;
1770 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).site_flag := ptp.site_flag;
1771 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).process_for_applicability_flag := ptp.process_for_applicability_flag;
1772 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).rounding_level_code := ptp.rounding_level_code;
1773 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).withholding_start_date := ptp.withholding_start_date;
1774 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).allow_awt_flag := ptp.allow_awt_flag;
1775 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).use_le_as_subscriber_flag := ptp.use_le_as_subscriber_flag;
1776 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).legal_establishment_flag := ptp.legal_establishment_flag;
1777 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).first_party_le_flag := ptp.first_party_le_flag;
1778 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).reporting_authority_flag := ptp.reporting_authority_flag;
1779 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).collecting_authority_flag := ptp.collecting_authority_flag;
1780 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).provider_type_code := ptp.provider_type_code;
1781 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).create_awt_dists_type_code := ptp.create_awt_dists_type_code;
1782 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).create_awt_invoices_type_code := ptp.create_awt_invoices_type_code;
1783 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).allow_offset_tax_flag := ptp.allow_offset_tax_flag;
1784 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).effective_from_use_le := ptp.effective_from_use_le;
1785 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).rep_registration_number := ptp.rep_registration_number;
1786 ZX_GLOBAL_STRUCTURES_PKG.G_PARTY_TAX_PROF_INFO_TBL(ptp.party_tax_profile_id).rounding_rule_code := ptp.rounding_rule_code;
1787
1788 l_tbl_index := dbms_utility.get_hash_value(ptp.party_type_code||'$'||to_char(ptp.party_id), 1, 8192);
1789
1790 ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl(l_tbl_index).party_id := ptp.party_id;
1791 ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl(l_tbl_index).party_type_code := ptp.party_type_code;
1792 ZX_GLOBAL_STRUCTURES_PKG.g_party_tax_prof_id_info_tbl(l_tbl_index).party_tax_profile_id := ptp.party_tax_profile_id;
1793
1794 X_TBL_INDEX := ptp.party_tax_profile_id;
1795 exit;
1796 END LOOP;
1797 END IF;
1798
1799
1800 EXCEPTION
1801
1802 WHEN no_data_found then
1803 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1804 l_log_msg := 'No Data found in ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO. Still returning with success';
1805 FND_LOG.STRING(G_LEVEL_PROCEDURE,'ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO', l_log_msg);
1806 END IF;
1807 X_TBL_INDEX := NULL;
1808
1809 WHEN others then
1810 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
1811 l_log_msg := 'Unexpected error in ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO: '||SQLCODE||' ; '||SQLERRM;
1812 FND_LOG.STRING(G_LEVEL_UNEXPECTED,'ZX_TCM_PTP_PKG.GET_PARTY_TAX_PROF_INFO', l_log_msg);
1813 END IF;
1814
1815 END GET_PARTY_TAX_PROF_INFO;
1816
1817
1818 END ZX_TCM_PTP_PKG;