1 PACKAGE BODY XLE_BUSINESSINFO_GRP AS
2 /* $Header: xlegbuib.pls 120.40 2012/01/27 18:44:05 jmary ship $ */
3
4
5
6 PROCEDURE Get_BusinessGroup_Info(
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_msg_count OUT NOCOPY NUMBER,
9 x_msg_data OUT NOCOPY VARCHAR2,
10 P_LegalEntity_ID IN XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE,
11 P_party_id IN XLE_ENTITY_PROFILES.party_id%TYPE,
12 p_businessgroup_id IN hr_operating_units.business_group_id%type,
13 x_businessgroup_info OUT NOCOPY BG_LE_Tbl_Type
14 )
15 IS
16
17
18 l_index number := 1;
19 l_legal_entity_id number;
20 l_party_id number;
21 l_business_group_id number;
22
23 l_row_count NUMBER := 0;
24
25
26 /* The following cursor selects legal entity information related to a
27 business group*/
28 cursor business_c is
29 select xlep.legal_entity_id,
30 xlep.party_id
31 from hr_legal_entities hrle,
32 xle_entity_profiles xlep
33 where business_group_id = l_business_group_id
34 and xlep.legal_entity_id = hrle.organization_id;
35
36 BEGIN
37 l_legal_entity_id := p_legalentity_id;
38
39
40 /* Missing mandatory parameters.
41 Business Group ID/ (Legal Entity ID or party ID) is mandatory */
42
43 IF p_party_ID is null and
44 p_legalentity_ID is null and p_businessgroup_id is null then
45 x_msg_data := 'Missing Mandatory Parameters.';
46 return;
47 End if;
48
49
50 /* Either Business Group information or Legal Entity Information has to be
51 passed. Information about both is not accepted. */
52
53 IF p_businessgroup_id is not null and
54 (p_party_ID is not null OR p_legalentity_ID is not null) then
55 x_msg_data := 'Enter just one of the listed parameters:
56 Business Group ID, Legal Entity ID or Party ID of the Legal Entity. ';
57 return;
58
59 End if;
60
61 /* Check for Valid combination of Legal Entity ID and Party ID */
62
63 IF p_party_ID is not null then
64
65 /* If Legal Entity ID is also passed, check if the party ID and the
66 Legal Entity ID match. Else return with error message; */
67 BEGIN
68 select legal_entity_id
69 into l_legal_entity_id
70 from xle_entity_profiles
71 where party_id = p_party_id;
72
73 EXCEPTION
74 when no_data_found then
75 x_msg_data := 'Invalid Party ID : ' || p_party_id;
76 return;
77 END;
78
79 if p_legalEntity_ID is not null then
80 if p_legalEntity_ID <> l_legal_entity_id then
81 x_msg_data := 'Invalid Legal Entity ID and Party ID combination.';
82 return;
83 end if;
84 end if;
85
86 End if;
87
88 If l_legal_entity_id is not null then
89
90 BEGIN
91 select party_id
92 into l_party_id
93 from xle_entity_profiles
94 where legal_entity_id = l_legal_entity_id;
95
96 EXCEPTION
97 when no_data_found then
98 x_msg_data := 'Invalid Legal Entity ID : ' || l_legal_entity_id;
99 return;
100 END;
101
102 if p_party_ID is not null then
103 if p_party_ID <> l_party_id then
104 x_msg_data := 'Legal Entity ID and Party ID do not match.';
105 return;
106 end if;
107
108 end if;
109
110 /* Select business group information into placeholder variables
111 for the given Legal Entity information */
112 BEGIN
113 select business_group_id
114 into l_business_group_id
115 from hr_legal_entities
116 where organization_id = l_legal_entity_id;
117
118 EXCEPTION
119 when no_data_found then
120 x_msg_data := 'Invalid Legal Entity : Not associated to a Business Group.';
121 return;
122 END;
123
124 /* Populate the output table with the Business group and Legal Entity
125 information */
126
127 x_businessgroup_info(1).legal_entity_id := l_legal_entity_id;
128 x_businessgroup_info(1).party_id := l_party_id;
129 x_businessgroup_info(1).business_group_id := l_business_group_id;
130
131 else
132
133 /* If business group information is passed, invoke the cursor
134 business_c to obtain Legal Entity information associated with the
135 Business Group */
136
137 l_business_group_id := p_businessgroup_id;
138
139 for business_r in business_c loop
140
141 /* Populate the output table with the Business group and Legal
142 Entity information */
143
144 l_row_count := l_row_count + 1;
145
146 x_businessgroup_info(l_index).legal_entity_id := business_r.legal_entity_id;
147 x_businessgroup_info(l_index).party_id := business_r.party_id;
148 x_businessgroup_info(l_index).business_group_id := l_business_group_id;
149
150 l_index := l_index + 1;
151 end loop;
152
153
154
155 if l_row_count = 0 then
156 x_msg_data := 'Invalid legal entity ID.';
157 x_return_status :='S';
158 else
159 x_return_status :='E';
160 end if;
161
162
163 end if;
164
165 END Get_BusinessGroup_Info;
166
167
168 PROCEDURE Get_Ledger_Info(
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_data OUT NOCOPY VARCHAR2,
171 P_Ledger_ID IN NUMBER,
172 x_Ledger_info OUT NOCOPY LE_Ledger_Rec_Type
173 )
174 IS
175
176 l_party_id number;
177 l_le_flag boolean := false;
178 l_le_exists_flag boolean := false;
179 l_legal_entity_id number;
180
181 l_ledger_flag boolean := false;
182 l_bsv_flag boolean := false;
183 l_bsv_return_flag boolean := false;
184
185
186 x_allow_all_bsv_flag varchar2(1);
187
188 l_index number := 1;
189
190 x_bsv_list gl_mc_info.le_bsv_tbl_type := gl_mc_info.le_bsv_tbl_type();
191 x_le_list gl_mc_info.le_bsv_tbl_type := gl_mc_info.le_bsv_tbl_type();
192
193 l_legalentity_info XLE_UTILITIES_GRP.LegalEntity_Rec;
194 l_msg_data VARCHAR2(1000);
195 l_msg_count number;
196 l_return_status varchar2(10);
197
198 BEGIN
199
200 /* Ledger ID is mandatory information */
201
202 if P_Ledger_ID is null then
203 x_msg_data := 'Please pass a value for Ledger ID.';
204 x_return_status := 'E';
205 return;
206 end if;
207
208 l_le_flag := gl_mc_info.get_legal_entities(
209 p_ledger_id,x_le_list);
210
211
212 if x_le_list.count > 0 then
213 for i in x_le_list.first..x_le_list.last loop
214
215 x_bsv_list := gl_mc_info.le_bsv_tbl_type();
216 /* Invoke GL API get_legal_entities to get the legal entities
217 associated with the given ledger. The output legal entities are returned in x_le_list.*/
218
219 l_bsv_flag := gl_mc_info.get_bal_seg_values(
220 p_ledger_id,
221 x_le_list(i).legal_entity_id,
222 null,
223 x_allow_all_bsv_flag,
224 x_bsv_list);
225
226
227 if x_bsv_list.count > 0 then
228
229 /* The following loop loops through the balancing segment values associated
230 with the legal entities in x_le_list */
231 for j in x_bsv_list.first..x_bsv_list.last loop
232
233
234 x_Ledger_info(l_index).legal_entity_id := x_le_list(i).legal_entity_id;
235
236
237 x_ledger_info(l_index).ledger_id := p_ledger_id;
238 x_ledger_info(l_index).bal_seg_value := x_bsv_list(j).bal_seg_value;
239
240
241
242 xle_utilities_grp.get_legalentity_info(
243 x_return_status => l_return_status,
244 x_msg_count => l_msg_count,
245 x_msg_data => l_msg_data,
246 p_party_id => null,
247 p_legalentity_id => x_le_list(i).legal_entity_id,
248 x_legalentity_info => l_legalentity_info);
249
250
251 /* Populate the output table with the Ledger, BSV information */
252
253 x_Ledger_info(l_index).party_id := l_legalentity_info.party_id;
254 x_Ledger_info(l_index).name := l_legalentity_info.name;
255 x_Ledger_info(l_index).LEGAL_ENTITY_IDENTIFIER := l_legalentity_info.LEGAL_ENTITY_IDENTIFIER;
256 x_Ledger_info(l_index).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
257 x_Ledger_info(l_index).ACTIVITY_CODE := l_legalentity_info.ACTIVITY_CODE;
258 x_Ledger_info(l_index).sub_activity_code := l_legalentity_info.sub_activity_code;
259 x_Ledger_info(l_index).type_of_company := l_legalentity_info.type_of_company;
260 x_Ledger_info(l_index).LE_EFFECTIVE_FROM := l_legalentity_info.LE_EFFECTIVE_FROM;
261 x_Ledger_info(l_index).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
262 x_Ledger_info(l_index).REGISTRATION_NUMBER := l_legalentity_info.REGISTRATION_NUMBER;
263 x_Ledger_info(l_index).LEGISLATIVE_CATEGORY := l_legalentity_info.LEGISLATIVE_CATEGORY;
264 x_Ledger_info(l_index).EFFECTIVE_FROM := l_legalentity_info.EFFECTIVE_FROM;
265 x_Ledger_info(l_index).EFFECTIVE_TO := l_legalentity_info.EFFECTIVE_TO;
266 x_Ledger_info(l_index).ADDRESS_LINE_1 := l_legalentity_info.ADDRESS_LINE_1;
267 x_Ledger_info(l_index).ADDRESS_LINE_2 := l_legalentity_info.ADDRESS_LINE_2;
268 x_Ledger_info(l_index).ADDRESS_LINE_3 := l_legalentity_info.ADDRESS_LINE_3;
269 x_Ledger_info(l_index).TOWN_OR_CITY := l_legalentity_info.TOWN_OR_CITY;
270 x_Ledger_info(l_index).REGION_1 := l_legalentity_info.REGION_1;
271 x_Ledger_info(l_index).REGION_2 := l_legalentity_info.REGION_2;
272 x_Ledger_info(l_index).REGION_3 := l_legalentity_info.REGION_3;
273 x_Ledger_info(l_index).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
274 x_Ledger_info(l_index).COUNTRY := l_legalentity_info.COUNTRY;
275
276 l_index := l_index + 1;
277
278 end loop;
279
280 else
281
282
283 x_Ledger_info(l_index).legal_entity_id := x_le_list(i).legal_entity_id;
284
285 /* Invoke XLE API Get_LegalEntity_Info to retrieve legale entity information */
286
287 xle_utilities_grp.get_legalentity_info(
288 x_return_status => l_return_status,
289 x_msg_count => l_msg_count,
290 x_msg_data => l_msg_data,
291 p_party_id => null,
292 p_legalentity_id => x_le_list(i).legal_entity_id,
293 x_legalentity_info => l_legalentity_info);
294
295
296
297 x_Ledger_info(l_index).party_id := l_legalentity_info.party_id;
298 x_Ledger_info(l_index).name := l_legalentity_info.name;
299 x_Ledger_info(l_index).LEGAL_ENTITY_IDENTIFIER := l_legalentity_info.LEGAL_ENTITY_IDENTIFIER;
300 x_Ledger_info(l_index).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
301 x_Ledger_info(l_index).ACTIVITY_CODE := l_legalentity_info.ACTIVITY_CODE;
302 x_Ledger_info(l_index).sub_activity_code := l_legalentity_info.sub_activity_code;
303 x_Ledger_info(l_index).type_of_company := l_legalentity_info.type_of_company;
304 x_Ledger_info(l_index).LE_EFFECTIVE_FROM := l_legalentity_info.LE_EFFECTIVE_FROM;
305 x_Ledger_info(l_index).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
306 x_Ledger_info(l_index).REGISTRATION_NUMBER := l_legalentity_info.REGISTRATION_NUMBER;
307 x_Ledger_info(l_index).LEGISLATIVE_CATEGORY := l_legalentity_info.LEGISLATIVE_CATEGORY;
308 x_Ledger_info(l_index).EFFECTIVE_FROM := l_legalentity_info.EFFECTIVE_FROM;
309 x_Ledger_info(l_index).EFFECTIVE_TO := l_legalentity_info.EFFECTIVE_TO;
310 x_Ledger_info(l_index).ADDRESS_LINE_1 := l_legalentity_info.ADDRESS_LINE_1;
311 x_Ledger_info(l_index).ADDRESS_LINE_2 := l_legalentity_info.ADDRESS_LINE_2;
312 x_Ledger_info(l_index).ADDRESS_LINE_3 := l_legalentity_info.ADDRESS_LINE_3;
313 x_Ledger_info(l_index).TOWN_OR_CITY := l_legalentity_info.TOWN_OR_CITY;
314 x_Ledger_info(l_index).REGION_1 := l_legalentity_info.REGION_1;
315 x_Ledger_info(l_index).REGION_2 := l_legalentity_info.REGION_2;
316 x_Ledger_info(l_index).REGION_3 := l_legalentity_info.REGION_3;
317 x_Ledger_info(l_index).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
318 x_Ledger_info(l_index).COUNTRY := l_legalentity_info.COUNTRY;
319
320
321 x_ledger_info(l_index).ledger_id := p_ledger_id;
322
323 x_msg_data := 'The Legal Entity: ' || x_le_list(i).legal_entity_id || ' is not associated with a BSV.';
324
325 l_index := l_index + 1;
326 return;
327 end if;
328
329
330 end loop;
331 else
332 x_msg_data := 'Either the Ledger ID is invalid or the Ledger is not associated with any Legal Entities.';
333
334 end if;
335
336
337 END Get_Ledger_Info;
338
339
340 PROCEDURE Get_Ledger_Info(
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_msg_data OUT NOCOPY VARCHAR2,
343 P_Ledger_ID IN NUMBER,
344 P_BSV IN Varchar2,
345 x_Ledger_info OUT NOCOPY LE_Ledger_Rec_Type
346 ) IS
347 l_party_id number;
348 l_le_flag boolean := false;
349 l_le_return_flag boolean := false;
350
351 l_index number := 1;
352
353
354 l_legalentity_info XLE_UTILITIES_GRP.LegalEntity_Rec;
355 l_msg_data VARCHAR2(1000);
356 l_msg_count number;
357 l_return_status varchar2(10);
358
359 x_le_list gl_mc_info.le_bsv_tbl_type := gl_mc_info.le_bsv_tbl_type();
360 BEGIN
361
362
363 /* Parameters Ledger ID and BSV are mandatory */
364
365 IF p_ledger_id is null OR P_BSV is null then
366 x_msg_data := 'Please pass values for Ledger ID and BSV.';
367 return;
368 end if;
369
370
371
372 /* Invoke the GL API get_legal_entities to retrieve the legal entities associated with the
373 Ledger and BSV combination. The legal entity is returned in x_le_list*/
374
375 l_le_flag := gl_mc_info.get_legal_entities(
376 p_ledger_id,P_BSV,null,x_le_list);
377
378
379 if x_le_list.count > 0 then
380
381 /* The following loop loops through the list of legal entities returned in previous step */
382 for k in x_le_list.first..x_le_list.last loop
383
384 l_le_return_flag := true;
385
386
387
388
389 x_Ledger_info(l_index).legal_entity_id := x_le_list(k).legal_entity_id;
390 x_ledger_info(l_index).ledger_id := p_ledger_id;
391 x_ledger_info(l_index).bal_seg_value := P_BSV;
392
393 /* Invoke XLE API Get_LegalEntity_Info to retrieve legale entity information */
394 xle_utilities_grp.get_legalentity_info(
395 x_return_status => l_return_status,
396 x_msg_count => l_msg_count,
397 x_msg_data => l_msg_data,
398 p_party_id => null,
399 p_legalentity_id => x_le_list(k).legal_entity_id,
400 x_legalentity_info => l_legalentity_info);
401
402
403 /* Assign ledeger and legal entity information to output table */
404
405 x_Ledger_info(l_index).party_id := l_legalentity_info.party_id;
406 x_Ledger_info(l_index).name := l_legalentity_info.name;
407 x_Ledger_info(l_index).LEGAL_ENTITY_IDENTIFIER := l_legalentity_info.LEGAL_ENTITY_IDENTIFIER;
408 x_Ledger_info(l_index).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
409 x_Ledger_info(l_index).ACTIVITY_CODE := l_legalentity_info.ACTIVITY_CODE;
410 x_Ledger_info(l_index).sub_activity_code := l_legalentity_info.sub_activity_code;
411 x_Ledger_info(l_index).type_of_company := l_legalentity_info.type_of_company;
412 x_Ledger_info(l_index).LE_EFFECTIVE_FROM := l_legalentity_info.LE_EFFECTIVE_FROM;
413 x_Ledger_info(l_index).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
414 x_Ledger_info(l_index).REGISTRATION_NUMBER := l_legalentity_info.REGISTRATION_NUMBER;
415 x_Ledger_info(l_index).LEGISLATIVE_CATEGORY := l_legalentity_info.LEGISLATIVE_CATEGORY;
416 x_Ledger_info(l_index).EFFECTIVE_FROM := l_legalentity_info.EFFECTIVE_FROM;
417 x_Ledger_info(l_index).EFFECTIVE_TO := l_legalentity_info.EFFECTIVE_TO;
418 x_Ledger_info(l_index).ADDRESS_LINE_1 := l_legalentity_info.ADDRESS_LINE_1;
419 x_Ledger_info(l_index).ADDRESS_LINE_2 := l_legalentity_info.ADDRESS_LINE_2;
420 x_Ledger_info(l_index).ADDRESS_LINE_3 := l_legalentity_info.ADDRESS_LINE_3;
421 x_Ledger_info(l_index).TOWN_OR_CITY := l_legalentity_info.TOWN_OR_CITY;
422 x_Ledger_info(l_index).REGION_1 := l_legalentity_info.REGION_1;
423 x_Ledger_info(l_index).REGION_2 := l_legalentity_info.REGION_2;
424 x_Ledger_info(l_index).REGION_3 := l_legalentity_info.REGION_3;
425 x_Ledger_info(l_index).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
426 x_Ledger_info(l_index).COUNTRY := l_legalentity_info.COUNTRY;
427
428
429
430 l_index := l_index + 1;
431 end loop;
432 else
433
434 x_msg_data := 'The Ledger ID and BSV are not associated with any Legal Entities';
435 return;
436 end if;
437 END Get_Ledger_Info;
438
439
440
441 /*
442 PROCEDURE Get_Ledger_Info(
443 x_return_status OUT VARCHAR2,
444 x_msg_data OUT VARCHAR2,
445 p_party_id IN NUMBER,
446 p_LegalEntity_ID IN Number,
447 x_Ledger_info OUT LE_Ledger_Rec_Type
448 )IS
449
450 l_legal_entity_id number;
451 l_party_id number;
452
453 l_ledger_flag boolean := false;
454 l_ledger_return_flag boolean := false;
455 l_bsv_flag boolean := false;
456 l_bsv_return_flag boolean := false;
457
458
459 x_allow_all_bsv_flag varchar2(1) := 'N';
460
461 l_index number := 1;
462
463 x_ledger_list gl_mc_info.ledger_tbl_type := gl_mc_info.ledger_tbl_type();
464 x_bsv_list gl_mc_info.le_bsv_tbl_type := gl_mc_info.le_bsv_tbl_type();
465
466
467
468 BEGIN
469
470 l_legal_entity_id := p_LegalEntity_ID;
471
472 if p_legalEntity_ID is not null then
473
474
475 begin
476 select party_id
477 into l_party_id
478 from xle_entity_profiles
479 where legal_entity_id = p_LegalEntity_ID;
480 exception
481 when no_data_found then
482 x_msg_data := 'Legal Entity ID is invalid';
483 return;
484 end;
485
486 if p_party_ID is not null then
487 if p_party_ID <> l_party_id then
488 x_msg_data := 'Legal Entity ID and Party ID do not match.';
489 return;
490 end if;
491 end if;
492 elsif p_party_ID is not null then
493
494 BEGIN
495 select legal_entity_id
496 into l_legal_entity_id
497 from xle_entity_profiles
498 where party_id = p_party_id;
499
500 exception
501 when no_data_found then
502 x_msg_data := 'Party ID is invalid';
503 return;
504 end;
505
506 if p_legalEntity_ID is not null then
507 if p_legalEntity_ID <> l_legal_entity_id then
508 x_msg_data := 'Legal Entity ID and Party ID do not match.';
509 return;
510 end if;
511 end if;
512 end if;
513
514
515
516 l_ledger_flag := gl_mc_info.get_le_ledgers(
517 p_LegalEntity_ID,
518 'Y',
519 'Y',
520 null,
521 x_ledger_list);
522
523
524
525 for i in x_ledger_list.first..x_ledger_list.last loop
526
527 l_ledger_return_flag := true;
528
529 l_bsv_flag := gl_mc_info.get_bal_seg_values(
530 x_ledger_list(i).ledger_id,
531 p_LegalEntity_ID,
532 null,
533 x_allow_all_bsv_flag,
534 x_bsv_list);
535
536
537
538 for j in x_bsv_list.first..x_bsv_list.last loop
539 l_bsv_return_flag := true;
540
541 x_Ledger_info(l_index).legal_entity_id := l_legal_entity_id;
542
543 x_ledger_info(l_index).ledger_id := x_ledger_list(i).ledger_id;
544 x_ledger_info(l_index).bal_seg_value := x_bsv_list(j).bal_seg_value;
545
546
547 xle_utilities_grp.get_legalentity_info(
548 x_return_status => l_return_status,
549 x_msg_count => l_msg_count,
550 x_msg_data => l_msg_data,
551 p_party_id => null,
552 p_legalentity_id => x_le_list(i).legal_entity_id,
553 x_legalentity_info => l_legalentity_info);
554
555
556
557 x_Ledger_info(l_index).party_id := l_legalentity_info.party_id;
558 x_Ledger_info(l_index).name := l_legalentity_info.name;
559 x_Ledger_info(l_index).LEGAL_ENTITY_IDENTIFIER := l_legalentity_info.LEGAL_ENTITY_IDENTIFIER;
560 x_Ledger_info(l_index).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
561 x_Ledger_info(l_index).ACTIVITY_CODE := l_legalentity_info.ACTIVITY_CODE;
562 x_Ledger_info(l_index).sub_activity_code := l_legalentity_info.sub_activity_code;
563 x_Ledger_info(l_index).type_of_company := l_legalentity_info.type_of_company;
564 x_Ledger_info(l_index).LE_EFFECTIVE_FROM := l_legalentity_info.LE_EFFECTIVE_FROM;
565 x_Ledger_info(l_index).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
566 x_Ledger_info(l_index).REGISTRATION_NUMBER := l_legalentity_info.REGISTRATION_NUMBER;
567 x_Ledger_info(l_index).LEGISLATIVE_CATEGORY := l_legalentity_info.LEGISLATIVE_CATEGORY;
568 x_Ledger_info(l_index).EFFECTIVE_FROM := l_legalentity_info.EFFECTIVE_FROM;
569 x_Ledger_info(l_index).EFFECTIVE_TO := l_legalentity_info.EFFECTIVE_TO;
570 x_Ledger_info(l_index).ADDRESS_LINE_1 := l_legalentity_info.ADDRESS_LINE_1;
571 x_Ledger_info(l_index).ADDRESS_LINE_2 := l_legalentity_info.ADDRESS_LINE_2;
572 x_Ledger_info(l_index).ADDRESS_LINE_3 := l_legalentity_info.ADDRESS_LINE_3;
573 x_Ledger_info(l_index).TOWN_OR_CITY := l_legalentity_info.TOWN_OR_CITY;
574 x_Ledger_info(l_index).REGION_1 := l_legalentity_info.REGION_1;
575 x_Ledger_info(l_index).REGION_2 := l_legalentity_info.REGION_2;
576 x_Ledger_info(l_index).REGION_3 := l_legalentity_info.REGION_3;
577 x_Ledger_info(l_index).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
578 x_Ledger_info(l_index).COUNTRY := l_legalentity_info.COUNTRY;
579
580
581
582 l_index := l_index + 1;
583
584 end loop;
585
586 if l_bsv_return_flag = false then
587 x_Ledger_info(l_index).legal_entity_id := l_legal_entity_id;
588 x_Ledger_info(l_index).party_id := l_party_id;
589 x_ledger_info(l_index).ledger_id := x_ledger_list(i).ledger_id;
590
591 l_index := l_index + 1;
592 return;
593 end if;
594
595
596
597 end loop;
598
599 if l_ledger_return_flag = false then
600 x_msg_data := 'Either the Legal Entity ID is invalid or it is not associated with a Ledger.';
601 return;
602 end if;
603
604
605
606 END Get_Ledger_Info;
607 */
608
609
610 PROCEDURE Get_OperatingUnit_Info(
611 x_return_status OUT NOCOPY VARCHAR2,
612 x_msg_data OUT NOCOPY VARCHAR2,
613 p_operating_unit IN NUMBER,
614 p_legal_entity_id IN NUMBER,
615 p_party_id IN NUMBER,
616 x_ou_le_info OUT NOCOPY OU_LE_Tbl_Type
617 )
618 IS
619
620 l_le_rowcount_flag boolean := false;
621 l_ou_rowcount_flag boolean := false;
622 l_ledger_flag boolean;
623 l_le_flag boolean;
624 l_ledger_id gl_ledgers.ledger_id%type;
625 l_str_ledger_id varchar2(150);
626 l_return_status varchar2(1);
627 l_msg_data varchar2(1000);
628
629 x_ledger_info XLE_BUSINESSINFO_GRP.LE_Ledger_Rec_Type;
630 x_ledger_list GL_MC_INFO.ledger_tbl_type := GL_MC_INFO.ledger_tbl_type();
631 x_le_list gl_mc_info.le_bsv_tbl_type := gl_mc_info.le_bsv_tbl_type();
632
633 l_index number:= 1;
634 l_legal_entity_id number;
635 l_legalentity_info XLE_UTILITIES_GRP.LegalEntity_Rec;
636 l_msg_count number;
637
638
639 /* The following cursor selects operating unit information associated
640 with a Ledger */
641
642 cursor OperUnit_c is
643 SELECT o.organization_id operating_unit_id
644 FROM hr_all_organization_units o,
645 hr_organization_information o2,
646 hr_organization_information o3
647 WHERE o.organization_id = o2.organization_id
648 AND o.organization_id = o3.organization_id
649 AND o2.org_information_context || '' = 'CLASS'
650 AND o3.org_information_context = 'Operating Unit Information'
651 AND o2.org_information1 = 'OPERATING_UNIT'
652 AND o2.org_information2 = 'Y'
653 AND o3.org_information3 = l_str_ledger_id;
654
655 BEGIN
656 l_legal_entity_id := p_legal_entity_id;
657
658 IF p_operating_unit is null and p_legal_entity_id is null and
659 p_party_id is null then
660 x_msg_data := 'Missing Mandatory Parameters.';
661 return;
662 End if;
663
664 IF p_operating_unit is not null and
665 (p_party_ID is not null OR p_legal_entity_id is not null) then
666 x_msg_data := 'Enter just one of the listed parameters: Operating Unit
667 ID, Legal Entity ID or Party ID of the Legal Entity.';
668 return;
669 End if;
670
671
672 /* If operating unit information is passed then the following if statement
673 retrieves the legal entity information associated with the Operating unit
674 through its ledger */
675
676 IF p_operating_unit is not null then
677
678 /* Select ledger associated with the Operating unit*/
679
680 SELECT DISTINCT o3.org_information3
681 INTO l_ledger_id
682 FROM hr_all_organization_units o,
683 hr_organization_information o2,
684 hr_organization_information o3
685 WHERE o.organization_id = o2.organization_id
686 AND o.organization_id = o3.organization_id
687 AND o3.organization_id = o2.organization_id
688 AND o2.org_information_context || '' = 'CLASS'
689 AND o3.org_information_context = 'Operating Unit Information'
690 AND o2.org_information1 = 'OPERATING_UNIT'
691 AND o2.org_information2 = 'Y'
692 AND o.organization_id = p_operating_unit;
693
694 /* The following API call retrieves legal entities associated with the ledger
695 identified in previous step */
696 l_le_flag := gl_mc_info.get_legal_entities(
697 l_ledger_id,x_le_list);
698
699 /* The following statement loops through the legal entities and
700 populates the output table */
701 if x_le_list.count > 0 then
702 for i in x_le_list.first..x_le_list.last loop
703 l_ou_rowcount_flag := true;
704
705 x_ou_le_info(l_index).Operating_Unit_ID := p_operating_unit;
706 x_ou_le_info(l_index).LEGAL_ENTITY_ID := x_le_list(i).legal_entity_id;
707
708 /* Invoke XLE API Get_LegalEntity_Info to retrieve legal entity
709 information */
710
711 xle_utilities_grp.get_legalentity_info(
712 x_return_status => l_return_status,
713 x_msg_count => l_msg_count,
714 x_msg_data => l_msg_data,
715 p_party_id => null,
716 p_legalentity_id => x_le_list(i).legal_entity_id,
717 x_legalentity_info => l_legalentity_info);
718
719
720 x_ou_le_info(l_index).party_id := l_legalentity_info.party_id;
721 x_ou_le_info(l_index).name := l_legalentity_info.name;
722 x_ou_le_info(l_index).LEGAL_ENTITY_IDENTIFIER := l_legalentity_info.LEGAL_ENTITY_IDENTIFIER;
723 x_ou_le_info(l_index).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
724 x_ou_le_info(l_index).ACTIVITY_CODE := l_legalentity_info.ACTIVITY_CODE;
725 x_ou_le_info(l_index).sub_activity_code := l_legalentity_info.sub_activity_code;
726 x_ou_le_info(l_index).type_of_company := l_legalentity_info.type_of_company;
727 x_ou_le_info(l_index).LE_EFFECTIVE_FROM := l_legalentity_info.LE_EFFECTIVE_FROM;
728 x_ou_le_info(l_index).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
729 x_ou_le_info(l_index).REGISTRATION_NUMBER := l_legalentity_info.REGISTRATION_NUMBER;
730 x_ou_le_info(l_index).LEGISLATIVE_CATEGORY := l_legalentity_info.LEGISLATIVE_CATEGORY;
731 x_ou_le_info(l_index).EFFECTIVE_FROM := l_legalentity_info.EFFECTIVE_FROM;
732 x_ou_le_info(l_index).EFFECTIVE_TO := l_legalentity_info.EFFECTIVE_TO;
733 x_ou_le_info(l_index).ADDRESS_LINE_1 := l_legalentity_info.ADDRESS_LINE_1;
734 x_ou_le_info(l_index).ADDRESS_LINE_2 := l_legalentity_info.ADDRESS_LINE_2;
735 x_ou_le_info(l_index).ADDRESS_LINE_3 := l_legalentity_info.ADDRESS_LINE_3;
736 x_ou_le_info(l_index).TOWN_OR_CITY := l_legalentity_info.TOWN_OR_CITY;
737 x_ou_le_info(l_index).REGION_1 := l_legalentity_info.REGION_1;
738 x_ou_le_info(l_index).REGION_2 := l_legalentity_info.REGION_2;
739 x_ou_le_info(l_index).REGION_3 := l_legalentity_info.REGION_3;
740 x_ou_le_info(l_index).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
741 x_ou_le_info(l_index).COUNTRY := l_legalentity_info.COUNTRY;
742 l_index := l_index + 1; -- bug: 7633921
743 end loop;
744 end if;
745
746 if l_ou_rowcount_flag = false then
747 x_msg_data := 'The Operating Unit is not associated with any
748 Legal Entities.';
749 return;
750 end if;
751
752 end if;
753
754
755 /* If the legal entity information is passed, the following if statement
756 retrieves the Operating unit information associated with the LE */
757
758 if p_legal_entity_id is not null or p_party_id is not null then
759 l_legal_entity_id := p_legal_entity_id;
760
761 IF p_party_ID is not null then
762
763 BEGIN
764 select legal_entity_id
765 into l_legal_entity_id
766 from xle_entity_profiles
767 where party_id = p_party_id;
768
769 EXCEPTION
770 when no_data_found then
771 x_msg_data := 'Invalid Party ID : ' || p_party_id;
772 return;
773 END;
774
775 if p_legal_Entity_ID is not null then
776 if p_legal_Entity_ID <> l_legal_entity_id then
777 x_msg_data := 'Invalid Legal Entity ID and Party ID combination.';
778 return;
779 end if;
780 end if;
781
782 End if;
783
784 if p_legal_entity_id is null then
785 select legal_entity_id
786 into l_legal_entity_id
787 from xle_entity_profiles
788 where party_id = p_party_id;
789 else
790 l_legal_entity_id := p_legal_entity_id;
791 end if;
792
793 /* Invoke API get_le_ledgers to retrieve ledger associated with
794 the LE */
795 l_ledger_flag := gl_mc_info.get_le_ledgers(
796 l_legal_entity_id,
797 'Y',
798 'Y',
799 null,
800 x_ledger_list);
801
802 /* The following statement loops through the ledger retrieved in the
803 previous step */
804 if x_ledger_list.count > 0 then
805 for j in x_ledger_list.first..x_ledger_list.last loop
806 l_le_rowcount_flag := true;
807 l_ledger_id := x_ledger_list(j).ledger_id;
808
809 select to_char(l_ledger_id)
810 into l_str_ledger_id
811 from dual;
812
813 x_ou_le_info(j).LEGAL_ENTITY_ID := l_legal_entity_id;
814
815
816 /* Invoke XLE API Get_LegalEntity_Info to retrieve legal entity
817 information */
818 xle_utilities_grp.get_legalentity_info(
819 x_return_status => l_return_status,
820 x_msg_count => l_msg_count,
821 x_msg_data => l_msg_data,
822 p_party_id => null,
823 p_legalentity_id => l_legal_entity_id,
824 x_legalentity_info => l_legalentity_info);
825
826
827 for OperUnit_r in OperUnit_c loop
828 x_ou_le_info(l_index).LEGAL_ENTITY_ID := l_legal_entity_id;
829 x_ou_le_info(l_index).operating_unit_id := OperUnit_r.operating_unit_id;
830 x_ou_le_info(l_index).party_id := l_legalentity_info.party_id;
831 x_ou_le_info(l_index).name := l_legalentity_info.name;
832 x_ou_le_info(l_index).LEGAL_ENTITY_IDENTIFIER := l_legalentity_info.LEGAL_ENTITY_IDENTIFIER;
833 x_ou_le_info(l_index).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
834 x_ou_le_info(l_index).ACTIVITY_CODE := l_legalentity_info.ACTIVITY_CODE;
835 x_ou_le_info(l_index).sub_activity_code := l_legalentity_info.sub_activity_code;
836 x_ou_le_info(l_index).type_of_company := l_legalentity_info.type_of_company;
837 x_ou_le_info(l_index).LE_EFFECTIVE_FROM := l_legalentity_info.LE_EFFECTIVE_FROM;
838 x_ou_le_info(l_index).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
839 x_ou_le_info(l_index).REGISTRATION_NUMBER := l_legalentity_info.REGISTRATION_NUMBER;
840 x_ou_le_info(l_index).LEGISLATIVE_CATEGORY := l_legalentity_info.LEGISLATIVE_CATEGORY;
841 x_ou_le_info(l_index).EFFECTIVE_FROM := l_legalentity_info.EFFECTIVE_FROM;
842 x_ou_le_info(l_index).EFFECTIVE_TO := l_legalentity_info.EFFECTIVE_TO;
843 x_ou_le_info(l_index).ADDRESS_LINE_1 := l_legalentity_info.ADDRESS_LINE_1;
844 x_ou_le_info(l_index).ADDRESS_LINE_2 := l_legalentity_info.ADDRESS_LINE_2;
845 x_ou_le_info(l_index).ADDRESS_LINE_3 := l_legalentity_info.ADDRESS_LINE_3;
846 x_ou_le_info(l_index).TOWN_OR_CITY := l_legalentity_info.TOWN_OR_CITY;
847 x_ou_le_info(l_index).REGION_1 := l_legalentity_info.REGION_1;
848 x_ou_le_info(l_index).REGION_2 := l_legalentity_info.REGION_2;
849 x_ou_le_info(l_index).REGION_3 := l_legalentity_info.REGION_3;
850 x_ou_le_info(l_index).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
851 x_ou_le_info(l_index).COUNTRY := l_legalentity_info.COUNTRY;
852
853 l_index := l_index + 1;
854 end loop;
855 end loop;
856 end if;
857
858 if l_le_rowcount_flag = false then
859 x_msg_data := 'The Legal Entity is not associated with an Operating Unit';
860 return;
861 end if;
862
863 end if; /* End if for legal entity and party id information is passed */
864
865 END Get_OperatingUnit_Info;
866
867 PROCEDURE Get_InvOrg_Info(
868 x_return_status OUT NOCOPY VARCHAR2,
869 x_msg_data OUT NOCOPY VARCHAR2,
870 P_InvOrg_ID IN NUMBER,
871 P_Le_ID IN NUMBER,
872 P_Party_ID IN NUMBER,
873 x_Inv_Le_info OUT NOCOPY inv_org_Rec_Type
874 )
875 IS
876
877 l_party_id number;
878 l_le_id number;
879 l_row_count NUMBER := 0;
880 l_inv_org_id number;
881 l_legal_entity_id number;
882
883 l_index number := 1;
884
885 /* The following cursor selects organization information related to a legal entity*/
886 cursor le_c is
887 select organization_id
888 from org_organization_definitions
889 where legal_entity=l_Le_ID;
890
891 /* The following cursor selects legal entity information related to a organization*/
892 cursor org_c is
893 SELECT DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION2), null) LEGAL_ENTITY
894 FROM HR_ORGANIZATION_UNITS HoU,
895 HR_ORGANIZATION_INFORMATION HOI1,
896 HR_ORGANIZATION_INFORMATION HOI2
897 WHERE HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
898 AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
899 AND HOI1.ORG_INFORMATION1 = 'INV'
900 AND HOI1.ORG_INFORMATION2 = 'Y'
901 AND ( HOI1.ORG_INFORMATION_CONTEXT || '') = 'CLASS'
902 AND (HOI2.org_information_context || '') = 'Accounting Information'
903 AND (hou.organization_id)= p_InvOrg_ID;
904
905
906
907 l_legalentity_info XLE_UTILITIES_GRP.LegalEntity_Rec;
908 l_msg_data VARCHAR2(1000);
909 l_msg_count number;
910 l_return_status varchar2(10);
911
912 BEGIN
913
914 l_le_id:=p_le_id;
915
916 /* Check for mandatory information */
917 IF p_party_ID is null and p_le_ID is null and p_invorg_id is null then
918 x_msg_data := 'Missing Mandatory Parameters.';
919 return;
920 end if;
921
922 /* Either inv org information or Legal Entity Information has to be passed.
923 Information about both is not accepted. */
924
925 IF p_invorg_id is not null and (p_party_ID is not null OR p_le_ID is not null) then
926 x_msg_data := 'Enter just one of the listed parameters: Inventory org ID, Legal Entity ID or Party ID of the Legal Entity. ';
927 return;
928
929 end if;
930
931
932 IF p_party_ID is not null then
933
934 /*If Legal Entity ID is also passed, check if the party ID and the Legal Entity ID match.
935 Else return with error message; */
936 BEGIN
937 select legal_entity_id
938 into l_le_id
939 from xle_entity_profiles
940 where party_id = p_party_id;
941
942 EXCEPTION
943 when no_data_found then
944 x_msg_data := 'Invalid Party ID : ' || p_party_id;
945 return;
946 END;
947
948 If p_le_ID is not null then
949 if p_le_ID <> l_le_id then
950 x_msg_data := 'Invalid Legal Entity ID and Party ID combination.';
951 return;
952 end if;
953
954 end if;
955
956 end if;
957
958 If l_le_id is not null then
959
960 BEGIN
961 select party_id
962 into l_party_id
963 from xle_entity_profiles
964 where legal_entity_id = l_le_id;
965
966
967 EXCEPTION
968 when no_data_found then
969 x_msg_data := 'Invalid Legal Entity ID : ' || l_le_id;
970 return;
971 END;
972
973 If p_party_ID is not null then
974 if p_party_ID <> l_party_id then
975 x_msg_data := 'Legal Entity ID and Party ID do not match.';
976 return;
977 end if;
978
979 end if;
980
981
982 for le_r in le_c loop
983 l_row_count := l_row_count + 1;
984
985 x_Inv_Le_info(l_index).legal_entity_id := l_le_id;
986 x_Inv_Le_info(l_index).inv_org_id := le_r.organization_id;
987
988 xle_utilities_grp.get_legalentity_info(
989 x_return_status => l_return_status,
990 x_msg_count => l_msg_count,
991 x_msg_data => l_msg_data,
992 p_party_id => null,
993 p_legalentity_id => x_Inv_Le_info(l_index).legal_entity_id,
994 x_legalentity_info => l_legalentity_info);
995
996
997 /* Populate the output table with the Legal entity and inventory org information */
998
999 x_Inv_Le_info(l_index).party_id := l_legalentity_info.party_id;
1000 x_Inv_Le_info(l_index).name := l_legalentity_info.name;
1001 x_Inv_Le_info(l_index).LEGAL_ENTITY_IDENTIFIER := l_legalentity_info.LEGAL_ENTITY_IDENTIFIER;
1002 x_Inv_Le_info(l_index).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
1003 x_Inv_Le_info(l_index).ACTIVITY_CODE := l_legalentity_info.ACTIVITY_CODE;
1004 x_Inv_Le_info(l_index).sub_activity_code := l_legalentity_info.sub_activity_code;
1005 x_Inv_Le_info(l_index).type_of_company := l_legalentity_info.type_of_company;
1006 x_Inv_Le_info(l_index).LE_EFFECTIVE_FROM := l_legalentity_info.LE_EFFECTIVE_FROM;
1007 x_Inv_Le_info(l_index).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
1008 x_Inv_Le_info(l_index).REGISTRATION_NUMBER := l_legalentity_info.REGISTRATION_NUMBER;
1009 x_Inv_Le_info(l_index).LEGISLATIVE_CATEGORY := l_legalentity_info.LEGISLATIVE_CATEGORY;
1010 x_Inv_Le_info(l_index).EFFECTIVE_FROM := l_legalentity_info.EFFECTIVE_FROM;
1011 x_Inv_Le_info(l_index).EFFECTIVE_TO := l_legalentity_info.EFFECTIVE_TO;
1012 x_Inv_Le_info(l_index).ADDRESS_LINE_1 := l_legalentity_info.ADDRESS_LINE_1;
1013 x_Inv_Le_info(l_index).ADDRESS_LINE_2 := l_legalentity_info.ADDRESS_LINE_2;
1014 x_Inv_Le_info(l_index).ADDRESS_LINE_3 := l_legalentity_info.ADDRESS_LINE_3;
1015 x_Inv_Le_info(l_index).TOWN_OR_CITY := l_legalentity_info.TOWN_OR_CITY;
1016 x_Inv_Le_info(l_index).REGION_1 := l_legalentity_info.REGION_1;
1017 x_Inv_Le_info(l_index).REGION_2 := l_legalentity_info.REGION_2;
1018 x_Inv_Le_info(l_index).REGION_3 := l_legalentity_info.REGION_3;
1019 x_Inv_Le_info(l_index).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
1020 x_Inv_Le_info(l_index).COUNTRY := l_legalentity_info.COUNTRY;
1021
1022
1023 l_index := l_index + 1;
1024 end loop;
1025
1026 if l_row_count = 0 then
1027
1028 x_msg_data := 'Invalid legal entity ID.';
1029 x_return_status :='E';
1030
1031 else
1032
1033 x_return_status :='S';
1034
1035 end if;
1036 END IF;
1037
1038
1039 IF P_InvOrg_ID is not null then
1040 l_inv_org_id:=p_invorg_id;
1041 for org_rec in org_c loop
1042 l_row_count := l_row_count + 1;
1043
1044 x_Inv_Le_info(l_index).legal_entity_id := org_rec.legal_entity;
1045 x_Inv_Le_info(l_index).inv_org_id := l_inv_org_id;
1046
1047 xle_utilities_grp.get_legalentity_info(
1048 x_return_status => l_return_status,
1049 x_msg_count => l_msg_count,
1050 x_msg_data => l_msg_data,
1051 p_party_id => null,
1052 p_legalentity_id => x_Inv_Le_info(l_index).legal_entity_id,
1053 x_legalentity_info => l_legalentity_info);
1054
1055
1056 /* Populate the output table with the Legal entity and inventory org information */
1057
1058 x_Inv_Le_info(l_index).party_id := l_legalentity_info.party_id;
1059 x_Inv_Le_info(l_index).name := l_legalentity_info.name;
1060 x_Inv_Le_info(l_index).LEGAL_ENTITY_IDENTIFIER := l_legalentity_info.LEGAL_ENTITY_IDENTIFIER;
1061 x_Inv_Le_info(l_index).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
1062 x_Inv_Le_info(l_index).ACTIVITY_CODE := l_legalentity_info.ACTIVITY_CODE;
1063 x_Inv_Le_info(l_index).sub_activity_code := l_legalentity_info.sub_activity_code;
1064 x_Inv_Le_info(l_index).type_of_company := l_legalentity_info.type_of_company;
1065 x_Inv_Le_info(l_index).LE_EFFECTIVE_FROM := l_legalentity_info.LE_EFFECTIVE_FROM;
1066 x_Inv_Le_info(l_index).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
1067 x_Inv_Le_info(l_index).REGISTRATION_NUMBER := l_legalentity_info.REGISTRATION_NUMBER;
1068 x_Inv_Le_info(l_index).LEGISLATIVE_CATEGORY := l_legalentity_info.LEGISLATIVE_CATEGORY;
1069 x_Inv_Le_info(l_index).EFFECTIVE_FROM := l_legalentity_info.EFFECTIVE_FROM;
1070 x_Inv_Le_info(l_index).EFFECTIVE_TO := l_legalentity_info.EFFECTIVE_TO;
1071 x_Inv_Le_info(l_index).ADDRESS_LINE_1 := l_legalentity_info.ADDRESS_LINE_1;
1072 x_Inv_Le_info(l_index).ADDRESS_LINE_2 := l_legalentity_info.ADDRESS_LINE_2;
1073 x_Inv_Le_info(l_index).ADDRESS_LINE_3 := l_legalentity_info.ADDRESS_LINE_3;
1074 x_Inv_Le_info(l_index).TOWN_OR_CITY := l_legalentity_info.TOWN_OR_CITY;
1075 x_Inv_Le_info(l_index).REGION_1 := l_legalentity_info.REGION_1;
1076 x_Inv_Le_info(l_index).REGION_2 := l_legalentity_info.REGION_2;
1077 x_Inv_Le_info(l_index).REGION_3 := l_legalentity_info.REGION_3;
1078 x_Inv_Le_info(l_index).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
1079 x_Inv_Le_info(l_index).COUNTRY := l_legalentity_info.COUNTRY;
1080
1081
1082 l_index := l_index + 1;
1083 end loop;
1084 if l_row_count = 0 then
1085 x_msg_data := 'Invalid Inventory org ID.';
1086 x_return_status :='E';
1087 else
1088 x_return_status :='S';
1089
1090 end if;
1091 end if;
1092 exception
1093 when others then
1094 -- null;
1095 raise;
1096
1097 END Get_invorg_Info;
1098
1099
1100 FUNCTION Get_Le_Id_Mfg(p_operating_unit IN NUMBER,
1101 p_transaction_type IN NUMBER,
1102 p_customer_account IN NUMBER)
1103 RETURN NUMBER IS
1104 BEGIN
1105 RETURN NULL;
1106 END Get_Le_Id_Mfg;
1107
1108
1109 PROCEDURE Get_CCID_Info(
1110 x_return_status OUT NOCOPY VARCHAR2,
1111 x_msg_data OUT NOCOPY VARCHAR2,
1112 P_operating_unit_ID IN NUMBER,
1113 P_code_combination_id IN Number,
1114 x_ccid_le_info OUT NOCOPY XLE_BUSINESSINFO_GRP.ccid_le_Rec_Type
1115 ) IS
1116 l_party_id number;
1117 l_le_flag boolean := false;
1118 l_le_return_flag boolean := false;
1119
1120 l_index number := 1;
1121
1122
1123 l_ledger_info XLE_BUSINESSINFO_GRP.LE_Ledger_Rec_Type;
1124 l_msg_data VARCHAR2(1000);
1125 l_msg_count number;
1126 l_return_status varchar2(10);
1127 l_segment VARCHAR2(500);
1128 l_sel_column varchar2(10);
1129 l_ledger_id Number;
1130 l_le_id Number;
1131 l_legalentity_info XLE_UTILITIES_GRP.LegalEntity_Rec;
1132
1133 x_le_list gl_mc_info.le_bsv_tbl_type := gl_mc_info.le_bsv_tbl_type();
1134 BEGIN
1135
1136
1137 /* Check for mandatory information */
1138 IF P_operating_unit_ID is null and P_code_combination_id is null then
1139 x_msg_data := 'Missing Mandatory Parameters.';
1140 return;
1141 end if;
1142
1143
1144 /* Parameters code combination ID and operating unit id are mandatory */
1145
1146 IF ( P_operating_unit_ID is not null and P_code_combination_id is null)
1147 or ( P_operating_unit_ID is null and P_code_combination_id is not null) then
1148 x_msg_data := 'Please pass values for operating unit ID and code combination id';
1149 return;
1150 end if;
1151 IF P_operating_unit_ID is not null and P_code_combination_id is not null then
1152 begin
1153
1154 SELECT o3.org_information3
1155 INTO l_ledger_id
1156 FROM hr_all_organization_units o,
1157 hr_organization_information o2,
1158 hr_organization_information o3
1159 WHERE o.organization_id = o2.organization_id
1160 AND o.organization_id = o3.organization_id
1161 AND o3.organization_id = o2.organization_id
1162 AND o2.org_information_context || '' = 'CLASS'
1163 AND o3.org_information_context = 'Operating Unit Information'
1164 AND o2.org_information1 = 'OPERATING_UNIT'
1165 AND o2.org_information2 = 'Y'
1166 AND o.organization_id = p_operating_unit_id;
1167
1168 Exception
1169 When no_data_found then
1170 return;
1171 When others then
1172 Return;
1173
1174 End;
1175 End if;
1176
1177 IF ( l_ledger_id is not null) and P_code_combination_id is not null then
1178
1179 Begin
1180
1181 SELECT application_column_name
1182 INTO l_segment
1183 FROM fnd_segment_attribute_values ,
1184 gl_ledgers sob
1185 WHERE id_flex_code = 'GL#'
1186 AND attribute_value = 'Y'
1187 AND segment_attribute_type = 'GL_BALANCING'
1188 AND application_id = 101
1189 AND sob.chart_of_accounts_id = id_flex_num
1190 AND sob.ledger_id = l_ledger_id ;
1191
1192
1193 EXECUTE IMMEDIATE ' SELECT '|| l_segment ||' FROM gl_code_combinations
1194 WHERE code_combination_id ='|| P_code_combination_id INTO l_sel_column;
1195
1196 Exception
1197
1198 When no_data_found then
1199 return;
1200 When others then
1201 Return;
1202
1203 End;
1204
1205 End if;
1206
1207
1208
1209 /* Invoke XLE API Get_ledger_Info to retrieve ledger info information */
1210 XLE_BUSINESSINFO_GRP.get_ledger_info(
1211 x_return_status => l_return_status,
1212 x_msg_data => l_msg_data,
1213 p_ledger_id => l_ledger_id,
1214 p_bsv=>l_sel_column,
1215 x_ledger_info => l_ledger_info);
1216
1217
1218 if l_ledger_info.count > 0 then
1219
1220 /* The following loop loops through the list of ledgers returned in previous step */
1221 for k in l_ledger_info.first..l_ledger_info.last loop
1222
1223
1224 /* Assign ledeger and legal entity information to output table */
1225
1226 x_ccid_le_info(l_index).ledger_id := l_ledger_id;
1227 x_ccid_le_info(l_index).ccid := p_code_combination_id;
1228 x_ccid_le_info(l_index).legal_entity_id := l_ledger_info(l_index).legal_entity_id;
1229 x_ccid_le_info(l_index).name := l_ledger_info(l_index).name;
1230 x_ccid_le_info(l_index).party_id := l_ledger_info(l_index).party_id;
1231 x_ccid_le_info(l_index).LEGAL_ENTITY_IDENTIFIER := l_ledger_info(l_index).LEGAL_ENTITY_IDENTIFIER;
1232 x_ccid_le_info(l_index).TRANSACTING_ENTITY_FLAG := l_ledger_info(l_index).TRANSACTING_ENTITY_FLAG;
1233 x_ccid_le_info(l_index).ACTIVITY_CODE := l_ledger_info(l_index).ACTIVITY_CODE;
1234 x_ccid_le_info(l_index).sub_activity_code := l_ledger_info(l_index).sub_activity_code;
1235 x_ccid_le_info(l_index).type_of_company := l_ledger_info(l_index).type_of_company;
1236 x_ccid_le_info(l_index).LE_EFFECTIVE_FROM := l_ledger_info(l_index).LE_EFFECTIVE_FROM;
1237 x_ccid_le_info(l_index).LE_EFFECTIVE_TO := l_ledger_info(l_index).LE_EFFECTIVE_TO;
1238 x_ccid_le_info(l_index).REGISTRATION_NUMBER := l_ledger_info(l_index).REGISTRATION_NUMBER;
1239 x_ccid_le_info(l_index).LEGISLATIVE_CATEGORY := l_ledger_info(l_index).LEGISLATIVE_CATEGORY;
1240 x_ccid_le_info(l_index).EFFECTIVE_FROM := l_ledger_info(l_index).EFFECTIVE_FROM;
1241 x_ccid_le_info(l_index).EFFECTIVE_TO := l_ledger_info(l_index).EFFECTIVE_TO;
1242 x_ccid_le_info(l_index).ADDRESS_LINE_1 := l_ledger_info(l_index).ADDRESS_LINE_1;
1243 x_ccid_le_info(l_index).ADDRESS_LINE_2 := l_ledger_info(l_index).ADDRESS_LINE_2;
1244 x_ccid_le_info(l_index).ADDRESS_LINE_3 := l_ledger_info(l_index).ADDRESS_LINE_3;
1245 x_ccid_le_info(l_index).TOWN_OR_CITY := l_ledger_info(l_index).TOWN_OR_CITY;
1246 x_ccid_le_info(l_index).REGION_1 := l_ledger_info(l_index).REGION_1;
1247 x_ccid_le_info(l_index).REGION_2 := l_ledger_info(l_index).REGION_2;
1248 x_ccid_le_info(l_index).REGION_3 := l_ledger_info(l_index).REGION_3;
1249 x_ccid_le_info(l_index).POSTAL_CODE := l_ledger_info(l_index).POSTAL_CODE;
1250 x_ccid_le_info(l_index).COUNTRY := l_ledger_info(l_index).COUNTRY;
1251 --Bug:11657204
1252 l_index := l_index + 1;
1253
1254
1255 end loop;
1256 else
1257 l_le_id := xle_utilities_grp.GET_DefaultLegalContext_OU(p_operating_unit_id);
1258 xle_utilities_grp.get_legalentity_info(
1259 x_return_status => l_return_status,
1260 x_msg_count => l_msg_count,
1261 x_msg_data => l_msg_data,
1262 p_party_id => null,
1263 p_legalentity_id => l_le_id,
1264 x_legalentity_info => l_legalentity_info);
1265
1266
1267
1268 /* Assign ledeger and legal entity information to output table */
1269
1270 x_ccid_le_info(1).ledger_id := l_ledger_id;
1271 x_ccid_le_info(1).ccid := p_code_combination_id;
1272 x_ccid_le_info(1).legal_entity_id := l_legalentity_info.legal_entity_id;
1273 x_ccid_le_info(1).name := l_legalentity_info.name;
1274 x_ccid_le_info(1).party_id := l_legalentity_info.party_id;
1275 x_ccid_le_info(1).LEGAL_ENTITY_IDENTIFIER := l_legalentity_info.LEGAL_ENTITY_IDENTIFIER;
1276 x_ccid_le_info(1).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
1277 x_ccid_le_info(1).ACTIVITY_CODE := l_legalentity_info.ACTIVITY_CODE;
1278 x_ccid_le_info(1).sub_activity_code := l_legalentity_info.sub_activity_code;
1279 x_ccid_le_info(1).type_of_company := l_legalentity_info.type_of_company;
1280 x_ccid_le_info(1).LE_EFFECTIVE_FROM := l_legalentity_info.LE_EFFECTIVE_FROM;
1281 x_ccid_le_info(1).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
1282 x_ccid_le_info(1).REGISTRATION_NUMBER := l_legalentity_info.REGISTRATION_NUMBER;
1283 x_ccid_le_info(1).LEGISLATIVE_CATEGORY := l_legalentity_info.LEGISLATIVE_CATEGORY;
1284 x_ccid_le_info(1).EFFECTIVE_FROM := l_legalentity_info.EFFECTIVE_FROM;
1285 x_ccid_le_info(1).EFFECTIVE_TO := l_legalentity_info.EFFECTIVE_TO;
1286 x_ccid_le_info(1).ADDRESS_LINE_1 := l_legalentity_info.ADDRESS_LINE_1;
1287 x_ccid_le_info(1).ADDRESS_LINE_2 := l_legalentity_info.ADDRESS_LINE_2;
1288 x_ccid_le_info(1).ADDRESS_LINE_3 := l_legalentity_info.ADDRESS_LINE_3;
1289 x_ccid_le_info(1).TOWN_OR_CITY := l_legalentity_info.TOWN_OR_CITY;
1290 x_ccid_le_info(1).REGION_1 := l_legalentity_info.REGION_1;
1291 x_ccid_le_info(1).REGION_2 := l_legalentity_info.REGION_2;
1292 x_ccid_le_info(1).REGION_3 := l_legalentity_info.REGION_3;
1293 x_ccid_le_info(1).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
1294 x_ccid_le_info(1).COUNTRY := l_legalentity_info.COUNTRY;
1295
1296
1297
1298
1299 end if;
1300 END Get_ccid_Info;
1301
1302 PROCEDURE Get_PurchasetoPay_Info(
1303 x_return_status OUT NOCOPY VARCHAR2,
1304 x_msg_data OUT NOCOPY VARCHAR2,
1305 P_registration_code IN VARCHAR2 DEFAULT NULL,
1306 P_registration_number IN VARCHAR2 DEFAULT NULL,
1307 P_location_id IN NUMBER DEFAULT NULL,
1308 P_code_combination_id IN NUMBER DEFAULT NULL,
1309 P_operating_unit_id IN NUMBER,
1310 x_ptop_le_info OUT NOCOPY XLE_BUSINESSINFO_GRP.ptop_le_rec)
1311 IS
1312
1313 l_ou_le_info XLE_BUSINESSINFO_GRP.OU_LE_Tbl_Type;
1314 l_api_version CONSTANT NUMBER := 1.0;
1315 l_return_status VARCHAR2(1);
1316 l_msg_data VARCHAR2(1000);
1317 l_msg_count NUMBER(5);
1318 x_le_flag BOOLEAN :=FALSE;
1319 l_le_id NUMBER(15);
1320 l_registration_code VARCHAR2(10);
1321 l_le_info XLE_UTILITIES_GRP.LegalEntity_Rec;
1322 TYPE l_le_tbl_type IS TABLE OF XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE INDEX BY BINARY_INTEGER;
1323 l_le_tbl l_le_tbl_type;
1324
1325 l_ccid_le_info XLE_BUSINESSINFO_GRP.ccid_le_Rec_Type;
1326
1327 BEGIN
1328
1329 x_return_status := FND_API.G_RET_STS_SUCCESS;
1330 /* Check for mandatory information */
1331
1332 if (P_operating_unit_ID is null) then
1333 x_msg_data := 'Please provide the Operating Unit Id';
1334 x_return_status := FND_API.G_RET_STS_ERROR;
1335 return;
1336 end if;
1337
1338 if (P_REGISTRATION_NUMBER is null AND P_REGISTRATION_CODE is null
1339 AND P_LOCATION_ID is null AND P_CODE_COMBINATION_ID is null) then
1340 x_msg_data := 'Please provide either Registration Code and Number or Location id or Code combination id';
1341 x_return_status := FND_API.G_RET_STS_ERROR;
1342 return;
1343 end if;
1344
1345 /* Check if Both Registration Code and Registration Number are provided */
1346
1347 if (P_REGISTRATION_CODE IS NOT NULL AND P_REGISTRATION_NUMBER IS NULL) OR
1348 (P_REGISTRATION_NUMBER IS NOT NULL AND P_REGISTRATION_CODE IS NULL) then
1349
1350 x_msg_data := 'Registration Code and Registration Number need to be provided';
1351 x_return_status := FND_API.G_RET_STS_ERROR;
1352 return;
1353 End if;
1354
1355 /* Derive LE from Le-Ledger-OU relationship if the Accounting Environment is Exclusive */
1356
1357 if P_OPERATING_UNIT_ID is NOT NULL then
1358 xle_businessinfo_grp.Get_OperatingUnit_Info(
1359 x_return_status => l_return_status,
1360 x_msg_data => l_msg_data,
1361 p_operating_unit => p_operating_unit_id,
1362 p_legal_entity_id => NULL,
1363 p_party_id => NULL,
1364 x_ou_le_info => l_ou_le_info);
1365
1366 if l_ou_le_info.count = 1 then
1367 l_le_id := l_ou_le_info(1).legal_entity_id;
1368 x_le_flag := TRUE;
1369 end if;
1370 end if; /* End if Operating Unit Id */
1371
1372 --Derive LE From Registration Information
1373
1374 if (P_REGISTRATION_CODE IS NOT NULL AND
1375 P_REGISTRATION_NUMBER IS NOT NULL) AND (NOT x_le_flag) then
1376
1377 --Derive LE from the Registration Information at the LE level
1378
1379 if P_REGISTRATION_CODE = 'ANY' then
1380 l_registration_code := NULL;
1381 else
1382 l_registration_code := P_REGISTRATION_CODE;
1383 end if;
1384 BEGIN
1385
1386 select ent.legal_entity_id
1387 into l_le_id
1388 from XLE_ENTITY_PROFILES ent,
1389 XLE_REGISTRATIONS reg,
1390 XLE_JURISDICTIONS_B jur
1391 where reg.REGISTRATION_NUMBER = P_REGISTRATION_NUMBER
1392 and REG.jurisdiction_id = jur.jurisdiction_id
1393 and REG.SOURCE_ID = ent.LEGAL_ENTITY_ID
1394 and JUR.REGISTRATION_CODE_LE = nvl(l_registration_code,jur.registration_code_le);
1395
1396 x_le_flag := TRUE;
1397
1398 EXCEPTION
1399
1400 WHEN OTHERS THEN
1401
1402 x_le_flag :=FALSE;
1403
1404
1405 END;
1406 --Derive LE from the Registration Information at the Estb Level
1407
1408 IF (NOT x_le_flag) THEN
1409
1410 BEGIN
1411 select etb.legal_entity_id
1412 BULK COLLECT into l_le_tbl
1413 from XLE_ETB_PROFILES etb,
1414 XLE_REGISTRATIONS reg,
1415 XLE_JURISDICTIONS_B jur
1416 where reg.REGISTRATION_NUMBER = P_REGISTRATION_NUMBER
1417 and REG.SOURCE_ID = etb.ESTABLISHMENT_ID
1418 and REG.jurisdiction_id = jur.jurisdiction_id
1419 and JUR.REGISTRATION_CODE_ETB = nvl(l_registration_code,jur.registration_code_etb)
1420 group by etb.legal_entity_id;
1421 if SQL%ROWCOUNT = 0 then
1422 x_le_flag := FALSE;
1423 end if;
1424
1425 if l_le_tbl.count = 1 then
1426 l_le_id := l_le_tbl(1);
1427 x_le_flag := TRUE;
1428 elsif l_le_tbl.count > 1 then
1429 x_le_flag := FALSE;
1430
1431 end if;
1432 EXCEPTION
1433
1434 WHEN OTHERS THEN
1435 x_le_flag := FALSE;
1436 END;
1437
1438 IF (NOT x_le_flag) then
1439 -- Derive LE from the E-tax Registration Call Ebtax API
1440 l_le_id := ZX_API_PUB.get_le_from_tax_registration(
1441 p_api_version => l_api_version,
1442 p_init_msg_list => null,
1443 p_commit => 'FALSE',
1444 p_validation_level => null ,
1445 x_return_status => l_return_status,
1446 x_msg_count => l_msg_count,
1447 x_msg_data => l_msg_data ,
1448 p_registration_num => p_registration_number,
1449 p_effective_date => sysdate,
1450 p_country => null) ;
1451
1452 if l_le_id IS NOT NULL and nvl(l_return_status,'x') = 'S' then
1453 x_le_flag := TRUE;
1454 else
1455 x_le_flag := FALSE;
1456 end if;
1457
1458 end if;
1459 end if;
1460 end if;
1461
1462 /* Use the Legal Associations view to find the associations between establishments and
1463 the given location id. If there is more than one establishment associated
1464 to a location check if all establishments belong to the same Legal Entity */
1465
1466 if ((P_LOCATION_ID is not null) AND (NOT x_le_flag)) then
1467
1468 BEGIN
1469 if (l_le_tbl.EXISTS(1)) then
1470 l_le_tbl.DELETE;
1471 end if;
1472
1473 SELECT legal_parent_id BULK COLLECT INTO l_le_tbl
1474 FROM xle_tax_associations
1475 WHERE ENTITY_ID =P_LOCATION_ID
1476 -- AND context = 'TAX_CALCULATION'
1477 AND ENTITY_TYPE ='BILL_TO_LOCATION'
1478 AND LEGAL_CONSTRUCT = 'ESTABLISHMENT'
1479 GROUP BY legal_parent_id;
1480
1481 if l_le_tbl.count = 1 then
1482 l_le_id := l_le_tbl(1);
1483 x_le_flag := TRUE;
1484 else
1485 x_le_flag := FALSE;
1486 end if;
1487
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 x_le_flag := FALSE;
1491 END;
1492
1493 end if; --End if P_location_id
1494
1495
1496 --Derive LE from the Code combination ID. Derive BSV of the Code combination ID
1497 --and derive Ledger from the Operating Unit - If No LE is assigned , the DLC is used.
1498 -- Use the LE CCId API to derive the LE
1499
1500 if (p_code_combination_id is not null and p_operating_unit_id is not null) AND (NOT x_le_flag) then
1501
1502 xle_businessinfo_grp.get_ccid_info(
1503 x_return_status => l_return_status,
1504 x_msg_data => l_msg_data,
1505 p_operating_unit_id => p_operating_unit_id,
1506 p_code_combination_id => p_code_combination_id,
1507 x_ccid_le_info => l_ccid_le_info);
1508
1509
1510 if l_ccid_le_info.count = 1 then
1511 l_le_id := l_ccid_le_info(1).legal_entity_id;
1512 x_le_flag := TRUE;
1513 else
1514 x_le_flag := FALSE;
1515 end if;
1516 end if;
1517
1518 /* Use the Default Legal Context */
1519 if (p_operating_unit_id IS NOT NULL) AND (NOT x_le_flag) then
1520
1521 l_le_id := XLE_UTILITIES_GRP.get_defaultlegalcontext_ou(p_operating_unit => p_operating_unit_id);
1522
1523
1524 if l_le_id is NOT NULL then
1525 x_le_flag := TRUE;
1526 end if;
1527 end if;
1528
1529 if (x_le_flag) then
1530 Xle_utilities_grp.get_legalentity_info (
1531 x_return_status => l_return_status,
1532 x_msg_count => l_msg_count,
1533 x_msg_data => l_msg_data,
1534 p_party_id => null,
1535 p_legalentity_id => l_le_id,
1536 x_legalentity_info => l_le_info);
1537
1538 if l_le_info.legal_entity_id IS NOT NULL then
1539 x_ptop_le_info.legal_entity_id := l_le_info.legal_entity_id;
1540 x_ptop_le_info.name := l_le_info.name;
1541 x_ptop_le_info.party_ID := l_le_info.party_ID;
1542 x_ptop_le_info.legal_entity_identifier := l_le_info.legal_entity_identifier;
1543 x_ptop_le_info.transacting_entity_flag := l_le_info.transacting_entity_flag;
1544 x_ptop_le_info.activity_code := l_le_info.activity_code ;
1545 x_ptop_le_info.sub_activity_code := l_le_info.sub_activity_code ;
1546 x_ptop_le_info.type_of_company := l_le_info.type_of_company ;
1547 x_ptop_le_info.le_effective_from := l_le_info.le_effective_from ;
1548 x_ptop_le_info.le_effective_to := l_le_info.le_effective_to ;
1549 x_ptop_le_info.registration_number := l_le_info.registration_number ;
1550 x_ptop_le_info.legislative_category := l_le_info.legislative_category ;
1551 x_ptop_le_info.effective_from := l_le_info.effective_from ;
1552 x_ptop_le_info.effective_to := l_le_info.effective_to;
1553 x_ptop_le_info.address_line_1 := l_le_info.address_line_1 ;
1554 x_ptop_le_info.address_line_2 := l_le_info.address_line_2 ;
1555 x_ptop_le_info.address_line_3 := l_le_info.address_line_3 ;
1556 x_ptop_le_info.town_or_city := l_le_info.town_or_city ;
1557 x_ptop_le_info.region_1 := l_le_info.region_1 ;
1558 x_ptop_le_info.region_2 := l_le_info.region_2 ;
1559 x_ptop_le_info.region_3 := l_le_info.region_3 ;
1560 x_ptop_le_info.postal_code := l_le_info.postal_code ;
1561 x_ptop_le_info.country := l_le_info.country ;
1562 else
1563 x_return_status := FND_API.G_RET_STS_ERROR ;
1564 x_msg_data := 'No Legal Entity Found';
1565 l_le_id := null;
1566 end if;
1567 end if;
1568 EXCEPTION
1569 WHEN OTHERS THEN
1570 x_return_status := FND_API.G_RET_STS_ERROR ;
1571 raise;
1572 -- null;
1573
1574 END Get_PurchasetoPay_Info;
1575
1576 /*============================================+
1577 | PROCEDURE Get_OrdertoCash_Info
1578 |
1579 | DESCRIPTION
1580 | Public Procedure which returns the otoc_Le_info record
1581 |
1582 | IN parameters:
1583 | P_customer_type VARCHAR optional 'SOLD_TO' or 'BILL_TO'
1584 | P_customer_id NUMBER optional
1585 | P_transaction_type_id NUMBER optional
1586 | P_batch_source_id NUMBER optional
1587 | P_operating_unit_id NUMBER mandatory
1588 |
1589 | OUT parameters:
1590 | x_otoc_Le_info otoc_le_rec
1591 | x_return_status VARCHAR 'E' for error,'S' for sucess
1592 | x_msg_data VARCHAR error message
1593 |
1594 | If the returned otoc_Le_info record contains a legal entity id value of -1,
1595 | then the legal entity could not be found. An error is raised with error msg
1596 |
1597 |
1598 | DEV NOTE: This procedure calls the AR API get_default_le and if the LE id
1599 | cannot be found from this then the GET_DefaultLegalContext_OU is called,
1600 | which tries to find the LE ID using the operating unit parameter.
1601 |
1602 |
1603 | MODIFICATION HISTORY
1604 | DATE Author Description of Changes
1605 | 21-Jun-2005 N Foley Created
1606 |
1607 *===========================================================================*/
1608
1609 PROCEDURE Get_OrdertoCash_Info(
1610 x_return_status OUT NOCOPY VARCHAR2 ,
1611 x_msg_data OUT NOCOPY VARCHAR2 ,
1612 P_customer_type IN VARCHAR2 DEFAULT NULL,
1613 P_customer_id IN NUMBER DEFAULT NULL,
1614 P_transaction_type_id IN NUMBER DEFAULT NULL,
1615 P_batch_source_id IN NUMBER DEFAULT NULL,
1616 P_operating_unit_id IN NUMBER,
1617 x_otoc_Le_info OUT NOCOPY XLE_BUSINESSINFO_GRP.otoc_le_rec)
1618 IS
1619 l_le_id NUMBER;
1620 l_return_status varchar2(1);
1621 l_msg_data varchar2(1000);
1622 BEGIN
1623
1624 /* Parameter Operating Unit Id is mandatory */
1625 IF P_operating_unit_id IS null then
1626 x_msg_data := 'Please pass a value for Operating Unit ID.';
1627 x_return_status := FND_API.G_RET_STS_ERROR;
1628 x_otoc_Le_info.legal_entity_id := -1;
1629 return;
1630 end if;
1631
1632 /* Parameter Customer Type must be 'SOLD_TO' or 'BILL_TO'*/
1633 IF P_customer_type IS NOT NULL then
1634 IF P_customer_type = 'SOLD_TO' or P_customer_type = 'BILL_TO' then
1635 IF P_customer_id IS NULL then
1636 x_msg_data := 'Please pass a value for Customer Id as
1637 Customer Type has been specified.';
1638 x_return_status := FND_API.G_RET_STS_ERROR;
1639 x_otoc_Le_info.legal_entity_id := -1;
1640 return;
1641 End if;
1642 else
1643 x_msg_data := 'Please pass value SOLD_TO or BILL_TO for Customer Type.';
1644 x_return_status := FND_API.G_RET_STS_ERROR;
1645 x_otoc_Le_info.legal_entity_id := -1;
1646 return;
1647 end if;
1648 End if;
1649 --Bug:8547524
1650 /* Call the AR API to get the Legal Entity Id*/
1651 l_le_id := arp_legal_entity_util.get_default_le (
1652 p_sold_to_cust_id => P_CUSTOMER_ID,
1653 p_bill_to_cust_id => P_CUSTOMER_ID,
1654 p_trx_type_id => P_TRANSACTION_TYPE_ID ,
1655 p_batch_source_id => P_batch_source_id,
1656 p_org_id => P_operating_unit_id);
1657
1658 /* If no Legal Entity Id found then returns -1*/
1659 if l_le_id = -1 then
1660 /* Next try to get LE id from Default Legal Context */
1661 l_le_id := xle_utilities_grp.GET_DefaultLegalContext_OU(
1662 P_operating_unit_id);
1663 if l_le_id = -1 then
1664 x_return_status := FND_API.G_RET_STS_ERROR ;
1665 x_msg_data := 'No Legal Entity Found';
1666 x_otoc_Le_info.legal_entity_id := -1;
1667 return;
1668 end if;
1669 end if;
1670
1671 x_otoc_Le_info.legal_entity_id := l_le_id;
1672
1673 x_return_status := FND_API.G_RET_STS_SUCCESS;
1674 EXCEPTION
1675 WHEN OTHERS THEN
1676 x_return_status := FND_API.G_RET_STS_ERROR ;
1677
1678 END Get_OrdertoCash_Info ;
1679
1680 /*============================================+
1681 | FUNCTION Get_OrdertoCash_Info
1682 |
1683 | DESCRIPTION
1684 | Public Procedure which returns the legal entity id as a NUMBER
1685 |
1686 | IN parameters:
1687 | P_customer_type VARCHAR optional 'SOLD_TO' or 'BILL_TO'
1688 | P_customer_id NUMBER optional
1689 | P_transaction_type_id NUMBER optional
1690 | P_batch_source_id NUMBER optional
1691 | P_operating_unit_id NUMBER mandatory
1692 |
1693 | OUT parameters:
1694 | P_legal_entity_id NUMBER -1 if not found
1695 |
1696 | If returned legal entity id is -1, then the legal entity could not be
1697 | found. An error is raised, with an error message in this case describing
1698 | the problem. Please see other version of this method for more details.
1699 |
1700 | DEV NOTE: This Procedure is in fact a wrapper for the other
1701 | Get_OrdertoCash_Info which returns a otoc_le_info record.
1702 |
1703 | This procedures takes the otoc_le_info record and extracts the legal
1704 | entity Id from it and returns it.
1705 |
1706 | This was a request from another product team as they required only the
1707 | legal entity id and not the whole record.
1708 |
1709 | This function signature-without out parameters can be used in sql statement.
1710 |
1711 | MODIFICATION HISTORY
1712 | DATE Author Description of Changes
1713 | 21-Jun-2005 N Foley Created
1714 | 23-Jun-2005 N Foley Changed to function
1715 | 27-Sep-2005 R Basker Bug 4635044: PL/SQL functions referenced by SQL
1716 | statements must not contain the OUT parameter.
1717 *===========================================================================*/
1718 FUNCTION Get_OrdertoCash_Info(
1719 P_customer_type IN VARCHAR2 DEFAULT NULL,
1720 P_customer_id IN NUMBER DEFAULT NULL,
1721 P_transaction_type_id IN NUMBER DEFAULT NULL,
1722 P_batch_source_id IN NUMBER DEFAULT NULL,
1723 P_operating_unit_id IN NUMBER
1724 )
1725 RETURN NUMBER IS
1726
1727 l_le_id NUMBER;
1728 l_return_status varchar2(1);
1729 l_msg_data varchar2(1000);
1730 l_legal_entity_id number;
1731 l_customer_type varchar2(30);
1732 l_customer_id number;
1733 l_transaction_type_id number;
1734 l_batch_source_id number;
1735 l_operating_unit_id number;
1736 l_otoc_le_info XLE_BUSINESSINFO_GRP.otoc_le_rec;
1737
1738 BEGIN
1739
1740 l_customer_type := P_customer_type;
1741 l_customer_id := P_customer_id;
1742 l_transaction_type_id := P_transaction_type_id;
1743 l_batch_source_id := P_batch_source_id;
1744 l_operating_unit_id := P_operating_unit_id;
1745
1746 /* Call the main Get_OrdertoCash_Info method that returns the
1747 otoc_Le_info record*/
1748
1749 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info (
1750 x_return_status => l_return_status,
1751 x_msg_data => l_msg_data,
1752 P_customer_type => l_customer_type,
1753 P_customer_id => l_customer_id,
1754 P_transaction_type_id => l_transaction_type_id,
1755 P_batch_source_id => l_batch_source_id,
1756 P_operating_unit_id => l_operating_unit_id,
1757 x_otoc_Le_info => l_otoc_le_info);
1758
1759 -- if any error occurs propagate as unexpected error
1760
1761 -- Commenting for bug 5159735
1762 /*
1763 IF l_return_status = FND_API.G_RET_STS_ERROR OR
1764 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1766 END IF ;
1767
1768 */
1769 /* If the otoc_Le_info is returned just get the Legal Entity Id*/
1770 if l_otoc_le_info.legal_entity_id IS NOT NULL then
1771 return l_otoc_le_info.legal_entity_id;
1772 else
1773 return -1;
1774 end if;
1775
1776 END Get_OrdertoCash_Info ;
1777
1778 /*============================================+
1779 | FUNCTION Get_OrdertoCash_Info
1780 |
1781 | DESCRIPTION
1782 | Public Procedure which returns the legal entity id as a NUMBER
1783 |
1784 | IN parameters:
1785 | P_customer_type VARCHAR optional 'SOLD_TO' or 'BILL_TO'
1786 | P_customer_id NUMBER optional
1787 | P_transaction_type_id NUMBER optional
1788 | P_batch_source_id NUMBER optional
1789 | P_operating_unit_id NUMBER mandatory
1790 |
1791 | OUT parameters:
1792 | P_legal_entity_id NUMBER -1 if not found
1793 | x_return_status VARCHAR 'E' for error,'S' for sucess
1794 | x_msg_data VARCHAR error message
1795 |
1796 | If returned legal entity id is -1, then the legal entity could not be
1797 | found. An error is raised, with an error message in this case describing the
1798 | problem.
1799 | Please see other version of this method for more details.
1800 |
1801 | DEV NOTE: This Procedure is in fact a wrapper for the other Get_OrdertoCash_Info
1802 | which returns a otoc_le_info record. This procedures takes the otoc_le_info
1803 | record and extracts the legal entity Id from it and returns it. This was
1804 | a request from another product team as they required only the legal entity id
1805 | and not the whole record.
1806 | This function signature-with out parameters can be used in any where except in
1807 | sql statement.
1808 |
1809 | MODIFICATION HISTORY
1810 | DATE Author Description of Changes
1811 | 21-Oct-2005 spasupun Bgu : 4690944 Added the function overloaded with OUT
1812 | parameters.
1813 *===========================================================================*/
1814 FUNCTION Get_OrdertoCash_Info(
1815 x_return_status OUT NOCOPY VARCHAR2 ,
1816 x_msg_data OUT NOCOPY VARCHAR2 ,
1817 P_customer_type IN VARCHAR2 DEFAULT NULL,
1818 P_customer_id IN NUMBER DEFAULT NULL,
1819 P_transaction_type_id IN NUMBER DEFAULT NULL,
1820 P_batch_source_id IN NUMBER DEFAULT NULL,
1821 P_operating_unit_id IN NUMBER
1822 )
1823 RETURN NUMBER IS
1824 l_le_id NUMBER;
1825 l_return_status varchar2(1);
1826 l_msg_data varchar2(1000);
1827 l_legal_entity_id number;
1828 l_customer_type varchar2(30);
1829 l_customer_id number;
1830 l_transaction_type_id number;
1831 l_batch_source_id number;
1832 l_operating_unit_id number;
1833 l_otoc_le_info XLE_BUSINESSINFO_GRP.otoc_le_rec;
1834
1835 BEGIN
1836 l_customer_type := P_customer_type;
1837 l_customer_id := P_customer_id;
1838 l_transaction_type_id := P_transaction_type_id;
1839 l_batch_source_id := P_batch_source_id;
1840 l_operating_unit_id := P_operating_unit_id;
1841
1842 /* Call the main Get_OrdertoCash_Info method that returns the otoc_Le_info record*/
1843 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info (
1844 x_return_status => l_return_status,
1845 x_msg_data => l_msg_data,
1846 P_customer_type => l_customer_type,
1847 P_customer_id => l_customer_id,
1848 P_transaction_type_id => l_transaction_type_id,
1849 P_batch_source_id => l_batch_source_id,
1850 P_operating_unit_id => l_operating_unit_id,
1851 x_otoc_Le_info => l_otoc_le_info);
1852
1853 if l_return_status = FND_API.G_RET_STS_ERROR then
1854 x_return_status := l_return_status ;
1855 x_msg_data := l_msg_data;
1856 RETURN -1;
1857 end if;
1858
1859
1860 /* If the otoc_Le_info is returned just get the Legal Entity Id*/
1861 if l_otoc_le_info.legal_entity_id IS NOT NULL then
1862 return l_otoc_le_info.legal_entity_id;
1863
1864 else
1865 x_return_status := FND_API.G_RET_STS_ERROR ;
1866 x_msg_data := 'No Legal Entity Found';
1867 l_le_id := -1;
1868 return -1;
1869 end if;
1870
1871 EXCEPTION
1872 WHEN OTHERS THEN
1873 x_return_status := FND_API.G_RET_STS_ERROR ;
1874
1875 END Get_OrdertoCash_Info ;
1876
1877 END XLE_BUSINESSINFO_GRP;
[Home] [Help]