1 PACKAGE BODY INV_INVDRRSV_XMLP_PKG AS
2 /* $Header: INVDRRSVB.pls 120.1.12020000.2 2012/10/11 12:09:50 zhizheng ship $ */
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
172 NULL;
169 ,3) = 3 THEN
170 NULL;
171 ELSE
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 /*begin of bug14601364
318 RETURN ('and md.demand_source_header_id = mkts.sales_order_id
319 and ' || P_SOURCE_WHERE);
320 end of bug14601364*/
321 RETURN (' '); --bug14601364
322 ELSE
323 IF P_SOURCE_TYPE_ID = 3 THEN
324 /*begin of bug14601364
325 RETURN ('and md.demand_source_header_id = gl1.code_combination_id
326 and ' || P_SOURCE_WHERE);
327 end of bug14601364*/
328 RETURN (' '); --bug14601364
329 ELSE
330 IF P_SOURCE_TYPE_ID = 6 THEN
331 /*begin of bug14601364
332 RETURN ('and md.demand_source_header_id = mdsp.disposition_id
336 ELSE
333 and ' || P_SOURCE_WHERE);
334 end of bug14601364*/
335 RETURN (' '); --bug14601364
337 IF P_SOURCE_TYPE_ID = 8 THEN
338 /*begin of bug14601364
339 RETURN ('and md.demand_source_header_id = mkts.sales_order_id
340 and ' || P_SOURCE_WHERE);
341 end of bug14601364*/
342 RETURN (' '); --bug14601364
343 ELSE
344 IF P_SOURCE_TYPE_ID = 5 AND P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
345 /*begin of bug14601364
346 RETURN ('and md.demand_source_header_id = wip1.wip_entity_id
347 and wip1.wip_entity_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
348 end of bug14601364*/
349 RETURN ('and wip1.wip_entity_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' '); --bug14601364
350 ELSE
351 IF P_SOURCE_TYPE_ID = 5 AND P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
352 /*begin of bug14601364
353 RETURN ('and md.demand_source_header_id = wip1.wip_entity_id
354 and wip1.wip_entity_name >= ''' || P_SOURCE_LO || ''' ');
355 end of bug14601364*/
356 RETURN ('and wip1.wip_entity_name >= ''' || P_SOURCE_LO || ''' '); --bug14601364
357 ELSE
358 IF P_SOURCE_TYPE_ID = 5 AND P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
359 /*begin of bug14601364
360 RETURN ('and md.demand_source_header_id = wip1.wip_entity_id
361 and wip1.wip_entity_name <= ''' || P_SOURCE_HI || ''' ');
362 end of bug14601364*/
363 RETURN ('and wip1.wip_entity_name <= ''' || P_SOURCE_HI || ''' '); --bug14601364
364 ELSE
365 IF P_SOURCE_TYPE_ID >= 13 AND P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NOT NULL THEN
366 RETURN ('and md.demand_source_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
367 ELSE
368 IF P_SOURCE_TYPE_ID >= 13 AND P_SOURCE_HI IS NULL AND P_SOURCE_LO IS NOT NULL THEN
369 RETURN ('and md.demand_source_name >= ''' || P_SOURCE_LO || ''' ');
370 ELSE
371 IF P_SOURCE_TYPE_ID >= 13 AND P_SOURCE_HI IS NOT NULL AND P_SOURCE_LO IS NULL THEN
372 RETURN ('and md.demand_source_name <= ''' || P_SOURCE_HI || ''' ');
373 ELSE
374 IF P_SOURCE_TYPE_ID IS NULL THEN
375 /*begin of bug14601364
376 RETURN ('and md.demand_source_header_id = mkts.sales_order_id(+)
377 and md.demand_source_header_id = gl1.code_combination_id(+)
378 and md.demand_source_header_id = mdsp.disposition_id(+)
379 ');
380 end of bug14601364*/
381 RETURN (' '); --bug14601364
382 ELSE
383 --NULL; --bug14601364
384 RETURN (' '); --bug14601364
385 END IF;
386 END IF;
387 END IF;
388 END IF;
389 END IF;
390 END IF;
391 END IF;
392 END IF;
393 END IF;
394 END IF;
395 END IF;
396 RETURN NULL;
397 END C_SOURCE_WHEREFORMULA;
398
399 FUNCTION C_SOURCE_FROMFORMULA(C_SOURCE_WHERE IN VARCHAR2) RETURN VARCHAR2 IS
400 BEGIN
401 /*begin of bug14601364
402 IF P_SOURCE_TYPE_ID = 2 THEN
403 RETURN (',MTL_SALES_ORDERS mkts');
404 ELSE
405 IF P_SOURCE_TYPE_ID = 3 THEN
406 RETURN (',GL_CODE_COMBINATIONS gl1');
407 ELSE
408 IF P_SOURCE_TYPE_ID = 6 THEN
409 RETURN (',MTL_GENERIC_DISPOSITIONS mdsp');
410 ELSE
411 IF P_SOURCE_TYPE_ID = 5 THEN
412 IF C_SOURCE_WHERE IS NULL THEN
413 RETURN NULL;
414 ELSE
415 RETURN (',WIP_ENTITIES wip1');
416 END IF;
417 ELSE
418 IF P_SOURCE_TYPE_ID = 8 THEN
419 RETURN (',MTL_SALES_ORDERS mkts');
420 ELSE
421 IF P_SOURCE_TYPE_ID IS NULL THEN
422 RETURN (',MTL_SALES_ORDERS mkts,
423 MTL_GENERIC_DISPOSITIONS mdsp,
424 GL_CODE_COMBINATIONS gl1 ');
425 ELSE
426 NULL;
427 END IF;
428 END IF;
429 END IF;
430 END IF;
431 END IF;
432 END IF;
433 RETURN NULL;
434 end of bug14601364*/
435 RETURN (' '); --bug14601364
436 END C_SOURCE_FROMFORMULA;
437
438 FUNCTION C_SOURCE_TYPE_NAMEFORMULA RETURN VARCHAR2 IS
439 BEGIN
440 DECLARE
441 SOURCE_TYPE_ID NUMBER;
442 NAME VARCHAR2(40);
443 BEGIN
444 IF P_SOURCE_TYPE_ID IS NOT NULL THEN
445 SOURCE_TYPE_ID := P_SOURCE_TYPE_ID;
446 SELECT
447 MAX(TRANSACTION_SOURCE_TYPE_NAME)
448 INTO NAME
449 FROM
450 MTL_TXN_SOURCE_TYPES
451 WHERE TRANSACTION_SOURCE_TYPE_ID = SOURCE_TYPE_ID;
452 RETURN (NAME);
453 ELSE
454 NULL;
455 END IF;
456 EXCEPTION
457 WHEN NO_DATA_FOUND THEN
458 RETURN ('No Data');
459 WHEN OTHERS THEN
460 RETURN ('Error');
461 END;
462 RETURN NULL;
463 END C_SOURCE_TYPE_NAMEFORMULA;
464
465 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
466 BEGIN
470 BEGIN
467 DECLARE
468 NAME VARCHAR2(30);
469 SET_ID NUMBER;
471 IF P_CAT_SET_ID IS NULL THEN
472 RETURN ('');
473 ELSE
474 SET_ID := P_CAT_SET_ID;
475 SELECT
476 CATEGORY_SET_NAME
477 INTO NAME
478 FROM
479 MTL_CATEGORY_SETS
480 WHERE CATEGORY_SET_ID = SET_ID;
481 RETURN (NAME);
482 END IF;
483 EXCEPTION
484 WHEN NO_DATA_FOUND THEN
485 RETURN ('No Data');
486 WHEN OTHERS THEN
487 RETURN ('Error');
488 END;
489 RETURN NULL;
490 END C_CAT_SET_NAMEFORMULA;
491
492 FUNCTION C_DATE_WHEREFORMULA RETURN VARCHAR2 IS
493 BEGIN
494 IF P_DATE_LO_V IS NOT NULL AND P_DATE_HI_V IS NOT NULL THEN
495 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''' || ')');
496 ELSE
497 IF P_DATE_LO_V IS NULL AND P_DATE_HI_V IS NOT NULL THEN
498 RETURN ('and md.requirement_date <= ' || 'to_date(''' || P_DATE_HI_V || ''',' || '''DD-MON-RRRR HH24:MI:SS''' || ')');
499 ELSE
500 IF P_DATE_HI_V IS NULL AND P_DATE_LO_V IS NOT NULL THEN
501 RETURN ('and md.requirement_date >= ' || 'to_date(''' || P_DATE_LO_V || ''',' || '''DD-MON-RRRR HH24:MI:SS''' || ')');
502 ELSE
503 NULL;
504 END IF;
505 END IF;
506 END IF;
507 RETURN NULL;
508 END C_DATE_WHEREFORMULA;
509
510 FUNCTION AFTERPFORM RETURN BOOLEAN IS
511 BEGIN
512 RETURN (TRUE);
513 END AFTERPFORM;
514
515 FUNCTION C_SRC_WHEREFORMULA RETURN VARCHAR2 IS
516 BEGIN
517 RETURN ('and md.demand_source_name between ''' || P_SOURCE_LO || ''' and ''' || P_SOURCE_HI || ''' ');
518 END C_SRC_WHEREFORMULA;
519
520 FUNCTION C_SORT_DATE_FIELDFORMULA(C_SORT_FLEX IN VARCHAR2) RETURN DATE IS
521 BEGIN
522 IF P_SORT_ID = 1 THEN
523 RETURN TO_DATE(C_SORT_FLEX
524 ,'J');
525 ELSE
526 NULL;
527 END IF;
528 RETURN NULL;
529 END C_SORT_DATE_FIELDFORMULA;
530
531 END INV_INVDRRSV_XMLP_PKG;
532
[Home] [Help]