[Home] [Help]
PACKAGE BODY: APPS.INV_INVTRSTS_XMLP_PKG
Source
1 PACKAGE BODY INV_INVTRSTS_XMLP_PKG AS
2 /* $Header: INVTRSTSB.pls 120.2 2008/01/08 06:47:24 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 IF P_DROP IS NULL THEN
7 EXECUTE IMMEDIATE
8 'drop view ' || P_VIEW;
9 ELSE
10 NULL;
11 END IF;
12 EXCEPTION
13 WHEN /*SRW.DO_SQL_FAILURE*/OTHERS THEN
14 /*SRW.MESSAGE(123
15 ,'Do sql failed to drop view.')*/NULL;
16 END;
17 BEGIN
18 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
19 EXCEPTION
20 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
21 /*SRW.MESSAGE(1
22 ,'Error in SRWEXIT')*/NULL;
23 END;
24 RETURN (TRUE);
25 RETURN (TRUE);
26 END AFTERREPORT;
27 FUNCTION C_FROM_CATFORMULA RETURN VARCHAR2 IS
28 BEGIN
29 RETURN (',mtl_item_categories mic, mtl_categories mc');
30 END C_FROM_CATFORMULA;
31 FUNCTION C_WHERE_CATFORMULA RETURN VARCHAR2 IS
32 BEGIN
33 RETURN ('and msi.inventory_item_id = mic.inventory_item_id and
34 mic.category_id = mc.category_id
35 and mic.organization_id = ' || TO_CHAR(P_ORG_ID) || '
36 and mic.category_set_id = ' || TO_CHAR(P_CAT_SET_ID));
37 END C_WHERE_CATFORMULA;
38 FUNCTION C_SOURCE_TYPE1FORMULA RETURN VARCHAR2 IS
39 BEGIN
40 DECLARE
41 SOURCE_TYPE_ID NUMBER;
42 NAME VARCHAR2(40);
43 BEGIN
44 SOURCE_TYPE_ID := P_STYPE1;
45 SELECT
46 TRANSACTION_SOURCE_TYPE_NAME
47 INTO NAME
48 FROM
49 MTL_TXN_SOURCE_TYPES
50 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
51 RETURN (NAME);
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 RETURN ('Error');
55 WHEN OTHERS THEN
56 RETURN ('Error');
57 END;
58 RETURN NULL;
59 END C_SOURCE_TYPE1FORMULA;
60 FUNCTION C_SOURCE_TYPE2FORMULA RETURN VARCHAR2 IS
61 BEGIN
62 DECLARE
63 SOURCE_TYPE_ID NUMBER;
64 NAME VARCHAR2(40);
65 BEGIN
66 SOURCE_TYPE_ID := P_STYPE2;
67 SELECT
68 TRANSACTION_SOURCE_TYPE_NAME
69 INTO NAME
70 FROM
71 MTL_TXN_SOURCE_TYPES
72 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
73 RETURN (NAME);
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76 RETURN ('Error');
77 WHEN OTHERS THEN
78 RETURN ('Error');
79 END;
80 RETURN NULL;
81 END C_SOURCE_TYPE2FORMULA;
82 FUNCTION C_SOURCE_TYPE3FORMULA RETURN VARCHAR2 IS
83 BEGIN
84 DECLARE
85 SOURCE_TYPE_ID NUMBER;
86 NAME VARCHAR2(40);
87 BEGIN
88 SOURCE_TYPE_ID := P_STYPE3;
89 SELECT
90 TRANSACTION_SOURCE_TYPE_NAME
91 INTO NAME
92 FROM
93 MTL_TXN_SOURCE_TYPES
94 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
95 RETURN (NAME);
96 EXCEPTION
97 WHEN NO_DATA_FOUND THEN
98 RETURN ('Error');
99 WHEN OTHERS THEN
100 RETURN ('Error');
101 END;
102 RETURN NULL;
103 END C_SOURCE_TYPE3FORMULA;
104 FUNCTION C_WHERE_SUBINVFORMULA RETURN VARCHAR2 IS
105 BEGIN
106 IF P_SUBINV_LO IS NOT NULL AND P_SUBINV_HI IS NOT NULL THEN
107 RETURN ('and v.subinv between ''' || P_SUBINV_LO || ''' and
108 ''' || P_SUBINV_HI || '''');
109 ELSE
110 IF P_SUBINV_LO IS NOT NULL AND P_SUBINV_HI IS NULL THEN
111 RETURN ('and v.subinv >= ''' || P_SUBINV_LO || ''' ');
112 ELSE
113 IF P_SUBINV_LO IS NULL AND P_SUBINV_HI IS NOT NULL THEN
114 RETURN ('and v.subinv <= ''' || P_SUBINV_HI || ''' ');
115 ELSE
116 RETURN (' ');
117 /*SRW.MESSAGE(1
118 ,C_WHERE_SUBINV)*/NULL;
119 END IF;
120 END IF;
121 END IF;
122 RETURN ' ';
123 END C_WHERE_SUBINVFORMULA;
124 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
125 BEGIN
126 BEGIN
127 BEGIN
128 /*SRW.MESSAGE(501
129 ,'Initializing Report')*/NULL;
130 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
131 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
132 EXCEPTION
133 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
134 /*SRW.MESSAGE(1
135 ,'Error in SRWINIT')*/NULL;
136 RAISE;
137 END;
138 /*SRW.MESSAGE(502
139 ,'Report Initialized')*/NULL;
140 BEGIN
141 NULL;
142 EXCEPTION
143 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
144 /*SRW.MESSAGE(2
145 ,'Error in MSTK/select')*/NULL;
146 RAISE;
147 END;
148 BEGIN
149 NULL;
150 EXCEPTION
151 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
152 /*SRW.MESSAGE(3
153 ,'Error in MSTK/where')*/NULL;
154 RAISE;
155 END;
156 BEGIN
157 NULL;
158 EXCEPTION
159 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
160 /*SRW.MESSAGE(3
161 ,'Error in MSTK/order by')*/NULL;
162 RAISE;
163 END;
164 IF P_SORT_ID <> 3 THEN
165 P_CAT_FLEX := '''X''';
166 ELSE
167 BEGIN
168 NULL;
169 EXCEPTION
170 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
171 /*SRW.MESSAGE(4
172 ,'Error in MCAT/Select')*/NULL;
173 RAISE;
174 END;
175 END IF;
176 IF P_CAT_HI IS NOT NULL OR P_CAT_LO IS NOT NULL THEN
177 BEGIN
178 NULL;
179 EXCEPTION
180 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
181 /*SRW.MESSAGE(5
182 ,'Error in MCAT')*/NULL;
183 RAISE;
184 END;
185 ELSE
186 NULL;
187 END IF;
188 END;
189 RETURN (TRUE);
190 END BEFOREREPORT;
191 FUNCTION AFTERPFORM RETURN BOOLEAN IS
192 BEGIN
193 P_DATE_LO_1 := TO_CHAR(TO_DATE(P_DATE_LO
194 ,'YYYY/MM/DD HH24:MI:SS')
195 ,'DD-MON-RRRR HH24:MI:SS');
196 P_DATE_HI_1 := TO_CHAR(TO_DATE(P_DATE_HI
197 ,'YYYY/MM/DD HH24:MI:SS')
198 ,'DD-MON-RRRR');
199 IF (P_DATE_HI IS NOT NULL) THEN
200 P_DATE_HI_1 := TO_CHAR(TO_DATE(P_DATE_HI_1 || ' 23:59:59'
201 ,'DD-MON-RRRR HH24:MI:SS')
202 ,'DD-MON-RRRR HH24:MI:SS');
203 ELSE
204 P_DATE_HI_1 := TO_CHAR(TO_DATE(P_DATE_HI_1
205 ,'DD-MON-RRRR HH24:MI:SS')
206 ,'DD-MON-RRRR HH24:MI:SS');
207 END IF;
208 BEGIN
209 /*SRW.MESSAGE(100
210 ,'Setting optimizer goal')*/NULL;
211 /*SRW.MESSAGE(101
212 ,'Optimizer goal set')*/NULL;
213 /*SRW.MESSAGE(1
214 ,P_VIEW)*/NULL;
215 /*SRW.MESSAGE(102
216 ,'Creating View')*/NULL;
217 EXECUTE IMMEDIATE
218 'create view ' || P_VIEW || ' as
219 select mmt.subinventory_code subinv,
220 mmt.inventory_item_id item_id,
221 sum(decode(mmt.transaction_source_type_id,' || P_STYPE1 || ',
222 decode(' || TO_CHAR(P_SELECTION) || ',1,primary_quantity,2,1,
223 decode(' || P_STYPE1 || ',11,quantity_adjusted*
224 (new_cost-prior_cost),
225 13,
226 decode ( mmt.transaction_type_id, 80,
227 quantity_adjusted*(new_cost-prior_cost),
228 primary_quantity * actual_cost
229 ),
230 primary_quantity * actual_cost))
231 ,0)) source_type1,
232 sum(decode(mmt.transaction_source_type_id,' || P_STYPE2 || ',
233 decode(' || TO_CHAR(P_SELECTION) || ',1,primary_quantity,2,1,
234 decode(' || P_STYPE2 || ',11,quantity_adjusted*
235 (new_cost-prior_cost),
236 13,
237 decode ( mmt.transaction_type_id, 80,
238 quantity_adjusted*(new_cost-prior_cost),
239 primary_quantity * actual_cost
240 ),
241 primary_quantity * actual_cost))
242 ,0)) source_type2,
243 sum(decode(mmt.transaction_source_type_id,' || P_STYPE3 || ',
244 decode(' || TO_CHAR(P_SELECTION) || ',1,primary_quantity,2,1,
245 decode(' || P_STYPE3 || ',11,quantity_adjusted*
246 (new_cost-prior_cost),
247 13,
248 decode ( mmt.transaction_type_id, 80,
249 quantity_adjusted*(new_cost-prior_cost),
250 primary_quantity * actual_cost
251 ),
252 primary_quantity * actual_cost))
253 ,0)) source_type3,
254 sum(decode(mmt.transaction_source_type_id,' || P_STYPE1 || ',0,
255 ' || P_STYPE2 || ',0,' || P_STYPE3 || ',0,' || P_STYPE4 || ',0,' || P_STYPE5 || ',0,
256 decode(' || TO_CHAR(P_SELECTION) || ',1,primary_quantity,2,1,
257 decode(mmt.transaction_source_type_id,11,
258 quantity_adjusted*(new_cost-prior_cost),
259 13,
260 decode ( mmt.transaction_type_id, 80,
261 quantity_adjusted*(new_cost-prior_cost),
262 primary_quantity * actual_cost
263 ),
264 primary_quantity * actual_cost))
265 )) other
266 from mtl_material_transactions mmt
267 where mmt.organization_id = ' || TO_CHAR(P_ORG_ID) || '
268 and (transaction_date) >= nvl(to_date(''' || P_DATE_LO_1 || ''',' || '''DD-MON-YYYY HH24:MI:SS''' || '), (transaction_date))
269 and (transaction_date) <= nvl(to_date(''' || P_DATE_HI_1 || ''',' || '''DD-MON-YYYY HH24:MI:SS''' || '), (transaction_date))
270 group by mmt.subinventory_code, mmt.inventory_item_id';
271 /*SRW.MESSAGE(103
272 ,'View Created and ready to use')*/NULL;
273 EXCEPTION
274 WHEN /*SRW.DO_SQL_FAILURE*/OTHERS THEN
275 /*SRW.MESSAGE(111
276 ,'Create view failed')*/NULL;
277 END;
278 RETURN (TRUE);
279 END AFTERPFORM;
280 FUNCTION C_ITEM_TOTALFORMULA(SOURCE_TYPE1 IN NUMBER
281 ,SOURCE_TYPE2 IN NUMBER
282 ,SOURCE_TYPE3 IN NUMBER
283 ,OTHER IN NUMBER) RETURN NUMBER IS
284 BEGIN
285 RETURN (SOURCE_TYPE1 + SOURCE_TYPE2 + SOURCE_TYPE3 + OTHER);
286 END C_ITEM_TOTALFORMULA;
287 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
288 BEGIN
289 DECLARE
290 NAME VARCHAR2(30);
291 SET_ID NUMBER;
292 BEGIN
293 IF P_CAT_SET_ID IS NULL THEN
294 RETURN ('');
295 ELSE
296 SET_ID := P_CAT_SET_ID;
297 SELECT
298 CATEGORY_SET_NAME
299 INTO NAME
300 FROM
301 MTL_CATEGORY_SETS
302 WHERE CATEGORY_SET_ID = SET_ID;
303 RETURN (NAME);
304 END IF;
305 EXCEPTION
306 WHEN NO_DATA_FOUND THEN
307 RETURN ('');
308 WHEN OTHERS THEN
309 RETURN ('Error');
310 END;
311 RETURN NULL;
312 END C_CAT_SET_NAMEFORMULA;
313 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
314 BEGIN
315 RETURN (TRUE);
316 END BEFOREPFORM;
317 FUNCTION C_CURRENCY_CODEFORMULA(R_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
318 BEGIN
319 RETURN ('(' || R_CURRENCY_CODE || ')');
320 END C_CURRENCY_CODEFORMULA;
321 FUNCTION P_VIEW_PUTVALIDTRIGGER RETURN BOOLEAN IS
322 BEGIN
323 BEGIN
324 P_VIEW := 'TXN_USAGE_VIEW' || TO_CHAR(P_CONC_REQUEST_ID);
325 END;
326 RETURN (TRUE);
327 END P_VIEW_PUTVALIDTRIGGER;
328 FUNCTION C_STATUS_WHEREFORMULA RETURN VARCHAR2 IS
329 BEGIN
330 RETURN (' ');
331 END C_STATUS_WHEREFORMULA;
332 FUNCTION C_CAT_PADFORMULA(C_CAT_FIELD IN VARCHAR2) RETURN VARCHAR2 IS
333 BEGIN
334 /*SRW.REFERENCE(C_CAT_FIELD)*/NULL;
335 RETURN (C_CAT_FIELD);
336 END C_CAT_PADFORMULA;
337 END INV_INVTRSTS_XMLP_PKG;
338