1 PACKAGE BODY INV_INVARPSL_XMLP_PKG AS
2 /* $Header: INVARPSLB.pls 120.1 2007/12/25 10:09:03 dwkrishn noship $ */
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 FUNCTION CF_OUTERMOST_LPNFORMULA(OUTERMOST_LPN_ID IN NUMBER) RETURN CHAR IS
195 M_OUTERMOST_LPN VARCHAR2(30) := NULL;
196 BEGIN
197 IF ((P_WMS_INSTALLED = 'TRUE') AND (OUTERMOST_LPN_ID IS NOT NULL)) THEN
198 BEGIN
199 SELECT
200 LICENSE_PLATE_NUMBER
201 INTO M_OUTERMOST_LPN
202 FROM
203 WMS_LICENSE_PLATE_NUMBERS
204 WHERE LPN_ID = OUTERMOST_LPN_ID;
205 EXCEPTION
206 WHEN NO_DATA_FOUND THEN
207 M_OUTERMOST_LPN := 'ERROR';
208 END;
209 END IF;
210 RETURN (M_OUTERMOST_LPN);
211 END CF_OUTERMOST_LPNFORMULA;
212
213 FUNCTION CF_PARENT_LPNFORMULA(PARENT_LPN_ID IN NUMBER) RETURN CHAR IS
214 M_PARENT_LPN VARCHAR2(30) := NULL;
215 BEGIN
216 IF ((P_WMS_INSTALLED = 'TRUE') AND (PARENT_LPN_ID IS NOT NULL)) THEN
217 BEGIN
218 SELECT
219 LICENSE_PLATE_NUMBER
220 INTO M_PARENT_LPN
221 FROM
222 WMS_LICENSE_PLATE_NUMBERS
223 WHERE LPN_ID = PARENT_LPN_ID;
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 M_PARENT_LPN := 'ERROR';
227 END;
228 END IF;
229 RETURN (M_PARENT_LPN);
230 END CF_PARENT_LPNFORMULA;
231
232 FUNCTION CF_COST_GROUPFORMULA(COST_GROUP_ID IN NUMBER) RETURN CHAR IS
233 M_COST_GROUP VARCHAR2(10) := NULL;
234 BEGIN
235 IF ((P_WMS_INSTALLED = 'TRUE') AND (COST_GROUP_ID IS NOT NULL)) THEN
236 BEGIN
237 SELECT
238 COST_GROUP
239 INTO M_COST_GROUP
240 FROM
241 CST_COST_GROUPS
242 WHERE COST_GROUP_ID = COST_GROUP_ID;
243 EXCEPTION
244 WHEN NO_DATA_FOUND THEN
245 M_COST_GROUP := 'ERROR';
246 END;
247 END IF;
248 RETURN (M_COST_GROUP);
249 END CF_COST_GROUPFORMULA;
250
251 END INV_INVARPSL_XMLP_PKG;
252
[Home] [Help]