[Home] [Help]
Skip to content
PACKAGE BODY: APPS.XLA_DRILLDOWN_PKG
Source
1 PACKAGE BODY xla_drilldown_pkg AS
2 /* $Header: xlaiqdrl.pkb 120.16 2007/12/21 06:48:27 svellani 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 END IF;
255 END IF;
256
257 -- Return drilldown_flag and application id.
258 p_drilldown_flag := l_drilldown_flag;
259 p_application_id := l_appl_id;
260
261 END check_drilldown;
262
263
264 /*==========================================================================+
265 | |
266 | Public Procedure |
267 | |
268 | open_drilldown |
269 | |
270 +==========================================================================*/
271
272
273 PROCEDURE open_drilldown (
274 p_je_source VARCHAR2
275 ,p_je_header_id NUMBER
276 ,p_je_from_sla_flag VARCHAR2 DEFAULT NULL
277 ,p_form_function IN OUT NOCOPY VARCHAR2
278 ,p_je_line_num IN OUT NOCOPY NUMBER
279 )
280
281 IS
282
283 l_dummy VARCHAR2(1);
284 CURSOR c_reference_6(c_je_header_id in number, c_je_line_num in number)
285 IS
286 SELECT reference_6, reference_10 from gl_je_lines
287 WHERE je_header_id = c_je_header_id
288 AND je_line_num = c_je_line_num;
289
290 l_reference_6 c_reference_6%rowtype;
291 l_je_line_num NUMBER(15);
292
293 CURSOR c_xla IS
294 SELECT application_id
295 FROM xla_subledgers
296 WHERE je_source_name = p_je_source;
297
298
299
300
301 l_appl_id INTEGER;
302 l_je_from_sla_flag VARCHAR2(1);
303 l_count NUMBER := 0 ;
304 l_link_id NUMBER;
305 l_link_table VARCHAR2(20);
306
307 BEGIN
308 OPEN c_xla;
309 FETCH c_xla INTO l_appl_id;
310 CLOSE c_xla;
311
312
313 l_je_from_sla_flag := p_je_from_sla_flag;
314 l_je_line_num := p_je_line_num;
315
316
317 SELECT count(*) into l_count FROM dual
318 where exists (select 1 from xla_ae_lines ael,gl_import_references gir
319 where ael.gl_sl_link_id = gir.gl_sl_link_id
320 and ael.gl_sl_link_table = gir.gl_sl_link_table
321 and gir.je_header_id = p_je_header_id);
322
323 IF (l_je_from_sla_flag is NULL) THEN
324
325 SELECT je_from_sla_flag INTO l_je_from_sla_flag FROM gl_je_headers
326 WHERE je_header_id = p_je_header_id;
327
328 END IF;
329
333 FETCH c_reference_6 INTO l_reference_6;
330 IF (l_je_from_sla_flag is NULL AND p_je_source = 'Receivables') THEN
331
332 OPEN c_reference_6 (p_je_header_id, p_je_line_num);
334 CLOSE c_reference_6;
335
336 IF l_reference_6.reference_10 like 'glxfje%' THEN
337 l_je_line_num := to_number(l_reference_6.reference_6);
338 END IF;
339
340 END IF;
341
342 IF (l_appl_id IS NOT NULL AND NVL(l_je_from_sla_flag,'X') IN ('Y','U')) THEN
343 IF (p_je_source <> 'Payables') THEN -- Bug 6658576
344 p_form_function := 'XLA_LINESINQ_GL_DRILLDOWN';
345 ELSE
346 IF (l_count = 1) THEN
347 p_form_function := 'XLA_LINESINQ_GL_DRILLDOWN';
348 END IF;
349 END IF;
350
351 END IF;
352
353 p_je_line_num := l_je_line_num;
354
355 END open_drilldown;
356
357 END xla_drilldown_pkg;