[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_GL_SEGS_PKG
Source
1 PACKAGE BODY jl_zz_gl_segs_pkg AS
2 /* $Header: jlzzgsgb.pls 120.3 2005/07/05 22:10:16 rguerrer ship $ */
3
4 -- Divide Segment Values
5 FUNCTION breakup_segments (catsegs IN VARCHAR2,
6 delimiter IN VARCHAR2,
7 segs OUT NOCOPY SegmentArray)
8 RETURN NUMBER AS
9 seg_start NUMBER;
10 seg_end NUMBER;
11 seg_len NUMBER;
12 catsegs_len NUMBER;
13 delimiter_len NUMBER;
14 seg_index BINARY_INTEGER;
15 ctrl_loop BOOLEAN;
16 BEGIN
17 seg_index := 1;
18 seg_start := 1;
19 ctrl_loop:= TRUE;
20 IF ((catsegs IS NOT NULL) AND
21 (delimiter IS NOT NULL)) THEN
22 catsegs_len := LENGTH(catsegs);
23 delimiter_len := LENGTH(delimiter);
24 WHILE( ctrl_loop = TRUE) LOOP
25 IF (seg_start > catsegs_len) THEN
26 segs(seg_index) := NULL;
27 ctrl_loop:= FALSE;
28 ELSE
29 seg_end := INSTR(catsegs, delimiter, seg_start);
30 IF (seg_end = 0) THEN
31 seg_end := catsegs_len + 1;
32 ctrl_loop:= FALSE;
33 END IF;
34 seg_len := seg_end - seg_start;
35 IF (seg_len = 0) THEN
36 segs(seg_index) := NULL;
37 ELSE
38 segs(seg_index) := REPLACE(SUBSTR(catsegs, seg_start, seg_len),
39 NEWLINE, delimiter);
40 END IF;
41 END IF;
42 seg_index := seg_index + 1;
43 seg_start := seg_end + delimiter_len;
44 END LOOP;
45 END IF;
46 RETURN(TO_NUMBER(seg_index - 1));
47 END breakup_segments;
48
49 -- Build all of the concatened segments
50 FUNCTION get_columns (structure_number IN NUMBER, -- key flexfield structure number
51 alias IN VARCHAR2, -- table alias
52 segment IN VARCHAR2, -- Flexfield segment (ALL,GL_ACCOUNT)
53 descriptor IN VARCHAR2) -- segment descriptor (LOW,HIGH,TYPE)
54 RETURN VARCHAR2 AS
55 cht_id NUMBER;
56 app_id NUMBER;
57 delimiter VARCHAR2(1);
58 consegs VARCHAR2(2000);
59 num NUMBER;
60 addition1 VARCHAR2(100);
61 addition2 VARCHAR2(100);
62 separate VARCHAR2(100);
63 column VARCHAR2(150);
64 BEGIN
65 consegs := NULL;
66
67 -- Get structure number
68 cht_id := structure_number;
69
70 -- Get application id
71 SELECT application_id
72 INTO app_id
73 FROM fnd_application
74 WHERE application_short_name = 'SQLGL';
75
76 -- Get concatenated segment delimiter
77 delimiter := fnd_flex_ext.get_delimiter('SQLGL','GL#',cht_id);
78
79 -- Concatened alias
80 IF alias IS NULL THEN
81 addition1 := NULL;
82 ELSE
83 addition1 := alias || '.';
84 END IF;
85
86 -- Build the concatened segments
87 IF descriptor IS NULL THEN
88 addition2 := NULL;
89 ELSE
90 addition2 := '_' || descriptor;
91 END IF;
92
93 IF segment = 'ALL' THEN
94 separate := '||' || QUOTAMARK || delimiter || QUOTAMARK || '||';
95 FOR s IN (SELECT segment_num num,
96 application_column_name acn
97 FROM fnd_id_flex_segments
98 WHERE id_flex_code = 'GL#'
99 AND id_flex_num = cht_id
100 AND application_id = app_id
101 AND enabled_flag = 'Y'
102 ORDER BY segment_num)
103 LOOP
104 consegs := consegs || addition1 || s.acn || addition2 || separate;
105 END LOOP;
106
107 num := LENGTH(consegs) - LENGTH(separate);
108 consegs := SUBSTR(consegs, 1, num);
109
110 ELSE
111 OPEN seg(app_id, cht_id, segment);
112 FETCH seg INTO num, column;
113 IF NOT seg%NOTFOUND THEN
114 consegs := addition1 || column || addition2;
115 END IF;
116 CLOSE seg;
117 END IF;
118
119 RETURN(consegs);
120 END get_columns;
121
122
123 -- Assemble WHERE segment BETWEEN 'value1' AND 'value2' with connect segments
124 FUNCTION get_between (structure_number IN NUMBER, -- key flexfield structure number
125 alias IN VARCHAR2, -- table alias
126 catseg1 IN VARCHAR2, -- Concatenated segments low
127 catseg2 IN VARCHAR2, -- Concatenated segments high
128 segment IN VARCHAR2) -- Flexfield segment (ALL,GL_ACCOUNT)
129 RETURN VARCHAR2 AS
130 consegs VARCHAR2(2000);
131 cht_id NUMBER;
132 app_id NUMBER;
133 delimiter VARCHAR2(1);
134 nsegs NUMBER;
135 seg1 SegmentArray;
136 seg2 SegmentArray;
137 num NUMBER;
138 column VARCHAR2(150);
139 addition VARCHAR2(100);
140 BEGIN
141 consegs := NULL;
142
143 -- Get structure number
144 cht_id := structure_number;
145
146 -- Get application id
147 SELECT application_id
148 INTO app_id
149 FROM fnd_application
150 WHERE application_short_name = 'SQLGL';
151
152 -- Get concatenated segment delimiter
153 delimiter := fnd_flex_ext.get_delimiter('SQLGL','GL#',cht_id);
154
155 -- Concatened alias
156 IF alias IS NULL THEN
157 addition := NULL;
158 ELSE
159 addition := alias || '.';
160 END IF;
161
162 -- Break concatenated segments
163 -- nsegs := fnd_flex_ext.breakup_segments(catseg1, delimiter, seg1);
164 -- nsegs := fnd_flex_ext.breakup_segments(catseg2, delimiter, seg2);
165 nsegs := breakup_segments(catseg1, delimiter, seg1);
166 nsegs := breakup_segments(catseg2, delimiter, seg2);
167
168 -- Assemble where Clause
169 IF segment = 'ALL' THEN
170 num := 1;
171 FOR s IN (SELECT segment_num num,
172 application_column_name acn
173 FROM fnd_id_flex_segments
174 WHERE id_flex_code = 'GL#'
175 AND id_flex_num = cht_id
176 AND application_id = app_id
177 AND enabled_flag = 'Y'
178 ORDER BY segment_num)
179 LOOP
180 IF nsegs > 0 THEN
181 -- Bug 2554099
182 num := to_number(substr(s.acn,8,length(s.acn)-7));
183 consegs := consegs || addition || s.acn || ' BETWEEN ' ||
184 QUOTAMARK || seg1(num) || QUOTAMARK ||
185 ' AND ' ||
186 QUOTAMARK || seg2(num) || QUOTAMARK ||
187 ' AND ';
188 END IF;
189 -- num := num + 1;
190 nsegs := nsegs - 1;
191 END LOOP;
192
193 num := LENGTH(consegs) - LENGTH(' AND ');
194 consegs := SUBSTR(consegs, 1, num);
195
196 ELSE
197 OPEN seg(app_id, cht_id, segment);
198 FETCH seg INTO num, column;
199 IF NOT seg%NOTFOUND THEN
200 consegs := addition || column || ' BETWEEN ' ||
201 QUOTAMARK || seg1(num) || QUOTAMARK ||
202 ' AND ' ||
203 QUOTAMARK || seg2(num) || QUOTAMARK;
204 END IF;
205 CLOSE seg;
206 END IF;
207
208 RETURN(consegs);
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 IF seg%ISOPEN THEN
213 close seg;
214 END IF;
215 RETURN(NULL);
216 END get_between;
217
218 END jl_zz_gl_segs_pkg;