1 PACKAGE BODY JAI_DRILLDOWN_UTILS_PKG AS
2 /* $Header: jai_drilldown_utils_pkg.plb 120.1.12020000.2 2012/10/09 06:48:23 anupgupt ship $ */
3
4
5 /********************************************************************************************************
6 FILENAME : jai_drilldown_utils_pkg.plb
7
8 Created By : Walton
9
10 Created Date : 15-Apr-2010
11
12 Bug : 9311844
13
14 Purpose : This is the drilldown utility package which contain some common used subprograms.
15
16 Called from : OFI code
17
18 --------------------------------------------------------------------------------------------------------
19 CHANGE HISTORY:
20 --------------------------------------------------------------------------------------------------------
21 S.No Date Author and Details
22 --------------------------------------------------------------------------------------------------------
23 1. 2010/04/07 Walton Liu
24 Bug No : 9311844
25 Description : The file is changed for ER GL drilldown
26 Fix Details : http://files.oraclecorp.com/content/MySharedFolders/R12.1.3/TDD/TDD_1213_FIN_JAI_GL_Drilldown.doc
27 Doc Impact : YES
28 Dependencies: YES, refer to Technical Design
29
30 2. 2010/04/15 Xiao Lv
31 Bug No : 9311844
32 Description : The file is changed for ER GL drilldown
33 Fix Details : http://files.oraclecorp.com/content/MySharedFolders/R12.1.3/TDD/TDD_1213_FIN_JAI_GL_Drilldown.doc
34 Doc Impact : YES
35 Dependencies: YES, refer to Technical Design
36
37
38 ***************************************************************************************************************/
39
40 --==========================================================================
41 -- FUNCTION NAME:
42 --
43 -- if_accounted Public
44 --
45 -- DESCRIPTION:
46 --
47 -- This function is used to check if accounting entries for OFI transactions
48 -- was imported into GL journal or not.
49 --
50 -- PARAMETERS:
51 -- In: pv_view_name Name of base view
52 -- pn_transaction_id Identifier of transaction header
53 --
57 -- CHANGE HISTORY:
54 -- DESIGN REFERENCES:
55 -- FDD_R12_1_3_GL_Drilldowndocx
56 --
58 --
59 -- 15-Apr-2010 Walton created
60 --==========================================================================
61 FUNCTION if_accounted (pv_view_name VARCHAR2, pn_transaction_id NUMBER) RETURN BOOLEAN is
62 v_view_name varchar2(80);
63 v_trx_header_id number;
64 v_sql varchar2 (1000);
65 v_result number;
66 BEGIN
67 v_view_name:=pv_view_name;
68 v_trx_header_id:=pn_transaction_id;
69 v_sql:='select count(1) from '||v_view_name||' where TRX_HDR_ID= '||to_char(v_trx_header_id);
70 execute immediate v_sql into v_result;
71
72 if v_result>0 then
73 return true;
74 else
75 return false;
76 end if;
77 EXCEPTION
78 WHEN OTHERS THEN
79 RETURN false;
80 END if_accounted;
81
82 --==========================================================================
83 -- FUNCTION NAME:
84 --
85 -- get_transfer_number Public
86 --
87 -- DESCRIPTION:
88 --
89 -- This function is used to get destination transfer number.
90 --
91 --
92 -- PARAMETERS:
93 -- In: pn_transfer_id Transfer id for Service Tax Distribution
94 -- pn_org_id Org id for accounting entries
95 -- pn_balance Balance for each accounting entries
96 --
97 --
98 -- DESIGN REFERENCES:
99 -- FDD_R12_1_3_GL_Drilldowndocx
100 --
101 -- CHANGE HISTORY:
102 --
103 -- 15-Apr-2010 Xiao created
104 --==========================================================================
105
106 FUNCTION get_transfer_number(pn_transfer_id IN NUMBER
107 , pn_org_id IN NUMBER
108 , pn_balance IN NUMBER) RETURN NUMBER IS
109 ln_transfer_number NUMBER;
110 ln_source_org_id NUMBER;
111
112 CURSOR get_source_org_id_cur IS
113 SELECT party_id
114 FROM jai_rgm_dis_src_hdrs
115 WHERE transfer_id = pn_transfer_id;
116
117 BEGIN
118
119 OPEN get_source_org_id_cur;
120 FETCH get_source_org_id_cur INTO ln_source_org_id;
121 CLOSE get_source_org_id_cur;
122
123 IF ( pn_org_id = ln_source_org_id) THEN -- 'Distribution Out'
124 -- fetch transfer_number by pn_balance, if get multi records, return NULL
125 SELECT DISTINCT transfer_number
126 INTO ln_transfer_number
127 FROM jai_rgm_dis_des_hdrs jrddh,
128 jai_rgm_dis_src_taxes jrdst,
129 jai_rgm_dis_des_taxes jrddt
130
131 WHERE round(jrddt.transfer_amount,2) = pn_balance
132 AND jrddh.transfer_destination_id = jrddt.transfer_destination_id
133 AND jrdst.transfer_source_id = jrddt.transfer_source_id
134 AND jrdst.transfer_id = pn_transfer_id
135 AND jrddh.transfer_id = pn_transfer_id;
136
137 RETURN(ln_transfer_number);
138
139 ELSE -- 'Distribution In'
140 -- fetch transfer_number by pn_balance, pn_org_id(des org id),
141 -- if get multi records, return NULL
142 SELECT DISTINCT transfer_number
143 INTO ln_transfer_number
144 FROM jai_rgm_dis_des_hdrs jrddh,
145 jai_rgm_dis_src_taxes jrdst,
146 jai_rgm_dis_des_taxes jrddt
147
148 WHERE round(jrddt.transfer_amount,2) = pn_balance
149
150 AND jrddh.transfer_destination_id = jrddt.transfer_destination_id
151 AND jrdst.transfer_source_id = jrddt.transfer_source_id
152 AND jrdst.transfer_id = pn_transfer_id
153
154 AND jrddh.destination_party_id = pn_org_id
155 AND jrddh.transfer_id = pn_transfer_id;
156
157 RETURN(ln_transfer_number);
158 END IF;
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 -- exception when accountings are 'Distribution In' and distribtion taxes amount
163 -- are same.
164 RETURN to_number(NULL);
165 END get_transfer_number;
166
167
168 --==========================================================================
169 -- FUNCTION NAME:
170 --
171 -- get_des_org_name Public
172 --
173 -- DESCRIPTION:
174 --
175 -- This function is used to get destination organization name.
176 --
177 --
178 -- PARAMETERS:
179 -- In: pn_transfer_id Transfer id for Service Tax Distribution
180 -- pn_org_id Org id for accounting entries
181 -- pn_balance Balance for each accounting entries
182 --
183 --
184 -- DESIGN REFERENCES:
185 -- FDD_R12_1_3_GL_Drilldowndocx
186 --
187 -- CHANGE HISTORY:
188 --
189 -- 15-Apr-2010 Xiao created
190 --==========================================================================
191 FUNCTION get_des_org_name(pn_transfer_id IN NUMBER
192 , pn_org_id IN NUMBER
193 , pn_balance IN NUMBER) RETURN VARCHAR2 IS
194 ln_source_org_id NUMBER;
195 ln_des_org_name VARCHAR2(80);
196
197 CURSOR get_source_org_id_cur IS
198 SELECT party_id
199 FROM jai_rgm_dis_src_hdrs
200 WHERE transfer_id = pn_transfer_id;
201
202 CURSOR get_des_org_name_cur(pn_id IN NUMBER) IS
203 SELECT organization_name
204 FROM org_organization_definitions
205 WHERE organization_id = pn_id;
206
207 BEGIN
208
209 OPEN get_source_org_id_cur;
210 FETCH get_source_org_id_cur INTO ln_source_org_id;
211 CLOSE get_source_org_id_cur;
212
213 IF ( pn_org_id = ln_source_org_id) THEN -- 'Distribution Out'
217 jai_rgm_dis_src_taxes jrdst,
214 SELECT DISTINCT organization_name
215 INTO ln_des_org_name
216 FROM jai_rgm_dis_des_hdrs jrddh,
218 jai_rgm_dis_des_taxes jrddt,
219 org_organization_definitions ood
220
221 WHERE round(jrddt.transfer_amount,2) = pn_balance
222 AND jrddh.destination_party_id = ood.organization_id
223 AND jrdst.transfer_source_id = jrddt.transfer_source_id
224 AND jrdst.transfer_id = pn_transfer_id
225 AND jrddh.transfer_destination_id = jrddt.transfer_destination_id
226 AND jrddh.transfer_id = pn_transfer_id;
227
228 ELSE -- 'Distribution In'
229 OPEN get_des_org_name_cur(pn_org_id);
230 FETCH get_des_org_name_cur INTO ln_des_org_name;
231 CLOSE get_des_org_name_cur;
232 END IF;
233
234 RETURN(ln_des_org_name);
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 -- exception when accountings are 'Distribution In' and distribtion taxes amount
239 -- are same, and transfer to multi-orgs.
240 RETURN NULL;
241 END get_des_org_name;
242
243 --==========================================================================
244 -- FUNCTION NAME:
245 --
246 -- get_des_org_type Public
247 --
248 -- DESCRIPTION:
249 --
250 -- This function is used to get destination organization type.
251 --
252 --
253 -- PARAMETERS:
254 -- In: pn_transfer_id Transfer id for Service Tax Distribution
255 -- pn_org_id Org id for accounting entries
256 -- pn_balance Balance for each accounting entries
257 --
258 --
259 -- DESIGN REFERENCES:
260 -- FDD_R12_1_3_GL_Drilldowndocx
261 --
262 -- CHANGE HISTORY:
263 --
264 -- 15-Apr-2010 Xiao created
265 --==========================================================================
266 FUNCTION get_des_org_type(pn_transfer_id IN NUMBER
267 , pn_org_id IN NUMBER
268 , pn_balance IN NUMBER) RETURN VARCHAR IS
269 lv_des_org_type VARCHAR2(200);
270 ln_source_org_id NUMBER;
271
272 CURSOR get_source_org_id_cur IS
273 SELECT party_id
274 FROM jai_rgm_dis_src_hdrs
275 WHERE transfer_id = pn_transfer_id;
276
277 BEGIN
278
279 OPEN get_source_org_id_cur;
280 FETCH get_source_org_id_cur INTO ln_source_org_id;
281 CLOSE get_source_org_id_cur;
282
283 IF ( pn_org_id = ln_source_org_id) THEN -- 'Distribution Out'
284 SELECT DISTINCT ja.meaning
285 INTO lv_des_org_type
286 FROM ja_lookups ja,
287 jai_rgm_dis_des_hdrs jrddh,
288 jai_rgm_dis_src_taxes jrdst,
289 jai_rgm_dis_des_taxes jrddt
290
291 WHERE ja.lookup_type = 'JAI_ORGANIZATION_TYPES'
292 AND ja.lookup_code = jrddh.destination_party_type
293
294 AND round(jrddt.transfer_amount,2) = pn_balance
295 AND jrdst.transfer_source_id = jrddt.transfer_source_id
296 AND jrdst.transfer_id = pn_transfer_id
297 AND jrddh.transfer_destination_id = jrddt.transfer_destination_id
298 AND jrddh.transfer_id = pn_transfer_id;
299
300 RETURN(lv_des_org_type);
301 ELSE -- 'Distribution In'
302 SELECT DISTINCT ja.meaning
303 INTO lv_des_org_type
304 FROM ja_lookups ja,
305 jai_rgm_dis_des_hdrs jrddh,
306 jai_rgm_dis_src_taxes jrdst,
307 jai_rgm_dis_des_taxes jrddt
308
309 WHERE ja.lookup_type = 'JAI_ORGANIZATION_TYPES'
310 AND ja.lookup_code = jrddh.destination_party_type
311
312 AND round(jrddt.transfer_amount,2) = pn_balance
313 AND jrdst.transfer_source_id = jrddt.transfer_source_id
314 AND jrdst.transfer_id = pn_transfer_id
315 AND jrddh.transfer_destination_id = jrddt.transfer_destination_id
316 AND jrddh.destination_party_id = pn_org_id
317 AND jrddh.transfer_id = pn_transfer_id;
318
319 RETURN(lv_des_org_type);
320 END IF;
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 -- exception when accountings are 'Distribution In' and distribtion taxes amount
325 -- are same and transfer to multi-orgs with different types.
326 RETURN NULL;
327 END get_des_org_type;
328
329
330 --==========================================================================
331 -- FUNCTION NAME:
332 --
333 -- get_des_loc_name Public
334 --
335 -- DESCRIPTION:
336 --
337 -- This function is used to get destination organization location name.
338 --
339 --
340 -- PARAMETERS:
341 -- In: pn_transfer_id Transfer id for Service Tax Distribution
342 -- pn_org_id Org id for accounting entries
343 -- pn_balance Balance for each accounting entries
344 --
345 --
346 -- DESIGN REFERENCES:
347 -- FDD_R12_1_3_GL_Drilldowndocx
348 --
349 -- CHANGE HISTORY:
350 --
351 -- 15-Apr-2010 Xiao created
352 --==========================================================================
353 FUNCTION get_des_loc_name(pn_transfer_id IN NUMBER
354 , pn_org_id IN NUMBER
355 , pn_balance IN NUMBER) RETURN VARCHAR IS
356 lv_des_loc_name VARCHAR2(200);
357 ln_source_org_id NUMBER;
358
359 CURSOR get_source_org_id_cur IS
360 SELECT party_id
361 FROM jai_rgm_dis_src_hdrs
362 WHERE transfer_id = pn_transfer_id;
363
364 BEGIN
365
366 OPEN get_source_org_id_cur;
367 FETCH get_source_org_id_cur INTO ln_source_org_id;
368 CLOSE get_source_org_id_cur;
369
370 IF ( pn_org_id = ln_source_org_id) THEN -- 'Distribution Out'
371 SELECT DISTINCT hr.description
372 INTO lv_des_loc_name
373 FROM hr_locations hr,
374 jai_rgm_dis_des_hdrs jrddh,
375 jai_rgm_dis_src_taxes jrdst,
376 jai_rgm_dis_des_taxes jrddt
377
378 WHERE hr.location_id(+) = jrddh.location_id
379 AND round(jrddt.transfer_amount,2) = pn_balance
380 AND jrdst.transfer_source_id = jrddt.transfer_source_id
381 AND jrdst.transfer_id = pn_transfer_id
382 AND jrddh.transfer_destination_id = jrddt.transfer_destination_id
383 AND jrddh.transfer_id = pn_transfer_id;
384
385 RETURN(lv_des_loc_name);
386 ELSE -- 'Distribution In'
387 SELECT DISTINCT hr.description
388 INTO lv_des_loc_name
389 FROM hr_locations hr,
390 jai_rgm_dis_des_hdrs jrddh,
391 jai_rgm_dis_src_taxes jrdst,
392 jai_rgm_dis_des_taxes jrddt
393
394 WHERE hr.location_id(+) = jrddh.location_id
395 AND round(jrddt.transfer_amount,2) = pn_balance
396 AND jrdst.transfer_source_id = jrddt.transfer_source_id
397 AND jrdst.transfer_id = pn_transfer_id
398 AND jrddh.transfer_destination_id = jrddt.transfer_destination_id
399 AND jrddh.destination_party_id = pn_org_id
400 AND jrddh.transfer_id = pn_transfer_id;
401
402 RETURN(lv_des_loc_name);
403 END IF;
404
405 EXCEPTION
406 WHEN OTHERS THEN
407 -- exception when accountings are 'Distribution In' and distribtion taxes amount
408 -- are same, and transfer to multi-locations within same orgs.
409 RETURN NULL;
410 END get_des_loc_name;
411
412 --==========================================================================
413 -- FUNCTION NAME:
414 --
415 -- get_user_categ_name Public
416 --
417 -- DESCRIPTION:
418 --
419 -- This function is used to get gl user category name.
420 --
421 --
422 -- PARAMETERS:
423 -- In: pn_categ_name category name from gl
424 --
425 --
426 -- DESIGN REFERENCES:
427 -- FDD_R12_1_3_GL_Drilldowndocx
428 --
429 -- CHANGE HISTORY:
430 --
431 -- 09-Oct-2012 anupgupt created bug 14671875
432 --==========================================================================
433
434 FUNCTION get_user_categ_name(pn_categ_name IN VARCHAR) RETURN VARCHAR IS
435 lv_categ_name VARCHAR2(25);
436 BEGIN
437 SELECT user_je_category_name
438 INTO lv_categ_name
439 FROM gl_je_categories
440 WHERE je_category_name LIKE pn_categ_name;
441
442 RETURN lv_categ_name;
443 EXCEPTION
444 WHEN OTHERS THEN
445 RETURN NULL;
446 END get_user_categ_name;
447
448 END JAI_DRILLDOWN_UTILS_PKG;
[Home] [Help]