[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTREIVR_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTREIVR_XMLP_PKG AS
2 /* $Header: CSTREIVRB.pls 120.0.12020000.2 2012/10/26 09:09:29 vnagasow ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 L_FCN_CURRENCY VARCHAR2(15);
7 L_RETURN_STATUS VARCHAR2(1);
8 L_MSG_COUNT NUMBER;
9 L_MSG_DATA VARCHAR2(2000);
10 SQL_STMT_NUM VARCHAR2(5);
11 L_AS_OF_DATE VARCHAR2(30);
12 L_CST_INV_VAL EXCEPTION;
13 BEGIN
14 SQL_STMT_NUM := '0: ';
15 P_EXCHANGE_RATE := FND_NUMBER.CANONICAL_TO_NUMBER(P_EXCHANGE_RATE_CHAR);
16 SQL_STMT_NUM := '0: ';
17 IF P_VIEW_COST <> 1 THEN
18 FND_MESSAGE.SET_NAME('null'
19 ,'null');
20 /*SRW.USER_EXIT('FND MESSAGE_DISPLAY')*/NULL;
21 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
22 END IF;
23 SQL_STMT_NUM := '2: ';
24 SELECT
25 O.ORGANIZATION_NAME,
26 MP.COST_ORGANIZATION_ID,
27 NVL(MINIMUM_ACCOUNTABLE_UNIT
28 ,POWER(10
29 ,NVL(-PRECISION
30 ,0))),
31 MCS.CATEGORY_SET_NAME,
32 LU1.MEANING,
33 LU2.MEANING,
34 USERENV('SESSIONID')
35 INTO P_ORGANIZATION,P_COST_ORG_ID,ROUND_UNIT,P_CAT_SET_NAME,P_SORT_BY,P_DETAIL_LEVEL,P_SESSIONID
36 FROM
37 ORG_ORGANIZATION_DEFINITIONS O,
38 MTL_PARAMETERS MP,
39 FND_CURRENCIES FC,
40 MTL_CATEGORY_SETS MCS,
41 MFG_LOOKUPS LU1,
42 MFG_LOOKUPS LU2
43 WHERE FC.CURRENCY_CODE = P_CURRENCY_CODE
44 AND O.ORGANIZATION_ID = P_ORG_ID
45 AND MP.ORGANIZATION_ID = P_ORG_ID
46 AND MCS.CATEGORY_SET_ID = P_CATEGORY_SET
47 AND LU1.LOOKUP_TYPE = 'CST_ITEM_REPORT_SORT'
48 AND LU1.LOOKUP_CODE = P_SORT_OPTION
49 AND LU2.LOOKUP_TYPE = 'CST_BICR_DETAIL_OPTION'
50 AND LU2.LOOKUP_CODE = P_RPT_OPTION;
51 SQL_STMT_NUM := '3: ';
52 SELECT
53 SOB.CURRENCY_CODE
54 INTO L_FCN_CURRENCY
55 FROM
56 GL_SETS_OF_BOOKS SOB,
57 ORG_ORGANIZATION_DEFINITIONS OOD
58 WHERE OOD.ORGANIZATION_ID = P_ORG_ID
59 AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID;
60 SQL_STMT_NUM := '4: ';
61 SELECT
62 DEFAULT_COST_TYPE_ID,
63 COST_TYPE
64 INTO P_DEF_COST_TYPE,P_COST_TYPE
65 FROM
66 CST_COST_TYPES
67 WHERE COST_TYPE_ID = P_COST_TYPE_ID;
68 IF L_FCN_CURRENCY = P_CURRENCY_CODE THEN
69 P_CURRENCY_DSP := P_CURRENCY_CODE;
70 ELSE
71 P_CURRENCY_DSP := P_CURRENCY_CODE || ' @ ' || TO_CHAR(ROUND(1 / P_EXCHANGE_RATE
72 ,5)) || L_FCN_CURRENCY;
73 END IF;
74 SQL_STMT_NUM := '5: ';
75 IF P_SORT_OPTION = 3 THEN
76 /*SRW.SET_MAXROW('Q_IC_MAIN'
77 ,0)*/NULL;
78 ELSE
79 /*SRW.SET_MAXROW('Q_SI_MAIN'
80 ,0)*/NULL;
81 END IF;
82 BEGIN
83 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
84 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
85 EXCEPTION
86 WHEN OTHERS THEN
87 /*SRW.MESSAGE(999
88 ,'FND SRWINIT >X')*/NULL;
89 RAISE;
90 END;
91 BEGIN
92 NULL;
93 EXCEPTION
94 WHEN OTHERS THEN
95 /*SRW.MESSAGE(999
96 ,'FND FLEXSQL(MCAT) >X')*/NULL;
97 RAISE;
98 END;
99 BEGIN
100 NULL;
101 EXCEPTION
102 WHEN OTHERS THEN
103 /*SRW.MESSAGE(999
104 ,'FND FLEXSQL(MSTK) >X')*/NULL;
105 RAISE;
106 END;
107 SQL_STMT_NUM := '6: ';
108 CST_INVENTORY_PUB.CALCULATE_INVENTORYVALUE(P_API_VERSION => 1.0
109 ,P_INIT_MSG_LIST => CST_UTILITY_PUB.GET_TRUE
110 ,P_ORGANIZATION_ID => P_ORG_ID
111 ,P_ONHAND_VALUE => 1
112 ,P_INTRANSIT_VALUE => P_INTRANSIT
113 ,P_RECEIVING_VALUE => 0
114 ,P_VALUATION_DATE => TO_DATE(P_AS_OF_DATE
115 ,'YYYY/MM/DD HH24:MI:SS')
116 ,P_COST_TYPE_ID => P_COST_TYPE_ID
117 ,P_ITEM_FROM => P_ITEM_FROM
118 ,P_ITEM_TO => P_ITEM_TO
119 ,P_CATEGORY_SET_ID => P_CATEGORY_SET
120 ,P_CATEGORY_FROM => P_CAT_FROM
121 ,P_CATEGORY_TO => P_CAT_TO
122 ,P_COST_GROUP_FROM => NULL
123 ,P_COST_GROUP_TO => NULL
124 ,P_SUBINVENTORY_FROM => P_SUBINV_FROM
125 ,P_SUBINVENTORY_TO => P_SUBINV_TO
126 ,P_QTY_BY_REVISION => 2
127 ,P_ZERO_COST_ONLY => P_ZERO_COST
128 ,P_ZERO_QTY => P_ZERO_QTY
129 ,P_EXPENSE_ITEM => P_EXP_ITEM
130 ,P_EXPENSE_SUB => P_EXP_SUB
131 ,P_UNVALUED_TXNS => P_UNCOSTED_TXN
132 ,P_RECEIPT => P_INTRANSIT
133 ,P_SHIPMENT => P_INTRANSIT
134 ,X_RETURN_STATUS => L_RETURN_STATUS
135 ,X_MSG_COUNT => L_MSG_COUNT
136 ,X_MSG_DATA => L_MSG_DATA);
137 IF L_RETURN_STATUS <> CST_UTILITY_PUB.GET_RET_STS_SUCCESS THEN
138 RAISE L_CST_INV_VAL;
139 END IF;
140 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
141 ,P_COUNT => L_MSG_COUNT
142 ,P_DATA => L_MSG_DATA);
143 IF (L_MSG_COUNT > 0) THEN
144 FOR i IN 1 .. L_MSG_COUNT LOOP
145 L_MSG_DATA := FND_MSG_PUB.GET(I
146 ,CST_UTILITY_PUB.GET_FALSE);
147 FND_FILE.PUT_LINE(CST_UTILITY_PUB.GET_LOG
148 ,I || '-' || L_MSG_DATA);
149 END LOOP;
150 END IF;
151 SELECT
152 TO_CHAR(TO_DATE(P_AS_OF_DATE
153 ,'YYYY/MM/DD HH24:MI:SS')
154 ,'DD-MON-YYYY HH24:MI:SS')
155 INTO L_AS_OF_DATE
156 FROM
157 DUAL;
158 P_AS_OF_DATE1 := L_AS_OF_DATE;
159 /*SRW.MESSAGE(0
160 ,'BOM_CSTREIVR_XMLP_PKG << ' || TO_CHAR(SYSDATE
161 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
162 EXCEPTION
163 WHEN OTHERS THEN
164 /*SRW.MESSAGE(999
165 ,SQL_STMT_NUM || SQLERRM)*/NULL;
166 /*SRW.MESSAGE(999
167 ,'BOM_CSTREIVR_XMLP_PKG >X ' || TO_CHAR(SYSDATE
168 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
169 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
170 ,P_COUNT => L_MSG_COUNT
171 ,P_DATA => L_MSG_DATA);
172 IF (L_MSG_COUNT > 0) THEN
173 FOR i IN 1 .. L_MSG_COUNT LOOP
174 L_MSG_DATA := FND_MSG_PUB.GET(I
175 ,CST_UTILITY_PUB.GET_FALSE);
176 /*SRW.MESSAGE(999
177 ,I || ': ' || L_MSG_DATA)*/NULL;
178 END LOOP;
179 END IF;
180 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
181 END;
182 RETURN (TRUE);
183 END BEFOREREPORT;
184 FUNCTION AFTERREPORT RETURN BOOLEAN IS
185 BEGIN
186 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
187 EXECUTE IMMEDIATE
188 'ROLLBACK';
189 /*SRW.MESSAGE(0
190 ,'BOM_CSTREIVR_XMLP_PKG >> ' || TO_CHAR(SYSDATE
191 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
192 RETURN (TRUE);
193 END AFTERREPORT;
194 FUNCTION AFTERPFORM RETURN BOOLEAN IS
195 BEGIN
196 BEGIN
197 IF (P_ZERO_QTY = 1 AND P_NEG_QTY = 1) THEN
198 P_QTY_WHERE := 'HAVING SUM(MOH.TRANSACTION_QUANTITY) <= 0';
199 ELSIF (P_ZERO_QTY = 1 AND P_NEG_QTY = 2) THEN
200 P_QTY_WHERE := 'HAVING SUM(MOH.TRANSACTION_QUANTITY) >= 0';
201 ELSIF (P_ZERO_QTY = 2 AND P_NEG_QTY = 1) THEN
202 P_QTY_WHERE := 'HAVING SUM(MOH.TRANSACTION_QUANTITY) < 0';
203 ELSE
204 P_QTY_WHERE := 'HAVING SUM(MOH.TRANSACTION_QUANTITY) <> 0';
205 END IF;
206 END;
207 RETURN (TRUE);
208 END AFTERPFORM;
209 FUNCTION IC_MATL1FORMULA(IC_QUANTITY IN NUMBER
210 ,IC_MATL IN NUMBER) RETURN NUMBER IS
211 BEGIN
212 IF P_NEG_QTY = 1 AND IC_QUANTITY > 0 THEN
213 RETURN (0);
214 ELSE
215 RETURN (IC_MATL);
216 END IF;
217 RETURN NULL;
218 END IC_MATL1FORMULA;
219 FUNCTION IC_MOVH1FORMULA(IC_QUANTITY IN NUMBER
220 ,IC_MOVH IN NUMBER) RETURN NUMBER IS
221 BEGIN
222 IF P_NEG_QTY = 1 AND IC_QUANTITY > 0 THEN
223 RETURN (0);
224 ELSE
225 RETURN (IC_MOVH);
226 END IF;
227 RETURN NULL;
228 END IC_MOVH1FORMULA;
229 FUNCTION IC_RES1FORMULA(IC_QUANTITY IN NUMBER
230 ,IC_RES IN NUMBER) RETURN NUMBER IS
231 BEGIN
232 IF P_NEG_QTY = 1 AND IC_QUANTITY > 0 THEN
233 RETURN (0);
234 ELSE
235 RETURN (IC_RES);
236 END IF;
237 RETURN NULL;
238 END IC_RES1FORMULA;
239 FUNCTION IC_OSP1FORMULA(IC_QUANTITY IN NUMBER
240 ,IC_OSP IN NUMBER) RETURN NUMBER IS
241 BEGIN
242 IF P_NEG_QTY = 1 AND IC_QUANTITY > 0 THEN
243 RETURN (0);
244 ELSE
245 RETURN (IC_OSP);
246 END IF;
247 RETURN NULL;
248 END IC_OSP1FORMULA;
249 FUNCTION IC_OVHD1FORMULA(IC_QUANTITY IN NUMBER
250 ,IC_OVHD IN NUMBER) RETURN NUMBER IS
251 BEGIN
252 IF P_NEG_QTY = 1 AND IC_QUANTITY > 0 THEN
253 RETURN (0);
254 ELSE
255 RETURN (IC_OVHD);
256 END IF;
257 RETURN NULL;
258 END IC_OVHD1FORMULA;
259 FUNCTION IC_TOTAL1FORMULA(IC_QUANTITY IN NUMBER
260 ,IC_TOTAL IN NUMBER) RETURN NUMBER IS
261 BEGIN
262 IF P_NEG_QTY = 1 AND IC_QUANTITY > 0 THEN
263 RETURN (0);
264 ELSE
265 RETURN (IC_TOTAL);
266 END IF;
267 RETURN NULL;
268 END IC_TOTAL1FORMULA;
269 FUNCTION SI_MATL1FORMULA(SI_QUANTITY IN NUMBER
270 ,SI_MATL IN NUMBER) RETURN NUMBER IS
271 BEGIN
272 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
273 RETURN (0);
274 ELSE
275 RETURN (SI_MATL);
276 END IF;
277 RETURN NULL;
278 END SI_MATL1FORMULA;
279 FUNCTION SI_MOVH1FORMULA(SI_QUANTITY IN NUMBER
280 ,SI_MOVH IN NUMBER) RETURN NUMBER IS
281 BEGIN
282 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
283 RETURN (0);
284 ELSE
285 RETURN (SI_MOVH);
286 END IF;
287 RETURN NULL;
288 END SI_MOVH1FORMULA;
289 FUNCTION SI_RES1FORMULA(SI_QUANTITY IN NUMBER
290 ,SI_RES IN NUMBER) RETURN NUMBER IS
291 BEGIN
292 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
293 RETURN (0);
294 ELSE
295 RETURN (SI_RES);
296 END IF;
297 RETURN NULL;
298 END SI_RES1FORMULA;
299 FUNCTION SI_OSP1FORMULA(SI_QUANTITY IN NUMBER
300 ,SI_OSP IN NUMBER) RETURN NUMBER IS
301 BEGIN
302 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
303 RETURN (0);
304 ELSE
305 RETURN (SI_OSP);
306 END IF;
307 RETURN NULL;
308 END SI_OSP1FORMULA;
309 FUNCTION SI_OVHD1FORMULA(SI_QUANTITY IN NUMBER
310 ,SI_OVHD IN NUMBER) RETURN NUMBER IS
311 BEGIN
312 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
313 RETURN (0);
314 ELSE
315 RETURN (SI_OVHD);
316 END IF;
317 RETURN NULL;
318 END SI_OVHD1FORMULA;
319 FUNCTION SI_TOTAL1FORMULA(SI_QUANTITY IN NUMBER
320 ,SI_TOTAL IN NUMBER) RETURN NUMBER IS
321 BEGIN
322 IF P_NEG_QTY = 1 AND SI_QUANTITY > 0 THEN
323 RETURN (0);
324 ELSE
325 RETURN (SI_TOTAL);
326 END IF;
327 RETURN NULL;
328 END SI_TOTAL1FORMULA;
329 FUNCTION IC_QUANTITY1FORMULA(IC_QUANTITY IN NUMBER) RETURN NUMBER IS
330 BEGIN
331 IF P_NEG_QTY = 1 AND IC_QUANTITY > 0 THEN
332 RETURN (NULL);
333 ELSE
334 RETURN (IC_QUANTITY);
335 END IF;
336 RETURN NULL;
337 END IC_QUANTITY1FORMULA;
338 FUNCTION IC_ROWCOUNTFORMULA(IC_QUANTITY IN NUMBER) RETURN NUMBER IS
339 BEGIN
340 IF P_ZERO_QTY = 1 AND P_NEG_QTY = 1 AND IC_QUANTITY <= 0 THEN
341 RETURN (1);
342 ELSIF P_ZERO_QTY = 1 AND P_NEG_QTY = 2 THEN
343 RETURN (2);
344 ELSIF P_ZERO_QTY = 2 AND P_NEG_QTY = 1 AND IC_QUANTITY < 0 THEN
345 RETURN (3);
346 ELSIF P_ZERO_QTY = 2 AND P_NEG_QTY = 2 AND IC_QUANTITY <> 0 THEN
347 RETURN (4);
348 ELSE
349 RETURN (0);
350 END IF;
351 RETURN NULL;
352 END IC_ROWCOUNTFORMULA;
353 FUNCTION SI_ROWCOUNTFORMULA(SI_QUANTITY IN NUMBER) RETURN NUMBER IS
354 BEGIN
355 IF P_ZERO_QTY = 1 AND P_NEG_QTY = 1 AND SI_QUANTITY <= 0 THEN
356 RETURN (1);
357 ELSIF P_ZERO_QTY = 1 AND P_NEG_QTY = 2 THEN
358 RETURN (1);
359 ELSIF P_ZERO_QTY = 2 AND P_NEG_QTY = 1 AND SI_QUANTITY < 0 THEN
360 RETURN (1);
361 ELSIF P_ZERO_QTY = 2 AND P_NEG_QTY = 2 AND SI_QUANTITY <> 0 THEN
362 RETURN (1);
363 ELSE
364 RETURN (0);
365 END IF;
366 RETURN NULL;
367 END SI_ROWCOUNTFORMULA;
368 FUNCTION IC_ORDERFORMULA(IC_ITEM_NUMBER IN VARCHAR2
369 ,IC_CATEGORY IN VARCHAR2
370 ,IC_ITEM_SEGMENT IN VARCHAR2
371 ,IC_CATEGORY_SEGMENT IN VARCHAR2
372 ,IC_ITEM_PSEG IN VARCHAR2
373 ,IC_CAT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
374 BEGIN
375 /*SRW.REFERENCE(IC_ITEM_NUMBER)*/NULL;
376 /*SRW.REFERENCE(IC_CATEGORY)*/NULL;
377 /*SRW.REFERENCE(IC_ITEM_SEGMENT)*/NULL;
378 /*SRW.REFERENCE(IC_CATEGORY_SEGMENT)*/NULL;
379 /*SRW.REFERENCE(IC_ITEM_PSEG)*/NULL;
380 /*SRW.REFERENCE(IC_CAT_PSEG)*/NULL;
381 IF P_SORT_OPTION = 1 THEN
382 RETURN (IC_ITEM_PSEG);
383 ELSE
384 RETURN (IC_CAT_PSEG);
385 END IF;
386 RETURN NULL;
387 END IC_ORDERFORMULA;
388 FUNCTION IC_ITEM_PSEGFORMULA(IC_ITEM_SEGMENT IN VARCHAR2
389 ,IC_ITEM_NUMBER IN VARCHAR2
390 ,IC_ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
391 BEGIN
392 /*SRW.REFERENCE(IC_ITEM_SEGMENT)*/NULL;
393 /*SRW.REFERENCE(IC_ITEM_NUMBER)*/NULL;
394 RETURN (IC_ITEM_PSEG);
395 END IC_ITEM_PSEGFORMULA;
396 FUNCTION IC_CAT_PSEGFORMULA(IC_CATEGORY_SEGMENT IN VARCHAR2
397 ,IC_CATEGORY IN VARCHAR2
398 ,IC_CAT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
399 BEGIN
400 /*SRW.REFERENCE(IC_CATEGORY_SEGMENT)*/NULL;
401 /*SRW.REFERENCE(IC_CATEGORY)*/NULL;
402 RETURN (IC_CAT_PSEG);
403 END IC_CAT_PSEGFORMULA;
404 FUNCTION SI_ITEM_PSEGFORMULA(SI_ITEM_NUMBER IN VARCHAR2
405 ,SI_ITEM_SEGMENT IN VARCHAR2
406 ,SI_ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
407 BEGIN
408 /*SRW.REFERENCE(SI_ITEM_NUMBER)*/NULL;
409 /*SRW.REFERENCE(SI_ITEM_SEGMENT)*/NULL;
410 RETURN (SI_ITEM_PSEG);
411 END SI_ITEM_PSEGFORMULA;
412 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
413 BEGIN
414 RETURN (TRUE);
415 END BEFOREPFORM;
416 END BOM_CSTREIVR_XMLP_PKG;
417