[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRUSIW_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRUSIW_XMLP_PKG AS
2 /* $Header: CSTRUSIWB.pls 120.0 2007/12/24 10:21:36 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 WMS_ORG_COUNT NUMBER;
7 PJM_ORG_COUNT NUMBER;
8 BEGIN
9 SELECT
10 count(*)
11 INTO WMS_ORG_COUNT
12 FROM
13 MTL_PARAMETERS
14 WHERE WMS_ENABLED_FLAG = 'Y'
15 AND ORGANIZATION_ID = P_ORG_ID;
16 SELECT
17 count(*)
18 INTO PJM_ORG_COUNT
19 FROM
20 MTL_PARAMETERS
21 WHERE COST_GROUP_ACCOUNTING = 1
22 AND PROJECT_REFERENCE_ENABLED = 1
23 AND ORGANIZATION_ID = P_ORG_ID;
24 IF WMS_ORG_COUNT < 1 AND PJM_ORG_COUNT < 1 THEN
25 FND_MESSAGE.SET_NAME('BOM'
26 ,'CST_WMS_ORG_REPORT_ONLY');
27 /*SRW.MESSAGE(24200
28 ,FND_MESSAGE.GET)*/NULL;
29 RETURN FALSE;
30 END IF;
31 IF P_RPT_ONLY = 1 THEN
32 SELECT
33 FC.CURRENCY_CODE,
34 NVL(FC.EXTENDED_PRECISION
35 ,FC.PRECISION),
36 NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
37 ,POWER(10
38 ,NVL(-PRECISION
39 ,0))),
40 SOB.CHART_OF_ACCOUNTS_ID,
41 CAT.CATEGORY_SET_NAME,
42 CAT.CATEGORY_SET_ID,
43 CAT.STRUCTURE_ID,
44 O.ORGANIZATION_NAME ORGANIZATION,
45 LU1.MEANING,
46 LU2.MEANING,
47 CCT.COST_TYPE
48 INTO P_CURRENCY_CODE,EXT_PREC,ROUND_UNIT,P_GL_NUM,P_CAT_SET_NAME,P_CATEGORY_SET,P_CAT_NUM,P_ORGANIZATION,P_SORT_BY,P_ITEM_RANGE,P_COST_TYPE
49 FROM
50 FND_CURRENCIES FC,
51 GL_SETS_OF_BOOKS SOB,
52 ORG_ORGANIZATION_DEFINITIONS O,
53 MTL_CATEGORY_SETS CAT,
54 MTL_DEFAULT_CATEGORY_SETS DCAT,
55 CST_COST_TYPES CCT,
56 MFG_LOOKUPS LU1,
57 MFG_LOOKUPS LU2
58 WHERE O.ORGANIZATION_ID = P_ORG_ID
59 AND O.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
60 AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
61 AND FC.ENABLED_FLAG = 'Y'
62 AND DCAT.FUNCTIONAL_AREA_ID = 5
63 AND CAT.CATEGORY_SET_ID = DECODE(P_RANGE_OPTION
64 ,5
65 ,P_CATEGORY_SET
66 ,DECODE(P_SORT_OPTION
67 ,2
68 ,P_CATEGORY_SET
69 ,DCAT.CATEGORY_SET_ID))
70 AND LU1.LOOKUP_TYPE = 'CST_ITEM_REPORT_SORT'
71 AND LU1.LOOKUP_CODE = P_SORT_OPTION
72 AND LU2.LOOKUP_TYPE = 'CST_ITEM_RANGE'
73 AND LU2.LOOKUP_CODE = P_RANGE_OPTION
74 AND CCT.COST_TYPE_ID = P_COST_TYPE_ID;
75 ELSE
76 SELECT
77 FC.CURRENCY_CODE,
78 NVL(FC.EXTENDED_PRECISION
79 ,FC.PRECISION),
80 NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
81 ,POWER(10
82 ,NVL(-PRECISION
83 ,0))),
84 SOB.CHART_OF_ACCOUNTS_ID,
85 CAT.CATEGORY_SET_NAME,
86 CAT.CATEGORY_SET_ID,
87 CAT.STRUCTURE_ID,
88 O.ORGANIZATION_NAME,
89 C.RANGE_OPTION,
90 LU1.MEANING,
91 LU2.MEANING,
92 C.ITEM_RANGE_LOW,
93 C.ITEM_RANGE_HIGH,
94 C.SINGLE_ITEM,
95 C.CATEGORY_ID,
96 C.INV_ADJUSTMENT_ACCOUNT,
97 C.UPDATE_DATE,
98 C.DESCRIPTION,
99 CCT.COST_TYPE
100 INTO P_CURRENCY_CODE,EXT_PREC,ROUND_UNIT,P_GL_NUM,P_CAT_SET_NAME,P_CATEGORY_SET,P_CAT_NUM,P_ORGANIZATION,P_RANGE_OPTION,P_SORT_BY,P_ITEM_RANGE,P_ITEM_FROM,P_ITEM_TO,P_ITEM,P_CAT,P_ADJ_ACCOUNT,P_UPDATE_DATE,P_UPDATE_DESC,P_COST_TYPE
101 FROM
102 FND_CURRENCIES FC,
103 GL_SETS_OF_BOOKS SOB,
104 ORG_ORGANIZATION_DEFINITIONS O,
105 MTL_CATEGORY_SETS CAT,
106 MTL_DEFAULT_CATEGORY_SETS DCAT,
107 MFG_LOOKUPS LU1,
108 MFG_LOOKUPS LU2,
109 CST_COST_TYPES CCT,
110 CST_COST_UPDATES C
111 WHERE C.COST_UPDATE_ID = P_UPDATE_ID
112 AND O.ORGANIZATION_ID = C.ORGANIZATION_ID
113 AND O.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
114 AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
115 AND FC.ENABLED_FLAG = 'Y'
116 AND DCAT.FUNCTIONAL_AREA_ID = 5
117 AND CAT.CATEGORY_SET_ID = NVL(C.CATEGORY_SET_ID
118 ,DCAT.CATEGORY_SET_ID)
119 AND LU1.LOOKUP_TYPE = 'CST_ITEM_REPORT_SORT'
120 AND LU1.LOOKUP_CODE = P_SORT_OPTION
121 AND LU2.LOOKUP_TYPE = 'CST_ITEM_RANGE'
122 AND LU2.LOOKUP_CODE = C.RANGE_OPTION
123 AND CCT.COST_TYPE_ID = C.COST_TYPE_ID;
124 END IF;
125 IF P_SORT_OPTION = 8 THEN
126 /*SRW.SET_MAXROW('Q_IC_MAIN'
127 ,0)*/NULL;
128 ELSE
129 /*SRW.SET_MAXROW('Q_CG_MAIN'
130 ,0)*/NULL;
131 END IF;
132 IF P_RANGE_OPTION <> 2 THEN
133 /*SRW.SET_MAXROW('Q_ITEM'
134 ,0)*/NULL;
135 END IF;
136 IF P_RANGE_OPTION <> 5 THEN
137 /*SRW.SET_MAXROW('Q_CAT'
138 ,0)*/NULL;
139 END IF;
140 BEGIN
141 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
142 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
143 EXCEPTION
144 WHEN OTHERS THEN
145 /*SRW.MESSAGE(999
146 ,'FND SRWINIT >X')*/NULL;
147 RAISE;
148 END;
149 BEGIN
150 NULL;
151 EXCEPTION
152 WHEN OTHERS THEN
153 /*SRW.MESSAGE(999
154 ,'FND FLEXSQL(MCAT) >X')*/NULL;
155 RAISE;
156 END;
157 BEGIN
158 NULL;
159 EXCEPTION
160 WHEN OTHERS THEN
161 /*SRW.MESSAGE(999
162 ,'FND FLEXSQL(MSTK) >X')*/NULL;
163 RAISE;
164 END;
165 BEGIN
166 NULL;
167 EXCEPTION
168 WHEN OTHERS THEN
169 /*SRW.MESSAGE(999
170 ,'FND FLEXSQL(GL#) >X')*/NULL;
171 RAISE;
172 END;
173 /*SRW.MESSAGE(0
174 ,'BOM_CSTRUSIW_XMLP_PKG << ' || TO_CHAR(SYSDATE
175 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
176 EXCEPTION
177 WHEN OTHERS THEN
178 /*SRW.MESSAGE(999
179 ,SQLERRM)*/NULL;
180 /*SRW.MESSAGE(999
181 ,'BOM_CSTRUSIW_XMLP_PKG >X ' || TO_CHAR(SYSDATE
182 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
183 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
184 END;
185
186 RETURN (TRUE);
187 END BEFOREREPORT;
188
189 FUNCTION AFTERREPORT RETURN BOOLEAN IS
190 BEGIN
191 BEGIN
192 IF P_DEL_SNAPSHOT = 1 THEN
193 ROLLBACK;
194 DELETE FROM CST_STD_COST_ADJ_VALUES
195 WHERE COST_UPDATE_ID = P_UPDATE_ID
196 AND TRANSACTION_TYPE = 1;
197 COMMIT;
198 END IF;
199 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
200 /*SRW.MESSAGE(0
201 ,'CSTRUSIA >> ' || TO_CHAR(SYSDATE
202 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
203 EXCEPTION
204 WHEN OTHERS THEN
205 /*SRW.MESSAGE(999
206 ,SQLERRM)*/NULL;
207 END;
208 RETURN (TRUE);
209 END AFTERREPORT;
210
211 FUNCTION CG_CG_ADJFORMULA(CG_CG_MTL IN NUMBER
212 ,CG_CG_MOH IN NUMBER
213 ,CG_CG_RES IN NUMBER
214 ,CG_CG_OSP IN NUMBER
215 ,CG_CG_OVH IN NUMBER) RETURN NUMBER IS
216 BEGIN
217 RETURN (-(CG_CG_MTL + CG_CG_MOH + CG_CG_RES + CG_CG_OSP + CG_CG_OVH));
218 END CG_CG_ADJFORMULA;
219
220 FUNCTION ORG_ADJFORMULA(CG_ORG_MTL IN NUMBER
221 ,CG_ORG_MOH IN NUMBER
222 ,CG_ORG_RES IN NUMBER
223 ,CG_ORG_OSP IN NUMBER
224 ,CG_ORG_OVH IN NUMBER
225 ,IC_ORG_MTL IN NUMBER
226 ,IC_ORG_MOH IN NUMBER
227 ,IC_ORG_RES IN NUMBER
228 ,IC_ORG_OSP IN NUMBER
229 ,IC_ORG_OVH IN NUMBER) RETURN NUMBER IS
230 BEGIN
231 IF P_SORT_OPTION = 8 THEN
232 RETURN (-(CG_ORG_MTL + CG_ORG_MOH + CG_ORG_RES + CG_ORG_OSP + CG_ORG_OVH));
233 ELSE
234 RETURN (-(IC_ORG_MTL + IC_ORG_MOH + IC_ORG_RES + IC_ORG_OSP + IC_ORG_OVH));
235 END IF;
236 RETURN NULL;
237 END ORG_ADJFORMULA;
238
239 FUNCTION REP_ADJFORMULA(CG_REP_MTL IN NUMBER
240 ,CG_REP_MOH IN NUMBER
241 ,CG_REP_RES IN NUMBER
242 ,CG_REP_OSP IN NUMBER
243 ,CG_REP_OVH IN NUMBER
244 ,IC_REP_MTL IN NUMBER
245 ,IC_REP_MOH IN NUMBER
246 ,IC_REP_RES IN NUMBER
247 ,IC_REP_OSP IN NUMBER
248 ,IC_REP_OVH IN NUMBER) RETURN NUMBER IS
249 BEGIN
250 IF P_SORT_OPTION = 8 THEN
251 RETURN (-(CG_REP_MTL + CG_REP_MOH + CG_REP_RES + CG_REP_OSP + CG_REP_OVH));
252 ELSE
253 RETURN (-(IC_REP_MTL + IC_REP_MOH + IC_REP_RES + IC_REP_OSP + IC_REP_OVH));
254 END IF;
255 RETURN NULL;
256 END REP_ADJFORMULA;
257
258 FUNCTION IC_ORDERFORMULA(IC_ITEM_NUMBER IN VARCHAR2
259 ,IC_CATEGORY IN VARCHAR2
260 ,IC_ITEM_SEG IN VARCHAR2
261 ,IC_CAT_SEG IN VARCHAR2
262 ,IC_ITEM_PSEG IN VARCHAR2
263 ,IC_CAT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
264 BEGIN
265 /*SRW.REFERENCE(IC_ITEM_NUMBER)*/NULL;
266 /*SRW.REFERENCE(IC_CATEGORY)*/NULL;
267 /*SRW.REFERENCE(IC_ITEM_SEG)*/NULL;
268 /*SRW.REFERENCE(IC_CAT_SEG)*/NULL;
269 /*SRW.REFERENCE(IC_ITEM_PSEG)*/NULL;
270 /*SRW.REFERENCE(IC_CAT_PSEG)*/NULL;
271 IF P_SORT_OPTION = 1 THEN
272 RETURN (IC_ITEM_PSEG);
273 ELSE
274 RETURN (IC_CAT_PSEG);
275 END IF;
276 RETURN NULL;
277 END IC_ORDERFORMULA;
278
279 FUNCTION IC_CAT_PSEGFORMULA(IC_CATEGORY IN VARCHAR2
280 ,IC_CAT_SEG IN VARCHAR2
281 ,IC_CAT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
282 BEGIN
283 /*SRW.REFERENCE(IC_CATEGORY)*/NULL;
284 /*SRW.REFERENCE(IC_CAT_SEG)*/NULL;
285 RETURN (IC_CAT_PSEG);
286 END IC_CAT_PSEGFORMULA;
287
288 FUNCTION IC_ITEM_PSEGFORMULA(IC_ITEM_NUMBER IN VARCHAR2
289 ,IC_ITEM_SEG IN VARCHAR2
290 ,IC_ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
291 BEGIN
292 /*SRW.REFERENCE(IC_ITEM_NUMBER)*/NULL;
293 /*SRW.REFERENCE(IC_ITEM_SEG)*/NULL;
294 /*SRW.REFERENCE(IC_ITEM_PSEG)*/NULL;
295 RETURN (IC_ITEM_PSEG);
296 END IC_ITEM_PSEGFORMULA;
297
298 FUNCTION CG_ITEM_PSEGFORMULA(CG_ITEM_NUMBER IN VARCHAR2
299 ,CG_ITEM_SEG IN VARCHAR2
300 ,CG_ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
301 BEGIN
302 /*SRW.REFERENCE(CG_ITEM_NUMBER)*/NULL;
303 /*SRW.REFERENCE(CG_ITEM_SEG)*/NULL;
304 /*SRW.REFERENCE(CG_ITEM_PSEG)*/NULL;
305 RETURN (CG_ITEM_PSEG);
306 END CG_ITEM_PSEGFORMULA;
307 FUNCTION dummy1 return boolean is
308 begin
309 IF P_SORT_OPTION = 8 THEN
310 return(false);
311 end if;
312 END dummy1;
313
314 FUNCTION dummy2 return boolean is
315 begin
316 IF P_SORT_OPTION <> 8 THEN
317 return(false);
318 end if;
319 END dummy2;
320
321 FUNCTION dummy3 return boolean is
322 begin
323 IF P_RANGE_OPTION <> 2 THEN
324 return(false);
325 end if;
326 END dummy3;
327
328 FUNCTION dummy4 return boolean is
329 begin
330 IF P_RANGE_OPTION <> 5 THEN
331 return(false);
332 end if;
333 END dummy4;
334
335 END BOM_CSTRUSIW_XMLP_PKG;
336