1 PACKAGE BODY MRP_MRPRPLOR_XMLP_PKG AS
2 /* $Header: MRPRPLORB.pls 120.2 2007/12/25 08:31:06 nchinnam noship $ */
3 FUNCTION C_PLANNER_RANGEFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 DECLARE
6 PLANNER_RANGE VARCHAR2(80);
7 BEGIN
8 IF (P_PLANNER IS NOT NULL) THEN
9 PLANNER_RANGE := 'AND req.planner_code = ''' || P_PLANNER || '''';
10 ELSE
11 PLANNER_RANGE := ' ';
12 END IF;
13 RETURN (PLANNER_RANGE);
14 END;
15 RETURN NULL;
16 END C_PLANNER_RANGEFORMULA;
17 FUNCTION C_BUYER_RANGEFORMULA RETURN VARCHAR2 IS
18 BEGIN
19 DECLARE
20 BUYER_RANGE VARCHAR2(80);
21 BEGIN
22 IF (P_BUYER IS NOT NULL) THEN
23 BUYER_RANGE := 'AND req.buyer_id = ' || P_BUYER;
24 ELSE
25 BUYER_RANGE := ' ';
26 END IF;
27 RETURN (BUYER_RANGE);
28 END;
29 RETURN NULL;
30 END C_BUYER_RANGEFORMULA;
31 FUNCTION C_CATEGORY_FROMFORMULA RETURN VARCHAR2 IS
32 BEGIN
33 DECLARE
34 CATEGORY_FROM VARCHAR2(80);
35 BEGIN
36 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL) OR (P_SORT = 2)) THEN
37 CATEGORY_FROM := ',mtl_categories cat';
38 ELSE
39 CATEGORY_FROM := ' ';
40 END IF;
41 RETURN (CATEGORY_FROM);
42 END;
43 RETURN NULL;
44 END C_CATEGORY_FROMFORMULA;
45 FUNCTION C_SORT_COLUMNFORMULA RETURN VARCHAR2 IS
46 BEGIN
47 DECLARE
48 SORT_COLUMN VARCHAR2(80);
49 BEGIN
50 IF (P_SORT = 3) THEN
51 SORT_COLUMN := ',req.planner_code';
52 ELSIF (P_SORT = 4) THEN
53 SORT_COLUMN := ',req.buyer_name';
54 ELSIF (P_SORT = 6) THEN
55 SORT_COLUMN := ',abc.abc_class_id';
56 ELSE
57 SORT_COLUMN := ' ';
58 END IF;
59 RETURN (SORT_COLUMN);
60 END;
61 RETURN NULL;
62 END C_SORT_COLUMNFORMULA;
63 FUNCTION C_ORDER_BYFORMULA RETURN VARCHAR2 IS
64 BEGIN
65 DECLARE
66 ORDER_BY VARCHAR2(80);
67 BEGIN
68 IF (P_SORT = 3) THEN
69 ORDER_BY := 'req.planner_code,';
70 ELSIF (P_SORT = 4) THEN
71 ORDER_BY := 'req.buyer_name,';
72 ELSIF (P_SORT = 6) THEN
73 ORDER_BY := 'abc.abc_class_id,';
74 ELSIF (P_SORT = 7) THEN
75 ORDER_BY := 'par.organization_code,';
76 ELSIF (P_SORT = 0) THEN
77 ORDER_BY := ' ';
78 ELSE
79 ORDER_BY := ', ';
80 END IF;
81 RETURN (ORDER_BY);
82 END;
83 RETURN NULL;
84 END C_ORDER_BYFORMULA;
85 FUNCTION C_ABC_WHEREFORMULA RETURN VARCHAR2 IS
86 BEGIN
87 DECLARE
88 ABC_WHERE VARCHAR2(200);
89 BEGIN
90 IF (P_ABC_ASSGN IS NOT NULL) THEN
91 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';
92 ELSIF ((P_ABC_CLASS IS NOT NULL) OR (P_SORT = 6)) THEN
93 ABC_WHERE := 'AND abc.inventory_item_id(+) = req.inventory_item_id ' || 'AND abc_cls.abc_class_id(+) = abc.abc_class_id';
94 ELSE
95 ABC_WHERE := ' ';
96 END IF;
97 RETURN (ABC_WHERE);
98 END;
99 RETURN NULL;
100 END C_ABC_WHEREFORMULA;
101 FUNCTION C_ABC_FROMFORMULA RETURN VARCHAR2 IS
102 BEGIN
103 DECLARE
104 ABC_FROM VARCHAR2(80);
105 BEGIN
106 IF (P_ABC_ASSGN IS NOT NULL) OR ((P_ABC_CLASS IS NOT NULL) OR (P_SORT = 6)) THEN
107 ABC_FROM := ',mtl_abc_assignments abc' || ',mtl_abc_classes abc_cls';
108 ELSE
109 ABC_FROM := ' ';
110 END IF;
111 RETURN (ABC_FROM);
112 END;
113 RETURN NULL;
114 END C_ABC_FROMFORMULA;
115 FUNCTION C_ABC_RANGEFORMULA RETURN VARCHAR2 IS
116 BEGIN
117 DECLARE
118 ABC_RANGE VARCHAR2(80);
119 BEGIN
120 IF (P_ABC_CLASS IS NOT NULL) THEN
121 ABC_RANGE := 'AND abc.abc_class_id = ' || P_ABC_CLASS;
122 ELSE
123 ABC_RANGE := ' ';
124 END IF;
125 RETURN (ABC_RANGE);
126 END;
127 RETURN NULL;
128 END C_ABC_RANGEFORMULA;
129 FUNCTION C_CATEGORY_WHEREFORMULA RETURN VARCHAR2 IS
130 BEGIN
131 DECLARE
132 CATEGORY_WHERE VARCHAR2(100);
133 BEGIN
134 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL) OR (P_SORT = 2)) THEN
135 CATEGORY_WHERE := 'AND ic.category_id = cat.category_id(+)' || ' AND cat.structure_id = ' || P_CAT_STRUCT;
136 ELSE
137 CATEGORY_WHERE := ' ';
138 END IF;
139 RETURN (CATEGORY_WHERE);
140 END;
141 RETURN NULL;
142 END C_CATEGORY_WHEREFORMULA;
143 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
144 BEGIN
145 DECLARE
146 CAL_CODE VARCHAR2(10);
147 CAL_EXCEPTION_SET_ID NUMBER;
148 CAT_STRUCT_NUM NUMBER;
149 BEGIN
150 -- LP_ADVANCE_NOTICE_DATE:=P_ADVANCE_NOTICE_DATE;
151 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
152 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
153 IF (P_DEBUG = 'Y') THEN
154 EXECUTE IMMEDIATE
155 'ALTER SESSION SET SQL_TRACE TRUE';
156 END IF;
157 P_CAT_STRUCT_NUM := P_CAT_STRUCT;
158 IF (P_SORT = 1) THEN
159 NULL;
160 END IF;
161 IF (P_SORT = 2) THEN
162 NULL;
163 END IF;
164 IF ((P_LOW_ITEM IS NOT NULL) OR (P_HIGH_ITEM IS NOT NULL)) THEN
165 NULL;
166 END IF;
167 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL)) THEN
168 NULL;
169 END IF;
170 SELECT
171 CALENDAR_CODE,
172 CALENDAR_EXCEPTION_SET_ID
173 INTO CAL_CODE,CAL_EXCEPTION_SET_ID
174 FROM
175 MTL_PARAMETERS
176 WHERE ORGANIZATION_ID = P_ORG_ID;
177 P_CAL_CODE := CAL_CODE;
178 P_CAL_EXCEPTION_SET_ID := CAL_EXCEPTION_SET_ID;
179 END;
180 LP_ADVANCE_NOTICE_DATE:=to_char(P_ADVANCE_NOTICE_DATE,'DD-MON-YYYY');
181 RETURN (TRUE);
182 END BEFOREREPORT;
183 FUNCTION AFTERREPORT RETURN BOOLEAN IS
184 BEGIN
185 BEGIN
186 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
187 END;
188 RETURN (TRUE);
189 END AFTERREPORT;
190 FUNCTION C_SORT_PFORMULA RETURN VARCHAR2 IS
191 BEGIN
192 DECLARE
193 SORT VARCHAR2(80);
194 BEGIN
195 SELECT
196 MEANING
197 INTO SORT
198 FROM
199 MFG_LOOKUPS
200 WHERE LOOKUP_TYPE = 'MRP_DATA_SELECT'
201 AND LOOKUP_CODE = P_SORT;
202 RETURN (SORT);
203 END;
204 RETURN NULL;
205 END C_SORT_PFORMULA;
206 FUNCTION C_BUYER_PFORMULA RETURN VARCHAR2 IS
207 BEGIN
208 DECLARE
209 BUYER VARCHAR2(50);
210 BEGIN
211 IF (P_BUYER IS NOT NULL) THEN
212 SELECT
213 FULL_NAME
214 INTO BUYER
215 FROM
216 MTL_EMPLOYEES_VIEW
217 WHERE EMPLOYEE_ID = P_BUYER
218 AND ORGANIZATION_ID = P_ORG_ID;
219 END IF;
220 RETURN (BUYER);
221 END;
222 RETURN NULL;
223 END C_BUYER_PFORMULA;
224 FUNCTION C_CAT_SET_PFORMULA RETURN VARCHAR2 IS
225 BEGIN
226 DECLARE
227 CAT_SET VARCHAR2(30);
228 BEGIN
229 SELECT
230 CATEGORY_SET_NAME
231 INTO CAT_SET
232 FROM
233 MTL_CATEGORY_SETS
234 WHERE CATEGORY_SET_ID = P_CAT_SET;
235 RETURN (CAT_SET);
236 END;
237 RETURN NULL;
238 END C_CAT_SET_PFORMULA;
239 FUNCTION C_ABC_ASSGN_PFORMULA RETURN VARCHAR2 IS
240 BEGIN
241 DECLARE
242 ABC_ASSGN VARCHAR2(40);
243 BEGIN
244 SELECT
245 ASSIGNMENT_GROUP_NAME
246 INTO ABC_ASSGN
247 FROM
248 MTL_ABC_ASSIGNMENT_GROUPS
249 WHERE ASSIGNMENT_GROUP_ID = P_ABC_ASSGN
250 AND ORGANIZATION_ID = P_ORG_ID;
251 RETURN (ABC_ASSGN);
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 NULL;
255 END;
256 RETURN NULL;
257 END C_ABC_ASSGN_PFORMULA;
258 FUNCTION C_ABC_CLASS_PFORMULA RETURN VARCHAR2 IS
259 BEGIN
260 DECLARE
261 ABC_CLASS VARCHAR2(40);
262 BEGIN
263 IF (P_ABC_CLASS IS NOT NULL) THEN
264 SELECT
265 ABC_CLASS_NAME
266 INTO ABC_CLASS
267 FROM
268 MTL_ABC_CLASSES
269 WHERE ABC_CLASS_ID = P_ABC_CLASS
270 AND ORGANIZATION_ID = P_ORG_ID;
271 END IF;
272 RETURN (ABC_CLASS);
273 END;
274 RETURN NULL;
275 END C_ABC_CLASS_PFORMULA;
276 FUNCTION C_P_MULTI_ORGFORMULA RETURN NUMBER IS
277 BEGIN
278 DECLARE
279 ORG_SELECTION NUMBER;
280 MULTI_ORG NUMBER;
281 BEGIN
282 SELECT
283 DISTINCT
284 ORGANIZATION_SELECTION
285 INTO ORG_SELECTION
286 FROM
287 MRP_PLAN_ORGANIZATIONS_V
288 WHERE DECODE(P_ORG_TYPE
289 ,1
290 ,PLANNED_ORGANIZATION
291 ,ORGANIZATION_ID) = P_ORG_ID
292 AND COMPILE_DESIGNATOR = P_PLAN_NAME;
293 IF ((ORG_SELECTION = 2) OR (ORG_SELECTION = 3)) THEN
294 MULTI_ORG := 1;
295 ELSE
296 MULTI_ORG := 2;
297 END IF;
298 RETURN (MULTI_ORG);
299 END;
300 RETURN NULL;
301 END C_P_MULTI_ORGFORMULA;
302 FUNCTION C_P_ORG_TYPEFORMULA RETURN VARCHAR2 IS
303 BEGIN
304 DECLARE
305 ORG_TYPE VARCHAR2(80) := 'Current Organization';
306 BEGIN
307 IF P_ORG_TYPE = 2 THEN
308 SELECT
309 MEANING
310 INTO ORG_TYPE
311 FROM
312 MFG_LOOKUPS
313 WHERE LOOKUP_CODE = P_ORG_TYPE
314 AND LOOKUP_TYPE = 'MRP_REPORT_MULTIORG';
315 END IF;
316 RETURN (ORG_TYPE);
317 END;
318 RETURN NULL;
319 END C_P_ORG_TYPEFORMULA;
320 END MRP_MRPRPLOR_XMLP_PKG;
321
[Home] [Help]