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