1 PACKAGE BODY INV_INVARCLI_XMLP_PKG AS
2 /* $Header: INVARCLIB.pls 120.2.12020000.2 2012/07/09 09:06:49 ptkumar ship $ */
3
4 -- For bug 14037978, modified the parameter by adding "p_" as prefix and modified the same in the definition
5 FUNCTION C_FORMATTEDCURRENCYCODEFORMULA(P_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
6 BEGIN
7 DECLARE
8 TEMP_C VARCHAR2(20);
9 BEGIN
10 TEMP_C := '(' || P_CURRENCY_CODE || ')';
11 RETURN (TEMP_C);
12 END;
13 RETURN NULL;
14 END C_FORMATTEDCURRENCYCODEFORMULA;
15
16 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
17 BEGIN
18 BEGIN
19 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
20 LP_FROMDATE :=P_FROMDATE ;
21 LP_TODATE:= P_TODATE;
22 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
23 EXCEPTION
24 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
25 /*SRW.MESSAGE(1
26 ,'Before Report: Init')*/NULL;
27 END;
28 DECLARE
29 P_ORG_ID_CHAR VARCHAR2(100) := TO_CHAR(P_ORG_ID);
30 BEGIN
31 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
32 EXCEPTION
33 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
34 /*SRW.MESSAGE(020
35 ,'Failed in before report trigger, setting org profile ')*/NULL;
36 RAISE;
37 END;
38 BEGIN
39 NULL;
40 EXCEPTION
41 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
42 /*SRW.MESSAGE(1
43 ,'Before Report: LocatorFlex')*/NULL;
44 END;
45 BEGIN
46 NULL;
47 EXCEPTION
48 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
49 /*SRW.MESSAGE(1
50 ,'Before Report: ItemFlex')*/NULL;
51 END;
52 DECLARE
53 M_RETURN_STATUS VARCHAR2(1);
54 M_MSG_COUNT NUMBER;
55 M_MSG_DATA VARCHAR2(2000);
56 BEGIN
57 IF (WMS_INSTALL.CHECK_INSTALL(X_RETURN_STATUS => M_RETURN_STATUS
58 ,X_MSG_COUNT => M_MSG_COUNT
59 ,X_MSG_DATA => M_MSG_DATA
60 ,P_ORGANIZATION_ID => P_ORG_ID)) THEN
61 P_WMS_INSTALLED := 'TRUE';
62 END IF;
63 END;
64 RETURN (TRUE);
65 END BEFOREREPORT;
66
67 FUNCTION AFTERREPORT RETURN BOOLEAN IS
68 BEGIN
69 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
70 RETURN (TRUE);
71 RETURN (TRUE);
72 END AFTERREPORT;
73
74 FUNCTION F_RECOUNTONLYFORMULA RETURN VARCHAR2 IS
75 BEGIN
76 BEGIN
77 IF P_RECOUNTONLY = 1 THEN
78 RETURN ('Recounts Only');
79 ELSE
80 RETURN ('');
81 END IF;
82 END;
83 RETURN NULL;
84 END F_RECOUNTONLYFORMULA;
85
86 FUNCTION AFTERPFORM RETURN BOOLEAN IS
87 BEGIN
88 RETURN (TRUE);
89 END AFTERPFORM;
90
91 -- For bug 14037978, modified the parameters by adding "p_" as prefix and modified the same in the definition
92 FUNCTION CF_SYSQTYFORMULA(P_SERIAL_NUMBER_CONTROL_CODE IN NUMBER
93 ,P_SERIAL_COUNT_OPTION IN NUMBER
94 ,P_INVENTORY_ITEM_ID IN NUMBER
95 ,P_SUBINVENTORY IN VARCHAR2
96 ,P_LOTNUMBER IN VARCHAR2
97 ,P_REVISION IN VARCHAR2
98 ,P_LOCATOR IN NUMBER
99 ,P_PARENT_LPN_ID IN NUMBER
100 ,P_SERIAL_NUMBER IN VARCHAR2) RETURN NUMBER IS
101 V_PRIMARY_SYS_QTY NUMBER := 0;
102 BEGIN
103 IF (P_SERIAL_NUMBER_CONTROL_CODE in (1,6) OR P_SERIAL_COUNT_OPTION = 1) THEN
104 SELECT
105 NVL(SUM(PRIMARY_TRANSACTION_QUANTITY)
106 ,0)
107 INTO V_PRIMARY_SYS_QTY
108 FROM
109 MTL_ONHAND_QUANTITIES_DETAIL
110 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
111 AND ORGANIZATION_ID = P_ORG_ID
112 AND SUBINVENTORY_CODE = P_SUBINVENTORY
113 AND NVL(LOT_NUMBER
114 ,'XX') = NVL(P_LOTNUMBER
115 ,'XX')
116 AND NVL(REVISION
117 ,'XXX') = NVL(P_REVISION
118 ,'XXX')
119 AND NVL(LOCATOR_ID
120 ,-2) = NVL(P_LOCATOR
121 ,-2)
122 AND NVL(LPN_ID
123 ,-3) = NVL(P_PARENT_LPN_ID
124 ,-3);
125 ELSIF (P_SERIAL_NUMBER_CONTROL_CODE in (2,5) AND P_SERIAL_COUNT_OPTION > 1) THEN
126 SELECT
127 NVL(SUM(DECODE(MSN.CURRENT_STATUS
128 ,3
129 ,1
130 ,0))
131 ,0)
132 INTO V_PRIMARY_SYS_QTY
133 FROM
134 MTL_SERIAL_NUMBERS MSN
135 WHERE MSN.SERIAL_NUMBER = NVL(P_SERIAL_NUMBER
136 ,MSN.SERIAL_NUMBER)
137 AND MSN.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
138 AND MSN.CURRENT_ORGANIZATION_ID = P_ORG_ID
139 AND MSN.CURRENT_SUBINVENTORY_CODE = P_SUBINVENTORY
140 AND NVL(MSN.LOT_NUMBER
141 ,'XX') = NVL(P_LOTNUMBER
142 ,'XX')
143 AND NVL(MSN.REVISION
144 ,'XXX') = NVL(P_REVISION
145 ,'XXX')
146 AND NVL(MSN.CURRENT_LOCATOR_ID
147 ,-2) = NVL(P_LOCATOR
148 ,-2)
149 AND NVL(MSN.LPN_ID
150 ,-3) = NVL(P_PARENT_LPN_ID
151 ,-3);
152 END IF;
153 RETURN NVL(V_PRIMARY_SYS_QTY
154 ,0);
155 END CF_SYSQTYFORMULA;
156
157 -- For bug 14037978, modified the parameters by adding "p_" as prefix and modified the same in the definition
158 FUNCTION CF_ISSERIALPRESENTFORMULA(P_SERIAL_NUMBER IN VARCHAR2
159 ,P_INVENTORY_ITEM_ID IN NUMBER
160 ,P_SUBINVENTORY IN VARCHAR2
161 ,P_LOTNUMBER IN VARCHAR2
162 ,P_REVISION IN VARCHAR2
163 ,P_LOCATOR IN NUMBER) RETURN VARCHAR2 IS
164 V_ISSERIALPRESENT VARCHAR2(1);
165 BEGIN
166 SELECT
167 DECODE(MSN.CURRENT_STATUS
168 ,3
169 ,'Y'
170 ,'N')
171 INTO V_ISSERIALPRESENT
172 FROM
173 MTL_SERIAL_NUMBERS MSN
174 WHERE MSN.SERIAL_NUMBER = P_SERIAL_NUMBER
175 AND MSN.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
176 AND MSN.CURRENT_ORGANIZATION_ID = P_ORG_ID
177 AND ( MSN.CURRENT_STATUS = 6
178 OR ( MSN.CURRENT_SUBINVENTORY_CODE = P_SUBINVENTORY
179 AND NVL(MSN.LOT_NUMBER
180 ,'XX') = NVL(P_LOTNUMBER
181 ,'XX')
182 AND NVL(MSN.REVISION
183 ,'XXX') = NVL(P_REVISION
184 ,'XXX')
185 AND NVL(MSN.CURRENT_LOCATOR_ID
186 ,-2) = NVL(P_LOCATOR
187 ,-2) ) );
188 RETURN V_ISSERIALPRESENT;
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 RETURN ('N');
192 END CF_ISSERIALPRESENTFORMULA;
193
194 -- For bug 14037978, modified the parameters by adding "p_" as prefix and modified the same in the definition
195 -- and replace return clause CHAR with VARCHAR2
196 FUNCTION CF_COST_GROUPFORMULA(P_COST_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
197 --M_COST_GROUP VARCHAR2(10) := NULL;
198 M_COST_GROUP CST_COST_GROUPS.COST_GROUP%TYPE := NULL;
199 BEGIN
200 IF ((P_WMS_INSTALLED = 'TRUE') AND (P_COST_GROUP_ID IS NOT NULL)) THEN
201 BEGIN
202 SELECT
203 COST_GROUP
204 INTO M_COST_GROUP
205 FROM
206 CST_COST_GROUPS
207 WHERE COST_GROUP_ID = P_COST_GROUP_ID;
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 M_COST_GROUP := NULL;
211 END;
212 END IF;
213 RETURN (M_COST_GROUP);
214 END CF_COST_GROUPFORMULA;
215
216 -- For bug 14037978, modified the parameters by adding "p_" as prefix and modified the same in the definition
217 -- and replace return clause CHAR with VARCHAR2
218 FUNCTION CF_OUTERMOST_LPNFORMULA(P_CONTAINER_ENABLED_FLAG IN NUMBER
219 ,P_OUTERMOST_LPN_ID IN NUMBER) RETURN VARCHAR2 IS
220 M_OUTERMOST_LPN VARCHAR2(30) := NULL;
221 BEGIN
222 IF ((P_WMS_INSTALLED = 'TRUE') AND (NVL(P_CONTAINER_ENABLED_FLAG,-99) > 0)) THEN
223 IF (P_OUTERMOST_LPN_ID IS NOT NULL) THEN
224 SELECT
225 LICENSE_PLATE_NUMBER
226 INTO M_OUTERMOST_LPN
227 FROM
228 WMS_LICENSE_PLATE_NUMBERS
229 WHERE LPN_ID = P_OUTERMOST_LPN_ID;
230 -- For bug 14037978 commented and added Exception block code
231 /* IF SQL%NOTFOUND THEN
232 M_OUTERMOST_LPN := 'ERROR';
233 END IF;*/
234 END IF;
235 END IF;
236 RETURN (M_OUTERMOST_LPN);
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 M_OUTERMOST_LPN := 'ERROR';
240 RETURN (M_OUTERMOST_LPN);
241 END CF_OUTERMOST_LPNFORMULA;
242
243 -- For bug 14037978, modified the parameters by adding "p_" as prefix and modified the same in the definition
244 -- and replace return clause CHAR with VARCHAR2
245 FUNCTION CF_PARENT_LPNFORMULA(P_CONTAINER_ENABLED_FLAG IN NUMBER
246 ,P_PARENT_LPN_ID IN NUMBER) RETURN VARCHAR2 IS
247 M_PARENT_LPN VARCHAR2(30) := NULL;
248 BEGIN
249 IF ((P_WMS_INSTALLED = 'TRUE') AND (NVL(P_CONTAINER_ENABLED_FLAG
250 ,-99) > 0)) THEN
251 IF (P_PARENT_LPN_ID IS NOT NULL) THEN
252 SELECT
253 LICENSE_PLATE_NUMBER
254 INTO M_PARENT_LPN
255 FROM
256 WMS_LICENSE_PLATE_NUMBERS
257 WHERE LPN_ID = P_PARENT_LPN_ID;
258 -- For bug 14037978 commented and added Exception block code
259 /* IF SQL%NOTFOUND THEN
260 M_PARENT_LPN := 'ERROR';
261 END IF;*/
262 END IF;
263 END IF;
264 RETURN (M_PARENT_LPN);
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 M_PARENT_LPN := 'ERROR';
268 RETURN (M_PARENT_LPN);
269 END CF_PARENT_LPNFORMULA;
270
271 END INV_INVARCLI_XMLP_PKG;
272
[Home] [Help]