1 PACKAGE BODY WIP_WIPDJORQ_XMLP_PKG AS
2 /* $Header: WIPDJORQB.pls 120.2 2010/11/24 10:58:46 javakat ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
6 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
7 /*SRW.USER_EXIT('FND FLEXSQL CODE="MSTK"
8 APPL_SHORT_NAME="INV" OUTPUT=":P_ASSY"
9 TABLEALIAS="MSI"
10 MODE="SELECT" DISPLAY="ALL"')*/NULL;
11 IF (P_FROM_COMP IS NOT NULL) THEN
12 IF (P_TO_COMP IS NOT NULL) THEN
13 NULL;
14 ELSE
15 NULL;
16 END IF;
17 ELSE
18 IF (P_TO_COMP IS NOT NULL) THEN
19 NULL;
20 END IF;
21 END IF;
22 RETURN (TRUE);
23 RETURN (TRUE);
24 END BEFOREREPORT;
25
26 FUNCTION AFTERREPORT RETURN BOOLEAN IS
27 BEGIN
28 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
29 RETURN (TRUE);
30 END AFTERREPORT;
31
32 FUNCTION LIMIT_JOBS RETURN CHARACTER IS
33 LIMIT_JOBS VARCHAR2(768);
34 BEGIN
35 IF (P_FROM_JOB IS NOT NULL) THEN
36 IF (P_TO_JOB IS NOT NULL) THEN
37 LIMIT_JOBS := ' AND WE.WIP_ENTITY_NAME BETWEEN ''' || REPLACE(P_FROM_JOB
38 ,''''
39 ,'''''') || ''' AND ''' || REPLACE(P_TO_JOB
40 ,''''
41 ,'''''') || '''';
42 ELSE
43 LIMIT_JOBS := ' AND WE.WIP_ENTITY_NAME >= ''' || REPLACE(P_FROM_JOB
44 ,''''
45 ,'''''') || '''';
46 END IF;
47 ELSE
48 IF (P_TO_JOB IS NOT NULL) THEN
49 LIMIT_JOBS := ' AND WE.WIP_ENTITY_NAME <= ''' || REPLACE(P_TO_JOB
50 ,''''
51 ,'''''') || '''';
52 ELSE
53 LIMIT_JOBS := ' ';
54 END IF;
55 END IF;
56 RETURN (LIMIT_JOBS);
57 END LIMIT_JOBS;
58
59 FUNCTION LIMIT_DATES RETURN CHARACTER IS
60 LIMIT_DATES VARCHAR2(120);
61 BEGIN
62 IF (P_FROM_DATE IS NOT NULL) THEN
63 IF (P_TO_DATE IS NOT NULL) THEN
64 LIMIT_DATES := ' AND WRO.date_required >= TO_DATE(''' || TO_CHAR(P_FROM_DATE
65 ,'YYYYMMDD') || ''',''YYYYMMDD'')' || ' AND WRO.date_required < TO_DATE(''' || TO_CHAR(P_TO_DATE + 1
66 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
67 ELSE
68 LIMIT_DATES := ' AND WRO.date_required >= TO_DATE(''' || TO_CHAR(P_FROM_DATE
69 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
70 END IF;
71 ELSE
72 IF (P_TO_DATE IS NOT NULL) THEN
73 LIMIT_DATES := ' AND WRO.date_required < TO_DATE(''' || TO_CHAR(P_TO_DATE + 1
74 ,'YYYYMMDD') || ''',''YYYYMMDD'')';
75 ELSE
76 LIMIT_DATES := ' ';
77 END IF;
78 END IF;
79 RETURN (LIMIT_DATES);
80 END LIMIT_DATES;
81
82 FUNCTION C_NETTABLE_CONSTRAINTFORMULA RETURN VARCHAR2 IS
83 BEGIN
84 IF (P_NETTABLE = 1) THEN
85 RETURN ('AND MSS.AVAILABILITY_TYPE(+) = 1');
86 ELSE
87 RETURN (' ');
88 END IF;
89 RETURN NULL;
90 END C_NETTABLE_CONSTRAINTFORMULA;
91
92 FUNCTION C_TOTAL_QTY_FORMULAFORMULA(C_TOTAL_QTY IN NUMBER
93 ,SUPPLY_FLAG IN NUMBER) RETURN NUMBER IS
94 BEGIN
95 RETURN NVL(C_TOTAL_QTY * SUPPLY_FLAG,0); --Fix for bug 10104729, Added nvl to retun 0 if the value is null
96 END C_TOTAL_QTY_FORMULAFORMULA;
97
98 FUNCTION C_QTY_SHORT_QOHFORMULA(C_QTY_SHORT_FORMULA IN NUMBER
99 ,C_OPEN_REQUIREMENTS IN VARCHAR2
100 ,AVAIL_QTY IN NUMBER) RETURN NUMBER IS
101 BEGIN
102 IF ((P_OPEN_SHORT = 1) AND (P_SORT_BY <> 9) AND (P_SORT_BY <> 13)) THEN
103 RETURN NVL(-1 * (C_QTY_SHORT_FORMULA - C_OPEN_REQUIREMENTS),0);
104 --Fix for bug 10104729, Added nvl to retun 0 if the value is null
105 ELSE
106 RETURN NVL(-1 * (AVAIL_QTY - C_OPEN_REQUIREMENTS),0);
107 --Fix for bug 10104729, Added nvl to retun 0 if the value is null
108 END IF;
109 RETURN NULL;
110 END C_QTY_SHORT_QOHFORMULA;
111
112 FUNCTION DEPARTMENT_LIMITER RETURN CHARACTER IS
113 LIMIT_DEPT VARCHAR2(80);
114 BEGIN
115 IF (P_FROM_DEPT IS NOT NULL) THEN
116 IF (P_TO_DEPT IS NOT NULL) THEN
117 LIMIT_DEPT := ' AND BOD.DEPARTMENT_CODE BETWEEN ''' || P_FROM_DEPT || ''' AND ''' || P_TO_DEPT || '''';
118 ELSE
119 LIMIT_DEPT := ' AND BOD.DEPARTMENT_CODE >= ''' || P_FROM_DEPT || '''';
120 END IF;
121 ELSE
122 IF (P_TO_DEPT IS NOT NULL) THEN
123 LIMIT_DEPT := ' AND BOD.DEPARTMENT_CODE <= ''' || P_TO_DEPT || '''';
124 ELSE
125 LIMIT_DEPT := ' ';
126 END IF;
127 END IF;
128 RETURN (LIMIT_DEPT);
129 END DEPARTMENT_LIMITER;
130
131 FUNCTION C_BREAK_JOBFORMULA(JOB IN VARCHAR2) RETURN VARCHAR2 IS
132 BEGIN
133 IF (P_SORT_BY = 11) THEN
134 RETURN (JOB);
135 ELSE
136 RETURN ('X');
137 END IF;
138 RETURN NULL;
139 END C_BREAK_JOBFORMULA;
140
141 FUNCTION C_BREAK_DATE_REQUIREDFORMULA(DATE_REQUIRED IN DATE) RETURN DATE IS
142 BEGIN
143 IF (P_SORT_BY = 5) THEN
144 RETURN (DATE_REQUIRED);
145 ELSE
146 RETURN (NULL);
147 END IF;
148 RETURN NULL;
149 END C_BREAK_DATE_REQUIREDFORMULA;
150
151 FUNCTION C_BREAK_DEPARTMENTFORMULA(DEPARTMENT IN VARCHAR2) RETURN VARCHAR2 IS
152 BEGIN
153 IF (P_SORT_BY = 12) THEN
154 RETURN (DEPARTMENT);
155 ELSE
156 RETURN (' ');
157 END IF;
158 RETURN NULL;
159 END C_BREAK_DEPARTMENTFORMULA;
160
161 FUNCTION C_LIMIT_COMPONENTSFORMULA RETURN VARCHAR2 IS
162 BEGIN
163 BEGIN
164 IF (P_FLEXWHERE IS NOT NULL) THEN
165 RETURN ('AND ');
166 ELSE
167 RETURN (' ');
168 END IF;
169 END;
170 RETURN NULL;
171 END C_LIMIT_COMPONENTSFORMULA;
172
173 FUNCTION C_BREAK_COMP_DISPFORMULA(COMPONENT IN VARCHAR2
174 ,C_COMPONENT_DISP IN VARCHAR2) RETURN VARCHAR2 IS
175 BEGIN
176 /*SRW.REFERENCE(COMPONENT)*/NULL;
177 /*SRW.REFERENCE(C_COMPONENT_DISP)*/NULL;
178 IF (P_SORT_BY = 9) OR (P_SORT_BY = 13) THEN
179 RETURN (C_COMPONENT_DISP);
180 ELSE
181 RETURN (' ');
182 END IF;
183 RETURN NULL;
184 END C_BREAK_COMP_DISPFORMULA;
185
186 FUNCTION NET_QTY_DETAILFORMULA(AVAILABILITY_TYPE IN NUMBER
187 ,QUANTITY_ON_HAND IN NUMBER) RETURN NUMBER IS
188 BEGIN
189 /*SRW.REFERENCE(AVAILABILITY_TYPE)*/NULL;
190 /*SRW.REFERENCE(QUANTITY_ON_HAND)*/NULL;
191 RETURN NVL((AVAILABILITY_TYPE * QUANTITY_ON_HAND),0);
192 --Fix for bug 10104729, Added nvl to retun 0 if the value is null
193 END NET_QTY_DETAILFORMULA;
194
195 FUNCTION AVAIL_QTYFORMULA(NET_QTY_COMP IN NUMBER
196 ,C_ITEM_TOTAL_QTY IN NUMBER) RETURN NUMBER IS
197 BEGIN
198 /*SRW.REFERENCE(NET_QTY_COMP)*/NULL;
199 /*SRW.REFERENCE(C_ITEM_TOTAL_QTY)*/NULL;
200 /*SRW.REFERENCE(P_NETTABLE)*/NULL;
201 IF (P_NETTABLE = 1) THEN
202 RETURN NVL(NET_QTY_COMP,0);
203 --Fix for bug 10104729, Added nvl to retun 0 if the value is null
204 ELSE
205 RETURN NVL(C_ITEM_TOTAL_QTY,0);
206 --Fix for bug 10104729, Added nvl to retun 0 if the value is null
207 END IF;
208 RETURN NULL;
209 END AVAIL_QTYFORMULA;
210
211 FUNCTION C_COMPONENT_SORTFORMULA(COMPONENT IN VARCHAR2
212 ,C_COMPONENT_DISP IN VARCHAR2
213 ,C_COMPONENT_SORT IN VARCHAR2) RETURN VARCHAR2 IS
214 BEGIN
215 /*SRW.REFERENCE(COMPONENT)*/NULL;
216 /*SRW.REFERENCE(C_COMPONENT_DISP)*/NULL;
217 RETURN (C_COMPONENT_SORT);
218 END C_COMPONENT_SORTFORMULA;
219
220 FUNCTION ZEROFORMULA RETURN NUMBER IS
221 BEGIN
222 RETURN (0);
223 END ZEROFORMULA;
224
225 FUNCTION LIMIT_PLANNERS RETURN CHARACTER IS
226 LIMIT_PLANNERS VARCHAR2(240);
227 BEGIN
228 IF (P_FROM_PLANNER IS NOT NULL) THEN
229 IF (P_TO_PLANNER IS NOT NULL) THEN
230 LIMIT_PLANNERS := ' AND MSI2.PLANNER_CODE BETWEEN ''' || P_FROM_PLANNER || ''' AND ''' || P_TO_PLANNER || '''';
231 ELSE
232 LIMIT_PLANNERS := ' AND MSI2.PLANNER_CODE >= ''' || P_FROM_PLANNER || '''';
233 END IF;
234 ELSE
235 IF (P_TO_PLANNER IS NOT NULL) THEN
236 LIMIT_PLANNERS := ' AND MSI2.PLANNER_CODE <= ''' || P_TO_PLANNER || '''';
237 ELSE
238 LIMIT_PLANNERS := ' ';
239 END IF;
240 END IF;
241 RETURN (LIMIT_PLANNERS);
242 END LIMIT_PLANNERS;
243
244 FUNCTION LIMIT_BUYER RETURN CHARACTER IS
245 LIMIT_BUYER VARCHAR2(240);
246 BEGIN
247 IF (P_FROM_BUYER IS NOT NULL) THEN
248 IF (P_TO_BUYER IS NOT NULL) THEN
249 LIMIT_BUYER := ' AND EMP.FULL_NAME BETWEEN ''' || REPLACE(P_FROM_BUYER
250 ,''''
251 ,'''''') || ''' AND ''' || REPLACE(P_TO_BUYER
252 ,''''
253 ,'''''') || '''';
254 ELSE
255 LIMIT_BUYER := ' AND EMP.FULL_NAME >= ''' || REPLACE(P_FROM_BUYER
256 ,''''
257 ,'''''') || '''';
258 END IF;
259 ELSE
260 IF (P_TO_BUYER IS NOT NULL) THEN
261 LIMIT_BUYER := ' AND EMP.FULL_NAME <= ''' || REPLACE(P_TO_BUYER
262 ,''''
263 ,'''''') || '''';
264 ELSE
265 LIMIT_BUYER := ' ';
266 END IF;
267 END IF;
268 RETURN (LIMIT_BUYER);
269 END LIMIT_BUYER;
270
271 FUNCTION C_BREAK_ASSY_DISPFORMULA(C_ASSY IN VARCHAR2
272 ,C_ASSY_DISP IN VARCHAR2) RETURN VARCHAR2 IS
273 BEGIN
274 /*SRW.REFERENCE(C_ASSY)*/NULL;
275 /*SRW.REFERENCE(C_ASSY_DISP)*/NULL;
276 IF (P_SORT_BY = 11) THEN
277 RETURN (C_ASSY_DISP);
278 ELSE
279 RETURN ('X');
280 END IF;
281 RETURN NULL;
282 END C_BREAK_ASSY_DISPFORMULA;
283
284 FUNCTION MASTER_BREAK_FUNC(COMPONENT IN VARCHAR2
285 ,C_COMPONENT_DISP IN VARCHAR2
286 ,C_COMPONENT_SORT IN VARCHAR2
287 ,JOB IN VARCHAR2
288 ,DEPARTMENT IN VARCHAR2
289 ,MAKE_BUY IN VARCHAR2
290 ,BUYER_NAME IN VARCHAR2
291 ,PLANNER_CODE IN VARCHAR2
292 ,DATE_REQUIRED IN DATE) RETURN CHARACTER IS
293 TEMP VARCHAR2(1250);
294 BEGIN
295 /*SRW.REFERENCE(COMPONENT)*/NULL;
296 /*SRW.REFERENCE(C_COMPONENT_DISP)*/NULL;
297 /*SRW.REFERENCE(C_COMPONENT_SORT)*/NULL;
298 /*SRW.REFERENCE(JOB)*/NULL;
299 /*SRW.REFERENCE(DEPARTMENT)*/NULL;
300 /*SRW.REFERENCE(MAKE_BUY)*/NULL;
301 /*SRW.REFERENCE(BUYER_NAME)*/NULL;
302 /*SRW.REFERENCE(PLANNER_CODE)*/NULL;
303 /*SRW.REFERENCE(DATE_REQUIRED)*/NULL;
304 IF (P_SORT_BY = 9) THEN
305 TEMP := C_COMPONENT_SORT;
306 ELSE
307 IF (P_SORT_BY = 5) THEN
308 TEMP := LPAD(TO_CHAR(DATE_REQUIRED
309 ,'J')
310 ,10);
311 ELSE
312 IF (P_SORT_BY = 11) THEN
313 TEMP := JOB;
314 ELSE
315 IF (P_SORT_BY = 12) THEN
316 TEMP := DEPARTMENT;
317 ELSE
318 TEMP := RPAD(MAKE_BUY
319 ,10) || RPAD(BUYER_NAME
320 ,240) || RPAD(PLANNER_CODE
321 ,40) || C_COMPONENT_SORT;
322 END IF;
323 END IF;
324 END IF;
325 END IF;
326 RETURN (TEMP);
327 END MASTER_BREAK_FUNC;
328
329 FUNCTION C_FIRST_SORTFORMULA RETURN VARCHAR2 IS
330 BEGIN
331 IF (P_SORT_BY = 5) THEN
332 RETURN ('WRO.Date_Required');
333 ELSE
334 IF (P_SORT_BY = 11) THEN
335 RETURN ('WE.WIP_ENTITY_NAME');
336 ELSE
337 IF (P_SORT_BY = 12) THEN
338 RETURN ('BOD.DEPARTMENT_CODE');
339 ELSE
340 RETURN ('''X''');
341 END IF;
342 END IF;
343 END IF;
344 RETURN NULL;
345 END C_FIRST_SORTFORMULA;
346
347 FUNCTION C_TOT_QTY_SHORTFORMULA(C_TOTAL_AVAIL_QTY IN VARCHAR2
348 ,C_TOTAL_AVAIL_NET_QTY IN VARCHAR2
349 ,C_TOT_QTY_OPEN IN VARCHAR2) RETURN NUMBER IS
350 BEGIN
351 /*SRW.REFERENCE(C_TOTAL_AVAIL_QTY)*/NULL;
352 /*SRW.REFERENCE(C_TOTAL_AVAIL_NET_QTY)*/NULL;
353 /*SRW.REFERENCE(C_TOT_QTY_OPEN)*/NULL;
354 IF (P_NETTABLE = 1) THEN
355 RETURN NVL((C_TOT_QTY_OPEN - C_TOTAL_AVAIL_NET_QTY),0);
356 --Fix for bug 10104729, Added nvl to retun 0 if the value is null
357 ELSE
358 RETURN NVL((C_TOT_QTY_OPEN - C_TOTAL_AVAIL_QTY),0);
359 --Fix for bug 10104729, Added nvl to retun 0 if the value is null
360 END IF;
361 RETURN NULL;
362 END C_TOT_QTY_SHORTFORMULA;
363
364 FUNCTION C_BREAK_COMP_DESCFORMULA(COMP_DESC IN VARCHAR2) RETURN VARCHAR2 IS
365 BEGIN
366 IF ((P_SORT_BY = 9) OR (P_SORT_BY = 13)) THEN
367 RETURN (COMP_DESC);
368 ELSE
369 RETURN ('X');
370 END IF;
371 RETURN NULL;
372 END C_BREAK_COMP_DESCFORMULA;
373
374 FUNCTION C_DISPLAY_FORMULAFORMULA(C_QTY_SHORT_QOH IN NUMBER) RETURN NUMBER IS
375 BEGIN
376 IF ((P_OPEN_SHORT = 1) OR (P_OPEN_SHORT = 2)) AND (C_QTY_SHORT_QOH <= 0) THEN
377 RETURN (0);
378 ELSE
379 RETURN (1);
380 END IF;
381 RETURN NULL;
382 END C_DISPLAY_FORMULAFORMULA;
383
384 FUNCTION AFTERPFORM RETURN BOOLEAN IS
385 BEGIN
386 IF P_FROM_COMP IS NOT NULL OR P_TO_COMP IS NOT NULL THEN
387 P_ITEM_ORG := 'and MSI2.ORGANIZATION_ID = :P_ORGANIZATION_ID';
388 END IF;
389 IF P_SCHEDULE_GROUP IS NOT NULL THEN
390 P_SG_OUTER := ' ';
391 END IF;
392 RETURN (TRUE);
393 END AFTERPFORM;
394
395 FUNCTION LIMIT_BULK_SUPPLY_TYPE RETURN CHARACTER IS
396 LIMIT_BULK_SUPPLY_TYPE VARCHAR2(100);
397 BEGIN
398 IF (P_INCLUDE_BULK = 1) THEN
399 LIMIT_BULK_SUPPLY_TYPE := ' ';
400 ELSE
401 LIMIT_BULK_SUPPLY_TYPE := 'AND WRO.WIP_SUPPLY_TYPE <> 4 ';
402 END IF;
403 RETURN (LIMIT_BULK_SUPPLY_TYPE);
404 END LIMIT_BULK_SUPPLY_TYPE;
405
406 FUNCTION LIMIT_VENDOR_SUPPLY_TYPE RETURN CHARACTER IS
407 LIMIT_VENDOR_SUPPLY_TYPE VARCHAR2(100);
408 BEGIN
409 IF (P_INCLUDE_VENDOR = 1) THEN
410 LIMIT_VENDOR_SUPPLY_TYPE := ' ';
411 ELSE
412 LIMIT_VENDOR_SUPPLY_TYPE := 'AND WRO.WIP_SUPPLY_TYPE <> 5 ';
413 END IF;
414 RETURN (LIMIT_VENDOR_SUPPLY_TYPE);
415 END LIMIT_VENDOR_SUPPLY_TYPE;
416
417 FUNCTION C_LIMITER RETURN VARCHAR2 IS
418 C_OUT VARCHAR2(200);
419 BEGIN
420 IF P_SCHEDULE_GROUP IS NOT NULL THEN
421 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME = ''' || P_SCHEDULE_GROUP || '''';
422 ELSE
423 C_OUT := ' ';
424 END IF;
425 RETURN (C_OUT);
426 END C_LIMITER;
427
428 FUNCTION C_LIMIT_DATE_REQFORMULA RETURN CHAR IS
429 LIMIT_DATE_REQ VARCHAR2(120) := ' ';
430 BEGIN
431 IF (P_REQ_END_DATE IS NOT NULL) THEN
432 LIMIT_DATE_REQ := ' AND WRO.DATE_REQUIRED < TO_DATE(''' || TO_CHAR(P_REQ_END_DATE
433 ,'YYYYMMDD') || ''',''YYYYMMDD'') + 1 ';
434 END IF;
435 RETURN (LIMIT_DATE_REQ);
436 END C_LIMIT_DATE_REQFORMULA;
437
438 END WIP_WIPDJORQ_XMLP_PKG;
439
[Home] [Help]