[Home] [Help]
PACKAGE BODY: APPS.BOM_CSTRUSIA_XMLP_PKG
Source
1 PACKAGE BODY BOM_CSTRUSIA_XMLP_PKG AS
2 /* $Header: CSTRUSIAB.pls 120.1 2008/01/02 15:05:37 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 qty_precision:=bom_common_xmlp_pkg.get_precision(P_qty_precision);
6 BEGIN
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 CAT.CATEGORY_SET_NAME,
18 CAT.CATEGORY_SET_ID,
19 CAT.STRUCTURE_ID,
20 O.ORGANIZATION_NAME ORGANIZATION,
21 LU1.MEANING,
22 LU2.MEANING,
23 CCT.COST_TYPE
24 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
25 FROM
26 FND_CURRENCIES FC,
27 GL_SETS_OF_BOOKS SOB,
28 ORG_ORGANIZATION_DEFINITIONS O,
29 MTL_CATEGORY_SETS CAT,
30 MTL_DEFAULT_CATEGORY_SETS DCAT,
31 CST_COST_TYPES CCT,
32 MFG_LOOKUPS LU1,
33 MFG_LOOKUPS LU2
34 WHERE O.ORGANIZATION_ID = P_ORG_ID
35 AND O.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
36 AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
37 AND FC.ENABLED_FLAG = 'Y'
38 AND DCAT.FUNCTIONAL_AREA_ID = 5
39 AND CAT.CATEGORY_SET_ID = DECODE(P_RANGE_OPTION
40 ,5
41 ,P_CATEGORY_SET
42 ,DECODE(P_SORT_OPTION
43 ,2
44 ,P_CATEGORY_SET
45 ,DCAT.CATEGORY_SET_ID))
46 AND LU1.LOOKUP_TYPE = 'CST_ITEM_REPORT_SORT'
47 AND LU1.LOOKUP_CODE = P_SORT_OPTION
48 AND LU2.LOOKUP_TYPE = 'CST_ITEM_RANGE'
49 AND LU2.LOOKUP_CODE = P_RANGE_OPTION
50 AND CCT.COST_TYPE_ID = P_COST_TYPE_ID;
51 ELSE
52 SELECT
53 FC.CURRENCY_CODE,
54 NVL(FC.EXTENDED_PRECISION
55 ,FC.PRECISION),
56 NVL(FC.MINIMUM_ACCOUNTABLE_UNIT
57 ,POWER(10
58 ,NVL(-PRECISION
59 ,0))),
60 SOB.CHART_OF_ACCOUNTS_ID,
61 CAT.CATEGORY_SET_NAME,
62 CAT.CATEGORY_SET_ID,
63 CAT.STRUCTURE_ID,
64 O.ORGANIZATION_NAME,
65 C.RANGE_OPTION,
66 LU1.MEANING,
67 LU2.MEANING,
68 C.ITEM_RANGE_LOW,
69 C.ITEM_RANGE_HIGH,
70 C.SINGLE_ITEM,
71 C.CATEGORY_ID,
72 C.INV_ADJUSTMENT_ACCOUNT,
73 C.UPDATE_DATE,
74 C.DESCRIPTION,
75 CCT.COST_TYPE
76 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
77 FROM
78 FND_CURRENCIES FC,
79 GL_SETS_OF_BOOKS SOB,
80 ORG_ORGANIZATION_DEFINITIONS O,
81 MTL_CATEGORY_SETS CAT,
82 MTL_DEFAULT_CATEGORY_SETS DCAT,
83 MFG_LOOKUPS LU1,
84 MFG_LOOKUPS LU2,
85 CST_COST_TYPES CCT,
86 CST_COST_UPDATES C
87 WHERE C.COST_UPDATE_ID = P_UPDATE_ID
88 AND O.ORGANIZATION_ID = C.ORGANIZATION_ID
89 AND O.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
90 AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
91 AND FC.ENABLED_FLAG = 'Y'
92 AND DCAT.FUNCTIONAL_AREA_ID = 5
93 AND CAT.CATEGORY_SET_ID = NVL(C.CATEGORY_SET_ID
94 ,DCAT.CATEGORY_SET_ID)
95 AND LU1.LOOKUP_TYPE = 'CST_ITEM_REPORT_SORT'
96 AND LU1.LOOKUP_CODE = P_SORT_OPTION
97 AND LU2.LOOKUP_TYPE = 'CST_ITEM_RANGE'
98 AND LU2.LOOKUP_CODE = C.RANGE_OPTION
99 AND CCT.COST_TYPE_ID = C.COST_TYPE_ID;
100 END IF;
101 IF P_SORT_OPTION = 3 THEN
102 /*SRW.SET_MAXROW('Q_IC_MAIN'
103 ,0)*/NULL;
104 ELSE
105 /*SRW.SET_MAXROW('Q_SI_MAIN'
106 ,0)*/NULL;
107 END IF;
108 IF P_RANGE_OPTION <> 2 THEN
109 /*SRW.SET_MAXROW('Q_ITEM'
110 ,0)*/NULL;
111 END IF;
112 IF P_RANGE_OPTION <> 5 THEN
113 /*SRW.SET_MAXROW('Q_CAT'
114 ,0)*/NULL;
115 END IF;
116 BEGIN
117 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
118 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
119 EXCEPTION
120 WHEN OTHERS THEN
121 /*SRW.MESSAGE(999
122 ,'FND SRWINIT >X')*/NULL;
123 RAISE;
124 END;
125 BEGIN
126 NULL;
127 EXCEPTION
128 WHEN OTHERS THEN
129 /*SRW.MESSAGE(999
130 ,'FND FLEXSQL(MCAT) >X')*/NULL;
131 RAISE;
132 END;
133 BEGIN
134 NULL;
135 EXCEPTION
136 WHEN OTHERS THEN
137 /*SRW.MESSAGE(999
138 ,'FND FLEXSQL(MSTK) >X')*/NULL;
139 RAISE;
140 END;
141 BEGIN
142 NULL;
143 EXCEPTION
144 WHEN OTHERS THEN
145 /*SRW.MESSAGE(999
146 ,'FND FLEXSQL(GL#) >X')*/NULL;
147 RAISE;
148 END;
149 /*SRW.MESSAGE(0
150 ,'BOM_CSTRUSIA_XMLP_PKG << ' || TO_CHAR(SYSDATE
151 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
152 EXCEPTION
153 WHEN OTHERS THEN
154 /*SRW.MESSAGE(999
155 ,SQLERRM)*/NULL;
156 /*SRW.MESSAGE(999
157 ,'BOM_CSTRUSIA_XMLP_PKG >X ' || TO_CHAR(SYSDATE
158 ,'Dy Mon FmDD HH24:MI:SS YYYY'))*/NULL;
159 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
160 END;
161 RETURN (TRUE);
162 END BEFOREREPORT;
163
164 FUNCTION AFTERREPORT RETURN BOOLEAN IS
165 BEGIN
166 BEGIN
167 IF P_DEL_SNAPSHOT = 1 THEN
168 ROLLBACK;
169 DELETE FROM CST_STD_COST_ADJ_VALUES
170 WHERE COST_UPDATE_ID = P_UPDATE_ID
171 AND TRANSACTION_TYPE = 1;
172 COMMIT;
173 END IF;
174 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
175 /*SRW.MESSAGE(0
176 ,'BOM_CSTRUSIA_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 ,SQLERRM)*/NULL;
182 END;
183 RETURN (TRUE);
184 END AFTERREPORT;
185
186 FUNCTION G_FILTER1 RETURN BOOLEAN IS
187 BEGIN
188 IF P_SORT_OPTION = 3 THEN
189 RETURN(FALSE);
190 ELSE
191 RETURN(TRUE);
192
193 END IF;
194 END G_FILTER1;
195
196 FUNCTION G_FILTER2 RETURN BOOLEAN IS
197 BEGIN
198 IF P_SORT_OPTION <> 3 THEN
199 RETURN(FALSE);
200 ELSE
201 RETURN(TRUE);
202
203 END IF;
204 END G_FILTER2;
205 FUNCTION G_FILTER3 RETURN BOOLEAN IS
206 BEGIN
207 IF P_RANGE_OPTION <> 2 THEN
208 RETURN(FALSE);
209 ELSE
210 RETURN(TRUE);
211
212 END IF;
213 END G_FILTER3;
214
215 FUNCTION G_FILTER4 RETURN BOOLEAN IS
216 BEGIN
217 IF P_RANGE_OPTION <> 5 THEN
218 RETURN(FALSE);
219 ELSE
220 RETURN(TRUE);
221
222 END IF;
223 END G_FILTER4;
224
225 FUNCTION SI_SI_ADJFORMULA(SI_SI_MTL IN NUMBER
226 ,SI_SI_MOH IN NUMBER
227 ,SI_SI_RES IN NUMBER
228 ,SI_SI_OSP IN NUMBER
229 ,SI_SI_OVH IN NUMBER) RETURN NUMBER IS
230 BEGIN
231 RETURN (-(SI_SI_MTL + SI_SI_MOH + SI_SI_RES + SI_SI_OSP + SI_SI_OVH));
232 END SI_SI_ADJFORMULA;
233
234 FUNCTION ORG_ADJFORMULA(SI_ORG_MTL IN NUMBER
235 ,SI_ORG_MOH IN NUMBER
236 ,SI_ORG_RES IN NUMBER
237 ,SI_ORG_OSP IN NUMBER
238 ,SI_ORG_OVH IN NUMBER
239 ,IC_ORG_MTL IN NUMBER
240 ,IC_ORG_MOH IN NUMBER
241 ,IC_ORG_RES IN NUMBER
242 ,IC_ORG_OSP IN NUMBER
243 ,IC_ORG_OVH IN NUMBER) RETURN NUMBER IS
244 BEGIN
245 IF P_SORT_OPTION = 3 THEN
246 RETURN (-(SI_ORG_MTL + SI_ORG_MOH + SI_ORG_RES + SI_ORG_OSP + SI_ORG_OVH));
247 ELSE
248 RETURN (-(IC_ORG_MTL + IC_ORG_MOH + IC_ORG_RES + IC_ORG_OSP + IC_ORG_OVH));
249 END IF;
250 RETURN NULL;
251 END ORG_ADJFORMULA;
252
253 FUNCTION REP_ADJFORMULA(SI_REP_MTL IN NUMBER
254 ,SI_REP_MOH IN NUMBER
255 ,SI_REP_RES IN NUMBER
256 ,SI_REP_OSP IN NUMBER
257 ,SI_REP_OVH IN NUMBER
258 ,IC_REP_MTL IN NUMBER
259 ,IC_REP_MOH IN NUMBER
260 ,IC_REP_RES IN NUMBER
261 ,IC_REP_OSP IN NUMBER
262 ,IC_REP_OVH IN NUMBER) RETURN NUMBER IS
263 BEGIN
264 IF P_SORT_OPTION = 3 THEN
265 RETURN (-(SI_REP_MTL + SI_REP_MOH + SI_REP_RES + SI_REP_OSP + SI_REP_OVH));
266 ELSE
267 RETURN (-(IC_REP_MTL + IC_REP_MOH + IC_REP_RES + IC_REP_OSP + IC_REP_OVH));
268 END IF;
269 RETURN NULL;
270 END REP_ADJFORMULA;
271
272 FUNCTION IC_ORDERFORMULA(IC_ITEM_NUMBER IN VARCHAR2
273 ,IC_CATEGORY IN VARCHAR2
274 ,IC_ITEM_SEG IN VARCHAR2
275 ,IC_CAT_SEG IN VARCHAR2
276 ,IC_ITEM_PSEG IN VARCHAR2
277 ,IC_CAT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
278 BEGIN
279 /*SRW.REFERENCE(IC_ITEM_NUMBER)*/NULL;
280 /*SRW.REFERENCE(IC_CATEGORY)*/NULL;
281 /*SRW.REFERENCE(IC_ITEM_SEG)*/NULL;
282 /*SRW.REFERENCE(IC_CAT_SEG)*/NULL;
283 /*SRW.REFERENCE(IC_ITEM_PSEG)*/NULL;
284 /*SRW.REFERENCE(IC_CAT_PSEG)*/NULL;
285 IF P_SORT_OPTION = 1 THEN
286 RETURN (IC_ITEM_PSEG);
287 ELSE
288 RETURN (IC_CAT_PSEG);
289 END IF;
290 RETURN NULL;
291 END IC_ORDERFORMULA;
292
293 FUNCTION IC_CAT_PSEGFORMULA(IC_CATEGORY IN VARCHAR2
294 ,IC_CAT_SEG IN VARCHAR2
295 ,IC_CAT_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
296 BEGIN
297 /*SRW.REFERENCE(IC_CATEGORY)*/NULL;
298 /*SRW.REFERENCE(IC_CAT_SEG)*/NULL;
299 RETURN (IC_CAT_PSEG);
300 END IC_CAT_PSEGFORMULA;
301
302 FUNCTION IC_ITEM_PSEGFORMULA(IC_ITEM_NUMBER IN VARCHAR2
303 ,IC_ITEM_SEG IN VARCHAR2
304 ,IC_ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
305 BEGIN
306 /*SRW.REFERENCE(IC_ITEM_NUMBER)*/NULL;
307 /*SRW.REFERENCE(IC_ITEM_SEG)*/NULL;
308 /*SRW.REFERENCE(IC_ITEM_PSEG)*/NULL;
309 RETURN (IC_ITEM_PSEG);
310 END IC_ITEM_PSEGFORMULA;
311
312 FUNCTION SI_ITEM_PSEGFORMULA(SI_ITEM_NUMBER IN VARCHAR2
313 ,SI_ITEM_SEG IN VARCHAR2
314 ,SI_ITEM_PSEG IN VARCHAR2) RETURN VARCHAR2 IS
315 BEGIN
316 /*SRW.REFERENCE(SI_ITEM_NUMBER)*/NULL;
317 /*SRW.REFERENCE(SI_ITEM_SEG)*/NULL;
318 /*SRW.REFERENCE(SI_ITEM_PSEG)*/NULL;
319 RETURN (SI_ITEM_PSEG);
320 END SI_ITEM_PSEGFORMULA;
321
322 END BOM_CSTRUSIA_XMLP_PKG;
323