[Home] [Help]
PACKAGE BODY: APPS.XLE_UTILITIES_GRP
Source
1 PACKAGE BODY XLE_UTILITIES_GRP AS
2 /* $Header: xlegfptb.pls 120.62 2011/05/20 09:24:13 srsampat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):=' XLE_UTILITIES_GRP';
5
6
7 PROCEDURE Get_Registration_Info(
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2,
11 P_PARTY_ID IN XLE_ENTITY_PROFILES.PARTY_ID%TYPE,
12 P_ENTITY_ID IN XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE,
13 P_ENTITY_TYPE IN VARCHAR2,
14 P_identifying_flag IN VARCHAR2,
15 P_LEGISLATIVE_CATEGORY IN VARCHAR2,
16 X_REGISTRATION_INFO OUT NOCOPY Registration_Tbl_Type
17 ) AS
18
19
20 l_party_ID VARCHAR2(100);
21 l_entity_ID VARCHAR2(100);
22 l_identifying_flag VARCHAR2(1);
23 l_legislative_category VARCHAR2(100);
24
25 l_index NUMBER := 1;
26 rowcount_flag BOOLEAN := false;
27
28
29 /* The following cursor retrieves Legal Entity and registration
30 Information for a given Legal Entity */
31
32 CURSOR LE_Reg_c IS
33 SELECT lep.party_id,
34 lep.legal_entity_id ENTITY_ID,
35 lep.name ENTITY_NAME,
36 'LEGAL_ENTITY' ENTITY_TYPE,
37 reg.registration_number,
38 reg.registered_name,
39 reg.alternate_registered_name,
40 reg.identifying_flag identifying_flag,
41 jur.legislative_cat_code LEGISLATIVE_CATEGORY,
42 (select party_name
43 from hz_parties
44 where party_id = reg.issuing_authority_id) LEGALAUTH_NAME,
45 (select hzl.address1 || ' ' || hzl.address2 || ' '
46 || hzl.city || ',' || hzl.state
47 || ',' || hzl.country || ' ' || hzl.postal_code
48 from hz_locations hzl, hz_party_sites hps
49 where hps.location_id = hzl.location_id
50 and hps.party_site_id = reg.issuing_authority_site_id) LEGALAUTH_ADDRESS,
51 reg.effective_from,
52 reg.effective_to,
53 reg.location_id,
54 hrl.address_line_1,
55 hrl.address_line_2,
56 hrl.address_line_3,
57 hrl.town_or_city,
58 hrl.region_1,
59 hrl.region_2,
60 hrl.region_3,
61 hrl.postal_code,
62 hrl.country
63 FROM XLE_ENTITY_PROFILES lep,
64 XLE_REGISTRATIONS reg,
65 HR_LOCATIONS_ALL hrl,
66 XLE_JURISDICTIONS_VL jur
67 WHERE
68 lep.legal_entity_id = reg.source_id
69 AND reg.source_table = 'XLE_ENTITY_PROFILES'
70 AND hrl.location_id = reg.location_id
71 AND jur.jurisdiction_id = reg.jurisdiction_id
72 AND lep.party_ID like l_party_ID
73 AND lep.legal_entity_id like l_entity_ID
74 AND nvl(reg.identifying_flag,'N') like l_identifying_flag
75 AND jur.legislative_cat_code like l_legislative_category
76 ;
77
78 /* The following cursor retrieves Establishment and registration
79 Information for a given Establishment */
80
81 CURSOR ETB_Reg_c IS
82 SELECT etb.party_id,
83 etb.establishment_id ENTITY_ID,
84 etb.name ENTITY_NAME,
85 'ESTABLISHMENT' ENTITY_TYPE,
86 reg.registration_number,
87 reg.registered_name,
88 reg.alternate_registered_name,
89 reg.identifying_flag identifying_flag,
90 jur.legislative_cat_code LEGISLATIVE_CATEGORY,
91 (select party_name
92 from hz_parties
93 where party_id = reg.issuing_authority_id) LEGALAUTH_NAME,
94 (select hzl.address1 || ' ' || hzl.address2 || ' '
95 || hzl.city || ',' || hzl.state
96 || ',' || hzl.country || ' ' || hzl.postal_code
97 from hz_locations hzl, hz_party_sites hps
98 where hps.location_id = hzl.location_id
99 and hps.party_site_id = reg.issuing_authority_site_id) LEGALAUTH_ADDRESS,
100 reg.effective_from,
101 reg.effective_to,
102 reg.location_id,
103 hrl.address_line_1,
104 hrl.address_line_2,
105 hrl.address_line_3,
106 hrl.town_or_city,
107 hrl.region_1,
108 hrl.region_2,
109 hrl.region_3,
110 hrl.postal_code,
111 hrl.country
112 FROM XLE_ETB_PROFILES etb,
113 XLE_REGISTRATIONS reg,
114 HR_LOCATIONS_ALL hrl,
115 XLE_JURISDICTIONS_VL jur
116 WHERE
117 etb.establishment_id = reg.source_id
118 AND reg.source_table = 'XLE_ETB_PROFILES'
119 AND hrl.location_id = reg.location_id
120 AND jur.jurisdiction_id = reg.jurisdiction_id
121 AND etb.party_ID like l_party_ID
122 AND etb.establishment_id like l_entity_ID
123 AND nvl(reg.identifying_flag,'N') like l_identifying_flag
124 AND jur.legislative_cat_code like l_legislative_category
125 ;
126
127
128
129 BEGIN
130
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132
133
134 /* Entity Type 'LEGAL_ENTITY' or 'ESTABLISHMENT' is mandatory */
135 IF p_entity_type IS NULL THEN
136 x_return_status := FND_API.G_RET_STS_ERROR ;
137 x_msg_data := 'Missing mandatory arguments';
138 return;
139 END IF;
140
141 /* Entity Type should be 'LEGAL_ENTITY' or 'ESTABLISHMENT' */
142 IF p_entity_type NOT IN ('LEGAL_ENTITY','ESTABLISHMENT') then
143 x_return_status := FND_API.G_RET_STS_ERROR ;
144 x_msg_data := 'Misspelt mandatory arguments';
145 return;
146 end if;
147
148 /* Party ID or Entity ID is mandatory */
149 IF p_party_ID IS null AND p_entity_ID IS NULL THEN
150 x_return_status := FND_API.G_RET_STS_ERROR ;
151 x_msg_data := 'Please pass a value for Party ID or Entity ID.';
152 return;
153 ELSIF p_party_ID IS null and p_entity_ID is not null THEN
154 l_party_id := '%';
155 l_entity_ID := p_entity_ID;
156
157 ELSIF p_entity_ID IS NULL and p_party_ID IS NOT null THEN
158 l_entity_ID := '%';
159 l_party_ID := p_party_ID;
160 ELSE
161 l_party_ID := p_party_ID;
162 l_entity_ID := p_entity_ID;
163
164 END IF;
165
166
167 IF p_identifying_flag IS NULL THEN
168 l_identifying_flag := '%';
169 ELSE
170 l_identifying_flag := p_identifying_flag;
171 END IF;
172
173 IF p_legislative_category IS NULL THEN
174 l_legislative_category := '%';
175 ELSE
176 l_legislative_category := p_legislative_category;
177 END IF;
178
179
180
181 IF p_entity_type = 'LEGAL_ENTITY' THEN
182
183 BEGIN
184
185 /* The following loop assigns Legal Entity, Registration and
186 Legal Address information
187 to the output PL/SQL table x_regitration_info */
188
189 FOR LE_Reg_r in LE_Reg_c LOOP
190 rowcount_flag := true;
191
192 x_registration_info(l_index).party_ID := LE_Reg_r.party_ID;
193 x_registration_info(l_index).entity_ID := LE_Reg_r.entity_ID;
194 x_registration_info(l_index).entity_type := LE_Reg_r.entity_type;
195 x_registration_info(l_index).registration_number := LE_Reg_r.registration_number;
196 x_registration_info(l_index).registered_name := LE_Reg_r.registered_name;
197 x_registration_info(l_index).alternate_registered_name := LE_Reg_r.alternate_registered_name;
198 x_registration_info(l_index).identifying_flag := LE_Reg_r.identifying_flag;
199 x_registration_info(l_index).legislative_category := LE_Reg_r.legislative_category;
200 x_registration_info(l_index).legalauth_name := LE_Reg_r.legalauth_name;
201 x_registration_info(l_index).legalauth_address := LE_Reg_r.legalauth_address;
202 x_registration_info(l_index).effective_from := LE_Reg_r.effective_from;
203 x_registration_info(l_index).effective_to := LE_Reg_r.effective_to;
204 x_registration_info(l_index).location_id := LE_Reg_r.location_id;
205 x_registration_info(l_index).address_line_1 := LE_Reg_r.address_line_1;
206 x_registration_info(l_index).address_line_2 := LE_Reg_r.address_line_2;
207 x_registration_info(l_index).address_line_3 := LE_Reg_r.address_line_3;
208 x_registration_info(l_index).town_or_city := LE_Reg_r.town_or_city;
209 x_registration_info(l_index).region_1 := LE_Reg_r.region_1;
210 x_registration_info(l_index).region_2 := LE_Reg_r.region_2;
211 x_registration_info(l_index).region_3 := LE_Reg_r.region_3;
212 x_registration_info(l_index).postal_code := LE_Reg_r.postal_code;
213 x_registration_info(l_index).country := LE_Reg_r.country;
214
215 l_index := l_index + 1;
216
217 END LOOP;
218
219 IF rowcount_flag <> true THEN
220 x_msg_data := 'No data found for the given parameters.';
221 return;
222 END IF;
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 x_msg_data := 'No data found for the given parameters.';
227 END;
228
229 ELSIF p_entity_type = 'ESTABLISHMENT' THEN
230
231 BEGIN
232
233
234 /* The following loop assigns Establishment, Registration and
235 Legal Address information
236 to the output PL/SQL table x_regitration_info */
237
238 FOR ETB_Reg_r in ETB_Reg_c LOOP
239
240 rowcount_flag := true;
241
242 x_registration_info(l_index).party_ID := ETB_Reg_r.party_ID;
243 x_registration_info(l_index).entity_ID := ETB_Reg_r.entity_ID;
244 x_registration_info(l_index).entity_type := ETB_Reg_r.entity_type;
245 x_registration_info(l_index).registration_number := ETB_Reg_r.registration_number;
246 x_registration_info(l_index).registered_name := ETB_Reg_r.registered_name;
247 x_registration_info(l_index).alternate_registered_name := ETB_Reg_r.alternate_registered_name;
248 x_registration_info(l_index).identifying_flag := ETB_Reg_r.identifying_flag;
249 x_registration_info(l_index).legislative_category := ETB_Reg_r.legislative_category;
250 x_registration_info(l_index).legalauth_name := ETB_Reg_r.legalauth_name;
251 x_registration_info(l_index).legalauth_address := ETB_Reg_r.legalauth_address;
252 x_registration_info(l_index).effective_from := ETB_Reg_r.effective_from;
253 x_registration_info(l_index).effective_to := ETB_Reg_r.effective_to;
254 x_registration_info(l_index).location_id := ETB_Reg_r.location_id;
255 x_registration_info(l_index).address_line_1 := ETB_Reg_r.address_line_1;
256 x_registration_info(l_index).address_line_2 := ETB_Reg_r.address_line_2;
257 x_registration_info(l_index).address_line_3 := ETB_Reg_r.address_line_3;
258 x_registration_info(l_index).town_or_city := ETB_Reg_r.town_or_city;
259 x_registration_info(l_index).region_1 := ETB_Reg_r.region_1;
260 x_registration_info(l_index).region_2 := ETB_Reg_r.region_2;
261 x_registration_info(l_index).region_3 := ETB_Reg_r.region_3;
262 x_registration_info(l_index).postal_code := ETB_Reg_r.postal_code;
263 x_registration_info(l_index).country := ETB_Reg_r.country;
264
265 l_index := l_index + 1;
266
267
268 END LOOP;
269
270 IF rowcount_flag <> true THEN
271 x_msg_data := 'No data found for the given parameters.';
272 return;
273 END IF;
274
275 EXCEPTION
276 WHEN NO_DATA_FOUND THEN
277 x_msg_data := 'No data found for the given parameters.';
278 END;
279
280 END IF;
281
282 EXCEPTION
283 WHEN NO_DATA_FOUND THEN
284 x_return_status := FND_API.G_RET_STS_ERROR ;
285 x_msg_data := 'No data found for the given parameters.';
286
287 WHEN FND_API.G_EXC_ERROR THEN
288 x_return_status := FND_API.G_RET_STS_ERROR ;
289 x_msg_data := 'No data found for the given parameters.';
290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
292 x_msg_data := 'No data found for the given parameters.';
293 WHEN OTHERS THEN
294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
295 x_msg_data := 'No data found for the given parameters.';
296 END Get_Registration_Info;
297
298
299 PROCEDURE Get_Establishment_Info(
300 x_return_status OUT NOCOPY VARCHAR2 ,
301 x_msg_count OUT NOCOPY NUMBER ,
302 x_msg_data OUT NOCOPY VARCHAR2 ,
303 P_PARTY_ID IN XLE_ENTITY_PROFILES.PARTY_ID%TYPE,
304 p_establishment_id IN XLE_ETB_PROFILES.ESTABLISHMENT_ID%TYPE,
305 p_legalentity_id IN XLE_ENTITY_PROFILES.legal_entity_id%TYPE,
306 p_etb_reg IN VARCHAR2,
307 X_ESTABLISHMENT_INFO OUT NOCOPY Establishment_Tbl_Type
308 )
309 AS
310
311 /* Declare local variables */
312
313 party_id NUMBER;
314 establishment_id NUMBER;
315
316 l_party_ID VARCHAR2(100);
317 l_establishment_ID VARCHAR2(100);
318 l_identifying_flag VARCHAR2(1);
319 l_legislative_category VARCHAR2(100);
320
321 l_index NUMBER := 1;
322
323 l_registration_info XLE_UTILITIES_GRP.Registration_Tbl_Type;
324 l_msg_data VARCHAR2(1000);
325 l_msg_count number;
326 l_return_status varchar2(10);
327 l_establishment_name xle_etb_profiles.name%type;
328 l_legalentity_id varchar2(100);
329 legalentity_id number;
330
331 rowcount_flag BOOLEAN := false;
332
333 /* The following cursor selects establishment specific legal information
334 from xle_etb_profiles */
335
336 CURSOR ETB_Reg_c IS
337 SELECT etb.party_id,
338 etb.establishment_id,
339 etb.name establishment_name,
340 etb.legal_entity_id,
341 etb.main_establishment_flag,
342 etb.activity_code,
343 etb.sub_activity_code,
344 etb.type_of_company,
345 etb.effective_from etb_effective_from,
346 etb.effective_to etb_effective_to
347 FROM XLE_ETB_PROFILES etb
348 WHERE etb.party_ID like l_party_ID
349 AND etb.establishment_id like l_establishment_id
350 AND etb.legal_entity_id like l_legalentity_id
351 ;
352
353
354
355 BEGIN
356
357 -- SAVEPOINT Get_Establishment_Info;
358
359 -- Initialize API return status to success
360
361 /* Mandatory arguments are missing. Either Party ID or Legal Entity ID
362 has to be passed */
363
364 IF p_party_ID IS null AND p_establishment_ID IS NULL AND
365 p_legalentity_id IS NULL THEN
366 x_return_status := FND_API.G_RET_STS_ERROR ;
367 x_msg_data := 'Please pass a value for Party ID or Establishment ID or Legal Entity ID.';
368 return;
369 END IF;
370
371 IF p_party_ID IS null THEN
372 l_party_ID := '%';
373 ELSE
374 l_party_ID := p_party_ID;
375
376 SELECT to_number(l_party_ID)
377 INTO party_ID
378 from dual;
379 END IF;
380
381
382 IF p_establishment_ID is null THEN
383 l_establishment_ID := '%';
384 ELSE
385 l_establishment_ID := p_establishment_ID;
386 SELECT to_number(l_establishment_ID)
387 INTO establishment_ID
388 from dual;
389 END IF;
390
391 IF p_legalentity_id IS NULL THEN
392 l_legalentity_id := '%';
393 ELSE
394 l_legalentity_id := p_legalentity_id;
395 END IF;
396
397
398 BEGIN
399
400 /* Establishment information for the given party ID or ETB ID or
401 Legal Entity ID is retrieved from the table XLE_ETB_PROFILES in the
402 following cursor */
403
404 FOR ETB_Reg_r in ETB_Reg_c LOOP
405
406 BEGIN
407
408
409 /* Invoke get_registration_info API to get the registration information
410 for the
411 current establishment obtained from the cursor record ETB_REG_R */
412
413 xle_utilities_grp.get_registration_info(
414 x_return_status=> l_return_status,
415 x_msg_count=> l_msg_count,
416 x_msg_data=> l_msg_data,
417 p_party_id => ETB_Reg_r.party_id,
418 p_entity_id => ETB_Reg_r.establishment_ID,
419 p_entity_type => 'ESTABLISHMENT',
420 p_identifying_flag => 'Y',
421 p_legislative_category => null,
422 x_registration_info=> l_registration_info);
423
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 x_msg_data := 'No data found for the given parameters.';
428 return;
429 END;
430
431 BEGIN
432
433 /* The registration information for the establishment in ETB_REG_R
434 is returned by the previous API call as a PL/SQL table
435 l_registration_info.
436 The following loop retrieves the registration information and
437 assigns the values to output table records*/
438
439 FOR x IN l_registration_info.FIRST..l_registration_info.LAST LOOP
440 rowcount_flag := true;
441
442
443 /* Assign the Establishment information to the output record
444 variables */
445
446 x_establishment_info(l_index).establishment_id := ETB_Reg_r.establishment_id;
447 x_establishment_info(l_index).establishment_name := ETB_Reg_r.establishment_name;
448 x_establishment_info(l_index).party_ID := ETB_Reg_r.party_ID;
449 x_establishment_info(l_index).legal_entity_id := ETB_Reg_r.legal_entity_id;
450 x_establishment_info(l_index).main_establishment_flag := ETB_Reg_r.main_establishment_flag;
451 x_establishment_info(l_index).activity_code := ETB_Reg_r.activity_code;
452 x_establishment_info(l_index).sub_activity_code := ETB_Reg_r.sub_activity_code;
453 x_establishment_info(l_index).type_of_company := ETB_Reg_r.type_of_company;
454 x_establishment_info(l_index).effective_from := ETB_Reg_r.etb_effective_from;
455 x_establishment_info(l_index).effective_to := ETB_Reg_r.etb_effective_to;
456
457
458 /* Assign the Establishment's registration information to the output
459 record variables */
460
461 x_establishment_info(l_index).registration_number := l_registration_info(x).registration_number;
462 x_establishment_info(l_index).identifying_flag := l_registration_info(x).identifying_flag;
463 x_establishment_info(l_index).legislative_category := l_registration_info(x).legislative_category;
464 x_establishment_info(l_index).effective_from := l_registration_info(x).effective_from;
465 x_establishment_info(l_index).effective_to := l_registration_info(x).effective_to;
466 x_establishment_info(l_index).location_id := l_registration_info(x).location_id;
467 x_establishment_info(l_index).address_line_1 := l_registration_info(x).address_line_1;
468 x_establishment_info(l_index).address_line_2 := l_registration_info(x).address_line_2;
469 x_establishment_info(l_index).address_line_3 := l_registration_info(x).address_line_3;
470 x_establishment_info(l_index).town_or_city := l_registration_info(x).town_or_city;
471 x_establishment_info(l_index).region_1 := l_registration_info(x).region_1;
472 x_establishment_info(l_index).region_2 := l_registration_info(x).region_2;
473 x_establishment_info(l_index).region_3 := l_registration_info(x).region_3;
474 x_establishment_info(l_index).postal_code := l_registration_info(x).postal_code;
475 x_establishment_info(l_index).country := l_registration_info(x).country;
476
477
478 l_index := l_index + 1;
479
480
481 END LOOP;
482
483 -- Bug 4185317
484 /*If the flag is set to derive all etbs (with and without registrations) */
485 IF ( p_etb_reg <> 'Y') then
486 IF l_registration_info.count = 0 THEN
487
488 x_establishment_info(l_index).establishment_id := ETB_Reg_r.establishment_id;
489 x_establishment_info(l_index).establishment_name := ETB_Reg_r.establishment_name;
490 x_establishment_info(l_index).party_ID := ETB_Reg_r.party_ID;
491 x_establishment_info(l_index).legal_entity_id := ETB_Reg_r.legal_entity_id;
492 x_establishment_info(l_index).main_establishment_flag := ETB_Reg_r.main_establishment_flag;
493 x_establishment_info(l_index).activity_code := ETB_Reg_r.activity_code;
494 x_establishment_info(l_index).sub_activity_code := ETB_Reg_r.sub_activity_code;
495 x_establishment_info(l_index).type_of_company := ETB_Reg_r.type_of_company;
496 x_establishment_info(l_index).effective_from := ETB_Reg_r.etb_effective_from;
497 x_establishment_info(l_index).effective_to := ETB_Reg_r.etb_effective_to;
498
499 l_index := l_index + 1;
500 END IF;
501 END IF;
502
503 EXCEPTION
504 when others then
505 x_msg_data := 'No data found for the given parameters.';
506 END;
507
508 END LOOP;
509
510 if rowcount_flag <> true then
511 x_msg_data := 'No data found for the given parameters.';
512 end if;
513
514 EXCEPTION
515 WHEN NO_DATA_FOUND THEN
516 x_msg_data := 'No data found for the given parameters.';
517 return;
518
519 END;
520
521 x_msg_data := null;
522 x_return_status := FND_API.G_RET_STS_SUCCESS;
523
524 EXCEPTION
525 WHEN NO_DATA_FOUND THEN
526 x_return_status := FND_API.G_RET_STS_ERROR ;
527 x_msg_data := 'No data found for the given parameters.';
528 -- ROLLBACK TO Get_Establishment_Info;
529
530 WHEN FND_API.G_EXC_ERROR THEN
531 x_return_status := FND_API.G_RET_STS_ERROR ;
532 x_msg_data := 'No data found for the given parameters.';
533 --ROLLBACK TO Get_Establishment_Info;
534 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
536 x_msg_data := 'No data found for the given parameters.';
537 -- ROLLBACK TO Get_Establishment_Info;
538 WHEN OTHERS THEN
539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
540 x_msg_data := 'No data found for the given parameters.';
541 -- ROLLBACK TO Get_Establishment_Info;
542 END Get_Establishment_Info;
543
544
545 PROCEDURE Get_LegalEntity_Info(
546 x_return_status OUT NOCOPY VARCHAR2 ,
547 x_msg_count OUT NOCOPY NUMBER ,
548 x_msg_data OUT NOCOPY VARCHAR2 ,
549 P_PARTY_ID IN XLE_ENTITY_PROFILES.PARTY_ID%TYPE,
550 P_LegalEntity_ID IN XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE,
551 X_LEGALENTITY_INFO OUT NOCOPY LegalEntity_Rec
552 )
553 AS
554 party_id NUMBER;
555 legalentity_id NUMBER;
556
557 l_party_ID number;
558 l_legal_entity_id number;
559 l_legalentity_name xle_entity_profiles.name%type;
560 l_legal_identifier xle_entity_profiles.legal_entity_identifier%type;
561 l_transacting_flag xle_entity_profiles.transacting_entity_flag%type;
562 l_activity_code xle_entity_profiles.activity_code%type;
563 l_type_of_company xle_entity_profiles.type_of_company%type;
564 l_sub_activity_code xle_entity_profiles.sub_activity_code%type;
565 l_le_effective_from xle_entity_profiles.effective_from%type;
566 l_le_effective_to xle_entity_profiles.effective_to%type;
567
568 l_index NUMBER := 1;
569
570 l_registration_info XLE_UTILITIES_GRP.Registration_Tbl_Type;
571 l_msg_data VARCHAR2(1000);
572 l_msg_count number;
573 l_return_status varchar2(10);
574
575 rowcount_flag BOOLEAN := false;
576
577 BEGIN
578
579 -- SAVEPOINT Get_LegalEntity_Info;
580
581 -- Initialize API return status to success
582
583 x_return_status := FND_API.G_RET_STS_SUCCESS;
584
585
586 /* Mandatory arguments are missing. Either Party ID or Legal Entity ID
587 has to be passed */
588
589 IF p_party_ID IS null AND p_legalentity_id IS NULL THEN
590 x_return_status := FND_API.G_RET_STS_ERROR ;
591 x_msg_data := 'Please pass a value for Party ID or Legal Entity ID.';
592 return;
593 ELSIF p_party_ID IS null and p_legalentity_id is not null THEN
594
595 /* Select legal entity information into local placeholder variables
596 for the given party ID or Legal Entity ID */
597
598 SELECT lep.party_id,
599 lep.legal_entity_id,
600 lep.name legalentity_name,
601 lep.legal_entity_identifier,
602 lep.transacting_entity_flag,
603 lep.activity_code,
604 lep.sub_activity_code,
605 lep.type_of_company,
606 lep.effective_from,
607 lep.effective_to
608 into l_party_id,
609 l_legal_entity_id,
610 l_legalentity_name,
611 l_legal_identifier,
612 l_transacting_flag,
613 l_activity_code,
614 l_sub_activity_code,
615 l_type_of_company,
616 l_le_effective_from,
617 l_le_effective_to
618 FROM XLE_ENTITY_PROFILES lep
619 WHERE lep.legal_entity_id = p_legalentity_id;
620
621 ELSIF p_legalentity_id IS NULL and p_party_ID IS NOT null THEN
622
623 SELECT lep.party_id,
624 lep.legal_entity_id,
625 lep.name legalentity_name,
626 lep.legal_entity_identifier,
627 lep.transacting_entity_flag,
628 lep.activity_code,
629 lep.sub_activity_code,
630 lep.type_of_company,
631 lep.effective_from,
632 lep.effective_to
633 into l_party_id,
634 l_legal_entity_id,
635 l_legalentity_name,
636 l_legal_identifier,
637 l_transacting_flag,
638 l_activity_code,
639 l_sub_activity_code,
640 l_type_of_company,
641 l_le_effective_from,
642 l_le_effective_to
643 FROM XLE_ENTITY_PROFILES lep
644 WHERE lep.party_ID = p_party_id;
645
646
647 ELSE
648
649 SELECT lep.party_id,
650 lep.legal_entity_id,
651 lep.name legalentity_name,
652 lep.legal_entity_identifier,
653 lep.transacting_entity_flag,
654 lep.activity_code,
655 lep.sub_activity_code,
656 lep.type_of_company,
657 lep.effective_from,
658 lep.effective_to
659 into l_party_id,
660 l_legal_entity_id,
661 l_legalentity_name,
662 l_legal_identifier,
663 l_transacting_flag,
664 l_activity_code,
665 l_sub_activity_code,
666 l_type_of_company,
667 l_le_effective_from,
668 l_le_effective_to
669 FROM XLE_ENTITY_PROFILES lep
670 WHERE lep.party_ID = p_party_id
671 and lep.legal_entity_id = p_legalentity_id;
672
673 END IF;
674
675
676 BEGIN
677
678 xle_utilities_grp.get_registration_info(
679 x_return_status=> l_return_status,
680 x_msg_count=> l_msg_count,
681 x_msg_data=> l_msg_data,
682 p_party_id => l_party_id,
683 p_entity_id => l_legal_entity_id,
684 p_entity_type => 'LEGAL_ENTITY',
685 p_identifying_flag => 'Y',
686 p_legislative_category => null,
687 x_registration_info=> l_registration_info);
688
689
690 EXCEPTION
691 WHEN OTHERS THEN
692 x_msg_data := 'No data found for the given parameters.';
693 return;
694 END;
695
696
697 x_legalentity_info.legal_entity_id := l_legal_entity_id;
698 x_legalentity_info.name := l_legalentity_name;
699 x_legalentity_info.party_ID := l_party_ID;
700 x_legalentity_info.legal_entity_identifier := l_legal_identifier;
701 x_legalentity_info.transacting_entity_flag := l_transacting_flag;
702 x_legalentity_info.activity_code := l_activity_code;
703 x_legalentity_info.sub_activity_code := l_sub_activity_code;
704 x_legalentity_info.type_of_company := l_type_of_company;
705 x_legalentity_info.le_effective_from := l_le_effective_from;
706 x_legalentity_info.le_effective_to := l_le_effective_to;
707 x_legalentity_info.registration_number := l_registration_info(1).registration_number;
708 x_legalentity_info.identifying_flag := l_registration_info(1).identifying_flag;
709 x_legalentity_info.legislative_category := l_registration_info(1).legislative_category;
710 x_legalentity_info.effective_from := l_registration_info(1).effective_from;
711 x_legalentity_info.effective_to := l_registration_info(1).effective_to;
712 x_legalentity_info.location_id := l_registration_info(1).location_id;
713 x_legalentity_info.address_line_1 := l_registration_info(1).address_line_1;
714 x_legalentity_info.address_line_2 := l_registration_info(1).address_line_2;
715 x_legalentity_info.address_line_3 := l_registration_info(1).address_line_3;
716 x_legalentity_info.town_or_city := l_registration_info(1).town_or_city;
717 x_legalentity_info.region_1 := l_registration_info(1).region_1;
718 x_legalentity_info.region_2 := l_registration_info(1).region_2;
719 x_legalentity_info.region_3 := l_registration_info(1).region_3;
720 x_legalentity_info.postal_code := l_registration_info(1).postal_code;
721 x_legalentity_info.country := l_registration_info(1).country;
722
723
724 x_msg_data := null;
725 x_return_status := FND_API.G_RET_STS_SUCCESS;
726 EXCEPTION
727 WHEN NO_DATA_FOUND THEN
728 x_return_status := FND_API.G_RET_STS_ERROR ;
729 x_msg_data := 'No data found for the given parameters.';
730 -- ROLLBACK TO Get_LegalEntity_Info;
731
732 WHEN FND_API.G_EXC_ERROR THEN
733 x_return_status := FND_API.G_RET_STS_ERROR ;
734 x_msg_data := 'No data found for the given parameters.';
735 -- ROLLBACK TO Get_LegalEntity_Info;
736 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
738 x_msg_data := 'No data found for the given parameters.';
739 -- ROLLBACK TO Get_LegalEntity_Info;
740 WHEN OTHERS THEN
741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
742 x_msg_data := 'No data found for the given parameters.';
743 -- ROLLBACK TO Get_LegalEntity_Info;
744 END Get_LegalEntity_Info;
745
746
747
748
749 PROCEDURE Get_History_Info(
750 x_return_status OUT NOCOPY VARCHAR2 ,
751 x_msg_count OUT NOCOPY NUMBER ,
752 x_msg_data OUT NOCOPY VARCHAR2 ,
753 P_ENTITY_ID IN XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE,
754 P_ENTITY_TYPE IN VARCHAR2,
755 P_EFFECTIVE_DATE IN VARCHAR2,
756 X_HISTORY_INFO OUT NOCOPY History_Tbl_Type
757 )
758 IS
759
760 l_history_rec History_Rec;
761
762 l_source_table varchar2(100);
763 l_source_id number;
764 l_source_column_name varchar2(100);
765 l_entity_id number;
766
767 l_effective_date date;
768
769 cursor history_dated_c is
770 SELECT xlh1.source_table,
771 xlh1.source_id,
772 xlh1.source_column_name,
773 xlh1.source_column_value,
774 xlh1.effective_from,
775 xlh1.effective_to,
776 xlh1.comments
777 FROM XLE_HISTORIES xlh1
778 where trunc(to_date(l_effective_date,'DD-MM-YYYY'))
779 between (trunc(to_date(effective_from,'DD-MM-YYYY'))) and
780 trunc(nvl(to_date(effective_to,'DD-MM-YYYY'),sysdate))
781 AND xlh1.source_table = l_source_table
782 AND xlh1.source_id = l_entity_id;
783
784
785 cursor history_c is
786 SELECT xlh1.source_table,
787 xlh1.source_id,
788 xlh1.source_column_name,
789 xlh1.source_column_value,
790 xlh1.effective_from,
791 xlh1.effective_to,
792 xlh1.comments
793 FROM XLE_HISTORIES xlh1
794 WHERE xlh1.source_table = l_source_table
795 AND xlh1.source_id = l_entity_id;
796
797 l_index number := 1;
798 rowcount_flag boolean := false;
799
800 BEGIN
801 -- Initialize API return status to success
802 x_return_status := FND_API.G_RET_STS_SUCCESS;
803
804 IF P_ENTITY_ID IS null OR P_ENTITY_TYPE IS NULL THEN
805 x_msg_data := 'Missing Mandatory Arguments.';
806 return;
807 END IF;
808
809 IF P_ENTITY_TYPE = 'LEGAL_ENTITY' THEN
810 l_source_table := 'XLE_ENTITY_PROFILES';
811 ELSIF P_ENTITY_TYPE = 'ESTABLISHMENT' THEN
812 l_source_table := 'XLE_ETB_PROFILES';
813 ELSIF P_ENTITY_TYPE = 'REGISTRATIONS' THEN
814 l_source_table := 'XLE_REGISTRATIONS';
815 END IF;
816
817 IF P_EFFECTIVE_DATE is not null THEN
818
819 BEGIN
820 l_entity_id := p_entity_id;
821
822 select to_date(p_effective_date,'DD-MM-YYYY')
823 into l_effective_date
824 from dual;
825
826 begin
827
828 for history_dated_r in history_dated_c loop
829 rowcount_flag := true;
830
831
832 X_HISTORY_INFO(l_index).source_table := history_dated_r.source_table;
833 X_HISTORY_INFO(l_index).source_id := history_dated_r.source_id;
834 X_HISTORY_INFO(l_index).source_column_name := history_dated_r.source_column_name;
835 X_HISTORY_INFO(l_index).source_column_value := history_dated_r.source_column_value;
836 X_HISTORY_INFO(l_index).effective_from := history_dated_r.effective_from;
837 X_HISTORY_INFO(l_index).effective_to := history_dated_r.effective_to;
838 X_HISTORY_INFO(l_index).comments := history_dated_r.comments;
839
840
841 l_index := l_index + 1;
842 end loop;
843 exception
844 when others then
845 null;
846 end;
847
848 if rowcount_flag <> true then
849 x_msg_data := 'No data found for the given parameters.';
850 end if;
851
852 EXCEPTION
853 WHEN NO_DATA_FOUND THEN
854 x_msg_data := 'No data found for the given parameters.';
855 return;
856 END;
857
858 ELSE
859
860 l_entity_id := p_entity_id;
861
862
863 for history_r in history_c loop
864 rowcount_flag := true;
865
866 X_HISTORY_INFO(l_index).source_table := history_r.source_table;
867 X_HISTORY_INFO(l_index).source_id := history_r.source_id;
868 X_HISTORY_INFO(l_index).source_column_name := history_r.source_column_name;
869 X_HISTORY_INFO(l_index).source_column_value := history_r.source_column_value;
870 X_HISTORY_INFO(l_index).effective_from := history_r.effective_from;
871 X_HISTORY_INFO(l_index).effective_to := history_r.effective_to;
872 X_HISTORY_INFO(l_index).comments := history_r.comments;
873
874 l_index := l_index + 1;
875 end loop;
876
877 if rowcount_flag <> true then
878 x_msg_data := 'No data found for the given parameters.';
879 end if;
880
881 END IF;
882
883 EXCEPTION
884 WHEN NO_DATA_FOUND THEN
885 x_return_status := FND_API.G_RET_STS_ERROR ;
886 x_msg_data := 'No data found for the given parameters.';
887
888 WHEN FND_API.G_EXC_ERROR THEN
889 x_return_status := FND_API.G_RET_STS_ERROR ;
890 x_msg_data := 'No data found for the given parameters.';
891 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
893 x_msg_data := 'No data found for the given parameters.';
894 WHEN OTHERS THEN
895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
896 x_msg_data := 'No data found for the given parameters.';
897
898 END Get_History_Info;
899
900
901
902 PROCEDURE Get_LegalEntityID_OU
903 ( p_api_version IN NUMBER,
904 p_init_msg_list IN VARCHAR2,
905 p_commit IN VARCHAR2,
906 x_return_status OUT NOCOPY VARCHAR2 ,
907 x_msg_count OUT NOCOPY NUMBER ,
908 x_msg_data OUT NOCOPY VARCHAR2 ,
909 p_operating_unit IN NUMBER ,
910 x_LegalEntity_tbl OUT NOCOPY LegalEntity_tbl_type
911
912 )
913 AS
914
915 l_api_name CONSTANT VARCHAR2(30):= 'Get_LegalEntityID_OU';
916 l_api_version CONSTANT NUMBER:= 1.0;
917 l_ledger_id HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE;
918 l_le_list GL_MC_INFO.LE_BSV_TBL_TYPE := gl_mc_info.le_bsv_tbl_type();
919 l_legal_entity_id XLE_ENTITY_PROFILES.legal_entity_id%TYPE;
920 l_index NUMBER := 0;
921 l_ledger_flag BOOLEAN;
922
923 l_init_msg_list VARCHAR2(100);
924 l_commit VARCHAR2(100);
925 BEGIN
926 SELECT O3.ORG_INFORMATION3
927 INTO l_ledger_id
928 FROM HR_ALL_ORGANIZATION_UNITS O, HR_ORGANIZATION_INFORMATION O2, HR_ORGANIZATION_INFORMATION O3
929 WHERE O.ORGANIZATION_ID = O2.ORGANIZATION_ID
930 AND O2.ORGANIZATION_ID = O3.ORGANIZATION_ID
931 AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID
932 AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS'
933 AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
934 AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
935 AND O2.ORG_INFORMATION2 = 'Y'
936 AND o.organization_id = p_operating_unit;
937
938 -- l_le_list := gl_mc_info.le_bsv_tbl_type();
939
940 l_ledger_flag := GL_MC_INFO.get_legal_entities(
941 l_ledger_id,
942 l_le_list
943 );
944
945
946 FOR x IN l_le_list.FIRST..l_le_list.LAST LOOP
947
948 l_legal_entity_id := l_le_list(x).legal_entity_id;
949 x_LegalEntity_tbl(l_index) := l_legal_entity_id;
950 l_index :=l_index + 1;
951
952 END LOOP;
953
954
955 END Get_LegalEntityID_OU;
956
957
958 PROCEDURE Get_LegalEntityName_PID(
959 p_api_version IN NUMBER,
960 p_init_msg_list IN VARCHAR2,
961 p_commit IN VARCHAR2,
962 x_return_status OUT NOCOPY VARCHAR2,
963 x_msg_count OUT NOCOPY NUMBER,
964 x_msg_data OUT NOCOPY VARCHAR2,
965 p_party_id IN NUMBER,
966 x_legal_entity_name OUT NOCOPY VARCHAR2
967 )
968 IS
969 l_api_name CONSTANT VARCHAR2(30) := 'Get_LegalEntityName_PID';
970 l_api_version CONSTANT NUMBER:= 1.0;
971
972 l_init_msg_list VARCHAR2(100);
973 l_commit VARCHAR2(100);
974
975 BEGIN
976
977 IF p_init_msg_list IS NULL THEN
978 l_init_msg_list := FND_API.G_FALSE;
979 ELSE
980 l_init_msg_list := p_init_msg_list;
981 END IF;
982
983 -- Standard call to check for call compatibility.
984
985 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
986 p_api_version ,
987 l_api_name ,
988 G_PKG_NAME )
989 THEN
990 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
991 END IF;
992 -- Initialize message list if p_init_msg_list is set to TRUE.
993
994 IF FND_API.to_Boolean( l_init_msg_list ) THEN
995 FND_MSG_PUB.initialize;
996 END IF;
997
998 -- Initialize API return status to success
999
1000 x_return_status := FND_API.G_RET_STS_SUCCESS;
1001
1002 -- API body
1003
1004 -- Waiting for Ledger API.
1005
1006 SELECT lep.name
1007 INTO x_legal_entity_name
1008 FROM XLE_ENTITY_PROFILES lep
1009 WHERE lep.party_id=p_party_id;
1010
1011 -- End of API body.
1012 -- Standard call to get message count and if count is 1, get message info.
1013 FND_MSG_PUB.Count_And_Get
1014 ( p_count => x_msg_count ,
1015 p_data => x_msg_data
1016 );
1017
1018 EXCEPTION
1019
1020 WHEN NO_DATA_FOUND THEN
1021 x_return_status := FND_API.G_RET_STS_ERROR ;
1022 x_msg_data := 'The Party ' || p_party_id ||
1023 ' does not have a Legal Entity associated with it.';
1024
1025 WHEN TOO_MANY_ROWS THEN
1026 x_return_status := FND_API.G_RET_STS_ERROR ;
1027 x_msg_data := 'The Party ' || p_party_id ||
1028 ' is associated with more than one Legal Entity.';
1029
1030 WHEN FND_API.G_EXC_ERROR THEN
1031 x_return_status := FND_API.G_RET_STS_ERROR ;
1032 FND_MSG_PUB.Count_And_Get
1033 ( p_count => x_msg_count ,
1034 p_data => x_msg_data
1035 );
1036 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1038 FND_MSG_PUB.Count_And_Get
1039 ( p_count => x_msg_count ,
1040 p_data => x_msg_data
1041 );
1042 WHEN OTHERS THEN
1043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1044 IF FND_MSG_PUB.Check_Msg_Level
1045 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1046 THEN
1047 FND_MSG_PUB.Add_Exc_Msg
1048 ( G_PKG_NAME ,
1049 l_api_name
1050 );
1051 END IF;
1052 FND_MSG_PUB.Count_And_Get
1053 ( p_count => x_msg_count ,
1054 p_data => x_msg_data
1055 );
1056 END Get_LegalEntityName_PID;
1057
1058 PROCEDURE Get_FP_CountryCode_LID(
1059 p_api_version IN NUMBER ,
1060 p_init_msg_list IN VARCHAR2,
1061 p_commit IN VARCHAR2,
1062 x_return_status OUT NOCOPY VARCHAR2 ,
1063 x_msg_count OUT NOCOPY NUMBER ,
1064 x_msg_data OUT NOCOPY VARCHAR2 ,
1065 p_ledger_id IN NUMBER,
1066 x_register_country_tbl OUT NOCOPY CountryCode_tbl_type
1067 )
1068 IS
1069 l_api_name CONSTANT VARCHAR2(30):= 'Get_FP_CountryCode_LID';
1070 l_api_version CONSTANT NUMBER:= 1.0;
1071 l_ledger_id HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE;
1072 l_le_list GL_MC_INFO.LE_BSV_TBL_TYPE := gl_mc_info.le_bsv_tbl_type();
1073 l_legal_entity_id XLE_ENTITY_PROFILES.legal_entity_id%TYPE;
1074 l_index NUMBER;
1075 l_ledger_flag BOOLEAN;
1076 l_country_code HZ_GEOGRAPHIES.COUNTRY_CODE%TYPE;
1077
1078 l_init_msg_list VARCHAR2(100);
1079 l_commit VARCHAR2(100);
1080
1081 l_exists BOOLEAN;
1082 BEGIN
1083 l_exists := FALSE;
1084 l_index := 0;
1085
1086 IF p_init_msg_list IS NULL THEN
1087 l_init_msg_list := FND_API.G_FALSE;
1088 ELSE
1089 l_init_msg_list := p_init_msg_list;
1090 END IF;
1091
1092
1093 -- Standard call to check for call compatibility.
1094 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1095 p_api_version ,
1096 l_api_name ,
1097 G_PKG_NAME )
1098 THEN
1099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100 END IF;
1101 -- Initialize message list if p_init_msg_list is set to TRUE.
1102
1103 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1104 FND_MSG_PUB.initialize;
1105 END IF;
1106
1107 -- Initialize API return status to success
1108
1109 x_return_status := FND_API.G_RET_STS_SUCCESS;
1110
1111 -- API body
1112
1113 l_ledger_id := p_ledger_id;
1114
1115 l_ledger_flag := GL_MC_INFO.get_legal_entities(
1116 p_ledger_id => l_ledger_id,
1117 x_le_list => l_le_list
1118 );
1119
1120
1121 FOR x IN l_le_list.FIRST..l_le_list.LAST LOOP
1122
1123 l_legal_entity_id := l_le_list(x).legal_entity_id;
1124
1125 SELECT DISTINCT hrl.country
1126 INTO l_country_code
1127 FROM XLE_ENTITY_PROFILES lep,
1128 HR_LOCATIONS_ALL hrl,
1129 XLE_REGISTRATIONS reg
1130 WHERE lep.legal_entity_id = l_legal_entity_id
1131 AND reg.source_id = lep.legal_entity_id
1132 AND reg.source_table = 'XLE_ENTITY_PROFILES'
1133 AND reg.location_id = hrl.location_id;
1134
1135
1136 FOR i IN 1..l_index loop
1137 IF x_register_country_tbl(i).country_code = l_country_code THEN
1138 l_exists := true;
1139 EXIT;
1140 ELSE
1141 l_exists := false;
1142 END IF;
1143 END LOOP;
1144
1145 IF l_exists = false THEN
1146 x_register_country_tbl(l_index).country_code := l_country_code;
1147 l_index :=l_index + 1;
1148 END IF;
1149
1150
1151 END LOOP;
1152
1153 -- End of API body.
1154 -- Standard call to get message count and if count is 1, get message info.
1155 FND_MSG_PUB.Count_And_Get
1156 ( p_count => x_msg_count ,
1157 p_data => x_msg_data
1158 );
1159
1160 EXCEPTION
1161
1162 WHEN NO_DATA_FOUND THEN
1163 x_return_status := FND_API.G_RET_STS_ERROR ;
1164 x_msg_data := 'Could not find an associated Country Code for Ledger ID : ' || p_ledger_id;
1165
1166 WHEN TOO_MANY_ROWS THEN
1167 x_return_status := FND_API.G_RET_STS_ERROR ;
1168 x_msg_data := 'Ledger ID : '|| p_ledger_id || ' has more than one Country Code associated with it.';
1169 RAISE FND_API.G_EXC_ERROR;
1170
1171 WHEN FND_API.G_EXC_ERROR THEN
1172 x_return_status := FND_API.G_RET_STS_ERROR ;
1173 FND_MSG_PUB.Count_And_Get
1174 ( p_count => x_msg_count ,
1175 p_data => x_msg_data
1176 );
1177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1179 FND_MSG_PUB.Count_And_Get
1180 ( p_count => x_msg_count ,
1181 p_data => x_msg_data
1182 );
1183 WHEN OTHERS THEN
1184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1185 IF FND_MSG_PUB.Check_Msg_Level
1186 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1187 THEN
1188 FND_MSG_PUB.Add_Exc_Msg
1189 ( G_PKG_NAME ,
1190 l_api_name
1191 );
1192 END IF;
1193 FND_MSG_PUB.Count_And_Get
1194 ( p_count => x_msg_count ,
1195 p_data => x_msg_data
1196 );
1197 END Get_FP_CountryCode_LID;
1198
1199
1200 PROCEDURE Get_FP_CountryCode_OU(
1201 p_api_version IN NUMBER,
1202 p_init_msg_list IN VARCHAR2,
1203 p_commit IN VARCHAR2,
1204 x_return_status OUT NOCOPY VARCHAR2 ,
1205 x_msg_count OUT NOCOPY NUMBER ,
1206 x_msg_data OUT NOCOPY VARCHAR2 ,
1207 p_operating_unit IN NUMBER,
1208 x_country_code OUT NOCOPY VARCHAR2
1209 )
1210 IS
1211 l_api_name CONSTANT VARCHAR2(30):= 'Get_FP_CountryCode_OU';
1212 l_api_version CONSTANT NUMBER:= 1.0;
1213 l_ledger_id HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE;
1214 l_le_list GL_MC_INFO.LE_BSV_TBL_TYPE;
1215 l_legal_entity_id XLE_ENTITY_PROFILES.legal_entity_id%TYPE;
1216 l_index NUMBER;
1217 l_ledger_flag BOOLEAN;
1218 l_country_code HZ_GEOGRAPHIES.COUNTRY_CODE%TYPE;
1219
1220 l_init_msg_list VARCHAR2(100);
1221 l_commit VARCHAR2(100);
1222 BEGIN
1223
1224 l_index := 0;
1225
1226 IF p_init_msg_list IS NULL THEN
1227 l_init_msg_list := FND_API.G_FALSE;
1228 ELSE
1229 l_init_msg_list := p_init_msg_list;
1230 END IF;
1231
1232
1233 -- Standard call to check for call compatibility.
1234 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1235 p_api_version ,
1236 l_api_name ,
1237 G_PKG_NAME )
1238 THEN
1239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1240 END IF;
1241 -- Initialize message list if p_init_msg_list is set to TRUE.
1242
1243 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1244 FND_MSG_PUB.initialize;
1245 END IF;
1246
1247 -- Initialize API return status to success
1248
1249 x_return_status := FND_API.G_RET_STS_SUCCESS;
1250
1251 -- API body
1252
1253
1254 BEGIN
1255
1256 l_legal_entity_id := GET_DefaultLegalContext_OU(p_operating_unit);
1257
1258 IF l_legal_entity_id IS NOT NULL THEN
1259 SELECT hrl.country
1260 INTO x_country_code
1261 FROM XLE_ENTITY_PROFILES xlep,
1262 XLE_REGISTRATIONS reg,
1263 HR_LOCATIONS_ALL hrl
1264 WHERE xlep.legal_entity_id = reg.source_id
1265 AND reg.source_table = 'XLE_ENTITY_PROFILES'
1266 AND reg.identifying_flag = 'Y'
1267 AND nvl(reg.effective_from,sysdate) <= sysdate
1268 AND nvl(reg.effective_to, sysdate) >= sysdate
1269 AND reg.location_id = hrl.location_id
1270 AND xlep.legal_entity_id = l_legal_entity_id;
1271 ELSE
1272 x_country_code := null;
1273 x_return_status := FND_API.G_RET_STS_ERROR;
1274 x_msg_data := 'The Operating Unit is not associated with a Legal Entity.';
1275 END IF;
1276
1277 EXCEPTION
1278 WHEN NO_DATA_FOUND THEN
1279 x_return_status := FND_API.G_RET_STS_ERROR ;
1280 x_msg_data := 'Could not find an associated Country Code for the Legal Entity : ' || l_legal_entity_id;
1281 RAISE FND_API.G_EXC_ERROR;
1282
1283 WHEN TOO_MANY_ROWS THEN
1284 x_return_status := FND_API.G_RET_STS_ERROR ;
1285 x_msg_data := 'The Legal Entity : ' || l_legal_entity_id || ' is associated with more than one Country';
1286 RAISE FND_API.G_EXC_ERROR;
1287
1288 END;
1289
1290 -- End of API body.
1291 -- Standard call to get message count and if count is 1, get message info.
1292 FND_MSG_PUB.Count_And_Get
1293 ( p_count => x_msg_count ,
1294 p_data => x_msg_data
1295 );
1296
1297 EXCEPTION
1298
1299 WHEN FND_API.G_EXC_ERROR THEN
1300 x_return_status := FND_API.G_RET_STS_ERROR ;
1301 FND_MSG_PUB.Count_And_Get
1302 ( p_count => x_msg_count ,
1303 p_data => x_msg_data
1304 );
1305 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1307 FND_MSG_PUB.Count_And_Get
1308 ( p_count => x_msg_count ,
1309 p_data => x_msg_data
1310 );
1311 WHEN OTHERS THEN
1312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1313 IF FND_MSG_PUB.Check_Msg_Level
1314 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1315 THEN
1316 FND_MSG_PUB.Add_Exc_Msg
1317 ( G_PKG_NAME ,
1318 l_api_name
1319 );
1320 END IF;
1321 FND_MSG_PUB.Count_And_Get
1322 ( p_count => x_msg_count ,
1323 p_data => x_msg_data
1324 );
1325 END Get_FP_CountryCode_OU;
1326
1327
1328 PROCEDURE IsEstablishment_PID(
1329 p_api_version IN NUMBER,
1330 p_init_msg_list IN VARCHAR2,
1331 p_commit IN VARCHAR2,
1332 x_return_status OUT NOCOPY VARCHAR2,
1333 x_msg_count OUT NOCOPY NUMBER,
1334 x_msg_data OUT NOCOPY VARCHAR2,
1335 p_party_id IN NUMBER,
1336 x_establishment OUT NOCOPY VARCHAR2
1337
1338 )
1339 IS
1340 l_api_name CONSTANT VARCHAR2(30):= 'IsEstablishment_PID';
1341 l_api_version CONSTANT NUMBER:= 1.0;
1342 l_establishment_flag varchar2(1);
1343
1344 l_init_msg_list VARCHAR2(100);
1345 l_commit VARCHAR2(100);
1346 BEGIN
1347
1348 -- Standard Start of API savepoint
1349
1350
1351 IF p_init_msg_list IS NULL THEN
1352 l_init_msg_list := FND_API.G_FALSE;
1353 ELSE
1354 l_init_msg_list := p_init_msg_list;
1355 END IF;
1356
1357
1358 -- Standard call to check for call compatibility.
1359
1360 IF NOT FND_API.Compatible_API_Call (l_api_version,
1361 p_api_version,
1362 l_api_name,
1363 G_PKG_NAME )
1364 THEN
1365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1366 END IF;
1367 -- Initialize message list if p_init_msg_list is set to TRUE.
1368
1369 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1370 FND_MSG_PUB.initialize;
1371 END IF;
1372
1373 -- Initialize API return status to success
1374
1375 x_return_status := FND_API.G_RET_STS_SUCCESS;
1376
1377 -- API body
1378
1379 l_establishment_flag := 'N';
1380
1381 BEGIN
1382 SELECT 'Y'
1383 INTO l_establishment_flag
1384 FROM XLE_ETB_PROFILES
1385 WHERE party_id = p_party_id
1386 AND ( effective_to >= sysdate OR effective_to is null);
1387
1388 EXCEPTION
1389 WHEN NO_DATA_FOUND THEN
1390 l_establishment_flag := 'N';
1391
1392 END;
1393
1394 IF l_establishment_flag = 'Y' THEN
1395 x_establishment := FND_API.G_TRUE;
1396 ELSE
1397 x_establishment := FND_API.G_FALSE;
1398 END IF;
1399
1400
1401 -- End of API body.
1402
1403 -- Standard call to get message count and if count is 1, get message info.
1404 FND_MSG_PUB.Count_And_Get
1405 ( p_count => x_msg_count ,
1406 p_data => x_msg_data
1407 );
1408
1409 EXCEPTION
1410
1411
1412 WHEN FND_API.G_EXC_ERROR THEN
1413 x_return_status := FND_API.G_RET_STS_ERROR ;
1414 FND_MSG_PUB.Count_And_Get
1415 ( p_count => x_msg_count ,
1416 p_data => x_msg_data
1417 );
1418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1420 FND_MSG_PUB.Count_And_Get
1421 ( p_count => x_msg_count ,
1422 p_data => x_msg_data
1423 );
1424 WHEN OTHERS THEN
1425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1426 IF FND_MSG_PUB.Check_Msg_Level
1427 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1428 THEN
1429 FND_MSG_PUB.Add_Exc_Msg
1430 ( G_PKG_NAME ,
1431 l_api_name
1432 );
1433 END IF;
1434 FND_MSG_PUB.Count_And_Get
1435 ( p_count => x_msg_count ,
1436 p_data => x_msg_data
1437 );
1438 END IsEstablishment_PID;
1439
1440 PROCEDURE IsTransEntity_PID (
1441 p_api_version IN NUMBER ,
1442 p_init_msg_list IN VARCHAR2,
1443 p_commit IN VARCHAR2,
1444 x_return_status OUT NOCOPY VARCHAR2 ,
1445 x_msg_count OUT NOCOPY NUMBER ,
1446 x_msg_data OUT NOCOPY VARCHAR2 ,
1447 p_party_id IN NUMBER ,
1448 x_TransEntity OUT NOCOPY VARCHAR2
1449 )
1450 IS
1451 l_api_name CONSTANT VARCHAR2(30):= 'IsTransEntity_PID';
1452 l_api_version CONSTANT NUMBER:= 1.0;
1453 l_TransEntity VARCHAR2(1);
1454
1455 l_init_msg_list VARCHAR2(100);
1456 l_commit VARCHAR2(100);
1457 BEGIN
1458
1459 -- Standard Start of API savepoint
1460
1461
1462 l_TransEntity := null;
1463
1464 IF p_init_msg_list IS NULL THEN
1465 l_init_msg_list := FND_API.G_FALSE;
1466 ELSE
1467 l_init_msg_list := p_init_msg_list;
1468 END IF;
1469
1470
1471 -- Standard call to check for call compatibility.
1472 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1473 p_api_version ,
1474 l_api_name ,
1475 G_PKG_NAME )
1476 THEN
1477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1478 END IF;
1479 -- Initialize message list if p_init_msg_list is set to TRUE.
1480
1481 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1482 FND_MSG_PUB.initialize;
1483 END IF;
1484
1485 -- Initialize API return status to success
1486 x_return_status := FND_API.G_RET_STS_SUCCESS;
1487
1488 -- API body
1489 BEGIN
1490 SELECT transacting_entity_flag
1491 INTO l_TransEntity
1492 FROM xle_entity_profiles
1493 WHERE party_id = p_party_id;
1494 EXCEPTION
1495 WHEN NO_DATA_FOUND THEN
1496 l_TransEntity := 'N';
1497
1498 END;
1499
1500 IF l_TransEntity = 'Y' THEN
1501 x_TransEntity := FND_API.G_TRUE;
1502 ELSE
1503 x_TransEntity := FND_API.G_FALSE;
1504 END IF;
1505
1506 -- End of API body.
1507 -- Standard call to get message count and if count is 1, get message info.
1508 FND_MSG_PUB.Count_And_Get
1509 ( p_count => x_msg_count ,
1510 p_data => x_msg_data
1511 );
1512
1513
1514 EXCEPTION
1515
1516 WHEN NO_DATA_FOUND THEN
1517 x_return_status := FND_API.G_RET_STS_ERROR ;
1518 x_msg_data := 'The Party ID ' || p_party_id || ' is not associated with a Legal Entity.' ;
1519
1520 WHEN FND_API.G_EXC_ERROR THEN
1521 x_return_status := FND_API.G_RET_STS_ERROR ;
1522 FND_MSG_PUB.Count_And_Get
1523 ( p_count => x_msg_count ,
1524 p_data => x_msg_data
1525 );
1526 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1528 FND_MSG_PUB.Count_And_Get
1529 ( p_count => x_msg_count ,
1530 p_data => x_msg_data
1531 );
1532 WHEN OTHERS THEN
1533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1534 IF FND_MSG_PUB.Check_Msg_Level
1535 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1536 THEN
1537 FND_MSG_PUB.Add_Exc_Msg
1538 ( G_PKG_NAME ,
1539 l_api_name
1540 );
1541 END IF;
1542 FND_MSG_PUB.Count_And_Get
1543 ( p_count => x_msg_count ,
1544 p_data => x_msg_data
1545 );
1546 END IsTransEntity_PID;
1547
1548
1549 PROCEDURE Get_PartyID_OU(
1550 p_api_version IN NUMBER ,
1551 p_init_msg_list IN VARCHAR2,
1552 p_commit IN VARCHAR2,
1553 x_return_status OUT NOCOPY VARCHAR2 ,
1554 x_msg_count OUT NOCOPY NUMBER ,
1555 x_msg_data OUT NOCOPY VARCHAR2,
1556 p_operating_unit IN NUMBER,
1557 x_party_tbl OUT NOCOPY PartyID_tbl_type
1558 )
1559 IS
1560 l_api_name CONSTANT VARCHAR2(30):= 'Get_PartyID_OU';
1561 l_api_version CONSTANT NUMBER:= 1.0;
1562 l_ledger_id HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE;
1563 l_le_list GL_MC_INFO.LE_BSV_TBL_TYPE;
1564 l_legal_entity_id XLE_ENTITY_PROFILES.legal_entity_id%TYPE;
1565 l_index NUMBER;
1566 l_party_id HR_ALL_ORGANIZATION_UNITS.PARTY_ID%TYPE;
1567 l_ledger_flag BOOLEAN;
1568
1569 l_init_msg_list VARCHAR2(100);
1570 l_commit VARCHAR2(100);
1571 BEGIN
1572 -- Standard Start of API savepoint
1573
1574 l_index := 0;
1575
1576 IF p_init_msg_list IS NULL THEN
1577 l_init_msg_list := FND_API.G_FALSE;
1578 ELSE
1579 l_init_msg_list := p_init_msg_list;
1580 END IF;
1581
1582
1583 -- Standard call to check for call compatibility.
1584 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1585 p_api_version ,
1586 l_api_name ,
1587 G_PKG_NAME )
1588 THEN
1589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1590 END IF;
1591 -- Initialize message list if p_init_msg_list is set to TRUE.
1592
1593 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1594 FND_MSG_PUB.initialize;
1595 END IF;
1596
1597 -- Initialize API return status to success
1598 x_return_status := FND_API.G_RET_STS_SUCCESS;
1599
1600 -- API body
1601
1602 BEGIN
1603 SELECT O3.ORG_INFORMATION3
1604 INTO l_ledger_id
1605 FROM HR_ALL_ORGANIZATION_UNITS O, HR_ORGANIZATION_INFORMATION O2, HR_ORGANIZATION_INFORMATION O3
1606 WHERE O.ORGANIZATION_ID = O2.ORGANIZATION_ID
1607 AND O2.ORGANIZATION_ID = O3.ORGANIZATION_ID
1608 AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID
1609 AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS'
1610 AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
1611 AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
1612 AND O2.ORG_INFORMATION2 = 'Y'
1613 AND o.organization_id = p_operating_unit;
1614 EXCEPTION
1615 WHEN NO_DATA_FOUND THEN
1616 x_return_status := FND_API.G_RET_STS_ERROR ;
1617 x_msg_data := 'The Operating Unit : ' || p_operating_unit || ' is not associated with a Ledger ID.';
1618 RAISE FND_API.G_EXC_ERROR;
1619
1620 WHEN TOO_MANY_ROWS THEN
1621 x_return_status := FND_API.G_RET_STS_ERROR ;
1622 x_msg_data := 'The Operating Unit : ' || p_operating_unit || ' is associated with more than one Ledger ID.';
1623 RAISE FND_API.G_EXC_ERROR;
1624 END;
1625
1626
1627 l_ledger_flag := GL_MC_INFO.get_legal_entities(
1628 p_ledger_id => l_ledger_id,
1629 x_le_list => l_le_list
1630 );
1631
1632 BEGIN
1633 FOR x IN l_le_list.FIRST..l_le_list.LAST LOOP
1634 l_legal_entity_id := l_le_list(x).legal_entity_id;
1635
1636 SELECT party_id
1637 INTO l_party_id
1638 FROM XLE_ENTITY_PROFILES
1639 WHERE legal_entity_id = l_legal_entity_id;
1640
1641 x_party_tbl(l_index) := l_party_id;
1642 l_index :=l_index + 1;
1643
1644 END LOOP;
1645 EXCEPTION
1646 WHEN NO_DATA_FOUND THEN
1647 x_return_status := FND_API.G_RET_STS_ERROR ;
1648 x_msg_data := 'The Legal Entity : ' || l_legal_entity_id || ' does not have an associated Party ID.';
1649 RAISE FND_API.G_EXC_ERROR;
1650
1651 WHEN TOO_MANY_ROWS THEN
1652 x_return_status := FND_API.G_RET_STS_ERROR ;
1653 x_msg_data := 'The Legal Entity : ' || l_legal_entity_id || ' is associated with more than one Party ID.';
1654 RAISE FND_API.G_EXC_ERROR;
1655 END;
1656 -- End of API body.
1657
1658 -- Standard call to get message count and if count is 1, get message info.
1659 FND_MSG_PUB.Count_And_Get
1660 ( p_count => x_msg_count ,
1661 p_data => x_msg_data
1662 );
1663
1664 EXCEPTION
1665
1666 WHEN FND_API.G_EXC_ERROR THEN
1667 x_return_status := FND_API.G_RET_STS_ERROR ;
1668 FND_MSG_PUB.Count_And_Get
1669 ( p_count => x_msg_count ,
1670 p_data => x_msg_data
1671 );
1672 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1674 FND_MSG_PUB.Count_And_Get
1675 ( p_count => x_msg_count ,
1676 p_data => x_msg_data
1677 );
1678 WHEN OTHERS THEN
1679 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1680 IF FND_MSG_PUB.Check_Msg_Level
1681 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1682 THEN
1683 FND_MSG_PUB.Add_Exc_Msg
1684 ( G_PKG_NAME ,
1685 l_api_name
1686 );
1687 END IF;
1688 FND_MSG_PUB.Count_And_Get
1689 ( p_count => x_msg_count ,
1690 p_data => x_msg_data
1691 );
1692 END Get_PartyID_OU;
1693
1694
1695
1696 PROCEDURE Is_Intercompany_LEID(
1697 p_api_version IN NUMBER,
1698 p_init_msg_list IN VARCHAR2,
1699 p_commit IN VARCHAR2,
1700 x_return_status OUT NOCOPY VARCHAR2,
1701 x_msg_count OUT NOCOPY NUMBER,
1702 x_msg_data OUT NOCOPY VARCHAR2,
1703 p_legal_entity_id1 IN VARCHAR2,
1704 p_legal_entity_id2 IN VARCHAR2,
1705 x_Intercompany OUT NOCOPY VARCHAR2
1706 )
1707 IS
1708 l_api_name CONSTANT VARCHAR2(30):= 'Is_Intercompany_LEID';
1709 l_api_version CONSTANT NUMBER:= 1.0;
1710
1711 l_init_msg_list VARCHAR2(100);
1712 l_commit VARCHAR2(100);
1713 l_count NUMBER := 0;
1714
1715 BEGIN
1716
1717 IF p_init_msg_list IS NULL THEN
1718 l_init_msg_list := FND_API.G_FALSE;
1719 ELSE
1720 l_init_msg_list := p_init_msg_list;
1721 END IF;
1722
1723
1724 -- Standard call to check for call compatibility.
1725 IF NOT FND_API.Compatible_API_Call (l_api_version,
1726 p_api_version,
1727 l_api_name,
1728 G_PKG_NAME )
1729 THEN
1730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1731 END IF;
1732 -- Initialize message list if p_init_msg_list is set to TRUE.
1733
1734 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1735 FND_MSG_PUB.initialize;
1736 END IF;
1737
1738 -- Initialize API return status to success
1739 x_return_status := FND_API.G_RET_STS_SUCCESS;
1740
1741 -- It is assumed that all LEs can interact with all LEs unless
1742 -- an exception has been explicitly defined
1743 -- Bug 4724057, Related LE functionality will now be used
1744 -- to store intercompany exceptions
1745 SELECT COUNT(*)
1746 INTO l_count
1747 FROM xle_associations ass,
1748 xle_association_types typ
1749 WHERE typ.association_type_id = ass.association_type_id
1750 AND typ.context = 'RELATED_LEGAL_ENTITIES'
1751 AND Nvl(ass.effective_to,SYSDATE) >= SYSDATE
1752 AND ((ass.object_id = p_legal_entity_id1
1753 AND ass.subject_id = p_legal_entity_id2)
1754 OR (ass.object_id = p_legal_entity_id2
1755 AND ass.subject_id = p_legal_entity_id1));
1756
1757 IF l_count > 0
1758 THEN
1759 x_intercompany := 'N';
1760 ELSE
1761 x_intercompany := 'Y';
1762 END IF;
1763
1764 FND_MSG_PUB.Count_And_Get
1765 (p_count => x_msg_count,
1766 p_data => x_msg_data);
1767
1768
1769 EXCEPTION
1770
1771 WHEN FND_API.G_EXC_ERROR THEN
1772 x_return_status := FND_API.G_RET_STS_ERROR ;
1773 FND_MSG_PUB.Count_And_Get
1774 (p_count => x_msg_count,
1775 p_data => x_msg_data);
1776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1778 FND_MSG_PUB.Count_And_Get
1779 (p_count => x_msg_count,
1780 p_data => x_msg_data);
1781 WHEN OTHERS THEN
1782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1783 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1784 THEN
1785 FND_MSG_PUB.Add_Exc_Msg
1786 (G_PKG_NAME ,
1787 l_api_name);
1788 END IF;
1789 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,
1790 p_data => x_msg_data);
1791 END Is_Intercompany_LEID;
1792
1793
1794
1795
1796 PROCEDURE Get_ME_PARTYID_LEID(
1797 p_api_version IN NUMBER,
1798 p_init_msg_list IN VARCHAR2,
1799 p_commit IN VARCHAR2,
1800 x_return_status OUT NOCOPY VARCHAR2,
1801 x_msg_count OUT NOCOPY NUMBER,
1802 x_msg_data OUT NOCOPY VARCHAR2,
1803 p_legal_entity_id IN VARCHAR2,
1804 x_me_party_id OUT NOCOPY VARCHAR2
1805 )
1806 IS
1807 l_api_name CONSTANT VARCHAR2(30):= 'Get_ME_PARTYID_LEID';
1808 l_api_version CONSTANT NUMBER:= 1.0;
1809
1810 l_init_msg_list VARCHAR2(100);
1811 l_commit VARCHAR2(100);
1812
1813 BEGIN
1814
1815 -- Standard Start of API savepoint
1816
1817
1818 IF p_init_msg_list IS NULL THEN
1819 l_init_msg_list := FND_API.G_FALSE;
1820 ELSE
1821 l_init_msg_list := p_init_msg_list;
1822 END IF;
1823
1824
1825 -- Standard call to check for call compatibility.
1826 IF NOT FND_API.Compatible_API_Call (l_api_version,
1827 p_api_version,
1828 l_api_name,
1829 G_PKG_NAME )
1830 THEN
1831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1832 END IF;
1833 -- Initialize message list if p_init_msg_list is set to TRUE.
1834
1835 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1836 FND_MSG_PUB.initialize;
1837 END IF;
1838
1839 -- Initialize API return status to success
1840 x_return_status := FND_API.G_RET_STS_SUCCESS;
1841
1842 -- Start of API body.
1843
1844 BEGIN
1845
1846 SELECT etbp.party_id
1847 INTO x_me_party_id
1848 FROM XLE_ETB_PROFILES etbp
1849 WHERE etbp.main_establishment_flag = 'Y'
1850 AND etbp.legal_entity_id = p_legal_entity_id
1851 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(main_effective_from,sysdate))
1852 AND TRUNC(NVL(main_effective_to,sysdate));
1853
1854 EXCEPTION
1855 WHEN NO_DATA_FOUND THEN
1856 x_return_status := FND_API.G_RET_STS_ERROR ;
1857 x_msg_data := 'There exists no legal entity with ID ' || p_legal_entity_id;
1858 RAISE FND_API.G_EXC_ERROR;
1859
1860 END;
1861
1862 x_return_status := FND_API.G_RET_STS_SUCCESS;
1863
1864 -- End of API body.
1865
1866 -- Standard call to get message count and if count is 1, get message info.
1867 FND_MSG_PUB.Count_And_Get
1868 ( p_count => x_msg_count ,
1869 p_data => x_msg_data
1870 );
1871
1872
1873 EXCEPTION
1874
1875 WHEN FND_API.G_EXC_ERROR THEN
1876 x_return_status := FND_API.G_RET_STS_ERROR ;
1877 FND_MSG_PUB.Count_And_Get
1878 ( p_count => x_msg_count ,
1879 p_data => x_msg_data
1880 );
1881 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1883 FND_MSG_PUB.Count_And_Get
1884 ( p_count => x_msg_count ,
1885 p_data => x_msg_data
1886 );
1887 WHEN OTHERS THEN
1888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1889 IF FND_MSG_PUB.Check_Msg_Level
1890 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1891 THEN
1892 FND_MSG_PUB.Add_Exc_Msg
1893 ( G_PKG_NAME ,
1894 l_api_name
1895 );
1896 END IF;
1897 FND_MSG_PUB.Count_And_Get
1898 ( p_count => x_msg_count ,
1899 p_data => x_msg_data
1900 );
1901 END Get_ME_PARTYID_LEID;
1902
1903
1904
1905
1906
1907 PROCEDURE Get_RegisterNumber_PID(
1908 p_api_version IN NUMBER,
1909 p_init_msg_list IN VARCHAR2,
1910 p_commit IN VARCHAR2,
1911 x_return_status OUT NOCOPY VARCHAR2,
1912 x_msg_count OUT NOCOPY NUMBER,
1913 x_msg_data OUT NOCOPY VARCHAR2,
1914 p_party_id IN NUMBER,
1915 x_regnum_tbl OUT NOCOPY RegNum_tbl_type
1916 )
1917 IS
1918 l_api_name CONSTANT VARCHAR2(30):= 'Get_RegisterNumber_PID';
1919 l_api_version CONSTANT NUMBER:= 1.0;
1920 l_index NUMBER;
1921
1922 l_init_msg_list VARCHAR2(100);
1923 l_commit VARCHAR2(100);
1924 CURSOR regnum_c IS
1925 SELECT reg.registration_number,jur.legislative_cat_code
1926 FROM XLE_ETB_PROFILES etbp,
1927 XLE_REGISTRATIONS reg,
1928 XLE_JURISDICTIONS_VL jur
1929 WHERE etbp.establishment_id = reg.source_id
1930 AND trunc(reg.source_table) = 'XLE_ETB_PROFILES'
1931 AND reg.jurisdiction_id = jur.jurisdiction_id
1932 AND etbp.party_id = p_party_id;
1933
1934 BEGIN
1935
1936 -- Standard Start of API savepoint
1937
1938 l_index := 0;
1939
1940 IF p_init_msg_list IS NULL THEN
1941 l_init_msg_list := FND_API.G_FALSE;
1942 ELSE
1943 l_init_msg_list := p_init_msg_list;
1944 END IF;
1945
1946
1947 -- Standard call to check for call compatibility.
1948 IF NOT FND_API.Compatible_API_Call (l_api_version,
1949 p_api_version,
1950 l_api_name,
1951 G_PKG_NAME )
1952 THEN
1953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1954 END IF;
1955 -- Initialize message list if p_init_msg_list is set to TRUE.
1956
1957 IF FND_API.to_Boolean( l_init_msg_list ) THEN
1958 FND_MSG_PUB.initialize;
1959 END IF;
1960
1961 -- Initialize API return status to success
1962 x_return_status := FND_API.G_RET_STS_SUCCESS;
1963
1964 FOR regnum_r IN regnum_c LOOP
1965
1966 x_regnum_tbl(l_index).registration_number := regnum_r.registration_number;
1967 x_regnum_tbl(l_index).legislative_cat_code := regnum_r.legislative_cat_code;
1968 l_index :=l_index+1;
1969
1970 END LOOP;
1971
1972 -- End of API body.
1973
1974 -- Standard call to get message count and if count is 1, get message info.
1975 FND_MSG_PUB.Count_And_Get
1976 ( p_count => x_msg_count ,
1977 p_data => x_msg_data
1978 );
1979
1980 EXCEPTION
1981
1982 WHEN NO_DATA_FOUND THEN
1983 x_return_status := FND_API.G_RET_STS_ERROR ;
1984 -- x_msg_data := 'Party ID : ' || p_party_id || ' is not associated with an Establishment.';
1985 -- For bug 4185317
1986 x_msg_data := 'No data found for the given set of parameters.';
1987 RAISE FND_API.G_EXC_ERROR;
1988
1989 WHEN FND_API.G_EXC_ERROR THEN
1990 x_return_status := FND_API.G_RET_STS_ERROR ;
1991 FND_MSG_PUB.Count_And_Get
1992 ( p_count => x_msg_count ,
1993 p_data => x_msg_data
1994 );
1995 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1997 FND_MSG_PUB.Count_And_Get
1998 ( p_count => x_msg_count ,
1999 p_data => x_msg_data
2000 );
2001 WHEN OTHERS THEN
2002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2003 IF FND_MSG_PUB.Check_Msg_Level
2004 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2005 THEN
2006 FND_MSG_PUB.Add_Exc_Msg
2007 ( G_PKG_NAME ,
2008 l_api_name
2009 );
2010 END IF;
2011 FND_MSG_PUB.Count_And_Get
2012 ( p_count => x_msg_count ,
2013 p_data => x_msg_data
2014 );
2015 END Get_RegisterNumber_PID;
2016
2017 PROCEDURE Get_PartyClassification_PID(
2018 p_api_version IN NUMBER,
2019 p_init_msg_list IN VARCHAR2,
2020 p_commit IN VARCHAR2,
2021 x_return_status OUT NOCOPY VARCHAR2,
2022 x_msg_count OUT NOCOPY NUMBER,
2023 x_msg_data OUT NOCOPY VARCHAR2,
2024 p_party_id IN NUMBER,
2025 x_partyclass_tbl OUT NOCOPY PartyClass_tbl_type
2026 )
2027 IS
2028 l_api_name CONSTANT VARCHAR2(30):= 'Get_PartyClassification_PID';
2029 l_api_version CONSTANT NUMBER:= 1.0;
2030 l_index NUMBER;
2031
2032 l_init_msg_list VARCHAR2(100);
2033 l_commit VARCHAR2(100);
2034
2035 CURSOR parties_c IS
2036 SELECT etbp.etb_information1 activity_code,
2037 fndlookup.lookup_type class_category,
2038 fndlookup.lookup_code class_code,
2039 fndlookup.meaning meaning
2040 FROM XLE_ETB_PROFILES etbp,
2041 XLE_LOOKUPS fndlookup
2042 WHERE etbp.party_id = p_party_id
2043 AND fndlookup.lookup_code = etbp.etb_information1
2044 AND NVL(fndlookup.START_DATE_ACTIVE, SYSDATE) <= SYSDATE
2045 AND NVL(fndlookup.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
2046 AND fndlookup.ENABLED_FLAG = 'Y'
2047 AND fndlookup.LOOKUP_TYPE IN
2048 (select class_category
2049 from hz_class_categories)
2050 UNION
2051 SELECT etbp.etb_information2 sub_activity_code,
2052 fndlookup.lookup_type class_category,
2053 fndlookup.lookup_code class_code,
2054 fndlookup.meaning meaning
2055 FROM XLE_ETB_PROFILES etbp,
2056 XLE_LOOKUPS fndlookup
2057 WHERE etbp.party_id = p_party_id
2058 AND fndlookup.lookup_code = etbp.etb_information2
2059 AND NVL(fndlookup.START_DATE_ACTIVE, SYSDATE) <= SYSDATE
2060 AND NVL(fndlookup.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
2061 AND fndlookup.ENABLED_FLAG = 'Y'
2062 AND fndlookup.LOOKUP_TYPE IN
2063 (select class_category
2064 from hz_class_categories)
2065 ;
2066
2067
2068 BEGIN
2069 -- Standard Start of API savepoint
2070
2071
2072 l_index := 0;
2073
2074 IF p_init_msg_list IS NULL THEN
2075 l_init_msg_list := FND_API.G_FALSE;
2076 ELSE
2077 l_init_msg_list := p_init_msg_list;
2078 END IF;
2079
2080
2081 -- Standard call to check for call compatibility.
2082 IF NOT FND_API.Compatible_API_Call (l_api_version,
2083 p_api_version,
2084 l_api_name,
2085 G_PKG_NAME )
2086 THEN
2087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2088 END IF;
2089 -- Initialize message list if p_init_msg_list is set to TRUE.
2090
2091 IF FND_API.to_Boolean( l_init_msg_list ) THEN
2092 FND_MSG_PUB.initialize;
2093 END IF;
2094
2095 -- Initialize API return status to success
2096 x_return_status := FND_API.G_RET_STS_SUCCESS;
2097
2098 FOR parties_r IN parties_c LOOP
2099
2100 x_partyclass_tbl(l_index).class_category := parties_r.class_category;
2101 x_partyclass_tbl(l_index).class_code := parties_r.class_code;
2102 x_partyclass_tbl(l_index).meaning := parties_r.meaning;
2103 l_index :=l_index + 1;
2104
2105 END LOOP;
2106
2107 -- End of API body.
2108
2109 -- Standard call to get message count and if count is 1, get message info.
2110 FND_MSG_PUB.Count_And_Get
2111 ( p_count => x_msg_count ,
2112 p_data => x_msg_data
2113 );
2114
2115 EXCEPTION
2116 WHEN NO_DATA_FOUND THEN
2117 x_return_status := FND_API.G_RET_STS_ERROR ;
2118 x_msg_data := 'Party ID :' || p_party_id || ' is not associated with a Legal Entity.';
2119 RAISE FND_API.G_EXC_ERROR;
2120
2121 WHEN FND_API.G_EXC_ERROR THEN
2122 x_return_status := FND_API.G_RET_STS_ERROR ;
2123 FND_MSG_PUB.Count_And_Get
2124 ( p_count => x_msg_count ,
2125 p_data => x_msg_data
2126 );
2127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2129 FND_MSG_PUB.Count_And_Get
2130 ( p_count => x_msg_count ,
2131 p_data => x_msg_data
2132 );
2133 WHEN OTHERS THEN
2134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2135 IF FND_MSG_PUB.Check_Msg_Level
2136 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2137 THEN
2138 FND_MSG_PUB.Add_Exc_Msg
2139 ( G_PKG_NAME ,
2140 l_api_name
2141 );
2142 END IF;
2143 FND_MSG_PUB.Count_And_Get
2144 ( p_count => x_msg_count ,
2145 p_data => x_msg_data
2146 );
2147 END Get_PartyClassification_PID;
2148
2149
2150
2151 PROCEDURE Get_LegalEntity_LGER_BSV
2152 ( p_api_version IN NUMBER ,
2153 p_init_msg_list IN VARCHAR2,
2154 p_commit IN VARCHAR2,
2155 x_return_status OUT NOCOPY VARCHAR2 ,
2156 x_msg_count OUT NOCOPY NUMBER ,
2157 x_msg_data OUT NOCOPY VARCHAR2 ,
2158 p_ledger_id IN NUMBER ,
2159 p_bsv IN VARCHAR2,
2160 x_legal_entity_id OUT NOCOPY NUMBER,
2161 x_legal_entity_name OUT NOCOPY VARCHAR2
2162
2163 )
2164 IS
2165 l_api_name CONSTANT VARCHAR2(30):= 'Get_LegalEntity_LGER_BSV';
2166 l_api_version CONSTANT NUMBER := 1.0;
2167 l_ledger_id HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE;
2168 l_le_list GL_MC_INFO.LE_BSV_TBL_TYPE;
2169 l_legal_entity_id XLE_ENTITY_PROFILES.legal_entity_id%TYPE;
2170 l_index NUMBER;
2171 l_ledger_flag BOOLEAN;
2172 l_country_code HZ_GEOGRAPHIES.COUNTRY_CODE%TYPE;
2173
2174 l_init_msg_list VARCHAR2(100);
2175 l_commit VARCHAR2(100);
2176
2177 l_legal_entity_name XLE_ENTITY_PROFILES.NAME%TYPE;
2178 l_return_var VARCHAR2(1000);
2179 BEGIN
2180 -- Standard Start of API savepoint
2181
2182 l_index := 0;
2183
2184 IF p_init_msg_list IS NULL THEN
2185 l_init_msg_list := FND_API.G_FALSE;
2186 ELSE
2187 l_init_msg_list := p_init_msg_list;
2188 END IF;
2189
2190
2191 -- Standard call to check for call compatibility.
2192 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2193 p_api_version ,
2194 l_api_name ,
2195 G_PKG_NAME )
2196 THEN
2197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2198 END IF;
2199 -- Initialize message list if p_init_msg_list is set to TRUE.
2200
2201 IF FND_API.to_Boolean( l_init_msg_list ) THEN
2202 FND_MSG_PUB.initialize;
2203 END IF;
2204
2205 -- Initialize API return status to success
2206 x_return_status := FND_API.G_RET_STS_SUCCESS;
2207
2208 -- API body
2209
2210 SELECT legal_entity_id,
2211 legal_entity_name
2212 INTO l_legal_entity_id,
2213 l_legal_entity_name
2214 FROM GL_LEDGER_LE_BSV_SPECIFIC_V
2215 WHERE ledger_id = p_ledger_id
2216 AND segment_value = p_bsv;
2217
2218
2219 IF l_legal_entity_id IS NULL OR l_legal_entity_name IS NULL THEN
2220 l_return_var := GL_MC_INFO.INIT_LEDGER_LE_BSV_GT(p_ledger_id);
2221
2222 SELECT legal_entity_id,
2223 legal_entity_name
2224 INTO l_legal_entity_id,
2225 l_legal_entity_name
2226 FROM GL_LEDGER_LE_BSV_GT
2227 WHERE ledger_id = p_ledger_id
2228 AND bal_seg_value = p_bsv;
2229
2230 IF l_legal_entity_id IS NULL OR l_legal_entity_name IS NULL THEN
2231 x_legal_entity_id := null;
2232 x_legal_entity_name := null;
2233 return;
2234
2235 ELSE
2236 x_legal_entity_id := l_legal_entity_id;
2237 x_legal_entity_name := l_legal_entity_name;
2238 END IF;
2239
2240 ELSE
2241 x_legal_entity_id := l_legal_entity_id;
2242 x_legal_entity_name := l_legal_entity_name;
2243 END IF;
2244
2245
2246
2247 -- End of API body.
2248 -- Standard call to get message count and if count is 1, get message info.
2249 FND_MSG_PUB.Count_And_Get
2250 ( p_count => x_msg_count ,
2251 p_data => x_msg_data
2252 );
2253
2254 EXCEPTION
2255 WHEN FND_API.G_EXC_ERROR THEN
2256 x_return_status := FND_API.G_RET_STS_ERROR ;
2257 FND_MSG_PUB.Count_And_Get
2258 ( p_count => x_msg_count ,
2259 p_data => x_msg_data
2260 );
2261 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2263 FND_MSG_PUB.Count_And_Get
2264 ( p_count => x_msg_count ,
2265 p_data => x_msg_data
2266 );
2267 WHEN OTHERS THEN
2268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2269 IF FND_MSG_PUB.Check_Msg_Level
2270 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2271 THEN
2272 FND_MSG_PUB.Add_Exc_Msg
2273 ( G_PKG_NAME ,
2274 l_api_name
2275 );
2276 END IF;
2277 FND_MSG_PUB.Count_And_Get
2278 ( p_count => x_msg_count ,
2279 p_data => x_msg_data
2280 );
2281 END Get_LegalEntity_LGER_BSV;
2282
2283 PROCEDURE Get_LE_Interface(
2284 x_return_status OUT NOCOPY VARCHAR2,
2285 x_msg_count OUT NOCOPY NUMBER,
2286 x_msg_data OUT NOCOPY VARCHAR2,
2287 P_INTERFACE_ATTRIBUTE IN VARCHAR2,
2288 P_INTERFACE_VALUE IN VARCHAR2,
2289 X_LEGAL_ENTITY_ID OUT NOCOPY XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE
2290 ) IS
2291
2292
2293 /* Local Variable */
2294
2295 l_api_name CONSTANT VARCHAR2(30) := 'Get_LE_Interface';
2296 l_api_version CONSTANT NUMBER:= 1.0;
2297
2298 l_cnt NUMBER := 0;
2299 l_return_status VARCHAR2(50);
2300 l_msg_data VARCHAR2(50);
2301 l_ou_le_info XLE_BUSINESSINFO_GRP.OU_LE_Tbl_Type;
2302 l_Ledger_info XLE_BUSINESSINFO_GRP.LE_Ledger_Rec_Type;
2303 l_Inv_Le_info XLE_BUSINESSINFO_GRP.Inv_Org_Rec_Type;
2304
2305
2306 BEGIN
2307
2308 /* If Company Name is passed by the interface */
2309
2310 IF P_INTERFACE_ATTRIBUTE = 'COMPANY_NAME' THEN
2311 select count(legal_entity_id) into l_cnt from xle_entity_profiles
2312 where name = P_INTERFACE_VALUE;
2313
2314 IF l_cnt = 0 THEN
2315 select count(legal_entity_id) into l_cnt
2316 from XLE_ETB_PROFILES
2317 where name = P_INTERFACE_VALUE;
2318
2319 IF l_cnt = 0 THEN
2320 select etb.legal_entity_id into x_legal_entity_id
2321 from XLE_ETB_PROFILES etb, HZ_PARTIES parties
2322 where parties.party_name = P_INTERFACE_VALUE
2323 and parties.party_id = etb.party_id;
2324 ELSE
2325 select legal_entity_id into x_legal_entity_id
2326 from XLE_ETB_PROFILES
2327 where name = P_INTERFACE_VALUE;
2328 END IF;
2329
2330 ELSE
2331 select legal_entity_id into x_legal_entity_id
2332 from XLE_ENTITY_PROFILES
2333 where name = P_INTERFACE_VALUE;
2334 END IF;
2335
2336 END IF;
2337
2338 /* If Legislative Category Code is passed by the interface */
2339
2340 IF P_INTERFACE_ATTRIBUTE = 'LEGISLATIVE_CAT' THEN
2341
2342 select ent.LEGAL_ENTITY_ID into x_legal_entity_id
2343 from XLE_ENTITY_PROFILES ent, XLE_JURISDICTIONS_B jur, XLE_REGISTRATIONS reg
2344 where jur.LEGISLATIVE_CAT_CODE = P_INTERFACE_VALUE
2345 and jur.JURISDICTION_ID = reg.JURISDICTION_ID
2346 and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2347
2348 END IF;
2349
2350
2351 /* If Registration Number is passed by the interface */
2352
2353 IF P_INTERFACE_ATTRIBUTE = 'REGISTRATION_NUM' THEN
2354
2355 /* Check if the Registration Number belongs to Legal Entity or an Establishment */
2356
2357 select count(ent.legal_entity_id) into l_cnt from
2358 XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
2359 where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2360 and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2361
2362 IF l_cnt = 0 THEN
2363 select etb.legal_entity_id into x_legal_entity_id
2364 from XLE_ETB_PROFILES etb, XLE_REGISTRATIONS reg
2365 where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2366 and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
2367 ELSE
2368 select ent.legal_entity_id into x_legal_entity_id from
2369 XLE_ENTITY_PROFILES ent,XLE_REGISTRATIONS reg
2370 where reg.REGISTRATION_NUMBER = P_INTERFACE_VALUE
2371 and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2372 END IF;
2373
2374 END IF;
2375
2376 /* If Geography ID is passed by the interface */
2377
2378 IF P_INTERFACE_ATTRIBUTE = 'GEOGRAPHY_ID' THEN
2379
2380 select ent.LEGAL_ENTITY_ID into x_legal_entity_id
2381 from XLE_ENTITY_PROFILES ent, XLE_JURISDICTIONS_B jur, XLE_REGISTRATIONS reg
2382 where jur.GEOGRAPHY_ID = TO_NUMBER(P_INTERFACE_VALUE)
2383 and jur.JURISDICTION_ID = reg.JURISDICTION_ID
2384 and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2385
2386 END IF;
2387
2388 /* If Location ID is passed by the interface */
2389
2390 IF P_INTERFACE_ATTRIBUTE = 'LOCATION_ID' THEN
2391
2392 /* Check if the Registration Number belongs to Legal Entity or an Establishment */
2393 select count(ent.legal_entity_id) into l_cnt
2394 from XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
2395 where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2396 and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2397
2398 IF l_cnt = 0 THEN
2399 select etb.legal_entity_id into x_legal_entity_id
2400 from XLE_ETB_PROFILES etb, XLE_REGISTRATIONS reg
2401 where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2402 and reg.SOURCE_ID = etb.ESTABLISHMENT_ID;
2403 ELSE
2404 select ent.legal_entity_id into x_legal_entity_id from
2405 XLE_ENTITY_PROFILES ent, XLE_REGISTRATIONS reg
2406 where reg.LOCATION_ID = TO_NUMBER(P_INTERFACE_VALUE)
2407 and reg.SOURCE_ID = ent.LEGAL_ENTITY_ID;
2408 END IF;
2409 END IF;
2410
2411 /* If OPERATING UNIT ID is passed by the interface */
2412
2413 IF P_INTERFACE_ATTRIBUTE = 'OPERATING_UNIT_ID' THEN
2414 XLE_BUSINESSINFO_GRP.Get_OperatingUnit_Info(
2415 x_return_status => l_return_status,
2416 x_msg_data => l_msg_data,
2417 p_operating_unit => TO_NUMBER(P_INTERFACE_VALUE),
2418 p_legal_entity_id => NULL,
2419 p_party_id => NULL,
2420 x_ou_le_info => l_ou_le_info);
2421
2422 x_legal_entity_id := l_ou_le_info(1).legal_entity_id;
2423
2424 END IF;
2425
2426 /* If LEDGER ID is passed by the interface */
2427
2428 IF P_INTERFACE_ATTRIBUTE = 'LEDGER_ID' THEN
2429 XLE_BUSINESSINFO_GRP.Get_Ledger_Info(
2430 x_return_status => l_return_status,
2431 x_msg_data => l_msg_data,
2432 P_Ledger_ID => TO_NUMBER(P_INTERFACE_VALUE),
2433 x_Ledger_info => l_Ledger_info);
2434
2435 x_legal_entity_id := l_ledger_info(1).legal_entity_id;
2436
2437 END IF;
2438
2439 /* If INVENTORY ORG ID is passed by the interface */
2440
2441 IF P_INTERFACE_ATTRIBUTE = 'INVENTORY_ORG_ID' THEN
2442 XLE_BUSINESSINFO_GRP.Get_InvOrg_Info(
2443 x_return_status => l_return_status,
2444 x_msg_data => l_msg_data,
2445 P_InvOrg_ID => TO_NUMBER(P_INTERFACE_VALUE),
2446 P_Le_ID => NULL,
2447 P_Party_ID => NULL,
2448 x_Inv_Le_info => l_Inv_Le_info);
2449
2450 x_legal_entity_id := l_Inv_Le_info(1).legal_entity_id;
2451
2452 END IF;
2453
2454
2455 EXCEPTION
2456
2457 WHEN NO_DATA_FOUND THEN
2458 x_return_status := FND_API.G_RET_STS_ERROR ;
2459 x_msg_data := 'No data found for the given parameters.';
2460
2461 WHEN OTHERS THEN
2462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2463 x_msg_data := 'No data found for the given parameters';
2464
2465 END Get_LE_Interface;
2466
2467 Procedure Get_FP_VATRegistration_LEID
2468 (
2469 p_api_version IN NUMBER,
2470 p_init_msg_list IN VARCHAR2,
2471 p_commit IN VARCHAR2,
2472 p_effective_date IN zx_registrations.effective_from%Type,
2473 x_return_status OUT NOCOPY VARCHAR2,
2474 x_msg_count OUT NOCOPY NUMBER,
2475 x_msg_data OUT NOCOPY VARCHAR2,
2476 p_legal_entity_id IN NUMBER,
2477 x_registration_number OUT NOCOPY VARCHAR2
2478 )
2479 IS
2480
2481 l_api_name CONSTANT VARCHAR2(30):= 'Get_FP_VATRegistration_LEID';
2482 l_api_version CONSTANT NUMBER := 1.0;
2483 l_commit VARCHAR2(100);
2484 l_init_msg_list VARCHAR2(100);
2485
2486 x_me_party_id NUMBER;
2487 l_me_party_id NUMBER;
2488
2489 l_vat_registration VARCHAR2(1000);
2490
2491 BEGIN
2492
2493 -- Standard Start of API savepoint
2494
2495
2496 IF p_init_msg_list IS NULL THEN
2497 l_init_msg_list := FND_API.G_FALSE;
2498 ELSE
2499 l_init_msg_list := p_init_msg_list;
2500 END IF;
2501
2502
2503 -- Standard call to check for call compatibility.
2504 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2505 p_api_version ,
2506 l_api_name ,
2507 G_PKG_NAME )
2508 THEN
2509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2510 END IF;
2511 -- Initialize message list if p_init_msg_list is set to TRUE.
2512
2513 IF FND_API.to_Boolean( l_init_msg_list ) THEN
2514 FND_MSG_PUB.initialize;
2515 END IF;
2516
2517 -- Initialize API return status to success
2518 x_return_status := FND_API.G_RET_STS_SUCCESS;
2519
2520 -- API body
2521
2522 Get_ME_PARTYID_LEID(
2523 1.0,
2524 'F',
2525 'F',
2526 x_return_status,
2527 x_msg_count,
2528 x_msg_data,
2529 p_legal_entity_id,
2530 x_me_party_id
2531 );
2532
2533 l_me_party_id := x_me_party_id;
2534
2535 /* x_registration_number := ZX_TCM_CONTROL_PKG.Get_Default_Tax_Reg (
2536 l_me_party_id,
2537 'LEGAL_ESTABLISHMENT',
2538 p_effective_date,
2539 p_init_msg_list,
2540 x_return_status,
2541 x_msg_count,
2542 x_msg_data
2543 ); */
2544
2545 x_registration_number := ZX_API_PUB.get_default_tax_reg
2546 (
2547 p_api_version => 1.0 ,
2548 p_init_msg_list => NULL,
2549 p_commit=> NULL,
2550 p_validation_level => NULL,
2551 x_return_status => x_return_status,
2552 x_msg_count => x_msg_count,
2553 x_msg_data => x_msg_data,
2554 p_party_id => l_me_party_id,
2555 p_party_type => 'LEGAL_ESTABLISHMENT',
2556 p_effective_date =>p_effective_date );
2557
2558 -- End of API body.
2559 -- Standard call to get message count and if count is 1, get message info.
2560 FND_MSG_PUB.Count_And_Get
2561 ( p_count => x_msg_count ,
2562 p_data => x_msg_data
2563 );
2564
2565
2566 EXCEPTION
2567 WHEN OTHERS THEN
2568 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2569 x_msg_data := SQLERRM;
2570 END;
2571
2572
2573 function Get_DefaultLegalContext_OU(
2574 p_operating_unit IN NUMBER )
2575 RETURN NUMBER IS
2576 DLC_VAL NUMBER;
2577 BEGIN
2578
2579 -- For Bug 4616389
2580 -- SAVEPOINT Get_DefaultLegalContext_OU;
2581
2582 SELECT NVL(O3.ORG_INFORMATION2,-1)
2583 INTO DLC_VAL
2584 FROM HR_ALL_ORGANIZATION_UNITS O
2585 , HR_ORGANIZATION_INFORMATION O2
2586 , HR_ORGANIZATION_INFORMATION O3
2587 WHERE O.ORGANIZATION_ID = O2.ORGANIZATION_ID
2588 AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID
2589 AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS'
2590 AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
2591 AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
2592 AND O2.ORG_INFORMATION2 = 'Y'
2593 AND O.ORGANIZATION_ID = P_OPERATING_UNIT;
2594
2595
2596
2597 RETURN DLC_VAL;
2598
2599 EXCEPTION
2600
2601 WHEN TOO_MANY_ROWS THEN
2602 -- For Bug 4616389
2603 -- ROLLBACK TO Get_DefaultLegalContext_OU;
2604 return -1;
2605
2606 WHEN FND_API.G_EXC_ERROR THEN
2607 -- ROLLBACK TO Get_DefaultLegalContext_OU;
2608 return -1;
2609
2610 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2611 -- ROLLBACK TO Get_DefaultLegalContext_OU;
2612 return -1;
2613 WHEN OTHERS THEN
2614 -- ROLLBACK TO Get_DefaultLegalContext_OU;
2615 return -1;
2616 END Get_DefaultLegalContext_OU;
2617
2618
2619 function Get_DLC_LE_OU RETURN VARCHAR2 IS
2620 l_legal_entity_id NUMBER;
2621 l_legal_entity_name VARCHAR2(1000);
2622 l_le_count NUMBER := 0;
2623 BEGIN
2624
2625
2626 SELECT COUNT(*)
2627 INTO l_le_count
2628 FROM XLE_FP_OU_LEDGER_V
2629 where OPERATING_UNIT_ID = nvl(fnd_profile.value_wnps('ORG_ID'),-99);
2630
2631 IF (l_le_count = 1) THEN
2632 SELECT legal_entity_name
2633 INTO l_legal_entity_name
2634 FROM XLE_FP_OU_LEDGER_V
2635 WHERE OPERATING_UNIT_ID = nvl(fnd_profile.value_wnps('ORG_ID'),-99);
2636 ELSE
2637 BEGIN
2638 SELECT NAME
2639 INTO l_legal_entity_name
2640 FROM XLE_FIRSTPARTY_INFORMATION_V
2641 WHERE LEGAL_ENTITY_ID = Get_DefaultLegalContext_OU(fnd_profile.value_wnps('ORG_ID'));
2642 EXCEPTION
2643 WHEN OTHERS THEN
2644 RETURN NULL;
2645 END;
2646 END IF;
2647
2648 RETURN l_legal_entity_name;
2649
2650 EXCEPTION
2651
2652 WHEN TOO_MANY_ROWS THEN
2653 return null;
2654
2655 WHEN FND_API.G_EXC_ERROR THEN
2656 return null;
2657
2658 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2659 return null;
2660 WHEN OTHERS THEN
2661 return null;
2662 END Get_DLC_LE_OU;
2663
2664 PROCEDURE IsLegalEntity_LEID(
2665 x_return_status OUT NOCOPY VARCHAR2,
2666 x_msg_data OUT NOCOPY VARCHAR2,
2667 p_legal_entity_id IN NUMBER,
2668 x_legal_entity OUT NOCOPY VARCHAR2
2669
2670 )
2671 IS
2672 l_le_flag varchar2(1);
2673
2674 BEGIN
2675
2676
2677 -- Initialize API return status to success
2678
2679 x_return_status := FND_API.G_RET_STS_SUCCESS;
2680
2681
2682 -- API body
2683
2684 if p_legal_entity_id IS NULL then
2685
2686 x_msg_data := 'Missing Mandatory parameters. Please provide Legal Entity Id';
2687 x_return_status := FND_API.G_RET_STS_ERROR;
2688 return;
2689 end if;
2690
2691 l_le_flag := 'N';
2692
2693 BEGIN
2694 SELECT 'Y'
2695 INTO l_le_flag
2696 FROM XLE_ENTITY_PROFILES
2697 WHERE legal_entity_id = p_legal_entity_id
2698 AND ( effective_to >= sysdate OR effective_to is null);
2699 EXCEPTION
2700 WHEN NO_DATA_FOUND THEN
2701 l_le_flag := 'N';
2702 END;
2703
2704 IF l_le_flag = 'Y' THEN
2705 x_legal_entity := FND_API.G_TRUE;
2706 ELSE
2707 x_legal_entity := FND_API.G_FALSE;
2708 END IF;
2709
2710
2711 -- End of API body.
2712
2713
2714 EXCEPTION
2715
2716
2717 WHEN FND_API.G_EXC_ERROR THEN
2718
2719 x_return_status := FND_API.G_RET_STS_ERROR ;
2720
2721 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2722 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2723
2724 WHEN OTHERS THEN
2725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2726
2727 END IsLegalEntity_LEID;
2728
2729
2730 PROCEDURE Check_IC_Invoice_required(
2731 x_return_status OUT NOCOPY VARCHAR2,
2732 x_msg_data OUT NOCOPY VARCHAR2,
2733 p_legal_entity_id IN NUMBER,
2734 p_party_id IN NUMBER,
2735 x_intercompany_inv OUT NOCOPY VARCHAR2)
2736 IS
2737 l_ic_inv varchar2(1);
2738 l_count number;
2739
2740 BEGIN
2741
2742
2743 -- Initialize API return status to success
2744
2745 x_return_status := FND_API.G_RET_STS_SUCCESS;
2746
2747
2748 -- API body
2749
2750 if (p_legal_entity_id IS NULL AND p_party_id IS NULL) then
2751
2752 x_msg_data := 'Missing Mandatory parameters. Please provide either the Legal Entity Id or Party Id';
2753 x_return_status := FND_API.G_RET_STS_ERROR;
2754 return;
2755 end if;
2756
2757 l_ic_inv := 'N';
2758
2759 if p_legal_entity_id IS NOT NULL then
2760 BEGIN
2761
2762 SELECT
2763 count(reg_func.function_code)
2764 INTO l_count
2765 FROM
2766 xle_reg_functions reg_func,
2767 xle_registrations reg,
2768 xle_lookups lkp
2769 WHERE
2770 lkp.lookup_type = 'XLE_LE_FUNCTION'
2771 AND lkp.lookup_code = reg_func.function_code
2772 AND lkp.lookup_code = 'ICINV'
2773 AND reg.source_id = p_legal_entity_id
2774 AND reg.source_table = 'XLE_ENTITY_PROFILES'
2775 AND reg.registration_id = reg_func.registration_id;
2776
2777 if l_count >= 1 then
2778 l_ic_inv := 'Y';
2779 end if;
2780
2781
2782 EXCEPTION
2783 WHEN OTHERS THEN
2784 l_ic_inv := 'N';
2785 END;
2786 end if;
2787
2788 if (p_party_id IS NOT NULL AND l_ic_inv = 'N') then
2789 BEGIN
2790 SELECT
2791 count(reg_func.function_code)
2792 INTO l_count
2793 FROM
2794 xle_reg_functions reg_func,
2795 xle_registrations reg,
2796 xle_lookups lkp ,
2797 xle_entity_profiles ent_prof
2798 WHERE
2799 lkp.lookup_type = 'XLE_LE_FUNCTION'
2800 AND lkp.lookup_code = reg_func.function_code
2801 AND lkp.lookup_code = 'ICINV'
2802 AND reg.registration_id = reg_func.registration_id
2803 AND reg.source_id = ent_prof.legal_entity_id
2804 AND reg.source_table = 'XLE_ENTITY_PROFILES'
2805 AND ent_prof.party_id = p_party_id;
2806
2807 if l_count >= 1 then
2808 l_ic_inv := 'Y';
2809 end if;
2810
2811 EXCEPTION
2812 WHEN OTHERS THEN
2813 l_ic_inv := 'N';
2814
2815 END;
2816 end if;
2817
2818 if l_ic_inv = 'Y' THEN
2819 x_intercompany_inv := FND_API.G_TRUE;
2820 else
2821 x_intercompany_inv := FND_API.G_FALSE;
2822 end if;
2823 -- End of API body.
2824
2825
2826 EXCEPTION
2827 WHEN FND_API.G_EXC_ERROR THEN
2828 x_return_status := FND_API.G_RET_STS_ERROR ;
2829
2830 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2832
2833 WHEN OTHERS THEN
2834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2835
2836 END Check_IC_Invoice_required;
2837 END XLE_UTILITIES_GRP;