[Home] [Help]
PACKAGE BODY: APPS.GMF_FND_GET_SEGMENT_VAL
Source
1 PACKAGE BODY GMF_FND_GET_SEGMENT_VAL AS
2 /* $Header: gmfseglb.pls 115.6 2002/11/11 00:42:27 rseshadr ship $ */
3
4 --
5 -- removed gl_stat_account_uom table from the cursor Bug 1837544
6 --
7 CURSOR cur_get_segment_val ( startdate date,
8 enddate date,
9 sobname varchar2,
10 segmentname varchar2,
11 segmentnum number,
12 segmentval varchar2,
13 segmentdesc varchar2,
14 segmentuom varchar2) IS
15 SELECT VAL.start_date_active,
16 VAL.end_date_active,
17 FND.segment_name,
18 FND.segment_num,
19 VAL.flex_value,
20 VAL.description,
21 GLS.chart_of_accounts_id
22 FROM fnd_id_flex_segments FND,
23 gl_sets_of_books GLS,
24 fnd_flex_values_vl VAL
25 WHERE GLS.name = NVL(sobname,GLS.name)
26 AND GLS.chart_of_accounts_id = FND.id_flex_num
27 AND LOWER(FND.segment_name) = LOWER(NVL(segmentname,FND.segment_name))
28 AND FND.segment_num = NVL(segmentnum, FND.segment_num)
29 AND FND.enabled_flag = 'Y'
30 AND FND.flex_value_set_id = VAL.flex_value_set_id
31 AND VAL.enabled_flag = 'Y'
32 AND NVL(VAL.start_date_active,SYSDATE) <= NVL(startdate, SYSDATE)
33 AND NVL(VAL.end_date_active,SYSDATE) >= NVL(enddate, SYSDATE)
34 AND VAL.flex_value = NVL(segmentval, VAL.flex_value)
35 AND NVL(VAL.description,' ') = NVL(segmentdesc, NVL(VAL.description,' '))
36 --AND VAL.flex_value = GLU.ACCOUNT_SEGMENT_VALUE(+)
37 --AND nvl(GLU.chart_of_accounts_id, GLS.chart_of_accounts_id) = GLS.chart_of_accounts_id -- Bug# 1837544
38 AND VAL.summary_flag = 'N'
39 ORDER BY FND.segment_name,
40 FND.segment_num,
41 VAL.flex_value;
42
43 PROCEDURE proc_get_segment_val( startdate IN OUT NOCOPY date,
44 enddate IN OUT NOCOPY date,
45 sobname IN varchar2,
46 segmentname IN OUT NOCOPY varchar2,
47 segmentnum IN OUT NOCOPY number,
48 segmentval IN OUT NOCOPY varchar2,
49 segmentdesc IN OUT NOCOPY varchar2,
50 row_to_fetch IN number,
51 statuscode OUT NOCOPY number,
52 segmentuom IN OUT NOCOPY varchar2 ) as
53
54
55 -- Bug# 1837544 : Added following cursor and variable
56
57 CURSOR cur_get_uom(acct_seg_val VARCHAR2,
58 chart_of_accts_id NUMBER)
59 IS
60 SELECT unit_of_measure
61 FROM gl_stat_account_uom
62 WHERE account_segment_value = acct_seg_val
63 AND chart_of_accounts_id = chart_of_accts_id ;
64
65 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE ;
66
67 BEGIN
68 IF NOT cur_get_segment_val%ISOPEN THEN
69 OPEN cur_get_segment_val(startdate,
70 enddate,
71 sobname,
72 segmentname,
73 segmentnum,
74 segmentval,
75 segmentdesc,
76 segmentuom );
77 END IF;
78
79 FETCH cur_get_segment_val
80 INTO startdate,
81 enddate,
82 segmentname,
83 segmentnum,
84 segmentval,
85 segmentdesc,
86 --segmentuom; -- Bug# 1837544
87 l_chart_of_accounts_id ;
88
89 IF cur_get_segment_val%NOTFOUND THEN
90 statuscode := 100;
91 CLOSE cur_get_segment_val;
92 END IF;
93
94 IF row_to_fetch = 1 AND cur_get_segment_val%ISOPEN THEN
95 CLOSE cur_get_segment_val;
96 END IF;
97
98 /* Begin Bug# 1837544 */
99
100 IF NOT cur_get_uom%ISOPEN THEN
101 OPEN cur_get_uom( segmentval, l_chart_of_accounts_id) ;
102 END IF;
103
104 FETCH cur_get_uom INTO segmentuom ;
105
106 IF cur_get_uom%NOTFOUND THEN
107 segmentuom := '' ;
108 CLOSE cur_get_uom;
109 END IF ;
110
111 IF cur_get_uom%ISOPEN THEN
112 CLOSE cur_get_uom;
113 END IF ;
114
115 /* End Bug# 1837544 */
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 statuscode := SQLCODE;
120 END;
121 END GMF_FND_GET_SEGMENT_VAL;