1 PACKAGE BODY ARP_LEGAL_ENTITY_UTIL AS
2 /* $Header: ARXLEUTB.pls 120.8.12010000.4 2009/05/27 09:42:23 rvelidi ship $ */
3 /*=======================================================================+
4 | Package Globals
5 +=======================================================================*/
6
7 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
8
9 /*========================================================================
10 | Prototype Declarations Procedures
11 *=======================================================================*/
12
13 /*============================================+
14 | FUNCTION Get_LE_Name()
15 |
16 | DESCRIPTION
17 | Public Function which will get the LE Name based upon
18 | the legal entity id
19 |
20 | PARAMETERS
21 |
22 | MODIFICATION HISTORY
23 | DATE Author Description of Changes
24 | 14-Apr-2005 Debbie Sue Jancis Created
25 |
26 *===========================================================================*/
27 FUNCTION Get_LE_Name (p_legal_entity_id NUMBER,
28 p_sob_id NUMBER ) RETURN VARCHAR2 IS
29
30 l_legal_entity_name VARCHAR2(240);
31
32 BEGIN
33
34 Select Legal_entity_name
35 INTO l_legal_entity_name
36 FROM GL_LEDGER_LE_V
37 WHERE legal_entity_id = p_legal_entity_id
38 AND ledger_id = p_sob_id;
39
40 RETURN l_legal_entity_name;
41
42 END Get_LE_Name;
43
44 /*============================================+
45 | FUNCTION get_default_le
46 |
47 | DESCRIPTION
48 | Public Function which returns the default legal_entity_id
49 | for a transaction based on its sold to, bill to custs,
50 | trx_type or finally batch_source.
51 |
52 | p_sold_to_cust NUMBER
53 | p_bill_to_cust NUMBER
54 | p_trx_type_id NUMBER
55 | p_batch_source_id NUMBER
56 |
57 | RETURNS legal_entity_id NUMBER
58 | IF returned number is -1, then we were not able
59 | to determine the default value. An error may need to
60 | be raised by the calling program in this situation.
61 |
62 | DEV NOTE: This API was designed as a function to make it useful
63 | for quick calls from forms and plsql. It was not intended
64 | for batch processing or high volumn processes. In those cases,
65 | the defaulting logic should mimic what is present in
66 | /src/autoinv/raadhd.lpc (single sql statement with multiple
67 | joins.
68 |
69 | It should also be noted that this api returns as soon as it
70 | locates a LE. However, if the customer always uses a
71 | particular level of default, we could add a profile or system
72 | option that determines the customer preference (checks it first)
73 | before rolling through all the defaults in order. This would
74 | clearly be more efficient than testing each level each time
75 | the api is called.
76 |
77 | MODIFICATION HISTORY
78 | DATE Author Description of Changes
79 | 28-Apr-2005 Michael S. Raymond Created
80 |
81 *===========================================================================*/
82 FUNCTION get_default_le(p_sold_to_cust_id NUMBER,
83 p_bill_to_cust_id NUMBER,
84 p_trx_type_id NUMBER,
85 p_batch_source_id NUMBER)
86 RETURN NUMBER IS
87
88 l_legal_entity_id NUMBER := -1;
89
90 BEGIN
91 IF PG_DEBUG in ('Y', 'C') THEN
92 arp_standard.debug('arp_legal_entity_util.get_default_le()+');
93 arp_standard.debug(' parms:'||p_sold_to_cust_id||':'||
94 p_bill_to_cust_id||':'||
95 p_trx_type_id||':'||
96 p_batch_source_id);
97 END IF;
98
99 /* This API needs to return the LE from one of the
100 five possible sources. */
101
102 /* Get from org (if there is just one) */
103
104 l_legal_entity_id := get_default_le_id(arp_standard.sysparm.org_id,
105 arp_standard.sysparm.set_of_books_id);
106
107 IF l_legal_entity_id <> -1
108 THEN
109 RETURN l_legal_entity_id;
110 END IF;
111
112 /* trx_type */
113 IF p_trx_type_id IS NOT NULL
114 THEN
115 BEGIN
116 SELECT NVL(legal_entity_id,-1)
117 INTO l_legal_entity_id
118 FROM RA_CUST_TRX_TYPES
119 WHERE cust_trx_type_id = p_trx_type_id;
120
121 IF l_legal_entity_id <> -1
122 THEN
123 IF PG_DEBUG in ('Y', 'C') THEN
124 arp_standard.debug(' def from trx_type:' || l_legal_entity_id);
125 END IF;
126 RETURN l_legal_entity_id;
127 END IF;
128
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 arp_standard.debug('EXCEPTION: invalid cust_trx_type_id');
132 /* Invalid trx_type, but keep going */
133 END;
134
135 END IF;
136
137 /* batch source */
138 IF p_batch_source_id IS NOT NULL
139 THEN
140 BEGIN
141 SELECT NVL(legal_entity_id,-1)
142 INTO l_legal_entity_id
143 FROM RA_BATCH_SOURCES
144 WHERE batch_source_id = p_batch_source_id;
145
146 IF l_legal_entity_id <> -1
147 THEN
148 IF PG_DEBUG in ('Y', 'C') THEN
149 arp_standard.debug(' def from batch_source:' || l_legal_entity_id);
150 END IF;
151 RETURN l_legal_entity_id;
152 END IF;
153
154 EXCEPTION
155 WHEN NO_DATA_FOUND THEN
156 arp_standard.debug('EXCEPTION: invalid batch_source_id');
157 /* Invalid batch_source */
158 END;
159
160 END IF;
161
162 IF l_legal_entity_id = -1
163 THEN
164 /* If all else fails, return the default_legal_context */
165 l_legal_entity_id := get_default_legal_context(arp_standard.sysparm.org_id);
166 END IF;
167
168 RETURN l_legal_entity_id;
169
170 END get_default_le;
171
172 -- bug 8516757
173 /* Over loading get_default_le with p_org_id parameter */
174
175
176 FUNCTION get_default_le(p_sold_to_cust_id NUMBER,
177 p_bill_to_cust_id NUMBER,
178 p_trx_type_id NUMBER,
179 p_batch_source_id NUMBER,
180 p_org_id NUMBER)
181
182 RETURN NUMBER IS
183
184 l_legal_entity_id NUMBER := -1;
185 l_org_id NUMBER := p_org_id ;
186 l_org_return_status VARCHAR2(1);
187
188 BEGIN
189 IF PG_DEBUG in ('Y', 'C') THEN
190 arp_standard.debug('arp_legal_entity_util.get_default_le()+');
191 arp_standard.debug(' parms:'||p_sold_to_cust_id||':'||
192 p_bill_to_cust_id||':'||
193 p_trx_type_id||':'||
194 p_batch_source_id||':'||
195 p_org_id);
196 END IF;
197
198
199 IF (p_org_id is NOT NULL and NVL(p_org_id,-1) <> arp_standard.sysparm.org_id )
200 THEN
201 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id ,
202 p_return_status =>l_org_return_status);
203 END IF;
204
205 l_legal_entity_id := ARP_LEGAL_ENTITY_UTIL.get_default_le(p_sold_to_cust_id ,
206 p_bill_to_cust_id,
207 p_trx_type_id ,
208 p_batch_source_id);
209
210 return l_legal_entity_id;
211
212 END get_default_le;
213
214 /*============================================+
215 | FUNCTION Get_Default_LE_ID
216 |
217 | DESCRIPTION
218 | Public Function which returns the default legal entity ID
219 | based on org_id and sob_id. It will only return one
220 | entity. If there is more than one (shared), then it will return
221 | a -1
222 |
223 | PARAMETERS
224 | p_org_id NUMBER
225 | p_sob_id NUMBER
226 |
227 | MODIFICATION HISTORY
228 | DATE Author Description of Changes
229 | 19-May-2005 MRAYMOND Created
230 |
231 *===========================================================================*/
232
233 FUNCTION Get_Default_LE_ID(p_org_id NUMBER,
234 p_sob_id NUMBER ) RETURN NUMBER IS
235
236 l_legal_entity_id r_legal_entity_id;
237
238 BEGIN
239 IF PG_DEBUG in ('Y', 'C') THEN
240 arp_standard.debug('arp_legal_entity_util.Get_Default_LE_ID()+');
241 arp_standard.debug(' parms: ' ||p_org_id||':'||
242 p_sob_id);
243 END IF;
244
245 IF p_org_id = g_org_id AND p_sob_id = g_sob_id
246 THEN
247 IF PG_DEBUG in ('Y', 'C') THEN
248 arp_standard.debug(' Returning cached LE : ' || g_le_id);
249 END IF;
250 ELSE
251 g_org_id := p_org_id;
252 g_sob_id := p_sob_id;
253
254 Select legal_entity_id
255 BULK COLLECT INTO
256 l_legal_entity_id
257 from xle_le_ou_ledger_v
258 where ledger_id = p_sob_id and
259 operating_unit_id = p_org_id;
260
261 IF (l_legal_entity_id.COUNT = 1) THEN
262 IF PG_DEBUG in ('Y', 'C') THEN
263 arp_standard.debug(' Caching and returning LE : ' || g_le_id);
264 END IF;
265 g_le_id := l_legal_entity_id(1);
266 ELSE
267 IF PG_DEBUG in ('Y', 'C') THEN
268 arp_standard.debug(' No default LE available, returning -1');
269 END IF;
270
271 g_le_id := -1;
272 END IF;
273 END IF;
274
275 IF PG_DEBUG in ('Y', 'C') THEN
276 arp_standard.debug('arp_legal_entity_util.get_default_le_id()-');
277 END IF;
278
279 RETURN g_le_id;
280
281 END Get_Default_LE_ID;
282
283 /*============================================+
284 | FUNCTION Get_Default_LE_Name
285 |
286 | DESCRIPTION
287 | Public Function which returns the default legal entity name
288 | if there is only 1 Le returned from the Legal Entity View.
289 |
290 | PARAMETERS
291 | p_org_id NUMBER
292 | p_sob_id NUMBER
293 |
294 | MODIFICATION HISTORY
295 | DATE Author Description of Changes
296 | 13-May-2005 Debbie Sue Jancis Created
297 |
298 *===========================================================================*/
299
300 FUNCTION Get_Default_LE_Name(p_org_id NUMBER,
301 p_sob_id NUMBER ) RETURN VARCHAR2 IS
302
303 l_legal_entity_name r_legal_entity_name;
304
305 BEGIN
306 IF PG_DEBUG in ('Y', 'C') THEN
307 arp_standard.debug('arp_legal_entity_util.Get_Default_LE_NAme()+');
308 arp_standard.debug(' parms: ' ||p_org_id||':'||
309 p_sob_id);
310 END IF;
311
312 Select legal_entity_name
313 BULK COLLECT INTO
314 l_legal_entity_name
315 from xle_le_ou_ledger_v
316 where ledger_id = p_sob_id and
317 operating_unit_id = p_org_id;
318
319 IF (l_legal_entity_name.COUNT = 1) THEN
320 RETURN l_legal_entity_name(1);
321 ELSE
322 RETURN NULL;
323 END IF;
324
325 END Get_Default_LE_Name;
326
327 /*============================================+
328 | FUNCTION Is_LE_Subscriber
329 |
330 | DESCRIPTION
331 | Public Function which returns True if the Subscriber is LE
332 | and FALSE if the Subscriber is OU
333 |
334 | PARAMETERS
335 |
336 | MODIFICATION HISTORY
337 | DATE Author Description of Changes
338 | 02-Sep-2005 Debbie Sue Jancis Created
339 |
340 *===========================================================================*/
341
342 FUNCTION Is_LE_Subscriber RETURN BOOLEAN IS
343 l_subscriber_flag VARCHAR2(1);
344 BEGIN
345 IF PG_DEBUG IN ('Y','C')
346 THEN
347 arp_standard.debug('arp_legal_entity_util.Is_LE_Subscriber()+' );
348 END IF;
349
350 SELECT USE_LE_AS_SUBSCRIBER_FLAG
351 INTO l_subscriber_flag
352 FROM zx_party_tax_profile
353 WHERE PARTY_TYPE_CODE = 'OU'
354 AND PARTY_ID = arp_global.sysparam.org_id;
355
356 IF (l_subscriber_flag = 'Y') THEN
357 RETURN TRUE;
358 ELSE
359 RETURN FALSE;
360 END IF;
361 /* Bug fix 5253720 : Introduced the exception block */
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 IF PG_DEBUG IN ('Y','C')
365 THEN
366 arp_standard.debug('NO_DATA_FOUND EXCETION : arp_legal_entity_util.Is_LE_Subscriber()');
367 END IF;
368 fnd_message.set_name('AR','AR_OU_PARTY_NOT_EXIST');
369 fnd_message.set_token('ORG_ID',arp_global.sysparam.org_id);
370 app_exception.raise_exception;
371 WHEN OTHERS THEN
372 IF PG_DEBUG IN ('Y','C')
373 THEN
374 arp_standard.debug('OTHERS EXCETION : arp_legal_entity_util.Is_LE_Subscriber()');
375 END IF;
376 RAISE;
377 END Is_LE_Subscriber;
378
379 /*============================================+
380 | FUNCTION get_default_legal_context
381 |
382 | DESCRIPTION
383 | Public Function which returns the default legal entity
384 | as the default_legal_context from HR_ORGANIZATIONS. Each
385 | org will have one and only one DLC, but it may or may not
386 | be the correct legal entity for a transaction. No effort
387 | is made by this function to determine if there are more
388 | LEs, if LE is the subscriber, etc.
389 |
390 | This function just returns what HR_ORGANIZATIONS has in
391 | default_legal_context_id column.
392 |
393 | PARAMETERS
394 | p_org_id NUMBER
395 |
396 | MODIFICATION HISTORY
397 | DATE Author Description of Changes
398 | 01-JUN-2006 MRAYMOND Created
399 |
400 *===========================================================================*/
401
402 FUNCTION get_default_legal_context(p_org_id NUMBER) RETURN NUMBER IS
403
404 l_legal_entity_id NUMBER;
405
406 BEGIN
407 IF PG_DEBUG in ('Y', 'C') THEN
408 arp_standard.debug('arp_legal_entity_util.get)default_legal_context()+');
409 arp_standard.debug(' p_org_id: ' ||p_org_id);
410 END IF;
411
412 IF NVL(g_org_id_dlc, -999) = p_org_id
413 THEN
414 l_legal_entity_id := g_le_id_dlc;
415 ELSE
416 /* Not cached, go get DLC, set cache value */
417 g_org_id_dlc := p_org_id;
418
419 select default_legal_context_id
420 into l_legal_entity_id
421 from hr_operating_units
422 where organization_id = p_org_id;
423
424 g_le_id_dlc := l_legal_entity_id;
425 END IF;
426
427 IF PG_DEBUG in ('Y', 'C') THEN
428 arp_standard.debug(' legal_entity_id [dlc]: ' || l_legal_entity_id);
429 arp_standard.debug('arp_legal_entity_util.get_default_legal_context()-');
430 END IF;
431
432 RETURN l_legal_entity_id;
433
434 END get_default_legal_context;
435
436 END ARP_Legal_Entity_Util;
[Home] [Help]