[Home] [Help]
PACKAGE BODY: APPS.MRP_PARAMETER_MRPFSETA
Source
1 PACKAGE BODY MRP_PARAMETER_MRPFSETA as
2 /* $Header: MRPBISFB.pls 120.3 2011/08/08 20:33:32 schaudha ship $ */
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 /*
178 param(i).Label := FND_MESSAGE.get_string( 'MRP', 'MRP_ORGANIZATION_LABEL');
179 param(i).Value := htf.formSelectOpen( 'P_ORG' );
180 -- param(i).Value := htf.formSelectOpen( 'P_ORG', cattributes=>'onChange="setPoplists()"' );
181 FOR c1 in c_organizations LOOP
182 param(i).Value := param(i).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.org_id );
183 END LOOP;
184 param(i).Value := param(i).Value || htf.formSelectClose;
185 */
186 NULL;
187 END MRPFSETA_Parameter_PrintOrg;
188
189 PROCEDURE MRPFSETA_Parameter_PrintTrgt(
190 -- param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type, --2663505
191 i IN NUMBER) IS
192 /*
193 CURSOR c_business_plans IS
194 SELECT plan_id, name FROM bisbv_business_plans;
195 */
196 BEGIN
197 /*
198 param(i).Label := FND_MESSAGE.get_string( 'BOM', 'CST_BUSINESS_PLAN_LABEL');
199 param(i).Value := htf.formSelectOpen( 'P_TARGET' );
200 FOR c1 in c_business_plans LOOP
201 param(i).Value := param(i).Value ||
202 htf.formSelectOption( c1.name, NULL, 'value=' || c1.plan_id );
203 END LOOP;
204 param(i).Value := param(i).Value || htf.formSelectClose;
205 */
206 NULL;
207 END MRPFSETA_Parameter_PrintTrgt;
208
209 PROCEDURE MRPFSETA_Parameter_PrintPer(
210 -- param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type, --2663505
211 i IN NUMBER) IS
212 CURSOR c_period IS SELECT distinct period_type name, period_type id
213 FROM gl_periods;
214 BEGIN
215 /*
216 param(i).Label := FND_MESSAGE.get_string( 'MRP', 'MRP_PERIOD_LABEL');
217 param(i).Value := htf.formSelectOpen( 'P_PERIOD' );
218 FOR c1 in c_period LOOP
219 param(i).Value := param(i).Value ||
220 htf.formSelectOption( c1.name, NULL, 'value=' || c1.id );
221 END LOOP;
222 param(i).Value := param(i).Value || htf.formSelectClose;
223 */
224 NULL;
225 END MRPFSETA_Parameter_PrintPer;
226
227 PROCEDURE MRPFSETA_Parameter_PrintFcst(
228 -- param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type, --2663505
229 i IN NUMBER) IS
230 --v_org_id IN PLS_INTEGER) IS
231 CURSOR c_forecast IS
232 SELECT forecast_designator name, forecast_designator id
233 FROM mrp_forecast_designators
234 WHERE forecast_set IS NULL
235 AND organization_id = 207
236 ORDER BY forecast_designator;
237 BEGIN
238 /*
239 param(i).Label := FND_MESSAGE.get_string( 'MRP', 'MRP_FORECAST_SET_LABEL');
240 param(i).Value := htf.formSelectOpen( 'P_FROM_FORECAST' );
241 FOR c1 in c_forecast LOOP
242 param(i).Value := param(i).Value ||
243 htf.formSelectOption( c1.name, NULL, 'value=' || c1.id );
244 END LOOP;
245 param(i).Value := param(i).Value || htf.formSelectClose;
246
247 param(i).Value := param(i).Value || ' - ' ||
248 htf.formSelectOpen( 'P_TO_FORECAST' );
249 FOR c1 in c_forecast LOOP
250 param(i).Value := param(i).Value ||
251 htf.formSelectOption( c1.name, NULL, 'value=' || c1.id );
252 END LOOP;
253 param(i).Value := param(i).Value || htf.formSelectClose;
254 */
255 NULL;
256 END MRPFSETA_Parameter_PrintFcst;
257
258 /*
259 * LaunchReport
260 * Launches the report using parameters in
261 * the ICX session attibutes.
262 * Returns TRUE if all necessary parameters are present,
263 * and the report is launched. Otherwise returns FALSE.
264 */
265
266 function LaunchReport(
267 l_session_id in number,
268 L_BUSINESS_PLAN_ID in varchar2,
269 L_ORGANIZATION_ID in varchar2,
270 L_PLAN1 in varchar2,
271 L_PLAN2 in varchar2,
272 L_PERIOD_TYPE in varchar2
273 ) return boolean is
274 begin
275
276 if( L_BUSINESS_PLAN_ID is null or
277 L_ORGANIZATION_ID is null or
278 L_PLAN1 is null or
279 L_PLAN2 is null or
280 L_PERIOD_TYPE is null ) then
281 return false;
282 end if;
283
284 /*
285 * Commenting out for bug 6687733
286 OracleOASIS.RunReport(
287 report => 'MRPFSETA',
288 parameters =>
289 replace(
290 'paramform=NO*' ||
291 'P_BIS_PLAN=' || L_BUSINESS_PLAN_ID || '*' ||
292 'P_ORGANIZATION_ID=' || L_ORGANIZATION_ID || '*' ||
293 'PF_1=' || L_PLAN1 || '*' ||
294 'PF_2=' || L_PLAN2 || '*' ||
295 'P_PERIOD_TYPE=' || L_PERIOD_TYPE || '*',
296 ' ', '%20' ),
297 paramform=> 'NO');
298 */
299
300 return true;
301
302 end LaunchReport;
303
304
305
306
307 /*
308 * Parameter_FormView_MRPEPPS
309 *
310 * This function is invoked via a form function
311 * and is the entry point into this package.
312 * It creates the HTML parameter page used by
313 * the BIS Sales Revenue report.
314 */
315 PROCEDURE Parameter_FormView_MRPFSETA (force_display in varchar2 default null ) IS
316
317 -- params BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type;
318
319 /*
320 CURSOR c_business_plans IS
321 select plan_id, name from bisbv_business_plans;
322 */
323 CURSOR c_period_types IS
324 SELECT distinct period_type name from gl_periods;
325
326 CURSOR c_forecast IS
327 SELECT distinct forecast_designator name from mrp_forecast_designators
328 where forecast_set IS null
329 AND organization_id = 207
330 ORDER BY forecast_designator;
331
332 CURSOR c_orgs IS
333 SELECT organization_name name, organization_id from org_organization_definitions
334 ORDER BY organization_name;
335
336 l_return_status VARCHAR2(1000);
337 -- l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
338
339 l_launch_success boolean;
340
341
342
343 l_session_id number;
344
345 L_BUSINESS_PLAN_ID varchar2(80);
346 L_ORGANIZATION_ID varchar2(80);
347 L_PLAN1 varchar2(80);
348 L_PLAN2 varchar2(80);
349 L_PERIOD_TYPE varchar2(80);
350
351
352 BEGIN
353
354
355 if not icx_sec.validateSession THEN
356 return;
357 end if;
358
359 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
360
361 L_BUSINESS_PLAN_ID := icx_sec.getSessionAttributeValue( 'BUSINESS PLAN', l_session_id );
362 L_ORGANIZATION_ID := icx_sec.getSessionAttributeValue( 'ORGANIZATION_ID', l_session_id );
363 L_PLAN1 := icx_sec.getSessionAttributeValue( 'PLAN1', l_session_id );
364 L_PLAN2 := icx_sec.getSessionAttributeValue( 'PLAN2', l_session_id );
365 L_PERIOD_TYPE := icx_sec.getSessionAttributeValue( 'PERIOD TYPE', l_session_id );
366
367
368 /* launch the report if we have the necessary parameters */
369
370 if( force_display is null or
371 upper(force_display) = 'NO' or
372 upper(force_display) = 'N' ) then
373 l_launch_success := LaunchReport
374 (
375 l_session_id ,
376 L_BUSINESS_PLAN_ID ,
377 L_ORGANIZATION_ID ,
378 L_PLAN1 ,
379 L_PLAN2 ,
380 L_PERIOD_TYPE
381 );
382 if( l_launch_success ) then
383 return;
384 end if;
385 end if;
386
387 Before_Parameter_MRPFSETA;
388
389
390 htp.htmlOpen;
391
392 -- BIS_UTILITIES_PUB.Build_Report_Title( 'BIS_MRPFSETA', 'MRPFSETA', '' );
393
394 htp.headOpen;
395 js.scriptOpen;
396 icx_util.LOVScript;
397
398 js.scriptClose;
399 htp.headClose;
400
401 htp.bodyOpen;
402
403 htp.centerOpen;
404
405
406 /* ORGANIZATION */
407 /*
408 params(1).Label := fnd_message.get_string( 'MRP', 'MRP_ORGANIZATION_LABEL' );
409 params(1).Value := htf.formSelectOpen( 'P_ORGANIZATION_ID' );
410 FOR c1 in c_orgs LOOP
411 if( c1.organization_id = L_ORGANIZATION_ID ) then
412 params(1).Value := params(1).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.organization_id );
413 else
414 params(1).Value := params(1).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.organization_id );
415 end if;
416 END LOOP;
417 params(1).Value := params(1).Value || htf.formSelectClose;
418 */
419
420 /* BUSINESS PLAN */
421 /*
422 params(2).Label := fnd_message.get_string( 'BOM', 'CST_BUSINESS_PLAN_LABEL' );
423 params(2).Value := htf.formSelectOpen( 'P_BUSINESS_PLAN' );
424 FOR c1 in c_business_plans LOOP
425 if( c1.plan_id = L_BUSINESS_PLAN_ID ) then
426 params(2).Value := params(2).Value || htf.formSelectOption( c1.name, 'Y', 'value=' || c1.plan_id );
427 else
428 params(2).Value := params(2).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.plan_id );
429 end if;
430 END LOOP;
431 params(2).Value := params(2).Value || htf.formSelectClose;
432 */
433 /* MRP PLANS */
434 /*
435 params(3).Label := fnd_message.get_string( 'MRP', 'MRP_FORECAST_SET_LABEL' );
436 params(3).Value := htf.formSelectOpen('P_PLAN1');
437 FOR c1 in c_forecast LOOP
438 params(3).Value := params(3).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.name );
439 END LOOP;
440 params(3).Value := params(3).Value || htf.formSelectClose;
441 params(3).Value := params(3).Value || ' - ' || htf.formSelectOpen( 'P_PLAN2' );
442 FOR c1 in c_forecast LOOP
443 params(3).Value := params(3).Value || htf.formSelectOption( c1.name, NULL, 'value=' || c1.name );
444 END LOOP;
445 params(3).Value := params(3).Value || htf.formSelectClose;
446 */
447
448 /* PERIOD TYPE */
449
450 /*
451 params(4).Label := fnd_message.get_string( 'MRP', 'MRP_PERIOD_TYPE_LABEL' );
452 params(4).Value := htf.formSelectOpen( 'P_PERIOD_TYPE' );
453 FOR c1 in c_period_types LOOP
454 params(4).Value := params(4).Value ||
455 htf.formSelectOption( c1.name, NULL, 'value=' || c1.name);
456 END LOOP;
457 params(4).Value := params(4).Value || htf.formSelectClose;
458
459 BIS_UTILITIES_PUB.Build_Parameter_Form(
460 'NAME="param" ACTION="MRP_PARAMETER_MRPFSETA.Parameter_ActionView_MRPFSETA" METHOD="GET" ', params );
461 */
462
463 END Parameter_FormView_MRPFSETA;
464
465
466 /*
467 * Parameter_ActionView_MRPEPPS
468 *
469 * This function is invoked when the user clicks
470 * the OK button in the HTML page generated by
471 * Parameter_FormView_MRPEPPS. It will validate
472 * the input parameters and launch the Sales Revenue
473 * report.
474 */
475 PROCEDURE Parameter_ActionView_MRPFSETA(
476 P_BUSINESS_PLAN NUMBER,
477 P_ORGANIZATION_ID NUMBER,
478 P_PLAN1 VARCHAR2,
479 P_PLAN2 VARCHAR2,
480 P_PERIOD_TYPE VARCHAR2)
481 IS
482
483 l_session_id number;
484
485 L_BUSINESS_PLAN_ID varchar2(80);
486 L_ORGANIZATION_ID varchar2(80);
487 L_PLAN1 varchar2(80);
488 L_PLAN2 varchar2(80);
489 L_PERIOD_TYPE varchar2(80);
490
491 l_launch_success boolean;
492
493 BEGIN
494
495 if not icx_sec.validateSession THEN
496 return;
497 end if;
498
499 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
500
501 L_BUSINESS_PLAN_ID := p_business_plan;
502 L_PERIOD_TYPE := p_period_type;
503 L_ORGANIZATION_ID := p_organization_id;
504 L_PLAN1 := p_plan1;
505 L_PLAN2 := p_plan2;
506
507 icx_sec.putSessionAttributeValue( 'BUSINESS PLAN', L_BUSINESS_PLAN_ID , l_session_id );
508 icx_sec.putSessionAttributeValue( 'ORGANIZATION', L_ORGANIZATION_ID , l_session_id );
509 icx_sec.putSessionAttributeValue( 'PLAN1', L_PLAN1 , l_session_id );
510 icx_sec.putSessionAttributeValue( 'PLAN2', L_PLAN2 , l_session_id );
511 icx_sec.putSessionAttributeValue( 'PERIOD TYPE', L_PERIOD_TYPE , l_session_id );
512
513
514 l_launch_success := LaunchReport
515 (
516 l_session_id ,
517 L_BUSINESS_PLAN_ID ,
518 L_ORGANIZATION_ID ,
519 L_PLAN1 ,
520 L_PLAN2 ,
521 L_PERIOD_TYPE
522 );
523
524
525 END Parameter_ActionView_MRPFSETA;
526
527
528 END MRP_PARAMETER_MRPFSETA;