[Home] [Help]
PACKAGE BODY: APPS.MRP_PARAMETER_MRPFSETA
Source
1 PACKAGE BODY MRP_PARAMETER_MRPFSETA as
2 /* $Header: MRPBISFB.pls 120.1 2007/12/19 02:34:04 ahoque noship $ */
3
4
5 PROCEDURE error( P_FIELD IN VARCHAR2 ) IS
6 BEGIN
7 BIS_UTILITIES_PUB.Build_Report_Title( 'BIS_MRPFSETA', 'MRPFSETA', NULL );
8 htp.p( '<FONT face="arial" size=+1><BR><BR>' ||
9 fnd_message.get_string( 'WIP', 'INVALID_PARAM' ) || ': <B>' ||
10 P_FIELD || '</B></FONT>');
11 htp.p( '<FONT face="arial"><BR><BR>' ||
12 fnd_message.get_string( 'WIP', 'INVALID_PARAM_INSTRUCTION' ) ||
13 '</FONT>');
14 END error;
15
16
17 /*
18
19 FUNCTION Validate_Org(P_ORG_ID IN OUT NUMBER,
20 P_ORG_NAME IN VARCHAR2 default null)
21 RETURN BOOLEAN IS
22 l_org_id number := 0;
23 l_exist number := 0;
24 l_count number := 0;
25 BEGIN
26
27 if (P_ORG_NAME is NULL) then
28 return FALSE;
29 end if;
30
31 select max(organization_id), count(organization_name)
32 into l_org_id, l_count
33 from org_organization_definitions
34 where organization_name = P_ORG_NAME;
35 if (l_org_id is NULL) then
36 goto no_org_found;
37 end if;
38 if (l_count > 1) then -- duplicate org name, verify id
39 select 1
40 into l_exist
41 from org_organization_definitions
42 where organization_name = P_ORG_NAME
43 and organization_id = P_ORG_ID;
44 end if;
45
46 if (l_org_id <> P_ORG_ID) then
47 P_ORG_ID := l_org_id;
48 end if;
49
50 return TRUE;
51
52 << no_org_found >>
53 htp.p('no org found at all');
54 return FALSE;
55
56 EXCEPTION
57 WHEN OTHERS then
58 htp.p('no org id for duplicate data');
59 return FALSE;
60
61 END Validate_Org;
62
63 FUNCTION Validate_Per(p_org_id IN NUMBER,
64 P_PERIOD IN VARCHAR2)
65 RETURN BOOLEAN IS
66 l_period_type VARCHAR2(240);
67 BEGIN
68 if (P_PERIOD is NULL) then
69 return FALSE;
70 else
71 select distinct period_type
72 into l_period_type
73 from gl_periods
74 where period_set_name in (select period_set_name
75 from gl_sets_of_books gl,
76 org_organization_definitions org
77 where org.set_of_books_id = gl.set_of_books_id
78 and org.organization_id = p_org_id)
79 and period_type = P_PERIOD;
80 end if;
81
82 return TRUE;
83
84 EXCEPTION
85 WHEN NO_DATA_FOUND then
86 htp.p('no period type');
87 return FALSE;
88
89 END Validate_Per;
90
91 FUNCTION Validate_Fcst(p_org_id IN NUMBER,
92 P_FROM_FORECAST IN VARCHAR2,
93 P_TO_FORECAST IN VARCHAR2)
94 RETURN BOOLEAN IS
95 l_from_forecast VARCHAR2(30);
96 l_to_forecast VARCHAR2(30);
97 BEGIN
98 if (P_FROM_FORECAST is NULL) OR (P_TO_FORECAST IS NULL) then
99 return FALSE;
100 else
101 select forecast_designator
102 into l_from_forecast
103 from mrp_forecast_designators
104 where organization_id = p_org_id
105 and forecast_set is null
106 and forecast_designator = P_FROM_FORECAST;
107
108 select forecast_designator
109 into l_to_forecast
110 from mrp_forecast_designators
111 where organization_id = p_org_id
112 and forecast_set is null
113 and forecast_designator >= P_FROM_FORECAST
114 and forecast_designator = P_TO_FORECAST;
115 end if;
116
117 return TRUE;
118
119 EXCEPTION
120 WHEN NO_DATA_FOUND then
121 htp.p('no forecast');
122 return FALSE;
123
124 END Validate_Fcst;
125
126 FUNCTION Validate_Parameters(
127 P_ORG_ID IN OUT NUMBER,
128 P_ORG_NAME VARCHAR2 default null,
129 P_FROM_FORECAST IN VARCHAR2 default null,
130 P_TO_FORECAST IN VARCHAR2 default null,
131 P_PERIOD IN VARCHAR2) RETURN BOOLEAN IS
132 l_org_id NUMBER := P_ORG_ID;
133 l_status BOOLEAN;
134 BEGIN
135 if Validate_Org(l_org_id, P_ORG_NAME) then
136 if Validate_Per(l_org_id, P_PERIOD) then
137 if Validate_Fcst(l_org_id, P_FROM_FORECAST, P_TO_FORECAST) then
138 l_status := TRUE;
139 end if;
140 end if;
141 end if;
142
143 P_ORG_ID := l_org_id;
144
145 return l_status;
146 END Validate_Parameters;
147
148 */
149
150 PROCEDURE Before_Parameter_MRPFSETA IS
151 l_user_id NUMBER;
152 l_resp_id NUMBER;
153 l_appl_id NUMBER;
154 l_org_id NUMBER;
155 BEGIN
156 -- Initialize the report
157 -- FND_GLOBAL.apps_initialize(l_user_id, l_resp_id, l_appl_id);
158 l_org_id := fnd_profile.value('ORG_ID');
159 FND_CLIENT_INFO.set_org_context(l_org_id);
160 END Before_Parameter_MRPFSETA;
161
162 PROCEDURE After_Parameter_MRPFSETA IS
163 BEGIN
164 NULL;
165 END After_Parameter_MRPFSETA;
166
167
168 PROCEDURE MRPFSETA_Parameter_PrintOrg(
169 param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type, --2663505
170 i IN NUMBER) IS
171 CURSOR c_organizations IS
172 SELECT organization_id org_id, organization_name name
173 FROM org_organization_definitions
174 ORDER BY organization_name;
175 BEGIN
176 -- htp.formHidden('p_org_id');
177 param(i).Label := FND_MESSAGE.get_string( 'MRP', 'MRP_ORGANIZATION_LABEL');
178 param(i).Value := htf.formSelectOpen( 'P_ORG' );
179 -- param(i).Value := htf.formSelectOpen( 'P_ORG', cattributes=>'onChange="setPoplists()"' );
180 FOR c1 in c_organizations LOOP
181 param(i).Value := param(i).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.org_id );
182 END LOOP;
183 param(i).Value := param(i).Value || htf.formSelectClose;
184
185 END MRPFSETA_Parameter_PrintOrg;
186
187 PROCEDURE MRPFSETA_Parameter_PrintTrgt(
188 param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type, --2663505
189 i IN NUMBER) IS
190 CURSOR c_business_plans IS
191 SELECT plan_id, name FROM bisbv_business_plans;
192 BEGIN
193 param(i).Label := FND_MESSAGE.get_string( 'BOM', 'CST_BUSINESS_PLAN_LABEL');
194 param(i).Value := htf.formSelectOpen( 'P_TARGET' );
195 FOR c1 in c_business_plans LOOP
196 param(i).Value := param(i).Value ||
197 htf.formSelectOption( c1.name, NULL, 'value=' || c1.plan_id );
198 END LOOP;
199 param(i).Value := param(i).Value || htf.formSelectClose;
200
201 END MRPFSETA_Parameter_PrintTrgt;
202
203 PROCEDURE MRPFSETA_Parameter_PrintPer(
204 param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type, --2663505
205 i IN NUMBER) IS
206 CURSOR c_period IS SELECT distinct period_type name, period_type id
207 FROM gl_periods;
208 BEGIN
209 param(i).Label := FND_MESSAGE.get_string( 'MRP', 'MRP_PERIOD_LABEL');
210 param(i).Value := htf.formSelectOpen( 'P_PERIOD' );
211 FOR c1 in c_period LOOP
212 param(i).Value := param(i).Value ||
213 htf.formSelectOption( c1.name, NULL, 'value=' || c1.id );
214 END LOOP;
215 param(i).Value := param(i).Value || htf.formSelectClose;
216
217 END MRPFSETA_Parameter_PrintPer;
218
219 PROCEDURE MRPFSETA_Parameter_PrintFcst(
220 param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type, --2663505
221 i IN NUMBER) IS
222 --v_org_id IN PLS_INTEGER) IS
223 CURSOR c_forecast IS
224 SELECT forecast_designator name, forecast_designator id
225 FROM mrp_forecast_designators
226 WHERE forecast_set IS NULL
227 AND organization_id = 207
228 ORDER BY forecast_designator;
229 BEGIN
230 param(i).Label := FND_MESSAGE.get_string( 'MRP', 'MRP_FORECAST_SET_LABEL');
231 param(i).Value := htf.formSelectOpen( 'P_FROM_FORECAST' );
232 FOR c1 in c_forecast LOOP
233 param(i).Value := param(i).Value ||
234 htf.formSelectOption( c1.name, NULL, 'value=' || c1.id );
235 END LOOP;
236 param(i).Value := param(i).Value || htf.formSelectClose;
237
238 param(i).Value := param(i).Value || ' - ' ||
239 htf.formSelectOpen( 'P_TO_FORECAST' );
240 FOR c1 in c_forecast LOOP
241 param(i).Value := param(i).Value ||
242 htf.formSelectOption( c1.name, NULL, 'value=' || c1.id );
243 END LOOP;
244 param(i).Value := param(i).Value || htf.formSelectClose;
245
246 END MRPFSETA_Parameter_PrintFcst;
247
248 /*
249 * LaunchReport
250 * Launches the report using parameters in
251 * the ICX session attibutes.
252 * Returns TRUE if all necessary parameters are present,
253 * and the report is launched. Otherwise returns FALSE.
254 */
255
256 function LaunchReport(
257 l_session_id in number,
258 L_BUSINESS_PLAN_ID in varchar2,
259 L_ORGANIZATION_ID in varchar2,
260 L_PLAN1 in varchar2,
261 L_PLAN2 in varchar2,
262 L_PERIOD_TYPE in varchar2
263 ) return boolean is
264 begin
265
266 if( L_BUSINESS_PLAN_ID is null or
267 L_ORGANIZATION_ID is null or
268 L_PLAN1 is null or
269 L_PLAN2 is null or
270 L_PERIOD_TYPE is null ) then
271 return false;
272 end if;
273
274 /*
275 * Commenting out for bug 6687733
276 OracleOASIS.RunReport(
277 report => 'MRPFSETA',
278 parameters =>
279 replace(
280 'paramform=NO*' ||
281 'P_BIS_PLAN=' || L_BUSINESS_PLAN_ID || '*' ||
282 'P_ORGANIZATION_ID=' || L_ORGANIZATION_ID || '*' ||
283 'PF_1=' || L_PLAN1 || '*' ||
284 'PF_2=' || L_PLAN2 || '*' ||
285 'P_PERIOD_TYPE=' || L_PERIOD_TYPE || '*',
286 ' ', '%20' ),
287 paramform=> 'NO');
288 */
289
290 return true;
291
292 end LaunchReport;
293
294
295
296
297 /*
298 * Parameter_FormView_MRPEPPS
299 *
300 * This function is invoked via a form function
301 * and is the entry point into this package.
302 * It creates the HTML parameter page used by
303 * the BIS Sales Revenue report.
304 */
305 PROCEDURE Parameter_FormView_MRPFSETA (force_display in varchar2 default null ) IS
306
307 params BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type;
308
309 CURSOR c_business_plans IS
310 select plan_id, name from bisbv_business_plans;
311
312 CURSOR c_period_types IS
313 SELECT distinct period_type name from gl_periods;
314
315 CURSOR c_forecast IS
316 SELECT distinct forecast_designator name from mrp_forecast_designators
317 where forecast_set IS null
318 AND organization_id = 207
319 ORDER BY forecast_designator;
320
321 CURSOR c_orgs IS
322 SELECT organization_name name, organization_id from org_organization_definitions
323 ORDER BY organization_name;
324
325 l_return_status VARCHAR2(1000);
326 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
327
328 l_launch_success boolean;
329
330
331
332 l_session_id number;
333
334 L_BUSINESS_PLAN_ID varchar2(80);
335 L_ORGANIZATION_ID varchar2(80);
336 L_PLAN1 varchar2(80);
337 L_PLAN2 varchar2(80);
338 L_PERIOD_TYPE varchar2(80);
339
340
341 BEGIN
342
343
344 if not icx_sec.validateSession THEN
345 return;
346 end if;
347
348 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
349
350 L_BUSINESS_PLAN_ID := icx_sec.getSessionAttributeValue( 'BUSINESS PLAN', l_session_id );
351 L_ORGANIZATION_ID := icx_sec.getSessionAttributeValue( 'ORGANIZATION_ID', l_session_id );
352 L_PLAN1 := icx_sec.getSessionAttributeValue( 'PLAN1', l_session_id );
353 L_PLAN2 := icx_sec.getSessionAttributeValue( 'PLAN2', l_session_id );
354 L_PERIOD_TYPE := icx_sec.getSessionAttributeValue( 'PERIOD TYPE', l_session_id );
355
356
357 /* launch the report if we have the necessary parameters */
358
359 if( force_display is null or
360 upper(force_display) = 'NO' or
361 upper(force_display) = 'N' ) then
362 l_launch_success := LaunchReport
363 (
364 l_session_id ,
365 L_BUSINESS_PLAN_ID ,
366 L_ORGANIZATION_ID ,
367 L_PLAN1 ,
368 L_PLAN2 ,
369 L_PERIOD_TYPE
370 );
371 if( l_launch_success ) then
372 return;
373 end if;
374 end if;
375
376 Before_Parameter_MRPFSETA;
377
378
379 htp.htmlOpen;
380
381 BIS_UTILITIES_PUB.Build_Report_Title( 'BIS_MRPFSETA', 'MRPFSETA', '' );
382
383 htp.headOpen;
384 js.scriptOpen;
385 icx_util.LOVScript;
386
387 js.scriptClose;
388 htp.headClose;
389
390 htp.bodyOpen;
391
392 htp.centerOpen;
393
394
395 /* ORGANIZATION */
396
397 params(1).Label := fnd_message.get_string( 'MRP', 'MRP_ORGANIZATION_LABEL' );
398 params(1).Value := htf.formSelectOpen( 'P_ORGANIZATION_ID' );
399 FOR c1 in c_orgs LOOP
400 if( c1.organization_id = L_ORGANIZATION_ID ) then
401 params(1).Value := params(1).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.organization_id );
402 else
403 params(1).Value := params(1).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.organization_id );
404 end if;
405 END LOOP;
406 params(1).Value := params(1).Value || htf.formSelectClose;
407
408
409 /* BUSINESS PLAN */
410
411 params(2).Label := fnd_message.get_string( 'BOM', 'CST_BUSINESS_PLAN_LABEL' );
412 params(2).Value := htf.formSelectOpen( 'P_BUSINESS_PLAN' );
413 FOR c1 in c_business_plans LOOP
414 if( c1.plan_id = L_BUSINESS_PLAN_ID ) then
415 params(2).Value := params(2).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.plan_id );
416 else
417 params(2).Value := params(2).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.plan_id );
418 end if;
419 END LOOP;
420 params(2).Value := params(2).Value || htf.formSelectClose;
421
422 /* MRP PLANS */
423
424 params(3).Label := fnd_message.get_string( 'MRP', 'MRP_FORECAST_SET_LABEL' );
425 params(3).Value := htf.formSelectOpen('P_PLAN1');
426 FOR c1 in c_forecast LOOP
427 params(3).Value := params(3).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.name );
428 END LOOP;
429 params(3).Value := params(3).Value || htf.formSelectClose;
430 params(3).Value := params(3).Value || ' - ' || htf.formSelectOpen( 'P_PLAN2' );
431 FOR c1 in c_forecast LOOP
432 params(3).Value := params(3).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.name );
433 END LOOP;
434 params(3).Value := params(3).Value || htf.formSelectClose;
435
436
437 /* PERIOD TYPE */
438
439 params(4).Label := fnd_message.get_string( 'MRP', 'MRP_PERIOD_TYPE_LABEL' );
440 params(4).Value := htf.formSelectOpen( 'P_PERIOD_TYPE' );
441 FOR c1 in c_period_types LOOP
442 params(4).Value := params(4).Value ||
443 htf.formSelectOption( c1.name, NULL, 'value=' || c1.name);
444 END LOOP;
445 params(4).Value := params(4).Value || htf.formSelectClose;
446
447 BIS_UTILITIES_PUB.Build_Parameter_Form(
448 'NAME="param" ACTION="MRP_PARAMETER_MRPFSETA.Parameter_ActionView_MRPFSETA" METHOD="GET" ', params );
449
450
451 END Parameter_FormView_MRPFSETA;
452
453
454 /*
455 * Parameter_ActionView_MRPEPPS
456 *
457 * This function is invoked when the user clicks
458 * the OK button in the HTML page generated by
459 * Parameter_FormView_MRPEPPS. It will validate
460 * the input parameters and launch the Sales Revenue
461 * report.
462 */
463 PROCEDURE Parameter_ActionView_MRPFSETA(
464 P_BUSINESS_PLAN NUMBER,
465 P_ORGANIZATION_ID NUMBER,
466 P_PLAN1 VARCHAR2,
467 P_PLAN2 VARCHAR2,
468 P_PERIOD_TYPE VARCHAR2)
469 IS
470
471 l_session_id number;
472
473 L_BUSINESS_PLAN_ID varchar2(80);
474 L_ORGANIZATION_ID varchar2(80);
475 L_PLAN1 varchar2(80);
476 L_PLAN2 varchar2(80);
477 L_PERIOD_TYPE varchar2(80);
478
479 l_launch_success boolean;
480
481 BEGIN
482
483 if not icx_sec.validateSession THEN
484 return;
485 end if;
486
487 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
488
489 L_BUSINESS_PLAN_ID := p_business_plan;
490 L_PERIOD_TYPE := p_period_type;
491 L_ORGANIZATION_ID := p_organization_id;
492 L_PLAN1 := p_plan1;
493 L_PLAN2 := p_plan2;
494
495 icx_sec.putSessionAttributeValue( 'BUSINESS PLAN', L_BUSINESS_PLAN_ID , l_session_id );
496 icx_sec.putSessionAttributeValue( 'ORGANIZATION', L_ORGANIZATION_ID , l_session_id );
497 icx_sec.putSessionAttributeValue( 'PLAN1', L_PLAN1 , l_session_id );
498 icx_sec.putSessionAttributeValue( 'PLAN2', L_PLAN2 , l_session_id );
499 icx_sec.putSessionAttributeValue( 'PERIOD TYPE', L_PERIOD_TYPE , l_session_id );
500
501
502 l_launch_success := LaunchReport
503 (
504 l_session_id ,
505 L_BUSINESS_PLAN_ID ,
506 L_ORGANIZATION_ID ,
507 L_PLAN1 ,
508 L_PLAN2 ,
509 L_PERIOD_TYPE
510 );
511
512
513 END Parameter_ActionView_MRPFSETA;
514
515
516 END MRP_PARAMETER_MRPFSETA;