1 PACKAGE BODY MRP_MRPRPSST_XMLP_PKG AS
2 /* $Header: MRPRPSSTB.pls 120.2 2008/01/01 13:49:22 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 END;
8 RETURN (TRUE);
9 END AFTERREPORT;
10
11 FUNCTION C_PERIOD_DELTAFORMULA(C_ACTUAL_SUM IN NUMBER
12 ,C_SCHED_SUM IN NUMBER) RETURN NUMBER IS
13 BEGIN
14 RETURN (ROUND((C_ACTUAL_SUM - C_SCHED_SUM)
15 ,P_QTY_PRECISION));
16 END C_PERIOD_DELTAFORMULA;
17
18 FUNCTION C_SCHED_COSTFORMULA(C_SCHED_SUM IN NUMBER
19 ,C_ITEM_COST IN NUMBER) RETURN NUMBER IS
20 BEGIN
21 RETURN (ROUND((C_SCHED_SUM * C_ITEM_COST)
22 ,P_PRECISION));
23 END C_SCHED_COSTFORMULA;
24
25 FUNCTION C_PERIOD_DELTA_COSTFORMULA(C_PERIOD_DELTA IN NUMBER
26 ,C_ITEM_COST IN NUMBER) RETURN NUMBER IS
27 BEGIN
28 RETURN (ROUND((C_PERIOD_DELTA * C_ITEM_COST)
29 ,P_PRECISION));
30 END C_PERIOD_DELTA_COSTFORMULA;
31
32 FUNCTION C_PERIOD_DELTA_CUM_COSTFORMULA(C_PERIOD_DELTA_CUM IN NUMBER
33 ,C_ITEM_COST IN NUMBER) RETURN NUMBER IS
34 BEGIN
35 RETURN (ROUND((C_PERIOD_DELTA_CUM * C_ITEM_COST)
36 ,P_PRECISION));
37 END C_PERIOD_DELTA_CUM_COSTFORMULA;
38
39 FUNCTION C_ACTUAL_COSTFORMULA(C_ACTUAL_SUM IN NUMBER
40 ,C_ITEM_COST IN NUMBER) RETURN NUMBER IS
41 BEGIN
42 RETURN (ROUND((C_ACTUAL_SUM * C_ITEM_COST)
43 ,P_PRECISION));
44 END C_ACTUAL_COSTFORMULA;
45
46 FUNCTION C_SCHED_VERSIONFORMULA RETURN VARCHAR2 IS
47 BEGIN
48 DECLARE
49 SCHED_VERSION VARCHAR2(80);
50 BEGIN
51 SELECT
52 MEANING
53 INTO SCHED_VERSION
54 FROM
55 MFG_LOOKUPS
56 WHERE LOOKUP_TYPE = 'MRP_SCHEDULE_LEVEL'
57 AND LOOKUP_CODE = P_SCHED_VERSION;
58 RETURN (SCHED_VERSION);
59 END;
60 RETURN NULL;
61 END C_SCHED_VERSIONFORMULA;
62
63 FUNCTION C_SORTFORMULA RETURN VARCHAR2 IS
64 BEGIN
65 DECLARE
66 SORT VARCHAR2(80);
67 BEGIN
68 SELECT
69 MEANING
70 INTO SORT
71 FROM
72 MFG_LOOKUPS
73 WHERE LOOKUP_TYPE = 'MRP_DATA_SELECT'
74 AND LOOKUP_CODE = P_SORT;
75 RETURN (SORT);
76 END;
77 RETURN NULL;
78 END C_SORTFORMULA;
79
80 FUNCTION C_CAT_SETFORMULA RETURN VARCHAR2 IS
81 BEGIN
82 DECLARE
83 CAT_SET VARCHAR2(30);
84 BEGIN
85 SELECT
86 CATEGORY_SET_NAME
87 INTO CAT_SET
88 FROM
89 MTL_CATEGORY_SETS
90 WHERE CATEGORY_SET_ID = P_CAT_SET;
91 RETURN (CAT_SET);
92 END;
93 RETURN NULL;
94 END C_CAT_SETFORMULA;
95
96 FUNCTION C_CATEGORY_FROMFORMULA RETURN VARCHAR2 IS
97 BEGIN
98 DECLARE
99 CATEGORY_FROM VARCHAR2(80);
100 BEGIN
101 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL) OR (P_SORT = 2)) THEN
102 CATEGORY_FROM := ',mtl_categories cat';
103 ELSE
104 CATEGORY_FROM := ' ';
105 END IF;
106 RETURN (CATEGORY_FROM);
107 END;
108 RETURN NULL;
109 END C_CATEGORY_FROMFORMULA;
110
111 FUNCTION C_CATEGORY_WHEREFORMULA RETURN VARCHAR2 IS
112 BEGIN
113 DECLARE
114 CATEGORY_WHERE VARCHAR2(200);
115 BEGIN
116 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL) OR (P_SORT = 2)) THEN
117 CATEGORY_WHERE := 'AND ic.category_id = cat.category_id(+) ' || 'AND cat.structure_id = ' || P_CAT_STRUCT;
118 ELSE
119 CATEGORY_WHERE := ' ';
120 END IF;
121 RETURN (CATEGORY_WHERE);
122 END;
123 RETURN NULL;
124 END C_CATEGORY_WHEREFORMULA;
125
126 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
127 BEGIN
128 DECLARE
129 QUERY_ID NUMBER;
130 PROC_CALL1 VARCHAR2(200);
131 CAL_CODE VARCHAR2(10);
132 CAL_EXCEPTION_SET_ID NUMBER;
133 CURRENCY_DESC VARCHAR2(80);
134 PRECISION NUMBER;
135 START_DATE DATE;
136 PERIODS NUMBER;
137 SCHEDULE_TYPE VARCHAR2(80);
138 SCHEDULE_VERSION VARCHAR2(80);
139 CAT_STRUCT_NUM NUMBER;
140 BEGIN
141 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
142 format_string := mrp_common_xmlp_pkg.get_precision(P_QTY_PRECISION);
143 LP_START_DATE := to_char(P_START_DATE, 'DD-MON-YYYY');
144 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
145 IF (P_DEBUG = 'Y') THEN
146 EXECUTE IMMEDIATE
147 'ALTER SESSION SET SQL_TRACE TRUE';
148 END IF;
149 IF ((P_SORT = 2) OR (P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL)) THEN
150 SELECT
151 STRUCTURE_ID
152 INTO CAT_STRUCT_NUM
153 FROM
154 MTL_DEFAULT_SETS_VIEW
155 WHERE FUNCTIONAL_AREA_ID = 3;
156 P_CAT_STRUCT_NUM := CAT_STRUCT_NUM;
157 END IF;
158 SELECT
159 DECODE(LOOKUP_CODE
160 ,1
161 ,12
162 ,2
163 ,24
164 ,3
165 ,36
166 ,4
167 ,48
168 ,12),
169 MRP_FORM_QUERY_S.NEXTVAL,
170 P_START_DATE
171 INTO PERIODS,QUERY_ID,START_DATE
172 FROM
173 MFG_LOOKUPS
174 WHERE LOOKUP_TYPE = 'MRP_DISPLAY_PERIODS'
175 AND LOOKUP_CODE = P_PERIODS;
176 P_PERIODS_ACTUAL := PERIODS;
177 P_QUERY_ID := QUERY_ID;
178 MRP_WEEKS_MONTHS(P_QUERY_ID
179 ,1
180 ,P_WEEKS
181 ,PERIODS
182 ,START_DATE
183 ,P_ORG_ID);
184 IF (P_SORT = 1) THEN
185 NULL;
186 END IF;
187 IF (P_SORT = 2) THEN
188 NULL;
189 END IF;
190 IF ((P_LOW_ITEM IS NOT NULL) OR (P_HIGH_ITEM IS NOT NULL)) THEN
191 NULL;
192 END IF;
193 IF ((P_LOW_CAT IS NOT NULL) OR (P_HIGH_CAT IS NOT NULL)) THEN
194 NULL;
195 END IF;
196 SELECT
197 CALENDAR_CODE,
198 CALENDAR_EXCEPTION_SET_ID
199 INTO CAL_CODE,CAL_EXCEPTION_SET_ID
200 FROM
201 MTL_PARAMETERS
202 WHERE ORGANIZATION_ID = P_ORG_ID;
203 P_CAL_CODE := CAL_CODE;
204 P_CAL_EXCEPTION_SET_ID := CAL_EXCEPTION_SET_ID;
205 SELECT
206 NAME,
207 PRECISION
208 INTO CURRENCY_DESC,PRECISION
209 FROM
210 FND_CURRENCIES_VL
211 WHERE CURRENCY_CODE = P_CURRENCY_CODE;
212 P_CURRENCY_DESC := CURRENCY_DESC;
213 P_PRECISION := PRECISION;
214 SELECT
215 MEANING
216 INTO SCHEDULE_TYPE
217 FROM
218 MFG_LOOKUPS
219 WHERE LOOKUP_TYPE = 'MRP_SCHEDULE_TYPE'
220 AND LOOKUP_CODE = P_SCHED_TYPE;
221 P_SCHED_TYPE_DESC := SCHEDULE_TYPE;
222 SELECT
223 MEANING
224 INTO SCHEDULE_VERSION
225 FROM
226 MFG_LOOKUPS
227 WHERE LOOKUP_TYPE = 'MRP_SCHEDULE_LEVEL'
228 AND LOOKUP_CODE = P_SCHED_VERSION;
229 P_SCHED_VERSION_DESC := SCHEDULE_VERSION;
230 END;
231 RETURN (TRUE);
232 END BEFOREREPORT;
233
234 FUNCTION C_P_MULTIORG_MPSFORMULA RETURN NUMBER IS
235 BEGIN
236 DECLARE
237 ORG_SELECTION NUMBER := 0;
238 USE_MULTIORG_MPS NUMBER := 0;
239 BEGIN
240 IF (P_SCHED_TYPE = 2 AND P_REPORT_MULTIORG = 2) THEN
241 SELECT
242 ORGANIZATION_SELECTION
243 INTO ORG_SELECTION
244 FROM
245 MRP_SCHEDULE_DESIGNATORS SCHED
246 WHERE ORGANIZATION_ID = P_ORG_ID
247 AND SCHEDULE_DESIGNATOR = P_SCHED_NAME;
248 IF (ORG_SELECTION = 2 OR ORG_SELECTION = 3) THEN
249 USE_MULTIORG_MPS := 1;
250 ELSE
251 USE_MULTIORG_MPS := 2;
252 END IF;
253 ELSE
254 USE_MULTIORG_MPS := 2;
255 END IF;
256 RETURN (USE_MULTIORG_MPS);
257 END;
258 RETURN NULL;
259 END C_P_MULTIORG_MPSFORMULA;
260
261 FUNCTION C_ORDER_BYFORMULA RETURN VARCHAR2 IS
262 BEGIN
263 DECLARE
264 ORDER_BY VARCHAR2(40);
265 BEGIN
266 IF (P_SORT = 3) THEN
267 ORDER_BY := 'org.organization_name, ';
268 ELSE
269 ORDER_BY := ' ';
270 END IF;
271 RETURN (ORDER_BY);
272 END;
273 RETURN ' ';
274 END C_ORDER_BYFORMULA;
275
276 FUNCTION C_P_REPORT_MULTIORGFORMULA RETURN VARCHAR2 IS
277 BEGIN
278 DECLARE
279 REPORT_MULTIORG_TEXT VARCHAR2(80) := 'Current Organization';
280 BEGIN
281 IF P_REPORT_MULTIORG = 2 THEN
282 SELECT
283 MEANING
284 INTO REPORT_MULTIORG_TEXT
285 FROM
286 MFG_LOOKUPS
287 WHERE LOOKUP_CODE = P_REPORT_MULTIORG
288 AND LOOKUP_TYPE = 'MRP_REPORT_MULTIORG';
289 END IF;
290 RETURN (REPORT_MULTIORG_TEXT);
291 END;
292 RETURN NULL;
293 END C_P_REPORT_MULTIORGFORMULA;
294
295 FUNCTION C_P_USE_MULTIORG_PLANFORMULA RETURN NUMBER IS
296 BEGIN
297 DECLARE
298 ORG_SELECTION NUMBER;
299 USE_MULTIORG_PLAN NUMBER := 2;
300 BEGIN
301 IF (P_SCHED_TYPE = 2 AND P_REPORT_MULTIORG = 2) THEN
302 SELECT
303 ORGANIZATION_SELECTION
304 INTO ORG_SELECTION
305 FROM
306 MRP_SCHEDULE_DESIGNATORS SCHED
307 WHERE ORGANIZATION_ID = P_ORG_ID
308 AND SCHEDULE_DESIGNATOR = P_SCHED_NAME;
309 ELSIF (P_SCHED_TYPE = 2 AND P_REPORT_MULTIORG = 1) THEN
310 SELECT
311 DISTINCT
312 NVL(SCHED.ORGANIZATION_SELECTION
313 ,0)
314 INTO ORG_SELECTION
315 FROM
316 MRP_SCHEDULE_DESIGNATORS SCHED,
317 MRP_ORGANIZATIONS_V MO
318 WHERE SCHED.ORGANIZATION_ID = mo.organization_id (+)
319 AND SCHED.SCHEDULE_DESIGNATOR = mo.compile_designator (+)
320 AND NVL(MO.PLANNED_ORGANIZATION
321 ,SCHED.ORGANIZATION_ID) = P_ORG_ID
322 AND SCHED.SCHEDULE_DESIGNATOR = P_SCHED_NAME;
323 END IF;
324 IF ((ORG_SELECTION = 2) OR (ORG_SELECTION = 3)) THEN
325 USE_MULTIORG_PLAN := 1;
326 ELSE
327 USE_MULTIORG_PLAN := 2;
328 END IF;
329 RETURN (USE_MULTIORG_PLAN);
330 END;
331 RETURN NULL;
332 END C_P_USE_MULTIORG_PLANFORMULA;
333
334 /*PROCEDURE MRP_WEEKS_MONTHS(ARG_QUERY_ID IN NUMBER
335 ,ARG_USER_ID IN NUMBER
336 ,ARG_WEEKS IN NUMBER
337 ,ARG_PERIODS IN NUMBER
338 ,ARG_START_DATE IN DATE
339 ,ARG_ORG_ID IN NUMBER) IS
340 BEGIN
341
342 MRP_WEEKS_MONTHS(ARG_QUERY_ID, ARG_USER_ID, ARG_WEEKS, ARG_PERIODS, ARG_START_DATE, ARG_ORG_ID);
343
344 END MRP_WEEKS_MONTHS;*/
345
346 PROCEDURE MRP_WEEKS_MONTHS(ARG_QUERY_ID IN NUMBER
347 ,ARG_USER_ID IN NUMBER
348 ,ARG_WEEKS IN NUMBER
349 ,ARG_PERIODS IN NUMBER
350 ,ARG_START_DATE IN DATE
351 ,ARG_ORG_ID IN NUMBER) IS
352 BEGIN
353 MRP_REPORTING_BUCKETS.MRP_WEEKS_MONTHS(ARG_QUERY_ID, ARG_USER_ID, ARG_WEEKS, ARG_PERIODS, ARG_START_DATE, ARG_ORG_ID);
354
355 END MRP_WEEKS_MONTHS;
356
357 END MRP_MRPRPSST_XMLP_PKG;
358
[Home] [Help]