[Home] [Help]
PACKAGE BODY: APPS.MRP_MRPRPPLO_XMLP_PKG
Source
1 PACKAGE BODY MRP_MRPRPPLO_XMLP_PKG AS
2 /* $Header: MRPRPPLOB.pls 120.3 2008/01/02 13:06:58 nchinnam noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4
5 DATE_FORMAT varchar2(20):='DD'||'-MON-'||'YYYY';
6 BEGIN
7 DECLARE
8 CURRENCY_DESC VARCHAR2(80);
9 PRECISION NUMBER;
10 CAT_STRUCT_NUM NUMBER;
11 BEGIN
12 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
13 QTY_PRECISION:=mrp_common_xmlp_pkg.get_precision(P_QTY_PRECISION);
14 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
15 IF (P_DEBUG = 'Y') THEN
16 EXECUTE IMMEDIATE
17 'ALTER SESSION SET SQL_TRACE TRUE';
18 END IF;
19 P_CAT_STRUCT_NUM := P_CAT_STRUCT;
20 IF (P_SORT = 1) THEN
21 NULL;
22 END IF;
23 IF (P_SORT = 2) THEN
24 NULL;
25 END IF;
26 IF ((P_LOW_ITEM IS NOT NULL) OR (P_HIGH_ITEM IS NOT NULL)) THEN
27 NULL;
28 END IF;
29 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL)) THEN
30 NULL;
31 END IF;
32 SELECT
33 NAME,
34 PRECISION
35 INTO CURRENCY_DESC,PRECISION
36 FROM
37 FND_CURRENCIES_VL
38 WHERE CURRENCY_CODE = P_CURRENCY_CODE;
39 P_CURRENCY_DESC := CURRENCY_DESC;
40 P_PRECISION := PRECISION;
41 END;
42 P_CUTOFF_DATE_1:=to_char(P_CUTOFF_DATE,DATE_FORMAT);
43 RETURN (TRUE);
44 END BEFOREREPORT;
45
46 FUNCTION AFTERREPORT RETURN BOOLEAN IS
47 BEGIN
48 BEGIN
49 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
50 END;
51 RETURN (TRUE);
52 END AFTERREPORT;
53
54 FUNCTION C_PLANNER_RANGEFORMULA RETURN VARCHAR2 IS
55 BEGIN
56 DECLARE
57 PLANNER_RANGE VARCHAR2(80);
58 BEGIN
59 IF (P_PLANNER IS NOT NULL) THEN
60 PLANNER_RANGE := 'AND req.planner_code = ''' || P_PLANNER || '''';
61 ELSE
62 PLANNER_RANGE := ' ';
63 END IF;
64 RETURN (PLANNER_RANGE);
65 END;
66 RETURN NULL;
67 END C_PLANNER_RANGEFORMULA;
68
69 FUNCTION C_BUYER_RANGEFORMULA RETURN VARCHAR2 IS
70 BEGIN
71 DECLARE
72 BUYER_RANGE VARCHAR2(80);
73 BEGIN
74 IF (P_BUYER IS NOT NULL) THEN
75 BUYER_RANGE := 'AND req.buyer_id = ' || P_BUYER;
76 ELSE
77 BUYER_RANGE := ' ';
78 END IF;
79 RETURN (BUYER_RANGE);
80 END;
81 RETURN NULL;
82 END C_BUYER_RANGEFORMULA;
83
84 FUNCTION C_CATEGORY_FROMFORMULA RETURN VARCHAR2 IS
85 BEGIN
86 DECLARE
87 CATEGORY_FROM VARCHAR2(80);
88 BEGIN
89 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL) OR (P_SORT = 2)) THEN
90 CATEGORY_FROM := ',mtl_categories cat';
91 ELSE
92 CATEGORY_FROM := ' ';
93 END IF;
94 RETURN (CATEGORY_FROM);
95 END;
96 RETURN NULL;
97 END C_CATEGORY_FROMFORMULA;
98
99 FUNCTION C_SORT_COLUMNFORMULA RETURN VARCHAR2 IS
100 BEGIN
101 DECLARE
102 SORT_COLUMN VARCHAR2(80);
103 BEGIN
104 IF (P_SORT = 3) THEN
105 SORT_COLUMN := ',req.planner_code';
106 ELSIF (P_SORT = 4) THEN
107 SORT_COLUMN := ',req.buyer_name';
108 ELSIF (P_SORT = 6) THEN
109 SORT_COLUMN := ',abc.abc_class_id';
110 ELSE
111 SORT_COLUMN := ' ';
112 END IF;
113 RETURN (SORT_COLUMN);
114 END;
115 RETURN NULL;
116 END C_SORT_COLUMNFORMULA;
117
118 FUNCTION C_ABC_WHEREFORMULA RETURN VARCHAR2 IS
119 BEGIN
120 DECLARE
121 ABC_WHERE VARCHAR2(200);
122 BEGIN
123 IF (P_ABC_ASSGN IS NOT NULL) THEN
124 ABC_WHERE := 'AND abc.inventory_item_id(+) = req.inventory_item_id ' || 'AND abc.assignment_group_id = ' || P_ABC_ASSGN || ' AND abc.abc_class_id = abc_cls.abc_class_id';
125 ELSIF ((P_ABC_CLASS IS NOT NULL) OR (P_SORT = 6)) THEN
126 ABC_WHERE := 'AND abc.inventory_item_id(+) = req.inventory_item_id ' || 'AND abc_cls.abc_class_id(+) = abc.abc_class_id';
127 ELSE
128 ABC_WHERE := ' ';
129 END IF;
130 RETURN (ABC_WHERE);
131 END;
132 RETURN NULL;
133 END C_ABC_WHEREFORMULA;
134
135 FUNCTION C_CUTOFF_COLUMNFORMULA RETURN VARCHAR2 IS
136 BEGIN
137 DECLARE
138 CUTOFF_COLUMN VARCHAR2(80);
139 BEGIN
140 IF (P_CUTOFF_TYPE = 1) THEN
141 CUTOFF_COLUMN := 'rec.new_order_placement_date';
142 ELSIF (P_CUTOFF_TYPE = 2) THEN
143 CUTOFF_COLUMN := 'rec.new_schedule_date';
144 ELSIF (P_CUTOFF_TYPE = 3) THEN
145 CUTOFF_COLUMN := 'rec.new_dock_date';
146 ELSE
147 CUTOFF_COLUMN := 'rec.new_wip_start_date';
148 END IF;
149 RETURN (CUTOFF_COLUMN);
150 END;
151 RETURN ('rec.new_schedule_date');
152 END C_CUTOFF_COLUMNFORMULA;
153
154 FUNCTION C_ABC_FROMFORMULA RETURN VARCHAR2 IS
155 BEGIN
156 DECLARE
157 ABC_FROM VARCHAR2(80);
158 BEGIN
159 IF (P_ABC_ASSGN IS NOT NULL) OR ((P_ABC_CLASS IS NOT NULL) OR (P_SORT = 6)) THEN
160 ABC_FROM := ',mtl_abc_assignments abc' || ',mtl_abc_classes abc_cls';
161 ELSE
162 ABC_FROM := ' ';
163 END IF;
164 RETURN (ABC_FROM);
165 END;
166 RETURN NULL;
167 END C_ABC_FROMFORMULA;
168
169 FUNCTION C_ABC_RANGEFORMULA RETURN VARCHAR2 IS
170 BEGIN
171 DECLARE
172 ABC_RANGE VARCHAR2(80);
173 BEGIN
174 IF (P_ABC_CLASS IS NOT NULL) THEN
175 ABC_RANGE := 'AND abc.abc_class_id = ' || P_ABC_CLASS;
176 ELSE
177 ABC_RANGE := ' ';
178 END IF;
179 RETURN (ABC_RANGE);
180 END;
181 RETURN NULL;
182 END C_ABC_RANGEFORMULA;
183
184 FUNCTION C_CATEGORY_WHEREFORMULA RETURN VARCHAR2 IS
185 BEGIN
186 DECLARE
187 CATEGORY_WHERE VARCHAR2(250);
188 BEGIN
189 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL) OR (P_SORT = 2)) THEN
190 CATEGORY_WHERE := 'AND ic.category_id = cat.category_id(+) ' || 'AND cat.structure_id = ' || P_CAT_STRUCT;
191 ELSE
192 CATEGORY_WHERE := ' ';
193 END IF;
194 RETURN (CATEGORY_WHERE);
195 END;
196 RETURN NULL;
197 END C_CATEGORY_WHEREFORMULA;
198
199 FUNCTION C_ORDER_BYFORMULA(P_ITEM_ORDER_BY varchar2) RETURN VARCHAR2 IS
200 BEGIN
201 DECLARE
202 ORDER_BY VARCHAR2(2000);
203 BEGIN
204 IF (P_SORT = 1) THEN
205 ORDER_BY := ',rec.new_schedule_date,' || P_ITEM_ORDER_BY || ',rec.new_order_quantity' || ',rec.schedule_compression_days' || ',rec.transaction_id';
206 ELSIF (P_SORT = 2) THEN
207 ORDER_BY := ',rec.new_schedule_date,' || P_ITEM_ORDER_BY || ',rec.new_order_quantity' || ',rec.schedule_compression_days' || ',rec.transaction_id';
208 ELSIF (P_SORT = 3) THEN
209 ORDER_BY := 'req.planner_code' || ',rec.new_schedule_date,' || P_ITEM_ORDER_BY || ',rec.new_order_quantity' || ',rec.schedule_compression_days' || ',rec.transaction_id';
210 ELSIF (P_SORT = 4) THEN
211 ORDER_BY := 'req.buyer_name' || ',rec.new_schedule_date,' || P_ITEM_ORDER_BY || ',rec.new_order_quantity' || ',rec.schedule_compression_days' || ',rec.transaction_id';
212 ELSIF (P_SORT = 6) THEN
213 ORDER_BY := 'abc.abc_class_id' || ',rec.new_schedule_date,' || P_ITEM_ORDER_BY || ',rec.new_order_quantity' || ',rec.schedule_compression_days' || ',rec.transaction_id';
214 ELSIF (P_SORT = 7) THEN
215 ORDER_BY := 'par.organization_code' || ',rec.new_schedule_date,' || P_ITEM_ORDER_BY || ',rec.new_order_quantity' || ',rec.schedule_compression_days' || ',rec.transaction_id';
216 ELSIF (P_SORT = 0) THEN
217 ORDER_BY := 'rec.new_schedule_date,' || P_ITEM_ORDER_BY || ',rec.new_order_quantity' || ',rec.schedule_compression_days' || ',rec.transaction_id';
218 ELSE
219 ORDER_BY := ' ';
220 END IF;
221 RETURN (ORDER_BY);
222 END;
223 RETURN 'rec.new_schedule_date';
224 END C_ORDER_BYFORMULA;
225
226 FUNCTION C_P_ITEM_SELECTFORMULA RETURN VARCHAR2 IS
227 BEGIN
228 DECLARE
229 ITEM_SELECT VARCHAR2(80);
230 BEGIN
231 IF P_ITEM_SELECT IS NOT NULL THEN
232 SELECT
233 MEANING
234 INTO ITEM_SELECT
235 FROM
236 MFG_LOOKUPS
237 WHERE LOOKUP_CODE = P_ITEM_SELECT
238 AND LOOKUP_TYPE = 'MTL_PLANNING_MAKE_BUY';
239 RETURN (ITEM_SELECT);
240 ELSE
241 RETURN (NULL);
242 END IF;
243 END;
244 RETURN NULL;
245 END C_P_ITEM_SELECTFORMULA;
246
247 FUNCTION C_P_INCLUDE_COSTFORMULA RETURN VARCHAR2 IS
248 BEGIN
249 DECLARE
250 INCLUDE_COST VARCHAR2(80);
251 BEGIN
252 SELECT
253 MEANING
254 INTO INCLUDE_COST
255 FROM
256 MFG_LOOKUPS
257 WHERE LOOKUP_CODE = P_INCLUDE_COST
258 AND LOOKUP_TYPE = 'SYS_YES_NO';
259 RETURN (INCLUDE_COST);
260 END;
261 RETURN NULL;
262 END C_P_INCLUDE_COSTFORMULA;
263
264 FUNCTION C_P_SORTFORMULA RETURN VARCHAR2 IS
265 BEGIN
266 DECLARE
267 SORT VARCHAR2(80);
268 BEGIN
269 SELECT
270 MEANING
271 INTO SORT
272 FROM
273 MFG_LOOKUPS
274 WHERE LOOKUP_CODE = P_SORT
275 AND LOOKUP_TYPE = 'MRP_PLANNED_ORDER_SORT';
276 RETURN (SORT);
277 END;
278 RETURN NULL;
279 END C_P_SORTFORMULA;
280
281 FUNCTION C_P_BUYERFORMULA RETURN VARCHAR2 IS
282 BEGIN
283 DECLARE
284 BUYER VARCHAR2(50);
285 BEGIN
286 IF (P_BUYER IS NOT NULL) THEN
287 SELECT
288 FULL_NAME
289 INTO BUYER
290 FROM
291 MTL_EMPLOYEES_VIEW
292 WHERE EMPLOYEE_ID = P_BUYER
293 AND ORGANIZATION_ID = P_ORG_ID;
294 END IF;
295 RETURN (BUYER);
296 END;
297 RETURN NULL;
298 END C_P_BUYERFORMULA;
299
300 FUNCTION C_P_CAT_SETFORMULA RETURN VARCHAR2 IS
301 BEGIN
302 DECLARE
303 CAT_SET VARCHAR2(30);
304 BEGIN
305 SELECT
306 CATEGORY_SET_NAME
307 INTO CAT_SET
308 FROM
309 MTL_CATEGORY_SETS
310 WHERE CATEGORY_SET_ID = P_CAT_SET;
311 RETURN (CAT_SET);
312 END;
313 RETURN NULL;
314 END C_P_CAT_SETFORMULA;
315
316 FUNCTION C_P_ABC_ASSGNFORMULA RETURN VARCHAR2 IS
317 BEGIN
318 DECLARE
319 ABC_ASSGN VARCHAR2(40);
320 BEGIN
321 SELECT
322 ASSIGNMENT_GROUP_NAME
323 INTO ABC_ASSGN
324 FROM
325 MTL_ABC_ASSIGNMENT_GROUPS
326 WHERE ASSIGNMENT_GROUP_ID = P_ABC_ASSGN
327 AND ORGANIZATION_ID = P_ORG_ID;
328 RETURN (ABC_ASSGN);
329 EXCEPTION
330 WHEN NO_DATA_FOUND THEN
331 NULL;
332 END;
333 RETURN NULL;
334 END C_P_ABC_ASSGNFORMULA;
335
336 FUNCTION C_P_CUTOFF_TYPEFORMULA RETURN VARCHAR2 IS
337 BEGIN
338 DECLARE
339 CUTOFF_TYPE VARCHAR2(80);
340 BEGIN
341 SELECT
342 MEANING
343 INTO CUTOFF_TYPE
344 FROM
345 MFG_LOOKUPS
346 WHERE LOOKUP_CODE = P_CUTOFF_TYPE
347 AND LOOKUP_TYPE = 'MRP_PL_ORDER_CUTOFF_TYPE';
348 RETURN (CUTOFF_TYPE);
349 END;
350 RETURN NULL;
351 END C_P_CUTOFF_TYPEFORMULA;
352
353 FUNCTION C_P_MULTI_ORGFORMULA RETURN NUMBER IS
354 BEGIN
355 DECLARE
356 VAR1 NUMBER;
357 MULTI_ORG NUMBER;
358 CURSOR C IS
359 SELECT
360 DISTINCT
361 ORGANIZATION_SELECTION
362 FROM
363 MRP_PLAN_ORGANIZATIONS_V
364 WHERE DECODE(P_ORG_TYPE
365 ,1
366 ,PLANNED_ORGANIZATION
367 ,ORGANIZATION_ID) = P_ORG_ID
368 AND COMPILE_DESIGNATOR = P_PLAN_NAME;
369 BEGIN
370 OPEN C;
371 FETCH C
372 INTO VAR1;
373 IF (C%NOTFOUND) THEN
374 MULTI_ORG := 2;
375 END IF;
376 IF ((VAR1 = 2) OR (VAR1 = 3)) THEN
377 MULTI_ORG := 1;
378 ELSE
379 MULTI_ORG := 2;
380 END IF;
381 CLOSE C;
382 RETURN (MULTI_ORG);
383 END;
384 RETURN NULL;
385 END C_P_MULTI_ORGFORMULA;
386
387 FUNCTION C_P_ORG_TYPEFORMULA RETURN VARCHAR2 IS
388 BEGIN
389 DECLARE
390 ORG_TYPE VARCHAR2(80);
391 BEGIN
392 ORG_TYPE := 'Current Organization';
393 IF P_ORG_TYPE = 2 THEN
394 SELECT
395 MEANING
396 INTO ORG_TYPE
397 FROM
398 MFG_LOOKUPS
399 WHERE LOOKUP_CODE = P_ORG_TYPE
400 AND LOOKUP_TYPE = 'MRP_REPORT_MULTIORG';
401 END IF;
402 RETURN (ORG_TYPE);
403 END;
404 RETURN NULL;
405 END C_P_ORG_TYPEFORMULA;
406
407 FUNCTION CP_order(P_ORDER_BY1 varchar2,P_ORDER_BY2 varchar2) RETURN VARCHAR2 IS
408 BEGIN
409
410 BEGIN
411 IF (P_SORT = 1) THEN
412 return(P_ORDER_BY1);
413 END IF;
414 IF (P_SORT = 2) THEN
415 return(P_ORDER_BY2);
416 END IF;
417 END;
418 RETURN NULL;
419 END CP_order;
420
421 END MRP_MRPRPPLO_XMLP_PKG;
422