4 ,S_COUNTS_COMPLETED IN NUMBER) RETURN NUMBER IS
1 PACKAGE BODY INV_INVARCTA_XMLP_PKG AS
2 /* $Header: INVARCTAB.pls 120.1.12020000.2 2012/07/03 10:02:18 pdong ship $ */
3 FUNCTION S_OTHER_COUNTSFORMULA(S_TOTAL_COUNTS IN NUMBER
5 BEGIN
6 RETURN (S_TOTAL_COUNTS - S_COUNTS_COMPLETED);
7 END S_OTHER_COUNTSFORMULA;
8
9 FUNCTION S_GROSS_ADJFORMULA(S_POS_ADJ IN NUMBER
10 ,S_NEG_ADJ IN NUMBER) RETURN NUMBER IS
11 BEGIN
12 /*SRW.REFERENCE(S_POS_ADJ)*/NULL;
13 /*SRW.REFERENCE(S_NEG_ADJ)*/NULL;
14 RETURN (S_POS_ADJ + ABS(S_NEG_ADJ));
15 END S_GROSS_ADJFORMULA;
16
17 FUNCTION S_NET_ADJFORMULA(S_POS_ADJ IN NUMBER
18 ,S_NEG_ADJ IN NUMBER) RETURN NUMBER IS
19 BEGIN
20 /*SRW.REFERENCE(S_POS_ADJ)*/NULL;
21 /*SRW.REFERENCE(S_NEG_ADJ)*/NULL;
22 RETURN (S_POS_ADJ - ABS(S_NEG_ADJ));
23 END S_NET_ADJFORMULA;
24
25 FUNCTION S_GROSS_ACCURACYFORMULA(S_GROSS_ADJ IN NUMBER
26 ,S_INVENTORY_VALUE IN NUMBER) RETURN NUMBER IS
27 BEGIN
28 BEGIN
29 /*SRW.REFERENCE(S_GROSS_ADJ)*/NULL;
30 /*SRW.REFERENCE(S_INVENTORY_VALUE)*/NULL;
31 IF ((S_GROSS_ADJ = 0) AND (S_INVENTORY_VALUE = 0)) THEN
32 RETURN (100);
33 ELSE
34 IF (S_INVENTORY_VALUE = 0) THEN
35 RETURN (0.00);
36 ELSE
37 IF (ABS(S_GROSS_ADJ) > ABS(S_INVENTORY_VALUE)) THEN
38 RETURN (0.00);
39 ELSE
43 END IF;
40 RETURN (100 - ((ABS(S_GROSS_ADJ) / ABS(S_INVENTORY_VALUE)) * 100));
41 END IF;
42 END IF;
44 END;
45 RETURN NULL;
46 END S_GROSS_ACCURACYFORMULA;
47
48 FUNCTION S_NET_ACCURACYFORMULA(S_NET_ADJ IN NUMBER
49 ,S_INVENTORY_VALUE IN NUMBER) RETURN NUMBER IS
50 BEGIN
51 BEGIN
52 /*SRW.REFERENCE(S_NET_ADJ)*/NULL;
53 /*SRW.REFERENCE(S_INVENTORY_VALUE)*/NULL;
54 END;
55 BEGIN
56 IF ((S_INVENTORY_VALUE = 0) AND (S_NET_ADJ = 0)) THEN
57 RETURN (100.00);
58 ELSE
59 IF (S_INVENTORY_VALUE = 0) THEN
60 RETURN (0.00);
61 ELSE
62 IF (ABS(S_NET_ADJ) > ABS(S_INVENTORY_VALUE)) THEN
63 RETURN (0.00);
64 ELSE
65 RETURN (ROUND(100 - ((ABS(S_NET_ADJ) / ABS(S_INVENTORY_VALUE)) * 100)
66 ,2));
67 END IF;
68 END IF;
69 END IF;
70 END;
71 RETURN NULL;
72 END S_NET_ACCURACYFORMULA;
73
74 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
75 BEGIN
76 BEGIN
77 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
78 LP_TO_DATE := to_char(P_TO_DATE, 'DD-MON-YYYY');
79 LP_FROM_DATE := to_char(P_FROM_DATE, 'DD-MON-YYYY');
80
81 select first_value(cur.precision) over() into pstd_precision
82 from org_organization_definitions org,
83 gl_sets_of_books gsob,
84 fnd_currencies cur
85 where org.organization_id = p_org_id
86 and org.set_of_books_id = gsob.set_of_books_id
87 and cur.currency_code = gsob.currency_code;
88
89 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
90 EXCEPTION
91 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
92 /*SRW.MESSAGE(1
93 ,'Before Report: Init')*/NULL;
94 END;
95 DECLARE
96 P_ORG_ID_CHAR VARCHAR2(100) := P_ORG_ID;
97 BEGIN
98 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
99 EXCEPTION
100 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
101 /*SRW.MESSAGE(020
102 ,'Failed in before report trigger, setting org profile ')*/NULL;
103 RAISE;
104 END;
105 BEGIN
106 NULL;
107 EXCEPTION
108 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
109 /*SRW.MESSAGE(1
110 ,'Before Report: LocatorFlex')*/NULL;
111 END;
112 BEGIN
113 NULL;
114 EXCEPTION
115 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
116 /*SRW.MESSAGE(1
117 ,'Before Report: ItemFlex')*/NULL;
118 END;
119 DECLARE
120 M_RETURN_STATUS VARCHAR2(1);
121 M_MSG_COUNT NUMBER;
122 M_MSG_DATA VARCHAR2(2000);
123 BEGIN
124 IF (WMS_INSTALL.CHECK_INSTALL(X_RETURN_STATUS => M_RETURN_STATUS
125 ,X_MSG_COUNT => M_MSG_COUNT
126 ,X_MSG_DATA => M_MSG_DATA
127 ,P_ORGANIZATION_ID => P_ORG_ID)) THEN
128 P_WMS_INSTALLED := 'TRUE';
129 END IF;
130 END;
131 RETURN (TRUE);
132 END BEFOREREPORT;
133
134 FUNCTION AFTERREPORT RETURN BOOLEAN IS
135 BEGIN
136 BEGIN
137 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
138 END;
139 RETURN (TRUE);
140 END AFTERREPORT;
141
142 FUNCTION C_FCURRENCYCODEFORMULA(C_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
143 BEGIN
144 DECLARE
145 TEMP_C VARCHAR2(20);
146 BEGIN
147 TEMP_C := '(' || C_CURRENCY_CODE || ')';
148 RETURN (TEMP_C);
149 END;
150 RETURN NULL;
151 END C_FCURRENCYCODEFORMULA;
152
153 FUNCTION ADJUSTMENT_AMOUNTFORMULA(ENTRY_STATUS_CODE IN NUMBER
154 ,COUNT_TYPE_CODE IN NUMBER
155 ,ADJUSTMENT_QUANTITY_CURRENT IN NUMBER
156 ,CONV_RATE_CURRENT IN NUMBER
157 ,ITEM_UNIT_COST IN NUMBER
158 ,S_STD_PRECISION IN VARCHAR2) RETURN NUMBER IS
159 BEGIN
160 BEGIN
161 IF ((ENTRY_STATUS_CODE = 5 OR P_APPROVED = 2) AND (COUNT_TYPE_CODE <> 4)) THEN
162 RETURN (ROUND(ADJUSTMENT_QUANTITY_CURRENT * CONV_RATE_CURRENT * ITEM_UNIT_COST
163 ,S_STD_PRECISION));
164 ELSE
165 RETURN (0);
166 END IF;
167 END;
168 RETURN NULL;
169 END ADJUSTMENT_AMOUNTFORMULA;
170
171 FUNCTION POS_ADJFORMULA(ADJUSTMENT_AMOUNT IN NUMBER) RETURN NUMBER IS
172 BEGIN
173 BEGIN
174 IF ADJUSTMENT_AMOUNT > 0 THEN
175 RETURN (ADJUSTMENT_AMOUNT);
176 ELSE
177 RETURN (0);
178 END IF;
179 END;
180 RETURN NULL;
181 END POS_ADJFORMULA;
182
183 FUNCTION NEG_ADJFORMULA(ADJUSTMENT_AMOUNT IN NUMBER
184 ,NEG_ADJUSTMENT_AMOUNT IN NUMBER) RETURN NUMBER IS
185 BEGIN
186 BEGIN
187 IF ADJUSTMENT_AMOUNT < 0 THEN
188 RETURN (ABS(ADJUSTMENT_AMOUNT) + NVL(NEG_ADJUSTMENT_AMOUNT
189 ,0));
190 ELSE
191 RETURN (0);
192 END IF;
193 END;
194 RETURN NULL;
195 END NEG_ADJFORMULA;
196
197 FUNCTION SF_NETACCURACYFORMULA(SR_NETADJ IN NUMBER
198 ,SR_INVENTORYVALUE IN NUMBER) RETURN NUMBER IS
202 /*SRW.REFERENCE(SR_INVENTORYVALUE)*/NULL;
199 BEGIN
200 BEGIN
201 /*SRW.REFERENCE(SR_NETADJ)*/NULL;
203 END;
204 BEGIN
205 IF ((SR_INVENTORYVALUE = 0) AND (SR_NETADJ = 0)) THEN
206 RETURN (100.00);
207 ELSE
208 IF (SR_INVENTORYVALUE = 0) THEN
209 RETURN (0.00);
210 ELSE
211 IF (ABS(SR_NETADJ) > ABS(SR_INVENTORYVALUE)) THEN
212 RETURN (0.00);
213 ELSE
214 RETURN (ROUND(100 - ((ABS(SR_NETADJ) / ABS(SR_INVENTORYVALUE)) * 100)
215 ,2));
216 END IF;
217 END IF;
218 END IF;
219 END;
220 RETURN NULL;
221 END SF_NETACCURACYFORMULA;
222
223 FUNCTION SF_GROSSACCURACYFORMULA(SR_GROSSADJ IN NUMBER
224 ,SR_INVENTORYVALUE IN NUMBER) RETURN NUMBER IS
225 BEGIN
226 BEGIN
227 /*SRW.REFERENCE(SR_GROSSADJ)*/NULL;
228 /*SRW.REFERENCE(SR_INVENTORYVALUE)*/NULL;
229 IF ((SR_GROSSADJ = 0) AND (SR_INVENTORYVALUE = 0)) THEN
230 RETURN (100);
231 ELSE
232 IF (SR_INVENTORYVALUE = 0) THEN
233 RETURN (0.00);
234 ELSE
235 IF (ABS(SR_GROSSADJ) > ABS(SR_INVENTORYVALUE)) THEN
236 RETURN (0.00);
237 ELSE
238 RETURN (100 - ((ABS(SR_GROSSADJ) / ABS(SR_INVENTORYVALUE)) * 100));
239 END IF;
240 END IF;
241 END IF;
242 END;
243 RETURN NULL;
244 END SF_GROSSACCURACYFORMULA;
245
246 FUNCTION AFTERPFORM RETURN BOOLEAN IS
247 BEGIN
248 RETURN (TRUE);
249 END AFTERPFORM;
250
251 FUNCTION C_APPROVEDFIELDFORMULA(ENTRY_STATUS_CODE IN NUMBER
252 ,COMPLETED_FLAG IN VARCHAR2
253 ,NON_COMPLETED_FLAG IN VARCHAR2) RETURN VARCHAR2 IS
254 BEGIN
255 BEGIN
256 IF ENTRY_STATUS_CODE = 5 THEN
257 RETURN (COMPLETED_FLAG);
258 ELSE
259 RETURN (NON_COMPLETED_FLAG);
260 END IF;
261 END;
262 RETURN NULL;
263 END C_APPROVEDFIELDFORMULA;
264
265 FUNCTION ADJUSTMENT_AMOUNT_PRIORFORMULA(ENTRY_STATUS_CODE IN NUMBER
266 ,COUNT_TYPE_CODE IN NUMBER
267 ,ADJUSTMENT_QUANTITY_PRIOR IN NUMBER
268 ,CONV_RATE_PRIOR IN NUMBER
269 ,ITEM_UNIT_COST IN NUMBER
270 ,S_STD_PRECISION IN VARCHAR2) RETURN NUMBER IS
271 BEGIN
272 BEGIN
273 IF ((ENTRY_STATUS_CODE = 5 OR P_APPROVED = 2) AND (COUNT_TYPE_CODE <> 4)) THEN
274 RETURN (ROUND(ADJUSTMENT_QUANTITY_PRIOR * CONV_RATE_PRIOR * ITEM_UNIT_COST
275 ,S_STD_PRECISION));
276 ELSE
277 RETURN (0);
278 END IF;
279 END;
280 RETURN NULL;
281 END ADJUSTMENT_AMOUNT_PRIORFORMULA;
282
283 FUNCTION ADJUSTMENT_AMOUNT_FIRSTFORMULA(ENTRY_STATUS_CODE IN NUMBER
284 ,COUNT_TYPE_CODE IN NUMBER
285 ,ADJUSTMENT_QUANTITY_FIRST IN NUMBER
286 ,CONV_RATE_FIRST IN NUMBER
287 ,ITEM_UNIT_COST IN NUMBER
288 ,S_STD_PRECISION IN VARCHAR2) RETURN NUMBER IS
289 BEGIN
290 BEGIN
291 IF ((ENTRY_STATUS_CODE = 5 OR P_APPROVED = 2) AND (COUNT_TYPE_CODE <> 4)) THEN
292 RETURN (ROUND(ADJUSTMENT_QUANTITY_FIRST * CONV_RATE_FIRST * ITEM_UNIT_COST
293 ,S_STD_PRECISION));
294 ELSE
295 RETURN (0);
296 END IF;
297 END;
298 RETURN NULL;
299 END ADJUSTMENT_AMOUNT_FIRSTFORMULA;
300
301 FUNCTION CONV_RATE_CURRENTFORMULA(COUNT_UOM_CURRENT IN VARCHAR2
302 ,INVENTORY_ITEM_ID1 IN NUMBER
303 ,UOM IN VARCHAR2) RETURN NUMBER IS
304 BEGIN
305 DECLARE
306 CUR_RATE NUMBER;
307 BEGIN
308 IF (COUNT_UOM_CURRENT IS NULL) THEN
309 RETURN (1);
310 ELSE
311 SELECT
312 CONVERSION_RATE
313 INTO CUR_RATE
314 FROM
315 MTL_UOM_CONVERSIONS_VIEW
316 WHERE INVENTORY_ITEM_ID = INVENTORY_ITEM_ID1
317 AND ORGANIZATION_ID = TO_NUMBER(P_ORG_ID)
318 AND PRIMARY_UOM_CODE = UOM
319 AND UOM_CODE = COUNT_UOM_CURRENT;
320 RETURN (CUR_RATE);
321 END IF;
322 END;
323 RETURN NULL;
324 END CONV_RATE_CURRENTFORMULA;
325
326 FUNCTION CONV_RATE_PRIORFORMULA(COUNT_UOM_PRIOR IN VARCHAR2
327 ,INVENTORY_ITEM_ID1 IN NUMBER
328 ,UOM IN VARCHAR2) RETURN NUMBER IS
329 BEGIN
330 DECLARE
331 PRIOR_RATE NUMBER;
332 BEGIN
333 IF (COUNT_UOM_PRIOR IS NULL) THEN
334 RETURN (1);
335 ELSE
336 SELECT
337 CONVERSION_RATE
338 INTO PRIOR_RATE
339 FROM
340 MTL_UOM_CONVERSIONS_VIEW
341 WHERE INVENTORY_ITEM_ID = INVENTORY_ITEM_ID1
342 AND ORGANIZATION_ID = TO_NUMBER(P_ORG_ID)
343 AND PRIMARY_UOM_CODE = UOM
344 AND UOM_CODE = COUNT_UOM_PRIOR;
345 RETURN (PRIOR_RATE);
346 END IF;
347 END;
348 RETURN NULL;
352 ,INVENTORY_ITEM_ID1 IN NUMBER
349 END CONV_RATE_PRIORFORMULA;
350
351 FUNCTION CONV_RATE_FIRSTFORMULA(COUNT_UOM_FIRST IN VARCHAR2
353 ,UOM IN VARCHAR2) RETURN NUMBER IS
354 BEGIN
355 DECLARE
356 FIRST_RATE NUMBER;
357 BEGIN
358 IF (COUNT_UOM_FIRST IS NULL) THEN
359 RETURN (1);
360 ELSE
361 SELECT
362 CONVERSION_RATE
363 INTO FIRST_RATE
364 FROM
365 MTL_UOM_CONVERSIONS_VIEW
366 WHERE INVENTORY_ITEM_ID = INVENTORY_ITEM_ID1
367 AND ORGANIZATION_ID = TO_NUMBER(P_ORG_ID)
368 AND PRIMARY_UOM_CODE = UOM
369 AND UOM_CODE = COUNT_UOM_FIRST;
370 RETURN (FIRST_RATE);
371 END IF;
372 END;
373 RETURN NULL;
374 END CONV_RATE_FIRSTFORMULA;
375
376 FUNCTION ITEM_INV_VALUE_CURRENTFORMULA(ENTRY_STATUS_CODE IN NUMBER
377 ,COUNT_TYPE_CODE IN NUMBER
378 ,SYSTEM_QUANTITY_CURRENT IN NUMBER
379 ,CONV_RATE_CURRENT IN NUMBER
380 ,ITEM_UNIT_COST IN NUMBER
381 ,S_STD_PRECISION IN VARCHAR2) RETURN NUMBER IS
382 BEGIN
383 BEGIN
384 IF (ENTRY_STATUS_CODE = 5) AND (COUNT_TYPE_CODE <> 4) THEN
385 RETURN (ROUND(NVL(SYSTEM_QUANTITY_CURRENT
386 ,0) * CONV_RATE_CURRENT * NVL(ITEM_UNIT_COST
387 ,0)
388 ,S_STD_PRECISION));
389 ELSE
390 RETURN (0);
391 END IF;
392 END;
393 RETURN NULL;
394 END ITEM_INV_VALUE_CURRENTFORMULA;
395
396 FUNCTION P_LOCATOR_FLEXVALIDTRIGGER RETURN BOOLEAN IS
397 BEGIN
398 RETURN (TRUE);
399 END P_LOCATOR_FLEXVALIDTRIGGER;
400
401 FUNCTION CF_OUTERMOST_LPNFORMULA(OUTERMOST_LPN_ID IN NUMBER) RETURN CHAR IS
402 X_OUTERMOST_LPN VARCHAR2(30) := NULL;
403 --bug14252379,added local variable
404 L_OUTERMOST_LPN_ID NUMBER;
405 BEGIN
406 IF (P_WMS_INSTALLED = 'TRUE') THEN
407 IF (OUTERMOST_LPN_ID IS NOT NULL) THEN
408 L_OUTERMOST_LPN_ID := OUTERMOST_LPN_ID;
409 BEGIN
410 SELECT
411 LICENSE_PLATE_NUMBER
412 INTO X_OUTERMOST_LPN
413 FROM
414 WMS_LICENSE_PLATE_NUMBERS
415 WHERE LPN_ID = L_OUTERMOST_LPN_ID;--bug14252379,parameter name same with the column name causing the ambiguous reference
416 EXCEPTION
417 WHEN NO_DATA_FOUND THEN
418 X_OUTERMOST_LPN := 'ERROR';
419 END;
420 END IF;
421 END IF;
422 RETURN (X_OUTERMOST_LPN);
423 END CF_OUTERMOST_LPNFORMULA;
424
425 FUNCTION CF_PARENT_LPNFORMULA(PARENT_LPN_ID IN NUMBER) RETURN CHAR IS
426 X_PARENT_LPN VARCHAR2(30) := NULL;
427 --bug14252379,added local variable
428 L_PARENT_LPN_ID NUMBER;
429 BEGIN
430 IF (P_WMS_INSTALLED = 'TRUE') THEN
431 IF (PARENT_LPN_ID IS NOT NULL) THEN
432 L_PARENT_LPN_ID := PARENT_LPN_ID;
433 BEGIN
434 SELECT
435 LICENSE_PLATE_NUMBER
436 INTO X_PARENT_LPN
437 FROM
438 WMS_LICENSE_PLATE_NUMBERS
439 WHERE LPN_ID = L_PARENT_LPN_ID;--bug14252379,parameter name same with the column name causing the ambiguous reference
440 EXCEPTION
441 WHEN NO_DATA_FOUND THEN
442 X_PARENT_LPN := 'ERROR';
443 END;
444 END IF;
445 END IF;
446 RETURN (X_PARENT_LPN);
447 END CF_PARENT_LPNFORMULA;
448
449 FUNCTION CF_COST_GROUPFORMULA(COST_GROUP_ID IN NUMBER) RETURN CHAR IS
450 M_COST_GROUP VARCHAR2(10) := NULL;
451 --bug14252379,added local variable
452 L_COST_GROUP_ID NUMBER;
453 BEGIN
454 IF ((P_WMS_INSTALLED = 'TRUE') AND (COST_GROUP_ID IS NOT NULL)) THEN
455 L_COST_GROUP_ID := COST_GROUP_ID;
456 BEGIN
457 SELECT
458 COST_GROUP
459 INTO M_COST_GROUP
460 FROM
461 CST_COST_GROUPS
462 WHERE COST_GROUP_ID = L_COST_GROUP_ID;--bug14252379,parameter name same with the column name causing the ambiguous reference
463 EXCEPTION
464 WHEN NO_DATA_FOUND THEN
465 M_COST_GROUP := 'ERROR';
466 END;
467 END IF;
468 RETURN (M_COST_GROUP);
469 END CF_COST_GROUPFORMULA;
470
471 END INV_INVARCTA_XMLP_PKG;
472
[Home] [Help]