[Home] [Help]
PACKAGE BODY: APPS.IGI_IGIPSIAP_XMLP_PKG
Source
1 PACKAGE BODY IGI_IGIPSIAP_XMLP_PKG AS
2 /* $Header: IGIPSIAPB.pls 120.0.12010000.3 2008/11/11 11:13:46 dramired ship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
6 RETURN (TRUE);
7 END AFTERREPORT;
8
9 FUNCTION BEFOREREPORT(P_FLEXFIELD_FROM in out NOCOPY varchar2, P_FLEXFIELD_TO in out NOCOPY varchar2) RETURN BOOLEAN IS
10 LV_MESSAGE VARCHAR2(4000) := NULL;
11 CURSOR CUR_GET_DESCRIPTION IS
12 SELECT
13 DESCRIPTION
14 FROM
15 IGI_LOOKUPS
16 WHERE UPPER(LOOKUP_CODE) = 'SIA'
17 AND LOOKUP_TYPE = 'GCC_DESCRIPTION';
18 L_DESCRIPTION IGI_LOOKUPS.DESCRIPTION%TYPE;
19 BEGIN
20 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
21 /*SRW.REFERENCE(P_STRUCT_NUM)*/NULL;
22 --P_FLEXDATA := CONV_FLEX_LOW(P_FLEXDATA);
23 --P_FLEXDATA_TO := CONV_FLEX_HIGH(P_FLEXDATA_TO);
24 /*SRW.REFERENCE(P_STRUCT_NUM)*/NULL;
25
26 /*SRW.REFERENCE(P_STRUCT_NUM)*/NULL;
27 P_WHERE := CONV_WHERE(P_FLEXFIELD_FROM,'LOW');
28
29
30 P_WHERE1 := CONV_WHERE1(P_FLEXFIELD_TO,'HIGH');
31
32 P_WHERES := '('||P_WHERE||')'||' '||'AND'||' '||'('||P_WHERE1||')';
33 --END IF;
34 /*SRW.REFERENCE(P_STRUCT_NUM)*/NULL;
35 OPEN CUR_GET_DESCRIPTION;
36 FETCH CUR_GET_DESCRIPTION
37 INTO L_DESCRIPTION;
38 CLOSE CUR_GET_DESCRIPTION;
39 IF IGI_GEN.IS_REQ_INSTALLED('SIA') THEN
40 RETURN (TRUE);
41 ELSE
42 FND_MESSAGE.SET_NAME('IGI'
43 ,'IGI_GEN_PROD_NOT_INSTALLED');
44 FND_MESSAGE.SET_TOKEN('OPTION_NAME'
45 ,L_DESCRIPTION);
46 LV_MESSAGE := FND_MESSAGE.GET;
47 /*SRW.MESSAGE(20000
48 ,LV_MESSAGE)*/NULL;
49 APP_EXCEPTION.RAISE_EXCEPTION;
50 RETURN (FALSE);
51 END IF;
52 RETURN (TRUE);
53 EXCEPTION
54 WHEN OTHERS THEN
55 /*SRW.MESSAGE(100
56 ,SQLERRM)*/NULL;
57 RETURN (FALSE);
58 END BEFOREREPORT;
59
60 /* FUNCTION CONV_FLEX_HIGH(P_FLEX IN VARCHAR2) RETURN VARCHAR2 IS
61 V_SEGS VARCHAR2(600) := NULL;
62 BEGIN
63 V_SEGS := P_FLEX;
64 V_SEGS := REPLACE(V_SEGS
65 ,'SEGMENT1'
66 ,'SEGMENT1_HIGH');
67 V_SEGS := REPLACE(V_SEGS
68 ,'SEGMENT2'
69 ,'SEGMENT2_HIGH');
70 V_SEGS := REPLACE(V_SEGS
71 ,'SEGMENT3'
72 ,'SEGMENT3_HIGH');
73 V_SEGS := REPLACE(V_SEGS
74 ,'SEGMENT4'
75 ,'SEGMENT4_HIGH');
76 V_SEGS := REPLACE(V_SEGS
77 ,'SEGMENT5'
78 ,'SEGMENT5_HIGH');
79 V_SEGS := REPLACE(V_SEGS
80 ,'SEGMENT6'
81 ,'SEGMENT6_HIGH');
82 V_SEGS := REPLACE(V_SEGS
83 ,'SEGMENT7'
84 ,'SEGMENT7_HIGH');
85 V_SEGS := REPLACE(V_SEGS
86 ,'SEGMENT8'
87 ,'SEGMENT8_HIGH');
88 V_SEGS := REPLACE(V_SEGS
89 ,'SEGMENT9'
90 ,'SEGMENT9_HIGH');
91 V_SEGS := REPLACE(V_SEGS
92 ,'SEGMENT10'
93 ,'SEGMENT10_HIGH');
94 V_SEGS := REPLACE(V_SEGS
95 ,'SEGMENT11'
96 ,'SEGMENT11_HIGH');
97 V_SEGS := REPLACE(V_SEGS
98 ,'SEGMENT12'
99 ,'SEGMENT12_HIGH');
100 V_SEGS := REPLACE(V_SEGS
101 ,'SEGMENT13'
102 ,'SEGMENT13_HIGH');
103 V_SEGS := REPLACE(V_SEGS
104 ,'SEGMENT14'
105 ,'SEGMENT14_HIGH');
106 V_SEGS := REPLACE(V_SEGS
107 ,'SEGMENT15'
108 ,'SEGMENT15_HIGH');
109 V_SEGS := REPLACE(V_SEGS
110 ,'SEGMENT16'
111 ,'SEGMENT16_HIGH');
112 V_SEGS := REPLACE(V_SEGS
113 ,'SEGMENT17'
114 ,'SEGMENT17_HIGH');
115 V_SEGS := REPLACE(V_SEGS
116 ,'SEGMENT18'
117 ,'SEGMENT18_HIGH');
118 V_SEGS := REPLACE(V_SEGS
119 ,'SEGMENT19'
120 ,'SEGMENT19_HIGH');
121 V_SEGS := REPLACE(V_SEGS
122 ,'SEGMENT20'
123 ,'SEGMENT20_HIGH');
124 V_SEGS := REPLACE(V_SEGS
125 ,'SEGMENT21'
126 ,'SEGMENT21_HIGH');
127 V_SEGS := REPLACE(V_SEGS
128 ,'SEGMENT22'
129 ,'SEGMENT22_HIGH');
130 V_SEGS := REPLACE(V_SEGS
131 ,'SEGMENT23'
132 ,'SEGMENT23_HIGH');
133 V_SEGS := REPLACE(V_SEGS
134 ,'SEGMENT24'
135 ,'SEGMENT24_HIGH');
136 V_SEGS := REPLACE(V_SEGS
137 ,'SEGMENT25'
138 ,'SEGMENT25_HIGH');
139 V_SEGS := REPLACE(V_SEGS
140 ,'SEGMENT26'
141 ,'SEGMENT26_HIGH');
142 V_SEGS := REPLACE(V_SEGS
143 ,'SEGMENT27'
144 ,'SEGMENT27_HIGH');
145 V_SEGS := REPLACE(V_SEGS
146 ,'SEGMENT28'
147 ,'SEGMENT28_HIGH');
148 V_SEGS := REPLACE(V_SEGS
149 ,'SEGMENT29'
150 ,'SEGMENT29_HIGH');
151 V_SEGS := REPLACE(V_SEGS
152 ,'SEGMENT30'
153 ,'SEGMENT30_HIGH');
154 RETURN (V_SEGS);
155 END CONV_FLEX_HIGH;
156
157 FUNCTION CONV_FLEX_LOW(P_FLEX IN VARCHAR2) RETURN VARCHAR2 IS
158 V_SEGS VARCHAR2(600) := NULL;
159 BEGIN
160 V_SEGS := P_FLEX;
161 V_SEGS := REPLACE(V_SEGS
162 ,'SEGMENT1'
163 ,'SEGMENT1_LOW');
164 V_SEGS := REPLACE(V_SEGS
165 ,'SEGMENT2'
166 ,'SEGMENT2_LOW');
167 V_SEGS := REPLACE(V_SEGS
168 ,'SEGMENT3'
169 ,'SEGMENT3_LOW');
170 V_SEGS := REPLACE(V_SEGS
171 ,'SEGMENT4'
172 ,'SEGMENT4_LOW');
173 V_SEGS := REPLACE(V_SEGS
174 ,'SEGMENT5'
175 ,'SEGMENT5_LOW');
176 V_SEGS := REPLACE(V_SEGS
177 ,'SEGMENT6'
178 ,'SEGMENT6_LOW');
179 V_SEGS := REPLACE(V_SEGS
180 ,'SEGMENT7'
181 ,'SEGMENT7_LOW');
182 V_SEGS := REPLACE(V_SEGS
183 ,'SEGMENT8'
184 ,'SEGMENT8_LOW');
185 V_SEGS := REPLACE(V_SEGS
186 ,'SEGMENT9'
187 ,'SEGMENT9_LOW');
188 V_SEGS := REPLACE(V_SEGS
189 ,'SEGMENT10'
190 ,'SEGMENT10_LOW');
191 V_SEGS := REPLACE(V_SEGS
192 ,'SEGMENT11'
193 ,'SEGMENT11_LOW');
194 V_SEGS := REPLACE(V_SEGS
195 ,'SEGMENT12'
196 ,'SEGMENT12_LOW');
197 V_SEGS := REPLACE(V_SEGS
198 ,'SEGMENT13'
199 ,'SEGMENT13_LOW');
200 V_SEGS := REPLACE(V_SEGS
201 ,'SEGMENT14'
202 ,'SEGMENT14_LOW');
203 V_SEGS := REPLACE(V_SEGS
204 ,'SEGMENT15'
205 ,'SEGMENT15_LOW');
206 V_SEGS := REPLACE(V_SEGS
207 ,'SEGMENT16'
208 ,'SEGMENT16_LOW');
209 V_SEGS := REPLACE(V_SEGS
210 ,'SEGMENT17'
211 ,'SEGMENT17_LOW');
212 V_SEGS := REPLACE(V_SEGS
213 ,'SEGMENT18'
214 ,'SEGMENT18_LOW');
215 V_SEGS := REPLACE(V_SEGS
216 ,'SEGMENT19'
217 ,'SEGMENT19_LOW');
218 V_SEGS := REPLACE(V_SEGS
219 ,'SEGMENT20'
220 ,'SEGMENT20_LOW');
221 V_SEGS := REPLACE(V_SEGS
222 ,'SEGMENT21'
223 ,'SEGMENT21_LOW');
224 V_SEGS := REPLACE(V_SEGS
225 ,'SEGMENT22'
226 ,'SEGMENT22_LOW');
227 V_SEGS := REPLACE(V_SEGS
228 ,'SEGMENT23'
229 ,'SEGMENT23_LOW');
230 V_SEGS := REPLACE(V_SEGS
231 ,'SEGMENT24'
232 ,'SEGMENT24_LOW');
233 V_SEGS := REPLACE(V_SEGS
234 ,'SEGMENT25'
235 ,'SEGMENT25_LOW');
236 V_SEGS := REPLACE(V_SEGS
237 ,'SEGMENT26'
238 ,'SEGMENT26_LOW');
239 V_SEGS := REPLACE(V_SEGS
240 ,'SEGMENT27'
241 ,'SEGMENT27_LOW');
242 V_SEGS := REPLACE(V_SEGS
243 ,'SEGMENT28'
244 ,'SEGMENT28_LOW');
245 V_SEGS := REPLACE(V_SEGS
246 ,'SEGMENT29'
247 ,'SEGMENT29_LOW');
248 V_SEGS := REPLACE(V_SEGS
249 ,'SEGMENT30'
250 ,'SEGMENT30_LOW');
251 RETURN (V_SEGS);
252 END CONV_FLEX_LOW;*/
253
254 FUNCTION CONV_WHERE(P_FLEXFIELD IN VARCHAR2,P_SEGMENT IN VARCHAR2) RETURN VARCHAR2 IS
255
256 TYPE T_VECTOR IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
257 TYPE T_VECTOR1 IS TABLE OF VARCHAR2(600) INDEX BY BINARY_INTEGER;
258 V_VALUE T_VECTOR;
259 V_INDEX_NEXT PLS_INTEGER := 1;
260 V_INDEX_PIPE PLS_INTEGER := 1;
261 i PLS_INTEGER := 0;
262 k PLS_INTEGER := 0;
263 V_SUBSTRI VARCHAR2(20);
264
265 BEGIN
266 P_FLEXDATA := NULL;
267 V_INDEX_PIPE := INSTR(P_FLEXFIELD, '-', 1, 1);
268 IF V_INDEX_PIPE > 0 THEN
269 WHILE V_INDEX_PIPE > 0 LOOP
270 i := i + 1;
271 V_VALUE(i) := substr(P_FLEXFIELD, V_INDEX_NEXT, V_INDEX_PIPE - V_INDEX_NEXT);
272 P_FLEXDATA := P_FLEXDATA||'SEGMENT'||i||'_LOW'||'||''-''||';
273 V_INDEX_NEXT := V_INDEX_PIPE + 1;
274 V_INDEX_PIPE := instr(P_FLEXFIELD, '-', V_INDEX_NEXT, 1);
275 k:= LENGTH(V_VALUE(i));
276 IF(k>0) THEN
277 FOR dummy in 1 .. k LOOP
278 V_SUBSTRI := substr(V_VALUE(i),dummy,1);
279 IF(substr(V_VALUE(i),1,1)<>'''') THEN
280 IF((V_SUBSTRI >='A' AND V_SUBSTRI <='Z')
281 OR (V_SUBSTRI >='a' AND V_SUBSTRI <='z')) THEN
282 V_VALUE(i):=''''||V_VALUE(i)||'''';
283 END IF;
284 END IF;
285 END LOOP;
286 END IF;
287 P_WHERE := 'SEGMENT'||i||'_'||P_SEGMENT||'>='||V_VALUE(i)||' '||'AND'||' '||P_WHERE;
288 END LOOP;
289 P_FLEXDATA := RTRIM(P_FLEXDATA,'||''-''||');
290 END IF;
291 RETURN(P_WHERE);
292 END CONV_WHERE;
293 FUNCTION CONV_WHERE1(P_FLEXFIELD IN VARCHAR2,P_SEGMENT IN VARCHAR2) RETURN VARCHAR2 IS
294
295 TYPE T_VECTOR IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
296 TYPE T_VECTOR1 IS TABLE OF VARCHAR2(600) INDEX BY BINARY_INTEGER;
297 V_VALUE T_VECTOR;
298 V_INDEX_NEXT PLS_INTEGER := 1;
299 V_INDEX_PIPE PLS_INTEGER := 1;
300 i PLS_INTEGER := 0;
301 k PLS_INTEGER := 0;
302 V_SUBSTRI VARCHAR2(20);
303
304 BEGIN
305 P_FLEXDATA_TO:=null;
306 V_INDEX_PIPE := INSTR(P_FLEXFIELD, '-', 1, 1);
307 IF V_INDEX_PIPE > 0 THEN
308 WHILE V_INDEX_PIPE > 0 LOOP
309 i := i + 1;
310 V_VALUE(i) := SUBSTR(P_FLEXFIELD, V_INDEX_NEXT, V_INDEX_PIPE - V_INDEX_NEXT);
311 P_FLEXDATA_TO := P_FLEXDATA_TO||'SEGMENT'||i||'_HIGH'||'||''-''||';
312 V_INDEX_NEXT := V_INDEX_PIPE + 1;
313 V_INDEX_PIPE := INSTR(P_FLEXFIELD, '-', V_INDEX_NEXT, 1);
314 k:= length(V_VALUE(i));
315 IF(k>0) THEN
316 FOR dummy in 1 .. k LOOP
317 V_SUBSTRI := SUBSTR(V_VALUE(i),dummy,1);
318 IF(SUBSTR(V_VALUE(i),1,1)<>'''') THEN
319 IF((V_SUBSTRI >='A' AND V_SUBSTRI <='Z')
320 OR (V_SUBSTRI >='a' AND V_SUBSTRI <='z')) THEN
321 V_VALUE(i):=''''||V_VALUE(i)||'''';
322 END IF;
323 END IF;
324 END LOOP;
325 END IF;
326 P_WHERE1 := 'SEGMENT'||i||'_'||P_SEGMENT||'<='||V_VALUE(i)||' '||'AND'||' '||P_WHERE1;
327 END LOOP;
328 P_FLEXDATA_TO := RTRIM(P_FLEXDATA_TO,'||''-''||');
329 END IF;
330 RETURN(P_WHERE1);
331 END CONV_WHERE1;
332
333 END IGI_IGIPSIAP_XMLP_PKG;