[Home] [Help]
PACKAGE BODY: APPS.MRP_PARAMETER_MRPEPPS
Source
1 PACKAGE BODY MRP_PARAMETER_MRPEPPS AS
2 /* $Header: MRPBISPB.pls 120.2 2011/08/08 20:33:56 schaudha ship $ */
3
4
5
6 /* some constants */
7 FORM_FUNCTION constant varchar2(20) := 'BIS_MRPEPPS';
8 RDF_FILENAME constant varchar2(20) := 'MRPEPPS';
9 DATE_FORMAT constant varchar2(20) := 'DD-MON-YYYY';
10
11
12
13
14 /*
15 * error
16 *
17 * This creates the pretty looking error message page.
18 */
19 PROCEDURE error( P_FIELD IN VARCHAR2 ) IS
20 BEGIN
21 -- BIS_UTILITIES_PUB.Build_Report_Title( FORM_FUNCTION, RDF_FILENAME, NULL );
22 htp.p( '<FONT face="arial" size=+1><BR><BR>' ||
23 fnd_message.get_string( 'WIP', 'INVALID_PARAM' ) || ': <B>' ||
24 P_FIELD || '</B></FONT>');
25 htp.p( '<FONT face="arial"><BR><BR>' ||
26 fnd_message.get_string( 'WIP', 'INVALID_PARAM_INSTRUCTION' ) ||
27 '</FONT>');
28 END error;
29
30
31
32
33 /*
34 * Before_Parameter_MRPEPPS
35 *
36 * This function is called by Parameter_FormView_MRPEPPS
37 * to perform initial setups. It should not be invoked
38 * directly.
39 */
40 PROCEDURE Before_Parameter_MRPEPPS IS
41 l_user_id NUMBER;
42 l_resp_id NUMBER;
43 l_appl_id NUMBER;
44 l_org_id NUMBER;
45 BEGIN
46 -- Initialize the report
47 -- FND_GLOBAL.apps_initialize(l_user_id, l_resp_id, l_appl_id);
48 l_org_id := fnd_profile.value('ORG_ID');
49 FND_CLIENT_INFO.set_org_context(l_org_id);
50 END Before_Parameter_MRPEPPS;
51
52
53
54
55 /*
56 * After_Parameter_MRPEPPS
57 *
58 * This function is called by Parameter_ActionView_MRPEPPS
59 * to perform validations. It should not be invoked
60 * directly.
61 */
62 PROCEDURE After_Parameter_MRPEPPS IS
63 BEGIN
64 null;
65 END After_Parameter_MRPEPPS;
66
67
68
69
70 /*
71 * LaunchReport
72 * Launches the report using parameters in
73 * the ICX session attibutes.
74 * Returns TRUE if all necessary parameters are present,
75 * and the report is launched. Otherwise returns FALSE.
76 */
77
78 function LaunchReport(
79 l_session_id in number,
80 L_BUSINESS_PLAN_ID in varchar2,
81 L_ORGANIZATION_ID in varchar2,
82 L_PLAN1 in varchar2,
83 L_PLAN2 in varchar2,
84 L_PERIOD_TYPE in varchar2
85 ) return boolean is
86 begin
87
88 if( L_BUSINESS_PLAN_ID is null or
89 L_ORGANIZATION_ID is null or
90 L_PLAN1 is null or
91 L_PLAN2 is null or
92 L_PERIOD_TYPE is null ) then
93 return false;
94 end if;
95
96
97 /*
98 * Commenting out for bug 6687733
99
100 OracleOASIS.RunReport(
101 report => RDF_FILENAME,
102 parameters =>
103 replace(
104 'paramform=NO*' ||
105 'P_BIS_PLAN=' || L_BUSINESS_PLAN_ID || '*' ||
106 'P_ORGANIZATION_ID=' || L_ORGANIZATION_ID || '*' ||
107 'PF_1=' || L_PLAN1 || '*' ||
108 'PF_2=' || L_PLAN2 || '*' ||
109 'P_PERIOD_TYPE=' || L_PERIOD_TYPE || '*',
110 ' ', '%20' ),
111 paramform=> 'NO');
112
113 */
114 return true;
115
116 end LaunchReport;
117
118
119
120 /*
121 * Parameter_FormView_MRPEPPS
122 *
123 * This function is invoked via a form function
124 * and is the entry point into this package.
125 * It creates the HTML parameter page used by
126 * the BIS Sales Revenue report.
127 */
128 PROCEDURE Parameter_FormView_MRPEPPS (force_display in varchar2 default null ) IS
129
130 --params BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type;
131
132 /*
133 CURSOR c_business_plans IS
134 select plan_id, name from bisbv_business_plans;
135 */
136 CURSOR c_period_types IS
137 SELECT distinct period_type name from gl_periods;
138
139 CURSOR c_plans IS
140 SELECT distinct compile_designator name from mrp_plans
141 where data_completion_date is not null
142 and plan_completion_date is not null;
143
144 CURSOR c_orgs IS
145 SELECT organization_name name, organization_id from org_organization_definitions;
146
147 l_return_status VARCHAR2(1000);
148 -- l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
149
150 l_launch_success boolean;
151
152
153
154 l_session_id number;
155
156 L_BUSINESS_PLAN_ID varchar2(80);
157 L_ORGANIZATION_ID varchar2(80);
158 L_PLAN1 varchar2(80);
159 L_PLAN2 varchar2(80);
160 L_PERIOD_TYPE varchar2(80);
161
162
163 BEGIN
164
165
166 if not icx_sec.validateSession THEN
167 return;
168 end if;
169
170 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
171
172 L_BUSINESS_PLAN_ID := icx_sec.getSessionAttributeValue( 'BUSINESS PLAN', l_session_id );
173 L_ORGANIZATION_ID := icx_sec.getSessionAttributeValue( 'ORGANIZATION_ID', l_session_id );
174 L_PLAN1 := icx_sec.getSessionAttributeValue( 'PLAN1', l_session_id );
175 L_PLAN2 := icx_sec.getSessionAttributeValue( 'PLAN2', l_session_id );
176 L_PERIOD_TYPE := icx_sec.getSessionAttributeValue( 'PERIOD TYPE', l_session_id );
177
178
179 /* launch the report if we have the necessary parameters */
180
181 if( force_display is null or
182 upper(force_display) = 'NO' or
183 upper(force_display) = 'N' ) then
184 l_launch_success := LaunchReport
185 (
186 l_session_id ,
187 L_BUSINESS_PLAN_ID ,
188 L_ORGANIZATION_ID ,
189 L_PLAN1 ,
190 L_PLAN2 ,
191 L_PERIOD_TYPE
192 );
193 if( l_launch_success ) then
194 return;
195 end if;
196 end if;
197
198 Before_Parameter_MRPEPPS;
199
200
201 htp.htmlOpen;
202
203 -- BIS_UTILITIES_PUB.Build_Report_Title( FORM_FUNCTION, RDF_FILENAME, '' );
204
205 htp.headOpen;
206 js.scriptOpen;
207 icx_util.LOVScript;
208
209 js.scriptClose;
210 htp.headClose;
211
212 htp.bodyOpen;
213
214 htp.centerOpen;
215
216
217 /* ORGANIZATION */
218 /*
219 params(1).Label := fnd_message.get_string( 'MRP', 'MRP_ORGANIZATION_LABEL' );
220 params(1).Value := htf.formSelectOpen( 'P_ORGANIZATION_ID' );
221 FOR c1 in c_orgs LOOP
222 if( c1.organization_id = L_ORGANIZATION_ID ) then
223 params(1).Value := params(1).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.organization_id );
224 else
225 params(1).Value := params(1).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.organization_id );
226 end if;
227 END LOOP;
228 params(1).Value := params(1).Value || htf.formSelectClose;
229
230 */
231
232 /* BUSINESS PLAN */
233 /*
234 params(2).Label := fnd_message.get_string( 'BOM', 'CST_BUSINESS_PLAN_LABEL' );
235 params(2).Value := htf.formSelectOpen( 'P_BUSINESS_PLAN' );
236 FOR c1 in c_business_plans LOOP
237 if( c1.plan_id = L_BUSINESS_PLAN_ID ) then
238 params(2).Value := params(2).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.plan_id );
239 else
240 params(2).Value := params(2).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.plan_id );
241 end if;
242 END LOOP;
243 params(2).Value := params(2).Value || htf.formSelectClose;
244 */
245 /* MRP PLANS */
246 /*
247 params(3).Label := fnd_message.get_string( 'MRP', 'MRP_PLAN_NAME_LABEL' );
248 params(3).Value := htf.formSelectOpen('P_PLAN1');
249 FOR c1 in c_plans LOOP
250 params(3).Value := params(3).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.name );
251 END LOOP;
252 params(3).Value := params(3).Value || htf.formSelectClose;
253 params(3).Value := params(3).Value || ' - ' || htf.formSelectOpen( 'P_PLAN2' );
254 FOR c1 in c_plans LOOP
255 params(3).Value := params(3).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.name );
256 END LOOP;
257 params(3).Value := params(3).Value || htf.formSelectClose;
258
259 */
260 /* PERIOD TYPE */
261 /*
262 params(4).Label := fnd_message.get_string( 'MRP', 'MRP_PERIOD_TYPE_LABEL' );
263 params(4).Value := htf.formSelectOpen( 'P_PERIOD_TYPE' );
264 FOR c1 in c_period_types LOOP
265 params(4).Value := params(4).Value ||
266 htf.formSelectOption( c1.name, NULL, 'value=' || c1.name);
267 END LOOP;
268 params(4).Value := params(4).Value || htf.formSelectClose;
269
270 BIS_UTILITIES_PUB.Build_Parameter_Form(
271 'NAME="param" ACTION="MRP_PARAMETER_MRPEPPS.Parameter_ActionView_MRPEPPS" METHOD="GET" ', params );
272 */
273
274 END Parameter_FormView_MRPEPPS;
275
276
277
278
279
280
281
282 FUNCTION Validate_Org( P_ORG_ID IN VARCHAR2,
283 P_ORG_NAME IN VARCHAR2,
284 l_organization_id out NOCOPY varchar2) RETURN BOOLEAN IS
285 l_count INTEGER;
286 BEGIN
287 if (P_ORG_NAME is NULL) then
288 -- htp.p( 'Please select an organization' );
289 error( FND_MESSAGE.get_string( 'BOM', 'CST_ORGANIZATION_LABEL' ) );
290 return FALSE;
291 end if;
292
293 select organization_id
294 into l_organization_id
295 from org_organization_definitions
296 where organization_name like P_ORG_NAME;
297
298 RETURN TRUE;
299
300 EXCEPTION
301 WHEN TOO_MANY_ROWS THEN
302 if P_ORG_ID is null then
303 -- htp.p( 'Too many matching organizations found' );
304 error( FND_MESSAGE.get_string( 'BOM', 'CST_ORGANIZATION_LABEL' ) );
305 return FALSE;
306 end if;
307
308 select max(organization_id), count(*)
309 into l_organization_id, l_count
310 from org_organization_definitions
311 where organization_name like P_ORG_NAME AND
312 organization_id = P_ORG_ID;
313
314 if l_count = 1 then
315 RETURN TRUE;
316 else
317 -- htp.p( 'Too many matching organizations found' );
318 error( FND_MESSAGE.get_string( 'BOM', 'CST_ORGANIZATION_LABEL' ) );
319 RETURN FALSE;
320 end if;
321
322 WHEN NO_DATA_FOUND THEN
323 -- htp.p( 'No matching organization found' );
324 error( FND_MESSAGE.get_string( 'BOM', 'CST_ORGANIZATION_LABEL' ) );
325 return FALSE;
326
327 WHEN OTHERS THEN
328 -- htp.p( 'Uncaught exception' );
329 error( FND_MESSAGE.get_string( 'BOM', 'CST_ORGANIZATION_LABEL' ) );
330 return FALSE;
331
332 END Validate_Org;
333
334
335 FUNCTION Validate_Plan(P_PLAN_NAME IN VARCHAR2,
336 l_plan_name out NOCOPY varchar2) RETURN BOOLEAN IS
337 l_count INTEGER;
338 BEGIN
339
340 select compile_designator
341 into l_plan_name
342 from mrp_plans
343 where compile_designator like P_PLAN_NAME;
344
345 RETURN TRUE;
346
347 EXCEPTION
348 WHEN TOO_MANY_ROWS THEN
349 if P_PLAN_NAME is null then
350 -- htp.p( 'Too many matching areas found' );
351 error( FND_MESSAGE.get_string( 'BOM', 'CST_AREA_LABEL' ) );
352 return FALSE;
353 end if;
354
355 select max(compile_designator), count(*)
356 into l_plan_name, l_count
357 from mrp_plans
358 where compile_designator like P_PLAN_NAME;
359
360 if l_count = 1 then
361 RETURN TRUE;
362 else
363 -- htp.p( 'Too many matching areas found' );
364 error( FND_MESSAGE.get_string( 'BOM', 'CST_AREA_LABEL' ) );
365 RETURN FALSE;
366 end if;
367
368 WHEN NO_DATA_FOUND THEN
369 -- htp.p( 'No matching area found' );
370 error( FND_MESSAGE.get_string( 'BOM', 'CST_AREA_LABEL' ) );
371 return FALSE;
372
373 WHEN OTHERS THEN
374 -- htp.p( 'Uncaught exception' );
375 error( FND_MESSAGE.get_string( 'BOM', 'CST_AREA_LABEL' ) );
376 return FALSE;
377
378 END Validate_Plan;
379
380
381 /*
382 * Parameter_ActionView_MRPEPPS
383 *
384 * This function is invoked when the user clicks
385 * the OK button in the HTML page generated by
386 * Parameter_FormView_MRPEPPS. It will validate
387 * the input parameters and launch the Sales Revenue
388 * report.
389 */
390 PROCEDURE Parameter_ActionView_MRPEPPS(
391 P_BUSINESS_PLAN NUMBER,
392 P_ORGANIZATION_ID NUMBER,
393 P_PLAN1 VARCHAR2,
394 P_PLAN2 VARCHAR2,
395 P_PERIOD_TYPE VARCHAR2)
396 IS
397
398 l_session_id number;
399
400 L_BUSINESS_PLAN_ID varchar2(80);
401 L_ORGANIZATION_ID varchar2(80);
402 L_PLAN1 varchar2(80);
403 L_PLAN2 varchar2(80);
404 L_PERIOD_TYPE varchar2(80);
405
406 l_launch_success boolean;
407
408 BEGIN
409
410 if not icx_sec.validateSession THEN
411 return;
412 end if;
413
414 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
415 /*
416 IF( Validate_Org
417 ( P_ORGANIZATION_ID, P_ORGANIZATION_NAME,
418 L_ORGANIZATION_ID ) = FALSE OR
419 Validate_Plan
420 ( P_PLAN_NAME, L_PLAN1 ) = FALSE OR
421 Validate_Plan
422 ( P_PLAN_NAME, L_PLAN2 ) = FALSE ) THEN
423 return;
424 END IF;
425 */
426 L_BUSINESS_PLAN_ID := p_business_plan;
427 L_PERIOD_TYPE := p_period_type;
428 L_ORGANIZATION_ID := p_organization_id;
429 L_PLAN1 := p_plan1;
430 L_PLAN2 := p_plan2;
431
432 icx_sec.putSessionAttributeValue( 'BUSINESS PLAN', L_BUSINESS_PLAN_ID , l_session_id );
433 icx_sec.putSessionAttributeValue( 'ORGANIZATION', L_ORGANIZATION_ID , l_session_id );
434 icx_sec.putSessionAttributeValue( 'PLAN1', L_PLAN1 , l_session_id );
435 icx_sec.putSessionAttributeValue( 'PLAN2', L_PLAN2 , l_session_id );
436 icx_sec.putSessionAttributeValue( 'PERIOD TYPE', L_PERIOD_TYPE , l_session_id );
437
438
439 l_launch_success := LaunchReport
440 (
441 l_session_id ,
442 L_BUSINESS_PLAN_ID ,
443 L_ORGANIZATION_ID ,
444 L_PLAN1 ,
445 L_PLAN2 ,
446 L_PERIOD_TYPE
447 );
448
449
450 END Parameter_ActionView_MRPEPPS;
451
452
453
454
455
456 END MRP_PARAMETER_MRPEPPS;