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