[Home] [Help]
PACKAGE BODY: APPS.INV_INVTRSHS_XMLP_PKG
Source
1 PACKAGE BODY INV_INVTRSHS_XMLP_PKG AS
2 /* $Header: INVTRSHSB.pls 120.2 2008/01/08 06:43:24 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 X_RETURN_STATUS VARCHAR2(1);
5 X_MSG_COUNT NUMBER;
6 X_MSG_DATA VARCHAR2(2000);
7 X_SEQ_NUM NUMBER;
8 X_CHECK_RESULT VARCHAR2(1);
9 BEGIN
10 BEGIN
11 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
12 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
13 EXCEPTION
14 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
15 /*SRW.MESSAGE(1
16 ,'Failed in before report trigger:SRWINIT')*/NULL;
17 END;
18 DECLARE
19 P_ORG_ID_CHAR VARCHAR2(100) := TO_CHAR(P_ORG_ID);
20 BEGIN
21 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
22 EXCEPTION
23 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
24 /*SRW.MESSAGE(020
25 ,'Failed in before report trigger, setting org profile ')*/NULL;
26 RAISE;
27 END;
28 BEGIN
29 NULL;
30 EXCEPTION
31 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
32 /*SRW.MESSAGE(1
33 ,'Failed in before report trigger:MSTK')*/NULL;
34 END;
35 BEGIN
36 IF P_ITEM_FROM IS NOT NULL OR P_ITEM_TO IS NOT NULL THEN
37 NULL;
38 ELSE
39 NULL;
40 END IF;
41 EXCEPTION
42 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
43 /*SRW.MESSAGE(1
44 ,'Failed in before report trigger:MSTK:WHERE')*/NULL;
45 END;
46 BEGIN
47 IF P_GROUP_BY = 1 THEN
48 NULL;
49 ELSE
50 P_CAT_FLEX := '''MC''';
51 END IF;
52 EXCEPTION
53 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
54 /*SRW.MESSAGE(1
55 ,'Failed in before report trigger:MCAT/sel')*/NULL;
56 END;
57 BEGIN
58 IF P_CATEGORY_FROM IS NOT NULL OR P_CATEGORY_TO IS NOT NULL THEN
59 NULL;
60 ELSE
61 NULL;
62 END IF;
63 EXCEPTION
64 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
65 /*SRW.MESSAGE(1
66 ,'Failed in before report trigger:MCAT/WHERE')*/NULL;
67 END;
68 INV_SHORTCHECKEXEC_PVT.EXECCHECK(P_API_VERSION => 1.0
69 ,P_INIT_MSG_LIST => P_G_TRUE
70 ,P_COMMIT => P_G_TRUE
71 ,X_RETURN_STATUS => X_RETURN_STATUS
72 ,X_MSG_COUNT => X_MSG_COUNT
73 ,X_MSG_DATA => X_MSG_DATA
74 ,P_SUM_DETAIL_FLAG => 1
75 ,P_ORGANIZATION_ID => P_ORG_ID
76 ,P_INVENTORY_ITEM_ID => NULL
77 ,P_COMP_ATT_QTY_FLAG => 2
78 ,P_PRIMARY_QUANTITY => 0
79 ,X_SEQ_NUM => X_SEQ_NUM
80 ,X_CHECK_RESULT => X_CHECK_RESULT);
81 P_SEQ_NUM := X_SEQ_NUM;
82 RETURN (TRUE);
83 END BEFOREREPORT;
84 FUNCTION AFTERREPORT RETURN BOOLEAN IS
85 --P_SEQ_NUM NUMBER := P_SEQ_NUM;
86 P_SEQ_NUM_T NUMBER := P_SEQ_NUM;
87 X_RETURN_STATUS VARCHAR2(1);
88 X_MSG_COUNT NUMBER;
89 X_MSG_DATA VARCHAR2(2000);
90 BEGIN
91 INV_SHORTCHECKEXEC_PVT.PURGETEMPTABLE(P_API_VERSION => 1.0
92 ,P_INIT_MSG_LIST => P_G_TRUE
93 ,P_COMMIT => P_G_TRUE
94 ,X_RETURN_STATUS => X_RETURN_STATUS
95 ,X_MSG_COUNT => X_MSG_COUNT
96 ,X_MSG_DATA => X_MSG_DATA
97 ,P_SEQ_NUM => P_SEQ_NUM_T);
98 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
99 RETURN (TRUE);
100 END AFTERREPORT;
101 FUNCTION C_FROM_CATFORMULA RETURN VARCHAR2 IS
102 BEGIN
103 IF P_GROUP_BY = 1 OR P_CATEGORY_FROM IS NOT NULL OR P_CATEGORY_TO IS NOT NULL THEN
104 RETURN (',mtl_item_categories MIC, mtl_categories MC');
105 ELSE
106 RETURN ('/* Do not select from category tables.*/');
107 END IF;
108 RETURN NULL;
109 END C_FROM_CATFORMULA;
110 FUNCTION C_CAT_WHEREFORMULA RETURN VARCHAR2 IS
111 BEGIN
112 IF P_GROUP_BY = 1 OR P_CATEGORY_FROM IS NOT NULL OR P_CATEGORY_TO IS NOT NULL THEN
113 RETURN ('and MSI.inventory_item_id = MIC.inventory_item_id
114 and MIC.organization_id = MSI.organization_id
115 and MIC.category_set_id = ' || TO_CHAR(P_CATEGORY_SET) || '
116 and MIC.category_id = MC.category_id
117 and MIC.organization_id = ' || TO_CHAR(P_ORG_ID));
118 ELSE
119 NULL;
120 END IF;
121 RETURN ' ';
122 END C_CAT_WHEREFORMULA;
123 FUNCTION C_ORDER_BYFORMULA RETURN VARCHAR2 IS
124 V_ORDER VARCHAR2(200) := NULL;
125 BEGIN
126 IF P_GROUP_BY in (1,3) THEN
127 V_ORDER := P_ORDER_ITEM;
128 ELSE
129 V_ORDER := '';
130 END IF;
131 RETURN ('ORDER BY 14 ASC,1 ASC,6 ASC,8 ASC,16 ASC,10 ASC ,' || P_ORDER_HDR || V_ORDER || P_ORDER_POS);
132 END C_ORDER_BYFORMULA;
133 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
134 BEGIN
135 RETURN (TRUE);
136 END BEFOREPFORM;
137 FUNCTION AFTERPFORM RETURN BOOLEAN IS
138 BEGIN
139 RETURN (TRUE);
140 END AFTERPFORM;
141 FUNCTION C_HDR_ITEM_PADFORMULA(C_HDR_ITEM_PAD IN VARCHAR2) RETURN VARCHAR2 IS
142 BEGIN
143 RETURN (C_HDR_ITEM_PAD);
144 END C_HDR_ITEM_PADFORMULA;
145 FUNCTION C_GROUP_OPTION_NAMEFORMULA RETURN VARCHAR2 IS
146 V_MEANING VARCHAR2(80);
147 BEGIN
148 SELECT
149 ML.MEANING
150 INTO V_MEANING
151 FROM
152 MFG_LOOKUPS ML
153 WHERE ML.LOOKUP_TYPE = 'MTL_SHORT_SUM_PRINT_GROUP'
154 AND ML.LOOKUP_CODE = P_GROUP_BY;
155 RETURN (V_MEANING);
156 RETURN NULL;
157 EXCEPTION
158 WHEN OTHERS THEN
159 RETURN (P_GROUP_BY);
160 END C_GROUP_OPTION_NAMEFORMULA;
161 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
162 BEGIN
163 DECLARE
164 NAME VARCHAR2(30);
165 SET_ID NUMBER;
166 BEGIN
167 IF P_CATEGORY_SET IS NULL THEN
168 RETURN ('');
169 ELSE
170 SET_ID := P_CATEGORY_SET;
171 SELECT
172 MCS.CATEGORY_SET_NAME
173 INTO NAME
174 FROM
175 MTL_CATEGORY_SETS MCS
176 WHERE MCS.CATEGORY_SET_ID = SET_ID;
177 RETURN (NAME);
178 END IF;
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 RETURN ('No Data');
182 WHEN OTHERS THEN
183 RETURN ('Error');
184 END;
185 RETURN NULL;
186 END C_CAT_SET_NAMEFORMULA;
187 FUNCTION C_POS_ITEM_PADFORMULA(C_POS_ITEM_PAD IN VARCHAR2) RETURN VARCHAR2 IS
188 BEGIN
189 RETURN (C_POS_ITEM_PAD);
190 END C_POS_ITEM_PADFORMULA;
191 FUNCTION C_CAT_PADFORMULA(C_CAT_PAD IN VARCHAR2) RETURN VARCHAR2 IS
192 BEGIN
193 RETURN (C_CAT_PAD);
194 END C_CAT_PADFORMULA;
195 FUNCTION C_ONHAND_WHEREFORMULA RETURN VARCHAR2 IS
196 BEGIN
197 IF P_ONHAND_ONLY_YN = 1 THEN
198 RETURN ('and 0 < (select NVL(SUM(DECODE(MSIN.availability_type, 1, 1, 0) * MIQV.quantity), 0)
199 from mtl_secondary_inventories MSIN
200 ,mtl_item_quantities_view MIQV
201 where MSSV.organization_id = MIQV.organization_id
202 and MSSV.inventory_item_id = MIQV.inventory_item_id
203 and MIQV.organization_id = MSIN.organization_id
204 and MIQV.subinventory_code = MSIN.secondary_inventory_name)');
205 ELSE
206 NULL;
207 END IF;
208 RETURN ' ';
209 END C_ONHAND_WHEREFORMULA;
210 FUNCTION C_ONHAND_ONLY_YNFORMULA RETURN VARCHAR2 IS
211 V_MEANING VARCHAR2(80);
212 BEGIN
213 SELECT
214 ML.MEANING
215 INTO V_MEANING
216 FROM
217 MFG_LOOKUPS ML
218 WHERE ML.LOOKUP_TYPE = 'SYS_YES_NO'
219 AND ML.LOOKUP_CODE = P_ONHAND_ONLY_YN;
220 RETURN (V_MEANING);
221 RETURN NULL;
222 EXCEPTION
223 WHEN OTHERS THEN
224 RETURN (P_ONHAND_ONLY_YN);
225 END C_ONHAND_ONLY_YNFORMULA;
226 FUNCTION C_NET_QTY_HDRFORMULA(C_INVENTORY_ITEM_ID_HDR IN NUMBER) RETURN NUMBER IS
227 SUM_QTY NUMBER;
228 C_INVENTORY_ITEM_ID NUMBER;
229 BEGIN
230 IF P_GROUP_BY = 2 THEN
231 /*SRW.REFERENCE(C_INVENTORY_ITEM_ID_HDR)*/NULL;
232 C_INVENTORY_ITEM_ID := C_INVENTORY_ITEM_ID_HDR;
233 SELECT
234 NVL(SUM(DECODE(MSI.AVAILABILITY_TYPE
235 ,1
236 ,1
237 ,0) * MIQV.QUANTITY)
238 ,0)
239 INTO SUM_QTY
240 FROM
241 MTL_ITEM_QUANTITIES_VIEW MIQV,
242 MTL_SECONDARY_INVENTORIES MSI
243 WHERE MIQV.INVENTORY_ITEM_ID = C_INVENTORY_ITEM_ID
244 AND MIQV.ORGANIZATION_ID = P_ORG_ID
245 AND MSI.ORGANIZATION_ID = P_ORG_ID
246 AND MSI.SECONDARY_INVENTORY_NAME = MIQV.SUBINVENTORY_CODE;
247 RETURN (SUM_QTY);
248 ELSE
249 RETURN (0);
250 END IF;
251 RETURN NULL;
252 END C_NET_QTY_HDRFORMULA;
253 FUNCTION C_NET_QTY_POSFORMULA(C_INVENTORY_ITEM_ID_POS IN NUMBER) RETURN NUMBER IS
254 SUM_QTY NUMBER;
255 C_INVENTORY_ITEM_ID NUMBER;
256 BEGIN
257 IF P_GROUP_BY <> 2 THEN
258 /*SRW.REFERENCE(C_INVENTORY_ITEM_ID_POS)*/NULL;
259 C_INVENTORY_ITEM_ID := C_INVENTORY_ITEM_ID_POS;
260 SELECT
261 NVL(SUM(DECODE(MSI.AVAILABILITY_TYPE
262 ,1
263 ,1
264 ,0) * MIQV.QUANTITY)
265 ,0)
266 INTO SUM_QTY
267 FROM
268 MTL_ITEM_QUANTITIES_VIEW MIQV,
269 MTL_SECONDARY_INVENTORIES MSI
270 WHERE MIQV.INVENTORY_ITEM_ID = C_INVENTORY_ITEM_ID
271 AND MIQV.ORGANIZATION_ID = P_ORG_ID
272 AND MSI.ORGANIZATION_ID = P_ORG_ID
273 AND MSI.SECONDARY_INVENTORY_NAME = MIQV.SUBINVENTORY_CODE;
274 RETURN (SUM_QTY);
275 ELSE
276 RETURN (0);
277 END IF;
278 RETURN NULL;
279 END C_NET_QTY_POSFORMULA;
280 FUNCTION C_NOTIFICATIONFORMULA(C_INVENTORY_ITEM_ID_HDR IN NUMBER
281 ,C_INVENTORY_ITEM_ID_POS IN NUMBER) RETURN NUMBER IS
282 L_INVENTORY_ITEM_ID NUMBER;
283 BEGIN
284 IF P_GROUP_BY = 2 THEN
285 L_INVENTORY_ITEM_ID := C_INVENTORY_ITEM_ID_HDR;
286 ELSE
287 L_INVENTORY_ITEM_ID := C_INVENTORY_ITEM_ID_POS;
288 END IF;
289 IF P_INVENTORY_ITEM_ID = 0 OR P_INVENTORY_ITEM_ID <> L_INVENTORY_ITEM_ID THEN
290 IF P_SEND_NOTIFICATIONS_YN = 1 THEN
291 SEND(P_INVENTORY_ITEM_ID => L_INVENTORY_ITEM_ID);
292 END IF;
293 P_INVENTORY_ITEM_ID := L_INVENTORY_ITEM_ID;
294 END IF;
295 RETURN 1;
296 END C_NOTIFICATIONFORMULA;
297 FUNCTION C_SEND_NOTIFICATIONS_YNFORMULA RETURN VARCHAR2 IS
298 V_MEANING VARCHAR2(80);
299 BEGIN
300 SELECT
301 ML.MEANING
302 INTO V_MEANING
303 FROM
304 MFG_LOOKUPS ML
305 WHERE ML.LOOKUP_TYPE = 'SYS_YES_NO'
306 AND ML.LOOKUP_CODE = P_SEND_NOTIFICATIONS_YN;
307 RETURN (V_MEANING);
308 RETURN NULL;
309 EXCEPTION
310 WHEN OTHERS THEN
311 RETURN (P_SEND_NOTIFICATIONS_YN);
312 END C_SEND_NOTIFICATIONS_YNFORMULA;
313 FUNCTION C_ITEMPLANNER_WHEREFORMULA RETURN VARCHAR2 IS
314 BEGIN
315 IF P_ITEM_PLANNER IS NOT NULL THEN
316 RETURN ('AND mssv.item_planner_code = ' || '''' || P_ITEM_PLANNER || '''');
317 ELSE
318 NULL;
319 END IF;
320 RETURN ' ';
321 END C_ITEMPLANNER_WHEREFORMULA;
322 PROCEDURE SEND(P_INVENTORY_ITEM_ID IN NUMBER) IS
323 X_RETURN_STATUS VARCHAR2(1);
324 X_MSG_COUNT NUMBER;
325 X_MSG_DATA VARCHAR2(2000);
326 L_SEQ_NUM NUMBER;
327 L_INVENTORY_ITEM_ID NUMBER;
328 L_PREREQUISITES BOOLEAN;
329 L_CHECK_RESULT VARCHAR2(1);
330 BEGIN
331 INV_SHORTCHECKEXEC_PVT.CHECKPREREQUISITES(P_API_VERSION => 1.0
332 ,P_INIT_MSG_LIST => P_G_TRUE
333 ,X_RETURN_STATUS => X_RETURN_STATUS
334 ,X_MSG_COUNT => X_MSG_COUNT
335 ,X_MSG_DATA => X_MSG_DATA
336 ,P_SUM_DETAIL_FLAG => 1
337 ,P_ORGANIZATION_ID => P_ORG_ID
341 IF L_CHECK_RESULT = P_G_TRUE AND X_RETURN_STATUS = P_G_RET_STS_SUCCESS THEN
338 ,P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID
339 ,P_TRANSACTION_TYPE_ID => NULL
340 ,X_CHECK_RESULT => L_CHECK_RESULT);
342 L_PREREQUISITES := TRUE;
343 ELSE
344 L_PREREQUISITES := FALSE;
345 END IF;
346 IF L_PREREQUISITES THEN
347 INV_SHORTCHECKEXEC_PVT.EXECCHECK(P_API_VERSION => 1.0
348 ,P_INIT_MSG_LIST => P_G_TRUE
349 ,P_COMMIT => P_G_TRUE
350 ,X_RETURN_STATUS => X_RETURN_STATUS
351 ,X_MSG_COUNT => X_MSG_COUNT
352 ,X_MSG_DATA => X_MSG_DATA
353 ,P_SUM_DETAIL_FLAG => 1
354 ,P_ORGANIZATION_ID => P_ORG_ID
355 ,P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID
356 ,P_COMP_ATT_QTY_FLAG => 1
357 ,P_PRIMARY_QUANTITY => 0
358 ,X_SEQ_NUM => L_SEQ_NUM
359 ,X_CHECK_RESULT => L_CHECK_RESULT);
360 IF X_RETURN_STATUS <> P_G_RET_STS_SUCCESS THEN
361 NULL;
362 ELSE
363 IF L_CHECK_RESULT = P_G_TRUE THEN
364 INV_SHORTCHECKEXEC_PVT.SENDNOTIFICATIONS(P_API_VERSION => 1.0
365 ,P_INIT_MSG_LIST => P_G_TRUE
366 ,P_COMMIT => P_G_TRUE
367 ,X_RETURN_STATUS => X_RETURN_STATUS
368 ,X_MSG_COUNT => X_MSG_COUNT
369 ,X_MSG_DATA => X_MSG_DATA
370 ,P_ORGANIZATION_ID => P_ORG_ID
371 ,P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID
372 ,P_SEQ_NUM => L_SEQ_NUM
373 ,P_NOTIFICATION_TYPE => 'S');
374 END IF;
375 END IF;
376 INV_SHORTCHECKEXEC_PVT.PURGETEMPTABLE(P_API_VERSION => 1.0
377 ,P_INIT_MSG_LIST => P_G_TRUE
378 ,P_COMMIT => P_G_TRUE
379 ,X_RETURN_STATUS => X_RETURN_STATUS
380 ,X_MSG_COUNT => X_MSG_COUNT
381 ,X_MSG_DATA => X_MSG_DATA
382 ,P_SEQ_NUM => L_SEQ_NUM);
383 END IF;
384 END SEND;
385 END INV_INVTRSHS_XMLP_PKG;
386