1 PACKAGE BODY ARP_LEGAL_ENTITY_UTIL AS
2 /* $Header: ARXLEUTB.pls 120.8 2006/06/05 15:27:03 rkader noship $ */
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 /*============================================+
173 | FUNCTION Get_Default_LE_ID
174 |
175 | DESCRIPTION
176 | Public Function which returns the default legal entity ID
177 | based on org_id and sob_id. It will only return one
178 | entity. If there is more than one (shared), then it will return
179 | a -1
180 |
181 | PARAMETERS
182 | p_org_id NUMBER
183 | p_sob_id NUMBER
184 |
185 | MODIFICATION HISTORY
186 | DATE Author Description of Changes
187 | 19-May-2005 MRAYMOND Created
188 |
189 *===========================================================================*/
190
191 FUNCTION Get_Default_LE_ID(p_org_id NUMBER,
192 p_sob_id NUMBER ) RETURN NUMBER IS
193
194 l_legal_entity_id r_legal_entity_id;
195
196 BEGIN
197 IF PG_DEBUG in ('Y', 'C') THEN
198 arp_standard.debug('arp_legal_entity_util.Get_Default_LE_ID()+');
199 arp_standard.debug(' parms: ' ||p_org_id||':'||
200 p_sob_id);
201 END IF;
202
203 IF p_org_id = g_org_id AND p_sob_id = g_sob_id
204 THEN
205 IF PG_DEBUG in ('Y', 'C') THEN
206 arp_standard.debug(' Returning cached LE : ' || g_le_id);
207 END IF;
208 ELSE
209 g_org_id := p_org_id;
210 g_sob_id := p_sob_id;
211
212 Select legal_entity_id
213 BULK COLLECT INTO
214 l_legal_entity_id
215 from xle_le_ou_ledger_v
216 where ledger_id = p_sob_id and
217 operating_unit_id = p_org_id;
218
219 IF (l_legal_entity_id.COUNT = 1) THEN
220 IF PG_DEBUG in ('Y', 'C') THEN
221 arp_standard.debug(' Caching and returning LE : ' || g_le_id);
222 END IF;
223 g_le_id := l_legal_entity_id(1);
224 ELSE
225 IF PG_DEBUG in ('Y', 'C') THEN
226 arp_standard.debug(' No default LE available, returning -1');
227 END IF;
228
229 g_le_id := -1;
230 END IF;
231 END IF;
232
233 IF PG_DEBUG in ('Y', 'C') THEN
234 arp_standard.debug('arp_legal_entity_util.get_default_le_id()-');
235 END IF;
236
237 RETURN g_le_id;
238
239 END Get_Default_LE_ID;
240
241 /*============================================+
242 | FUNCTION Get_Default_LE_Name
243 |
244 | DESCRIPTION
245 | Public Function which returns the default legal entity name
246 | if there is only 1 Le returned from the Legal Entity View.
247 |
248 | PARAMETERS
249 | p_org_id NUMBER
250 | p_sob_id NUMBER
251 |
252 | MODIFICATION HISTORY
253 | DATE Author Description of Changes
254 | 13-May-2005 Debbie Sue Jancis Created
255 |
256 *===========================================================================*/
257
258 FUNCTION Get_Default_LE_Name(p_org_id NUMBER,
259 p_sob_id NUMBER ) RETURN VARCHAR2 IS
260
261 l_legal_entity_name r_legal_entity_name;
262
263 BEGIN
264 IF PG_DEBUG in ('Y', 'C') THEN
265 arp_standard.debug('arp_legal_entity_util.Get_Default_LE_NAme()+');
266 arp_standard.debug(' parms: ' ||p_org_id||':'||
267 p_sob_id);
268 END IF;
269
270 Select legal_entity_name
271 BULK COLLECT INTO
272 l_legal_entity_name
273 from xle_le_ou_ledger_v
274 where ledger_id = p_sob_id and
275 operating_unit_id = p_org_id;
276
277 IF (l_legal_entity_name.COUNT = 1) THEN
278 RETURN l_legal_entity_name(1);
279 ELSE
280 RETURN NULL;
281 END IF;
282
283 END Get_Default_LE_Name;
284
285 /*============================================+
286 | FUNCTION Is_LE_Subscriber
287 |
288 | DESCRIPTION
289 | Public Function which returns True if the Subscriber is LE
290 | and FALSE if the Subscriber is OU
291 |
292 | PARAMETERS
293 |
294 | MODIFICATION HISTORY
295 | DATE Author Description of Changes
296 | 02-Sep-2005 Debbie Sue Jancis Created
297 |
298 *===========================================================================*/
299
300 FUNCTION Is_LE_Subscriber RETURN BOOLEAN IS
301 l_subscriber_flag VARCHAR2(1);
302 BEGIN
303 IF PG_DEBUG IN ('Y','C')
304 THEN
305 arp_standard.debug('arp_legal_entity_util.Is_LE_Subscriber()+' );
306 END IF;
307
308 SELECT USE_LE_AS_SUBSCRIBER_FLAG
309 INTO l_subscriber_flag
310 FROM zx_party_tax_profile
311 WHERE PARTY_TYPE_CODE = 'OU'
312 AND PARTY_ID = arp_global.sysparam.org_id;
313
314 IF (l_subscriber_flag = 'Y') THEN
315 RETURN TRUE;
316 ELSE
317 RETURN FALSE;
318 END IF;
319 /* Bug fix 5253720 : Introduced the exception block */
320 EXCEPTION
321 WHEN NO_DATA_FOUND THEN
322 IF PG_DEBUG IN ('Y','C')
323 THEN
324 arp_standard.debug('NO_DATA_FOUND EXCETION : arp_legal_entity_util.Is_LE_Subscriber()');
325 END IF;
326 fnd_message.set_name('AR','AR_OU_PARTY_NOT_EXIST');
327 fnd_message.set_token('ORG_ID',arp_global.sysparam.org_id);
328 app_exception.raise_exception;
329 WHEN OTHERS THEN
330 IF PG_DEBUG IN ('Y','C')
331 THEN
332 arp_standard.debug('OTHERS EXCETION : arp_legal_entity_util.Is_LE_Subscriber()');
333 END IF;
334 RAISE;
335 END Is_LE_Subscriber;
336
337 /*============================================+
338 | FUNCTION get_default_legal_context
339 |
340 | DESCRIPTION
341 | Public Function which returns the default legal entity
342 | as the default_legal_context from HR_ORGANIZATIONS. Each
343 | org will have one and only one DLC, but it may or may not
344 | be the correct legal entity for a transaction. No effort
345 | is made by this function to determine if there are more
346 | LEs, if LE is the subscriber, etc.
347 |
348 | This function just returns what HR_ORGANIZATIONS has in
349 | default_legal_context_id column.
350 |
351 | PARAMETERS
352 | p_org_id NUMBER
353 |
354 | MODIFICATION HISTORY
355 | DATE Author Description of Changes
356 | 01-JUN-2006 MRAYMOND Created
357 |
358 *===========================================================================*/
359
360 FUNCTION get_default_legal_context(p_org_id NUMBER) RETURN NUMBER IS
361
362 l_legal_entity_id NUMBER;
363
364 BEGIN
365 IF PG_DEBUG in ('Y', 'C') THEN
366 arp_standard.debug('arp_legal_entity_util.get)default_legal_context()+');
367 arp_standard.debug(' p_org_id: ' ||p_org_id);
368 END IF;
369
370 IF NVL(g_org_id_dlc, -999) = p_org_id
371 THEN
372 l_legal_entity_id := g_le_id_dlc;
373 ELSE
374 /* Not cached, go get DLC, set cache value */
375 g_org_id_dlc := p_org_id;
376
377 select default_legal_context_id
378 into l_legal_entity_id
379 from hr_operating_units
380 where organization_id = p_org_id;
381
382 g_le_id_dlc := l_legal_entity_id;
383 END IF;
384
385 IF PG_DEBUG in ('Y', 'C') THEN
386 arp_standard.debug(' legal_entity_id [dlc]: ' || l_legal_entity_id);
387 arp_standard.debug('arp_legal_entity_util.get_default_legal_context()-');
388 END IF;
389
390 RETURN l_legal_entity_id;
391
392 END get_default_legal_context;
393
394 END ARP_Legal_Entity_Util;
[Home] [Help]