[Home] [Help]
Skip to content
PACKAGE BODY: APPS.MRP_PARAMETER_MRPEPPS
Source
1 PACKAGE BODY MRP_PARAMETER_MRPEPPS AS
2 /* $Header: MRPBISPB.pls 120.0 2007/12/19 02:43:48 ahoque noship $ */
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 CURSOR c_business_plans IS
133 select plan_id, name from bisbv_business_plans;
134
135 CURSOR c_period_types IS
136 SELECT distinct period_type name from gl_periods;
137
138 CURSOR c_plans IS
139 SELECT distinct compile_designator name from mrp_plans
140 where data_completion_date is not null
141 and plan_completion_date is not null;
142
143 CURSOR c_orgs IS
144 SELECT organization_name name, organization_id from org_organization_definitions;
145
146 l_return_status VARCHAR2(1000);
147 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
148
149 l_launch_success boolean;
150
151
152
153 l_session_id number;
154
155 L_BUSINESS_PLAN_ID varchar2(80);
156 L_ORGANIZATION_ID varchar2(80);
157 L_PLAN1 varchar2(80);
158 L_PLAN2 varchar2(80);
159 L_PERIOD_TYPE varchar2(80);
160
161
162 BEGIN
163
164
165 if not icx_sec.validateSession THEN
166 return;
167 end if;
168
169 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
170
171 L_BUSINESS_PLAN_ID := icx_sec.getSessionAttributeValue( 'BUSINESS PLAN', l_session_id );
172 L_ORGANIZATION_ID := icx_sec.getSessionAttributeValue( 'ORGANIZATION_ID', l_session_id );
173 L_PLAN1 := icx_sec.getSessionAttributeValue( 'PLAN1', l_session_id );
174 L_PLAN2 := icx_sec.getSessionAttributeValue( 'PLAN2', l_session_id );
175 L_PERIOD_TYPE := icx_sec.getSessionAttributeValue( 'PERIOD TYPE', l_session_id );
176
177
178 /* launch the report if we have the necessary parameters */
179
180 if( force_display is null or
181 upper(force_display) = 'NO' or
182 upper(force_display) = 'N' ) then
183 l_launch_success := LaunchReport
184 (
185 l_session_id ,
186 L_BUSINESS_PLAN_ID ,
187 L_ORGANIZATION_ID ,
188 L_PLAN1 ,
189 L_PLAN2 ,
190 L_PERIOD_TYPE
191 );
192 if( l_launch_success ) then
193 return;
194 end if;
195 end if;
196
197 Before_Parameter_MRPEPPS;
198
199
200 htp.htmlOpen;
201
202 BIS_UTILITIES_PUB.Build_Report_Title( FORM_FUNCTION, RDF_FILENAME, '' );
203
204 htp.headOpen;
205 js.scriptOpen;
206 icx_util.LOVScript;
207
208 js.scriptClose;
209 htp.headClose;
210
211 htp.bodyOpen;
212
213 htp.centerOpen;
214
215
216 /* ORGANIZATION */
217
218 params(1).Label := fnd_message.get_string( 'MRP', 'MRP_ORGANIZATION_LABEL' );
219 params(1).Value := htf.formSelectOpen( 'P_ORGANIZATION_ID' );
220 FOR c1 in c_orgs LOOP
221 if( c1.organization_id = L_ORGANIZATION_ID ) then
222 params(1).Value := params(1).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.organization_id );
223 else
224 params(1).Value := params(1).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.organization_id );
225 end if;
226 END LOOP;
227 params(1).Value := params(1).Value || htf.formSelectClose;
228
229
230 /* BUSINESS PLAN */
231
232 params(2).Label := fnd_message.get_string( 'BOM', 'CST_BUSINESS_PLAN_LABEL' );
233 params(2).Value := htf.formSelectOpen( 'P_BUSINESS_PLAN' );
234 FOR c1 in c_business_plans LOOP
235 if( c1.plan_id = L_BUSINESS_PLAN_ID ) then
236 params(2).Value := params(2).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.plan_id );
237 else
238 params(2).Value := params(2).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.plan_id );
239 end if;
240 END LOOP;
241 params(2).Value := params(2).Value || htf.formSelectClose;
242
243 /* MRP PLANS */
244
245 params(3).Label := fnd_message.get_string( 'MRP', 'MRP_PLAN_NAME_LABEL' );
246 params(3).Value := htf.formSelectOpen('P_PLAN1');
247 FOR c1 in c_plans LOOP
248 params(3).Value := params(3).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.name );
249 END LOOP;
250 params(3).Value := params(3).Value || htf.formSelectClose;
251 params(3).Value := params(3).Value || ' - ' || htf.formSelectOpen( 'P_PLAN2' );
252 FOR c1 in c_plans LOOP
253 params(3).Value := params(3).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.name );
254 END LOOP;
255 params(3).Value := params(3).Value || htf.formSelectClose;
256
257
258 /* PERIOD TYPE */
259
260 params(4).Label := fnd_message.get_string( 'MRP', 'MRP_PERIOD_TYPE_LABEL' );
261 params(4).Value := htf.formSelectOpen( 'P_PERIOD_TYPE' );
262 FOR c1 in c_period_types LOOP
263 params(4).Value := params(4).Value ||
264 htf.formSelectOption( c1.name, NULL, 'value=' || c1.name);
265 END LOOP;
266 params(4).Value := params(4).Value || htf.formSelectClose;
267
268 BIS_UTILITIES_PUB.Build_Parameter_Form(
269 'NAME="param" ACTION="MRP_PARAMETER_MRPEPPS.Parameter_ActionView_MRPEPPS" METHOD="GET" ', params );
270
271
272 END Parameter_FormView_MRPEPPS;
273
274
275
276
277
278
279
280 FUNCTION Validate_Org( P_ORG_ID IN VARCHAR2,
281 P_ORG_NAME IN VARCHAR2,
282 l_organization_id out NOCOPY varchar2) RETURN BOOLEAN IS
283 l_count INTEGER;
284 BEGIN
285 if (P_ORG_NAME is NULL) then
286 -- htp.p( 'Please select an organization' );
287 error( FND_MESSAGE.get_string( 'BOM', 'CST_ORGANIZATION_LABEL' ) );
288 return FALSE;
289 end if;
290
291 select organization_id
292 into l_organization_id
293 from org_organization_definitions
294 where organization_name like P_ORG_NAME;
295
296 RETURN TRUE;
297
298 EXCEPTION
299 WHEN TOO_MANY_ROWS THEN
300 if P_ORG_ID is null then
301 -- htp.p( 'Too many matching organizations found' );
302 error( FND_MESSAGE.get_string( 'BOM', 'CST_ORGANIZATION_LABEL' ) );
303 return FALSE;
304 end if;
305
306 select max(organization_id), count(*)
307 into l_organization_id, l_count
308 from org_organization_definitions
309 where organization_name like P_ORG_NAME AND
310 organization_id = P_ORG_ID;
311
312 if l_count = 1 then
313 RETURN TRUE;
314 else
315 -- htp.p( 'Too many matching organizations found' );
316 error( FND_MESSAGE.get_string( 'BOM', 'CST_ORGANIZATION_LABEL' ) );
317 RETURN FALSE;
318 end if;
319
320 WHEN NO_DATA_FOUND THEN
321 -- htp.p( 'No matching organization found' );
322 error( FND_MESSAGE.get_string( 'BOM', 'CST_ORGANIZATION_LABEL' ) );
323 return FALSE;
324
325 WHEN OTHERS THEN
326 -- htp.p( 'Uncaught exception' );
327 error( FND_MESSAGE.get_string( 'BOM', 'CST_ORGANIZATION_LABEL' ) );
328 return FALSE;
329
330 END Validate_Org;
331
332
333 FUNCTION Validate_Plan(P_PLAN_NAME IN VARCHAR2,
334 l_plan_name out NOCOPY varchar2) RETURN BOOLEAN IS
335 l_count INTEGER;
336 BEGIN
337
338 select compile_designator
339 into l_plan_name
340 from mrp_plans
341 where compile_designator like P_PLAN_NAME;
342
343 RETURN TRUE;
344
345 EXCEPTION
346 WHEN TOO_MANY_ROWS THEN
347 if P_PLAN_NAME is null then
348 -- htp.p( 'Too many matching areas found' );
349 error( FND_MESSAGE.get_string( 'BOM', 'CST_AREA_LABEL' ) );
350 return FALSE;
351 end if;
352
353 select max(compile_designator), count(*)
354 into l_plan_name, l_count
355 from mrp_plans
356 where compile_designator like P_PLAN_NAME;
357
358 if l_count = 1 then
359 RETURN TRUE;
360 else
361 -- htp.p( 'Too many matching areas found' );
362 error( FND_MESSAGE.get_string( 'BOM', 'CST_AREA_LABEL' ) );
363 RETURN FALSE;
364 end if;
365
366 WHEN NO_DATA_FOUND THEN
367 -- htp.p( 'No matching area found' );
368 error( FND_MESSAGE.get_string( 'BOM', 'CST_AREA_LABEL' ) );
369 return FALSE;
370
371 WHEN OTHERS THEN
372 -- htp.p( 'Uncaught exception' );
373 error( FND_MESSAGE.get_string( 'BOM', 'CST_AREA_LABEL' ) );
374 return FALSE;
375
376 END Validate_Plan;
377
378
379 /*
380 * Parameter_ActionView_MRPEPPS
381 *
382 * This function is invoked when the user clicks
383 * the OK button in the HTML page generated by
384 * Parameter_FormView_MRPEPPS. It will validate
385 * the input parameters and launch the Sales Revenue
386 * report.
387 */
388 PROCEDURE Parameter_ActionView_MRPEPPS(
389 P_BUSINESS_PLAN NUMBER,
390 P_ORGANIZATION_ID NUMBER,
391 P_PLAN1 VARCHAR2,
392 P_PLAN2 VARCHAR2,
393 P_PERIOD_TYPE VARCHAR2)
394 IS
395
396 l_session_id number;
397
398 L_BUSINESS_PLAN_ID varchar2(80);
399 L_ORGANIZATION_ID varchar2(80);
400 L_PLAN1 varchar2(80);
401 L_PLAN2 varchar2(80);
402 L_PERIOD_TYPE varchar2(80);
403
404 l_launch_success boolean;
405
406 BEGIN
407
408 if not icx_sec.validateSession THEN
409 return;
410 end if;
411
412 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
413 /*
414 IF( Validate_Org
415 ( P_ORGANIZATION_ID, P_ORGANIZATION_NAME,
416 L_ORGANIZATION_ID ) = FALSE OR
417 Validate_Plan
418 ( P_PLAN_NAME, L_PLAN1 ) = FALSE OR
419 Validate_Plan
420 ( P_PLAN_NAME, L_PLAN2 ) = FALSE ) THEN
421 return;
422 END IF;
423 */
424 L_BUSINESS_PLAN_ID := p_business_plan;
425 L_PERIOD_TYPE := p_period_type;
426 L_ORGANIZATION_ID := p_organization_id;
427 L_PLAN1 := p_plan1;
428 L_PLAN2 := p_plan2;
429
430 icx_sec.putSessionAttributeValue( 'BUSINESS PLAN', L_BUSINESS_PLAN_ID , l_session_id );
431 icx_sec.putSessionAttributeValue( 'ORGANIZATION', L_ORGANIZATION_ID , l_session_id );
432 icx_sec.putSessionAttributeValue( 'PLAN1', L_PLAN1 , l_session_id );
433 icx_sec.putSessionAttributeValue( 'PLAN2', L_PLAN2 , l_session_id );
434 icx_sec.putSessionAttributeValue( 'PERIOD TYPE', L_PERIOD_TYPE , l_session_id );
435
436
437 l_launch_success := LaunchReport
438 (
439 l_session_id ,
440 L_BUSINESS_PLAN_ID ,
441 L_ORGANIZATION_ID ,
442 L_PLAN1 ,
443 L_PLAN2 ,
444 L_PERIOD_TYPE
445 );
446
447
448 END Parameter_ActionView_MRPEPPS;
449
450
451
452
453
454 END MRP_PARAMETER_MRPEPPS;