1 PACKAGE BODY BOM_CSTRPDSS_XMLP_PKG AS
2 /* $Header: CSTRPDSSB.pls 120.2 2008/01/03 03:42:48 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 apf boolean;
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 apf := AFTERPFORM;
9 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
10 EXCEPTION
11 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
12 /*SRW.MESSAGE(1
13 ,'Error in SRWINIT')*/NULL;
14 RAISE;
15 END;
16 BEGIN
17 SELECT
18 FRV.RESPONSIBILITY_NAME,
19 TO_CHAR(FCR.REQUEST_DATE
20 ,'YYYY/MM/DD HH24:MI:SS'),
21 FAV.APPLICATION_NAME,
22 FU.USER_NAME
23 INTO CP_RESPONSIBILITY,CP_REQUEST_TIME,CP_APPLICATION,CP_REQUESTED_BY
24 FROM
25 FND_CONCURRENT_REQUESTS FCR,
26 FND_RESPONSIBILITY_VL FRV,
27 FND_APPLICATION_VL FAV,
28 FND_USER FU
29 WHERE FCR.REQUEST_ID = P_CONC_REQUEST_ID
30 AND FCR.RESPONSIBILITY_APPLICATION_ID = FRV.APPLICATION_ID
31 AND FCR.RESPONSIBILITY_ID = FRV.RESPONSIBILITY_ID
32 AND FRV.APPLICATION_ID = FAV.APPLICATION_ID
33 AND FU.USER_ID = FCR.REQUESTED_BY;
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 /*SRW.MESSAGE(30
37 ,'Failed Request By and Request time Init,No data.')*/NULL;
38 WHEN OTHERS THEN
39 /*SRW.MESSAGE(31
40 ,'Failed Request By and Request time Init.')*/NULL;
41 END;
42 BEGIN
43 SELECT
44 DISTINCT
45 XFI.NAME,
46 CCG.COST_GROUP,
47 CCT.COST_TYPE,
48 GLL.CURRENCY_CODE,
49 CAH.PERIOD_NAME,
50 NVL(FC.PRECISION
51 ,2)
52 INTO CP_LEGAL_ENTITY,CP_COST_GROUP,CP_COST_TYPE,R_CURRENCY_CODE,CP_PERIOD_NAME,CP_PRECISION
53 FROM
54 CST_AE_HEADERS CAH,
55 XLE_FIRSTPARTY_INFORMATION_V XFI,
56 GL_LEDGER_LE_V GLL,
57 CST_COST_GROUPS CCG,
58 CST_COST_TYPES CCT,
59 FND_CURRENCIES FC
60 WHERE CAH.LEGAL_ENTITY_ID = XFI.LEGAL_ENTITY_ID
61 AND GLL.LEGAL_ENTITY_ID = XFI.LEGAL_ENTITY_ID
62 AND GLL.RELATIONSHIP_ENABLED_FLAG = 'Y'
63 AND GLL.LEDGER_CATEGORY_CODE = 'PRIMARY'
64 AND CAH.COST_GROUP_ID = P_COST_GROUP_ID
65 AND CAH.PERIOD_ID = P_PERIOD_ID
66 AND CCG.COST_GROUP_ID = CAH.COST_GROUP_ID
67 AND CCT.COST_TYPE_ID = P_COST_TYPE_ID
68 AND FC.CURRENCY_CODE = GLL.CURRENCY_CODE;
69 QTY_PRECISION:=bom_common_xmlp_pkg.get_precision(CP_PRECISION);
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 /*SRW.MESSAGE(30
73 ,'Failed in legal entity Init, No data.')*/NULL;
74 WHEN OTHERS THEN
75 /*SRW.MESSAGE(31
76 ,'Failed in legal entity Init.')*/NULL;
77 END;
78 BEGIN
79 IF P_WIP = 2 THEN
80 IF P_SORT_ID = 1 THEN
81 NULL;
82 ELSE
83 PLEX_ITEM_FLEX := ' ';
84 END IF;
85 ELSE
86 PLEX_ITEM_FLEX := ' ';
87 END IF;
88 EXCEPTION
89 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
90 /*SRW.MESSAGE(2
91 ,'Error in MSTK')*/NULL;
92 RAISE;
93 END;
94 BEGIN
95 NULL;
96 EXCEPTION
97 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
98 /*SRW.MESSAGE(3
99 ,'Error in GL#')*/NULL;
100 RAISE;
101 END;
102 BEGIN
106 NULL;
103 IF P_ACCT_FROM IS NOT NULL OR P_ACCT_TO IS NOT NULL THEN
104 NULL;
105 ELSE
107 END IF;
108 EXCEPTION
109 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
110 /*SRW.MESSAGE(4
111 ,'Error in GL#')*/NULL;
112 RAISE;
113 END;
114 RETURN (TRUE);
115 END BEFOREREPORT;
116
117 FUNCTION AFTERREPORT RETURN BOOLEAN IS
118 BEGIN
119 BEGIN
120 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
121 EXCEPTION
122 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
123 /*SRW.MESSAGE(1
124 ,'Error in SRWEXIT')*/NULL;
125 END;
126 RETURN (TRUE);
127 END AFTERREPORT;
128
129 FUNCTION C_ACCT_PADFORMULA(C_ACCT_PAD IN VARCHAR2) RETURN VARCHAR2 IS
130 BEGIN
131 RETURN (C_ACCT_PAD);
132 END C_ACCT_PADFORMULA;
133
134 FUNCTION AFTERPFORM RETURN BOOLEAN IS
135 BEGIN
136 BEGIN
137 IF P_WIP = 2 THEN
138 IF P_SORT_ID = 2 THEN
139 P_SORT_OPTION := 'caetv.description';
140 ELSIF P_SORT_ID = 3 THEN
141 P_SORT_OPTION := 'mtst.transaction_source_type_name';
142 ELSE
143 P_SORT_OPTION := '(TO_CHAR(3)) ';
144 END IF;
145 ELSE
146 P_SORT_OPTION := '(TO_CHAR(3)) ';
147 END IF;
148 EXCEPTION
149 WHEN OTHERS THEN
150 /*SRW.MESSAGE(1
151 ,'Failed in p_sort_option')*/NULL;
152 RAISE;
153 END;
154 BEGIN
155 IF P_WIP = 2 THEN
156 IF P_SORT_ID = 1 THEN
157 P_MAT_TABLE_FROM := ', mtl_system_items msi';
158 ELSIF P_SORT_ID = 2 THEN
159 P_MAT_TABLE_FROM := ', cst_accounting_event_types_v caetv';
160 ELSIF P_SORT_ID = 3 THEN
161 P_MAT_TABLE_FROM := ', mtl_txn_source_types mtst';
162 ELSE
163 P_MAT_TABLE_FROM := ' ';
164 END IF;
165 ELSE
166 P_MAT_TABLE_FROM := ' ';
167 END IF;
168 EXCEPTION
169 WHEN OTHERS THEN
170 /*SRW.MESSAGE(1
171 ,'Failed in p_mat_or_wip_table_from')*/NULL;
172 RAISE;
173 END;
174 BEGIN
175 IF P_WIP = 2 THEN
176 IF P_SORT_ID = 1 THEN
177 P_MAT_WHERE := 'AND mmt.inventory_item_id = msi.inventory_item_id
178 AND mmt.organization_id = msi.organization_id';
179 ELSIF P_SORT_ID = 2 THEN
180 P_MAT_WHERE := 'AND caetv.transaction_type_id = mmt.transaction_type_id
181 AND caetv.transaction_action_id = mmt.transaction_action_id
182 AND caetv.transaction_type_flag = ''' || 'INV' || '''';
183 ELSIF P_SORT_ID = 3 THEN
184 P_MAT_WHERE := 'AND mmt.transaction_source_type_id = mtst.transaction_source_type_id';
185 ELSE
186 P_MAT_WHERE := ' ';
187 END IF;
188 ELSE
189 P_MAT_WHERE := ' ';
190 END IF;
191 EXCEPTION
192 WHEN OTHERS THEN
193 /*SRW.MESSAGE(1
194 ,'Failed in p_mat_or_wip_where')*/NULL;
195 RAISE;
196 END;
197 BEGIN
198 IF P_WIP = 2 THEN
199 IF P_SORT_ID = 1 THEN
200 P_RCV_TABLE_FROM := ', mtl_system_items msi
201 , rcv_shipment_lines rsl';
202 ELSE
203 P_RCV_TABLE_FROM := ' ';
204 END IF;
205 ELSE
206 P_RCV_TABLE_FROM := ' ';
207 END IF;
208 EXCEPTION
209 WHEN OTHERS THEN
210 /*SRW.MESSAGE(1
211 ,'Failed in p_rcv_table_from')*/NULL;
212 RAISE;
213 END;
214 BEGIN
215 IF P_WIP = 2 THEN
216 IF P_SORT_ID = 1 THEN
217 P_RCV_WHERE := 'AND rt.shipment_line_id = rsl.shipment_line_id
218 AND rsl.item_id = msi.inventory_item_id(+)
219 AND cah.organization_id = msi.organization_id';
220 ELSE
221 P_RCV_WHERE := ' ';
222 END IF;
223 ELSE
224 P_RCV_WHERE := ' ';
225 END IF;
226 EXCEPTION
227 WHEN OTHERS THEN
228 /*SRW.MESSAGE(1
229 ,'Failed in p_rcv_where')*/NULL;
230 RAISE;
231 END;
232 RETURN (TRUE);
233 END AFTERPFORM;
234
235 --PROCEDURE FORMAT_QUANTITY(P_PRECISION IN NUMBER) IS
236 --BEGIN
237 -- SRW.ATTR.MASK := SRW.FORMATMASK_ATTR;
238 -- IF P_PRECISION = 0 THEN
239 -- SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0';
240 -- ELSIF P_PRECISION = 1 THEN
241 -- SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0.0';
242 -- ELSIF P_PRECISION = 3 THEN
243 -- SRW.ATTR.FORMATMASK := '-NN,NNN,NNN,NN0.000';
244 -- ELSIF P_PRECISION = 4 THEN
245 -- SRW.ATTR.FORMATMASK := '-N,NNN,NNN,NN0.0000';
246 -- ELSIF P_PRECISION = 5 THEN
247 -- SRW.ATTR.FORMATMASK := '-NNN,NNN,NN0.00000';
248 -- ELSIF P_PRECISION = 6 THEN
249 -- SRW.ATTR.FORMATMASK := '-NN,NNN,NN0.000000';
250 -- ELSIF P_PRECISION = 7 THEN
251 -- SRW.ATTR.FORMATMASK := '-NNNNNNNNNNN0';
252 -- ELSIF P_PRECISION = 8 THEN
253 -- SRW.ATTR.FORMATMASK := '-NNNNNNNNNNN0.0';
254 -- ELSIF P_PRECISION = 9 THEN
255 -- SRW.ATTR.FORMATMASK := '-NNNNNNNNNN0.00';
256 -- ELSIF P_PRECISION = 10 THEN
257 -- SRW.ATTR.FORMATMASK := '-NNNNNNNNNN0.000';
258 -- ELSIF P_PRECISION = 11 THEN
259 -- SRW.ATTR.FORMATMASK := '-NNNNNNNNN0.0000';
263 -- SRW.ATTR.FORMATMASK := '-NNNNNNN0.000000';
260 -- ELSIF P_PRECISION = 12 THEN
261 -- SRW.ATTR.FORMATMASK := '-NNNNNNNN0.00000';
262 -- ELSIF P_PRECISION = 13 THEN
264 -- ELSE
265 -- SRW.ATTR.FORMATMASK := '-NNN,NNN,NNN,NN0.00';
266 -- END IF;
267 /*SRW.SET_ATTR(0
268 ,SRW.ATTR)*/
269 -- NULL;
270 --END FORMAT_QUANTITY;
271
272 FUNCTION CF_NET_ACTIVITY_RFORMULA(NET_ACTIVITY IN NUMBER) RETURN NUMBER IS
273 BEGIN
274 RETURN (ROUND(NET_ACTIVITY
275 ,CP_PRECISION));
276 END CF_NET_ACTIVITY_RFORMULA;
277
278 FUNCTION CF_SUM_CREDITS_RFORMULA(SUM_CREDITS IN NUMBER) RETURN NUMBER IS
279 BEGIN
280 RETURN (ROUND(SUM_CREDITS
281 ,CP_PRECISION));
282 END CF_SUM_CREDITS_RFORMULA;
283
284 FUNCTION CF_SUM_DEBITS_RFORMULA(SUM_DEBITS IN NUMBER) RETURN NUMBER IS
285 BEGIN
286 RETURN (ROUND(SUM_DEBITS
287 ,CP_PRECISION));
288 END CF_SUM_DEBITS_RFORMULA;
289
290 FUNCTION CF_ACCT_TOT_RFORMULA(C_ACCT_TOT IN NUMBER) RETURN NUMBER IS
291 BEGIN
292 RETURN (ROUND(C_ACCT_TOT
293 ,CP_PRECISION));
294 END CF_ACCT_TOT_RFORMULA;
295
296 FUNCTION CF_CR_ACCT_TOT_RFORMULA(C_CR_ACCT_TOT IN NUMBER) RETURN NUMBER IS
297 BEGIN
298 RETURN (ROUND(C_CR_ACCT_TOT
299 ,CP_PRECISION));
300 END CF_CR_ACCT_TOT_RFORMULA;
301
302 FUNCTION CF_DR_ACCT_TOT_RFORMULA(C_DR_ACCT_TOT IN NUMBER) RETURN NUMBER IS
303 BEGIN
304 RETURN (ROUND(C_DR_ACCT_TOT
305 ,CP_PRECISION));
306 END CF_DR_ACCT_TOT_RFORMULA;
307
308 FUNCTION CF_CR_TOT_RFORMULA(C_CR_TOT IN NUMBER) RETURN NUMBER IS
309 BEGIN
310 RETURN (ROUND(C_CR_TOT
311 ,CP_PRECISION));
312 END CF_CR_TOT_RFORMULA;
313
314 FUNCTION CF_DR_TOT_RFORMULA(C_DR_TOT IN NUMBER) RETURN NUMBER IS
315 BEGIN
316 RETURN (ROUND(C_DR_TOT
317 ,CP_PRECISION));
318 END CF_DR_TOT_RFORMULA;
319
320 FUNCTION CF_REPORT_TOT_RFORMULA(C_REPORT_TOT IN NUMBER) RETURN NUMBER IS
321 BEGIN
322 RETURN (ROUND(C_REPORT_TOT
323 ,CP_PRECISION));
324 END CF_REPORT_TOT_RFORMULA;
325
326 FUNCTION CP_RESPONSIBILITY_P RETURN VARCHAR2 IS
327 BEGIN
328 RETURN CP_RESPONSIBILITY;
329 END CP_RESPONSIBILITY_P;
330
331 FUNCTION CP_REQUEST_TIME_P RETURN VARCHAR2 IS
332 BEGIN
333 RETURN CP_REQUEST_TIME;
334 END CP_REQUEST_TIME_P;
335
336 FUNCTION CP_APPLICATION_P RETURN VARCHAR2 IS
337 BEGIN
338 RETURN CP_APPLICATION;
339 END CP_APPLICATION_P;
340
341 FUNCTION CP_REQUESTED_BY_P RETURN VARCHAR2 IS
342 BEGIN
343 RETURN CP_REQUESTED_BY;
344 END CP_REQUESTED_BY_P;
345
346 FUNCTION CP_LEGAL_ENTITY_P RETURN VARCHAR2 IS
347 BEGIN
348 RETURN CP_LEGAL_ENTITY;
349 END CP_LEGAL_ENTITY_P;
350
351 FUNCTION CP_ACCT_ID_P RETURN NUMBER IS
352 BEGIN
353 RETURN CP_ACCT_ID;
354 END CP_ACCT_ID_P;
355
356 FUNCTION CP_COST_GROUP_P RETURN VARCHAR2 IS
357 BEGIN
358 RETURN CP_COST_GROUP;
359 END CP_COST_GROUP_P;
360
361 FUNCTION CP_COST_TYPE_P RETURN VARCHAR2 IS
362 BEGIN
363 RETURN CP_COST_TYPE;
364 END CP_COST_TYPE_P;
365
366 FUNCTION R_CURRENCY_CODE_P RETURN VARCHAR2 IS
367 BEGIN
368 RETURN R_CURRENCY_CODE;
369 END R_CURRENCY_CODE_P;
370
371 FUNCTION CP_PERIOD_NAME_P RETURN VARCHAR2 IS
372 BEGIN
373 RETURN CP_PERIOD_NAME;
374 END CP_PERIOD_NAME_P;
375
376 FUNCTION CP_PRECISION_P RETURN NUMBER IS
377 BEGIN
378 RETURN CP_PRECISION;
379 END CP_PRECISION_P;
380
381 FUNCTION C_item(C_item_field_p varchar2) RETURN varchar2 is
382 BEGIN
383 IF p_wip = 2
384 THEN
385 IF P_sort_id = 1
386 THEN
387
388 RETURN(C_item_field_p);
389 ELSE
390 RETURN(NULL);
391 END IF;
392 ELSE
393 RETURN(NULL);
394 END IF;
395 END C_item;
396
397 END BOM_CSTRPDSS_XMLP_PKG;
398
[Home] [Help]