[Home] [Help]
PACKAGE BODY: APPS.INV_INVARPTP_XMLP_PKG
Source
1 PACKAGE BODY INV_INVARPTP_XMLP_PKG AS
2 /* $Header: INVARPTPB.pls 120.1 2008/02/21 11:03:51 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
6 RETURN (TRUE);
7 RETURN (TRUE);
8 END AFTERREPORT;
9 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
10 BEGIN
11 BEGIN
12 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
13 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
14 EXCEPTION
15 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
16 /*SRW.MESSAGE(1
17 ,'Srwinit failed before report trigger')*/NULL;
18 RAISE;
19 END;
20 DECLARE
21 P_ORG_ID_CHAR VARCHAR2(100) := TO_CHAR(P_ORG_ID);
22 BEGIN
23 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
24 EXCEPTION
25 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
26 /*SRW.MESSAGE(020
27 ,'Failed in before report trigger, setting org profile ')*/NULL;
28 RAISE;
29 END;
30 SELECT
31 ID_FLEX_NUM
32 INTO P_ITEM_STRUCT
33 FROM
34 FND_ID_FLEX_STRUCTURES
35 WHERE ID_FLEX_CODE = 'MSTK';
36 SELECT
37 ID_FLEX_NUM
38 INTO P_LOC_STRUCT
39 FROM
40 FND_ID_FLEX_STRUCTURES
41 WHERE ID_FLEX_CODE = 'MTLL';
42 BEGIN
43 /*SRW.REFERENCE(P_ITEM_STRUCT)*/NULL;
44 EXCEPTION
45 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
46 /*SRW.MESSAGE(2
47 ,'Item Flex Select failed before report trigger')*/NULL;
48 RAISE;
49 END;
50 BEGIN
51 /*SRW.REFERENCE(P_LOC_STRUCT)*/NULL;
52 EXCEPTION
53 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
54 /*SRW.MESSAGE(3
55 ,'Locator Flex Select failed before report trigger')*/NULL;
56 RAISE;
57 END;
58 IF (P_RANGE = 2) THEN
59 IF (P_TAG_LO IS NOT NULL) AND (P_TAG_HI IS NOT NULL) THEN
60 P_RANGE_SQL := 'mpit.tag_number between' || '''' || P_TAG_LO || '''' || ' and ' || '''' || P_TAG_HI || '''';
61 ELSIF (P_SORT_ID = 1) AND (P_TAG_LO IS NOT NULL) THEN
62 P_RANGE_SQL := 'mpit.tag_number >= ' || '''' || P_TAG_LO || '''';
63 ELSIF (P_SORT_ID = 1) AND (P_TAG_HI IS NOT NULL) THEN
64 P_RANGE_SQL := 'mpit.tag_number <= ' || '''' || P_TAG_HI || '''';
65 END IF;
66 IF (P_LOC_LO IS NOT NULL) AND (P_LOC_HI IS NOT NULL) THEN
67 BEGIN
68 /*SRW.REFERENCE(P_LOC_STRUCT)*/NULL;
69 /*SRW.USER_EXIT('FND FLEXSQL
70 CODE = "MTLL"
71 APPL_SHORT_NAME = "INV"
72 OUTPUT = "P_RANGE_FLEX"
73 MODE = "WHERE"
74 NUM = ":P_LOC_STRUCT"
75 TABLEALIAS = "MIL"
76 OPERATOR = "BETWEEN"
77 OPERAND1 = ":P_LOC_LO"
78 OPERAND2 = ":P_LOC_HI"')*/NULL;
79 EXCEPTION
80 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
81 /*SRW.MESSAGE(4
82 ,'Locator Flex Where failed before report trigger')*/NULL;
83 RAISE;
84 END;
85 END IF;
86 IF (P_LOC_LO IS NOT NULL) AND (P_LOC_HI IS NULL) THEN
87 BEGIN
88 /*SRW.REFERENCE(P_LOC_STRUCT)*/NULL;
89 /*SRW.USER_EXIT('FND FLEXSQL
90 CODE = "MTLL"
91 APPL_SHORT_NAME = "INV"
92 OUTPUT = "P_RANGE_FLEX"
93 MODE = "WHERE"
94 NUM = ":P_LOC_STRUCT"
95 TABLEALIAS = "MIL"
96 OPERATOR = ">="
97 OPERAND1 = ":P_LOC_LO"')*/NULL;
98 EXCEPTION
99 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
100 /*SRW.MESSAGE(5
101 ,'Locator Flex Where failed before report trigger')*/NULL;
102 RAISE;
103 END;
104 END IF;
105 IF (P_LOC_LO IS NULL) AND (P_LOC_HI IS NOT NULL) THEN
106 BEGIN
107 /*SRW.REFERENCE(P_LOC_STRUCT)*/NULL;
108 /*SRW.USER_EXIT('FND FLEXSQL
109 CODE = "MTLL"
110 APPL_SHORT_NAME = "INV"
111 OUTPUT = "P_RANGE_FLEX"
112 MODE = "WHERE"
113 NUM = ":P_LOC_STRUCT"
114 TABLEALIAS = "MIL"
115 OPERATOR = "<="
116 OPERAND1 = ":P_LOC_HI"')*/NULL;
117 EXCEPTION
118 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
119 /*SRW.MESSAGE(6
120 ,'Locator Flex Where failed before report trigger')*/NULL;
121 RAISE;
122 END;
123 END IF;
124 IF (P_ITEM_LO IS NOT NULL) AND (P_ITEM_HI IS NOT NULL) THEN
125 BEGIN
126 /*SRW.REFERENCE(P_ITEM_STRUCT)*/NULL;
127 /*SRW.USER_EXIT('FND FLEXSQL
128 CODE = "MSTK"
129 NUM = ":P_ITEM_STRUCT"
130 APPL_SHORT_NAME = "INV"
131 OUTPUT = "P_RANGE_FLEX"
132 MODE = "WHERE"
133 TABLEALIAS = "MSI"
134 OPERATOR = "BETWEEN"
135 OPERAND1 = ":P_ITEM_LO"
136 OPERAND2 = ":P_ITEM_HI"')*/NULL;
137 EXCEPTION
138 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
139 /*SRW.MESSAGE(7
140 ,'Item Flex Where failed before report trigger')*/NULL;
141 RAISE;
142 END;
143 END IF;
144 IF (P_ITEM_LO IS NOT NULL) AND (P_ITEM_HI IS NULL) THEN
145 BEGIN
146 /*SRW.REFERENCE(P_ITEM_STRUCT)*/NULL;
147 /*SRW.USER_EXIT('FND FLEXSQL
148 CODE="MSTK"
149 APPL_SHORT_NAME="INV"
150 OUTPUT="P_RANGE_FLEX"
151 MODE="WHERE"
152 DISPLAY="ALL"
153 NUM=":P_ITEM_STRUCT"
154 TABLEALIAS="MSI"
155 OPERATOR=">="
156 OPERAND1=":P_ITEM_LO"')*/NULL;
157 EXCEPTION
158 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
159 /*SRW.MESSAGE(8
160 ,'Item Flex Where failed before report trigger')*/NULL;
161 RAISE;
162 END;
163 END IF;
164 IF (P_ITEM_LO IS NULL) AND (P_ITEM_HI IS NOT NULL) THEN
165 BEGIN
166 /*SRW.REFERENCE(P_ITEM_STRUCT)*/NULL;
167 /*SRW.USER_EXIT('FND FLEXSQL
168 CODE = "MSTK"
169 APPL_SHORT_NAME = "INV"
170 OUTPUT = "P_RANGE_FLEX"
171 MODE = "WHERE"
172 NUM = ":P_ITEM_STRUCT"
173 TABLEALIAS = "MSI"
174 OPERATOR = "<="
175 OPERAND1 = ":P_ITEM_HI"')*/NULL;
176 EXCEPTION
177 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
178 /*SRW.MESSAGE(9
179 ,'Item Flex Where failed before report trigger')*/NULL;
180 RAISE;
181 END;
182 END IF;
183 END IF;
184 BEGIN
185 /*SRW.REFERENCE(P_ITEM_STRUCT)*/NULL;
186 EXCEPTION
187 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
188 /*SRW.MESSAGE(10
189 ,'Item Flex Order By failed before report trigger')*/NULL;
190 END;
191 BEGIN
192 NULL;
193 EXCEPTION
194 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
195 /*SRW.MESSAGE(11
196 ,'Locator Flex Order By failed before report trigger')*/NULL;
197 END;
198 IF P_SORT_ID = 1 THEN
199 P_ORDER_BY := 'order by 4';
200 ELSIF P_SORT_ID = 2 THEN
201 P_ORDER_BY := 'order by 10, 11, 5, 8, 13, 14, 4';
202 ELSIF P_SORT_ID = 3 THEN
203 P_ORDER_BY := 'order by 10, 5, 8, 13, 14, 11, 4';
204 ELSIF P_SORT_ID = 4 THEN
205 P_ORDER_BY := 'order by 5, 10, 11';
206 END IF;
207 WMS_INSTALLED;
208 RETURN (TRUE);
209 END BEFOREREPORT;
210 PROCEDURE WMS_INSTALLED IS
211 X_RETURN_STATUS VARCHAR2(240) := NULL;
212 X_MSG_COUNT NUMBER := NULL;
213 X_MSG_DATA VARCHAR2(240) := NULL;
214 BEGIN
215 IF (WMS_INSTALL.CHECK_INSTALL(X_RETURN_STATUS
216 ,X_MSG_COUNT
217 ,X_MSG_DATA
218 ,P_ORG_ID)) THEN
219 P_WMS_INSTALLED := 'TRUE';
220 ELSE
221 P_WMS_INSTALLED := 'FALSE';
222 END IF;
223 EXCEPTION
224 WHEN OTHERS THEN
225 /*SRW.MESSAGE(12
226 ,'WMS Installed failed before report trigger')*/NULL;
227 END WMS_INSTALLED;
228 FUNCTION CF_PARENT_LPNFORMULA(PARENT_LPN IN NUMBER) RETURN CHAR IS
229 L_LPN VARCHAR2(30) := NULL;
230 BEGIN
231 --commetented as fix
232 /* L_LPN := GET_LPN(P_LPN_ID
233 ,PARENT_LPN);*/
234 L_LPN := GET_LPN(PARENT_LPN);
235 RETURN L_LPN;
236 END CF_PARENT_LPNFORMULA;
237 FUNCTION GET_LPN(P_LPN_ID IN NUMBER) RETURN VARCHAR2 IS
238 L_LPN_NAME VARCHAR2(30) := NULL;
239 BEGIN
240 --IF (P_WMS_INSTALLED = 'TRUE' AND PARENT_LPN IS NOT NULL) THEN
241 IF (P_WMS_INSTALLED = 'TRUE' AND P_LPN_ID IS NOT NULL) THEN
242 SELECT
243 LICENSE_PLATE_NUMBER
244 INTO L_LPN_NAME
245 FROM
246 WMS_LICENSE_PLATE_NUMBERS
247 WHERE LPN_ID = P_LPN_ID;
248 ELSE
249 L_LPN_NAME := NULL;
250 END IF;
251 RETURN L_LPN_NAME;
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 RETURN NULL;
255 END GET_LPN;
256 FUNCTION CF_OUTERMOST_LPNFORMULA(OUTERMOST_LPN IN NUMBER
257 ,PARENT_LPN IN NUMBER) RETURN CHAR IS
258 L_LPN VARCHAR2(30) := NULL;
259 BEGIN
260 /*L_LPN := GET_LPN(P_LPN_ID
261 ,PARENT_LPN);*/
262 L_LPN := GET_LPN(PARENT_LPN);
263 RETURN L_LPN;
264 END CF_OUTERMOST_LPNFORMULA;
265 FUNCTION CF_COST_GROUPFORMULA(COST_GROUP_ID_1 IN NUMBER) RETURN CHAR IS
266 M_COST_GROUP VARCHAR2(10) := NULL;
267 BEGIN
268 IF ((P_WMS_INSTALLED = 'TRUE') AND (COST_GROUP_ID_1 IS NOT NULL)) THEN
269 BEGIN
270 SELECT
271 COST_GROUP
272 INTO M_COST_GROUP
273 FROM
274 CST_COST_GROUPS
275 WHERE COST_GROUP_ID = COST_GROUP_ID_1;
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN
278 M_COST_GROUP := 'ERROR';
279 END;
280 END IF;
281 RETURN (M_COST_GROUP);
282 END CF_COST_GROUPFORMULA;
283 END INV_INVARPTP_XMLP_PKG;
284