1 PACKAGE BODY XLE_BUSINESSINFO_GRP AS
2 /* $Header: xlegbuib.pls 120.34 2006/04/17 06:53:10 akonatha 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
324
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.';
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;
351 l_index number := 1;
348 l_le_flag boolean := false;
349 l_le_return_flag boolean := false;
350
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
408 x_Ledger_info(l_index).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
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;
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);
538 for j in x_bsv_list.first..x_bsv_list.last loop
535
536
537
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;
657
654
655 BEGIN
656 l_legal_entity_id := p_legal_entity_id;
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(
700 populates the output table */
697 l_ledger_id,x_le_list);
698
699 /* The following statement loops through the legal entities and
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(i).Operating_Unit_ID := p_operating_unit;
706 x_ou_le_info(i).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
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;
838 x_ou_le_info(l_index).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
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;
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;
884
881 l_legal_entity_id number;
882
883 l_index number := 1;
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;
1019 x_Inv_Le_info(l_index).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
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;
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();
1137 /* Check for mandatory information */
1134 BEGIN
1135
1136
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
1252
1253
1254 end loop;
1255 else
1256 l_le_id := xle_utilities_grp.GET_DefaultLegalContext_OU(p_operating_unit_id);
1257 xle_utilities_grp.get_legalentity_info(
1258 x_return_status => l_return_status,
1259 x_msg_count => l_msg_count,
1260 x_msg_data => l_msg_data,
1261 p_party_id => null,
1265
1262 p_legalentity_id => l_le_id,
1263 x_legalentity_info => l_legalentity_info);
1264
1266
1267 /* Assign ledeger and legal entity information to output table */
1268
1269 x_ccid_le_info(1).ledger_id := l_ledger_id;
1270 x_ccid_le_info(1).ccid := p_code_combination_id;
1271 x_ccid_le_info(1).legal_entity_id := l_legalentity_info.legal_entity_id;
1272 x_ccid_le_info(1).name := l_legalentity_info.name;
1273 x_ccid_le_info(1).party_id := l_legalentity_info.party_id;
1274 x_ccid_le_info(1).LEGAL_ENTITY_IDENTIFIER := l_legalentity_info.LEGAL_ENTITY_IDENTIFIER;
1275 x_ccid_le_info(1).TRANSACTING_ENTITY_FLAG := l_legalentity_info.TRANSACTING_ENTITY_FLAG;
1276 x_ccid_le_info(1).ACTIVITY_CODE := l_legalentity_info.ACTIVITY_CODE;
1277 x_ccid_le_info(1).sub_activity_code := l_legalentity_info.sub_activity_code;
1278 x_ccid_le_info(1).type_of_company := l_legalentity_info.type_of_company;
1279 x_ccid_le_info(1).LE_EFFECTIVE_FROM := l_legalentity_info.LE_EFFECTIVE_FROM;
1280 x_ccid_le_info(1).LE_EFFECTIVE_TO := l_legalentity_info.LE_EFFECTIVE_TO;
1281 x_ccid_le_info(1).REGISTRATION_NUMBER := l_legalentity_info.REGISTRATION_NUMBER;
1282 x_ccid_le_info(1).LEGISLATIVE_CATEGORY := l_legalentity_info.LEGISLATIVE_CATEGORY;
1283 x_ccid_le_info(1).EFFECTIVE_FROM := l_legalentity_info.EFFECTIVE_FROM;
1284 x_ccid_le_info(1).EFFECTIVE_TO := l_legalentity_info.EFFECTIVE_TO;
1285 x_ccid_le_info(1).ADDRESS_LINE_1 := l_legalentity_info.ADDRESS_LINE_1;
1286 x_ccid_le_info(1).ADDRESS_LINE_2 := l_legalentity_info.ADDRESS_LINE_2;
1287 x_ccid_le_info(1).ADDRESS_LINE_3 := l_legalentity_info.ADDRESS_LINE_3;
1288 x_ccid_le_info(1).TOWN_OR_CITY := l_legalentity_info.TOWN_OR_CITY;
1289 x_ccid_le_info(1).REGION_1 := l_legalentity_info.REGION_1;
1290 x_ccid_le_info(1).REGION_2 := l_legalentity_info.REGION_2;
1291 x_ccid_le_info(1).REGION_3 := l_legalentity_info.REGION_3;
1292 x_ccid_le_info(1).POSTAL_CODE := l_legalentity_info.POSTAL_CODE;
1293 x_ccid_le_info(1).COUNTRY := l_legalentity_info.COUNTRY;
1294
1295
1296
1297
1298 end if;
1299 END Get_ccid_Info;
1300
1301 PROCEDURE Get_PurchasetoPay_Info(
1302 x_return_status OUT NOCOPY VARCHAR2,
1303 x_msg_data OUT NOCOPY VARCHAR2,
1304 P_registration_code IN VARCHAR2 DEFAULT NULL,
1305 P_registration_number IN VARCHAR2 DEFAULT NULL,
1306 P_location_id IN NUMBER DEFAULT NULL,
1310 IS
1307 P_code_combination_id IN NUMBER DEFAULT NULL,
1308 P_operating_unit_id IN NUMBER,
1309 x_ptop_le_info OUT NOCOPY XLE_BUSINESSINFO_GRP.ptop_le_rec)
1311
1312 l_ou_le_info XLE_BUSINESSINFO_GRP.OU_LE_Tbl_Type;
1313 l_api_version CONSTANT NUMBER := 1.0;
1314 l_return_status VARCHAR2(1);
1315 l_msg_data VARCHAR2(1000);
1316 l_msg_count NUMBER(5);
1317 x_le_flag BOOLEAN :=FALSE;
1318 l_le_id NUMBER(15);
1319 l_registration_code VARCHAR2(10);
1320 l_le_info XLE_UTILITIES_GRP.LegalEntity_Rec;
1321 TYPE l_le_tbl_type IS TABLE OF XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE INDEX BY BINARY_INTEGER;
1322 l_le_tbl l_le_tbl_type;
1323
1324 l_ccid_le_info XLE_BUSINESSINFO_GRP.ccid_le_Rec_Type;
1325
1326 BEGIN
1327
1328 x_return_status := FND_API.G_RET_STS_SUCCESS;
1329 /* Check for mandatory information */
1330
1331 if (P_operating_unit_ID is null) then
1332 x_msg_data := 'Please provide the Operating Unit Id';
1333 x_return_status := FND_API.G_RET_STS_ERROR;
1334 return;
1335 end if;
1336
1337 if (P_REGISTRATION_NUMBER is null AND P_REGISTRATION_CODE is null
1338 AND P_LOCATION_ID is null AND P_CODE_COMBINATION_ID is null) then
1339 x_msg_data := 'Please provide either Registration Code and Number or Location id or Code combination id';
1340 x_return_status := FND_API.G_RET_STS_ERROR;
1341 return;
1342 end if;
1343
1344 /* Check if Both Registration Code and Registration Number are provided */
1345
1346 if (P_REGISTRATION_CODE IS NOT NULL AND P_REGISTRATION_NUMBER IS NULL) OR
1347 (P_REGISTRATION_NUMBER IS NOT NULL AND P_REGISTRATION_CODE IS NULL) then
1348
1349 x_msg_data := 'Registration Code and Registration Number need to be provided';
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 return;
1352 End if;
1353
1354 /* Derive LE from Le-Ledger-OU relationship if the Accounting Environment is Exclusive */
1355
1356 if P_OPERATING_UNIT_ID is NOT NULL then
1357 xle_businessinfo_grp.Get_OperatingUnit_Info(
1358 x_return_status => l_return_status,
1359 x_msg_data => l_msg_data,
1363 x_ou_le_info => l_ou_le_info);
1360 p_operating_unit => p_operating_unit_id,
1361 p_legal_entity_id => NULL,
1362 p_party_id => NULL,
1364
1365 if l_ou_le_info.count = 1 then
1366 l_le_id := l_ou_le_info(1).legal_entity_id;
1367 x_le_flag := TRUE;
1368 end if;
1369 end if; /* End if Operating Unit Id */
1370
1371 --Derive LE From Registration Information
1372
1373 if (P_REGISTRATION_CODE IS NOT NULL AND
1374 P_REGISTRATION_NUMBER IS NOT NULL) AND (NOT x_le_flag) then
1375
1376 --Derive LE from the Registration Information at the LE level
1377
1378 if P_REGISTRATION_CODE = 'ANY' then
1379 l_registration_code := NULL;
1380 else
1381 l_registration_code := P_REGISTRATION_CODE;
1382 end if;
1383 BEGIN
1384
1385 select ent.legal_entity_id
1386 into l_le_id
1387 from XLE_ENTITY_PROFILES ent,
1388 XLE_REGISTRATIONS reg,
1389 XLE_JURISDICTIONS_B jur
1390 where reg.REGISTRATION_NUMBER = P_REGISTRATION_NUMBER
1391 and REG.jurisdiction_id = jur.jurisdiction_id
1392 and REG.SOURCE_ID = ent.LEGAL_ENTITY_ID
1393 and JUR.REGISTRATION_CODE_LE = nvl(l_registration_code,jur.registration_code_le);
1394
1395 x_le_flag := TRUE;
1396
1397 EXCEPTION
1398
1399 WHEN OTHERS THEN
1400
1401 x_le_flag :=FALSE;
1402
1403
1404 END;
1405 --Derive LE from the Registration Information at the Estb Level
1406
1407 IF (NOT x_le_flag) THEN
1408
1409 BEGIN
1410 select etb.legal_entity_id
1411 BULK COLLECT into l_le_tbl
1412 from XLE_ETB_PROFILES etb,
1413 XLE_REGISTRATIONS reg,
1414 XLE_JURISDICTIONS_B jur
1415 where reg.REGISTRATION_NUMBER = P_REGISTRATION_NUMBER
1416 and REG.SOURCE_ID = etb.ESTABLISHMENT_ID
1417 and REG.jurisdiction_id = jur.jurisdiction_id
1418 and JUR.REGISTRATION_CODE_ETB = nvl(l_registration_code,jur.registration_code_etb)
1419 group by etb.legal_entity_id;
1420 if SQL%ROWCOUNT = 0 then
1421 x_le_flag := FALSE;
1422 end if;
1423
1424 if l_le_tbl.count = 1 then
1425 l_le_id := l_le_tbl(1);
1426 x_le_flag := TRUE;
1427 elsif l_le_tbl.count > 1 then
1428 x_le_flag := FALSE;
1429
1430 end if;
1431 EXCEPTION
1432
1433 WHEN OTHERS THEN
1434 x_le_flag := FALSE;
1435 END;
1436
1437 IF (NOT x_le_flag) then
1438 -- Derive LE from the E-tax Registration Call Ebtax API
1439 l_le_id := ZX_API_PUB.get_le_from_tax_registration(
1440 p_api_version => l_api_version,
1441 p_init_msg_list => null,
1442 p_commit => 'FALSE',
1443 p_validation_level => null ,
1444 x_return_status => l_return_status,
1445 x_msg_count => l_msg_count,
1446 x_msg_data => l_msg_data ,
1447 p_registration_num => p_registration_number,
1448 p_effective_date => sysdate,
1449 p_country => null) ;
1450
1451 if l_le_id IS NOT NULL and nvl(l_return_status,'x') = 'S' then
1452 x_le_flag := TRUE;
1453 else
1454 x_le_flag := FALSE;
1455 end if;
1456
1457 end if;
1458 end if;
1459 end if;
1460
1461 /* Use the Legal Associations view to find the associations between establishments and
1462 the given location id. If there is more than one establishment associated
1463 to a location check if all establishments belong to the same Legal Entity */
1464
1465 if ((P_LOCATION_ID is not null) AND (NOT x_le_flag)) then
1466
1467 BEGIN
1468 if (l_le_tbl.EXISTS(1)) then
1469 l_le_tbl.DELETE;
1470 end if;
1471
1472 SELECT legal_parent_id BULK COLLECT INTO l_le_tbl
1473 FROM xle_tax_associations
1474 WHERE ENTITY_ID =P_LOCATION_ID
1475 -- AND context = 'TAX_CALCULATION'
1476 AND ENTITY_TYPE ='BILL_TO_LOCATION'
1477 AND LEGAL_CONSTRUCT = 'ESTABLISHMENT'
1478 GROUP BY legal_parent_id;
1479
1480 if l_le_tbl.count = 1 then
1481 l_le_id := l_le_tbl(1);
1482 x_le_flag := TRUE;
1483 else
1484 x_le_flag := FALSE;
1485 end if;
1486
1487 EXCEPTION
1488 WHEN OTHERS THEN
1489 x_le_flag := FALSE;
1490 END;
1491
1492 end if; --End if P_location_id
1493
1494
1495 --Derive LE from the Code combination ID. Derive BSV of the Code combination ID
1496 --and derive Ledger from the Operating Unit - If No LE is assigned , the DLC is used.
1497 -- Use the LE CCId API to derive the LE
1498
1499 if (p_code_combination_id is not null and p_operating_unit_id is not null) AND (NOT x_le_flag) then
1500
1504 p_operating_unit_id => p_operating_unit_id,
1501 xle_businessinfo_grp.get_ccid_info(
1502 x_return_status => l_return_status,
1503 x_msg_data => l_msg_data,
1505 p_code_combination_id => p_code_combination_id,
1506 x_ccid_le_info => l_ccid_le_info);
1507
1508
1509 if l_ccid_le_info.count = 1 then
1510 l_le_id := l_ccid_le_info(1).legal_entity_id;
1511 x_le_flag := TRUE;
1512 else
1513 x_le_flag := FALSE;
1514 end if;
1515 end if;
1516
1517 /* Use the Default Legal Context */
1518 if (p_operating_unit_id IS NOT NULL) AND (NOT x_le_flag) then
1519
1520 l_le_id := XLE_UTILITIES_GRP.get_defaultlegalcontext_ou(p_operating_unit => p_operating_unit_id);
1521
1522
1523 if l_le_id is NOT NULL then
1524 x_le_flag := TRUE;
1525 end if;
1526 end if;
1527
1528 if (x_le_flag) then
1529 Xle_utilities_grp.get_legalentity_info (
1530 x_return_status => l_return_status,
1531 x_msg_count => l_msg_count,
1532 x_msg_data => l_msg_data,
1533 p_party_id => null,
1534 p_legalentity_id => l_le_id,
1535 x_legalentity_info => l_le_info);
1536
1537 if l_le_info.legal_entity_id IS NOT NULL then
1538 x_ptop_le_info.legal_entity_id := l_le_info.legal_entity_id;
1539 x_ptop_le_info.name := l_le_info.name;
1540 x_ptop_le_info.party_ID := l_le_info.party_ID;
1541 x_ptop_le_info.legal_entity_identifier := l_le_info.legal_entity_identifier;
1542 x_ptop_le_info.transacting_entity_flag := l_le_info.transacting_entity_flag;
1543 x_ptop_le_info.activity_code := l_le_info.activity_code ;
1544 x_ptop_le_info.sub_activity_code := l_le_info.sub_activity_code ;
1545 x_ptop_le_info.type_of_company := l_le_info.type_of_company ;
1546 x_ptop_le_info.le_effective_from := l_le_info.le_effective_from ;
1547 x_ptop_le_info.le_effective_to := l_le_info.le_effective_to ;
1548 x_ptop_le_info.registration_number := l_le_info.registration_number ;
1549 x_ptop_le_info.legislative_category := l_le_info.legislative_category ;
1550 x_ptop_le_info.effective_from := l_le_info.effective_from ;
1551 x_ptop_le_info.effective_to := l_le_info.effective_to;
1552 x_ptop_le_info.address_line_1 := l_le_info.address_line_1 ;
1553 x_ptop_le_info.address_line_2 := l_le_info.address_line_2 ;
1554 x_ptop_le_info.address_line_3 := l_le_info.address_line_3 ;
1555 x_ptop_le_info.town_or_city := l_le_info.town_or_city ;
1556 x_ptop_le_info.region_1 := l_le_info.region_1 ;
1557 x_ptop_le_info.region_2 := l_le_info.region_2 ;
1558 x_ptop_le_info.region_3 := l_le_info.region_3 ;
1559 x_ptop_le_info.postal_code := l_le_info.postal_code ;
1560 x_ptop_le_info.country := l_le_info.country ;
1561 else
1562 x_return_status := FND_API.G_RET_STS_ERROR ;
1563 x_msg_data := 'No Legal Entity Found';
1564 l_le_id := null;
1565 end if;
1566 end if;
1567 EXCEPTION
1568 WHEN OTHERS THEN
1569 x_return_status := FND_API.G_RET_STS_ERROR ;
1570 raise;
1571 -- null;
1572
1573 END Get_PurchasetoPay_Info;
1574
1575 /*============================================+
1576 | PROCEDURE Get_OrdertoCash_Info
1577 |
1578 | DESCRIPTION
1579 | Public Procedure which returns the otoc_Le_info record
1580 |
1581 | IN parameters:
1582 | P_customer_type VARCHAR optional 'SOLD_TO' or 'BILL_TO'
1583 | P_customer_id NUMBER optional
1584 | P_transaction_type_id NUMBER optional
1585 | P_batch_source_id NUMBER optional
1586 | P_operating_unit_id NUMBER mandatory
1587 |
1588 | OUT parameters:
1589 | x_otoc_Le_info otoc_le_rec
1590 | x_return_status VARCHAR 'E' for error,'S' for sucess
1591 | x_msg_data VARCHAR error message
1592 |
1593 | If the returned otoc_Le_info record contains a legal entity id value of -1,
1594 | then the legal entity could not be found. An error is raised with error msg
1595 |
1596 |
1597 | DEV NOTE: This procedure calls the AR API get_default_le and if the LE id
1598 | cannot be found from this then the GET_DefaultLegalContext_OU is called,
1599 | which tries to find the LE ID using the operating unit parameter.
1600 |
1601 |
1602 | MODIFICATION HISTORY
1603 | DATE Author Description of Changes
1604 | 21-Jun-2005 N Foley Created
1605 |
1606 *===========================================================================*/
1607
1608 PROCEDURE Get_OrdertoCash_Info(
1609 x_return_status OUT NOCOPY VARCHAR2 ,
1610 x_msg_data OUT NOCOPY VARCHAR2 ,
1611 P_customer_type IN VARCHAR2 DEFAULT NULL,
1612 P_customer_id IN NUMBER DEFAULT NULL,
1613 P_transaction_type_id IN NUMBER DEFAULT NULL,
1614 P_batch_source_id IN NUMBER DEFAULT NULL,
1615 P_operating_unit_id IN NUMBER,
1616 x_otoc_Le_info OUT NOCOPY XLE_BUSINESSINFO_GRP.otoc_le_rec)
1617 IS
1618 l_le_id NUMBER;
1619 l_return_status varchar2(1);
1620 l_msg_data varchar2(1000);
1621 BEGIN
1622
1623 /* Parameter Operating Unit Id is mandatory */
1624 IF P_operating_unit_id IS null then
1628 return;
1625 x_msg_data := 'Please pass a value for Operating Unit ID.';
1626 x_return_status := FND_API.G_RET_STS_ERROR;
1627 x_otoc_Le_info.legal_entity_id := -1;
1629 end if;
1630
1631 /* Parameter Customer Type must be 'SOLD_TO' or 'BILL_TO'*/
1632 IF P_customer_type IS NOT NULL then
1633 IF P_customer_type = 'SOLD_TO' or P_customer_type = 'BILL_TO' then
1634 IF P_customer_id IS NULL then
1635 x_msg_data := 'Please pass a value for Customer Id as
1636 Customer Type has been specified.';
1637 x_return_status := FND_API.G_RET_STS_ERROR;
1638 x_otoc_Le_info.legal_entity_id := -1;
1639 return;
1640 End if;
1641 else
1642 x_msg_data := 'Please pass value SOLD_TO or BILL_TO for Customer Type.';
1643 x_return_status := FND_API.G_RET_STS_ERROR;
1644 x_otoc_Le_info.legal_entity_id := -1;
1645 return;
1646 end if;
1647 End if;
1648
1649 /* Call the AR API to get the Legal Entity Id*/
1650 l_le_id := arp_legal_entity_util.get_default_le (
1651 p_sold_to_cust_id => P_CUSTOMER_ID,
1652 p_bill_to_cust_id => P_CUSTOMER_ID,
1653 p_trx_type_id => P_TRANSACTION_TYPE_ID ,
1654 p_batch_source_id => P_batch_source_id );
1655
1656 /* If no Legal Entity Id found then returns -1*/
1657 if l_le_id = -1 then
1658 /* Next try to get LE id from Default Legal Context */
1659 l_le_id := xle_utilities_grp.GET_DefaultLegalContext_OU(
1660 P_operating_unit_id);
1661 if l_le_id = -1 then
1662 x_return_status := FND_API.G_RET_STS_ERROR ;
1663 x_msg_data := 'No Legal Entity Found';
1664 x_otoc_Le_info.legal_entity_id := -1;
1665 return;
1666 end if;
1667 end if;
1668
1669 x_otoc_Le_info.legal_entity_id := l_le_id;
1670
1671 x_return_status := FND_API.G_RET_STS_SUCCESS;
1672 EXCEPTION
1673 WHEN OTHERS THEN
1674 x_return_status := FND_API.G_RET_STS_ERROR ;
1675
1676 END Get_OrdertoCash_Info ;
1677
1678 /*============================================+
1679 | FUNCTION Get_OrdertoCash_Info
1680 |
1681 | DESCRIPTION
1682 | Public Procedure which returns the legal entity id as a NUMBER
1683 |
1684 | IN parameters:
1685 | P_customer_type VARCHAR optional 'SOLD_TO' or 'BILL_TO'
1686 | P_customer_id NUMBER optional
1687 | P_transaction_type_id NUMBER optional
1688 | P_batch_source_id NUMBER optional
1689 | P_operating_unit_id NUMBER mandatory
1690 |
1691 | OUT parameters:
1692 | P_legal_entity_id NUMBER -1 if not found
1693 |
1694 | If returned legal entity id is -1, then the legal entity could not be
1695 | found. An error is raised, with an error message in this case describing
1696 | the problem. Please see other version of this method for more details.
1697 |
1698 | DEV NOTE: This Procedure is in fact a wrapper for the other
1699 | Get_OrdertoCash_Info which returns a otoc_le_info record.
1700 |
1701 | This procedures takes the otoc_le_info record and extracts the legal
1702 | entity Id from it and returns it.
1703 |
1704 | This was a request from another product team as they required only the
1705 | legal entity id and not the whole record.
1706 |
1707 | This function signature-without out parameters can be used in sql statement.
1708 |
1709 | MODIFICATION HISTORY
1710 | DATE Author Description of Changes
1711 | 21-Jun-2005 N Foley Created
1712 | 23-Jun-2005 N Foley Changed to function
1713 | 27-Sep-2005 R Basker Bug 4635044: PL/SQL functions referenced by SQL
1714 | statements must not contain the OUT parameter.
1715 *===========================================================================*/
1716 FUNCTION Get_OrdertoCash_Info(
1717 P_customer_type IN VARCHAR2 DEFAULT NULL,
1718 P_customer_id IN NUMBER DEFAULT NULL,
1719 P_transaction_type_id IN NUMBER DEFAULT NULL,
1720 P_batch_source_id IN NUMBER DEFAULT NULL,
1721 P_operating_unit_id IN NUMBER
1722 )
1723 RETURN NUMBER IS
1724
1725 l_le_id NUMBER;
1726 l_return_status varchar2(1);
1727 l_msg_data varchar2(1000);
1728 l_legal_entity_id number;
1729 l_customer_type varchar2(30);
1730 l_customer_id number;
1731 l_transaction_type_id number;
1732 l_batch_source_id number;
1733 l_operating_unit_id number;
1734 l_otoc_le_info XLE_BUSINESSINFO_GRP.otoc_le_rec;
1735
1736 BEGIN
1737
1738 l_customer_type := P_customer_type;
1739 l_customer_id := P_customer_id;
1740 l_transaction_type_id := P_transaction_type_id;
1741 l_batch_source_id := P_batch_source_id;
1745 otoc_Le_info record*/
1742 l_operating_unit_id := P_operating_unit_id;
1743
1744 /* Call the main Get_OrdertoCash_Info method that returns the
1746
1747 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info (
1748 x_return_status => l_return_status,
1749 x_msg_data => l_msg_data,
1750 P_customer_type => l_customer_type,
1751 P_customer_id => l_customer_id,
1752 P_transaction_type_id => l_transaction_type_id,
1753 P_batch_source_id => l_batch_source_id,
1754 P_operating_unit_id => l_operating_unit_id,
1755 x_otoc_Le_info => l_otoc_le_info);
1756
1757 -- if any error occurs propagate as unexpected error
1758
1759 -- Commenting for bug 5159735
1760 /*
1761 IF l_return_status = FND_API.G_RET_STS_ERROR OR
1762 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1763 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1764 END IF ;
1765
1766 */
1767 /* If the otoc_Le_info is returned just get the Legal Entity Id*/
1768 if l_otoc_le_info.legal_entity_id IS NOT NULL then
1769 return l_otoc_le_info.legal_entity_id;
1770 else
1771 return -1;
1772 end if;
1773
1774 END Get_OrdertoCash_Info ;
1775
1776 /*============================================+
1777 | FUNCTION Get_OrdertoCash_Info
1778 |
1779 | DESCRIPTION
1780 | Public Procedure which returns the legal entity id as a NUMBER
1781 |
1782 | IN parameters:
1783 | P_customer_type VARCHAR optional 'SOLD_TO' or 'BILL_TO'
1784 | P_customer_id NUMBER optional
1785 | P_transaction_type_id NUMBER optional
1786 | P_batch_source_id NUMBER optional
1787 | P_operating_unit_id NUMBER mandatory
1788 |
1789 | OUT parameters:
1790 | P_legal_entity_id NUMBER -1 if not found
1791 | x_return_status VARCHAR 'E' for error,'S' for sucess
1792 | x_msg_data VARCHAR error message
1793 |
1794 | If returned legal entity id is -1, then the legal entity could not be
1795 | found. An error is raised, with an error message in this case describing the
1796 | problem.
1797 | Please see other version of this method for more details.
1798 |
1799 | DEV NOTE: This Procedure is in fact a wrapper for the other Get_OrdertoCash_Info
1800 | which returns a otoc_le_info record. This procedures takes the otoc_le_info
1801 | record and extracts the legal entity Id from it and returns it. This was
1802 | a request from another product team as they required only the legal entity id
1803 | and not the whole record.
1804 | This function signature-with out parameters can be used in any where except in
1805 | sql statement.
1806 |
1807 | MODIFICATION HISTORY
1808 | DATE Author Description of Changes
1809 | 21-Oct-2005 spasupun Bgu : 4690944 Added the function overloaded with OUT
1810 | parameters.
1811 *===========================================================================*/
1812 FUNCTION Get_OrdertoCash_Info(
1813 x_return_status OUT NOCOPY VARCHAR2 ,
1814 x_msg_data OUT NOCOPY VARCHAR2 ,
1815 P_customer_type IN VARCHAR2 DEFAULT NULL,
1816 P_customer_id IN NUMBER DEFAULT NULL,
1817 P_transaction_type_id IN NUMBER DEFAULT NULL,
1818 P_batch_source_id IN NUMBER DEFAULT NULL,
1819 P_operating_unit_id IN NUMBER
1820 )
1821 RETURN NUMBER IS
1822 l_le_id NUMBER;
1823 l_return_status varchar2(1);
1824 l_msg_data varchar2(1000);
1825 l_legal_entity_id number;
1826 l_customer_type varchar2(30);
1827 l_customer_id number;
1828 l_transaction_type_id number;
1829 l_batch_source_id number;
1830 l_operating_unit_id number;
1831 l_otoc_le_info XLE_BUSINESSINFO_GRP.otoc_le_rec;
1832
1833 BEGIN
1834 l_customer_type := P_customer_type;
1835 l_customer_id := P_customer_id;
1836 l_transaction_type_id := P_transaction_type_id;
1837 l_batch_source_id := P_batch_source_id;
1838 l_operating_unit_id := P_operating_unit_id;
1839
1840 /* Call the main Get_OrdertoCash_Info method that returns the otoc_Le_info record*/
1841 XLE_BUSINESSINFO_GRP.Get_OrdertoCash_Info (
1842 x_return_status => l_return_status,
1843 x_msg_data => l_msg_data,
1844 P_customer_type => l_customer_type,
1845 P_customer_id => l_customer_id,
1846 P_transaction_type_id => l_transaction_type_id,
1847 P_batch_source_id => l_batch_source_id,
1848 P_operating_unit_id => l_operating_unit_id,
1849 x_otoc_Le_info => l_otoc_le_info);
1850
1851 if l_return_status = FND_API.G_RET_STS_ERROR then
1852 x_return_status := l_return_status ;
1853 x_msg_data := l_msg_data;
1854 RETURN -1;
1855 end if;
1856
1857
1858 /* If the otoc_Le_info is returned just get the Legal Entity Id*/
1859 if l_otoc_le_info.legal_entity_id IS NOT NULL then
1860 return l_otoc_le_info.legal_entity_id;
1861
1862 else
1863 x_return_status := FND_API.G_RET_STS_ERROR ;
1864 x_msg_data := 'No Legal Entity Found';
1865 l_le_id := -1;
1866 return -1;
1867 end if;
1868
1869 EXCEPTION
1870 WHEN OTHERS THEN
1871 x_return_status := FND_API.G_RET_STS_ERROR ;
1872
1875 END XLE_BUSINESSINFO_GRP;1873 END Get_OrdertoCash_Info ;
1874
[Home] [Help]