[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRINVR_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRINVR_XMLP_PKG AS
2 /* $Header: CSTRINVRB.pls 120.1 2008/01/06 05:42:29 nchinnam noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
6 EXECUTE IMMEDIATE
7 'ROLLBACK';
8 /*SRW.MESSAGE(0
9 ,'BOM_CSTRINVR_XMLP_PKG >> ' || TO_CHAR(SYSDATE
10 ,'Dy Mon DD HH24:MI:SS YYYY'))*/NULL;
11 RETURN (TRUE);
12 END AFTERREPORT;
13 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
14 BEGIN
15 DECLARE
16 L_FCN_CURRENCY VARCHAR2(15);
17 L_REPORT_NAME VARCHAR2(80);
18 SQL_STMT_NUM VARCHAR2(5);
19 L_EXP_SUB NUMBER := P_EXP_SUB;
20 L_MSG_COUNT NUMBER;
21 L_MSG_DATA VARCHAR2(8000);
22 L_RETURN_STATUS VARCHAR2(1);
23 L_AS_OF_DATE VARCHAR2(30);
24 L_COST NUMBER;
25 WMS_ORG_COUNT NUMBER;
26 PJM_ORG_COUNT NUMBER;
27 L_CALCULATE_INTRANSIT NUMBER;
28 L_CST_INV_VAL EXCEPTION;
29 BEGIN
30 SQL_STMT_NUM := '0: ';
31 P_EXCHANGE_RATE := FND_NUMBER.CANONICAL_TO_NUMBER(P_EXCHANGE_RATE_CHAR);
32 SQL_STMT_NUM := '1: ';
33 IF P_VIEW_COST <> 1 THEN
34 FND_MESSAGE.SET_NAME('null'
35 ,'null');
36 /*SRW.USER_EXIT('FND MESSAGE_DISPLAY')*/NULL;
37 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
38 END IF;
39 SQL_STMT_NUM := '3: ';
40 SELECT
41 O.ORGANIZATION_NAME,
42 MP.COST_ORGANIZATION_ID,
43 SOB.CURRENCY_CODE,
44 NVL(EXTENDED_PRECISION
45 ,PRECISION),
46 NVL(FC.PRECISION
47 ,0),
48 NVL(MINIMUM_ACCOUNTABLE_UNIT
49 ,POWER(10
50 ,NVL(-PRECISION
51 ,0))),
52 MCS.CATEGORY_SET_NAME,
53 DEFAULT_COST_TYPE_ID,
54 COST_TYPE,
55 LU2.MEANING,
56 USERENV('SESSIONID')
57 INTO P_ORGANIZATION,P_COST_ORG_ID,L_FCN_CURRENCY,P_EXT_PREC,P_PRECISION,ROUND_UNIT,P_CAT_SET_NAME,P_DEF_COST_TYPE,P_COST_TYPE,P_DETAIL_LEVEL,P_SESSIONID
58 FROM
59 ORG_ORGANIZATION_DEFINITIONS O,
60 MTL_PARAMETERS MP,
61 GL_SETS_OF_BOOKS SOB,
62 FND_CURRENCIES FC,
63 MTL_CATEGORY_SETS MCS,
64 CST_COST_TYPES,
65 MFG_LOOKUPS LU2
66 WHERE FC.CURRENCY_CODE = P_CURRENCY_CODE
67 AND O.ORGANIZATION_ID = P_ORG_ID
68 AND MP.ORGANIZATION_ID = P_ORG_ID
69 AND SOB.SET_OF_BOOKS_ID = O.SET_OF_BOOKS_ID
70 AND MCS.CATEGORY_SET_ID = P_CATEGORY_SET
71 AND COST_TYPE_ID = P_COST_TYPE_ID
72 AND LU2.LOOKUP_TYPE = 'CST_BICR_DETAIL_OPTION'
73 AND LU2.LOOKUP_CODE = P_RPT_OPTION;
74 BEGIN
75 SELECT
76 MEANING
77 INTO P_SORT_BY
78 FROM
79 MFG_LOOKUPS
80 WHERE LOOKUP_TYPE = 'CST_ITEM_REPORT_SORT'
81 AND LOOKUP_CODE = P_SORT_OPTION;
82 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
83 IF P_CONC_REQUEST_ID > 0 THEN
84 SELECT
85 USER_CONCURRENT_PROGRAM_NAME
86 INTO P_REPORT_NAME
87 FROM
88 FND_CONCURRENT_PROGRAMS_VL P,
89 FND_CONCURRENT_REQUESTS R
90 WHERE R.REQUEST_ID = P_CONC_REQUEST_ID
91 AND P.APPLICATION_ID = R.PROGRAM_APPLICATION_ID
92 AND P.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID;
93 ELSE
94 SELECT
95 USER_CONCURRENT_PROGRAM_NAME
96 INTO P_REPORT_NAME
97 FROM
98 FND_CONCURRENT_PROGRAMS_VL P
99 WHERE P.APPLICATION_ID = 702
100 AND P.CONCURRENT_PROGRAM_NAME = DECODE(P_SORT_OPTION
101 ,4
102 ,'CSTRSAVR'
103 ,'BOM_CSTRINVR_XMLP_PKG');
104 END IF;
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 NULL;
108 WHEN OTHERS THEN
109 RAISE;
110 END;
111 SQL_STMT_NUM := '4: ';
112 IF L_FCN_CURRENCY = P_CURRENCY_CODE THEN
113 P_CURRENCY_DSP := P_CURRENCY_CODE;
114 ELSE
115 P_CURRENCY_DSP := P_CURRENCY_CODE || ' @ ' || TO_CHAR(ROUND(1 / P_EXCHANGE_RATE
116 ,5)) || L_FCN_CURRENCY;
117 END IF;
118 SQL_STMT_NUM := '5: ';
119 IF P_SORT_OPTION = 3 THEN
120 /*SRW.SET_MAXROW('Q_IC_MAIN'
121 ,0)*/NULL;
122 ELSE
123 /*SRW.SET_MAXROW('Q_SI_SUBINV'
124 ,0)*/NULL;
125 /*SRW.SET_MAXROW('Q_SI_MAIN'
126 ,0)*/NULL;
127 END IF;
128 BEGIN
129
130 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
131 EXCEPTION
132 WHEN OTHERS THEN
133 /*SRW.MESSAGE(999
134 ,'FND SRWINIT >X')*/NULL;
135 RAISE;
136 END;
137 BEGIN
138 NULL;
139 EXCEPTION
140 WHEN OTHERS THEN
141 /*SRW.MESSAGE(999
142 ,'FND FLEXSQL(MCAT) >X')*/NULL;
143 RAISE;
144 END;
145 BEGIN
146 NULL;
147 EXCEPTION
148 WHEN OTHERS THEN
149 /*SRW.MESSAGE(999
150 ,'FND FLEXSQL(MSTK) >X')*/NULL;
151 RAISE;
152 END;
153 SQL_STMT_NUM := '6: ';
154 CST_INVENTORY_PUB.CALCULATE_INVENTORYVALUE(P_API_VERSION => 1.0
155 ,P_INIT_MSG_LIST => CST_UTILITY_PUB.GET_TRUE
156 ,P_ORGANIZATION_ID => P_ORG_ID
157 ,P_ONHAND_VALUE => 1
158 ,P_INTRANSIT_VALUE => 1
159 ,P_RECEIVING_VALUE => 0
160 ,P_VALUATION_DATE => TO_DATE(P_AS_OF_DATE
161 ,'YYYY/MM/DD HH24:MI:SS')
162 ,P_COST_TYPE_ID => P_COST_TYPE_ID
163 ,P_ITEM_FROM => P_ITEM_FROM
164 ,P_ITEM_TO => P_ITEM_TO
165 ,P_CATEGORY_SET_ID => P_CATEGORY_SET
166 ,P_CATEGORY_FROM => P_CAT_FROM
167 ,P_CATEGORY_TO => P_CAT_TO
168 ,P_COST_GROUP_FROM => NULL
169 ,P_COST_GROUP_TO => NULL
170 ,P_SUBINVENTORY_FROM => P_SUBINV_FROM
171 ,P_SUBINVENTORY_TO => P_SUBINV_TO
172 ,P_QTY_BY_REVISION => P_ITEM_REVISION
173 ,P_ZERO_COST_ONLY => P_ZERO_COST
174 ,P_ZERO_QTY => P_ZERO_QTY
175 ,P_EXPENSE_ITEM => P_EXP_ITEM
176 ,P_EXPENSE_SUB => L_EXP_SUB
177 ,P_UNVALUED_TXNS => P_UNCOSTED_TXN
178 ,P_RECEIPT => 1
179 ,P_SHIPMENT => 1
180 ,X_RETURN_STATUS => L_RETURN_STATUS
181 ,X_MSG_COUNT => L_MSG_COUNT
182 ,X_MSG_DATA => L_MSG_DATA);
183 IF L_RETURN_STATUS <> CST_UTILITY_PUB.GET_RET_STS_SUCCESS THEN
184 RAISE L_CST_INV_VAL;
185 END IF;
186 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
187 ,P_COUNT => L_MSG_COUNT
188 ,P_DATA => L_MSG_DATA);
189 IF (L_MSG_COUNT > 0) THEN
190 FOR i IN 1 .. L_MSG_COUNT LOOP
191 L_MSG_DATA := FND_MSG_PUB.GET(I
192 ,CST_UTILITY_PUB.GET_FALSE);
193 FND_FILE.PUT_LINE(CST_UTILITY_PUB.GET_LOG
194 ,I || '-' || L_MSG_DATA);
195 END LOOP;
196 END IF;
197 SELECT
198 TO_CHAR(TO_DATE(P_AS_OF_DATE
199 ,'YYYY/MM/DD HH24:MI:SS')
200 ,'DD-MON-YYYY HH24:MI:SS')
201 INTO L_AS_OF_DATE
202 FROM
203 DUAL;
204 P_AS_OF_DATE1 := L_AS_OF_DATE;
205 /*SRW.MESSAGE(0
206 ,'BOM_CSTRINVR_XMLP_PKG << ' || TO_CHAR(SYSDATE
207 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
208 EXCEPTION
209 WHEN OTHERS THEN
210 /*SRW.MESSAGE(999
211 ,SQL_STMT_NUM || SQLERRM)*/NULL;
212 /*SRW.MESSAGE(999
213 ,'BOM_CSTRINVR_XMLP_PKG >X ' || TO_CHAR(SYSDATE
214 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
215 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
216 ,P_COUNT => L_MSG_COUNT
217 ,P_DATA => L_MSG_DATA);
218 IF (L_MSG_COUNT > 0) THEN
219 FOR i IN 1 .. L_MSG_COUNT LOOP
220 L_MSG_DATA := FND_MSG_PUB.GET(I
221 ,CST_UTILITY_PUB.GET_FALSE);
222 /*SRW.MESSAGE(999
223 ,I || ': ' || L_MSG_DATA)*/NULL;
224 END LOOP;
225 END IF;
226 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
227 END;
228 RETURN (TRUE);
229 END BEFOREREPORT;
230 FUNCTION IC_ROWCOUNTFORMULA(IC_QUANTITY IN NUMBER) RETURN NUMBER IS
231 BEGIN
232 IF P_ZERO_QTY = 1 AND P_NEG_QTY = 1 AND IC_QUANTITY <= 0 THEN
233 RETURN (1);
234 ELSIF P_ZERO_QTY = 2 AND P_NEG_QTY = 1 AND IC_QUANTITY < 0 THEN
235 RETURN (1);
236 ELSIF P_ZERO_QTY = 2 AND P_NEG_QTY = 2 AND IC_QUANTITY <> 0 THEN
237 RETURN (1);
238 ELSIF P_ZERO_QTY = 1 AND P_NEG_QTY = 2 THEN
239 RETURN (1);
240 ELSE
241 RETURN (0);
242 END IF;
243 RETURN NULL;
244 END IC_ROWCOUNTFORMULA;
245 FUNCTION IC_TOTAL1FORMULA(IC_QUANTITY IN NUMBER
246 ,IC_TOTAL IN NUMBER) RETURN NUMBER IS
247 BEGIN
248 IF P_NEG_QTY = 1 AND IC_QUANTITY > 0 THEN
249 RETURN (0);
250 ELSE
251 RETURN (IC_TOTAL);
252 END IF;
253 RETURN NULL;
254 END IC_TOTAL1FORMULA;
255 FUNCTION SI_TOTAL1FORMULA(SI_QUANTITY IN NUMBER
256 ,SI_TOTAL IN NUMBER) RETURN NUMBER IS
257 BEGIN
258 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
259 RETURN (0);
260 ELSE
261 RETURN (SI_TOTAL);
262 END IF;
263 RETURN NULL;
264 END SI_TOTAL1FORMULA;
265 FUNCTION SI_ROWCOUNTFORMULA(SI_QUANTITY IN NUMBER) RETURN NUMBER IS
266 BEGIN
267 IF P_ZERO_QTY = 1 AND P_NEG_QTY = 1 AND SI_QUANTITY <= 0 THEN
268 RETURN (1);
269 ELSIF P_ZERO_QTY = 1 AND P_NEG_QTY = 2 THEN
270 RETURN (1);
271 ELSIF P_ZERO_QTY = 2 AND P_NEG_QTY = 1 AND SI_QUANTITY < 0 THEN
272 RETURN (1);
273 ELSIF P_ZERO_QTY = 2 AND P_NEG_QTY = 2 AND SI_QUANTITY <> 0 THEN
274 RETURN (1);
275 ELSE
276 RETURN (0);
277 END IF;
278 RETURN NULL;
279 END SI_ROWCOUNTFORMULA;
280 FUNCTION SI_MATL1FORMULA(SI_QUANTITY IN NUMBER
281 ,SI_MATL IN NUMBER) RETURN NUMBER IS
282 BEGIN
283 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
284 RETURN (0);
285 ELSE
286 RETURN (SI_MATL);
287 END IF;
288 RETURN NULL;
289 END SI_MATL1FORMULA;
290 FUNCTION SI_MOVH1FORMULA(SI_QUANTITY IN NUMBER
291 ,SI_MOVH IN NUMBER) RETURN NUMBER IS
292 BEGIN
293 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
294 RETURN (0);
295 ELSE
296 RETURN (SI_MOVH);
297 END IF;
298 RETURN NULL;
299 END SI_MOVH1FORMULA;
300 FUNCTION SI_RES1FORMULA(SI_QUANTITY IN NUMBER
301 ,SI_RES IN NUMBER) RETURN NUMBER IS
302 BEGIN
303 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
304 RETURN (0);
305 ELSE
306 RETURN (SI_RES);
307 END IF;
308 RETURN NULL;
309 END SI_RES1FORMULA;
310 FUNCTION SI_OSP1FORMULA(SI_QUANTITY IN NUMBER
311 ,SI_OSP IN NUMBER) RETURN NUMBER IS
312 BEGIN
313 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
314 RETURN (0);
315 ELSE
316 RETURN (SI_OSP);
317 END IF;
318 RETURN NULL;
319 END SI_OSP1FORMULA;
320 FUNCTION SI_OVHD1FORMULA(SI_QUANTITY IN NUMBER
321 ,SI_OVHD IN NUMBER) RETURN NUMBER IS
322 BEGIN
323 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
324 RETURN (0);
325 ELSE
326 RETURN (SI_OVHD);
327 END IF;
328 RETURN NULL;
329 END SI_OVHD1FORMULA;
330 FUNCTION IC_ORDERFORMULA(IC_CATEGORY IN VARCHAR2
331 ,IC_CATEGORY_SEGMENT IN VARCHAR2
332 ,IC_CAT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
333 BEGIN
334 /*SRW.REFERENCE(IC_CATEGORY)*/NULL;
335 /*SRW.REFERENCE(IC_CATEGORY_SEGMENT)*/NULL;
336 /*SRW.REFERENCE(IC_CAT_PSEG)*/NULL;
337 IF P_SORT_OPTION = 2 THEN
338 RETURN (IC_CAT_PSEG);
339 ELSE
340 RETURN ('Item Sort');
341 END IF;
342 RETURN NULL;
343 END IC_ORDERFORMULA;
344 FUNCTION IC_ITEM_PSEGFORMULA(IC_ITEM_NUMBER IN VARCHAR2
345 ,IC_ITEM_SEGMENT IN VARCHAR2
346 ,IC_ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
347 BEGIN
348 /*SRW.REFERENCE(IC_ITEM_NUMBER)*/NULL;
349 /*SRW.REFERENCE(IC_ITEM_SEGMENT)*/NULL;
350 RETURN (IC_ITEM_PSEG);
351 END IC_ITEM_PSEGFORMULA;
352 FUNCTION IC_CAT_PSEGFORMULA(IC_CATEGORY IN VARCHAR2
353 ,IC_CATEGORY_SEGMENT IN VARCHAR2
354 ,IC_CAT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
355 BEGIN
356 /*SRW.REFERENCE(IC_CATEGORY)*/NULL;
357 /*SRW.REFERENCE(IC_CATEGORY_SEGMENT)*/NULL;
358 RETURN (IC_CAT_PSEG);
359 END IC_CAT_PSEGFORMULA;
360 FUNCTION SI_ITEM_PSEGFORMULA(SI_ITEM_NUMBER IN VARCHAR2
361 ,SI_ITEM_SEGMENT IN VARCHAR2
362 ,SI_ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
363 BEGIN
364 /*SRW.REFERENCE(SI_ITEM_NUMBER)*/NULL;
365 /*SRW.REFERENCE(SI_ITEM_SEGMENT)*/NULL;
366 RETURN (SI_ITEM_PSEG);
367 END SI_ITEM_PSEGFORMULA;
368 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
369 BEGIN
370 RETURN (TRUE);
371 END BEFOREPFORM;
372 FUNCTION IC_QUANTITY1FORMULA(IC_QUANTITY IN NUMBER) RETURN NUMBER IS
373 BEGIN
374 IF P_NEG_QTY = 1 AND IC_QUANTITY > 0 THEN
375 RETURN (0);
376 ELSE
377 RETURN (IC_QUANTITY);
378 END IF;
379 RETURN NULL;
380 END IC_QUANTITY1FORMULA;
381 FUNCTION SI_SI_TOTALFORMULA(SI_SI_MATL IN NUMBER
382 ,SI_SI_MOVH IN NUMBER
383 ,SI_SI_RES IN NUMBER
384 ,SI_SI_OSP IN NUMBER
385 ,SI_SI_OVHD IN NUMBER) RETURN NUMBER IS
386 SI_SI_TOTAL NUMBER;
387 BEGIN
388 SI_SI_TOTAL := (SI_SI_MATL + SI_SI_MOVH + SI_SI_RES + SI_SI_OSP + SI_SI_OVHD);
389 SI_SI_TOTAL := STANDARD.ROUND(SI_SI_TOTAL / ROUND_UNIT) * ROUND_UNIT;
390 RETURN SI_SI_TOTAL;
391 END SI_SI_TOTALFORMULA;
392 FUNCTION SI_REP_TOTALFORMULA(SI_REP_MATL IN NUMBER
393 ,SI_REP_MOVH IN NUMBER
394 ,SI_REP_RES IN NUMBER
395 ,SI_REP_OSP IN NUMBER
396 ,SI_REP_OVHD IN NUMBER) RETURN NUMBER IS
397 SI_REP_TOTAL NUMBER;
398 BEGIN
399 SI_REP_TOTAL := (SI_REP_MATL + SI_REP_MOVH + SI_REP_RES + SI_REP_OSP + SI_REP_OVHD);
400 SI_REP_TOTAL := ROUND(SI_REP_TOTAL / ROUND_UNIT) * ROUND_UNIT;
401 RETURN SI_REP_TOTAL;
402 END SI_REP_TOTALFORMULA;
403 FUNCTION IC_ITEM_TOTAL_RFORMULA(IC_ITEM_TOTAL IN NUMBER) RETURN NUMBER IS
404 IC_ITEM_TOTAL_R NUMBER;
405 BEGIN
406 IC_ITEM_TOTAL_R := STANDARD.ROUND(IC_ITEM_TOTAL / ROUND_UNIT
407 ,0) * ROUND_UNIT;
408 RETURN IC_ITEM_TOTAL_R;
409 END IC_ITEM_TOTAL_RFORMULA;
410 FUNCTION IC_TOTAL_RFORMULA(IC_TOTAL IN NUMBER) RETURN NUMBER IS
411 IC_TOTAL_R NUMBER;
412 BEGIN
413 IC_TOTAL_R := STANDARD.ROUND(IC_TOTAL / ROUND_UNIT ,0) * ROUND_UNIT;
414 RETURN IC_TOTAL_R;
415 END IC_TOTAL_RFORMULA;
416 FUNCTION IC_REP_TOTAL_RFORMULA(IC_REP_TOTAL IN NUMBER) RETURN NUMBER IS
417 IC_REP_TOTAL_R NUMBER;
418 BEGIN
419 IC_REP_TOTAL_R := ROUND(IC_REP_TOTAL / ROUND_UNIT) * ROUND_UNIT;
420 RETURN IC_REP_TOTAL_R;
421 END IC_REP_TOTAL_RFORMULA;
422 FUNCTION IC_CAT_TOTAL_RFORMULA(IC_CAT_TOTAL IN NUMBER) RETURN NUMBER IS
423 IC_CAT_TOTAL_R NUMBER;
424 BEGIN
425 IC_CAT_TOTAL_R := ROUND(IC_CAT_TOTAL / ROUND_UNIT) * ROUND_UNIT;
426 RETURN IC_CAT_TOTAL_R;
427 END IC_CAT_TOTAL_RFORMULA;
428 FUNCTION SI_TOTAL_RFORMULA(SI_TOTAL IN NUMBER) RETURN NUMBER IS
429 SI_TOTAL_R NUMBER;
430 BEGIN
431 SI_TOTAL_R := ROUND(SI_TOTAL / ROUND_UNIT) * ROUND_UNIT;
432 RETURN SI_TOTAL_R;
433 END SI_TOTAL_RFORMULA;
434 FUNCTION SI_SI_MATL_RFORMULA(SI_SI_MATL IN NUMBER) RETURN NUMBER IS
435 SI_SI_MATL_R NUMBER;
436 BEGIN
437 SI_SI_MATL_R := STANDARD.ROUND(SI_SI_MATL / ROUND_UNIT) * ROUND_UNIT;
438 RETURN SI_SI_MATL_R;
439 END SI_SI_MATL_RFORMULA;
440 FUNCTION SI_SI_MOVH_RFORMULA(SI_SI_MOVH IN NUMBER) RETURN NUMBER IS
441 SI_SI_MOVH_R NUMBER;
442 BEGIN
443 SI_SI_MOVH_R := ROUND(SI_SI_MOVH / ROUND_UNIT) * ROUND_UNIT;
444 RETURN SI_SI_MOVH_R;
445 END SI_SI_MOVH_RFORMULA;
446 FUNCTION SI_SI_OVHD_RFORMULA(SI_SI_OVHD IN NUMBER) RETURN NUMBER IS
447 SI_SI_OVHD_R NUMBER;
448 BEGIN
449 SI_SI_OVHD_R := ROUND(SI_SI_OVHD / ROUND_UNIT) * ROUND_UNIT;
450 RETURN SI_SI_OVHD_R;
451 END SI_SI_OVHD_RFORMULA;
452 FUNCTION SI_SI_RES_RFORMULA(SI_SI_RES IN NUMBER) RETURN NUMBER IS
453 SI_SI_RES_R NUMBER;
454 BEGIN
455 SI_SI_RES_R := ROUND(SI_SI_RES / ROUND_UNIT) * ROUND_UNIT;
456 RETURN SI_SI_RES_R;
457 END SI_SI_RES_RFORMULA;
458 FUNCTION SI_SI_OSP_RFORMULA(SI_SI_OSP IN NUMBER) RETURN NUMBER IS
459 SI_SI_OSP_R NUMBER;
460 BEGIN
461 SI_SI_OSP_R := ROUND(SI_SI_OSP / ROUND_UNIT) * ROUND_UNIT;
462 RETURN SI_SI_OSP_R;
463 END SI_SI_OSP_RFORMULA;
464 FUNCTION P_REPORT_NAMEVALIDTRIGGER RETURN BOOLEAN IS
465 BEGIN
466 RETURN (TRUE);
467 END P_REPORT_NAMEVALIDTRIGGER;
468 FUNCTION AFTERPFORM RETURN BOOLEAN IS
469 BEGIN
470 IF (P_SUBINV_FROM IS NULL AND P_SUBINV_TO IS NULL) THEN
471 P_SUB_INV_SEC := '(1=1)';
472 ELSIF (P_SUBINV_FROM IS NOT NULL AND P_SUBINV_TO IS NULL) THEN
473 P_SUB_INV_SEC := 'SEC.SECONDARY_INVENTORY_NAME >= :P_SUBINV_FROM';
474 ELSIF (P_SUBINV_FROM IS NULL AND P_SUBINV_TO IS NOT NULL) THEN
475 P_SUB_INV_SEC := 'SEC.SECONDARY_INVENTORY_NAME <= :P_SUBINV_TO';
476 ELSE
477 P_SUB_INV_SEC := 'SEC.SECONDARY_INVENTORY_NAME BETWEEN
478 :P_SUBINV_FROM AND :P_SUBINV_TO';
479 END IF;
480 IF (P_ZERO_QTY = 1 AND P_SUBINV_FROM IS NULL AND P_SUBINV_TO IS NULL) THEN
481 P_SUBINV_WHERE := 'SEC.ORGANIZATION_ID (+) = CIQT.ORGANIZATION_ID
482 AND SEC.SECONDARY_INVENTORY_NAME(+) = CIQT.SUBINVENTORY_CODE';
483 ELSE
484 P_SUBINV_WHERE := 'SEC.ORGANIZATION_ID(+) = CIQT.ORGANIZATION_ID
485 AND SEC.SECONDARY_INVENTORY_NAME(+) = CIQT.SUBINVENTORY_CODE';
486 END IF;
487 RETURN (TRUE);
488 END AFTERPFORM;
489 FUNCTION IC_UNIT_COST1FORMULA(IC_ITEM_QTY IN NUMBER
490 ,IC_ITEM_TOTAL IN NUMBER
491 ,IC_UNIT_COST IN NUMBER) RETURN NUMBER IS
492 BEGIN
493 IF (IC_ITEM_QTY <> 0) THEN
494 RETURN ROUND((IC_ITEM_TOTAL / IC_ITEM_QTY)
495 ,P_EXT_PREC);
496 ELSE
497 RETURN (IC_UNIT_COST);
498 END IF;
499 END IC_UNIT_COST1FORMULA;
500 END BOM_CSTRINVR_XMLP_PKG;
501