1 PACKAGE BODY INV_INVARPSL_XMLP_PKG AS
2 /* $Header: INVARPSLB.pls 120.1.12020000.2 2012/07/09 08:03:00 asugandh ship $ */
3 FUNCTION C_TOTAL_VALUEFORMULA(QUANTITY IN NUMBER
4 ,COST IN NUMBER
5 ,C_STD_PREC IN NUMBER) RETURN NUMBER IS
6 BEGIN
7 RETURN (ROUND(QUANTITY * COST
8 ,C_STD_PREC));
9 END C_TOTAL_VALUEFORMULA;
10
11 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
12 BEGIN
13 BEGIN
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 ,'Failed in before report trigger:SRWINIT')*/NULL;
20 RAISE;
21 IF (GET_P_STRUCT_NUM <> TRUE) THEN
22 /*SRW.MESSAGE('1'
23 ,'P Struct Num Init failed')*/NULL;
24 END IF;
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 ,'Failed in before report trigger:MSTK')*/NULL;
42 RAISE;
43 END;
44 BEGIN
45 NULL;
46 EXCEPTION
47 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
48 /*SRW.MESSAGE(1
49 ,'Failed in before report trigger:MSTK:ORDERBY')*/NULL;
50 RAISE;
51 END;
52 BEGIN
53 NULL;
54 EXCEPTION
55 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
56 /*SRW.MESSAGE(1
57 ,'Failed in before report trigger:MCAT')*/NULL;
58 RAISE;
59 END;
60 BEGIN
61 NULL;
62 EXCEPTION
63 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
64 /*SRW.MESSAGE(1
65 ,'Failed in before report trigger:MCAT:ORDERBY')*/NULL;
66 RAISE;
67 END;
68 BEGIN
69 NULL;
70 EXCEPTION
71 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
72 /*SRW.MESSAGE(1
73 ,'Failed in before report trigger:MTLL')*/NULL;
74 RAISE;
75 END;
76 DECLARE
77 M_RETURN_STATUS VARCHAR2(1);
78 M_MSG_COUNT NUMBER;
79 M_MSG_DATA VARCHAR2(2000);
80 BEGIN
81 IF (WMS_INSTALL.CHECK_INSTALL(X_RETURN_STATUS => M_RETURN_STATUS
82 ,X_MSG_COUNT => M_MSG_COUNT
83 ,X_MSG_DATA => M_MSG_DATA
84 ,P_ORGANIZATION_ID => P_ORG_ID)) THEN
85 P_WMS_INSTALLED := 'TRUE';
86 END IF;
87 END;
88 RETURN (TRUE);
89 END BEFOREREPORT;
90
91 FUNCTION AFTERREPORT RETURN BOOLEAN IS
92 BEGIN
93 BEGIN
94 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
95 EXCEPTION
96 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
97 /*SRW.MESSAGE(1
98 ,'SRWEXIT failed')*/NULL;
99 END;
100 RETURN (TRUE);
101 END AFTERREPORT;
102
103 FUNCTION C_ORDER_BYFORMULA RETURN VARCHAR2 IS
104 BEGIN
105 IF P_SORT_OPTION = '1' THEN
106 RETURN (P_ORDER_ITEM || ',' || P_ORDER_CAT || ',4,5');
107 END IF;
108 IF P_SORT_OPTION = '2' THEN
109 RETURN (P_ORDER_CAT || ',' || P_ORDER_ITEM || ',4,5');
110 END IF;
111 IF P_SORT_OPTION = '3' THEN
112 RETURN ('4,' || P_ORDER_CAT || ',' || P_ORDER_ITEM || ',5');
113 END IF;
114 IF P_SORT_OPTION = '4' THEN
115 RETURN ('5,' || P_ORDER_CAT || ',' || P_ORDER_ITEM || ',4');
116 ELSE
117 RETURN NULL;
118 END IF;
119 RETURN NULL;
120 END C_ORDER_BYFORMULA;
121
122 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
123 BEGIN
124 DECLARE
125 NAME VARCHAR2(30);
126 SET_ID NUMBER;
127 BEGIN
128 SET_ID := P_CATEGORY_SET_ID;
129 SELECT
130 CATEGORY_SET_NAME
131 INTO NAME
132 FROM
133 MTL_CATEGORY_SETS
134 WHERE CATEGORY_SET_ID = SET_ID;
135 RETURN (NAME);
136 EXCEPTION
137 WHEN NO_DATA_FOUND THEN
138 RETURN (NULL);
139 END;
140 RETURN NULL;
141 END C_CAT_SET_NAMEFORMULA;
142
143 FUNCTION C_PHYS_INV_NAMEFORMULA RETURN VARCHAR2 IS
144 BEGIN
145 DECLARE
146 NAME VARCHAR2(30);
147 ORG NUMBER;
148 PI_ID NUMBER;
149 BEGIN
150 ORG := P_ORG_ID;
151 PI_ID := P_PHYS_INV_ID;
152 SELECT
153 PHYSICAL_INVENTORY_NAME
154 INTO NAME
155 FROM
156 MTL_PHYSICAL_INVENTORIES
157 WHERE ORGANIZATION_ID = ORG
158 AND PHYSICAL_INVENTORY_ID = PI_ID;
159 RETURN (NAME);
160 EXCEPTION
161 WHEN NO_DATA_FOUND THEN
162 RETURN (NULL);
163 END;
164 RETURN NULL;
165 END C_PHYS_INV_NAMEFORMULA;
166
167 FUNCTION C_CURRENCY_CODEFORMULA(R_CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
168 BEGIN
169 RETURN ('(' || R_CURRENCY_CODE || ')');
170 END C_CURRENCY_CODEFORMULA;
171
172 FUNCTION AFTERPFORM RETURN BOOLEAN IS
173 BEGIN
174 RETURN (TRUE);
175 END AFTERPFORM;
176
177 FUNCTION GET_P_STRUCT_NUM RETURN BOOLEAN IS
178 L_P_STRUCT_NUM NUMBER;
179 BEGIN
180 SELECT
181 STRUCTURE_ID
182 INTO L_P_STRUCT_NUM
183 FROM
184 MTL_DEFAULT_SETS_VIEW
185 WHERE FUNCTIONAL_AREA_ID = 1;
186 P_CAT_STRUCT_NUM := L_P_STRUCT_NUM;
187 RETURN (TRUE);
188 RETURN NULL;
189 EXCEPTION
190 WHEN OTHERS THEN
191 RETURN (FALSE);
192 END GET_P_STRUCT_NUM;
193
194 -- bug13722951 changing the function argument from OUTERMOST_LPN_ID to P_OUTERMOST_LPN_ID
195 FUNCTION CF_OUTERMOST_LPNFORMULA(P_OUTERMOST_LPN_ID IN NUMBER) RETURN CHAR IS
196 M_OUTERMOST_LPN VARCHAR2(30) := NULL;
197 BEGIN
198 IF ((P_WMS_INSTALLED = 'TRUE') AND (P_OUTERMOST_LPN_ID IS NOT NULL)) THEN
199 BEGIN
200 SELECT
201 LICENSE_PLATE_NUMBER
202 INTO M_OUTERMOST_LPN
203 FROM
204 WMS_LICENSE_PLATE_NUMBERS
205 WHERE LPN_ID = P_OUTERMOST_LPN_ID;
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 M_OUTERMOST_LPN := 'ERROR';
209 END;
210 END IF;
211 RETURN (M_OUTERMOST_LPN);
212 END CF_OUTERMOST_LPNFORMULA;
213
214 -- bug13722951 changing the function argument from PARENT_LPN_ID to P_PARENT_LPN_ID
215 FUNCTION CF_PARENT_LPNFORMULA(P_PARENT_LPN_ID IN NUMBER) RETURN CHAR IS
216 M_PARENT_LPN VARCHAR2(30) := NULL;
217 BEGIN
218 IF ((P_WMS_INSTALLED = 'TRUE') AND (P_PARENT_LPN_ID IS NOT NULL)) THEN
219 BEGIN
220 SELECT
221 LICENSE_PLATE_NUMBER
222 INTO M_PARENT_LPN
223 FROM
224 WMS_LICENSE_PLATE_NUMBERS
225 WHERE LPN_ID = P_PARENT_LPN_ID;
226 EXCEPTION
227 WHEN NO_DATA_FOUND THEN
228 M_PARENT_LPN := 'ERROR';
229 END;
230 END IF;
231 RETURN (M_PARENT_LPN);
232 END CF_PARENT_LPNFORMULA;
233
234 -- bug13722951 changing the function argument from COST_GROUP_ID to P_COST_GROUP_ID
235 FUNCTION CF_COST_GROUPFORMULA(P_COST_GROUP_ID IN NUMBER) RETURN CHAR IS
236 M_COST_GROUP VARCHAR2(10) := NULL;
237 BEGIN
238 IF ((P_WMS_INSTALLED = 'TRUE') AND (P_COST_GROUP_ID IS NOT NULL)) THEN
239 BEGIN
240 SELECT
241 COST_GROUP
242 INTO M_COST_GROUP
243 FROM
244 CST_COST_GROUPS
245 WHERE COST_GROUP_ID = P_COST_GROUP_ID;
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN
248 M_COST_GROUP := 'ERROR';
249 END;
250 END IF;
251 RETURN (M_COST_GROUP);
252 END CF_COST_GROUPFORMULA;
253
254 END INV_INVARPSL_XMLP_PKG;
255
[Home] [Help]