[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRITVR_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRITVR_XMLP_PKG AS
2 /* $Header: CSTRITVRB.pls 120.0 2007/12/24 10:02:52 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
6 /*SRW.MESSAGE(1
7 ,'BOM_CSTRITVR_XMLP_PKG >> ' || TO_CHAR(SYSDATE
8 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
9 RETURN (TRUE);
10 END AFTERREPORT;
11 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
12 L_FCN_CURRENCY VARCHAR2(15);
13 L_STMT_NUM NUMBER;
14 L_MSG_COUNT NUMBER;
15 L_MSG_DATA VARCHAR2(8000);
16 L_RETURN_STATUS VARCHAR2(1);
17 L_AS_OF_DATE VARCHAR2(30);
18 L_CST_INV_VAL EXCEPTION;
19 BEGIN
20 L_STMT_NUM := 0;
21 P_EXCHANGE_RATE1:=P_EXCHANGE_RATE;
22 P_EXCHANGE_RATE1 := FND_NUMBER.CANONICAL_TO_NUMBER(P_EXCHANGE_RATE_CHAR);
23 L_STMT_NUM := 1;
24 P_EXP_ITEM:=nvl(P_EXP_ITEM,2);
25 P_ZERO_COST:=nvl(P_ZERO_COST,2);
26 IF P_VIEW_COST <> 1 THEN
27 FND_MESSAGE.SET_NAME('null'
28 ,'null');
29 /*SRW.USER_EXIT('FND MESSAGE_DISPLAY')*/NULL;
30 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
31 END IF;
32 L_STMT_NUM := 2;
33 FND_PROFILE.GET('CURRENCY_CONVERSION_TYPE'
34 ,P_CURR_TYPE);
35 L_STMT_NUM := 10;
36 L_STMT_NUM := 20;
37 IF P_COST_TYPE_DUMMY = 1 THEN
38 P_COST_TYPE_DUMMY1 := 1;
39 ELSE
40 P_COST_TYPE_DUMMY1 := 0;
41 END IF;
42 L_STMT_NUM := 40;
43 SELECT
44 O.ORGANIZATION_NAME,
45 NVL(EXTENDED_PRECISION
46 ,PRECISION),
47 NVL(MINIMUM_ACCOUNTABLE_UNIT
48 ,POWER(10
49 ,NVL(-PRECISION
50 ,0))),
51 MCS.CATEGORY_SET_NAME,
52 DEFAULT_COST_TYPE_ID,
53 COST_TYPE,
54 LU1.MEANING,
55 LU2.MEANING
56 INTO P_ORGANIZATION,P_EXT_PREC,ROUND_UNIT,P_CAT_SET_NAME,P_DEF_COST_TYPE,P_COST_TYPE,P_SORT_BY,P_DETAIL_LEVEL
57 FROM
58 ORG_ORGANIZATION_DEFINITIONS O,
59 FND_CURRENCIES FC,
60 MTL_CATEGORY_SETS MCS,
61 CST_COST_TYPES,
62 MTL_PARAMETERS MP,
63 MFG_LOOKUPS LU1,
64 MFG_LOOKUPS LU2
65 WHERE FC.CURRENCY_CODE = P_CURRENCY_CODE
66 AND O.ORGANIZATION_ID = P_ORG_ID
67 AND MCS.CATEGORY_SET_ID = P_CATEGORY_SET
68 AND COST_TYPE_ID = NVL(P_COST_TYPE_ID
69 ,MP.PRIMARY_COST_METHOD)
70 AND MP.ORGANIZATION_ID = P_ORG_ID
71 AND LU1.LOOKUP_TYPE (+) = 'CST_ITEM_REPORT_SORT'
72 AND LU1.LOOKUP_CODE (+) = P_SORT_OPTION
73 AND LU2.LOOKUP_TYPE (+) = 'CST_BICR_DETAIL_OPTION'
74 AND LU2.LOOKUP_CODE (+) = P_RPT_OPTION;
75 L_STMT_NUM := 50;
76 SELECT
77 SOB.CURRENCY_CODE
78 INTO L_FCN_CURRENCY
79 FROM
80 GL_SETS_OF_BOOKS SOB,
81 ORG_ORGANIZATION_DEFINITIONS OOD
82 WHERE OOD.ORGANIZATION_ID = P_ORG_ID
83 AND SOB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID;
84 L_STMT_NUM := 60;
85 IF L_FCN_CURRENCY = P_CURRENCY_CODE THEN
86 P_CURRENCY_DSP := P_CURRENCY_CODE;
87 ELSE
88 P_CURRENCY_DSP := P_CURRENCY_CODE || ' @ ' || TO_CHAR(ROUND(1 / P_EXCHANGE_RATE1
89 ,5)) || L_FCN_CURRENCY;
90 END IF;
91 L_STMT_NUM := 70;
92 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
93 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
94 L_STMT_NUM := 80;
95 L_STMT_NUM := 90;
96 L_STMT_NUM := 100;
97 CST_INVENTORY_PUB.CALCULATE_INVENTORYVALUE(P_API_VERSION => 1.0
98 ,P_INIT_MSG_LIST => CST_UTILITY_PUB.GET_TRUE
99 ,P_ORGANIZATION_ID => P_ORG_ID
100 ,P_ONHAND_VALUE => 0
101 ,P_INTRANSIT_VALUE => 1
102 ,P_RECEIVING_VALUE => 0
103 ,P_VALUATION_DATE => TO_DATE(P_AS_OF_DATE
104 ,'YYYY/MM/DD HH24:MI:SS')
105 ,P_COST_TYPE_ID => P_COST_TYPE_ID
106 ,P_ITEM_FROM => P_ITEM_FROM
107 ,P_ITEM_TO => P_ITEM_TO
108 ,P_CATEGORY_SET_ID => P_CATEGORY_SET
109 ,P_CATEGORY_FROM => P_CAT_FROM
110 ,P_CATEGORY_TO => P_CAT_TO
111 ,P_COST_GROUP_FROM => NULL
112 ,P_COST_GROUP_TO => NULL
113 ,P_SUBINVENTORY_FROM => NULL
114 ,P_SUBINVENTORY_TO => NULL
115 ,P_QTY_BY_REVISION => P_ITEM_REVISION
116 ,P_ZERO_COST_ONLY => P_ZERO_COST
117 ,P_ZERO_QTY => NULL
118 ,P_EXPENSE_ITEM => P_EXP_ITEM
119 ,P_EXPENSE_SUB => NULL
120 ,P_UNVALUED_TXNS => NULL
121 ,P_RECEIPT => P_RECEIPT
122 ,P_SHIPMENT => P_SHIPMENT
123 ,P_OWN => P_OWN
124 ,P_DETAIL => 1
125 ,X_RETURN_STATUS => L_RETURN_STATUS
126 ,X_MSG_COUNT => L_MSG_COUNT
127 ,X_MSG_DATA => L_MSG_DATA);
128 L_STMT_NUM := 110;
129 IF L_RETURN_STATUS <> CST_UTILITY_PUB.GET_RET_STS_SUCCESS THEN
130 RAISE L_CST_INV_VAL;
131 END IF;
132 L_STMT_NUM := 120;
133 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
134 ,P_COUNT => L_MSG_COUNT
135 ,P_DATA => L_MSG_DATA);
136 L_STMT_NUM := 130;
137 IF L_MSG_COUNT > 0 THEN
138 FOR i IN 1 .. L_MSG_COUNT LOOP
139 L_MSG_DATA := FND_MSG_PUB.GET(I
140 ,CST_UTILITY_PUB.GET_FALSE);
141 FND_FILE.PUT_LINE(CST_UTILITY_PUB.GET_LOG
142 ,I || '-' || L_MSG_DATA);
143 END LOOP;
144 END IF;
145 SELECT
146 TO_CHAR(TO_DATE(P_AS_OF_DATE
147 ,'YYYY/MM/DD HH24:MI:SS')
148 ,'DD-MON-YYYY HH24:MI:SS')
149 INTO L_AS_OF_DATE
150 FROM
151 DUAL;
152 P_AS_OF_DATE1 := L_AS_OF_DATE;
153 /*SRW.MESSAGE(0
154 ,'BOM_CSTRITVR_XMLP_PKG << ' || TO_CHAR(SYSDATE
155 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
156 RETURN TRUE;
157 EXCEPTION
158 WHEN OTHERS THEN
159 /*SRW.MESSAGE(999
160 ,L_STMT_NUM || ': ' || SQLERRM)*/NULL;
161 /*SRW.MESSAGE(999
162 ,'CSTRAIVW >X ' || TO_CHAR(SYSDATE
163 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
164 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => CST_UTILITY_PUB.GET_FALSE
165 ,P_COUNT => L_MSG_COUNT
166 ,P_DATA => L_MSG_DATA);
167 IF L_MSG_COUNT > 0 THEN
168 FOR i IN 1 .. L_MSG_COUNT LOOP
169 L_MSG_DATA := FND_MSG_PUB.GET(I
170 ,CST_UTILITY_PUB.GET_FALSE);
171 FND_FILE.PUT_LINE(CST_UTILITY_PUB.GET_LOG
172 ,I || '-' || L_MSG_DATA);
173 END LOOP;
174 END IF;
175 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
176 END BEFOREREPORT;
177 FUNCTION CATEGORY_PSEGFORMULA(CATEGORY IN VARCHAR2
178 ,CATEGORY_SEGMENT IN VARCHAR2
179 ,CATEGORY_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
180 BEGIN
181 /*SRW.REFERENCE(CATEGORY)*/NULL;
182 /*SRW.REFERENCE(CATEGORY_SEGMENT)*/NULL;
183 RETURN (CATEGORY_PSEG);
184 END CATEGORY_PSEGFORMULA;
185 FUNCTION ITEM_PSEGFORMULA(ITEM_NUMBER IN VARCHAR2
186 ,ITEM_SEGMENT IN VARCHAR2
187 ,ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
188 BEGIN
189 /*SRW.REFERENCE(ITEM_NUMBER)*/NULL;
190 /*SRW.REFERENCE(ITEM_SEGMENT)*/NULL;
191 RETURN (ITEM_PSEG);
192 END ITEM_PSEGFORMULA;
193 FUNCTION ITEMCATFORMULA(CATEGORY IN VARCHAR2
194 ,CATEGORY_SEGMENT IN VARCHAR2
195 ,CATEGORY_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
196 BEGIN
197 /*SRW.REFERENCE(CATEGORY)*/NULL;
198 /*SRW.REFERENCE(CATEGORY_SEGMENT)*/NULL;
199 /*SRW.REFERENCE(CATEGORY_PSEG)*/NULL;
200 IF P_SORT_OPTION = 2 THEN
201 RETURN (CATEGORY_PSEG);
202 ELSE
203 RETURN ('I have absolutely no idea why I am doing this');
204 END IF;
205 RETURN NULL;
206 END ITEMCATFORMULA;
207 FUNCTION XFER_COST_TOTALFORMULA(XFER_COST IN NUMBER
208 ,QUANTITY IN NUMBER) RETURN NUMBER IS
209 BEGIN
210 RETURN (XFER_COST * QUANTITY);
211 END XFER_COST_TOTALFORMULA;
212 FUNCTION XPORT_COST_TOTALFORMULA(XPORT_COST IN NUMBER
213 ,QUANTITY IN NUMBER) RETURN NUMBER IS
214 BEGIN
215 RETURN (XPORT_COST * QUANTITY);
216 END XPORT_COST_TOTALFORMULA;
217 FUNCTION ITEM_QTYFORMULA(UOM_CODE IN VARCHAR2
218 ,UOMCODE IN VARCHAR2
219 ,ITEM_ID IN NUMBER
220 ,QUANTITY IN NUMBER) RETURN NUMBER IS
221 QTY NUMBER;
222 BEGIN
223 IF UOM_CODE <> UOMCODE THEN
224 /*SRW.REFERENCE(ITEM_ID)*/NULL;
225 /*SRW.REFERENCE(QUANTITY)*/NULL;
226 QTY := INV_CONVERT.INV_UM_CONVERT(ITEM_ID
227 ,P_QTY_PRECISION
228 ,QUANTITY
229 ,UOMCODE
230 ,UOM_CODE
231 ,NULL
232 ,NULL);
233 RETURN (QTY);
234 ELSE
235 RETURN QUANTITY;
236 END IF;
237 RETURN NULL;
238 END ITEM_QTYFORMULA;
239 FUNCTION TOT_COSTFORMULA(OWNING_ORG_ID IN NUMBER
240 ,FROM_ORG_ID IN NUMBER
241 ,OWNING_ORG_PUOM_CODE IN VARCHAR2
242 ,UOMCODE IN VARCHAR2
243 ,ITEM_ID IN NUMBER
244 ,QUANTITY IN NUMBER
245 ,UNIT_COST IN NUMBER
246 ,TOTAL_COST IN NUMBER) RETURN NUMBER IS
247 QTY NUMBER;
248 BEGIN
249 IF OWNING_ORG_ID = FROM_ORG_ID THEN
250 IF OWNING_ORG_PUOM_CODE <> UOMCODE THEN
251 /*SRW.REFERENCE(ITEM_ID)*/NULL;
252 /*SRW.REFERENCE(QUANTITY)*/NULL;
253 QTY := INV_CONVERT.INV_UM_CONVERT(ITEM_ID
254 ,P_QTY_PRECISION
255 ,QUANTITY
256 ,UOMCODE
257 ,OWNING_ORG_PUOM_CODE
258 ,NULL
259 ,NULL);
260 ELSE
261 /*SRW.REFERENCE(QUANTITY)*/NULL;
262 QTY := QUANTITY;
263 END IF;
264 RETURN (QTY * UNIT_COST);
265 ELSE
266 RETURN TOTAL_COST;
267 END IF;
268 RETURN NULL;
269 END TOT_COSTFORMULA;
270 END BOM_CSTRITVR_XMLP_PKG;
271
272