1 PACKAGE BODY BOM_CSTGMSBK_XMLP_PKG AS
2 /* $Header: CSTGMSBKB.pls 120.0 2007/12/24 09:48:43 dwkrishn noship $ */
3 /* PROCEDURE FORMAT_QUANTITY(P_PRECISION IN NUMBER) IS
4 BEGIN
5 SRW.ATTR.MASK := SRW.FORMATMASK_ATTR;
6 IF P_PRECISION = 0 THEN
7 SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0';
8 ELSIF P_PRECISION = 1 THEN
9 SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0.0';
10 ELSIF P_PRECISION = 2 THEN
11 SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0.00';
12 ELSIF P_PRECISION = 3 THEN
13 SRW.ATTR.FORMATMASK := '-NN,NNN,NNN,NN0.000';
14 ELSIF P_PRECISION = 4 THEN
15 SRW.ATTR.FORMATMASK := '-N,NNN,NNN,NN0.0000';
16 ELSIF P_PRECISION = 5 THEN
17 SRW.ATTR.FORMATMASK := '-NNN,NNN,NN0.00000';
18 ELSIF P_PRECISION = 6 THEN
19 SRW.ATTR.FORMATMASK := '-NN,NNN,NN0.000000';
20 ELSIF P_PRECISION = 7 THEN
21 SRW.ATTR.FORMATMASK := '-NNNNNNNNNNN0';
22 ELSIF P_PRECISION = 8 THEN
23 SRW.ATTR.FORMATMASK := '-NNNNNNNNNNN0.0';
24 ELSIF P_PRECISION = 9 THEN
25 SRW.ATTR.FORMATMASK := '-NNNNNNNNNN0.00';
26 ELSIF P_PRECISION = 10 THEN
27 SRW.ATTR.FORMATMASK := '-NNNNNNNNNN0.000';
28 ELSIF P_PRECISION = 11 THEN
29 SRW.ATTR.FORMATMASK := '-NNNNNNNNN0.0000';
30 ELSIF P_PRECISION = 12 THEN
31 SRW.ATTR.FORMATMASK := '-NNNNNNNN0.00000';
32 ELSIF P_PRECISION = 13 THEN
33 SRW.ATTR.FORMATMASK := '-NNNNNNN0.000000';
34 ELSE
35 SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0.00';
36 END IF;
37 SRW.SET_ATTR(0
38 ,SRW.ATTR);
39 NULL;
40 END FORMAT_QUANTITY;*/
41
42 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
43 INVALID_DATE_RANGE_EXCEPT EXCEPTION;
44 BEGIN
45 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
46 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
47 H_RPT_DATE_FROM := TRUNC(FND_DATE.CANONICAL_TO_DATE(P_DATE_FROM));
48 H_RPT_DATE_TO := TRUNC(FND_DATE.CANONICAL_TO_DATE(P_DATE_TO)) + (86399 / 86400);
49 IF H_RPT_DATE_FROM > H_RPT_DATE_TO THEN
50 FND_MESSAGE.SET_NAME('FND'
51 ,'INVALID DATE RANGE');
52 H_MSG := FND_MESSAGE.GET;
53 RAISE INVALID_DATE_RANGE_EXCEPT;
54 END IF;
55 BEGIN
56
57 CST_MGD_MSTR_BOOK_RPT.CREATE_INV_MSBK_RPT(P_ORG_ID => P_ORG_ID
58 ,P_CATEGORY_SET_ID => P_CATEGORY_SET_ID
59 ,P_CATEGORY_FROM => P_CATEGORY_FROM
60 ,P_CATEGORY_TO => P_CATEGORY_TO
61 ,P_SUBINV_FROM => P_SUBINV_FROM
62 ,P_SUBINV_TO => P_SUBINV_TO
63 ,P_ABC_GROUP_ID => P_ABC_GROUP_ID
64 ,P_ABC_CLASS_ID => P_ABC_CLASS_ID
65 ,P_ITEM_FROM_CODE => P_ITEM_CODE_FROM
66 ,P_ITEM_TO_CODE => P_ITEM_CODE_TO
67 ,P_RPT_FROM_DATE => P_DATE_FROM
68 ,P_RPT_TO_DATE => P_DATE_TO);
69 EXCEPTION
70 WHEN OTHERS THEN
71 /*SRW.MESSAGE(1
72 ,FND_MSG_PUB.GET(P_MSG_INDEX => -1
73 ,P_ENCODED => 'F'))*/NULL;
74 END;
75 BEGIN
76 SELECT
77 FND_DATE.DATE_TO_CHARDT(SYSDATE)
78 INTO P_SYSTEM_DATE
79 FROM
80 DUAL;
81 END;
82 BEGIN
83 SELECT
84 FND_DATE.DATE_TO_CHARDATE(TRUNC(FND_DATE.CANONICAL_TO_DATE(P_DATE_FROM)))
85 INTO P_DATE_FROM_FORMATTING
86 FROM
87 DUAL;
88 END;
89 BEGIN
90 SELECT
91 FND_DATE.DATE_TO_CHARDATE(TRUNC(FND_DATE.CANONICAL_TO_DATE(P_DATE_TO)))
92 INTO P_DATE_TO_FORMATTING
93 FROM
94 DUAL;
95 END;
96 BEGIN
97 SELECT
98 FC.EXTENDED_PRECISION,
99 GSOB.CURRENCY_CODE
100 INTO CP_PRECISION_VAL,CP_CURRENCY_CODE
101 FROM
102 GL_SETS_OF_BOOKS GSOB,
103 ORG_ORGANIZATION_DEFINITIONS OOD,
104 FND_CURRENCIES FC
105 WHERE OOD.ORGANIZATION_ID = P_ORG_ID
106 AND OOD.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
107 AND FC.CURRENCY_CODE = GSOB.CURRENCY_CODE;
108 END;
109 BEGIN
110 SELECT
111 FRV.RESPONSIBILITY_NAME,
112 FND_DATE.DATE_TO_CHARDT(FCR.REQUEST_DATE),
113 FAV.APPLICATION_NAME,
114 FU.USER_NAME
115 INTO CP_RESPONSIBILITY,CP_REQUEST_TIME,CP_APPLICATION,CP_REQUESTED_BY
116 FROM
117 FND_CONCURRENT_REQUESTS FCR,
118 FND_RESPONSIBILITY_VL FRV,
119 FND_APPLICATION_VL FAV,
120 FND_USER FU
121 WHERE FCR.REQUEST_ID = P_CONC_REQUEST_ID
122 AND FCR.RESPONSIBILITY_APPLICATION_ID = FRV.APPLICATION_ID
123 AND FCR.RESPONSIBILITY_ID = FRV.RESPONSIBILITY_ID
124 AND FRV.APPLICATION_ID = FAV.APPLICATION_ID
125 AND FU.USER_ID = FCR.REQUESTED_BY;
126 END;
127 SELECT
128 MEANING
129 INTO CP_INCLUDE_COST
130 FROM
131 FND_LOOKUPS
132 WHERE LOOKUP_CODE = P_INCLUDE_ITEM_COST
133 AND LOOKUP_TYPE = 'YES_NO';
134 SELECT
135 MEANING
136 INTO CP_DETAIL
137 FROM
138 FND_LOOKUPS
139 WHERE LOOKUP_CODE = P_DETAIL
140 AND LOOKUP_TYPE = 'INV_BOOK_DETAIL';
141 IF P_ABC_CLASS_ID IS NOT NULL THEN
142 SELECT
143 ABC_CLASS_NAME
144 INTO CP_ABC_CLASS_NAME
145 FROM
146 MTL_ABC_CLASSES
147 WHERE ABC_CLASS_ID = P_ABC_CLASS_ID;
148 END IF;
149 IF P_ABC_GROUP_ID IS NOT NULL THEN
150 SELECT
151 ASSIGNMENT_GROUP_NAME
152 INTO CP_ABC_GROUP_NAME
153 FROM
154 MTL_ABC_ASSIGNMENT_GROUPS
155 WHERE ASSIGNMENT_GROUP_ID = P_ABC_GROUP_ID;
156 END IF;
157 IF P_CATEGORY_FROM IS NOT NULL THEN
158 SELECT
159 CATEGORY_SET_NAME
160 INTO CP_CATEGORY_SET_NAME
161 FROM
162 MTL_CATEGORY_SETS
163 WHERE CATEGORY_SET_ID = P_CATEGORY_SET_ID;
164 END IF;
165 RETURN (TRUE);
166 EXCEPTION
167 WHEN INVALID_DATE_RANGE_EXCEPT THEN
168 /*SRW.MESSAGE(55
169 ,H_MSG)*/NULL;
170 END BEFOREREPORT;
171
172 FUNCTION AFTERREPORT RETURN BOOLEAN IS
173 BEGIN
174 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
175 RETURN (TRUE);
176 END AFTERREPORT;
177
178 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
179 BEGIN
180 RETURN (TRUE);
181 END BEFOREPFORM;
182
183 FUNCTION AFTERPFORM RETURN BOOLEAN IS
184 BEGIN
185 RETURN (TRUE);
186 END AFTERPFORM;
187
188 FUNCTION CF_TYPEFORMULA(TXN_QTY IN NUMBER) RETURN CHAR IS
189 BEGIN
190 IF TXN_QTY > 0 THEN
191 RETURN (PTXT_RECEIPT);
192 ELSIF TXN_QTY < 0 THEN
193 RETURN (PTXT_ISSUE);
194 ELSE
195 RETURN ('None');
196 END IF;
197 END CF_TYPEFORMULA;
198
199 FUNCTION CF_DETAIL_LEVELFORMULA RETURN CHAR IS
200 BEGIN
201 IF P_DETAIL = 'S' THEN
202 RETURN ('Summary Level');
203 ELSIF P_DETAIL = 'I' THEN
204 RETURN ('Intermediate Level');
205 ELSIF P_DETAIL = 'D' THEN
206 RETURN ('Detail Level');
207 ELSE
208 RETURN ('Default Detail');
209 END IF;
210 END CF_DETAIL_LEVELFORMULA;
211
212 FUNCTION P_DATE_FROM_FORMATTINGVALIDTRI RETURN BOOLEAN IS
213 BEGIN
214 RETURN (TRUE);
215 END P_DATE_FROM_FORMATTINGVALIDTRI;
216
217 FUNCTION CF_TITLEFORMULA RETURN CHAR IS
218 TITLE VARCHAR2(40);
219 BEGIN
220 TITLE := '';
221 IF P_DETAIL = 'S' THEN
222 TITLE := 'Summary Level';
223 ELSIF P_DETAIL = 'I' THEN
224 TITLE := 'Intermediate Level';
225 ELSIF P_DETAIL = 'D' THEN
226 TITLE := 'Detail Level';
227 END IF;
228 IF P_INCLUDE_ITEM_COST = 'Y' THEN
229 RETURN (CONCAT(TITLE
230 ,' - Cost Included'));
231 ELSE
232 RETURN (TITLE);
233 END IF;
234 END CF_TITLEFORMULA;
235
236 FUNCTION CF_COUNT_ROWSFORMULA RETURN NUMBER IS
237 COUNT_ROWS NUMBER;
238 BEGIN
239 SELECT
240 count(*)
241 INTO COUNT_ROWS
242 FROM
243 CST_MGD_MSTR_BOOK_TEMP;
244 RETURN (COUNT_ROWS);
245 END CF_COUNT_ROWSFORMULA;
246
247 FUNCTION P_DATE_FROMVALIDTRIGGER RETURN BOOLEAN IS
248 BEGIN
249 RETURN (TRUE);
250 END P_DATE_FROMVALIDTRIGGER;
251
252 FUNCTION CF_TXN_GROUP_TYPEFORMULA(TXN_TYPE_QTY IN NUMBER) RETURN CHAR IS
253 BEGIN
254 IF TXN_TYPE_QTY > 0 THEN
255 RETURN (PTXT_RECEIPT);
256 ELSE
257 RETURN (PTXT_ISSUE);
258 END IF;
259 END CF_TXN_GROUP_TYPEFORMULA;
260
261 FUNCTION CF_FINAL_QUANTITYFORMULA(CS_FINAL_QUANTITY IN NUMBER
262 ,CS_B_QTY IN VARCHAR2) RETURN NUMBER IS
263 BEGIN
264 RETURN (NVL(CS_FINAL_QUANTITY
265 ,0) + NVL(CS_B_QTY
266 ,0));
267 END CF_FINAL_QUANTITYFORMULA;
268
269 FUNCTION CP_ORGANIZATION_P RETURN VARCHAR2 IS
270 BEGIN
271 RETURN CP_ORGANIZATION;
272 END CP_ORGANIZATION_P;
273
274 FUNCTION CP_REQUEST_TIME_P RETURN VARCHAR2 IS
275 BEGIN
276 RETURN CP_REQUEST_TIME;
277 END CP_REQUEST_TIME_P;
278
279 FUNCTION CP_RESPONSIBILITY_P RETURN VARCHAR2 IS
280 BEGIN
281 RETURN CP_RESPONSIBILITY;
282 END CP_RESPONSIBILITY_P;
283
284 FUNCTION CP_APPLICATION_P RETURN VARCHAR2 IS
285 BEGIN
286 RETURN CP_APPLICATION;
287 END CP_APPLICATION_P;
288
289 FUNCTION CP_ABC_CLASS_NAME_P RETURN VARCHAR2 IS
290 BEGIN
291 RETURN CP_ABC_CLASS_NAME;
292 END CP_ABC_CLASS_NAME_P;
293
294 FUNCTION CP_PRECISION_VAL_P RETURN NUMBER IS
295 BEGIN
296 RETURN CP_PRECISION_VAL;
297 END CP_PRECISION_VAL_P;
298
299 FUNCTION CP_REQUESTED_BY_P RETURN VARCHAR2 IS
300 BEGIN
301 RETURN CP_REQUESTED_BY;
302 END CP_REQUESTED_BY_P;
303
304 FUNCTION CP_INCLUDE_COST_P RETURN VARCHAR2 IS
305 BEGIN
306 RETURN CP_INCLUDE_COST;
307 END CP_INCLUDE_COST_P;
308
309 FUNCTION CP_DETAIL_P RETURN VARCHAR2 IS
310 BEGIN
311 RETURN CP_DETAIL;
312 END CP_DETAIL_P;
313
314 FUNCTION CP_TITLE_P RETURN VARCHAR2 IS
315 BEGIN
316 RETURN CP_TITLE;
317 END CP_TITLE_P;
318
319 FUNCTION CP_COUNT_P RETURN NUMBER IS
320 BEGIN
321 RETURN CP_COUNT;
322 END CP_COUNT_P;
323
324 FUNCTION CP_ABC_GROUP_NAME_P RETURN VARCHAR2 IS
325 BEGIN
326 RETURN CP_ABC_GROUP_NAME;
327 END CP_ABC_GROUP_NAME_P;
328
329 FUNCTION CP_CATEGORY_SET_NAME_P RETURN VARCHAR2 IS
330 BEGIN
331 RETURN CP_CATEGORY_SET_NAME;
332 END CP_CATEGORY_SET_NAME_P;
333
334 FUNCTION CP_CURRENCY_CODE_P RETURN VARCHAR2 IS
335 BEGIN
336 RETURN CP_CURRENCY_CODE;
337 END CP_CURRENCY_CODE_P;
338
339 FUNCTION H_RPT_DATE_FROM_P RETURN DATE IS
340 BEGIN
341 RETURN H_RPT_DATE_FROM;
342 END H_RPT_DATE_FROM_P;
343
344 FUNCTION H_RPT_DATE_TO_P RETURN DATE IS
345 BEGIN
346 RETURN H_RPT_DATE_TO;
347 END H_RPT_DATE_TO_P;
348
349 FUNCTION H_MSG_P RETURN VARCHAR2 IS
350 BEGIN
351 RETURN H_MSG;
352 END H_MSG_P;
353
354 END BOM_CSTGMSBK_XMLP_PKG;
355