1 PACKAGE BODY WIP_WIPRDJVR_XMLP_PKG AS
2 /* $Header: WIPRDJVRB.pls 120.1 2008/01/31 12:32:01 npannamp noship $ */
3 FUNCTION DISP_CURRENCYFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 RETURN (REPORT_OPTION || ' (' || CURRENCY_CODE || ')');
6 END DISP_CURRENCYFORMULA;
7
8 FUNCTION ORG_NAME_HDRFORMULA RETURN VARCHAR2 IS
9 BEGIN
10 RETURN (ORG_NAME);
11 END ORG_NAME_HDRFORMULA;
12
13 FUNCTION TOT_CST_INC_STD_CSTFORMULA(TOT_REQ_JOB_STD IN NUMBER
14 ,TOT_RES_STD_COST IN NUMBER
15 ,TOT_RES_OVR_STD_COST IN NUMBER
16 ,TOT_MV_OVR_STD_COST IN NUMBER) RETURN NUMBER IS
17 BEGIN
18 /*SRW.REFERENCE(TOT_REQ_JOB_STD)*/NULL;
19 /*SRW.REFERENCE(TOT_RES_STD_COST)*/NULL;
20 /*SRW.REFERENCE(TOT_RES_OVR_STD_COST)*/NULL;
21 /*SRW.REFERENCE(TOT_MV_OVR_STD_COST)*/NULL;
22 RETURN (NVL(TOT_REQ_JOB_STD
23 ,0) + NVL(TOT_RES_STD_COST
24 ,0) + NVL(TOT_RES_OVR_STD_COST
25 ,0) + NVL(TOT_MV_OVR_STD_COST
26 ,0));
27 END TOT_CST_INC_STD_CSTFORMULA;
28
29 FUNCTION TOT_CST_INC_APP_CSTFORMULA(TOT_ACT_ISS_STD IN NUMBER
30 ,TOT_RES_APP_COST IN NUMBER
31 ,TOT_RES_OVR_APP_COST IN NUMBER
32 ,TOT_MV_OVR_APP_COST IN NUMBER) RETURN NUMBER IS
33 BEGIN
34 /*SRW.REFERENCE(TOT_ACT_ISS_STD)*/NULL;
35 /*SRW.REFERENCE(TOT_RES_APP_COST)*/NULL;
36 /*SRW.REFERENCE(TOT_RES_OVR_APP_COST)*/NULL;
37 /*SRW.REFERENCE(TOT_MV_OVR_APP_COST)*/NULL;
38 RETURN (NVL(TOT_ACT_ISS_STD
39 ,0) + NVL(TOT_RES_APP_COST
40 ,0) + NVL(TOT_RES_OVR_APP_COST
41 ,0) + NVL(TOT_MV_OVR_APP_COST
42 ,0));
43 END TOT_CST_INC_APP_CSTFORMULA;
44
45 FUNCTION TOT_CST_INC_EFF_VARFORMULA(TOT_USG_VAR IN NUMBER
46 ,TOT_EFF_VAR IN NUMBER
47 ,TOT_RES_OVR_EFF_VAR IN NUMBER
48 ,TOT_MV_OVR_EFF_VAR IN NUMBER) RETURN NUMBER IS
49 BEGIN
50 /*SRW.REFERENCE(TOT_USG_VAR)*/NULL;
51 /*SRW.REFERENCE(TOT_EFF_VAR)*/NULL;
52 /*SRW.REFERENCE(TOT_RES_OVR_EFF_VAR)*/NULL;
53 /*SRW.REFERENCE(TOT_MV_OVR_EFF_VAR)*/NULL;
54 RETURN (NVL(TOT_USG_VAR
55 ,0) + NVL(TOT_EFF_VAR
56 ,0) + NVL(TOT_RES_OVR_EFF_VAR
57 ,0) + NVL(TOT_MV_OVR_EFF_VAR
58 ,0));
59 END TOT_CST_INC_EFF_VARFORMULA;
60
61 FUNCTION TOT_JOB_BALANCE_CSTFORMULA(TOT_CST_INC_APP_CST IN NUMBER
62 ,TOT_SCP_AND_COMP_CST IN NUMBER
63 ,TOT_CLOSE_TRX_CST IN NUMBER) RETURN NUMBER IS
64 BEGIN
65 /*SRW.REFERENCE(TOT_CST_INC_APP_CST)*/NULL;
66 /*SRW.REFERENCE(TOT_SCP_AND_COMP_CST)*/NULL;
67 /*SRW.REFERENCE(TOT_CLOSE_TRX_CST)*/NULL;
68 RETURN (NVL(TOT_CST_INC_APP_CST
69 ,0) + NVL(TOT_SCP_AND_COMP_CST
70 ,0) + NVL(TOT_CLOSE_TRX_CST
71 ,0));
72 END TOT_JOB_BALANCE_CSTFORMULA;
73
74 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
75 BEGIN
76 DECLARE
77 STD_ORG_COUNT NUMBER;
78 BEGIN
79 SELECT
80 COUNT(*)
81 INTO STD_ORG_COUNT
82 FROM
83 MTL_PARAMETERS
84 WHERE ORGANIZATION_ID = ORG_ID
85 AND PRIMARY_COST_METHOD = 1;
86 IF STD_ORG_COUNT < 1 THEN
87 SET_NAME('BOM'
88 ,'CST_STD_ORG_REPORT_ONLY');
89 /*SRW.MESSAGE(24201
90 ,GET)*/NULL;
91 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
92 END IF;
93
94 select id_flex_num_cl
95 into item_id_flex_num
96 from(
97 SELECT fifst.id_flex_num id_flex_num_cl
98 FROM fnd_id_flex_structures fifst
99 WHERE fifst.application_id = 401
100 AND fifst.id_flex_code = 'MSTK'
101 AND fifst.enabled_flag = 'Y'
102 AND fifst.freeze_flex_definition_flag = 'Y'
103 ORDER BY fifst.id_flex_num)
104 where rownum<2;
105
106 SELECT
107 OOD.ORGANIZATION_NAME,
108 SOB.CURRENCY_CODE,
109 FC.EXTENDED_PRECISION,
110 FC.PRECISION,
111 RPT_SORT_OPT.MEANING,
112 RPT_RUN_OPT.MEANING,
113 ML1.MEANING,
114 ML2.MEANING
115 INTO ORG_NAME,CURRENCY_CODE,EXT_PRECISION,PRECISION,REPORT_SORT,REPORT_OPTION,C_INCLUDE_BULK,C_INCLUDE_VENDOR
116 FROM
117 FND_CURRENCIES FC,
118 GL_SETS_OF_BOOKS SOB,
119 ORG_ORGANIZATION_DEFINITIONS OOD,
120 MFG_LOOKUPS RPT_SORT_OPT,
121 MFG_LOOKUPS RPT_RUN_OPT,
122 MFG_LOOKUPS ML1,
123 MFG_LOOKUPS ML2
124 WHERE OOD.ORGANIZATION_ID = ORG_ID
125 AND OOD.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
126 AND SOB.CURRENCY_CODE = FC.CURRENCY_CODE
127 AND FC.ENABLED_FLAG = 'Y'
128 AND RPT_SORT_OPT.LOOKUP_TYPE = 'CST_WIP_REPORT_SORT'
129 AND RPT_SORT_OPT.LOOKUP_CODE = REPORT_SORT_OPT
130 AND RPT_RUN_OPT.LOOKUP_TYPE = 'CST_WIP_VALUE_REPORT_TYPE'
131 AND RPT_RUN_OPT.LOOKUP_CODE = REPORT_RUN_OPT
132 AND ML1.LOOKUP_CODE = NVL(P_INCLUDE_BULK
133 ,2)
134 AND ML1.LOOKUP_TYPE = 'SYS_YES_NO'
135 AND ML2.LOOKUP_CODE = NVL(P_INCLUDE_VENDOR
136 ,2)
137 AND ML2.LOOKUP_TYPE = 'SYS_YES_NO';
138 IF CLASS_TYPE IS NULL THEN
139 CLASS_TYPE_NAME := '';
140 ELSE
141 SELECT
142 CLS_TYPE.MEANING
143 INTO CLASS_TYPE_NAME
144 FROM
145 MFG_LOOKUPS CLS_TYPE
146 WHERE CLS_TYPE.LOOKUP_TYPE = 'WIP_CLASS_TYPE'
147 AND CLS_TYPE.LOOKUP_CODE = CLASS_TYPE;
148 END IF;
149 IF STATUS_TYPE IS NULL THEN
150 STATUS_TYPE_NAME := '';
151 ELSE
152 SELECT
153 STS_TYPE.MEANING
154 INTO STATUS_TYPE_NAME
155 FROM
156 MFG_LOOKUPS STS_TYPE
157 WHERE STS_TYPE.LOOKUP_TYPE = 'WIP_JOB_STATUS'
158 AND STS_TYPE.LOOKUP_CODE = STATUS_TYPE;
159 END IF;
160 IF (JOB_FROM IS NULL) AND (JOB_TO IS NULL) THEN
161 WHERE_JOB := '1 = 1';
162 ELSIF (JOB_FROM IS NULL) AND (JOB_TO IS NOT NULL) THEN
163 WHERE_JOB := 'wp.wip_entity_name <= ''' || REPLACE(JOB_TO
164 ,''''
165 ,'''''') || '''';
166 ELSIF (JOB_FROM IS NOT NULL) AND (JOB_TO IS NULL) THEN
167 WHERE_JOB := 'wp.wip_entity_name >= ''' || REPLACE(JOB_FROM
168 ,''''
169 ,'''''') || '''';
170 ELSE
171 WHERE_JOB := 'wp.wip_entity_name BETWEEN ''' || REPLACE(JOB_FROM
172 ,''''
173 ,'''''') || ''' AND ''' || REPLACE(JOB_TO
174 ,''''
175 ,'''''') || '''';
176 END IF;
177 IF (CLASS_FROM IS NULL) AND (CLASS_TO IS NULL) THEN
178 WHERE_CLASS := '1 = 1';
179 ELSIF (CLASS_FROM IS NULL) AND (CLASS_TO IS NOT NULL) THEN
180 WHERE_CLASS := 'wdj.class_code <= ''' || CLASS_TO || '''';
181 ELSIF (CLASS_FROM IS NOT NULL) AND (CLASS_TO IS NULL) THEN
182 WHERE_CLASS := 'wdj.class_code >= ''' || CLASS_FROM || '''';
183 ELSE
184 WHERE_CLASS := 'wdj.class_code BETWEEN ''' || CLASS_FROM || ''' AND ''' || CLASS_TO || '''';
185 END IF;
186 IF (P_PROJECT_ID IS NOT NULL) THEN
187 P_PROJECT_WHERE := 'wdj.project_id = ' || P_PROJECT_ID;
188 P_PROJ_WHERE := 'wt.project_id =' || P_PROJECT_ID;
189 END IF;
190 IF REPORT_SORT_OPT = 1 THEN
191 REPORT_SORT_BY_BEF := 'wp.wip_entity_name, ';
192 REPORT_SORT_BY_AFT := ' ';
193 ELSIF REPORT_SORT_OPT = 2 THEN
194 REPORT_SORT_BY_BEF := ' ';
195 REPORT_SORT_BY_AFT := ', wp.wip_entity_name';
196 ELSIF REPORT_SORT_OPT = 3 THEN
197 REPORT_SORT_BY_BEF := 'ml_class_type.meaning, ';
198 REPORT_SORT_BY_AFT := ', wp.wip_entity_name';
199 END IF;
200 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
201 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
202 /*SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" SET=":ORG_ID"
203 APPL_SHORT_NAME="INV" OUTPUT=":P_FLEXDATA_ITEM"
204 MODE="SELECT" DISPLAY="ALL"
205 TABLEALIAS="MSI"')*/NULL;
206 IF (P_FROM_ITEM IS NOT NULL) THEN
207 IF (P_TO_ITEM IS NOT NULL) THEN
208 NULL;
209 ELSE
210 NULL;
211 END IF;
212 ELSE
213 IF (P_TO_ITEM IS NOT NULL) THEN
214 NULL;
215 ELSE
216 P_WHERE_ITEM := '1 = 1';
217 END IF;
218 END IF;
219 END;
220 RETURN (TRUE);
221 END BEFOREREPORT;
222
223 FUNCTION AFTERREPORT RETURN BOOLEAN IS
224 BEGIN
225 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
226 RETURN (TRUE);
227 END AFTERREPORT;
228
229 FUNCTION AFTERPFORM RETURN BOOLEAN IS
230 BEGIN
231 IF P_TO_ITEM IS NOT NULL OR P_FROM_ITEM IS NOT NULL THEN
232 P_OUTER := ' ';
233 END IF;
234 IF SUBMITTED_BY = 'SRS' THEN
235 P_SUBMISSION_TYPE := 'and wac.class_type = :class_type';
236 ELSE
237 P_SUBMISSION_TYPE := 'and wac.class_type IN (1, 3, 5, 6) and
238 wp.wip_entity_id in
239 (select wip_entity_id
240 from wip_dj_close_temp
241 where group_id = :GROUP_ID)';
242 END IF;
243 RETURN (TRUE);
244 END AFTERPFORM;
245
246 FUNCTION C_MAT_DISPFORMULA(WIP_SUPPLY_TYPE IN NUMBER
247 ,QTY_ISSUED IN NUMBER) RETURN NUMBER IS
248 BEGIN
249 IF (WIP_SUPPLY_TYPE <> 4 AND WIP_SUPPLY_TYPE <> 5) THEN
250 RETURN (1);
251 ELSE
252 IF (WIP_SUPPLY_TYPE = 4) THEN
253 IF (P_INCLUDE_BULK = 1) THEN
254 RETURN (1);
255 ELSE
256 IF (QTY_ISSUED <> 0) THEN
257 RETURN (1);
258 ELSE
259 RETURN (0);
260 END IF;
261 END IF;
262 ELSIF (WIP_SUPPLY_TYPE = 5) THEN
263 IF (P_INCLUDE_VENDOR = 1) THEN
264 RETURN (1);
265 ELSE
266 IF (QTY_ISSUED <> 0) THEN
267 RETURN (1);
268 ELSE
269 RETURN (0);
270 END IF;
271 END IF;
272 END IF;
273 END IF;
274 RETURN NULL;
275 END C_MAT_DISPFORMULA;
276
277 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
278 BEGIN
279 RETURN (TRUE);
280 END BEFOREPFORM;
281
282 FUNCTION REPORT_SORT_P RETURN VARCHAR2 IS
283 BEGIN
284 RETURN REPORT_SORT;
285 END REPORT_SORT_P;
286
287 FUNCTION WHERE_CLASS_P RETURN VARCHAR2 IS
288 BEGIN
289 RETURN WHERE_CLASS;
290 END WHERE_CLASS_P;
291
292 FUNCTION REPORT_SORT_BY_AFT_P RETURN VARCHAR2 IS
293 BEGIN
294 RETURN REPORT_SORT_BY_AFT;
295 END REPORT_SORT_BY_AFT_P;
296
297 FUNCTION CLASS_TYPE_NAME_P RETURN VARCHAR2 IS
298 BEGIN
299 RETURN CLASS_TYPE_NAME;
300 END CLASS_TYPE_NAME_P;
301
302 FUNCTION REPORT_SORT_BY_BEF_P RETURN VARCHAR2 IS
303 BEGIN
304 RETURN REPORT_SORT_BY_BEF;
305 END REPORT_SORT_BY_BEF_P;
306
307 FUNCTION PRECISION_P RETURN NUMBER IS
308 BEGIN
309 RETURN PRECISION;
310 END PRECISION_P;
311
312 FUNCTION EXT_PRECISION_P RETURN NUMBER IS
313 BEGIN
314 RETURN EXT_PRECISION;
315 END EXT_PRECISION_P;
316
317 FUNCTION CURRENCY_CODE_P RETURN VARCHAR2 IS
318 BEGIN
319 RETURN CURRENCY_CODE;
320 END CURRENCY_CODE_P;
321
322 FUNCTION REPORT_OPTION_P RETURN VARCHAR2 IS
323 BEGIN
324 RETURN REPORT_OPTION;
325 END REPORT_OPTION_P;
326
327 FUNCTION STATUS_TYPE_NAME_P RETURN VARCHAR2 IS
328 BEGIN
329 RETURN STATUS_TYPE_NAME;
330 END STATUS_TYPE_NAME_P;
331
332 FUNCTION ORG_NAME_P RETURN VARCHAR2 IS
333 BEGIN
334 RETURN ORG_NAME;
335 END ORG_NAME_P;
336
337 FUNCTION C_INCLUDE_BULK_P RETURN VARCHAR2 IS
338 BEGIN
339 RETURN C_INCLUDE_BULK;
340 END C_INCLUDE_BULK_P;
341
342 FUNCTION C_INCLUDE_VENDOR_P RETURN VARCHAR2 IS
343 BEGIN
344 RETURN C_INCLUDE_VENDOR;
345 END C_INCLUDE_VENDOR_P;
346
347 FUNCTION WHERE_JOB_P RETURN VARCHAR2 IS
348 BEGIN
349 RETURN WHERE_JOB;
350 END WHERE_JOB_P;
351
352 PROCEDURE SET_NAME(APPLICATION IN VARCHAR2
353 ,NAME IN VARCHAR2) IS
354 BEGIN
355 /*STPROC.INIT('begin FND_MESSAGE.SET_NAME(:APPLICATION, :NAME); end;');
356 STPROC.BIND_I(APPLICATION);
357 STPROC.BIND_I(NAME);
358 STPROC.EXECUTE;*/ null;
359 END SET_NAME;
360
361 PROCEDURE SET_TOKEN(TOKEN IN VARCHAR2
362 ,VALUE IN VARCHAR2
363 ,TRANSLATE IN BOOLEAN) IS
364 BEGIN
365 /* STPROC.INIT('declare TRANSLATE BOOLEAN; begin TRANSLATE := sys.diutil.int_to_bool(:TRANSLATE); FND_MESSAGE.SET_TOKEN(:TOKEN, :VALUE, TRANSLATE); end;');
366 STPROC.BIND_I(TRANSLATE);
367 STPROC.BIND_I(TOKEN);
368 STPROC.BIND_I(VALUE);
369 STPROC.EXECUTE;*/
370 null;
371 END SET_TOKEN;
372
373 PROCEDURE RETRIEVE(MSGOUT OUT NOCOPY VARCHAR2) IS
374 BEGIN
375 /*STPROC.INIT('begin FND_MESSAGE.RETRIEVE(:MSGOUT); end;');
376 STPROC.BIND_O(MSGOUT);
377 STPROC.EXECUTE;
378 STPROC.RETRIEVE(1
379 ,MSGOUT);*/ null;
380 END RETRIEVE;
381
382 PROCEDURE CLEAR IS
383 BEGIN
384 /*STPROC.INIT('begin FND_MESSAGE.CLEAR; end;');
385 STPROC.EXECUTE;*/ null;
386 END CLEAR;
387
388 FUNCTION GET_STRING(APPIN IN VARCHAR2
389 ,NAMEIN IN VARCHAR2) RETURN VARCHAR2 IS
390 X0 VARCHAR2(2000);
391 BEGIN
392 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_STRING(:APPIN, :NAMEIN); end;');
393 STPROC.BIND_O(X0);
394 STPROC.BIND_I(APPIN);
395 STPROC.BIND_I(NAMEIN);
396 STPROC.EXECUTE;
397 STPROC.RETRIEVE(1
398 ,X0);*/ null;
399 RETURN X0;
400 END GET_STRING;
401
402 FUNCTION GET_NUMBER(APPIN IN VARCHAR2
403 ,NAMEIN IN VARCHAR2) RETURN NUMBER IS
404 X0 NUMBER;
405 BEGIN
406 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_NUMBER(:APPIN, :NAMEIN); end;');
407 STPROC.BIND_O(X0);
408 STPROC.BIND_I(APPIN);
409 STPROC.BIND_I(NAMEIN);
410 STPROC.EXECUTE;
411 STPROC.RETRIEVE(1
412 ,X0);*/ null;
413 RETURN X0;
414 END GET_NUMBER;
415
416 FUNCTION GET RETURN VARCHAR2 IS
417 X0 VARCHAR2(2000);
418 BEGIN
419 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET; end;');
420 STPROC.BIND_O(X0);
421 STPROC.EXECUTE;
422 STPROC.RETRIEVE(1
423 ,X0);*/ null;
424 RETURN X0;
425 END GET;
426
427 FUNCTION GET_ENCODED RETURN VARCHAR2 IS
428 X0 VARCHAR2(2000);
429 BEGIN
430 /*STPROC.INIT('begin :X0 := FND_MESSAGE.GET_ENCODED; end;');
431 STPROC.BIND_O(X0);
432 STPROC.EXECUTE;
433 STPROC.RETRIEVE(1
434 ,X0);*/ null;
435 RETURN X0;
436 END GET_ENCODED;
437
438 PROCEDURE PARSE_ENCODED(ENCODED_MESSAGE IN VARCHAR2
439 ,APP_SHORT_NAME OUT NOCOPY VARCHAR2
440 ,MESSAGE_NAME OUT NOCOPY VARCHAR2) IS
441 BEGIN
442 /*STPROC.INIT('begin FND_MESSAGE.PARSE_ENCODED(:ENCODED_MESSAGE, :APP_SHORT_NAME, :MESSAGE_NAME); end;');
443 STPROC.BIND_I(ENCODED_MESSAGE);
444 STPROC.BIND_O(APP_SHORT_NAME);
445 STPROC.BIND_O(MESSAGE_NAME);
446 STPROC.EXECUTE;
447 STPROC.RETRIEVE(2
448 ,APP_SHORT_NAME);
449 STPROC.RETRIEVE(3
450 ,MESSAGE_NAME);*/ null;
451 END PARSE_ENCODED;
452
453 PROCEDURE SET_ENCODED(ENCODED_MESSAGE IN VARCHAR2) IS
454 BEGIN
455 /*STPROC.INIT('begin FND_MESSAGE.SET_ENCODED(:ENCODED_MESSAGE); end;');
456 STPROC.BIND_I(ENCODED_MESSAGE);
457 STPROC.EXECUTE;*/ null;
458 END SET_ENCODED;
459
460 PROCEDURE RAISE_ERROR IS
461 BEGIN
462 /* STPROC.INIT('begin FND_MESSAGE.RAISE_ERROR; end;');
463 STPROC.EXECUTE;*/ null;
464 END RAISE_ERROR;
465
466 END WIP_WIPRDJVR_XMLP_PKG;
467
468
[Home] [Help]