[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRPICR_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRPICR_XMLP_PKG AS
2 /* $Header: CSTRPICRB.pls 120.1.12020000.2 2013/01/22 07:50:14 vnagasow ship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
6 EXECUTE IMMEDIATE
7 'ROLLBACK';
8 /*SRW.MESSAGE(0
12 END AFTERREPORT;
9 ,'BOM_CSTRPICR_XMLP_PKG >> ' || TO_CHAR(SYSDATE
10 ,'Dy Mon DD HH24:MI:SS YYYY'))*/NULL;
11 RETURN (TRUE);
13 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
14 BEGIN
15 DECLARE
16 L_FCN_CURRENCY VARCHAR2(15);
17 L_REPORT_NAME VARCHAR2(80);
18 SQL_STMT_NUM VARCHAR2(5);
19 BEGIN
20 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS=''.,''';
21 SQL_STMT_NUM := '0: ';
22 P_EXCHANGE_RATE := FND_NUMBER.CANONICAL_TO_NUMBER(P_EXCHANGE_RATE_CHAR);
23 SQL_STMT_NUM := '1: ';
24 IF P_VIEW_COST <> 1 THEN
25 FND_MESSAGE.SET_NAME('null'
26 ,'null');
27 /*SRW.USER_EXIT('FND MESSAGE_DISPLAY')*/NULL;
28 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
29 END IF;
30 SQL_STMT_NUM := '3: ';
31 SQL_STMT_NUM := '4: ';
32 SELECT
33 XFI.NAME,
34 GLL.CURRENCY_CODE
35 INTO P_LEGAL_ENTITY,L_FCN_CURRENCY
36 FROM
37 XLE_FIRSTPARTY_INFORMATION_V XFI,
38 GL_LEDGER_LE_V GLL
39 WHERE XFI.LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
40 AND GLL.LEGAL_ENTITY_ID = XFI.LEGAL_ENTITY_ID
41 AND GLL.RELATIONSHIP_ENABLED_FLAG = 'Y'
42 AND GLL.LEDGER_CATEGORY_CODE = 'PRIMARY';
43 SQL_STMT_NUM := '5: ';
44 SELECT
45 NVL(FC.EXTENDED_PRECISION
46 ,PRECISION),
47 NVL(FC.PRECISION
48 ,0),
49 NVL(MINIMUM_ACCOUNTABLE_UNIT
50 ,POWER(10
51 ,NVL(-PRECISION
52 ,0)))
53 INTO P_EXT_PREC,P_PRECISION,ROUND_UNIT
54 FROM
55 FND_CURRENCIES FC
56 WHERE FC.CURRENCY_CODE = P_CURRENCY_CODE;
57 SQL_STMT_NUM := '6: ';
58 SELECT
59 MCS.CATEGORY_SET_NAME
60 INTO P_CAT_SET_NAME
61 FROM
62 MTL_CATEGORY_SETS MCS
63 WHERE MCS.CATEGORY_SET_ID = P_CATEGORY_SET;
64 SQL_STMT_NUM := '7: ';
65 SELECT
66 LU.MEANING
67 INTO P_DETAIL_LEVEL
68 FROM
69 MFG_LOOKUPS LU
70 WHERE LU.LOOKUP_TYPE = 'CST_BICR_DETAIL_OPTION'
71 AND LU.LOOKUP_CODE = P_RPT_OPTION;
72 SQL_STMT_NUM := '8: ';
73 SELECT
74 USERENV('SESSIONID')
75 INTO P_SESSIONID
76 FROM
77 DUAL;
78 SQL_STMT_NUM := '9: ';
79 SELECT
80 CT.COST_TYPE
81 INTO P_COST_TYPE
82 FROM
83 CST_COST_TYPES CT
84 WHERE CT.COST_TYPE_ID = P_COST_TYPE_ID;
85 SQL_STMT_NUM := '10: ';
86 SELECT
87 CCG.COST_GROUP
88 INTO P_COST_GROUP
89 FROM
90 CST_COST_GROUPS CCG
91 WHERE CCG.COST_GROUP_ID = P_COST_GROUP_ID
92 AND CCG.COST_GROUP_TYPE = 2;
93 SQL_STMT_NUM := '12: ';
94 SELECT
95 PERIOD_NAME
96 INTO P_PERIOD_NAME
97 FROM
98 CST_PAC_PERIODS
99 WHERE PAC_PERIOD_ID = P_PERIOD_ID
100 AND LEGAL_ENTITY = P_LEGAL_ENTITY_ID
101 AND COST_TYPE_ID = P_COST_TYPE_ID;
102 SQL_STMT_NUM := '13: ';
103 SELECT
104 MEANING
105 INTO P_SORT_BY
106 FROM
107 MFG_LOOKUPS
108 WHERE LOOKUP_TYPE = 'CST_ITEM_REPORT_SORT'
109 AND LOOKUP_CODE = P_SORT_OPTION;
110 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
111 IF P_CONC_REQUEST_ID > 0 THEN
112 SELECT
113 USER_CONCURRENT_PROGRAM_NAME
114 INTO P_REPORT_NAME
115 FROM
116 FND_CONCURRENT_PROGRAMS_TL
117 WHERE LANGUAGE = USERENV('LANG')
118 AND CONCURRENT_PROGRAM_ID IN (
119 SELECT
120 P.CONCURRENT_PROGRAM_ID
121 FROM
122 FND_CONCURRENT_PROGRAMS P,
123 FND_CONCURRENT_REQUESTS R
124 WHERE R.REQUEST_ID = P_CONC_REQUEST_ID
125 AND P.APPLICATION_ID = R.PROGRAM_APPLICATION_ID
126 AND P.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID );
127 ELSE
128 SELECT
129 USER_CONCURRENT_PROGRAM_NAME
130 INTO P_REPORT_NAME
131 FROM
132 FND_CONCURRENT_PROGRAMS_TL
133 WHERE LANGUAGE = USERENV('LANG')
134 AND CONCURRENT_PROGRAM_ID IN (
135 SELECT
136 CONCURRENT_PROGRAM_ID
137 FROM
138 FND_CONCURRENT_PROGRAMS P
139 WHERE P.APPLICATION_ID = 702
140 AND P.CONCURRENT_PROGRAM_NAME = 'BOM_CSTRPICR_XMLP_PKG' );
141 END IF;
142 SQL_STMT_NUM := '14: ';
143 IF L_FCN_CURRENCY = P_CURRENCY_CODE THEN
144 P_CURRENCY_DSP := P_CURRENCY_CODE;
145 ELSE
146 P_CURRENCY_DSP := P_CURRENCY_CODE || ' @ ' || TO_CHAR(ROUND(1 / P_EXCHANGE_RATE
147 ,5)) || L_FCN_CURRENCY;
148 END IF;
149 SQL_STMT_NUM := '15: ';
150 BEGIN
151
152 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
153 EXCEPTION
154 WHEN OTHERS THEN
155 /*SRW.MESSAGE(999
156 ,'FND SRWINIT >X')*/NULL;
157 RAISE;
158 END;
159 BEGIN
160 NULL;
161 EXCEPTION
162 WHEN OTHERS THEN
163 /*SRW.MESSAGE(999
164 ,'FND FLEXSQL(MCAT) >X')*/NULL;
165 RAISE;
166 END;
167 BEGIN
168 NULL;
169 EXCEPTION
170 WHEN OTHERS THEN
171 /*SRW.MESSAGE(999
175 /*SRW.MESSAGE(0
172 ,'FND FLEXSQL(MSTK) >X')*/NULL;
173 RAISE;
174 END;
176 ,'BOM_CSTRPICR_XMLP_PKG << ' || TO_CHAR(SYSDATE
177 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
178 EXCEPTION
179 WHEN OTHERS THEN
180 /*SRW.MESSAGE(999
181 ,SQL_STMT_NUM || SQLERRM)*/NULL;
182 /*SRW.MESSAGE(999
183 ,'BOM_CSTRPICR_XMLP_PKG >X ' || TO_CHAR(SYSDATE
184 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
185 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
186 END;
187 RETURN (TRUE);
188 END BEFOREREPORT;
189 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
190 BEGIN
191 RETURN (TRUE);
192 END BEFOREPFORM;
193 FUNCTION IC_REP_TOTAL_RFORMULA(IC_REP_TOTAL IN NUMBER) RETURN NUMBER IS
194 IC_REP_TOTAL_R NUMBER;
195 BEGIN
196 IC_REP_TOTAL_R := ROUND(IC_REP_TOTAL / ROUND_UNIT) * ROUND_UNIT;
197 RETURN IC_REP_TOTAL_R;
198 END IC_REP_TOTAL_RFORMULA;
199 FUNCTION IC_ORDERFORMULA(IC_CATEGORY IN VARCHAR2
200 ,IC_CATEGORY_SEGMENT IN VARCHAR2
201 ,IC_CAT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
202 BEGIN
203 /*SRW.REFERENCE(IC_CATEGORY)*/NULL;
204 /*SRW.REFERENCE(IC_CATEGORY_SEGMENT)*/NULL;
205 /*SRW.REFERENCE(IC_CAT_PSEG)*/NULL;
206 IF P_SORT_OPTION = 2 THEN
207 RETURN (IC_CAT_PSEG);
208 ELSE
209 RETURN ('Item Sort');
210 END IF;
211 RETURN NULL;
212 END IC_ORDERFORMULA;
213 FUNCTION IC_CAT_TOTAL_RFORMULA(IC_CAT_TOTAL IN NUMBER) RETURN NUMBER IS
214 IC_CAT_TOTAL_R NUMBER;
215 BEGIN
216 IC_CAT_TOTAL_R := ROUND(IC_CAT_TOTAL / ROUND_UNIT) * ROUND_UNIT;
217 RETURN IC_CAT_TOTAL_R;
218 END IC_CAT_TOTAL_RFORMULA;
219 FUNCTION IC_ITEM_PSEGFORMULA(IC_ITEM_NUMBER IN VARCHAR2
220 ,IC_ITEM_SEGMENT IN VARCHAR2
221 ,IC_ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
222 BEGIN
223 /*SRW.REFERENCE(IC_ITEM_NUMBER)*/NULL;
224 /*SRW.REFERENCE(IC_ITEM_SEGMENT)*/NULL;
225 RETURN (IC_ITEM_PSEG);
226 END IC_ITEM_PSEGFORMULA;
227 FUNCTION IC_CAT_PSEGFORMULA(IC_CATEGORY IN VARCHAR2
228 ,IC_CATEGORY_SEGMENT IN VARCHAR2
229 ,IC_CAT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
230 BEGIN
231 /*SRW.REFERENCE(IC_CATEGORY)*/NULL;
232 /*SRW.REFERENCE(IC_CATEGORY_SEGMENT)*/NULL;
233 RETURN (IC_CAT_PSEG);
234 END IC_CAT_PSEGFORMULA;
235 FUNCTION IC_TOTAL_COST_RFORMULA(IC_TOTAL_COST IN NUMBER) RETURN NUMBER IS
236 IC_TOTAL_COST_R NUMBER;
237 BEGIN
238 IC_TOTAL_COST_R := STANDARD.ROUND(IC_TOTAL_COST / ROUND_UNIT
239 ,0) * ROUND_UNIT;
240 RETURN IC_TOTAL_COST_R;
241 END IC_TOTAL_COST_RFORMULA;
242 FUNCTION IC_MATL_COST_RFORMULA(IC_MATL_COST IN NUMBER) RETURN NUMBER IS
243 IC_MATL_COST_R NUMBER;
244 BEGIN
245 IC_MATL_COST_R := ROUND(IC_MATL_COST / ROUND_UNIT) * ROUND_UNIT;
246 RETURN IC_MATL_COST_R;
247 END IC_MATL_COST_RFORMULA;
248 FUNCTION IC_MO_COST_RFORMULA(IC_MO_COST IN NUMBER) RETURN NUMBER IS
249 IC_MO_COST_R NUMBER;
250 BEGIN
251 IC_MO_COST_R := ROUND(IC_MO_COST / ROUND_UNIT) * ROUND_UNIT;
252 RETURN IC_MO_COST_R;
253 END IC_MO_COST_RFORMULA;
254 FUNCTION IC_RES_COST_RFORMULA(IC_RES_COST IN NUMBER) RETURN NUMBER IS
255 IC_RES_COST_R NUMBER;
256 BEGIN
257 IC_RES_COST_R := ROUND(IC_RES_COST / ROUND_UNIT) * ROUND_UNIT;
258 RETURN IC_RES_COST_R;
259 END IC_RES_COST_RFORMULA;
260 FUNCTION IC_OVHD_COST_RFORMULA(IC_OVHD_COST IN NUMBER) RETURN NUMBER IS
261 IC_OVHD_COST_R NUMBER;
262 BEGIN
263 IC_OVHD_COST_R := ROUND(IC_OVHD_COST / ROUND_UNIT) * ROUND_UNIT;
264 RETURN IC_OVHD_COST_R;
265 END IC_OVHD_COST_RFORMULA;
266 FUNCTION IC_OSP_COST_RFORMULA(IC_OSP_COST IN NUMBER) RETURN NUMBER IS
267 IC_OSP_COST_R NUMBER;
268 BEGIN
269 IC_OSP_COST_R := ROUND(IC_OSP_COST / ROUND_UNIT) * ROUND_UNIT;
270 RETURN IC_OSP_COST_R;
271 END IC_OSP_COST_RFORMULA;
272 FUNCTION IC_TOT_MATL_COST_RFORMULA(IC_TOT_MATL_COST IN NUMBER) RETURN NUMBER IS
273 IC_TOT_MATL_COST_R NUMBER;
274 BEGIN
275 IC_TOT_MATL_COST_R := ROUND(IC_TOT_MATL_COST / ROUND_UNIT) * ROUND_UNIT;
276 RETURN IC_TOT_MATL_COST_R;
277 END IC_TOT_MATL_COST_RFORMULA;
278 FUNCTION IC_TOT_MO_COST_RFORMULA(IC_TOT_MO_COST IN NUMBER) RETURN NUMBER IS
279 IC_TOT_MO_COST_R NUMBER;
280 BEGIN
281 IC_TOT_MO_COST_R := ROUND(IC_TOT_MO_COST / ROUND_UNIT) * ROUND_UNIT;
282 RETURN IC_TOT_MO_COST_R;
283 END IC_TOT_MO_COST_RFORMULA;
284 FUNCTION IC_TOT_RES_COST_RFORMULA(IC_TOT_RES_COST IN NUMBER) RETURN NUMBER IS
285 IC_TOT_RES_COST_R NUMBER;
286 BEGIN
287 IC_TOT_RES_COST_R := ROUND(IC_TOT_RES_COST / ROUND_UNIT) * ROUND_UNIT;
288 RETURN IC_TOT_RES_COST_R;
289 END IC_TOT_RES_COST_RFORMULA;
290 FUNCTION IC_TOT_OVHD_COST_RFORMULA(IC_TOT_OVHD_COST IN NUMBER) RETURN NUMBER IS
291 IC_TOT_OVHD_COST_R NUMBER;
292 BEGIN
293 IC_TOT_OVHD_COST_R := ROUND(IC_TOT_OVHD_COST / ROUND_UNIT) * ROUND_UNIT;
294 RETURN IC_TOT_OVHD_COST_R;
295 END IC_TOT_OVHD_COST_RFORMULA;
296 FUNCTION IC_TOT_OSP_COST_RFORMULA(IC_TOT_OSP_COST IN NUMBER) RETURN NUMBER IS
297 IC_TOT_OSP_COST_R NUMBER;
298 BEGIN
299 IC_TOT_OSP_COST_R := ROUND(IC_TOT_OSP_COST / ROUND_UNIT) * ROUND_UNIT;
300 RETURN IC_TOT_OSP_COST_R;
301 END IC_TOT_OSP_COST_RFORMULA;
302 FUNCTION P_LEGAL_ENTITYVALIDTRIGGER RETURN BOOLEAN IS
303 BEGIN
304 RETURN (TRUE);
305 END P_LEGAL_ENTITYVALIDTRIGGER;
306 FUNCTION P_CAT_NUMVALIDTRIGGER RETURN BOOLEAN IS
307 BEGIN
308 RETURN (TRUE);
309 END P_CAT_NUMVALIDTRIGGER;
310 FUNCTION P_DETAIL_LEVELVALIDTRIGGER RETURN BOOLEAN IS
311 BEGIN
312 RETURN (TRUE);
313 END P_DETAIL_LEVELVALIDTRIGGER;
314 FUNCTION P_PERIOD_IDVALIDTRIGGER RETURN BOOLEAN IS
315 BEGIN
316 RETURN (TRUE);
317 END P_PERIOD_IDVALIDTRIGGER;
318 FUNCTION P_SORT_BYVALIDTRIGGER RETURN BOOLEAN IS
319 BEGIN
320 RETURN (TRUE);
321 END P_SORT_BYVALIDTRIGGER;
322 FUNCTION CF_RCV_VALUEFORMULA(CS_IC_QUANTITY_TOTAL2 IN NUMBER
323 ,CS_ACQUISITION_COST2 IN NUMBER) RETURN NUMBER IS
324 BEGIN
325 RETURN (NVL(CS_IC_QUANTITY_TOTAL2
326 ,0) * NVL(CS_ACQUISITION_COST2
327 ,0));
328 END CF_RCV_VALUEFORMULA;
329 FUNCTION CF_TOTAL_VALUEFORMULA(CF_RCV_VALUE IN NUMBER
330 ,IC_TOTAL_COST_R IN NUMBER) RETURN NUMBER IS
331 BEGIN
332 RETURN (NVL(CF_RCV_VALUE
333 ,0) + NVL(IC_TOTAL_COST_R
334 ,0));
335 END CF_TOTAL_VALUEFORMULA;
336 FUNCTION IC_ACQUISITION_COST_RFORMULA(IC_ACQUISITION_COST IN NUMBER) RETURN NUMBER IS
337 IC_ACQUISITION_COST_R NUMBER;
338 BEGIN
339 IC_ACQUISITION_COST_R := STANDARD.ROUND(IC_ACQUISITION_COST / ROUND_UNIT
340 ,0) * ROUND_UNIT;
341 RETURN IC_ACQUISITION_COST_R;
342 END IC_ACQUISITION_COST_RFORMULA;
343 FUNCTION IC_VALUE_RFORMULA(IC_VALUE IN NUMBER) RETURN NUMBER IS
344 IC_VALUE_R NUMBER;
345 BEGIN
346 IC_VALUE_R := STANDARD.ROUND(IC_VALUE / ROUND_UNIT
347 ,0) * ROUND_UNIT;
348 RETURN IC_VALUE_R;
349 END IC_VALUE_RFORMULA;
350 FUNCTION CF_IC_VALUE_TOTAL2_RFORMULA(CS_IC_VALUE_TOTAL2 IN NUMBER) RETURN NUMBER IS
351 CF_IC_VALUE_TOTAL2_R NUMBER;
352 BEGIN
353 CF_IC_VALUE_TOTAL2_R := STANDARD.ROUND(CS_IC_VALUE_TOTAL2 / ROUND_UNIT
354 ,0) * ROUND_UNIT;
355 RETURN CF_IC_VALUE_TOTAL2_R;
356 END CF_IC_VALUE_TOTAL2_RFORMULA;
357 FUNCTION CF_ACQUISITION_COST2_RFORMULA(CS_ACQUISITION_COST2 IN NUMBER) RETURN NUMBER IS
358 CF_ACQUISITION_COST2_R NUMBER;
359 BEGIN
360 CF_ACQUISITION_COST2_R := STANDARD.ROUND(CS_ACQUISITION_COST2 / ROUND_UNIT
361 ,0) * ROUND_UNIT;
362 RETURN CF_ACQUISITION_COST2_R;
363 END CF_ACQUISITION_COST2_RFORMULA;
364 FUNCTION CF_RCV_VALUE_RFORMULA(CF_RCV_VALUE IN NUMBER) RETURN NUMBER IS
365 CF_RCV_VALUE_R NUMBER;
366 BEGIN
367 CF_RCV_VALUE_R := STANDARD.ROUND(CF_RCV_VALUE / ROUND_UNIT
368 ,0) * ROUND_UNIT;
369 RETURN CF_RCV_VALUE_R;
370 END CF_RCV_VALUE_RFORMULA;
371 FUNCTION CF_TOTAL_VALUE_RFORMULA(CF_TOTAL_VALUE IN NUMBER) RETURN NUMBER IS
372 CF_TOTAL_VALUE_R NUMBER;
373 BEGIN
374 CF_TOTAL_VALUE_R := STANDARD.ROUND(CF_TOTAL_VALUE / ROUND_UNIT
375 ,0) * ROUND_UNIT;
376 RETURN CF_TOTAL_VALUE_R;
377 END CF_TOTAL_VALUE_RFORMULA;
378 END BOM_CSTRPICR_XMLP_PKG;
379