1 PACKAGE BODY INV_INVIRITS_XMLP_PKG AS
2 /* $Header: INVIRITSB.pls 120.1 2007/12/25 10:28:17 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 EXCEPTION
8 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
9 /*SRW.MESSAGE(1
10 ,'Failed in SRWEXIT')*/NULL;
11 END;
12 RETURN (TRUE);
13 END AFTERREPORT;
14 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
15 BEGIN
16 BEGIN
17 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
18 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
19 EXCEPTION
20 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
21 /*SRW.MESSAGE(1
22 ,'Failed srwinit in before rpt trigger')*/NULL;
23 RAISE;
24 END;
25 BEGIN
26 IF P_CATG_HI IS NOT NULL OR P_CATG_LO IS NOT NULL THEN
27 NULL;
28 ELSE
29 NULL;
30 END IF;
31 EXCEPTION
32 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
33 /*SRW.MESSAGE(3
34 ,'Failed Flexsql MCAT Where in before rpt trig')*/NULL;
35 RAISE;
36 END;
37 BEGIN
38 IF P_ICG_LO IS NOT NULL OR P_ICG_HI IS NOT NULL THEN
39 NULL;
40 ELSE
41 NULL;
42 END IF;
43 EXCEPTION
44 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
45 /*SRW.MESSAGE(3
46 ,'Failed Flexsql MICG Where in before rpt trig')*/NULL;
47 RAISE;
48 END;
49 BEGIN
50 IF P_SORT_ID = 2 THEN
51 BEGIN
52 NULL;
53 EXCEPTION
54 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
55 /*SRW.MESSAGE(3
56 ,'Failed Flexsql MCAT Select in before rpt trig')*/NULL;
57 RAISE;
58 END;
59 ELSE
60 P_CATG_FLEXDATA := '''MCAT''';
61 END IF;
62 IF P_SORT_ID = 3 THEN
63 BEGIN
64 NULL;
65 EXCEPTION
66 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
67 /*SRW.MESSAGE(2
68 ,'Failed Flexsql MICG Select in before rpt trig')*/NULL;
69 RAISE;
70 END;
71 ELSE
72 P_ICG_FLEXDATA := '''ICG''';
73 END IF;
74 END;
75 BEGIN
76 NULL;
77 EXCEPTION
78 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
79 /*SRW.MESSAGE(6
80 ,'Failed Flexsql Item Select in before rpt trig')*/NULL;
81 RAISE;
82 END;
83 BEGIN
84 IF P_ITEM_LO IS NOT NULL OR P_ITEM_HI IS NOT NULL THEN
85 NULL;
86 ELSE
87 NULL;
88 END IF;
89 EXCEPTION
90 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
91 /*SRW.MESSAGE(7
92 ,'Failed Flexsql MSTK Where in before rpt trig')*/NULL;
93 RAISE;
94 END;
95 BEGIN
96 NULL;
97 EXCEPTION
98 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
99 /*SRW.MESSAGE(8
100 ,'Failed Flexsql Item Order by in before rpt trig')*/NULL;
101 RAISE;
102 END;
103 BEGIN
104 IF P_CURRENCY_CODE IS NULL THEN
105 SELECT
106 CURRENCY_CODE
107 INTO P_CURRENCY_CODE1
108 FROM
109 GL_SETS_OF_BOOKS GSOB,
110 ORG_ORGANIZATION_DEFINITIONS OOD
111 WHERE GSOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID
112 AND OOD.ORGANIZATION_ID = P_ORG;
113 P_EXCHANGE_RATE := 1;
114 END IF;
115 IF P_CURRENCY_CODE IS not NULL THEN
116 P_CURRENCY_CODE1:=P_CURRENCY_CODE;
117 end if;
118 EXCEPTION
119 WHEN OTHERS THEN
120 /*SRW.MESSAGE(9
121 ,'Failed defaulting currency code')*/NULL;
122 RAISE;
123 END;
124 RETURN (TRUE);
125 END BEFOREREPORT;
126 FUNCTION C_FROMFORMULA RETURN VARCHAR2 IS
127 BEGIN
128 IF P_SORT_ID = 2 THEN
129 RETURN (', mtl_item_categories mic,
130 mtl_categories mcat');
131 ELSE
132 IF P_SORT_ID = 3 THEN
133 IF P_CATG_SET_ID IS NOT NULL THEN
134 RETURN (', mtl_item_catalog_groups icg,
135 mtl_item_categories mic');
136 ELSE
137 RETURN (', mtl_item_catalog_groups icg');
138 END IF;
139 ELSE
140 IF P_SORT_ID = 1 THEN
141 RETURN (', mtl_item_categories mic');
142 ELSE
143 RETURN ('/* Do not return mcat or icg tables */');
144 END IF;
145 END IF;
146 END IF;
147 RETURN (' ');
148 END C_FROMFORMULA;
149 FUNCTION C_WHEREFORMULA RETURN VARCHAR2 IS
150 BEGIN
151 IF P_SORT_ID = 2 THEN
152 RETURN ('and msi.inventory_item_id = mic.inventory_item_id
153 and mic.organization_id = msi.organization_id
154 and mic.category_set_id = ' || TO_CHAR(P_CATG_SET_ID) || '
155 and mic.category_id = mcat.category_id');
156 ELSE
157 IF P_SORT_ID = 3 THEN
158 IF P_CATG_SET_ID IS NOT NULL THEN
159 RETURN ('and msi.item_catalog_group_id =
160 icg.item_catalog_group_id(+)
161 and msi.inventory_item_id = mic.inventory_item_id
162 and msi.organization_id = mic.organization_id
163 and mic.category_set_id=' || TO_CHAR(P_CATG_SET_ID) || '');
164 ELSE
165 RETURN ('and msi.item_catalog_group_id =
166 icg.item_catalog_group_id(+)');
167 END IF;
168 ELSE
169 IF P_SORT_ID = 1 THEN
170 RETURN ('and msi.inventory_item_id = mic.inventory_item_id
171 and msi.organization_id = mic.organization_id
172 and mic.category_set_id=' || TO_CHAR(P_CATG_SET_ID) || ' ');
173 ELSE
174 RETURN (' ');
175 END IF;
176 END IF;
177 END IF;
178 RETURN (' ');
179 END C_WHEREFORMULA;
180 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
181 BEGIN
182 RETURN (TRUE);
183 END BEFOREPFORM;
184 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
185 BEGIN
186 DECLARE
187 NAME VARCHAR2(30);
188 SET_ID NUMBER;
189 BEGIN
190 IF P_CATG_SET_ID IS NULL THEN
191 RETURN (NULL);
192 ELSE
193 SET_ID := P_CATG_SET_ID;
194 SELECT
195 CATEGORY_SET_NAME
196 INTO NAME
197 FROM
198 MTL_CATEGORY_SETS
199 WHERE CATEGORY_SET_ID = SET_ID;
200 RETURN (NAME);
201 END IF;
202 EXCEPTION
203 WHEN NO_DATA_FOUND THEN
204 RETURN (NULL);
205 END;
206 RETURN NULL;
207 END C_CAT_SET_NAMEFORMULA;
208 FUNCTION C_BREAK_OPTION_PADFORMULA(C_BREAK_OPTION_PAD IN VARCHAR2) RETURN VARCHAR2 IS
209 BEGIN
210 RETURN (C_BREAK_OPTION_PAD);
211 END C_BREAK_OPTION_PADFORMULA;
212 FUNCTION C_CURRENCY_CODEFORMULA(R_CURRENCY_CODE IN VARCHAR2
213 ,EXT_PRECISION_SAVED IN VARCHAR2) RETURN VARCHAR2 IS
214 BEGIN
215 IF P_EXCHANGE_RATE <> 1 THEN
216 RETURN ('(' || R_CURRENCY_CODE || ' @ ' || TO_CHAR(ROUND(P_EXCHANGE_RATE
217 ,NVL(EXT_PRECISION_SAVED
218 ,0))) || ')');
219 ELSE
220 RETURN ('(' || R_CURRENCY_CODE || ')');
221 END IF;
222 END C_CURRENCY_CODEFORMULA;
223 FUNCTION C_CATG_FROMFORMULA RETURN VARCHAR2 IS
224 BEGIN
225 IF P_SORT_ID = 1 OR P_SORT_ID = 3 THEN
226 IF P_CATG_HI IS NOT NULL OR P_CATG_LO IS NOT NULL THEN
227 RETURN (', mtl_categories mcat');
228 ELSE
229 RETURN (', mtl_categories mcat');
230 END IF;
231 END IF;
232 RETURN (' ');
233 END C_CATG_FROMFORMULA;
234 FUNCTION C_CATG_WHEREFORMULA RETURN VARCHAR2 IS
235 BEGIN
236 IF P_SORT_ID = 1 OR P_SORT_ID = 3 THEN
237 IF P_CATG_HI IS NOT NULL OR P_CATG_LO IS NOT NULL THEN
238 RETURN (' and mic.category_set_id =
239 ' || TO_CHAR(P_CATG_SET_ID) || '
240 and mic.category_id = mcat.category_id');
241 ELSE
242 RETURN (' ');
243 END IF;
244 END IF;
245 RETURN (' ');
246 END C_CATG_WHEREFORMULA;
247 FUNCTION C_ICG_FROMFORMULA RETURN VARCHAR2 IS
248 BEGIN
249 IF P_SORT_ID = 1 OR P_SORT_ID = 2 THEN
250 IF P_ICG_HI IS NOT NULL OR P_ICG_LO IS NOT NULL THEN
251 RETURN (', mtl_item_catalog_groups icg');
252 ELSE
253 RETURN (', mtl_item_catalog_groups icg');
254 END IF;
255 END IF;
256 RETURN (' ');
257 END C_ICG_FROMFORMULA;
258 FUNCTION C_ICG_WHEREFORMULA RETURN VARCHAR2 IS
259 BEGIN
260 IF P_SORT_ID = 1 OR P_SORT_ID = 2 THEN
261 IF P_ICG_HI IS NOT NULL OR P_ICG_LO IS NOT NULL THEN
262 RETURN (' and icg.item_catalog_group_id(+)=
263 msi.item_catalog_group_id');
264 ELSE
265 RETURN (' ');
266 END IF;
267 END IF;
268 RETURN (' ');
269 END C_ICG_WHEREFORMULA;
270 FUNCTION C_ICG_DESCFORMULA(ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
271 BEGIN
272 DECLARE
273 AA VARCHAR2(240);
274 PROFILE_VAL VARCHAR2(240);
275 BEGIN
276 AA := NULL;
277 PROFILE_VAL := NULL;
278 IF (P_DESC_OPTION = 2) THEN
279 FND_PROFILE.GET('USE_NAME_ICG_DESC'
280 ,PROFILE_VAL);
281 IF (PROFILE_VAL = 'Y' OR (PROFILE_VAL IS NULL)) THEN
282 AA := INVICGDS.INV_FN_GET_ICG_DESC(ITEM_ID
283 ,30
284 ,'Y'
285 ,P_SEPARATOR
286 ,'Y'
287 ,'Y');
288 ELSE
289 AA := INVICGDS.INV_FN_GET_ICG_DESC(ITEM_ID
290 ,30
291 ,'N'
292 ,P_SEPARATOR
293 ,'Y'
294 ,'Y');
295 END IF;
296 END IF;
297 RETURN (AA);
298 END;
299 RETURN NULL;
300 END C_ICG_DESCFORMULA;
301 FUNCTION C_break_option_pad_F(C_break_option_pad in varchar2) RETURN VARCHAR2 IS
302 begin
303 if P_sort_id=2 then
304 return(C_break_option_pad);
305 else
306 return(NULL);
307 end if;
308 RETURN NULL;
309 end C_break_option_pad_F;
310 FUNCTION C_break_option_value_F(C_break_option_value in varchar2) RETURN VARCHAR2 IS
311 begin
312 if P_sort_id=3 then
313 return(C_break_option_value);
314 else
315 return(NULL);
316 end if;
317 RETURN NULL;
318 end C_break_option_value_F;
319 END INV_INVIRITS_XMLP_PKG;
320
[Home] [Help]