1 PACKAGE BODY WMS_WMSCPTYR_XMLP_PKG AS
2 /* $Header: WMSCPTYRB.pls 120.2 2008/02/21 06:33:45 dwkrishn noship $ */
3 FUNCTION CF_LOC_FLEX_FIELDFORMULA(LOCATOR_ID IN NUMBER
4 ,ORGANIZATION_ID IN NUMBER) RETURN CHAR IS
5 BEGIN
6 RETURN INV_PROJECT.GET_LOCSEGS(LOCATOR_ID
7 ,ORGANIZATION_ID);
8 END CF_LOC_FLEX_FIELDFORMULA;
9
10 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
11 BEGIN
12 BEGIN
13
14 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
15 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
16 EXCEPTION
17 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
18 /*SRW.MESSAGE(1
19 ,'Srwinit failed in before report trigger')*/NULL;
20 END;
21 DECLARE
22 L_REPORT_NAME VARCHAR2(240);
23 BEGIN
24 SELECT
25 CP.USER_CONCURRENT_PROGRAM_NAME
26 INTO L_REPORT_NAME
27 FROM
28 FND_CONCURRENT_PROGRAMS_VL CP,
29 FND_CONCURRENT_REQUESTS CR
30 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
31 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
32 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
33 P_REPORT_NAME := L_REPORT_NAME;
34 P_REPORT_NAME := substr(P_REPORT_NAME,1,instr(P_REPORT_NAME,' (XML)'));
35 EXCEPTION
36 WHEN NO_DATA_FOUND THEN
37 P_REPORT_NAME := 'WMS Capacity Utilization Report';
38 END;
39 BEGIN
40 SELECT
41 REQUEST_DATE,
42 ACTUAL_START_DATE,
43 SAVE_OUTPUT_FLAG,
44 PRINTER
45 INTO P_REQUEST_DATE,P_ACTUAL_START_DATE,P_SAVE_OUTPUT_FLAG,P_PRINTER
46 FROM
47 FND_CONCURRENT_REQUESTS
48 WHERE REQUEST_ID = P_CONC_REQUEST_ID;
49 EXCEPTION
50 WHEN NO_DATA_FOUND THEN
51 NULL;
52 END;
53 BEGIN
54 SELECT
55 APPLICATION_NAME
56 INTO P_APPLICATION
57 FROM
58 FND_CONCURRENT_REQUESTS FCR,
59 FND_APPLICATION_VL FAV
60 WHERE FCR.REQUEST_ID = P_CONC_REQUEST_ID
61 AND FAV.APPLICATION_ID = FCR.PROGRAM_APPLICATION_ID;
62 EXCEPTION
63 WHEN NO_DATA_FOUND THEN
64 RETURN (TRUE);
65 END;
66 BEGIN
67 SELECT
68 RESPONSIBILITY_NAME
69 INTO P_RESPONSIBILITY
70 FROM
71 FND_CONCURRENT_REQUESTS FCR,
72 FND_RESPONSIBILITY_VL FRV
73 WHERE FCR.REQUEST_ID = P_CONC_REQUEST_ID
74 AND FRV.RESPONSIBILITY_ID = FCR.RESPONSIBILITY_ID;
75 EXCEPTION
76 WHEN NO_DATA_FOUND THEN
77 RETURN (TRUE);
78 END;
79 INITIALIZE_VALUES;
80 RETURN (TRUE);
81 END BEFOREREPORT;
82
83 FUNCTION AFTERPFORM RETURN BOOLEAN IS
84 L_MSG VARCHAR2(1000);
85 BEGIN
86 LP_NON_CONV_STOCK:=P_NON_CONV_STOCK;
87 LP_WHERE_CLAUSE := ' AND 1=1';
88 IF P_ORG_ID IS NOT NULL THEN
89 LP_WHERE_CLAUSE := LP_WHERE_CLAUSE || ' AND MIL.ORGANIZATION_ID = ' || P_ORG_ID;
90 END IF;
91 IF P_SUBINVENTORY IS NOT NULL THEN
92 LP_WHERE_CLAUSE := LP_WHERE_CLAUSE || ' AND MIL.SUBINVENTORY_CODE = ''' || P_SUBINVENTORY || '''';
93 END IF;
94 IF P_DETAIL_LEVEL = 2 THEN
95 IF P_UTIL_BY_WEIGHT IS NOT NULL AND P_WT_OPERATOR IS NOT NULL THEN
96 LP_WHERE_CLAUSE := LP_WHERE_CLAUSE || ' AND ((MIL.MAX_WEIGHT is null and MIL.CURRENT_WEIGHT <> 0) OR nvl(MIL.CURRENT_WEIGHT*100/MIL.MAX_WEIGHT,0) ' || P_WT_OPERATOR || ' ' || P_UTIL_BY_WEIGHT || ')';
97 ELSIF P_UTIL_BY_WEIGHT IS NOT NULL AND P_WT_OPERATOR IS NULL THEN
98 LP_WHERE_CLAUSE := LP_WHERE_CLAUSE || ' AND ((MIL.MAX_WEIGHT is null and MIL.current_weight <> 0) OR nvl(MIL.CURRENT_WEIGHT*100/MIL.MAX_WEIGHT,0) <' || P_UTIL_BY_WEIGHT || ')';
99 END IF;
100 IF P_UTIL_BY_VOLUME IS NOT NULL AND P_VOL_OPERATOR IS NOT NULL THEN
101 LP_WHERE_CLAUSE := LP_WHERE_CLAUSE || ' AND ((MIL.MAX_CUBIC_AREA is null and MIL.CURRENT_CUBIC_AREA <> 0) OR nvl(MIL.CURRENT_CUBIC_AREA*100/MIL.MAX_CUBIC_AREA,0) ' || P_VOL_OPERATOR || ' ' || P_UTIL_BY_VOLUME || ')';
102 ELSIF P_UTIL_BY_VOLUME IS NOT NULL AND P_VOL_OPERATOR IS NULL THEN
103 LP_WHERE_CLAUSE := LP_WHERE_CLAUSE || ' AND ((MIL.MAX_CUBIC_AREA is null AND MIL.CURRENT_CUBIC_AREA <> 0) OR nvl(MIL.CURRENT_CUBIC_AREA*100/MIL.MAX_CUBIC_AREA,0) < ' || P_UTIL_BY_VOLUME || ')';
104 END IF;
105 IF P_UTIL_BY_UNIT IS NOT NULL AND P_UNITS_OPERATOR IS NOT NULL THEN
106 LP_WHERE_CLAUSE := LP_WHERE_CLAUSE || ' AND ((MIL.LOCATION_MAXIMUM_UNITS is null and MIL.LOCATION_CURRENT_UNITS <> 0) OR nvl(MIL.LOCATION_CURRENT_UNITS*100/MIL.LOCATION_MAXIMUM_UNITS,0) ' || P_UNITS_OPERATOR || ' ' || P_UTIL_BY_UNIT || ')';
107 ELSIF P_UTIL_BY_UNIT IS NOT NULL AND P_UNITS_OPERATOR IS NULL THEN
108 LP_WHERE_CLAUSE := LP_WHERE_CLAUSE || ' AND ((MIL.LOCATION_MAXIMUM_UNITS is null AND MIL.LOCATION_CURRENT_UNITS <> 0) OR nvl(MIL.LOCATION_CURRENT_UNITS*100/MIL.LOCATION_MAXIMUM_UNITS,0) <' || P_UTIL_BY_UNIT || ')';
109 END IF;
110 END IF;
111 IF P_ORDER_BY IS NOT NULL THEN
112 IF P_ORDER_BY = 1 THEN
113 LP_ORDER_BY := LP_ORDER_BY || ' ORDER BY 2,decode(mil.physical_location_id,null,mil.inventory_location_id,mil.physical_location_id)';
114 ELSIF P_ORDER_BY = 2 THEN
115 LP_ORDER_BY := LP_ORDER_BY || ' ORDER BY 2,MIL.subinventory_code ';
116 ELSIF P_ORDER_BY = 3 THEN
117 LP_ORDER_BY := LP_ORDER_BY || ' ORDER BY 2, MILL.PICKING_ORDER';
118 END IF;
119 SELECT
120 MEANING
121 INTO P_ORDER_BY_MEANING
122 FROM
123 MFG_LOOKUPS
124 WHERE LOOKUP_TYPE = 'WMS_CAPACITY_DETAIL'
125 AND LOOKUP_CODE = P_ORDER_BY;
126 END IF;
127 IF P_ORG_ID IS NOT NULL THEN
128 SELECT
129 ORGANIZATION_CODE,
130 ORGANIZATION_NAME
131 INTO P_ORG_CODE,P_ORG_NAME
132 FROM
133 ORG_ORGANIZATION_DEFINITIONS
134 WHERE ORGANIZATION_ID = P_ORG_ID;
135 IF P_LOCATOR_ID IS NOT NULL THEN
136 P_LOCATOR_FLEX_ALL := INV_PROJECT.GET_LOCSEGS(P_LOCATOR_ID
137 ,P_ORG_ID);
138 END IF;
139 END IF;
140 /*SRW.MESSAGE(1
141 ,'Detail level' || P_DETAIL_LEVEL)*/NULL;
142 IF P_DETAIL_LEVEL IS NOT NULL THEN
143 SELECT
144 MEANING
145 INTO P_DETAIL_MEANING
146 FROM
147 MFG_LOOKUPS
148 WHERE LOOKUP_TYPE = 'WMS_CAPACITY_DETAIL'
149 AND LOOKUP_CODE = P_DETAIL_LEVEL;
150 END IF;
151 IF P_NON_CONV_STK = 1 THEN
152 P_MESSAGE := FND_MESSAGE.GET_STRING('WMS'
153 ,'WMS_UOM_CONV_FAIL');
154 LP_NON_CONV_STOCK := P_VALUE_YES;
155 ELSE
156 LP_NON_CONV_STOCK := P_VALUE_NO;
157 END IF;
158 RETURN (TRUE);
159 END AFTERPFORM;
160
161 FUNCTION AFTERREPORT RETURN BOOLEAN IS
162 BEGIN
163 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
164 RETURN (TRUE);
165 END AFTERREPORT;
166
167 FUNCTION CF_LOC_CAPACITYFORMULA(ORGANIZATION_ID IN NUMBER
168 ,LOCATOR_ID IN NUMBER
169 ,LOOSE_QTY IN NUMBER
170 ,PACKED_QTY IN NUMBER
171 ,TOTAL_QTY IN NUMBER
172 ,SUBINVENTORY_CODE1 IN VARCHAR2) RETURN NUMBER IS
173 X_RETURN_STATUS VARCHAR2(1);
174 X_MSG_COUNT NUMBER;
175 X_MSG_DATA VARCHAR2(1000);
176 X_LOCATION_MAXIMUM_UNITS NUMBER;
177 X_LOCATION_CURRENT_UNITS NUMBER;
178 X_LOCATION_SUGGESTED_UNITS NUMBER;
179 X_LOCATION_AVAILABLE_UNITS NUMBER;
180 X_LOCATION_WEIGHT_UOM_CODE VARCHAR2(3);
181 X_MAX_WEIGHT NUMBER;
182 X_CURRENT_WEIGHT NUMBER;
183 X_SUGGESTED_WEIGHT NUMBER;
184 X_AVAILABLE_WEIGHT NUMBER;
185 X_VOLUME_UOM_CODE VARCHAR2(3);
186 X_MAX_CUBIC_AREA NUMBER;
187 X_CURRENT_CUBIC_AREA NUMBER;
188 X_SUGGESTED_CUBIC_AREA NUMBER;
189 X_AVAILABLE_CUBIC_AREA NUMBER;
190 P_ORGANIZATION_ID NUMBER;
191 P_INVENTORY_LOCATION_ID NUMBER;
192 L_DISPLAY_LOCATOR BOOLEAN := TRUE;
193 BEGIN
194 INV_LOC_WMS_UTILS.GET_LOCATOR_CAPACITY(X_RETURN_STATUS
195 ,X_MSG_COUNT
196 ,X_MSG_DATA
197 ,X_LOCATION_MAXIMUM_UNITS
198 ,X_LOCATION_CURRENT_UNITS
199 ,X_LOCATION_SUGGESTED_UNITS
200 ,X_LOCATION_AVAILABLE_UNITS
201 ,X_LOCATION_WEIGHT_UOM_CODE
202 ,X_MAX_WEIGHT
203 ,X_CURRENT_WEIGHT
204 ,X_SUGGESTED_WEIGHT
205 ,X_AVAILABLE_WEIGHT
206 ,X_VOLUME_UOM_CODE
207 ,X_MAX_CUBIC_AREA
208 ,X_CURRENT_CUBIC_AREA
209 ,X_SUGGESTED_CUBIC_AREA
210 ,X_AVAILABLE_CUBIC_AREA
211 ,ORGANIZATION_ID
212 ,LOCATOR_ID);
213 CP_LOC_WT_PER := 0;
214 CP_LOC_VOL_PER := 0;
215 CP_LOC_UNITS_PER := 0;
216 IF X_CURRENT_WEIGHT IS NOT NULL THEN
217 CP_SUB_WT := INV_CONVERT.INV_UM_CONVERT(NULL
218 ,NULL
219 ,X_CURRENT_WEIGHT
220 ,X_LOCATION_WEIGHT_UOM_CODE
221 ,P_SUB_WT_UOM_CODE
222 ,NULL
223 ,NULL);
224 ELSE
225 CP_SUB_WT := NULL;
226 END IF;
227 IF X_CURRENT_CUBIC_AREA IS NOT NULL THEN
228 CP_SUB_VOL := INV_CONVERT.INV_UM_CONVERT(NULL
229 ,NULL
230 ,X_CURRENT_CUBIC_AREA
231 ,X_VOLUME_UOM_CODE
232 ,P_SUB_VOL_UOM_CODE
233 ,NULL
234 ,NULL);
235 ELSE
236 CP_SUB_VOL := NULL;
237 END IF;
238 IF X_MAX_WEIGHT IS NOT NULL THEN
239 CP_MAX_SUB_WT := INV_CONVERT.INV_UM_CONVERT(NULL
240 ,NULL
241 ,X_MAX_WEIGHT
242 ,X_LOCATION_WEIGHT_UOM_CODE
243 ,P_SUB_WT_UOM_CODE
244 ,NULL
245 ,NULL);
246 ELSE
247 CP_MAX_SUB_WT := NULL;
248 END IF;
249 IF X_MAX_CUBIC_AREA IS NOT NULL THEN
250 CP_MAX_SUB_VOL := INV_CONVERT.INV_UM_CONVERT(NULL
251 ,NULL
252 ,X_MAX_CUBIC_AREA
253 ,X_VOLUME_UOM_CODE
254 ,P_SUB_VOL_UOM_CODE
255 ,NULL
256 ,NULL);
257 ELSE
258 CP_MAX_SUB_VOL := NULL;
259 END IF;
260 IF CP_SUB_WT = -99999 THEN
261 CP_SUB_WT := NULL;
262 END IF;
263 IF CP_SUB_VOL = -99999 THEN
264 CP_SUB_VOL := NULL;
265 END IF;
266 IF CP_MAX_SUB_WT = -99999 THEN
267 L_DISPLAY_LOCATOR := FALSE;
268 CP_MAX_SUB_WT := NULL;
269 END IF;
270 IF CP_MAX_SUB_VOL = -99999 THEN
271 L_DISPLAY_LOCATOR := FALSE;
272 CP_MAX_SUB_VOL := NULL;
273 END IF;
277 CP_MAX_SUB_UNITS := NULL;
274 IF X_LOCATION_MAXIMUM_UNITS IS NOT NULL AND X_LOCATION_MAXIMUM_UNITS > 0 THEN
275 CP_MAX_SUB_UNITS := X_LOCATION_MAXIMUM_UNITS;
276 ELSE
278 CP_LOC_UNITS_PER := 1;
279 END IF;
280 IF X_LOCATION_CURRENT_UNITS IS NOT NULL AND X_LOCATION_CURRENT_UNITS >= 0 THEN
281 CP_UNITS := X_LOCATION_CURRENT_UNITS;
282 ELSE
283 CP_UNITS := NULL;
284 END IF;
285 IF CP_SUB_WT IS NULL AND CP_SUB_VOL IS NULL AND CP_UNITS IS NULL THEN
286 L_DISPLAY_LOCATOR := FALSE;
287 END IF;
288 IF L_DISPLAY_LOCATOR AND (P_LOCATOR_ID IS NULL OR P_LOCATOR_ID = LOCATOR_ID) THEN
289 IF CP_MAX_SUB_WT IS NOT NULL THEN
290 IF CP_MAX_SUB_WT = 0 THEN
291 CP_WT_PER := TO_CHAR(0);
292 ELSE
293 CP_WT_PER := TO_CHAR(100 * (ROUND(CP_SUB_WT / CP_MAX_SUB_WT
294 ,5)));
295 END IF;
296 ELSE
297 CP_WT_PER := P_VALUE_NA;
298 CP_LOC_WT_PER := 1;
299 END IF;
300 IF CP_MAX_SUB_VOL IS NOT NULL THEN
301 IF CP_MAX_SUB_VOL = 0 THEN
302 CP_VOL_PER := TO_CHAR(0);
303 ELSE
304 CP_VOL_PER := TO_CHAR(100 * (ROUND(CP_SUB_VOL / CP_MAX_SUB_VOL
305 ,5)));
306 END IF;
307 ELSE
308 CP_VOL_PER := P_VALUE_NA;
309 CP_LOC_VOL_PER := 1;
310 END IF;
311 IF CP_SUB_WT IS NOT NULL AND CP_SUB_WT > 0 THEN
312 CP_LOC_WEIGHT_C := CP_LOC_WEIGHT_C + 1;
313 END IF;
314 IF CP_SUB_VOL IS NOT NULL AND CP_SUB_VOL > 0 THEN
315 CP_LOC_VOLUME_C := CP_LOC_VOLUME_C + 1;
316 END IF;
317 IF LOOSE_QTY > 0 THEN
318 CP_LOC_LOOSE_QTY_C := CP_LOC_LOOSE_QTY_C + 1;
319 END IF;
320 IF PACKED_QTY > 0 THEN
321 CP_LOC_PACKED_QTY_C := CP_LOC_PACKED_QTY_C + 1;
322 END IF;
323 IF TOTAL_QTY > 0 THEN
324 CP_LOC_TOTAL_QTY_C := CP_LOC_TOTAL_QTY_C + 1;
325 END IF;
326 CP_DISPLAY_LOC := 1;
327 CP_LOC_LOOSE_QTY := LOOSE_QTY;
328 CP_LOC_PACKED_QTY := PACKED_QTY;
329 CP_LOC_TOTAL_QTY := TOTAL_QTY;
330 ELSE
331 IF (P_LOCATOR_ID IS NULL OR (P_LOCATOR_ID = LOCATOR_ID)) AND (P_SUBINVENTORY IS NULL OR (P_SUBINVENTORY = SUBINVENTORY_CODE1)) THEN
332 CP_DISPLAY_LOC := 0;
333 INSERT INTO MTL_LOC_CAPACITY_TEMP
334 (ORGANIZATION_ID
335 ,SUBINVENTORY_CODE
336 ,LOCATOR_ID
337 ,LOCATOR_WEIGHT_UOM_CODE
338 ,LOCATOR_VOLUME_UOM_CODE
339 ,LOCATOR_UNITS_UOM_CODE)
340 VALUES (ORGANIZATION_ID
341 ,SUBINVENTORY_CODE1
342 ,LOCATOR_ID
343 ,X_LOCATION_WEIGHT_UOM_CODE
344 ,X_VOLUME_UOM_CODE
345 ,NULL);
346 END IF;
347 CP_LOC_LOOSE_QTY := 0;
348 CP_LOC_PACKED_QTY := 0;
349 CP_LOC_TOTAL_QTY := 0;
350 END IF;
351 RETURN (1);
352 END CF_LOC_CAPACITYFORMULA;
353
354 FUNCTION CHECK_WEIGHT_PERCENT(CS_WEIGHT IN NUMBER) RETURN BOOLEAN IS
355 L_WEIGHT_PER NUMBER := P_UTIL_BY_WEIGHT;
356 L_WT_OP VARCHAR2(1) := P_WT_OPERATOR;
357 BEGIN
358 IF L_WEIGHT_PER IS NULL OR L_WT_OP IS NULL THEN
359 RETURN TRUE;
360 END IF;
361 IF CP_WEIGHT_PER = P_VALUE_NA THEN
362 IF CS_WEIGHT <> 0 THEN
363 RETURN (TRUE);
364 END IF;
365 ELSE
366 IF (L_WT_OP = '<') THEN
367 RETURN (TO_NUMBER(CP_WEIGHT_PER) < L_WEIGHT_PER);
368 ELSIF (L_WT_OP = '>') THEN
369 RETURN (TO_NUMBER(CP_WEIGHT_PER) > L_WEIGHT_PER);
370 ELSE
371 RETURN (TO_NUMBER(CP_WEIGHT_PER) = L_WEIGHT_PER);
372 END IF;
373 END IF;
374 RETURN (FALSE);
375 END CHECK_WEIGHT_PERCENT;
376
377 FUNCTION CHECK_VOL_PERCENT(CS_VOLUME IN NUMBER) RETURN BOOLEAN IS
378 L_VOL_PER NUMBER := P_UTIL_BY_VOLUME;
379 L_VOL_OP VARCHAR2(1) := P_VOL_OPERATOR;
380 BEGIN
381 IF L_VOL_PER IS NULL OR L_VOL_OP IS NULL THEN
382 RETURN TRUE;
383 END IF;
384 IF CP_VOLUME_PER = P_VALUE_NA THEN
385 IF CS_VOLUME <> 0 THEN
386 RETURN (TRUE);
387 END IF;
388 ELSE
389 IF (L_VOL_OP = '<') THEN
390 RETURN (TO_NUMBER(CP_VOLUME_PER) < L_VOL_PER);
391 ELSIF (L_VOL_OP = '>') THEN
392 RETURN (TO_NUMBER(CP_VOLUME_PER) > L_VOL_PER);
393 ELSE
394 RETURN (TO_NUMBER(CP_VOLUME_PER) = L_VOL_PER);
395 END IF;
396 END IF;
397 RETURN (FALSE);
398 END CHECK_VOL_PERCENT;
399
400 PROCEDURE INITIALIZE_VALUES IS
401 BEGIN
402 CP_LOOSE_QTY_C := 0;
403 CP_PACKED_QTY_C := 0;
404 CP_TOTAL_QTY_C := 0;
405 CP_WEIGHT_C := 0;
406 CP_VOLUME_C := 0;
407 CP_LOC_LOOSE_QTY_C := 0;
408 CP_LOC_PACKED_QTY_C := 0;
409 CP_LOC_TOTAL_QTY_C := 0;
410 CP_LOC_WEIGHT_C := 0;
411 CP_LOC_VOLUME_C := 0;
412 END INITIALIZE_VALUES;
413
414 FUNCTION CHECK_UNITS_PERCENT(CS_UNITS IN NUMBER) RETURN BOOLEAN IS
415 L_UNITS_PER NUMBER := P_UTIL_BY_UNIT;
416 L_UNITS_OP VARCHAR2(1) := P_UNITS_OPERATOR;
417 BEGIN
418 IF L_UNITS_PER IS NULL OR L_UNITS_OP IS NULL THEN
419 RETURN (TRUE);
420 END IF;
421 IF CP_UNITS_PER IS NULL THEN
422 IF CS_UNITS <> 0 THEN
423 RETURN (TRUE);
424 END IF;
425 ELSE
426 IF (L_UNITS_OP = '<') THEN
427 RETURN (CP_UNITS_PER < L_UNITS_PER);
428 ELSIF (L_UNITS_OP = '>') THEN
429 RETURN (CP_UNITS_PER > L_UNITS_PER);
430 ELSE
434 RETURN (FALSE);
431 RETURN (CP_UNITS_PER = L_UNITS_PER);
432 END IF;
433 END IF;
435 END CHECK_UNITS_PERCENT;
436
437 FUNCTION CF_1FORMULA(LOC IN NUMBER
438 ,ORG_ID IN NUMBER) RETURN CHAR IS
439 BEGIN
440 RETURN INV_PROJECT.GET_LOCSEGS(LOC
441 ,ORG_ID);
442 END CF_1FORMULA;
443
444 FUNCTION CF_CALC_COUNTERSFORMULA(CS_WEIGHT IN NUMBER
445 ,CS_VOLUME IN NUMBER
446 ,CS_UNITS IN NUMBER
447 ,CS_MAX_SUB_WT IN NUMBER
448 ,CS_SUB_WT_PER IN NUMBER
449 ,CS_MAX_SUB_VOL IN NUMBER
450 ,CS_SUB_VOL_PER IN NUMBER
451 ,CS_SUB_UNITS_PER IN NUMBER
452 ,CS_MAX_SUB_UNITS IN NUMBER
453 ,CS_LOOSE_QTY IN NUMBER
454 ,CS_PACKED_QTY IN NUMBER
455 ,CS_TOTAL_QTY IN NUMBER
456 ,CS_NUM_LOCS IN NUMBER) RETURN NUMBER IS
457 L_DISPLAY_SUB BOOLEAN := TRUE;
458 L_LEVEL NUMBER := 0;
459 BEGIN
460 L_LEVEL := 1;
461 IF CS_WEIGHT IS NULL AND CS_VOLUME IS NULL AND CS_UNITS IS NULL THEN
462 L_DISPLAY_SUB := FALSE;
463 END IF;
464 IF (L_DISPLAY_SUB) THEN
465 IF CS_MAX_SUB_WT IS NOT NULL AND CS_SUB_WT_PER = 0 THEN
466 IF CS_MAX_SUB_WT = 0 THEN
467 CP_WEIGHT_PER := TO_CHAR(0);
468 ELSE
469 CP_WEIGHT_PER := TO_CHAR(100 * ROUND(CS_WEIGHT / CS_MAX_SUB_WT
470 ,5));
471 END IF;
472 ELSE
473 CP_WEIGHT_PER := P_VALUE_NA;
474 END IF;
475 IF CS_MAX_SUB_VOL IS NOT NULL AND CS_SUB_VOL_PER = 0 THEN
476 IF CS_MAX_SUB_VOL = 0 THEN
477 CP_VOLUME_PER := TO_CHAR(0);
478 ELSE
479 CP_VOLUME_PER := TO_CHAR(100 * ROUND((CS_VOLUME / CS_MAX_SUB_VOL)
480 ,5));
481 END IF;
482 ELSE
483 CP_VOLUME_PER := P_VALUE_NA;
484 END IF;
485 IF CS_SUB_UNITS_PER = 0 THEN
486 IF CS_MAX_SUB_UNITS = 0 THEN
487 CP_UNITS_PER := 0;
488 ELSE
489 CP_UNITS_PER := 100 * ROUND((CS_UNITS / CS_MAX_SUB_UNITS)
490 ,5);
491 END IF;
492 ELSE
493 CP_UNITS_PER := NULL;
494 END IF;
495 END IF;
496 IF (L_DISPLAY_SUB AND P_DETAIL_LEVEL = 2) OR (L_DISPLAY_SUB AND P_DETAIL_LEVEL = 1 AND CHECK_WEIGHT_PERCENT(CS_WEIGHT) AND CHECK_VOL_PERCENT(CS_VOLUME) AND CHECK_UNITS_PERCENT(CS_UNITS)) THEN
497 IF CS_WEIGHT > 0 THEN
498 CP_WEIGHT_C := CP_WEIGHT_C + 1;
499 END IF;
500 IF CS_VOLUME > 0 THEN
501 CP_VOLUME_C := CP_VOLUME_C + 1;
502 END IF;
503 IF CS_LOOSE_QTY > 0 THEN
504 CP_LOOSE_QTY_C := CP_LOOSE_QTY_C + 1;
505 END IF;
506 IF CS_PACKED_QTY > 0 THEN
507 CP_PACKED_QTY_C := CP_PACKED_QTY_C + 1;
508 END IF;
509 IF CS_TOTAL_QTY > 0 THEN
510 CP_TOTAL_QTY_C := CP_TOTAL_QTY_C + 1;
511 END IF;
512 END IF;
513 IF (P_DETAIL_LEVEL = 1) THEN
514 IF (CHECK_WEIGHT_PERCENT(CS_WEIGHT) AND CHECK_VOL_PERCENT(CS_VOLUME) AND CHECK_UNITS_PERCENT(CS_UNITS) AND L_DISPLAY_SUB) THEN
515 CP_DISPLAY_SUB := 1;
516 CP_COUNT := 1;
517 CP_SUB_LOOSE_QTY := CS_LOOSE_QTY;
518 CP_SUB_PACKED_QTY := CS_PACKED_QTY;
519 CP_SUB_TOTAL_QTY := CS_TOTAL_QTY;
520 CP_SUB_WEIGHT := CS_WEIGHT;
521 CP_SUB_VOLUME := CS_VOLUME;
522 ELSE
523 CP_SUB_LOOSE_QTY := 0;
524 CP_SUB_PACKED_QTY := 0;
525 CP_SUB_TOTAL_QTY := 0;
526 CP_SUB_WEIGHT := 0;
527 CP_SUB_VOLUME := 0;
528 CP_DISPLAY_SUB := 0;
529 END IF;
530 ELSIF (P_DETAIL_LEVEL = 2) THEN
531 IF (L_DISPLAY_SUB AND CS_NUM_LOCS > 0) THEN
532 CP_DISPLAY_SUB := 1;
533 CP_COUNT := 1;
534 CP_SUB_LOOSE_QTY := CS_LOOSE_QTY;
535 CP_SUB_PACKED_QTY := CS_PACKED_QTY;
536 CP_SUB_TOTAL_QTY := CS_TOTAL_QTY;
537 CP_SUB_WEIGHT := CS_WEIGHT;
538 CP_SUB_VOLUME := CS_VOLUME;
539 ELSE
540 CP_DISPLAY_SUB := 0;
541 CP_SUB_LOOSE_QTY := 0;
542 CP_SUB_PACKED_QTY := 0;
543 CP_SUB_TOTAL_QTY := 0;
544 CP_SUB_WEIGHT := 0;
545 CP_SUB_VOLUME := 0;
546 END IF;
547 END IF;
548 RETURN (1);
549 EXCEPTION
550 WHEN OTHERS THEN
551 NULL;
552 END CF_CALC_COUNTERSFORMULA;
553
554 FUNCTION CF_CALC_AVGSFORMULA(CS_TOTAL_LOOSE_QTY IN NUMBER
555 ,CS_TOTAL_PACKED_QTY IN NUMBER
556 ,CS_TOTAL_TOTAL_QTY IN NUMBER
557 ,CS_TOTAL_WEIGHT IN NUMBER
558 ,CS_TOTAL_VOLUME IN NUMBER) RETURN NUMBER IS
559 BEGIN
560 IF P_DETAIL_LEVEL = 1 THEN
561 IF CP_LOOSE_QTY_C = 0 THEN
562 CP_AVG_LOOSE_QTY := CS_TOTAL_LOOSE_QTY;
563 ELSE
564 CP_AVG_LOOSE_QTY := ROUND(CS_TOTAL_LOOSE_QTY / CP_LOOSE_QTY_C
565 ,5);
566 END IF;
567 IF CP_PACKED_QTY_C = 0 THEN
568 CP_AVG_PACKED_QTY := CS_TOTAL_PACKED_QTY;
569 ELSE
570 CP_AVG_PACKED_QTY := ROUND(CS_TOTAL_PACKED_QTY / CP_PACKED_QTY_C
571 ,5);
572 END IF;
573 IF CP_TOTAL_QTY_C = 0 THEN
574 CP_AVG_TOTAL_QTY := CS_TOTAL_TOTAL_QTY;
578 END IF;
575 ELSE
576 CP_AVG_TOTAL_QTY := ROUND(CS_TOTAL_TOTAL_QTY / CP_TOTAL_QTY_C
577 ,5);
579 IF CP_WEIGHT_C = 0 THEN
580 CP_AVG_WEIGHT := CS_TOTAL_WEIGHT;
581 ELSE
582 CP_AVG_WEIGHT := ROUND(CS_TOTAL_WEIGHT / CP_WEIGHT_C
583 ,5);
584 END IF;
585 IF CP_VOLUME_C = 0 THEN
586 CP_AVG_VOLUME := CS_TOTAL_VOLUME;
587 ELSE
588 CP_AVG_VOLUME := ROUND(CS_TOTAL_VOLUME / CP_VOLUME_C
589 ,5);
590 END IF;
591 ELSE
592 IF CP_LOC_LOOSE_QTY_C = 0 THEN
593 CP_AVG_LOOSE_QTY := CS_TOTAL_LOOSE_QTY;
594 ELSE
595 CP_AVG_LOOSE_QTY := ROUND(CS_TOTAL_LOOSE_QTY / CP_LOC_LOOSE_QTY_C
596 ,5);
597 END IF;
598 IF CP_LOC_PACKED_QTY_C = 0 THEN
599 CP_AVG_PACKED_QTY := CS_TOTAL_PACKED_QTY;
600 ELSE
601 CP_AVG_PACKED_QTY := ROUND(CS_TOTAL_PACKED_QTY / CP_LOC_PACKED_QTY_C
602 ,5);
603 END IF;
604 IF CP_LOC_TOTAL_QTY_C = 0 THEN
605 CP_AVG_TOTAL_QTY := CS_TOTAL_TOTAL_QTY;
606 ELSE
607 CP_AVG_TOTAL_QTY := ROUND(CS_TOTAL_TOTAL_QTY / CP_LOC_TOTAL_QTY_C
608 ,5);
609 END IF;
610 IF CP_LOC_WEIGHT_C = 0 THEN
611 CP_AVG_WEIGHT := CS_TOTAL_WEIGHT;
612 ELSE
613 CP_AVG_WEIGHT := ROUND(CS_TOTAL_WEIGHT / CP_LOC_WEIGHT_C
614 ,5);
615 END IF;
616 IF CP_LOC_VOLUME_C = 0 THEN
617 CP_AVG_VOLUME := CS_TOTAL_VOLUME;
618 ELSE
619 CP_AVG_VOLUME := ROUND(CS_TOTAL_VOLUME / CP_LOC_VOLUME_C
620 ,5);
621 END IF;
622 END IF;
623 RETURN (1);
624 END CF_CALC_AVGSFORMULA;
625
626 FUNCTION CP_UNITS_PER_P RETURN NUMBER IS
627 BEGIN
628 RETURN CP_UNITS_PER;
629 END CP_UNITS_PER_P;
630
631 FUNCTION CP_WEIGHT_PER_P RETURN VARCHAR2 IS
632 BEGIN
633 RETURN CP_WEIGHT_PER;
634 END CP_WEIGHT_PER_P;
635
636 FUNCTION CP_VOLUME_PER_P RETURN VARCHAR2 IS
637 BEGIN
638 RETURN CP_VOLUME_PER;
639 END CP_VOLUME_PER_P;
640
641 FUNCTION CP_COUNT_P RETURN NUMBER IS
642 BEGIN
643 RETURN CP_COUNT;
644 END CP_COUNT_P;
645
646 FUNCTION CP_DISPLAY_SUB_P RETURN NUMBER IS
647 BEGIN
648 RETURN CP_DISPLAY_SUB;
649 END CP_DISPLAY_SUB_P;
650
651 FUNCTION CP_SUB_LOOSE_QTY_P RETURN NUMBER IS
652 BEGIN
653 RETURN CP_SUB_LOOSE_QTY;
654 END CP_SUB_LOOSE_QTY_P;
655
656 FUNCTION CP_SUB_PACKED_QTY_P RETURN NUMBER IS
657 BEGIN
658 RETURN CP_SUB_PACKED_QTY;
659 END CP_SUB_PACKED_QTY_P;
660
661 FUNCTION CP_SUB_TOTAL_QTY_P RETURN NUMBER IS
662 BEGIN
663 RETURN CP_SUB_TOTAL_QTY;
664 END CP_SUB_TOTAL_QTY_P;
665
666 FUNCTION CP_SUB_WEIGHT_P RETURN NUMBER IS
667 BEGIN
668 RETURN CP_SUB_WEIGHT;
669 END CP_SUB_WEIGHT_P;
670
671 FUNCTION CP_SUB_VOLUME_P RETURN NUMBER IS
672 BEGIN
673 RETURN CP_SUB_VOLUME;
674 END CP_SUB_VOLUME_P;
675
676 FUNCTION CP_SUB_WT_P RETURN NUMBER IS
677 BEGIN
678 RETURN CP_SUB_WT;
679 END CP_SUB_WT_P;
680
681 FUNCTION CP_SUB_VOL_P RETURN NUMBER IS
682 BEGIN
683 RETURN CP_SUB_VOL;
684 END CP_SUB_VOL_P;
685
686 FUNCTION CP_MAX_SUB_WT_P RETURN NUMBER IS
687 BEGIN
688 RETURN CP_MAX_SUB_WT;
689 END CP_MAX_SUB_WT_P;
690
691 FUNCTION CP_MAX_SUB_VOL_P RETURN NUMBER IS
692 BEGIN
693 RETURN CP_MAX_SUB_VOL;
694 END CP_MAX_SUB_VOL_P;
695
696 FUNCTION CP_WT_PER_P RETURN VARCHAR2 IS
697 BEGIN
698 RETURN CP_WT_PER;
699 END CP_WT_PER_P;
700
701 FUNCTION CP_VOL_PER_P RETURN VARCHAR2 IS
702 BEGIN
703 RETURN CP_VOL_PER;
704 END CP_VOL_PER_P;
705
706 FUNCTION CP_DISPLAY_LOC_P RETURN NUMBER IS
707 BEGIN
708 RETURN CP_DISPLAY_LOC;
709 END CP_DISPLAY_LOC_P;
710
711 FUNCTION CP_LOC_LOOSE_QTY_P RETURN NUMBER IS
712 BEGIN
713 RETURN CP_LOC_LOOSE_QTY;
714 END CP_LOC_LOOSE_QTY_P;
715
716 FUNCTION CP_LOC_PACKED_QTY_P RETURN NUMBER IS
717 BEGIN
718 RETURN CP_LOC_PACKED_QTY;
719 END CP_LOC_PACKED_QTY_P;
720
721 FUNCTION CP_UNITS_P RETURN NUMBER IS
722 BEGIN
723 RETURN CP_UNITS;
724 END CP_UNITS_P;
725
726 FUNCTION CP_MAX_SUB_UNITS_P RETURN NUMBER IS
727 BEGIN
728 RETURN CP_MAX_SUB_UNITS;
729 END CP_MAX_SUB_UNITS_P;
730
731 FUNCTION CP_LOC_TOTAL_QTY_P RETURN NUMBER IS
732 BEGIN
733 RETURN CP_LOC_TOTAL_QTY;
734 END CP_LOC_TOTAL_QTY_P;
735
736 FUNCTION CP_LOC_WT_PER_P RETURN NUMBER IS
737 BEGIN
738 RETURN CP_LOC_WT_PER;
739 END CP_LOC_WT_PER_P;
740
741 FUNCTION CP_LOC_VOL_PER_P RETURN NUMBER IS
742 BEGIN
743 RETURN CP_LOC_VOL_PER;
744 END CP_LOC_VOL_PER_P;
745
746 FUNCTION CP_LOC_UNITS_PER_P RETURN NUMBER IS
747 BEGIN
748 RETURN CP_LOC_UNITS_PER;
749 END CP_LOC_UNITS_PER_P;
750
751 FUNCTION CP_AVG_LOOSE_QTY_P RETURN NUMBER IS
752 BEGIN
753 RETURN CP_AVG_LOOSE_QTY;
754 END CP_AVG_LOOSE_QTY_P;
755
756 FUNCTION CP_AVG_PACKED_QTY_P RETURN NUMBER IS
757 BEGIN
758 RETURN CP_AVG_PACKED_QTY;
759 END CP_AVG_PACKED_QTY_P;
760
761 FUNCTION CP_AVG_TOTAL_QTY_P RETURN NUMBER IS
762 BEGIN
763 RETURN CP_AVG_TOTAL_QTY;
764 END CP_AVG_TOTAL_QTY_P;
765
766 FUNCTION CP_AVG_WEIGHT_P RETURN NUMBER IS
767 BEGIN
768 RETURN CP_AVG_WEIGHT;
769 END CP_AVG_WEIGHT_P;
770
771 FUNCTION CP_AVG_VOLUME_P RETURN NUMBER IS
772 BEGIN
773 RETURN CP_AVG_VOLUME;
774 END CP_AVG_VOLUME_P;
775
776 FUNCTION CP_WEIGHT_C_P RETURN NUMBER IS
777 BEGIN
778 RETURN CP_WEIGHT_C;
779 END CP_WEIGHT_C_P;
780
781 FUNCTION CP_LOC_WEIGHT_C_P RETURN NUMBER IS
782 BEGIN
783 RETURN CP_LOC_WEIGHT_C;
784 END CP_LOC_WEIGHT_C_P;
785
786 FUNCTION CP_VOLUME_C_P RETURN NUMBER IS
787 BEGIN
788 RETURN CP_VOLUME_C;
789 END CP_VOLUME_C_P;
790
791 FUNCTION CP_LOC_LOOSE_QTY_C_P RETURN NUMBER IS
792 BEGIN
793 RETURN CP_LOC_LOOSE_QTY_C;
794 END CP_LOC_LOOSE_QTY_C_P;
795
796 FUNCTION CP_PACKED_QTY_C_P RETURN NUMBER IS
797 BEGIN
798 RETURN CP_PACKED_QTY_C;
799 END CP_PACKED_QTY_C_P;
800
801 FUNCTION CP_LOC_PACKED_QTY_C_P RETURN NUMBER IS
802 BEGIN
803 RETURN CP_LOC_PACKED_QTY_C;
804 END CP_LOC_PACKED_QTY_C_P;
805
806 FUNCTION CP_LOC_VOLUME_C_P RETURN NUMBER IS
807 BEGIN
808 RETURN CP_LOC_VOLUME_C;
809 END CP_LOC_VOLUME_C_P;
810
811 FUNCTION CP_TOTAL_QTY_C_P RETURN NUMBER IS
812 BEGIN
813 RETURN CP_TOTAL_QTY_C;
814 END CP_TOTAL_QTY_C_P;
815
816 FUNCTION CP_LOOSE_QTY_C_P RETURN NUMBER IS
817 BEGIN
818 RETURN CP_LOOSE_QTY_C;
819 END CP_LOOSE_QTY_C_P;
820
821 FUNCTION CP_LOC_TOTAL_QTY_C_P RETURN NUMBER IS
822 BEGIN
823 RETURN CP_LOC_TOTAL_QTY_C;
824 END CP_LOC_TOTAL_QTY_C_P;
825
826 END WMS_WMSCPTYR_XMLP_PKG;
827
[Home] [Help]