1 PACKAGE BODY INV_INVTRREG_XMLP_PKG AS
2 /* $Header: INVTRREGB.pls 120.2 2008/01/08 06:46:51 dwkrishn noship $ */
3 FUNCTION P_STRUCT_NUMVALIDTRIGGER RETURN BOOLEAN IS
4 BEGIN
5 RETURN (TRUE);
6 END P_STRUCT_NUMVALIDTRIGGER;
7 FUNCTION WHERE_REASON RETURN VARCHAR2 IS
8 BEGIN
9 DECLARE
10 LO VARCHAR2(50);
11 HI VARCHAR2(50);
12 BEGIN
13 LO := P_REASON_LO;
14 HI := P_REASON_HI;
15 IF P_REASON_LO IS NULL AND P_REASON_HI IS NULL THEN
16 RETURN (' ');
17 ELSE
18 IF P_REASON_LO IS NOT NULL AND P_REASON_HI IS NULL THEN
19 RETURN (' AND mtr.reason_name >= ''' || LO || ''' ');
20 ELSE
21 IF P_REASON_LO IS NULL AND P_REASON_HI IS NOT NULL THEN
22 RETURN (' AND mtr.reason_name <= ''' || HI || ''' ');
23 ELSE
24 RETURN (' AND mtr.reason_name between ''' || LO || ''' and ''' || HI || ''' ');
25 END IF;
26 END IF;
27 END IF;
28 END;
29 RETURN (' ');
30 END WHERE_REASON;
31 FUNCTION WHERE_SUBINV RETURN VARCHAR2 IS
32 BEGIN
33 DECLARE
34 LO VARCHAR2(10);
35 HI VARCHAR2(10);
36 BEGIN
37 LO := P_SUBINV_LO;
38 HI := P_SUBINV_HI;
39 IF P_SUBINV_LO IS NULL AND P_SUBINV_HI IS NULL THEN
40 RETURN (' ');
41 ELSE
42 IF P_SUBINV_LO IS NOT NULL AND P_SUBINV_HI IS NULL THEN
43 RETURN (' AND mmt.subinventory_code >= ''' || LO || ''' ');
44 ELSE
45 IF P_SUBINV_LO IS NULL AND P_SUBINV_HI IS NOT NULL THEN
46 RETURN (' AND mmt.subinventory_code <= ''' || HI || ''' ');
47 ELSE
48 RETURN (' AND mmt.subinventory_code between ''' || LO || ''' and ''' || HI || ''' ');
49 END IF;
50 END IF;
51 END IF;
52 END;
53 RETURN (' ');
54 END WHERE_SUBINV;
55 FUNCTION WHERE_TXN_TYPE RETURN VARCHAR2 IS
56 BEGIN
57 DECLARE
58 LO VARCHAR2(240);
59 HI VARCHAR2(240);
60 BEGIN
61 LO := P_TXN_TYPE_LO;
62 HI := P_TXN_TYPE_HI;
63 IF P_TXN_TYPE_LO IS NULL AND P_TXN_TYPE_HI IS NULL THEN
64 RETURN (' ');
65 ELSE
66 IF P_TXN_TYPE_LO IS NOT NULL AND P_TXN_TYPE_HI IS NULL THEN
67 RETURN (' AND mtxt.transaction_type_name >= ''' || LO || ''' ');
68 ELSE
69 IF P_TXN_TYPE_LO IS NULL AND P_TXN_TYPE_HI IS NOT NULL THEN
70 RETURN (' AND mtxt.transaction_type_name <= ''' || HI || ''' ');
71 ELSE
72 RETURN (' AND mtxt.transaction_type_name between ''' || LO || ''' and ''' || HI || ''' ');
73 END IF;
74 END IF;
75 END IF;
76 END;
77 RETURN (' ');
78 END WHERE_TXN_TYPE;
79 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
80 BEGIN
81 BEGIN
82 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
83 P_ITEM_ORDER_BY := nvl(P_ITEM_ORDER_BY,1);
84 P_CATG_ORDER_BY := nvl(P_CATG_ORDER_BY,1);
85 QTY_PRECISION:= inv_common_xmlp_pkg.get_precision(P_qty_precision);
86 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
87 EXCEPTION
88 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
89 /*SRW.MESSAGE(010
90 ,'Failed in before report trigger, srwinit. ')*/NULL;
91 RAISE;
92 END;
93 DECLARE
94 P_ORG_ID_CHAR VARCHAR2(100) := TO_CHAR(P_ORG);
95 BEGIN
96 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
97 EXCEPTION
98 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
99 /*SRW.MESSAGE(020
100 ,'Failed in before report trigger, setting org profile ')*/NULL;
101 RAISE;
102 END;
103 BEGIN
104 NULL;
105 EXCEPTION
106 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
107 /*SRW.MESSAGE(020
108 ,'Failed in before report trigger, item select. ')*/NULL;
109 RAISE;
110 END;
111 BEGIN
112 NULL;
113 EXCEPTION
114 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
115 /*SRW.MESSAGE(030
116 ,'Failed in before report trigger, locator select. ')*/NULL;
117 RAISE;
118 END;
119 BEGIN
120 NULL;
121 EXCEPTION
122 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
123 /*SRW.MESSAGE(040
124 ,'Failed in before report trigger, mkts select. ')*/NULL;
125 RAISE;
126 END;
127 BEGIN
128 NULL;
129 EXCEPTION
130 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
131 /*SRW.MESSAGE(045
132 ,'Failed in before report trigger, mkts order by.')*/NULL;
133 RAISE;
134 END;
135 BEGIN
136 NULL;
137 EXCEPTION
138 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
139 /*SRW.MESSAGE(050
140 ,'Failed in before report trigger, mdsp select. ')*/NULL;
141 RAISE;
142 END;
143 BEGIN
144 NULL;
145 EXCEPTION
146 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
147 /*SRW.MESSAGE(055
148 ,'Failed in before report trigger, mdsp order by. ')*/NULL;
149 RAISE;
150 END;
151 BEGIN
152 NULL;
153 EXCEPTION
154 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
155 /*SRW.MESSAGE(060
156 ,'Failed in before report triger, glcc select. ')*/NULL;
157 RAISE;
158 END;
159 BEGIN
160 NULL;
161 EXCEPTION
162 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
163 /*SRW.MESSAGE(065
164 ,'Failed in before report trigger, glcc order by. ')*/NULL;
165 RAISE;
166 END;
167 BEGIN
168 NULL;
169 EXCEPTION
170 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
171 /*SRW.MESSAGE(070
172 ,'Failed in before report trigger, catg select. ')*/NULL;
173 RAISE;
174 END;
175 BEGIN
176 NULL;
177 EXCEPTION
178 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
179 /*SRW.MESSAGE(080
180 ,'Failed in before report trigger, item order by. ')*/NULL;
181 RAISE;
182 END;
183 BEGIN
184 NULL;
185 EXCEPTION
186 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
187 /*SRW.MESSAGE(100
188 ,'Failed in before report trigger, catg order by. ')*/NULL;
189 RAISE;
190 END;
191 BEGIN
192 NULL;
193 EXCEPTION
194 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
195 /*SRW.MESSAGE(110
196 ,'Failed in before report trigger, item where. ')*/NULL;
197 RAISE;
198 END;
199 BEGIN
200 NULL;
201 EXCEPTION
202 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
203 /*SRW.MESSAGE(120
204 ,'Failed in before report trigger, catg where. ')*/NULL;
205 RAISE;
206 END;
207 IF P_TXN_SOURCE_TYPE_ID in (2,8,12) THEN
208 BEGIN
209 IF P_TXN_SOURCE_HI IS NOT NULL OR P_TXN_SOURCE_LO IS NOT NULL THEN
210 NULL;
211 ELSE
212 NULL;
213 END IF;
214 EXCEPTION
215 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
216 /*SRW.MESSAGE(1
217 ,'Failed in before report trigger:MKTS/where')*/NULL;
218 RAISE;
219 END;
220 ELSE
221 IF P_TXN_SOURCE_TYPE_ID = 6 THEN
222 BEGIN
223 IF P_TXN_SOURCE_HI IS NOT NULL OR P_TXN_SOURCE_LO IS NOT NULL THEN
224 NULL;
225 ELSE
226 NULL;
227 END IF;
228 EXCEPTION
229 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
230 /*SRW.MESSAGE(1
231 ,'Failed in before report trigger:MDSP/where')*/NULL;
232 RAISE;
233 END;
234 ELSE
235 IF P_TXN_SOURCE_TYPE_ID = 3 THEN
236 BEGIN
237 IF P_TXN_SOURCE_HI IS NOT NULL OR P_TXN_SOURCE_LO IS NOT NULL THEN
238 NULL;
239 ELSE
240 NULL;
241 END IF;
242 EXCEPTION
243 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
244 /*SRW.MESSAGE(1
245 ,'Failed in before report trigger:GL/where')*/NULL;
246 RAISE;
247 END;
248 ELSE
249 NULL;
250 END IF;
251 END IF;
252 END IF;
253 RETURN (TRUE);
254 RETURN (TRUE);
255 END BEFOREREPORT;
256 FUNCTION C_SOURCE_WHEREFORMULA RETURN VARCHAR2 IS
257 BEGIN
258 BEGIN
259 IF P_TXN_SOURCE_TYPE_ID in (2,8,12) AND (P_TXN_SOURCE_LO IS NOT NULL OR P_TXN_SOURCE_HI IS NOT NULL) THEN
260 RETURN ('and mmt.transaction_source_id = mkts.sales_order_id and ' || P_SOURCE_WHERE);
261 ELSE
262 IF P_TXN_SOURCE_TYPE_ID = 3 AND (P_TXN_SOURCE_LO IS NOT NULL OR P_TXN_SOURCE_HI IS NOT NULL) THEN
263 RETURN ('and mmt.transaction_source_id = glc.code_combination_id and ' || P_SOURCE_WHERE);
264 ELSE
265 IF P_TXN_SOURCE_TYPE_ID = 6 AND (P_TXN_SOURCE_LO IS NOT NULL OR P_TXN_SOURCE_HI IS NOT NULL) THEN
266 RETURN ('and mmt.transaction_source_id = mdsp.disposition_id
267 and ' || P_SOURCE_WHERE);
268 END IF;
269 END IF;
270 END IF;
271 END;
272 BEGIN
273 IF P_TXN_SOURCE_TYPE_ID = 1 THEN
274 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
275 RETURN ('and mmt.transaction_source_id = poh.po_header_id
276 and poh.segment1 between ''' || P_TXN_SOURCE_LO || ''' and ''' || P_TXN_SOURCE_HI || ''' ');
277 ELSE
278 IF P_TXN_SOURCE_HI IS NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
279 RETURN ('and mmt.transaction_source_id = poh.po_header_id
280 and poh.segment1 >= ''' || P_TXN_SOURCE_LO || ''' ');
281 ELSE
282 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NULL THEN
283 RETURN ('and mmt.transaction_source_id = poh.po_header_id
284 and poh.segment1 <= ''' || P_TXN_SOURCE_HI || ''' ');
285 END IF;
286 END IF;
287 END IF;
288 END IF;
289 END;
290 BEGIN
291 IF P_TXN_SOURCE_TYPE_ID = 5 THEN
292 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
293 RETURN ('and mmt.transaction_source_id = wipe.wip_entity_id
294 and wipe.wip_entity_name between ''' || P_TXN_SOURCE_LO || ''' and ''' || P_TXN_SOURCE_HI || ''' ');
295 ELSE
296 IF P_TXN_SOURCE_HI IS NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
297 RETURN ('and mmt.transaction_source_id = wipe.wip_entity_id
298 and wipe.wip_entity_name >= ''' || P_TXN_SOURCE_LO || ''' ');
299 ELSE
300 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NULL THEN
301 RETURN ('and mmt.transaction_source_id = wipe.wip_entity_id
302 and wipe.wip_entity_name <= ''' || P_TXN_SOURCE_HI || ''' ');
303 END IF;
304 END IF;
305 END IF;
306 END IF;
307 END;
308 BEGIN
309 IF P_TXN_SOURCE_TYPE_ID = 7 THEN
310 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
311 RETURN ('and mmt.transaction_source_id = porh.requisition_header_id
312 and porh.segment1 between ''' || P_TXN_SOURCE_LO || ''' and ''' || P_TXN_SOURCE_HI || ''' ');
313 ELSE
314 IF P_TXN_SOURCE_HI IS NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
315 RETURN ('and mmt.transaction_source_id = porh.requisition_header_id
316 and porh.segment1 >= ''' || P_TXN_SOURCE_LO || ''' ');
317 ELSE
318 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NULL THEN
319 RETURN ('and mmt.transaction_source_id = porh.requisition_header_id
320 and porh.segment1 <= ''' || P_TXN_SOURCE_HI || ''' ');
321 END IF;
322 END IF;
323 END IF;
324 END IF;
325 END;
326 BEGIN
327 IF P_TXN_SOURCE_TYPE_ID = 9 THEN
328 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
329 RETURN ('and mmt.transaction_source_id = CCH.cycle_count_header_id
330 and CCH.cycle_count_header_name between ''' || P_TXN_SOURCE_LO || ''' and ''' || P_TXN_SOURCE_HI || ''' ');
331 ELSE
332 IF P_TXN_SOURCE_HI IS NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
333 RETURN ('and mmt.transaction_source_id = CCH.cycle_count_header_id
334 and CCH.cycle_count_header_name >= ''' || P_TXN_SOURCE_LO || ''' ');
335 ELSE
336 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NULL THEN
337 RETURN ('and mmt.transaction_source_id = CCH.cycle_count_header_id
338 and CCH.cycle_count_header_name <= ''' || P_TXN_SOURCE_HI || ''' ');
339 END IF;
340 END IF;
341 END IF;
342 END IF;
343 END;
344 BEGIN
345 IF P_TXN_SOURCE_TYPE_ID = 10 THEN
346 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
347 RETURN ('and mmt.transaction_source_id = mpi.physical_inventory_id
348 and mpi.physical_inventory_name between ''' || P_TXN_SOURCE_LO || ''' and ''' || P_TXN_SOURCE_HI || ''' ');
349 ELSE
350 IF P_TXN_SOURCE_HI IS NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
351 RETURN ('and mmt.transaction_source_id = mpi.physical_inventory_id
352 and mpi.physical_inventory_name >= ''' || P_TXN_SOURCE_LO || ''' ');
353 ELSE
354 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NULL THEN
355 RETURN ('and mmt.transaction_source_id = mpi.physical_inventory_id
356 and mpi.physical_inventory_name <= ''' || P_TXN_SOURCE_HI || ''' ');
357 END IF;
358 END IF;
359 END IF;
360 END IF;
361 END;
362 BEGIN
363 IF P_TXN_SOURCE_TYPE_ID = 11 THEN
364 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
365 RETURN ('and mmt.transaction_source_id = cupd.cost_update_id
366 and cupd.description between ''' || P_TXN_SOURCE_LO || ''' and ''' || P_TXN_SOURCE_HI || ''' ');
367 ELSE
368 IF P_TXN_SOURCE_HI IS NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
369 RETURN ('and mmt.transaction_source_id = cupd.cost_update_id
370 and cupd.description between >= ''' || P_TXN_SOURCE_LO || ''' ');
371 ELSE
372 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NULL THEN
373 RETURN ('and mmt.transaction_source_id = cupd.cost_update_id
374 and cupd.description <= ''' || P_TXN_SOURCE_HI || ''' ');
375 END IF;
376 END IF;
377 END IF;
378 END IF;
379 END;
380 BEGIN
381 IF P_TXN_SOURCE_TYPE_ID >= 13 THEN
382 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
383 RETURN ('and mmt.transaction_source_name between ''' || P_TXN_SOURCE_LO || ''' and ''' || P_TXN_SOURCE_HI || ''' ');
384 ELSE
385 IF P_TXN_SOURCE_HI IS NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
386 RETURN ('and mmt.transaction_source_name >= ''' || P_TXN_SOURCE_LO || ''' ');
387 ELSE
388 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NULL THEN
389 RETURN ('and mmt.transaction_source_name <= ''' || P_TXN_SOURCE_HI || ''' ');
390 END IF;
391 END IF;
392 END IF;
393 END IF;
394 END;
395 BEGIN
396 IF P_TXN_SOURCE_TYPE_ID = 4 THEN
397 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
398 RETURN ('and mmt.transaction_source_id = mtrh.header_id
399 and mtrh.request_number between ''' || P_TXN_SOURCE_LO || ''' and ''' || P_TXN_SOURCE_HI || ''' ');
400 ELSE
401 IF P_TXN_SOURCE_HI IS NULL AND P_TXN_SOURCE_LO IS NOT NULL THEN
402 RETURN ('and mmt.transaction_source_id = mtrh.header_id
403 and mtrh.request_number >= ''' || P_TXN_SOURCE_LO || ''' ');
404 ELSE
405 IF P_TXN_SOURCE_HI IS NOT NULL AND P_TXN_SOURCE_LO IS NULL THEN
406 RETURN ('and mmt.transaction_source_id = mtrh.header_id
407 and mtrh.request_number <= ''' || P_TXN_SOURCE_HI || ''' ');
408 END IF;
409 END IF;
410 END IF;
411 END IF;
412 END;
413 RETURN (' ');
414 END C_SOURCE_WHEREFORMULA;
415 FUNCTION C_TYPE_BREAK_COLUMNFORMULA RETURN VARCHAR2 IS
416 BEGIN
417 IF P_BREAK_ID = 4 THEN
418 RETURN (' mtst.transaction_source_type_name ');
419 ELSE
420 RETURN (' mtxt1.transaction_type_name ');
421 END IF;
422 RETURN NULL;
423 END C_TYPE_BREAK_COLUMNFORMULA;
424 FUNCTION C_TYPE_BREAK_FROMFORMULA RETURN VARCHAR2 IS
425 BEGIN
426 IF P_BREAK_ID = 4 THEN
427 RETURN (' , MTL_TXN_SOURCE_TYPES mtst ');
428 ELSE
429 RETURN (' , MTL_TRANSACTION_TYPES mtxt1 ');
430 END IF;
431 RETURN NULL;
432 END C_TYPE_BREAK_FROMFORMULA;
433 FUNCTION C_TYPE_BREAK_WHEREFORMULA RETURN VARCHAR2 IS
434 BEGIN
435 IF P_BREAK_ID = 4 THEN
436 RETURN (' and mmt.transaction_source_type_id = mtst.transaction_source_type_id ');
437 ELSE
438 RETURN (' and mmt.transaction_type_id = mtxt1.transaction_type_id ');
439 END IF;
440 RETURN (' ');
441 END C_TYPE_BREAK_WHEREFORMULA;
442 FUNCTION C_RPT_DATE_CUR_TITLEFORMULA(C_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
443 BEGIN
444 /* RETURN (' From ' || TO_CHAR(P_TXN_DATE_LO
445 ,'DD-MON-RRRR') || ' to ' || TO_CHAR(P_TXN_DATE_HI
446 ,'DD-MON-RRRR') || ' (' || C_CURRENCY_CODE || ')');*/
447 RETURN (' From ' || TO_CHAR(P_TXN_DATE_LO
448 ,'DD-MON-RRRR') || ' to ' || TO_CHAR(LP_TXN_DATE_HI
449 ,'DD-MON-RRRR') || ' (' || C_CURRENCY_CODE || ')');
450 END C_RPT_DATE_CUR_TITLEFORMULA;
451 FUNCTION C_ORDER_BYFORMULA RETURN VARCHAR2 IS
452 BEGIN
453 IF P_BREAK_ID = 1 THEN
454 RETURN (' order by ' || P_ITEM_ORDER_BY || ' , mmt.transaction_date, mmt.revision');
455 ELSE
456 IF P_BREAK_ID = 2 THEN
457 RETURN (' order by mmt.transaction_date, ' || P_ITEM_ORDER_BY || ' ,mmt.revision');
458 ELSE
459 IF P_BREAK_ID = 3 THEN
460 RETURN (' order by mtxt1.transaction_type_name, mmt.transaction_source_id,
461 mmt.transaction_source_name, mmt.transaction_date, ' || P_ITEM_ORDER_BY);
462 ELSE
463 IF P_BREAK_ID = 4 THEN
464 RETURN (' order by
465 mtst.transaction_source_type_name, mmt.transaction_source_id,mmt.transaction_source_name, mmt.transaction_date, ' || P_ITEM_ORDER_BY);
466 ELSE
467 IF P_BREAK_ID = 5 THEN
468 RETURN (' order by mtr.reason_name, mmt.transaction_date, ' || P_ITEM_ORDER_BY);
469 ELSE
470 IF P_BREAK_ID = 6 THEN
471 RETURN (' order by mmt.subinventory_code, mmt.transaction_date, ' || P_ITEM_ORDER_BY);
472 ELSE
473 RETURN (' order by ' || P_CATG_ORDER_BY || ' , mmt.transaction_date, ' || P_ITEM_ORDER_BY);
474 END IF;
475 END IF;
476 END IF;
477 END IF;
478 END IF;
479 END IF;
480 RETURN (' ');
481 END C_ORDER_BYFORMULA;
482 FUNCTION P_MDSP_ORDER_BYVALIDTRIGGER RETURN BOOLEAN IS
483 BEGIN
484 RETURN (TRUE);
485 END P_MDSP_ORDER_BYVALIDTRIGGER;
486 FUNCTION AFTERREPORT RETURN BOOLEAN IS
487 BEGIN
488 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
489 RETURN (TRUE);
490 END AFTERREPORT;
491 FUNCTION CALC_UNIT_COST(UNROUNDED_QTY IN NUMBER
492 ,UNROUNDED_TOT_COST IN NUMBER
493 ,C_EXT_PRECISION IN VARCHAR2) RETURN NUMBER IS
494 BEGIN
495 DECLARE
496 IN_QTY NUMBER;
497 IN_EXTD_COST NUMBER;
498 IN_ROUND_AT NUMBER;
499 OUT_COST NUMBER;
500 BEGIN
501 IN_QTY := UNROUNDED_QTY;
502 IN_EXTD_COST := UNROUNDED_TOT_COST;
503 IN_ROUND_AT := C_EXT_PRECISION;
504 IF IN_QTY = 0 THEN
505 OUT_COST := ROUND(0
506 ,IN_ROUND_AT);
507 ELSE
508 OUT_COST := ROUND((IN_EXTD_COST / IN_QTY)
509 ,IN_ROUND_AT);
510 END IF;
511 RETURN (OUT_COST);
512 END;
513 RETURN NULL;
514 END CALC_UNIT_COST;
515 FUNCTION AFTERPFORM RETURN BOOLEAN IS
516 BEGIN
517 /*P_TXN_DATE_HI := TRUNC(P_TXN_DATE_HI);
518 P_TXN_DATE_HI := TO_DATE(TO_CHAR(P_TXN_DATE_HI
519 ,'DD-MON-RRRR') || ' 23:59:59'
520 ,'DD-MON-YYYY HH24:MI:SS');
521 */
522 SELECT ARGUMENT6, ARGUMENT7 INTO LP_TXN_DATE_LO1,LP_TXN_DATE_HI1 FROM FND_CONCURRENT_REQUESTS
523 WHERE REQUEST_ID=FND_GLOBAL.CONC_REQUEST_ID;
524 LP_TXN_DATE_HI1 := SUBSTR(LP_TXN_DATE_HI1, 1, 10);
525 LP_TXN_DATE_LO1 := SUBSTR(LP_TXN_DATE_LO1, 1, 10);
526 LP_TXN_DATE_HI := TO_DATE(LP_TXN_DATE_HI1 || ' 23:59:59'
527 ,'YYYY/MM/DD HH24:MI:SS');
528 LP_TXN_DATE_LO := TO_DATE(LP_TXN_DATE_LO1 || ' 00:00:00'
529 ,'YYYY/MM/DD HH24:MI:SS');
530 RETURN (TRUE);
531 END AFTERPFORM;
532 FUNCTION C_SOURCE_TYPE_WHEREFORMULA RETURN VARCHAR2 IS
533 BEGIN
534 BEGIN
535 IF P_TXN_SOURCE_TYPE_ID IS NOT NULL THEN
536 RETURN (' and mmt.transaction_source_type_id = ' || TO_CHAR(P_TXN_SOURCE_TYPE_ID));
537 END IF;
538 END;
539 RETURN (' ');
540 END C_SOURCE_TYPE_WHEREFORMULA;
541 FUNCTION C_BREAK_DATE_VALUEFORMULA(BREAK_COLUMN IN VARCHAR2) RETURN DATE IS
542 BEGIN
543 BEGIN
544 /*SRW.REFERENCE(BREAK_COLUMN)*/NULL;
545 /*SRW.REFERENCE(P_BREAK_ID)*/NULL;
546 IF (P_BREAK_ID <> 2) THEN
547 RETURN (TO_DATE('01-01-1901','dd-mm-yyyy'));
548 ELSE
549 RETURN (TO_DATE(BREAK_COLUMN,'J'));
550 END IF;
551 END;
552 RETURN ('');
553 END C_BREAK_DATE_VALUEFORMULA;
554 END INV_INVTRREG_XMLP_PKG;
555
556