[Home] [Help]
PACKAGE BODY: APPS.MRP_MRPRPPIT_XMLP_PKG
Source
1 PACKAGE BODY "MRP_MRPRPPIT_XMLP_PKG" AS
2 /* $Header: MRPRPPITB.pls 120.0 2007/12/31 13:58:30 dwkrishn noship $ */
3 FUNCTION C_PLANNED_ORG_CODEFORMULA(C_PLANNED_ORGANIZATION IN NUMBER) RETURN VARCHAR2 IS
4 BEGIN
5 DECLARE
6 ORG_CODE VARCHAR2(3);
7 BEGIN
8 SELECT
9 ORGANIZATION_CODE
10 INTO ORG_CODE
11 FROM
12 MTL_PARAMETERS
13 WHERE ORGANIZATION_ID = C_PLANNED_ORGANIZATION;
14 RETURN (ORG_CODE);
15 END;
16 RETURN NULL;
17 END C_PLANNED_ORG_CODEFORMULA;
18
19 FUNCTION C_COST_TYPEFORMULA RETURN VARCHAR2 IS
20 BEGIN
21 DECLARE
22 COST_TYPE VARCHAR2(240);
23 BEGIN
24 SELECT
25 DESCRIPTION
26 INTO COST_TYPE
27 FROM
28 CST_COST_TYPES
29 WHERE COST_TYPE_ID = P_COST_TYPE;
30 RETURN (COST_TYPE);
31 END;
32 RETURN NULL;
33 END C_COST_TYPEFORMULA;
34
35 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
36 BEGIN
37 DECLARE
38 Q_ID NUMBER := 0;
39 EXPLOSION_DATE VARCHAR2(20);
40 SNAPSHOT_DATE VARCHAR2(20);
41 PLAN_DATE VARCHAR2(20);
42 START_DATE varchar2(30);
43 PLAN_NAME VARCHAR2(10);
44 SCHED_NAME VARCHAR2(10);
45 ORG_ID NUMBER;
46 CURRENCY_DESC VARCHAR2(80);
47 PRECISION NUMBER := 1;
48 PERIODS NUMBER;
49 DEF_COST_TYPE_ID NUMBER;
50 EXPLOSION_INCOMPLETE EXCEPTION;
51 SNAPSHOT_INCOMPLETE EXCEPTION;
52 PLAN_INCOMPLETE EXCEPTION;
53 CURSOR C IS
54 SELECT
55 ORG_V.COMPILE_DESIGNATOR,
56 PLN_SCHED.INPUT_DESIGNATOR_NAME,
57 ORG_V.PLANNED_ORGANIZATION
58 FROM
59 MRP_PLAN_ORGANIZATIONS_V ORG_V,
60 MRP_PLAN_SCHEDULES_V PLN_SCHED
61 WHERE PLN_SCHED.INPUT_ORGANIZATION_ID = ORG_V.PLANNED_ORGANIZATION
62 AND PLN_SCHED.COMPILE_DESIGNATOR = ORG_V.COMPILE_DESIGNATOR
63 AND DECODE(P_ORG_TYPE
64 ,1
65 ,ORG_V.PLANNED_ORGANIZATION
66 ,ORG_V.ORGANIZATION_ID) = P_ORG_ID
67 AND ORG_V.COMPILE_DESIGNATOR = P_PLAN_NAME;
68 BEGIN
69 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
70 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
71 IF (P_DEBUG = 'Y') THEN
72 EXECUTE IMMEDIATE
73 'ALTER SESSION SET SQL_TRACE TRUE';
74 END IF;
75 SELECT
76 DECODE(LOOKUP_CODE
77 ,1
78 ,12
79 ,2
80 ,24
81 ,3
82 ,36
83 ,4
84 ,48
85 ,12)
86 INTO PERIODS
87 FROM
88 MFG_LOOKUPS
89 WHERE LOOKUP_TYPE = 'MRP_DISPLAY_PERIODS'
90 AND LOOKUP_CODE = P_PERIODS;
91 P_PERIODS_ACTUAL := PERIODS;
92 SELECT
93 MRP_FORM_QUERY_S.NEXTVAL
94 INTO Q_ID
95 FROM
96 DUAL;
97 P_QUERY_ID := Q_ID;
98
99
100
101
102
103
104
105 SELECT
106 DISTINCT
107 PLAN.EXPLOSION_COMPLETION_DATE,
108 PLAN.DATA_COMPLETION_DATE,
109 PLAN.PLAN_COMPLETION_DATE,
110 PLAN.PLAN_COMPLETION_DATE
111 INTO EXPLOSION_DATE,SNAPSHOT_DATE,PLAN_DATE,START_DATE
112 FROM
113 MRP_PLAN_ORGANIZATIONS_V ORG_V,
114 MRP_PLANS PLAN
115 WHERE DECODE(P_ORG_TYPE
116 ,1
117 ,ORG_V.PLANNED_ORGANIZATION
118 ,ORG_V.ORGANIZATION_ID) = P_ORG_ID
119 AND ORG_V.COMPILE_DESIGNATOR = P_PLAN_NAME
120 AND ORG_V.ORGANIZATION_ID = PLAN.ORGANIZATION_ID
121 AND ORG_V.COMPILE_DESIGNATOR = PLAN.COMPILE_DESIGNATOR;
122 IF EXPLOSION_DATE IS NULL THEN
123 RAISE EXPLOSION_INCOMPLETE;
124 ELSIF SNAPSHOT_DATE IS NULL THEN
125 RAISE SNAPSHOT_INCOMPLETE;
126 ELSIF PLAN_DATE IS NULL THEN
127 RAISE PLAN_INCOMPLETE;
128 END IF;
129 SELECT
130 DEFAULT_COST_TYPE_ID
131 INTO DEF_COST_TYPE_ID
132 FROM
133 CST_COST_TYPES
134 WHERE COST_TYPE_ID = P_COST_TYPE;
135 OPEN C;
136 LOOP
137 FETCH C
138 INTO PLAN_NAME,SCHED_NAME,ORG_ID;
139 EXIT WHEN (C%NOTFOUND);
140 SELECT
141 MRP_FORM_QUERY_S.NEXTVAL
142 INTO Q_ID
143 FROM
144 DUAL;
145 MRP_WEEKS_MONTHS(Q_ID
146 ,1
147 ,P_WEEKS
148 ,PERIODS
149 ,START_DATE
150 ,ORG_ID);
151 MRP_CALCULATE_INVENTORY_TURNS(Q_ID
152 ,ORG_ID
153 ,PLAN_NAME
154 ,SCHED_NAME
155 ,P_COST_TYPE
156 ,DEF_COST_TYPE_ID);
157 EXECUTE IMMEDIATE
158 'UPDATE mrp_form_query SET query_id = ' || TO_CHAR(P_QUERY_ID) || ' WHERE query_id = ' || TO_CHAR(Q_ID);
159 END LOOP;
160 CLOSE C;
161 SELECT
162 NAME,
163 PRECISION
164 INTO CURRENCY_DESC,PRECISION
165 FROM
166 FND_CURRENCIES_VL
167 WHERE CURRENCY_CODE = P_CURRENCY_CODE;
168 P_CURRENCY_DESC := CURRENCY_DESC;
169 P_PRECISION := PRECISION;
170 EXCEPTION
171 WHEN EXPLOSION_INCOMPLETE THEN
172 /*SRW.USER_EXIT('FND MESSAGE_NAME NAME = "PLAN-PROCESS NOT COMPLETED"')*/NULL;
173 FND_MESSAGE.SET_TOKEN('PROCESS'
174 ,'E_EXPLODER');
175 P_MSG_BUF := FND_MESSAGE.GET;
176 /*SRW.MESSAGE('10001'
177 ,P_MSG_BUF)*/NULL;
178 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
179 WHEN SNAPSHOT_INCOMPLETE THEN
180 /*SRW.USER_EXIT('FND MESSAGE_NAME NAME = "PLAN-PROCESS NOT COMPLETED"')*/NULL;
181 FND_MESSAGE.SET_TOKEN('PROCESS'
182 ,'E_SNAPSHOT');
183 P_MSG_BUF := FND_MESSAGE.GET;
184 /*SRW.MESSAGE('10002'
185 ,P_MSG_BUF)*/NULL;
186 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
187 WHEN PLAN_INCOMPLETE THEN
188 /*SRW.USER_EXIT('FND MESSAGE_NAME NAME = "PLAN-PROCESS NOT COMPLETED"')*/NULL;
189 FND_MESSAGE.SET_TOKEN('PROCESS'
190 ,'E_PLANNER');
191 P_MSG_BUF := FND_MESSAGE.GET;
192 /*SRW.MESSAGE('10003'
193 ,P_MSG_BUF)*/NULL;
194 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
195 END;
196 RETURN (TRUE);
197 END BEFOREREPORT;
198
199 FUNCTION C_P_MULTI_ORGFORMULA RETURN NUMBER IS
200 BEGIN
201 DECLARE
202 ORG_SELECTION NUMBER := 1;
203 MULTI_ORG NUMBER;
204 BEGIN
205 SELECT
206 DISTINCT
207 ORGANIZATION_SELECTION
208 INTO ORG_SELECTION
209 FROM
210 MRP_PLAN_ORGANIZATIONS_V
211 WHERE DECODE(P_ORG_TYPE
212 ,1
213 ,PLANNED_ORGANIZATION
214 ,ORGANIZATION_ID) = P_ORG_ID
215 AND COMPILE_DESIGNATOR = P_PLAN_NAME;
216 IF ((ORG_SELECTION = 2) OR (ORG_SELECTION = 3)) THEN
217 MULTI_ORG := 1;
218 ELSE
219 MULTI_ORG := 2;
220 END IF;
221 RETURN (MULTI_ORG);
222 END;
223 RETURN NULL;
224 END C_P_MULTI_ORGFORMULA;
225
226 FUNCTION AFTERREPORT RETURN BOOLEAN IS
227 BEGIN
228 BEGIN
229 IF P_QUERY_ID IS NOT NULL THEN
230 EXECUTE IMMEDIATE
231 '
232 DELETE FROM mrp_form_query WHERE query_id = ' || TO_CHAR(P_QUERY_ID);
233 END IF;
234 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
235 END;
236 RETURN (TRUE);
237 END AFTERREPORT;
238
239 FUNCTION C_BEGIN_INV_D2FORMULA(C_BEGIN_INV_D IN VARCHAR2) RETURN VARCHAR2 IS
240 BEGIN
241 RETURN (LTRIM(C_BEGIN_INV_D));
242 END C_BEGIN_INV_D2FORMULA;
243
244 PROCEDURE MRP_WEEKS_MONTHS(ARG_QUERY_ID IN NUMBER
245 ,ARG_USER_ID IN NUMBER
246 ,ARG_WEEKS IN NUMBER
247 ,ARG_PERIODS IN NUMBER
248 ,ARG_START_DATE IN DATE
249 ,ARG_ORG_ID IN NUMBER) IS
250 BEGIN
251 /* STPROC.INIT('begin MRP_REPORTING_BUCKETS.MRP_WEEKS_MONTHS(:ARG_QUERY_ID, :ARG_USER_ID, :ARG_WEEKS, :ARG_PERIODS, :ARG_START_DATE, :ARG_ORG_ID); end;');
252 STPROC.BIND_I(ARG_QUERY_ID);
253 STPROC.BIND_I(ARG_USER_ID);
254 STPROC.BIND_I(ARG_WEEKS);
255 STPROC.BIND_I(ARG_PERIODS);
256 STPROC.BIND_I(ARG_START_DATE);
257 STPROC.BIND_I(ARG_ORG_ID);
258 STPROC.EXECUTE;*/
259 MRP_REPORTING_BUCKETS.MRP_WEEKS_MONTHS(ARG_QUERY_ID, ARG_USER_ID, ARG_WEEKS, ARG_PERIODS, ARG_START_DATE, ARG_ORG_ID);
260 END MRP_WEEKS_MONTHS;
261
262 PROCEDURE MRP_CALCULATE_INVENTORY_TURNS(ARG_QUERY_ID IN NUMBER
263 ,ARG_ORG_ID IN NUMBER
264 ,ARG_COMPILE_DESIG IN VARCHAR2
265 ,ARG_SCHED_DESIG IN VARCHAR2
266 ,ARG_COST_TYPE IN NUMBER
267 ,ARG_DEF_COST_TYPE IN NUMBER) IS
268 BEGIN
269 /*STPROC.INIT('begin MRP_REPORT_INV_TURNS.MRP_CALCULATE_INVENTORY_TURNS(:ARG_QUERY_ID, :ARG_ORG_ID, :ARG_COMPILE_DESIG, :ARG_SCHED_DESIG, :ARG_COST_TYPE, :ARG_DEF_COST_TYPE); end;');
270 STPROC.BIND_I(ARG_QUERY_ID);
271 STPROC.BIND_I(ARG_ORG_ID);
272 STPROC.BIND_I(ARG_COMPILE_DESIG);
273 STPROC.BIND_I(ARG_SCHED_DESIG);
274 STPROC.BIND_I(ARG_COST_TYPE);
275 STPROC.BIND_I(ARG_DEF_COST_TYPE);
276 STPROC.EXECUTE;*/
277 MRP_REPORT_INV_TURNS.MRP_CALCULATE_INVENTORY_TURNS(ARG_QUERY_ID, ARG_ORG_ID, ARG_COMPILE_DESIG, ARG_SCHED_DESIG, ARG_COST_TYPE, ARG_DEF_COST_TYPE);
278 END MRP_CALCULATE_INVENTORY_TURNS;
279
280 END MRP_MRPRPPIT_XMLP_PKG;
281