1 PACKAGE BODY INV_INVDRRSV_XMLP_PKG AS
2 /* $Header: INVDRRSVB.pls 120.1 2008/01/08 06:44:37 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 DATE_LO DATE;
7 DATE_HI DATE;
8 BEGIN
9 DATE_LO := TO_DATE(P_DATE_LO
10 ,'YYYY/MM/DD HH24:MI:SS');
11 DATE_HI := TO_DATE(P_DATE_HI
12 ,'YYYY/MM/DD HH24:MI:SS');
13 /*P_DATE_LO := TO_CHAR(DATE_LO
14 ,'DD-MON-RR');
15 P_DATE_HI := TO_CHAR(DATE_HI
16 ,'DD-MON-RR');
17 P_DATE_HI := P_DATE_HI || ' 23:59:59';*/
18 --added as fix
19 P_DATE_LO_V := TO_CHAR(DATE_LO
20 ,'DD-MON-RR');
21 P_DATE_HI_V := TO_CHAR(DATE_HI
22 ,'DD-MON-RR');
23 P_DATE_HI_V := P_DATE_HI_V || ' 23:59:59';
24 END;
25 BEGIN
26 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
27 qty_precision:=inv_common_xmlp_pkg.get_precision(P_qty_precision);
28 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
29 EXCEPTION
30 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
31 /*SRW.MESSAGE(1
32 ,'Failed in before report trigger:SRWINIT')*/NULL;
33 END;
34 DECLARE
35 P_ORG_ID_CHAR VARCHAR2(100) := TO_CHAR(P_ORG_ID);
36 BEGIN
37 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
38 EXCEPTION
39 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
40 /*SRW.MESSAGE(020
41 ,'Failed in before report trigger, setting org profile ')*/NULL;
42 RAISE;
43 END;
44 BEGIN
45 NULL;
46 EXCEPTION
47 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
48 /*SRW.MESSAGE(1
49 ,'Failed in before report trigger:MSTK')*/NULL;
50 END;
51 BEGIN
52 IF P_SORT_ID = 3 THEN
53 BEGIN
54 NULL;
55 EXCEPTION
56 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
57 /*SRW.MESSAGE(1
58 ,'Failed in before report trigger:MSTK:ORDERBY')*/NULL;
59 END;
60 BEGIN
61 IF P_SOURCE_TYPE_ID in (2,8) THEN
62 NULL;
63 ELSE
64 IF P_SOURCE_TYPE_ID = 3 THEN
65 NULL;
66 ELSE
67 IF P_SOURCE_TYPE_ID = 6 THEN
68 NULL;
69 ELSE
70 IF P_SOURCE_TYPE_ID = 5 THEN
71 P_ORDER_SOURCE := 'wip_entity_name';
72 ELSE
73 IF P_SOURCE_TYPE_ID >= 13 THEN
74 P_ORDER_SOURCE := 'md.demand_source_name';
75 ELSE
76 P_ORDER_SOURCE := '12';
77 END IF;
78 END IF;
79 END IF;
80 END IF;
81 END IF;
82 EXCEPTION
83 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
84 /*SRW.MESSAGE(1
85 ,'Failed in before report trigger:SOURCE:ORDERBY')*/NULL;
86 END;
87 ELSE
88 NULL;
89 END IF;
90 END;
91 BEGIN
92 IF P_ITEM_LO IS NOT NULL OR P_ITEM_HI IS NOT NULL THEN
93 NULL;
94 ELSE
95 NULL;
96 END IF;
97 EXCEPTION
98 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
99 /*SRW.MESSAGE(1
100 ,'Failed in before report trigger:MSTK:WHERE')*/NULL;
101 END;
102 BEGIN
103 IF P_BREAK_ID = 1 THEN
104 NULL;
105 ELSE
106 P_CAT_FLEX := '''MC''';
107 END IF;
108 EXCEPTION
109 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
110 /*SRW.MESSAGE(1
111 ,'Failed in before report trigger:MCAT/sel')*/NULL;
112 END;
113 BEGIN
114 IF P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
115 NULL;
116 ELSE
117 NULL;
118 END IF;
119 EXCEPTION
120 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
121 /*SRW.MESSAGE(1
122 ,'Failed in before report trigger:MCAT/WHERE')*/NULL;
123 END;
124 BEGIN
125 NULL;
126 EXCEPTION
127 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
128 /*SRW.MESSAGE(1
129 ,'Failed in before report trigger:MTLL')*/NULL;
130 END;
131 BEGIN
132 IF NVL(P_SOURCE_TYPE_ID
133 ,2) = 2 THEN
134 NULL;
135 ELSE
136 NULL;
137 END IF;
138 EXCEPTION
139 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
140 /*SRW.MESSAGE(9
141 ,'Failed in MKTS/Sel')*/NULL;
142 END;
143 BEGIN
144 IF NVL(P_SOURCE_TYPE_ID
145 ,6) = 6 THEN
146 NULL;
147 ELSE
148 NULL;
149 END IF;
150 EXCEPTION
151 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
152 /*SRW.MESSAGE(10
153 ,'Failed in MDSP/Sel')*/NULL;
154 END;
155 BEGIN
156 IF NVL(P_SOURCE_TYPE_ID
157 ,8) = 8 THEN
158 NULL;
159 ELSE
160 NULL;
161 END IF;
162 EXCEPTION
163 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
164 /*SRW.MESSAGE(9
165 ,'Failed in MKTS/Sel')*/NULL;
166 END;
167 BEGIN
168 IF NVL(P_SOURCE_TYPE_ID
169 ,3) = 3 THEN
170 NULL;
171 ELSE
172 NULL;
173 END IF;
174 EXCEPTION
175 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
176 /*SRW.MESSAGE(11
177 ,'Failed in GL#/Sel')*/NULL;
178 END;
179 IF P_SOURCE_TYPE_ID = 2 THEN
180 BEGIN
181 IF P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL THEN
182 NULL;
183 ELSE
184 NULL;
185 END IF;
186 EXCEPTION
187 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
188 /*SRW.MESSAGE(1
189 ,'Failed in before report trigger:MKTS/where')*/NULL;
190 END;
191 ELSE
192 IF P_SOURCE_TYPE_ID = 8 THEN
193 BEGIN
194 IF P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL THEN
195 NULL;
196 ELSE
197 NULL;
198 END IF;
199 EXCEPTION
200 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
201 /*SRW.MESSAGE(1
202 ,'Failed in before report trigger:MKTS/where')*/NULL;
203 END;
204 ELSE
205 IF P_SOURCE_TYPE_ID = 6 THEN
206 BEGIN
207 IF P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL THEN
208 NULL;
209 ELSE
210 NULL;
211 END IF;
212 EXCEPTION
213 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
214 /*SRW.MESSAGE(1
215 ,'Failed in before report trigger:MDSP/where')*/NULL;
216 END;
217 ELSE
218 IF P_SOURCE_TYPE_ID = 3 THEN
219 BEGIN
220 IF P_SOURCE_HI IS NOT NULL OR P_SOURCE_LO IS NOT NULL THEN
221 NULL;
222 ELSE
223 NULL;
224 END IF;
225 EXCEPTION
226 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
227 /*SRW.MESSAGE(1
228 ,'Failed in before report trigger:GL/where')*/NULL;
229 END;
230 ELSE
231 NULL;
232 END IF;
233 END IF;
234 END IF;
235 END IF;
236 RETURN (TRUE);
237 END BEFOREREPORT;
238
239 FUNCTION AFTERREPORT RETURN BOOLEAN IS
240 BEGIN
241 BEGIN
242 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
243 EXCEPTION
244 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
245 /*SRW.MESSAGE(1
246 ,'SRWEXIT failed')*/NULL;
247 END;
248 RETURN (TRUE);
249 END AFTERREPORT;
250
251 FUNCTION C_CURRENCY_CODEFORMULA(R_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
252 BEGIN
253 RETURN ('(' || R_CURRENCY_CODE || ')');
254 END C_CURRENCY_CODEFORMULA;
255
256 FUNCTION C_FROM_CATFORMULA RETURN VARCHAR2 IS
257 BEGIN
258 IF P_BREAK_ID = 1 OR P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
259 RETURN (',mtl_item_categories mic, mtl_categories mc');
260 ELSE
261 RETURN ('/* Do not select from category tables.*/');
262 END IF;
263 RETURN NULL;
264 END C_FROM_CATFORMULA;
265
266 FUNCTION C_CAT_WHEREFORMULA RETURN VARCHAR2 IS
267 BEGIN
268 IF P_BREAK_ID = 1 OR P_CAT_LO IS NOT NULL OR P_CAT_HI IS NOT NULL THEN
269 RETURN ('and msi.inventory_item_id = mic.inventory_item_id
270 and mic.organization_id = msi.organization_id
271 and mic.category_set_id = ' || TO_CHAR(P_CAT_SET_ID) || '
272 and mic.category_id = mc.category_id
273 and mic.organization_id = ' || TO_CHAR(P_ORG_ID));
274 ELSE
275 RETURN (' ');
276 END IF;
277 RETURN NULL;
278 END C_CAT_WHEREFORMULA;
279
280 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
281 BEGIN
282 RETURN (TRUE);
283 END BEFOREPFORM;
284
285 FUNCTION C_ORDER_BYFORMULA RETURN VARCHAR2 IS
286 BEGIN
287 IF P_SORT_ID in (1,2) THEN
288 RETURN ('order by revision, lot_number, subinventory');
289 ELSE
290 IF P_SORT_ID = 3 THEN
291 RETURN ('order by ' || P_ORDER_SOURCE || ', ' || P_ORDER_ITEM || ', revision');
292 ELSE
293 RETURN ('order by 1,2,3,4,5,6');
294 END IF;
295 END IF;
296 RETURN NULL;
297 END C_ORDER_BYFORMULA;
298
299 FUNCTION C_CAT_PADFORMULA(C_CAT_PAD IN VARCHAR2) RETURN VARCHAR2 IS
300 BEGIN
301 RETURN (C_CAT_PAD);
302 END C_CAT_PADFORMULA;
303
304 FUNCTION C_SORT_PADFORMULA(C_SORT_PAD IN VARCHAR2) RETURN VARCHAR2 IS
305 BEGIN
306 RETURN (C_SORT_PAD);
307 END C_SORT_PADFORMULA;
308
309 FUNCTION C_ITEM_PADFORMULA(C_ITEM_PAD IN VARCHAR2) RETURN VARCHAR2 IS
310 BEGIN
311 RETURN (C_ITEM_PAD);
312 END C_ITEM_PADFORMULA;
313
314 FUNCTION C_SOURCE_WHEREFORMULA RETURN VARCHAR2 IS
315 BEGIN
316 IF P_SOURCE_TYPE_ID = 2 THEN
317 RETURN ('and md.demand_source_header_id = mkts.sales_order_id
318 and ' || P_SOURCE_WHERE);
319 ELSE
320 IF P_SOURCE_TYPE_ID = 3 THEN
321 RETURN ('and md.demand_source_header_id = gl1.code_combination_id
322 and ' || P_SOURCE_WHERE);
323 ELSE
324 IF P_SOURCE_TYPE_ID = 6 THEN
325 RETURN ('and md.demand_source_header_id = mdsp.disposition_id
326 and ' || P_SOURCE_WHERE);
327 ELSE
328 IF P_SOURCE_TYPE_ID = 8 THEN
329 RETURN ('and md.demand_source_header_id = mkts.sales_order_id
330 and ' || P_SOURCE_WHERE);
331 ELSE
332 IF P_SOURCE_TYPE_ID = 5 AND P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
333 RETURN ('and md.demand_source_header_id = wip1.wip_entity_id
334 and wip1.wip_entity_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
335 ELSE
336 IF P_SOURCE_TYPE_ID = 5 AND P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
337 RETURN ('and md.demand_source_header_id = wip1.wip_entity_id
338 and wip1.wip_entity_name >= ''' || P_SOURCE_LO || ''' ');
339 ELSE
340 IF P_SOURCE_TYPE_ID = 5 AND P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
341 RETURN ('and md.demand_source_header_id = wip1.wip_entity_id
342 and wip1.wip_entity_name <= ''' || P_SOURCE_HI || ''' ');
343 ELSE
344 IF P_SOURCE_TYPE_ID >= 13 AND P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
345 RETURN ('and md.demand_source_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
346 ELSE
347 IF P_SOURCE_TYPE_ID >= 13 AND P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
348 RETURN ('and md.demand_source_name >= ''' || P_SOURCE_LO || ''' ');
349 ELSE
350 IF P_SOURCE_TYPE_ID >= 13 AND P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
351 RETURN ('and md.demand_source_name <= ''' || P_SOURCE_HI || ''' ');
352 ELSE
353 IF P_SOURCE_TYPE_ID IS NULL THEN
354 RETURN ('and md.demand_source_header_id = mkts.sales_order_id(+)
355 and md.demand_source_header_id = gl1.code_combination_id(+)
356 and md.demand_source_header_id = mdsp.disposition_id(+)
357 ');
358 ELSE
362 END IF;
359 NULL;
360 END IF;
361 END IF;
363 END IF;
364 END IF;
365 END IF;
366 END IF;
367 END IF;
368 END IF;
369 END IF;
370 END IF;
371 RETURN NULL;
372 END C_SOURCE_WHEREFORMULA;
373
374 FUNCTION C_SOURCE_FROMFORMULA(C_SOURCE_WHERE IN VARCHAR2) RETURN VARCHAR2 IS
375 BEGIN
376 IF P_SOURCE_TYPE_ID = 2 THEN
377 RETURN (',MTL_SALES_ORDERS mkts');
378 ELSE
379 IF P_SOURCE_TYPE_ID = 3 THEN
380 RETURN (',GL_CODE_COMBINATIONS gl1');
381 ELSE
382 IF P_SOURCE_TYPE_ID = 6 THEN
383 RETURN (',MTL_GENERIC_DISPOSITIONS mdsp');
384 ELSE
385 IF P_SOURCE_TYPE_ID = 5 THEN
386 IF C_SOURCE_WHERE IS NULL THEN
387 RETURN NULL;
388 ELSE
389 RETURN (',WIP_ENTITIES wip1');
390 END IF;
391 ELSE
392 IF P_SOURCE_TYPE_ID = 8 THEN
393 RETURN (',MTL_SALES_ORDERS mkts');
394 ELSE
395 IF P_SOURCE_TYPE_ID IS NULL THEN
396 RETURN (',MTL_SALES_ORDERS mkts,
397 MTL_GENERIC_DISPOSITIONS mdsp,
398 GL_CODE_COMBINATIONS gl1 ');
399 ELSE
400 NULL;
401 END IF;
402 END IF;
403 END IF;
404 END IF;
405 END IF;
406 END IF;
407 RETURN NULL;
408 END C_SOURCE_FROMFORMULA;
409
410 FUNCTION C_SOURCE_TYPE_NAMEFORMULA RETURN VARCHAR2 IS
411 BEGIN
412 DECLARE
413 SOURCE_TYPE_ID NUMBER;
414 NAME VARCHAR2(40);
415 BEGIN
416 IF P_SOURCE_TYPE_ID IS NOT NULL THEN
417 SOURCE_TYPE_ID := P_SOURCE_TYPE_ID;
418 SELECT
419 MAX(TRANSACTION_SOURCE_TYPE_NAME)
420 INTO NAME
421 FROM
422 MTL_TXN_SOURCE_TYPES
423 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
424 RETURN (NAME);
425 ELSE
426 NULL;
427 END IF;
428 EXCEPTION
429 WHEN NO_DATA_FOUND THEN
430 RETURN ('No Data');
431 WHEN OTHERS THEN
432 RETURN ('Error');
433 END;
434 RETURN NULL;
435 END C_SOURCE_TYPE_NAMEFORMULA;
436
437 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
438 BEGIN
439 DECLARE
440 NAME VARCHAR2(30);
441 SET_ID NUMBER;
442 BEGIN
443 IF P_CAT_SET_ID IS NULL THEN
444 RETURN ('');
445 ELSE
446 SET_ID := P_CAT_SET_ID;
447 SELECT
448 CATEGORY_SET_NAME
449 INTO NAME
450 FROM
451 MTL_CATEGORY_SETS
452 WHERE CATEGORY_SET_ID = SET_ID;
453 RETURN (NAME);
454 END IF;
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 RETURN ('No Data');
458 WHEN OTHERS THEN
459 RETURN ('Error');
460 END;
461 RETURN NULL;
462 END C_CAT_SET_NAMEFORMULA;
463
464 FUNCTION C_DATE_WHEREFORMULA RETURN VARCHAR2 IS
465 BEGIN
466 IF P_DATE_LO_V IS NOT NULL AND P_DATE_HI_V IS NOT NULL THEN
467 RETURN ('and md.requirement_date between ' || 'to_date(''' || P_DATE_LO_V || ''',' || '''DD-MON-RRRR HH24:MI:SS''' || ')' || ' and ' || 'to_date(''' || P_DATE_HI_V || ''',' || '''DD-MON-RRRR HH24:MI:SS''' || ')');
468 ELSE
469 IF P_DATE_LO_V IS NULL AND P_DATE_HI_V IS NOT NULL THEN
470 RETURN ('and md.requirement_date <= ' || 'to_date(''' || P_DATE_HI_V || ''',' || '''DD-MON-RRRR HH24:MI:SS''' || ')');
471 ELSE
472 IF P_DATE_HI_V IS NULL AND P_DATE_LO_V IS NOT NULL THEN
473 RETURN ('and md.requirement_date >= ' || 'to_date(''' || P_DATE_LO_V || ''',' || '''DD-MON-RRRR HH24:MI:SS''' || ')');
474 ELSE
475 NULL;
476 END IF;
477 END IF;
478 END IF;
479 RETURN NULL;
480 END C_DATE_WHEREFORMULA;
481
482 FUNCTION AFTERPFORM RETURN BOOLEAN IS
483 BEGIN
484 RETURN (TRUE);
485 END AFTERPFORM;
486
487 FUNCTION C_SRC_WHEREFORMULA RETURN VARCHAR2 IS
488 BEGIN
489 RETURN ('and md.demand_source_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
490 END C_SRC_WHEREFORMULA;
491
492 FUNCTION C_SORT_DATE_FIELDFORMULA(C_SORT_FLEX IN VARCHAR2) RETURN DATE IS
493 BEGIN
494 IF P_SORT_ID = 1 THEN
495 RETURN TO_DATE(C_SORT_FLEX
496 ,'J');
497 ELSE
498 NULL;
499 END IF;
500 RETURN NULL;
501 END C_SORT_DATE_FIELDFORMULA;
502
503 END INV_INVDRRSV_XMLP_PKG;
504
[Home] [Help]