[Home] [Help]
PACKAGE BODY: APPS.XLA_DRILLDOWN_PKG
Source
1 PACKAGE BODY xla_drilldown_pkg AS
2 /* $Header: xlaiqdrl.pkb 120.17.12020000.3 2012/07/30 13:14:54 sragadde ship $ */
3 /*==========================================================================+
4 | Copyright (c) 2000-2001 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | PACKAGE NAME |
9 | xla_drilldown_pkg |
10 | |
11 | DESCRIPTION |
12 | This Package is a PL/SQL wrapper to the GL Team for Drilldown |
13 | specific Procedures. |
14 | HISTORY |
15 | 14-MAR-05 Kprattip Created |
16 | |
17 | 31-JUL-05 V. Swapna Bug 4494345: Modified open_drilldown to |
18 | use je_from_sla flag to determine if an |
19 | entry originated from SLA. |
20 | |
21 | 08-FEB-06 V. Swapna Bug 5009082: Modification to procedures |
22 | check_drilldown and open_drilldown to |
23 | handle the values 'U' and 'N', for |
24 | je_from_sla_flag. |
25 +==========================================================================*/
26
27
28 /*==========================================================================+
29 | |
30 | Private Function |
31 | |
32 | is_FA_drilldown |
33 | |
34 +==========================================================================*/
35
36
37
38 FUNCTION is_FA_drilldown (
39 p_je_header_id NUMBER
40 ,p_je_source VARCHAR2
41 ,p_je_category VARCHAR2 ) RETURN BOOLEAN
42
43 IS
44
45
46 CURSOR fab_c ( c_jeh_id NUMBER,
47 c_je_source VARCHAR2,
48 c_je_category VARCHAR2) IS
49
50 SELECT gl_je_source
51 FROM fa_book_controls bc,
52 gl_je_headers jeh
53 WHERE jeh.je_header_id = c_jeh_id
54 AND bc.set_of_books_id = jeh.ledger_id
55 AND bc.gl_je_source = c_je_source
56 AND c_je_category IN (
57 bc.JE_RETIREMENT_CATEGORY
58 ,bc.JE_RECLASS_CATEGORY
59 ,bc.JE_ADDITION_CATEGORY
60 ,bc.JE_ADJUSTMENT_CATEGORY
61 ,bc.JE_REVAL_CATEGORY
62 ,bc.JE_TRANSFER_CATEGORY
63 ,bc.JE_CIP_ADJUSTMENT_CATEGORY
64 ,bc.JE_CIP_ADDITION_CATEGORY
65 ,bc.JE_CIP_RECLASS_CATEGORY
66 ,bc.JE_CIP_RETIREMENT_CATEGORY
67 ,bc.JE_CIP_REVAL_CATEGORY
68 ,bc.JE_CIP_TRANSFER_CATEGORY )
69 AND ROWNUM = 1;
70
71 l_FA_drilldown_flag BOOLEAN := FALSE;
72 l_dummy VARCHAR2(30);
73
74 BEGIN
75
76 OPEN fab_c ( p_je_header_id, p_je_source, p_je_category);
77 FETCH fab_c INTO l_dummy;
78
79 IF fab_c%FOUND THEN
80 l_FA_drilldown_flag := TRUE;
81 ELSE
82 l_FA_drilldown_flag := jg_zz_fa_drill_down_pkg.is_jg_fa_drilldown
83 (p_je_header_id,
84 p_je_source,
85 p_je_category);
86 END IF;
87
88 CLOSE fab_c;
89
90 RETURN ( l_FA_drilldown_flag );
91
92 END is_FA_drilldown;
93
94
95
96 /*==========================================================================+
97 | |
98 | Public Procedure |
99 | |
100 | check_drilldown |
101 | |
102 +==========================================================================*/
103
104 PROCEDURE check_drilldown (
105 p_je_source VARCHAR2
106 ,p_je_category VARCHAR2
107 ,p_je_header_id NUMBER
108 ,p_je_line_num NUMBER
109 ,p_drilldown_flag OUT NOCOPY VARCHAR2
110 ,p_application_id OUT NOCOPY NUMBER )
111
112 IS
113
114 AR_APPL CONSTANT NUMBER(3) := 222;
115 FA_APPL CONSTANT NUMBER(3) := 140;
116 PA_APPL CONSTANT NUMBER(3) := 275;
117 INV_APPL CONSTANT NUMBER(3) := 401;
118 WIP_APPL CONSTANT NUMBER(3) := 706;
119 PO_APPL CONSTANT NUMBER(3) := 201;
120 OZF_APPL CONSTANT NUMBER(4) := 682;
121 FED_APPL CONSTANT NUMBER(4) := 8901;
122 OKL_APPL CONSTANT NUMBER(3) := 540;
123 AP_APPL CONSTANT NUMBER(3) := 200;
124
125 l_drilldown_flag VARCHAR2(1):= 'N';
126 l_appl_id NUMBER(15) := NULL;
127 l_actual_flag VARCHAR2(1);
128 l_je_source_name VARCHAR2(30);
129 l_je_from_sla_flag VARCHAR2(1);
130
131 --
132 -- Cursor declaration
133 --
134 CURSOR c_xla_subledgers
135 IS
136 SELECT application_id, je_source_name
137 FROM xla_subledgers
138 WHERE je_source_name = p_je_source;
139
140 CURSOR c_je_from_sla_flag
141 IS
142 SELECT je_from_sla_flag
143 FROM gl_je_headers
144 WHERE je_header_id = p_je_header_id;
145
146 BEGIN
147 OPEN c_xla_subledgers;
148 FETCH c_xla_subledgers INTO l_appl_id, l_je_source_name;
149 CLOSE c_xla_subledgers;
150
151 OPEN c_je_from_sla_flag;
152 FETCH c_je_from_sla_flag INTO l_je_from_sla_flag;
153 CLOSE c_je_from_sla_flag;
154
155 IF l_je_from_sla_flag IN ('Y','U') THEN
156 l_drilldown_flag := 'Y';
157 ELSIF l_je_from_sla_flag = 'N' THEN -- Bug 5009082
158 l_drilldown_flag := 'N';
159 ELSE
160 IF ( p_je_source = 'Receivables') THEN
161 IF ( p_je_category IN ('Sales Invoices', 'Credit Memos',
162 'Credit Memo Applications',
163 'Debit Memos', 'Chargebacks',
164 'Misc Receipts', 'Trade Receipts',
165 'Rate Adjustments', 'Cross Currency',
166 'Adjustment', 'Bills Receivable' ) ) THEN
167
168 l_drilldown_flag := 'Y';
169 l_appl_id := AR_APPL;
170 END IF;
171
172 ELSIF ( p_je_Source = 'Lease' ) AND
173 ( p_je_category IN ( 'Termination', 'Asset Disposition',
174 'Booking', 'Rebook', 'Renewal',
175 'Release', 'Reverse',
176 'Syndication', 'Loss Provision',
177 'Adjustment', 'Accrual',
178 'Miscellaneous', 'Accrual',
179 'Adjustment', 'Asset Disposition',
180 'Booking', 'Loss Provision',
181 'Miscellaneous', 'Rebook',
182 'Release', 'Renewal',
183 'Reverse', 'Syndication and Termination')) THEN
184 l_drilldown_flag := 'Y';
185 l_appl_id := OKL_APPL;
186
187 ELSIF ( p_je_source = 'Project Accounting' AND
188 ( p_je_category IN ( 'Labor Cost', 'Miscellaneous Transaction',
189 'Revenue', 'Total Burdened Cost',
190 'Usage Cost','BORROWED_AND_LENT',
191 'PROVIDER_COST_RECLASS' ) ) ) THEN
192 l_drilldown_flag := 'Y';
193 l_appl_id := PA_APPL;
194
195 ELSIF ( p_je_source = 'Inventory' ) THEN
196
197 -- Both INV and WIP Transactions have the same GL_JE_SOURCE of Inventory
198
199 IF ( p_je_category IN ( 'MTL' ) ) THEN
200
201 l_drilldown_flag := 'Y';
202 l_appl_id := INV_APPL;
203
204 ELSIF ( p_je_category IN ( 'WIP' ) ) THEN
205 l_drilldown_flag := 'Y';
206 l_appl_id := WIP_APPL;
207 END IF;
208
209 ELSIF ( p_je_source = 'Purchasing' AND
210 ( p_je_category IN ( 'Receiving', 'Requisitions', 'Purchases'))) THEN
211 l_drilldown_flag := 'Y';
212 l_appl_id := PO_APPL;
213
214 ELSIF ( p_je_source = 'Periodic Inventory' ) THEN
215
216 -- Purchasing, INV and WIP Transactions for PAC have the same
217 -- GL_JE_SOURCE of Periodic Inventory
218 IF ( p_je_category IN ( 'MTL' ) ) THEN
219 l_drilldown_flag := 'Y';
220 l_appl_id := INV_APPL;
221 ELSIF ( p_je_category IN ( 'WIP' ) ) THEN
222 l_drilldown_flag := 'Y';
223 l_appl_id := WIP_APPL;
224 ELSIF ( p_je_category IN ( 'Receiving', 'Accrual' ) ) THEN
225 l_drilldown_flag := 'Y';
226 l_appl_id := PO_APPL;
227 END IF;
228
229 ELSIF ( p_je_source = 'Budgetary Transaction' ) THEN
230 l_drilldown_flag := 'Y';
231 l_appl_id := FED_APPL;
232
233 ELSIF ( p_je_source = 'PYA Transactions' AND
234 ( p_je_category IN ( 'Upward Adjustments',
235 'Downward Adjustments'))) THEN
236 l_drilldown_flag := 'Y';
237 l_appl_id := FED_APPL;
238
239 ELSIF ( p_je_source = 'Marketing' ) THEN
240
241 IF ( p_je_category IN ('Settlement', 'Claims', 'Deductions',
242 'Fixed Budgets', 'Accrual Budgets' ) ) THEN
243 l_drilldown_flag := 'Y';
244 l_appl_id := OZF_APPL;
245 END IF;
246
247 ELSIF is_FA_drilldown(p_je_header_id, p_je_source, p_je_category) THEN
248
249 l_drilldown_flag := 'Y';
250
251 l_appl_id := FA_APPL;
252
253
254 -- Added for bug 14269921 for upgraded payments
255 ELSIF ( p_je_source = 'Payables' ) THEN
256
257 l_drilldown_flag := 'Y';
258
259 l_appl_id := AP_APPL;
260
261 END IF;
262 END IF;
263
264 -- Return drilldown_flag and application id.
265 p_drilldown_flag := l_drilldown_flag;
266 p_application_id := l_appl_id;
267
268 END check_drilldown;
269
270
271 /*==========================================================================+
272 | |
273 | Public Procedure |
274 | |
275 | open_drilldown |
276 | |
277 +==========================================================================*/
278
279
280 PROCEDURE open_drilldown (
281 p_je_source VARCHAR2
282 ,p_je_header_id NUMBER
283 ,p_je_from_sla_flag VARCHAR2 DEFAULT NULL
284 ,p_form_function IN OUT NOCOPY VARCHAR2
285 ,p_je_line_num IN OUT NOCOPY NUMBER
286 )
287
288 IS
289
290 l_dummy VARCHAR2(1);
291 CURSOR c_reference_6(c_je_header_id in number, c_je_line_num in number)
292 IS
293 SELECT reference_6, reference_10 from gl_je_lines
294 WHERE je_header_id = c_je_header_id
295 AND je_line_num = c_je_line_num;
296
297 l_reference_6 c_reference_6%rowtype;
298 l_je_line_num NUMBER(15);
299
300 CURSOR c_xla IS
301 SELECT application_id
302 FROM xla_subledgers
303 WHERE je_source_name = p_je_source;
304
305
306
307
308 l_appl_id INTEGER;
309 l_je_from_sla_flag VARCHAR2(1);
310 l_count NUMBER := 0 ;
311 l_link_id NUMBER;
312 l_link_table VARCHAR2(20);
313
314 BEGIN
315 OPEN c_xla;
316 FETCH c_xla INTO l_appl_id;
317 CLOSE c_xla;
318
319
320 l_je_from_sla_flag := p_je_from_sla_flag;
321 l_je_line_num := p_je_line_num;
322
323 --7674572 Get the count only if the je_source is payables.
324 IF(p_je_source = 'Payables') THEN
325
326 SELECT count(*) into l_count FROM dual
327 where exists (select 1 from xla_ae_lines ael,gl_import_references gir
328 where ael.gl_sl_link_id = gir.gl_sl_link_id
329 and ael.gl_sl_link_table = gir.gl_sl_link_table
330 and gir.je_header_id = p_je_header_id
331 and gir.je_line_num = p_je_line_num); --included je_line_num join condition to imporve the performance
332
333 END IF;
334
335 IF (l_je_from_sla_flag is NULL) THEN
336
337 SELECT je_from_sla_flag INTO l_je_from_sla_flag FROM gl_je_headers
338 WHERE je_header_id = p_je_header_id;
339
340 END IF;
341
342 IF (l_je_from_sla_flag is NULL AND p_je_source = 'Receivables') THEN
343
344 OPEN c_reference_6 (p_je_header_id, p_je_line_num);
345 FETCH c_reference_6 INTO l_reference_6;
346 CLOSE c_reference_6;
347
348 IF l_reference_6.reference_10 like 'glxfje%' THEN
349 l_je_line_num := to_number(l_reference_6.reference_6);
350 END IF;
351
352 END IF;
353
354 IF (l_appl_id IS NOT NULL AND NVL(l_je_from_sla_flag,'X') IN ('Y','U')) THEN
355 IF (p_je_source <> 'Payables') THEN -- Bug 6658576
356 p_form_function := 'XLA_LINESINQ_GL_DRILLDOWN';
357 ELSE
358 IF (l_count = 1) THEN
359 p_form_function := 'XLA_LINESINQ_GL_DRILLDOWN';
360 END IF;
361 END IF;
362
363 END IF;
364
365 p_je_line_num := l_je_line_num;
366
367 END open_drilldown;
368
369 END xla_drilldown_pkg;