1 PACKAGE BODY INV_INVARPTS_XMLP_PKG AS
2 /* $Header: INVARPTSB.pls 120.1 2007/12/25 10:11:36 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
7 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
8 EXCEPTION
9 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
10 /*SRW.MESSAGE(1
11 ,'Failed in before report trigger:SRWINIT')*/NULL;
12 RAISE;
13 END;
14 RETURN (TRUE);
15 END BEFOREREPORT;
16
17 FUNCTION AFTERREPORT RETURN BOOLEAN IS
18 BEGIN
19 BEGIN
20 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
21 EXCEPTION
22 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
23 /*SRW.MESSAGE(1
24 ,'SRWEXIT failed')*/NULL;
25 END;
26 RETURN (TRUE);
27 END AFTERREPORT;
28
29 FUNCTION C_CURRENCY_CODEFORMULA(R_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
30 BEGIN
31 RETURN ('(' || R_CURRENCY_CODE || ')');
32 END C_CURRENCY_CODEFORMULA;
33
34 FUNCTION C_PHYS_INV_NAMEFORMULA RETURN VARCHAR2 IS
35 BEGIN
36 DECLARE
37 NAME VARCHAR2(30);
38 ORG NUMBER;
39 PI_ID NUMBER;
40 BEGIN
41 ORG := P_ORG_ID;
42 PI_ID := P_PHYS_INV_ID;
43 SELECT
44 PHYSICAL_INVENTORY_NAME
45 INTO NAME
46 FROM
47 MTL_PHYSICAL_INVENTORIES
48 WHERE ORGANIZATION_ID = ORG
49 AND PHYSICAL_INVENTORY_ID = PI_ID;
50 RETURN (NAME);
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN
53 RETURN (NULL);
54 END;
55 RETURN NULL;
56 END C_PHYS_INV_NAMEFORMULA;
57
58 FUNCTION C_ADJ_PCTFORMULA(ADJMNT_VALUE IN NUMBER
59 ,COUNT_VALUE IN NUMBER) RETURN NUMBER IS
60 BEGIN
61 BEGIN
62 RETURN (ROUND(ADJMNT_VALUE * 100 / COUNT_VALUE
63 ,2));
64 EXCEPTION
65 WHEN OTHERS THEN
66 RETURN (NULL);
67 END;
68 RETURN NULL;
69 END C_ADJ_PCTFORMULA;
70
71 FUNCTION C_VAR_PCTFORMULA(ABSOLUTE_VARIANCE IN NUMBER
72 ,COUNT_VALUE IN NUMBER) RETURN NUMBER IS
73 BEGIN
74 BEGIN
75 RETURN (ROUND((ABSOLUTE_VARIANCE * 100 / COUNT_VALUE)
76 ,2));
77 EXCEPTION
78 WHEN OTHERS THEN
79 RETURN (NULL);
80 END;
81 RETURN NULL;
82 END C_VAR_PCTFORMULA;
83
84 FUNCTION C_ADJ_PCT_SUMFORMULA(C_ADJ_VAL_SUM IN NUMBER
85 ,C_CNT_VAL_SUM IN NUMBER) RETURN NUMBER IS
86 BEGIN
87 BEGIN
88 RETURN (ROUND(C_ADJ_VAL_SUM * 100 / C_CNT_VAL_SUM
89 ,2));
90 EXCEPTION
91 WHEN OTHERS THEN
92 RETURN (NULL);
93 END;
94 RETURN NULL;
95 END C_ADJ_PCT_SUMFORMULA;
96
97 FUNCTION C_VAR_PCT_SUMFORMULA(C_ABS_VAR_SUM IN NUMBER
98 ,C_CNT_VAL_SUM IN NUMBER) RETURN NUMBER IS
99 BEGIN
100 BEGIN
101 RETURN (ROUND(C_ABS_VAR_SUM * 100 / C_CNT_VAL_SUM
102 ,2));
103 EXCEPTION
104 WHEN OTHERS THEN
105 RETURN (NULL);
106 END;
107 RETURN NULL;
108 END C_VAR_PCT_SUMFORMULA;
109
110 FUNCTION C_WHERE_OPTIONFORMULA RETURN VARCHAR2 IS
111 BEGIN
112 BEGIN
113 IF P_REPORT_OPTION = 1 THEN
114 RETURN (' ');
115 ELSE
116 RETURN ('and mpi.physical_inventory_id =
117 ' || TO_CHAR(P_PHYS_INV_ID));
118 END IF;
119 END;
120 RETURN NULL;
121 END C_WHERE_OPTIONFORMULA;
122
123 FUNCTION C_NUM_TAGSFORMULA(SUBINV IN VARCHAR2) RETURN NUMBER IS
124 BEGIN
125 DECLARE
126 MY_NUM_TAGS NUMBER;
127 MY_ORG_ID NUMBER;
128 MY_PHYSICAL_INV NUMBER;
129 BEGIN
130 MY_ORG_ID := P_ORG_ID;
131 MY_PHYSICAL_INV := P_PHYS_INV_ID;
132 SELECT
133 count(*)
134 INTO MY_NUM_TAGS
135 FROM
136 MTL_PHYSICAL_INVENTORY_TAGS
137 WHERE ORGANIZATION_ID = MY_ORG_ID
138 AND PHYSICAL_INVENTORY_ID = MY_PHYSICAL_INV
139 AND SUBINVENTORY = SUBINV
140 AND VOID_FLAG = 2;
141 RETURN (MY_NUM_TAGS);
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 RETURN (0);
145 END;
146 RETURN NULL;
147 END C_NUM_TAGSFORMULA;
148
149 END INV_INVARPTS_XMLP_PKG;
150