1 PACKAGE BODY WIP_WIPDJORQ_XMLP_PKG AS
2 /* $Header: WIPDJORQB.pls 120.1 2008/01/31 12:18:20 npannamp noship $ */
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 C_TOTAL_QTY * SUPPLY_FLAG;
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 -1 * (C_QTY_SHORT_FORMULA - C_OPEN_REQUIREMENTS);
104 ELSE
105 RETURN -1 * (AVAIL_QTY - C_OPEN_REQUIREMENTS);
106 END IF;
107 RETURN NULL;
108 END C_QTY_SHORT_QOHFORMULA;
109
110 FUNCTION DEPARTMENT_LIMITER RETURN CHARACTER IS
111 LIMIT_DEPT VARCHAR2(80);
112 BEGIN
113 IF (P_FROM_DEPT IS NOT NULL) THEN
114 IF (P_TO_DEPT IS NOT NULL) THEN
115 LIMIT_DEPT := ' AND BOD.DEPARTMENT_CODE BETWEEN ''' || P_FROM_DEPT || ''' AND ''' || P_TO_DEPT || '''';
116 ELSE
117 LIMIT_DEPT := ' AND BOD.DEPARTMENT_CODE >= ''' || P_FROM_DEPT || '''';
118 END IF;
119 ELSE
120 IF (P_TO_DEPT IS NOT NULL) THEN
121 LIMIT_DEPT := ' AND BOD.DEPARTMENT_CODE <= ''' || P_TO_DEPT || '''';
122 ELSE
123 LIMIT_DEPT := ' ';
124 END IF;
125 END IF;
126 RETURN (LIMIT_DEPT);
127 END DEPARTMENT_LIMITER;
128
129 FUNCTION C_BREAK_JOBFORMULA(JOB IN VARCHAR2) RETURN VARCHAR2 IS
130 BEGIN
131 IF (P_SORT_BY = 11) THEN
132 RETURN (JOB);
133 ELSE
134 RETURN ('X');
135 END IF;
136 RETURN NULL;
137 END C_BREAK_JOBFORMULA;
138
139 FUNCTION C_BREAK_DATE_REQUIREDFORMULA(DATE_REQUIRED IN DATE) RETURN DATE IS
140 BEGIN
141 IF (P_SORT_BY = 5) THEN
142 RETURN (DATE_REQUIRED);
143 ELSE
144 RETURN (NULL);
145 END IF;
146 RETURN NULL;
147 END C_BREAK_DATE_REQUIREDFORMULA;
148
149 FUNCTION C_BREAK_DEPARTMENTFORMULA(DEPARTMENT IN VARCHAR2) RETURN VARCHAR2 IS
150 BEGIN
151 IF (P_SORT_BY = 12) THEN
152 RETURN (DEPARTMENT);
153 ELSE
154 RETURN (' ');
155 END IF;
156 RETURN NULL;
157 END C_BREAK_DEPARTMENTFORMULA;
158
159 FUNCTION C_LIMIT_COMPONENTSFORMULA RETURN VARCHAR2 IS
160 BEGIN
161 BEGIN
162 IF (P_FLEXWHERE IS NOT NULL) THEN
163 RETURN ('AND ');
164 ELSE
165 RETURN (' ');
166 END IF;
167 END;
168 RETURN NULL;
169 END C_LIMIT_COMPONENTSFORMULA;
170
171 FUNCTION C_BREAK_COMP_DISPFORMULA(COMPONENT IN VARCHAR2
172 ,C_COMPONENT_DISP IN VARCHAR2) RETURN VARCHAR2 IS
173 BEGIN
174 /*SRW.REFERENCE(COMPONENT)*/NULL;
175 /*SRW.REFERENCE(C_COMPONENT_DISP)*/NULL;
176 IF (P_SORT_BY = 9) OR (P_SORT_BY = 13) THEN
177 RETURN (C_COMPONENT_DISP);
178 ELSE
179 RETURN (' ');
180 END IF;
181 RETURN NULL;
182 END C_BREAK_COMP_DISPFORMULA;
183
184 FUNCTION NET_QTY_DETAILFORMULA(AVAILABILITY_TYPE IN NUMBER
185 ,QUANTITY_ON_HAND IN NUMBER) RETURN NUMBER IS
186 BEGIN
187 /*SRW.REFERENCE(AVAILABILITY_TYPE)*/NULL;
188 /*SRW.REFERENCE(QUANTITY_ON_HAND)*/NULL;
189 RETURN (AVAILABILITY_TYPE * QUANTITY_ON_HAND);
190 END NET_QTY_DETAILFORMULA;
191
192 FUNCTION AVAIL_QTYFORMULA(NET_QTY_COMP IN NUMBER
193 ,C_ITEM_TOTAL_QTY IN NUMBER) RETURN NUMBER IS
194 BEGIN
195 /*SRW.REFERENCE(NET_QTY_COMP)*/NULL;
196 /*SRW.REFERENCE(C_ITEM_TOTAL_QTY)*/NULL;
197 /*SRW.REFERENCE(P_NETTABLE)*/NULL;
198 IF (P_NETTABLE = 1) THEN
199 RETURN (NET_QTY_COMP);
200 ELSE
201 RETURN (C_ITEM_TOTAL_QTY);
202 END IF;
203 RETURN NULL;
204 END AVAIL_QTYFORMULA;
205
206 FUNCTION C_COMPONENT_SORTFORMULA(COMPONENT IN VARCHAR2
207 ,C_COMPONENT_DISP IN VARCHAR2
208 ,C_COMPONENT_SORT IN VARCHAR2) RETURN VARCHAR2 IS
209 BEGIN
210 /*SRW.REFERENCE(COMPONENT)*/NULL;
211 /*SRW.REFERENCE(C_COMPONENT_DISP)*/NULL;
212 RETURN (C_COMPONENT_SORT);
213 END C_COMPONENT_SORTFORMULA;
214
215 FUNCTION ZEROFORMULA RETURN NUMBER IS
216 BEGIN
217 RETURN (0);
218 END ZEROFORMULA;
219
220 FUNCTION LIMIT_PLANNERS RETURN CHARACTER IS
221 LIMIT_PLANNERS VARCHAR2(240);
222 BEGIN
223 IF (P_FROM_PLANNER IS NOT NULL) THEN
224 IF (P_TO_PLANNER IS NOT NULL) THEN
225 LIMIT_PLANNERS := ' AND MSI2.PLANNER_CODE BETWEEN ''' || P_FROM_PLANNER || ''' AND ''' || P_TO_PLANNER || '''';
226 ELSE
227 LIMIT_PLANNERS := ' AND MSI2.PLANNER_CODE >= ''' || P_FROM_PLANNER || '''';
228 END IF;
229 ELSE
230 IF (P_TO_PLANNER IS NOT NULL) THEN
231 LIMIT_PLANNERS := ' AND MSI2.PLANNER_CODE <= ''' || P_TO_PLANNER || '''';
232 ELSE
233 LIMIT_PLANNERS := ' ';
234 END IF;
235 END IF;
236 RETURN (LIMIT_PLANNERS);
237 END LIMIT_PLANNERS;
238
239 FUNCTION LIMIT_BUYER RETURN CHARACTER IS
240 LIMIT_BUYER VARCHAR2(240);
241 BEGIN
242 IF (P_FROM_BUYER IS NOT NULL) THEN
243 IF (P_TO_BUYER IS NOT NULL) THEN
244 LIMIT_BUYER := ' AND EMP.FULL_NAME BETWEEN ''' || REPLACE(P_FROM_BUYER
245 ,''''
246 ,'''''') || ''' AND ''' || REPLACE(P_TO_BUYER
247 ,''''
248 ,'''''') || '''';
249 ELSE
250 LIMIT_BUYER := ' AND EMP.FULL_NAME >= ''' || REPLACE(P_FROM_BUYER
251 ,''''
252 ,'''''') || '''';
253 END IF;
254 ELSE
255 IF (P_TO_BUYER IS NOT NULL) THEN
256 LIMIT_BUYER := ' AND EMP.FULL_NAME <= ''' || REPLACE(P_TO_BUYER
257 ,''''
258 ,'''''') || '''';
259 ELSE
260 LIMIT_BUYER := ' ';
261 END IF;
262 END IF;
263 RETURN (LIMIT_BUYER);
264 END LIMIT_BUYER;
265
266 FUNCTION C_BREAK_ASSY_DISPFORMULA(C_ASSY IN VARCHAR2
267 ,C_ASSY_DISP IN VARCHAR2) RETURN VARCHAR2 IS
268 BEGIN
269 /*SRW.REFERENCE(C_ASSY)*/NULL;
270 /*SRW.REFERENCE(C_ASSY_DISP)*/NULL;
271 IF (P_SORT_BY = 11) THEN
272 RETURN (C_ASSY_DISP);
273 ELSE
274 RETURN ('X');
275 END IF;
276 RETURN NULL;
277 END C_BREAK_ASSY_DISPFORMULA;
278
279 FUNCTION MASTER_BREAK_FUNC(COMPONENT IN VARCHAR2
280 ,C_COMPONENT_DISP IN VARCHAR2
281 ,C_COMPONENT_SORT IN VARCHAR2
282 ,JOB IN VARCHAR2
283 ,DEPARTMENT IN VARCHAR2
284 ,MAKE_BUY IN VARCHAR2
285 ,BUYER_NAME IN VARCHAR2
286 ,PLANNER_CODE IN VARCHAR2
287 ,DATE_REQUIRED IN DATE) RETURN CHARACTER IS
288 TEMP VARCHAR2(1250);
289 BEGIN
290 /*SRW.REFERENCE(COMPONENT)*/NULL;
291 /*SRW.REFERENCE(C_COMPONENT_DISP)*/NULL;
292 /*SRW.REFERENCE(C_COMPONENT_SORT)*/NULL;
293 /*SRW.REFERENCE(JOB)*/NULL;
294 /*SRW.REFERENCE(DEPARTMENT)*/NULL;
295 /*SRW.REFERENCE(MAKE_BUY)*/NULL;
296 /*SRW.REFERENCE(BUYER_NAME)*/NULL;
297 /*SRW.REFERENCE(PLANNER_CODE)*/NULL;
298 /*SRW.REFERENCE(DATE_REQUIRED)*/NULL;
299 IF (P_SORT_BY = 9) THEN
300 TEMP := C_COMPONENT_SORT;
301 ELSE
302 IF (P_SORT_BY = 5) THEN
303 TEMP := LPAD(TO_CHAR(DATE_REQUIRED
304 ,'J')
305 ,10);
306 ELSE
307 IF (P_SORT_BY = 11) THEN
308 TEMP := JOB;
309 ELSE
310 IF (P_SORT_BY = 12) THEN
311 TEMP := DEPARTMENT;
312 ELSE
313 TEMP := RPAD(MAKE_BUY
314 ,10) || RPAD(BUYER_NAME
315 ,240) || RPAD(PLANNER_CODE
316 ,40) || C_COMPONENT_SORT;
317 END IF;
318 END IF;
319 END IF;
320 END IF;
321 RETURN (TEMP);
322 END MASTER_BREAK_FUNC;
323
324 FUNCTION C_FIRST_SORTFORMULA RETURN VARCHAR2 IS
325 BEGIN
326 IF (P_SORT_BY = 5) THEN
327 RETURN ('WRO.Date_Required');
328 ELSE
329 IF (P_SORT_BY = 11) THEN
330 RETURN ('WE.WIP_ENTITY_NAME');
331 ELSE
332 IF (P_SORT_BY = 12) THEN
333 RETURN ('BOD.DEPARTMENT_CODE');
334 ELSE
335 RETURN ('''X''');
336 END IF;
337 END IF;
338 END IF;
339 RETURN NULL;
340 END C_FIRST_SORTFORMULA;
341
342 FUNCTION C_TOT_QTY_SHORTFORMULA(C_TOTAL_AVAIL_QTY IN VARCHAR2
343 ,C_TOTAL_AVAIL_NET_QTY IN VARCHAR2
344 ,C_TOT_QTY_OPEN IN VARCHAR2) RETURN NUMBER IS
345 BEGIN
346 /*SRW.REFERENCE(C_TOTAL_AVAIL_QTY)*/NULL;
347 /*SRW.REFERENCE(C_TOTAL_AVAIL_NET_QTY)*/NULL;
348 /*SRW.REFERENCE(C_TOT_QTY_OPEN)*/NULL;
349 IF (P_NETTABLE = 1) THEN
350 RETURN (C_TOT_QTY_OPEN - C_TOTAL_AVAIL_NET_QTY);
351 ELSE
352 RETURN (C_TOT_QTY_OPEN - C_TOTAL_AVAIL_QTY);
353 END IF;
354 RETURN NULL;
355 END C_TOT_QTY_SHORTFORMULA;
356
357 FUNCTION C_BREAK_COMP_DESCFORMULA(COMP_DESC IN VARCHAR2) RETURN VARCHAR2 IS
358 BEGIN
359 IF ((P_SORT_BY = 9) OR (P_SORT_BY = 13)) THEN
360 RETURN (COMP_DESC);
361 ELSE
362 RETURN ('X');
363 END IF;
364 RETURN NULL;
365 END C_BREAK_COMP_DESCFORMULA;
366
367 FUNCTION C_DISPLAY_FORMULAFORMULA(C_QTY_SHORT_QOH IN NUMBER) RETURN NUMBER IS
368 BEGIN
369 IF ((P_OPEN_SHORT = 1) OR (P_OPEN_SHORT = 2)) AND (C_QTY_SHORT_QOH <= 0) THEN
370 RETURN (0);
371 ELSE
372 RETURN (1);
373 END IF;
374 RETURN NULL;
375 END C_DISPLAY_FORMULAFORMULA;
376
377 FUNCTION AFTERPFORM RETURN BOOLEAN IS
378 BEGIN
379 IF P_FROM_COMP IS NOT NULL OR P_TO_COMP IS NOT NULL THEN
380 P_ITEM_ORG := 'and MSI2.ORGANIZATION_ID = :P_ORGANIZATION_ID';
381 END IF;
382 IF P_SCHEDULE_GROUP IS NOT NULL THEN
383 P_SG_OUTER := ' ';
384 END IF;
385 RETURN (TRUE);
386 END AFTERPFORM;
387
388 FUNCTION LIMIT_BULK_SUPPLY_TYPE RETURN CHARACTER IS
389 LIMIT_BULK_SUPPLY_TYPE VARCHAR2(100);
390 BEGIN
391 IF (P_INCLUDE_BULK = 1) THEN
392 LIMIT_BULK_SUPPLY_TYPE := ' ';
393 ELSE
394 LIMIT_BULK_SUPPLY_TYPE := 'AND WRO.WIP_SUPPLY_TYPE <> 4 ';
395 END IF;
396 RETURN (LIMIT_BULK_SUPPLY_TYPE);
397 END LIMIT_BULK_SUPPLY_TYPE;
398
399 FUNCTION LIMIT_VENDOR_SUPPLY_TYPE RETURN CHARACTER IS
400 LIMIT_VENDOR_SUPPLY_TYPE VARCHAR2(100);
401 BEGIN
402 IF (P_INCLUDE_VENDOR = 1) THEN
403 LIMIT_VENDOR_SUPPLY_TYPE := ' ';
404 ELSE
405 LIMIT_VENDOR_SUPPLY_TYPE := 'AND WRO.WIP_SUPPLY_TYPE <> 5 ';
406 END IF;
407 RETURN (LIMIT_VENDOR_SUPPLY_TYPE);
408 END LIMIT_VENDOR_SUPPLY_TYPE;
409
410 FUNCTION C_LIMITER RETURN VARCHAR2 IS
411 C_OUT VARCHAR2(200);
412 BEGIN
413 IF P_SCHEDULE_GROUP IS NOT NULL THEN
414 C_OUT := ' AND SG.SCHEDULE_GROUP_NAME = ''' || P_SCHEDULE_GROUP || '''';
415 ELSE
416 C_OUT := ' ';
417 END IF;
418 RETURN (C_OUT);
419 END C_LIMITER;
420
421 FUNCTION C_LIMIT_DATE_REQFORMULA RETURN CHAR IS
422 LIMIT_DATE_REQ VARCHAR2(120) := ' ';
423 BEGIN
424 IF (P_REQ_END_DATE IS NOT NULL) THEN
425 LIMIT_DATE_REQ := ' AND WRO.DATE_REQUIRED < TO_DATE(''' || TO_CHAR(P_REQ_END_DATE
426 ,'YYYYMMDD') || ''',''YYYYMMDD'') + 1 ';
427 END IF;
428 RETURN (LIMIT_DATE_REQ);
429 END C_LIMIT_DATE_REQFORMULA;
430
431 END WIP_WIPDJORQ_XMLP_PKG;
432
[Home] [Help]