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