[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRUSJA_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRUSJA_XMLP_PKG AS
2 /* $Header: CSTRUSJAB.pls 120.1 2008/01/02 15:06:30 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 qty_precision:=bom_common_xmlp_pkg.get_precision(P_qty_precision);
7 IF P_RPT_ONLY = 1 THEN
8 SELECT
9 FC.CURRENCY_CODE,
10 NVL(FC.EXTENDED_PRECISION
11 ,FC.PRECISION),
12 NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
13 ,POWER(10
14 ,NVL(-PRECISION
15 ,0))),
16 SOB.CHART_OF_ACCOUNTS_ID,
17 O.ORGANIZATION_NAME ORGANIZATION,
18 CAT.CATEGORY_SET_NAME,
19 CAT.STRUCTURE_ID,
20 LU2.MEANING,
21 CCT.COST_TYPE
22 INTO P_CURRENCY_CODE,EXT_PREC,ROUND_UNIT,P_GL_NUM,P_ORGANIZATION,P_CAT_SET_NAME,P_CAT_NUM,P_ITEM_RANGE,P_COST_TYPE
23 FROM
24 FND_CURRENCIES FC,
25 GL_SETS_OF_BOOKS SOB,
26 ORG_ORGANIZATION_DEFINITIONS O,
27 MTL_CATEGORY_SETS CAT,
28 MTL_DEFAULT_CATEGORY_SETS DCAT,
29 MFG_LOOKUPS LU2,
30 CST_COST_TYPES CCT
31 WHERE O.ORGANIZATION_ID = P_ORG_ID
32 AND O.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
33 AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
34 AND FC.ENABLED_FLAG = 'Y'
35 AND DCAT.FUNCTIONAL_AREA_ID = 5
36 AND CAT.CATEGORY_SET_ID = DECODE(P_RANGE_OPTION
37 ,5
38 ,P_CATEGORY_SET
39 ,DCAT.CATEGORY_SET_ID)
40 AND CCT.COST_TYPE_ID = P_COST_TYPE_ID
41 AND LU2.LOOKUP_TYPE = 'CST_ITEM_RANGE'
42 AND LU2.LOOKUP_CODE = P_RANGE_OPTION;
43 ELSE
44 SELECT
45 FC.CURRENCY_CODE,
46 NVL(FC.EXTENDED_PRECISION
47 ,FC.PRECISION),
48 NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
49 ,POWER(10
50 ,NVL(-PRECISION
51 ,0))),
52 SOB.CHART_OF_ACCOUNTS_ID,
53 O.ORGANIZATION_NAME,
54 C.RANGE_OPTION,
55 LU2.MEANING,
56 C.ITEM_RANGE_LOW,
57 C.ITEM_RANGE_HIGH,
58 C.SINGLE_ITEM,
59 C.CATEGORY_ID,
60 C.CATEGORY_SET_ID,
61 CAT.CATEGORY_SET_NAME,
62 CAT.STRUCTURE_ID,
63 CCT.COST_TYPE,
64 C.UPDATE_DATE,
65 C.DESCRIPTION
66 INTO P_CURRENCY_CODE,EXT_PREC,ROUND_UNIT,P_GL_NUM,P_ORGANIZATION,P_RANGE_OPTION,P_ITEM_RANGE,P_ITEM_FROM,P_ITEM_TO,P_ITEM,P_CAT,P_CATEGORY_SET,P_CAT_SET_NAME,P_CAT_NUM,P_COST_TYPE,P_UPDATE_DATE,P_UPDATE_DESC
67 FROM
68 FND_CURRENCIES FC,
69 GL_SETS_OF_BOOKS SOB,
70 ORG_ORGANIZATION_DEFINITIONS O,
71 MTL_CATEGORY_SETS CAT,
72 MTL_DEFAULT_CATEGORY_SETS DCAT,
73 MFG_LOOKUPS LU2,
74 CST_COST_UPDATES C,
75 CST_COST_TYPES CCT
76 WHERE C.COST_UPDATE_ID = P_UPDATE_ID
77 AND O.ORGANIZATION_ID = C.ORGANIZATION_ID
78 AND O.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
79 AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
80 AND FC.ENABLED_FLAG = 'Y'
81 AND DCAT.FUNCTIONAL_AREA_ID = 5
82 AND CAT.CATEGORY_SET_ID = NVL(C.CATEGORY_SET_ID
83 ,DCAT.CATEGORY_SET_ID)
84 AND CCT.COST_TYPE_ID = C.COST_TYPE_ID
85 AND LU2.LOOKUP_TYPE = 'CST_ITEM_RANGE'
86 AND LU2.LOOKUP_CODE = C.RANGE_OPTION;
87 END IF;
88 IF P_RANGE_OPTION <> 2 THEN
89 /*SRW.SET_MAXROW('Q_ITEM'
90 ,0)*/NULL;
91 END IF;
92 IF P_RANGE_OPTION <> 5 THEN
93 /*SRW.SET_MAXROW('Q_CAT'
94 ,0)*/NULL;
95 END IF;
96 BEGIN
97 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
98 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
99 EXCEPTION
100 WHEN OTHERS THEN
101 /*SRW.MESSAGE(999
102 ,'FND SRWINIT >X')*/NULL;
103 RAISE;
104 END;
105 BEGIN
106 NULL;
107 EXCEPTION
108 WHEN OTHERS THEN
109 /*SRW.MESSAGE(999
110 ,'FND FLEXSQL(MCAT) >X')*/NULL;
111 RAISE;
112 END;
113 BEGIN
114 NULL;
115 EXCEPTION
116 WHEN OTHERS THEN
117 /*SRW.MESSAGE(999
118 ,'FND FLEXSQL(MSTK) >X')*/NULL;
119 RAISE;
120 END;
121 BEGIN
122 NULL;
123 EXCEPTION
124 WHEN OTHERS THEN
125 /*SRW.MESSAGE(999
126 ,'FND FLEXSQL(GL#) >X')*/NULL;
127 RAISE;
128 END;
129 /*SRW.MESSAGE(0
130 ,'BOM_CSTRUSJA_XMLP_PKG << ' || TO_CHAR(SYSDATE
131 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
132 EXCEPTION
133 WHEN OTHERS THEN
134 /*SRW.MESSAGE(999
135 ,SQLERRM)*/NULL;
136 /*SRW.MESSAGE(999
137 ,'BOM_CSTRUSJA_XMLP_PKG >X ' || TO_CHAR(SYSDATE
138 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
139 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
140 END;
141 RETURN (TRUE);
142 END BEFOREREPORT;
143
144 FUNCTION AFTERREPORT RETURN BOOLEAN IS
145 BEGIN
146 BEGIN
147 IF P_DEL_SNAPSHOT = 1 THEN
148 ROLLBACK;
149 DELETE FROM CST_STD_COST_ADJ_VALUES
150 WHERE COST_UPDATE_ID = P_UPDATE_ID
151 AND TRANSACTION_TYPE > 2;
152 COMMIT;
153 END IF;
154 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
155 /*SRW.MESSAGE(0
156 ,'BOM_CSTRUSJA_XMLP_PKG >> ' || TO_CHAR(SYSDATE
157 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
158 EXCEPTION
159 WHEN OTHERS THEN
160 /*SRW.MESSAGE(999
161 ,SQLERRM)*/NULL;
162 END;
163 RETURN (TRUE);
164 END AFTERREPORT;
165
166 FUNCTION JOB_ADJFORMULA(JOB_MTL IN NUMBER
167 ,JOB_MOH IN NUMBER
168 ,JOB_RES IN NUMBER
169 ,JOB_OSP IN NUMBER
170 ,JOB_OVH IN NUMBER) RETURN NUMBER IS
171 BEGIN
172 RETURN -ROUND((JOB_MTL + JOB_MOH + JOB_RES + JOB_OSP + JOB_OVH) / ROUND_UNIT) * ROUND_UNIT;
173 END JOB_ADJFORMULA;
174
175 FUNCTION CLASS_ADJFORMULA(CLASS_MTL IN NUMBER
176 ,CLASS_MOH IN NUMBER
177 ,CLASS_RES IN NUMBER
178 ,CLASS_OSP IN NUMBER
179 ,CLASS_OVH IN NUMBER) RETURN NUMBER IS
180 BEGIN
181 RETURN -ROUND((CLASS_MTL + CLASS_MOH + CLASS_RES + CLASS_OSP + CLASS_OVH) / ROUND_UNIT) * ROUND_UNIT;
182 END CLASS_ADJFORMULA;
183
184 FUNCTION REP_ADJFORMULA(REP_MTL IN NUMBER
185 ,REP_MOH IN NUMBER
186 ,REP_RES IN NUMBER
187 ,REP_OSP IN NUMBER
188 ,REP_OVH IN NUMBER) RETURN NUMBER IS
189 BEGIN
190 RETURN -ROUND((REP_MTL + REP_MOH + REP_RES + REP_OSP + REP_OVH) / ROUND_UNIT) * ROUND_UNIT;
191 END REP_ADJFORMULA;
192
193 FUNCTION AFTERPFORM RETURN BOOLEAN IS
194 BEGIN
195 RETURN (TRUE);
196 END AFTERPFORM;
197
198 FUNCTION OLD_COST2FORMULA(TRANS_TYPE IN NUMBER
199 ,OUC IN NUMBER
200 ,COUNT_QTY IN NUMBER
201 ,SUM_LEVEL IN NUMBER) RETURN NUMBER IS
202 BEGIN
203 IF (TRANS_TYPE in (5,8,9)) THEN
204 RETURN (OUC);
205 ELSE
206 BEGIN
207 IF COUNT_QTY = 0 THEN
208 RETURN (OUC);
209 END IF;
210 IF (SUM_LEVEL / COUNT_QTY) = 1 OR (SUM_LEVEL / COUNT_QTY) = 2 THEN
211 RETURN (ROUND((OUC / COUNT_QTY)
212 ,EXT_PREC));
213 ELSE
214 RETURN (ROUND((OUC * 2 / COUNT_QTY)
215 ,EXT_PREC));
216 END IF;
217 END;
218 RETURN (OUC);
219 END IF;
220 RETURN NULL;
221 END OLD_COST2FORMULA;
222
223 FUNCTION NEW_COST2FORMULA(TRANS_TYPE IN NUMBER
224 ,NUC IN NUMBER
225 ,COUNT_QTY IN NUMBER
226 ,SUM_LEVEL IN NUMBER) RETURN NUMBER IS
227 BEGIN
228 IF (TRANS_TYPE in (5,8,9)) THEN
229 RETURN (NUC);
230 ELSE
231 BEGIN
232 IF COUNT_QTY = 0 THEN
233 RETURN (NUC);
234 END IF;
235 IF (SUM_LEVEL / COUNT_QTY) = 1 OR (SUM_LEVEL / COUNT_QTY) = 2 THEN
236 RETURN (ROUND((NUC / COUNT_QTY)
237 ,EXT_PREC));
238 ELSE
239 RETURN (ROUND((NUC * 2 / COUNT_QTY)
240 ,EXT_PREC));
241 END IF;
242 END;
243 RETURN (NUC);
244 END IF;
245 RETURN NULL;
246 END NEW_COST2FORMULA;
247
248 FUNCTION ADJ_QTY_DISPLAYFORMULA(COUNT_QTY IN NUMBER
249 ,ADJ_QTY_DSP IN NUMBER
250 ,TRANSACTION_CODE IN NUMBER
251 ,SUM_LEVEL IN NUMBER) RETURN NUMBER IS
252 L_QTY NUMBER;
253 BEGIN
254 IF COUNT_QTY = 0 THEN
255 RETURN ADJ_QTY_DSP;
256 END IF;
257 SELECT
258 DECODE(TRANSACTION_CODE
259 ,4
260 ,DECODE(SUM_LEVEL / COUNT_QTY
261 ,1
262 ,ADJ_QTY_DSP
263 ,2
264 ,ADJ_QTY_DSP
265 ,ADJ_QTY_DSP / 2)
266 ,ADJ_QTY_DSP)
267 INTO L_QTY
268 FROM
269 DUAL;
270 RETURN (L_QTY);
271 END ADJ_QTY_DISPLAYFORMULA;
272
273 END BOM_CSTRUSJA_XMLP_PKG;
274