1 PACKAGE BODY ZPB_ACVAL_PVT AS
2 /* $Header: ZPBACVLB.pls 120.19 2007/12/04 14:32:40 mbhat ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(15) := 'zpb_acval_pvt';
6
7 -------------------------------------------------------------------------------
8
9 PROCEDURE validate_currentrun_helper (
10 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
11 x_changeCurrentRun OUT NOCOPY VARCHAR2)
12
13 IS
14
15 l_api_name CONSTANT VARCHAR2(30) := 'validate_currentrun_helper';
16 l_api_version CONSTANT NUMBER := 1.0;
17
18 TYPE lineMemberTyp IS REF CURSOR;
19 pub_line_member lineMemberTyp;
20 edt_line_member lineMemberTyp;
21 l_pub_ac_id zpb_analysis_cycles.analysis_cycle_id%type;
22 l_edt_ac_id zpb_analysis_cycles.analysis_cycle_id%type;
23 l_edt_query_path zpb_cycle_model_dimensions.query_object_path%type;
24 l_pub_query_path zpb_cycle_model_dimensions.query_object_path%type;
25 l_edt_status_sql zpb_status_sql.status_sql%type;
26 l_pub_status_sql zpb_status_sql.status_sql%type;
27 l_sql zpb_status_sql.status_sql%type;
28 l_cycle_dim zpb_cycle_model_dimensions.dimension_name%type;
29 l_line_dim zpb_cycle_model_dimensions.dimension_name%type;
30 l_dataset_dim zpb_cycle_model_dimensions.dataset_dimension_flag%type;
31 l_removed_dim zpb_cycle_model_dimensions.remove_dimension_flag%type;
32 l_pub_member varchar2(100);
33 l_edt_member varchar2(100);
34 l_lines_compare integer;
35 l_edt_inp_sel_path varchar(200);
36 l_pub_inp_sel_path varchar(200);
37 l_edt_out_sel_path varchar(200);
38 l_pub_out_sel_path varchar(200);
39 count number;
40
41
42 l_query VARCHAR2(8000);
43 l_user_id VARCHAR2(64);
44 l_task_id VARCHAR2(64);
45 l_count NUMBER;
46 l_excp_ct VARCHAR2(32766);
47 dummy_var varchar2(2);
48 i integer;
49 l_edt_currency VARCHAR2(30);
50 l_pub_currency VARCHAR2(30);
51 l_sel_dim varchar2(30);
52 l_member varchar2(50);
53 l_ret_val varchar2(8);
54
55 cursor published_ac is
56 select published_ac_id
57 from zpb_cycle_relationships
58 where editable_ac_id = p_analysis_cycle_id;
59
60 cursor published_currency is
61 select params.value
62 from zpb_ac_param_values params, fnd_lookup_values_vl fnd
63 where params.param_id = fnd.TAG and fnd.LOOKUP_CODE = 'BUSINESS_PROCESS_CURRENCY'
64 and fnd.LOOKUP_TYPE = 'ZPB_PARAMS' and params.analysis_cycle_id = l_pub_ac_id ;
65
66 cursor editable_currency is
67 select params.value
68 from zpb_ac_param_values params, fnd_lookup_values_vl fnd
69 where params.param_id = fnd.TAG and fnd.LOOKUP_CODE = 'BUSINESS_PROCESS_CURRENCY'
70 and fnd.LOOKUP_TYPE = 'ZPB_PARAMS' and params.analysis_cycle_id = l_edt_ac_id ;
71
72
73 cursor cycle_params(l_param_id IN INTEGER) is
74 select '1'
75 from zpb_ac_param_values pub, zpb_ac_param_values edt
76 where edt.analysis_cycle_id = l_edt_ac_id
77 and pub.analysis_cycle_id = l_pub_ac_id
78 and pub.param_id = l_param_id
79 and edt.param_id = l_param_id
80 and pub.value <> edt.value;
81
82
83 cursor data_set is
84 select '1'
85 from zpb_cycle_datasets pub
86 where pub.analysis_cycle_id = l_pub_ac_id
87 and pub.dataset_code not in (select edt.dataset_code
88 from zpb_cycle_datasets edt
89 where edt.analysis_cycle_id = l_edt_ac_id
90 and edt.order_id = pub.order_id
91 )
92 union
93 select '1'
94 from zpb_cycle_datasets edt
95 where edt.analysis_cycle_id = l_edt_ac_id
96 and edt.dataset_code not in (select pub.dataset_code
97 from zpb_cycle_datasets pub
98 where pub.analysis_cycle_id = l_pub_ac_id
99 and edt.order_id = pub.order_id
100 );
101
102
103 cursor model_dimensions_edt is
104 select dimension_name,dataset_dimension_flag,remove_dimension_flag
105 from zpb_cycle_model_dimensions
106 where analysis_cycle_id = p_analysis_cycle_id
107 minus
108 select dimension_name,dataset_dimension_flag,remove_dimension_flag
109 from zpb_cycle_model_dimensions
110 where analysis_cycle_id = l_pub_ac_id;
111
112 cursor model_dimensions_pub is
113 select dimension_name,dataset_dimension_flag,remove_dimension_flag
114 from zpb_cycle_model_dimensions
115 where analysis_cycle_id = l_pub_ac_id
116 minus
117 select dimension_name,dataset_dimension_flag,remove_dimension_flag
118 from zpb_cycle_model_dimensions
119 where analysis_cycle_id = p_analysis_cycle_id;
120
121 cursor query_identifier(l_ac_id in number) is
122 select query_object_path|| '/' || query_object_name
123 from zpb_cycle_model_dimensions
124 where dimension_name = l_line_dim
125 and analysis_cycle_id = l_ac_id;
126
127 cursor source_type is
128 select 1 from
129 zpb_solve_member_defs pub, zpb_solve_member_defs edt
130 where edt.analysis_cycle_id = p_analysis_cycle_id
131 and pub.analysis_cycle_id = l_pub_ac_id
132 and pub.member = edt.member
133 and edt.source_type <> pub.source_type;
134
135 cursor source_view_cur is
136 select 1 from
137 zpb_data_initialization_defs pub, zpb_data_initialization_defs edt
138 where edt.analysis_cycle_id = p_analysis_cycle_id
139 and pub.analysis_cycle_id = l_pub_ac_id
140 and pub.member = edt.member
141 and ( edt.source_view <> pub.source_view
142 or nvl(edt.lag_time_periods,-1) <> nvl(pub.lag_time_periods,-1)
143 or nvl(edt.lag_time_level,'-1') <> nvl(pub.lag_time_level,'-1')
144 or nvl(edt.change_number,-1) <> nvl(pub.change_number,-1)
145 or nvl(edt.percentage_flag, '-1') <> nvl(pub.percentage_flag, '-1')
146 );
147
148
149 -- and edt.data_source is not null
150 -- and pub.data_source is null;
151
152
153 cursor input_selections_edt is
154 select '1'
155 from (select member, dimension,selection_name,hierarchy
156 from zpb_solve_input_selections
157 where analysis_cycle_id = p_analysis_cycle_id
158 minus
159 select member, dimension,selection_name,hierarchy
160 from zpb_solve_input_selections
161 where analysis_cycle_id = l_pub_ac_id);
162
163 cursor input_selections_pub is
164 select '1'
165 from (select member, dimension,selection_name,hierarchy
166 from zpb_solve_input_selections
167 where analysis_cycle_id = l_pub_ac_id
168 minus
169 select member, dimension,selection_name,hierarchy
170 from zpb_solve_input_selections
171 where analysis_cycle_id = p_analysis_cycle_id);
172
173 cursor input_selection_identifier(l_ac_id in number) is
174 select member,dimension,selection_path|| '/' || selection_name
175 from zpb_solve_input_selections
176 where analysis_cycle_id = l_ac_id
177 and selection_name <> 'DEFAULT';
178
179 cursor input_selection_ident_pub(l_ac_id in number,l_dim_name in varchar2,l_member in varchar2) is
180 select selection_path|| '/' || selection_name
181 from zpb_solve_input_selections
182 where analysis_cycle_id = l_ac_id
183 and dimension = l_dim_name
184 and member = l_member;
185 cursor output_selections_edt is
186 select '1'
187 from (select member, dimension,selection_name,hierarchy,match_input_flag
188 from zpb_solve_output_selections
189 where analysis_cycle_id = p_analysis_cycle_id
190 minus
191 select member, dimension,selection_name, hierarchy,match_input_flag
192 from zpb_solve_output_selections
193 where analysis_cycle_id = l_pub_ac_id);
194
195 cursor output_selections_pub is
196 select '1'
197 from (select member, dimension,selection_name,hierarchy,match_input_flag
198 from zpb_solve_output_selections
199 where analysis_cycle_id = l_pub_ac_id
200 minus
201 select member, dimension,selection_name, hierarchy,match_input_flag
202 from zpb_solve_output_selections
203 where analysis_cycle_id = p_analysis_cycle_id);
204
205
206 cursor allocation_def_edt is
207 select '1'
208 from (select member,rule_name,method,basis,qualifier
209 from zpb_solve_allocation_defs
210 where analysis_cycle_id = p_analysis_cycle_id
211 minus
212 select member,rule_name,method,basis,qualifier
213 from zpb_solve_allocation_defs
214 where analysis_cycle_id = l_pub_ac_id);
215
216 cursor allocation_def_pub is
217 select '1'
218 from (select member,rule_name,method,basis,qualifier,evaluation_option
219 from zpb_solve_allocation_defs
220 where analysis_cycle_id = l_pub_ac_id
221 minus
222 select member,rule_name,method,basis,qualifier,evaluation_option
223 from zpb_solve_allocation_defs
224 where analysis_cycle_id = p_analysis_cycle_id);
225
226 cursor task_list is
227 select '1'
228 from zpb_analysis_cycle_tasks edt,
229 zpb_analysis_cycle_tasks pub
230 where edt.analysis_cycle_id = l_edt_ac_id
231 and pub.analysis_cycle_id = l_pub_ac_id
232 and edt.sequence = pub.sequence
233 and edt.task_name <> pub.task_name;
234
235 cursor task_list_pub is
236 select '1'
237 from zpb_analysis_cycle_tasks pub
238 where sequence not in (select sequence
239 from zpb_analysis_cycle_tasks edt
240 where edt.analysis_cycle_id = l_edt_ac_id)
241 and pub.analysis_cycle_id = l_pub_ac_id;
242
243 cursor status_sql(l_query_path in varchar2) is
244 select status_sql
245 from zpb_status_sql
246 where query_path = l_query_path
247 and dimension_name = l_line_dim
248 order by row_num;
249
250
251
252 BEGIN
253
254 x_changeCurrentRun := 'Y';
255 i := 4;
256
257 -- Standard Start of API savepoint
258 SAVEPOINT zpb_acval_pvt_validate;
259
260 -- API body
261 l_edt_ac_id := p_analysis_cycle_id;
262
263
264 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name, 'Running Validations for business process ' || to_char(l_edt_ac_id) || '.');
265
266 -- get the published_ac_id
267 open published_ac;
268 fetch published_ac into l_pub_ac_id;
269
270 -- if no published_ac_id then this cycle is being made effective for first time
271 if (published_ac%notfound) then
272 close published_ac;
273 x_changeCurrentRun := 'Y';
274 return;
275 end if;
276
277 -- Verify if the Currency has been modified in the edited version.
278
279 open published_currency;
280 fetch published_currency into l_pub_currency;
281 close published_currency;
282
283 open editable_currency;
284 fetch editable_currency into l_edt_currency;
285 close editable_currency;
286
287 if(l_pub_currency <> l_edt_currency) then
288 x_changeCurrentRun := 'N';
289 return;
290 end if;
291
292
293 -- verify that no changes were made to dataset settings
294 zpb_log.write_statement(G_PKG_NAME,'pub id is '||to_char(l_pub_ac_id));
295 open data_set;
296 fetch data_set into dummy_var;
297 if (data_set%found) then
298 close data_set;
299 x_changeCurrentRun := 'N';
300 return;
301 end if;
302 close data_set;
303
304 -- verify that no changes were made to horizon settings
305 zpb_log.write_statement(G_PKG_NAME,'data set succeeded');
306 for i in 4..17 loop
307 open cycle_params(i);
308 fetch cycle_params into dummy_var;
309 if cycle_params%found then
310 close cycle_params;
311 x_changeCurrentRun := 'N';
312 return;
313 end if;
314 close cycle_params;
315 end loop;
316
317 -- verify that no changes are made to the precompute percent field
318 open cycle_params(27);
319 fetch cycle_params into dummy_var;
320 if cycle_params%found then
321 close cycle_params;
322 x_changeCurrentRun := 'N';
323 return;
324 end if;
325 close cycle_params;
326
327
328 zpb_log.write_statement(G_PKG_NAME,'Horizon params succeeded');
329
330 -- verify that no changes are made to dimensions in composite
331 open cycle_params(52);
332 fetch cycle_params into dummy_var;
333 if cycle_params%found then
334 close cycle_params;
335 x_changeCurrentRun := 'N';
336 return;
337 end if;
338 close cycle_params;
339
340
341 -- verify that no changes were made to model dimensions settings
342 open model_dimensions_pub;
343 fetch model_dimensions_pub into l_cycle_dim,l_dataset_dim,l_removed_dim;
344 if (model_dimensions_pub%found) then
345 close model_dimensions_pub;
346 x_changeCurrentRun := 'N';
347 return;
348 end if;
349
350 zpb_log.write_statement(G_PKG_NAME,'Published cycle does not have more dims than temp');
351 open model_dimensions_edt;
352 fetch model_dimensions_edt into l_cycle_dim,l_dataset_dim,l_removed_dim;
353 if (model_dimensions_edt%found) then
354 close model_dimensions_edt;
355 x_changeCurrentRun := 'N';
356 return;
357 end if;
358
359 zpb_log.write_statement(G_PKG_NAME,'Tmp cycle does not have more dims than published');
360
361 -- verify that no changes were made to Solve source type to Input/Input and Init
362 open source_type;
363 fetch source_type into dummy_var;
364 if (source_type%found) then
365 close source_type;
366 x_changeCurrentRun := 'N';
367 return;
368 end if;
369 close source_type;
370 zpb_log.write_statement(G_PKG_NAME,'Tmp cycle does not have any line members that changed to input and initialized');
371
372 -- verify that no changes were made to Solve initialization source view
373 open source_view_cur;
374 fetch source_view_cur into dummy_var;
375 if (source_view_cur%found) then
376 close source_view_cur;
377 x_changeCurrentRun := 'N';
378 return;
379 end if;
380 close source_view_cur;
381 zpb_log.write_statement(G_PKG_NAME,'Tmp cycle does not have any input line members that have changed initialization source');
382
383 -- initialize solve strcutures for published id to enable comparison
384 initialize_solve_object(l_pub_ac_id);
385 if zpb_aw.interp('show vl.inseldiff(''' || l_pub_ac_id || ''',''' || l_edt_ac_id || ''')') > 0 then
389
386 x_changeCurrentRun :='N';
387 return;
388 end if;
390 -- verify that no changes were made to Solve Output Levels
391 open output_selections_edt;
392 fetch output_selections_edt into dummy_var;
393 if (output_selections_edt%found) then
394 close output_selections_edt;
395 x_changeCurrentRun := 'N';
396 return;
397 end if;
398 close output_selections_edt;
399 zpb_log.write_statement(G_PKG_NAME,'Temp cycle does not have any line members that have different output levels from published');
400
401 open output_selections_pub;
402 fetch output_selections_pub into dummy_var;
403 if (output_selections_pub%found) then
404 close output_selections_pub;
405 x_changeCurrentRun := 'N';
406 return;
407 end if;
408 close output_selections_pub;
409 zpb_log.write_statement(G_PKG_NAME,'Published cycle does not have any line members that have different output levels from edt');
410
411 -- verify that no changes were made to Solve allocation definitions
412 open allocation_def_edt;
413 fetch allocation_def_edt into dummy_var;
414 if (allocation_def_edt%found) then
415 close allocation_def_edt;
416 x_changeCurrentRun := 'N';
417 return;
418 end if;
419 close allocation_def_edt;
420 zpb_log.write_statement(G_PKG_NAME,'Temp cycle does not have any line members that have different allocation definition from published');
421
422 open allocation_def_pub;
423 fetch allocation_def_pub into dummy_var;
424 if (allocation_def_pub%found) then
425 close allocation_def_pub;
426 x_changeCurrentRun := 'N';
427 return;
428 end if;
429 close allocation_def_pub;
430 zpb_log.write_statement(G_PKG_NAME,'Published cycle does not have any line members that have different allocation definition from edt');
431
432 -- verify that no changes were made to order of existing tasks
433 open task_list;
434 fetch task_list into dummy_var;
435 if (task_list%found) then
436 close task_list;
437 x_changeCurrentRun := 'N';
438 return;
439 end if;
440 close task_list;
441 zpb_log.write_statement(G_PKG_NAME,'Published cycle does not have any tasks that are different in edt');
442
443
444 open task_list_pub;
445 fetch task_list_pub into dummy_var;
446 if (task_list_pub%found) then
447 close task_list_pub;
448 x_changeCurrentRun := 'N';
449 return;
450 end if;
451 close task_list_pub;
452 zpb_log.write_statement(G_PKG_NAME,'Published cycle does not have any tasks that do not exist in edt');
453
454 open query_identifier(l_edt_ac_id);
455 fetch query_identifier into l_edt_query_path;
456 close query_identifier;
457
458 open query_identifier(l_pub_ac_id);
459 fetch query_identifier into l_pub_query_path;
460 close query_identifier;
461 zpb_log.write_statement(G_PKG_NAME,'Published path:'||l_pub_query_path);
462 zpb_log.write_statement(G_PKG_NAME,'Temporary path:'||l_edt_query_path);
463
464 compare_line_members(l_pub_query_path, l_edt_query_path,l_lines_compare);
465 if l_lines_compare <> 0 then
466 x_changeCurrentRun := 'N';
467 return;
468 end if;
469
470 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name, ' Validation completed for Analysis Cycle' || l_edt_ac_id || '.');
471
472 /*
473 EXCEPTION
474 WHEN FND_API.G_EXC_ERROR THEN
475 ROLLBACK TO zpb_acval_pvt_validate;
476 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
477 x_changeCurrentRun := 'Y';
478 return;
479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
480 ROLLBACK TO zpb_acval_pvt_validate;
481 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
482 x_changeCurrentRun := 'Y';
483 return;
484
485 WHEN OTHERS THEN
486 ROLLBACK TO zpb_acval_pvt_validate;
487 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
488 x_changeCurrentRun := 'Y';
489 return;
490 */
491 END validate_currentrun_helper;
492
493 PROCEDURE validate_currentrun (
494 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
495 x_changeCurrentRun OUT NOCOPY VARCHAR2)
496
497 IS
498
499 l_api_name CONSTANT VARCHAR2(30) := 'validate_currentrun_helper';
500 l_api_version CONSTANT NUMBER := 1.0;
501
502 l_dataAw varchar2(128);
503 l_dataAwQual varchar2(128);
504 l_line_dim varchar2(128);
505 l_hier_dim varchar2(128);
506
507 begin
508
509 -- push objects that need to maintain status
510 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw;
511 l_dataAwQual := l_dataAw ||'!';
512 -- get line dimension name
513 l_line_dim := zpb_aw.interp('shw CM.GETLINEDIM('''||l_dataAw||''')');
514 l_line_dim := l_dataAWQual || l_line_dim;
515 l_hier_dim := zpb_aw.interp('shw obj(prp ''HIERDIM'' ' ||'''' || l_line_dim || ''')');
516 l_hier_dim := l_dataAWQual || l_hier_dim;
517 zpb_aw.execute('push ' || l_line_dim);
518 zpb_aw.execute('push ' || l_hier_dim);
519
520 validate_currentrun_helper(p_analysis_cycle_id, x_changeCurrentRun);
521 zpb_aw.execute('pop ' || l_hier_dim);
525 end validate_currentrun;
522 zpb_aw.execute('pop ' || l_line_dim);
523 return;
524
526
527 function compare_queries(p_dataAw IN varchar2,
528 p_first_query IN varchar2,
529 p_second_query IN varchar2,
530 p_line_dim IN varchar2) return integer
531 AS
532 l_api_name CONSTANT VARCHAR2(30) := 'compare_queries';
533 l_vs varchar2(100);
534 l_dataAwQual varchar2(70);
535 l_first_superset boolean;
536 l_second_superset boolean;
537 l_equal integer;
538 begin
539 l_dataAwQual := p_dataAw ||'!';
540 -- call the first query
541 zpb_aw_status.get_status(p_dataAw,p_first_query);
542 -- get the valuseset name
543 l_vs := '&' || 'joinchars('''||l_dataAwQual||''' obj(prp ''LASTQUERYVS'' '||''''
544 ||l_dataAwQual||p_line_dim ||'''))';
545 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'valueset name:' ||l_vs);
546
547 -- initialize
548 zpb_aw.execute('push oknullstatus '||l_dataAwQual ||p_line_dim);
549 zpb_aw.execute('oknullstatus=y');
550 if (not zpb_aw.interpbool('shw exists(''l_temp_vs'')')) then
551 zpb_aw.execute(' dfn l_temp_vs valueset '||l_dataAwQual ||p_line_dim|| ' aw ' ||p_dataAw);
552 end if;
553
554 -- lmt the first valueset to the first query
555 zpb_aw.execute('lmt '|| l_dataAwQual ||'l_temp_vs to '
556 || l_vs );
557
558 -- generate the valuseset for the second query
559 zpb_aw_status.get_status(p_dataAw,p_second_query);
560
561
562 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr w 40 values('||l_dataAwQual ||'l_temp_vs)'),1,254));
563 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr w 40 values('||l_vs||')'),1,254));
564
565 -- check if the two valusesets are identical
566 l_first_superset := zpb_aw.interpbool('shw inlist(values('||l_dataAwQual||'l_temp_vs)'
567 || ' values('||l_vs||'))');
568 l_second_superset := zpb_aw.interpbool('shw inlist(values('||l_vs||')'
569 || ' values(l_temp_vs))');
570 if l_first_superset then
571 if l_second_superset then
572 l_equal := 0;
573 else
574 l_equal := 1;
575 end if;
576 else
577 if l_second_superset then
578 l_equal := 2;
579 else
580 l_equal := 3;
581 end if;
582 end if;
583
584 zpb_aw.execute('pop oknullstatus '||l_dataAwQual ||p_line_dim);
585 return l_equal;
586
587 exception
588 when others then
589 l_equal := 0;
590 zpb_log.write_event(G_PKG_NAME,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
591 return l_equal;
592
593 end ;
594 -- this procedure returns can return 4 different values in the output variable
595 -- 0: both queries are identical
596 -- 1: first query is a superset of second
597 -- 2: second query is a superset of first
598 -- 3: both queries are different
599 procedure compare_line_members(p_first_query IN varchar2,
600 p_second_query IN varchar2,
601 x_equal OUT NOCOPY integer) IS
602
603 l_api_name CONSTANT VARCHAR2(30) := 'compare_line_members';
604 l_dataAw varchar2(30);
605 l_dataAwQual varchar2(70);
606 l_temp_vs varchar2(100);
607 l_line_dim zpb_cycle_model_dimensions.dimension_name%type;
608
609 begin
610
611 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw;
612 l_dataAwQual := l_dataAw ||'!';
613
614 -- get line dimension name
615 l_line_dim := zpb_aw.interp('shw CM.GETLINEDIM('''||l_dataAw||''')');
616 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'line_dim:' ||l_dataAwQual || l_line_dim);
617
618 x_equal := compare_queries(l_dataAw,p_first_query,p_second_query,l_line_dim);
619 -- cleanup and return
620 if (not zpb_aw.interpbool('shw exists(''l_temp_vs'')')) then
621 zpb_aw.execute('delete l_temp_vs aw ' ||l_dataAw);
622 end if;
623
624 exception
625 when others then
626 x_equal := 0;
627 zpb_log.write_event(G_PKG_NAME,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
628
629 end compare_line_members;
630
631
632
633
634 -- this procedure initializes the SOLVE objects so that they
635 -- can be used by multiple validation rules later without having to reset
636 -- the solve objects for every rule.
637 procedure initialize_solve_object(
638 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type) IS
639
640 l_api_name CONSTANT VARCHAR2(50) := 'initialize_solve_object';
641 l_dataAw varchar2(100);
642 return_status varchar2(4000);
643 msg_count number;
644 msg_data varchar2(4000);
645 begin
646 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw;
647 -- zpb_aw.execute('aw attach '|| zpb_aw.get_schema||'.'||zpb_aw.get_code_aw(fnd_global.user_id) || ' ro');
648 zpb_aw.execute('push oknullstatus ');
649 zpb_aw.execute('oknullstatus=y');
650 /*
651 zpb_aw.initialize_workspace(1.0, FND_API.G_FALSE,
652 FND_API.G_VALID_LEVEL_FULL, return_status, msg_count,
653 msg_data, fnd_global.user_id, 'ZPB_MANAGER_RESP');
654 */
658 zpb_aw.execute('call cm.setoutsels('''||p_analysis_cycle_id||''', '''||p_analysis_cycle_id||''')');
655 zpb_log.write_event(G_PKG_NAME||l_api_name,zpb_aw.interp('rpr w 30 aw(list)'));
656 zpb_aw.execute('call sv.get.solvedef('''||p_analysis_cycle_id||''' NA yes)');
657 zpb_aw.execute('call cm.setinsels('''||p_analysis_cycle_id||''')');
659
660 end initialize_solve_object;
661
662
663 -- this procedure detaches all attached aw and cleans the workspace.
664 PROCEDURE detach_aw(p_data_aw IN varchar2) IS
665
666 l_api_name CONSTANT VARCHAR2(20) := 'detach_aw';
667 return_status varchar2(4000);
668 msg_count number;
669 msg_data varchar2(4000);
670 begin
671
672 zpb_aw.execute('pop oknullstatus');
673 -- zpb_aw.execute('aw detach '|| zpb_aw.get_schema||'.'||p_data_aw );
674 -- zpb_aw.execute('aw detach '|| zpb_aw.get_schema||'.'||zpb_aw.get_code_aw(fnd_global.user_id) );
675
676 zpb_log.write_statement(G_PKG_NAME||l_api_name,zpb_aw.interp('rpr w 30 aw(list)'));
677
678 -- dont call clean_workspace because it resets the context also. Will have to fix this later.
679 -- zpb_aw.clean_workspace(1.0, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL, return_status, msg_count, msg_data);
680
681 end detach_aw;
682
683
684
685 -- this procedure returns can return 5 different values in the output variable
686 -- 0: Solve line members are identical to the line members of the model
687 -- 1: Solve has more line members than model
688 -- 2: Solve has less line members than model
689 -- 3: Both Solve and model have line members that dont exist in the other
690 -- 4: Cycle was not completely defined yet. so no validation was performed
691 PROCEDURE val_solve_eq_model(
692 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
693 x_comparision OUT NOCOPY VARCHAR2) IS
694
695 l_api_name CONSTANT VARCHAR2(30) := 'val_solve_eq_model';
696 l_task_id zpb_analysis_cycle_tasks.task_id%type;
697 l_line_dim zpb_cycle_model_dimensions.dimension_name%type;
698 l_query_name zpb_cycle_model_dimensions.query_object_path%type;
699 l_query_path zpb_cycle_model_dimensions.query_object_path%type;
700 l_vs varchar2(100);
701 l_dataAw varchar2(100);
702 l_pushed_solve varchar2(2) := 'N';
703
704 cursor query_identifier is
705 select query_object_path|| '/' || query_object_name
706 from zpb_cycle_model_dimensions
707 where dimension_name = l_line_dim
708 and analysis_cycle_id = p_analysis_cycle_id;
709
710 begin
711
712 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name, 'Validating if solve is equal to model ' || to_char(p_analysis_cycle_id) || '.');
713 -- initialize_solve_object(p_analysis_cycle_id);
714 zpb_aw.execute('push SV.LN.DIM ');
715 l_pushed_solve := 'Y';
716 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw;
717
718 l_line_dim := zpb_aw.interp('shw CM.GETLINEDIM('''||l_dataAw||''')');
719
720
721 -- get the valuseset name
722 l_vs := '&' || 'joinchars('''||l_dataAw||'!'' obj(prp ''LASTQUERYVS'' '||''''
723 ||l_dataAw||'!' ||l_line_dim ||'''))';
724
725 open query_identifier;
726 fetch query_identifier into l_query_name;
727
728 -- cycle not defined properly yet. so return without doing any validation
729 -- not information will be provided in the validation page
730 if query_identifier%notfound then
731 x_comparision := 4;
732 zpb_aw.execute('pop SV.LN.DIM ');
733 return;
734 end if;
735
736 close query_identifier;
737
738 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'query is :' ||l_query_name);
739
740 zpb_aw_status.get_status(l_dataAw,l_query_name);
741
742 if zpb_aw.interpbool('shw inlist(values(SV.LN.DIM) values('||
743 l_vs||'))') then
744 if zpb_aw.interpbool('shw inlist(values('|| l_vs||') values(SV.LN.DIM))') then
745 x_comparision := '0';
746 else
747 x_comparision := '1';
748 end if;
749 else
750 if zpb_aw.interpbool('shw inlist(values('|| l_vs||') values(SV.LN.DIM))') then
751 x_comparision := '2';
752 else
753 x_comparision := '3';
754 end if;
755 end if;
756 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr values('||l_vs||')'),1,254) );
757 zpb_aw.execute('pop SV.LN.DIM ');
758 return;
759
760 exception
761 when others then
762 x_comparision := 4;
763 if l_pushed_solve = 'Y' then
764 zpb_aw.execute('pop SV.LN.DIM ');
765 end if;
766 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
767
768 end val_solve_eq_model;
769
770 -- this procedure returns two possible output values
771 -- 'Y': The union of Line Members of ALL Load Data Tasks is equal to the
772 -- line members of Solve
773 -- 'N': The union of Line Members of ALL Load Data Tasks is different from the
774 -- line members of Solve
775 procedure val_solve_eq_data_load(
776 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
777 x_isvalid OUT NOCOPY VARCHAR2,
778 x_dim_members OUT NOCOPY VARCHAR2) IS
782 l_query_name zpb_cycle_model_dimensions.query_object_path%type;
779
780 l_api_name CONSTANT VARCHAR2(30) := 'val_solve_eq_model';
781 l_line_dim zpb_cycle_model_dimensions.dimension_name%type;
783 l_query_path zpb_cycle_model_dimensions.query_object_path%type;
784 l_task_id zpb_analysis_cycle_tasks.task_id%type;
785 l_vs varchar2(100);
786 l_dataAw varchar2(100);
787 l_task_exists varchar2(1);
788 l_path_exists varchar2(1);
789 l_name_exists varchar2(1);
790 l_selection_type varchar2(30);
791 l_pushed_solve varchar2(2) := 'N';
792
793 cursor generate_task is
794 select task_id
795 from zpb_analysis_cycle_tasks
796 where analysis_cycle_id = p_analysis_cycle_id
797 and wf_process_name = 'LOAD_DATA';
798
799 cursor load_data_query is
800 select name,value
801 from zpb_task_parameters
802 where task_id = l_task_id
803 and name in ('QUERY_OBJECT_PATH','QUERY_OBJECT_NAME', 'DATA_SELECTION_TYPE'
804 );
805 begin
806
807 -- initialize_solve_object(p_analysis_cycle_id);
808 zpb_aw.execute('push SV.LN.DIM ');
809 l_pushed_solve := 'Y';
810 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw;
811
812 l_line_dim := zpb_aw.interp('shw CM.GETLINEDIM('''||l_dataAw||''')');
813
814 zpb_aw.execute('lmt SV.LN.DIM keep SV.DEF.VAR(SV.DEF.PROP.DIM ''TYPE'') eq ''LOADED''');
815
816
817 -- get the valuseset name
818 l_vs := '&' || 'joinchars('''||l_dataAw||'!'' obj(prp ''LASTQUERYVS'' '||''''
819 ||l_dataAw||'!' ||l_line_dim ||'''))';
820
821 l_task_exists := 'n';
822 for each in generate_task loop
823 l_task_exists := 'y';
824 l_path_exists := 'n';
825 l_name_exists := 'n';
826
827 l_task_id := each.task_id;
828 for each in load_data_query loop
829 if (each.name = 'QUERY_OBJECT_PATH') then
830 l_path_exists := 'y';
831 l_query_path := each.value;
832 end if;
833 if (each.name = 'QUERY_OBJECT_NAME') then
834 l_name_exists := 'y';
835 l_query_name := each.value;
836 end if;
837 if (each.name = 'DATA_SELECTION_TYPE') then
838 l_name_exists := 'y';
839 l_selection_type := each.value;
840 end if;
841 end loop;
842
843 -- if any query is not properly defined then donot perform any validation
844 -- and return. Allso return if all line items are being selected
845 if l_path_exists <> 'y' or l_name_exists <> 'y' or l_selection_type = 'ALL_LINE_ITEMS_SELECTION_TYPE' then
846 x_isvalid := 'Y';
847 zpb_aw.execute('pop SV.LN.DIM ');
848 return;
849 end if;
850
851 l_query_name := l_query_path ||'/' || l_query_name;
852 -- l_query_name := 'System Private/Controller/AC11736/MODEL_QUERY_5894';
853
854 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'query is :' ||l_query_name);
855 zpb_aw_status.get_status(l_dataAw,l_query_name);
856
857 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr values('||l_vs||')'),1,254) );
858
859 zpb_aw.execute(' lmt SV.LN.DIM keep filterlines(values(sv.ln.dim) if inlist(values('||l_vs||') value) then na else value)');
860 end loop;
861
862
863 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr values(SV.LN.DIM)'),1,255) );
864
865 if zpb_aw.interpbool('shw statlen(SV.LN.DIM) gt 0 ') then
866 x_isvalid := 'N';
867 x_dim_members := zpb_aw.interp('shw joinchars(joincols(filterlines(values(SV.LN.DIM) joinchars(''\'''' value ''\'''')) '',''))');
868 if length(x_dim_members) > 0 then
869 x_dim_members := substr(x_dim_members,1,length(x_dim_members)-1);
870 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(x_dim_members,1,254));
871 end if;
872 else
873 x_isvalid := 'Y';
874 end if;
875
876 zpb_aw.execute('pop SV.LN.DIM ');
877 return;
878
879 exception
880 when others then
881 x_isvalid := 'Y';
882 if l_pushed_solve = 'Y' then
883 zpb_aw.execute('pop SV.LN.DIM ');
884 end if;
885
886 zpb_log.write_event(G_PKG_NAME,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
887
888 end val_solve_eq_data_load;
889
890
891 -- this procedure returns two possible output values
892 -- 'Y': if all Load Data Tasks Line members are subset of line members of Solve
893 -- 'N': there exists 1 or more Load Data Tasks which have line members that are
894 -- not a subset of the line members of Solve
895 procedure val_solve_gt_than_load(
896 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
897 x_isvalid OUT NOCOPY VARCHAR2,
898 x_task_name OUT NOCOPY VARCHAR2,
899 x_dim_members OUT NOCOPY VARCHAR2) IS
900
901 l_api_name CONSTANT VARCHAR2(30) := 'val_solve_gt_than_load';
902 l_line_dim zpb_cycle_model_dimensions.dimension_name%type;
903 l_query_name zpb_cycle_model_dimensions.query_object_path%type;
904 l_query_path zpb_cycle_model_dimensions.query_object_path%type;
905 l_task_id zpb_analysis_cycle_tasks.task_id%type;
906 l_task_name zpb_analysis_cycle_tasks.task_name%type;
907 l_vs varchar2(100);
911 l_name_exists varchar2(1);
908 l_dataAw varchar2(100);
909 l_task_exists varchar2(1);
910 l_path_exists varchar2(1);
912 l_pushed_solve varchar2(2) := 'N';
913 l_dim_members varchar2(32000);
914 l_selection_type varchar2(30);
915
916 cursor generate_task is
917 select task_id,task_name
918 from zpb_analysis_cycle_tasks
919 where analysis_cycle_id = p_analysis_cycle_id
920 and wf_process_name = 'LOAD_DATA';
921
922 cursor load_data_query is
923 select name,value
924 from zpb_task_parameters
925 where task_id = l_task_id
926 and name in ('QUERY_OBJECT_PATH','QUERY_OBJECT_NAME','DATA_SELECTION_TYPE');
927
928 begin
929
930 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name, 'Validating if generate worksheet tasks have input line member ' || to_char(p_analysis_cycle_id) || '.');
931 zpb_aw.execute('push SV.LN.DIM ');
932 l_pushed_solve := 'Y';
933 x_isvalid := 'Y';
934 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw;
935
936 l_line_dim := zpb_aw.interp('shw CM.GETLINEDIM('''||l_dataAw||''')');
937
938 zpb_aw.execute('lmt SV.LN.DIM to SV.DEF.VAR(SV.DEF.PROP.DIM ''TYPE'') eq ''LOADED''');
939
940
941 -- get the valuseset name
942 l_vs := '&' || 'joinchars('''||l_dataAw||'!'' obj(prp ''LASTQUERYVS'' '||''''
943 ||l_dataAw||'!' ||l_line_dim ||'''))';
944
945 /* for bug 4771735 --> Restrict the valueset also to type Loaded */
946 zpb_aw.execute(' push '||l_vs);
947 zpb_aw.execute(' lmt '||l_vs||' keep values(SV.LN.DIM)');
948 /* FOR BUG 4771735 */
949
950 l_task_exists := 'n';
951 for each in generate_task loop
952 l_task_exists := 'y';
953 l_path_exists := 'n';
954 l_name_exists := 'n';
955 l_task_id := each.task_id;
956 l_task_name := each.task_name;
957 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,to_char(l_task_id));
958
959 for each in load_data_query loop
960 if (each.name = 'QUERY_OBJECT_PATH') then
961 l_path_exists := 'y';
962 l_query_path := each.value;
963 end if;
964 if (each.name = 'QUERY_OBJECT_NAME') then
965 l_name_exists := 'y';
966 l_query_name := each.value;
967 end if;
968 if (each.name = 'DATA_SELECTION_TYPE') then
969 l_name_exists := 'y';
970 l_selection_type := each.value;
971 end if;
972 end loop;
973
974 -- if any query is not properly defined then donot perform any validation
975 -- and return
976 if l_path_exists <> 'y' or l_name_exists <> 'y' then
977 x_isvalid := 'Y';
978 zpb_aw.execute('pop SV.LN.DIM ');
979 -- for bug 4771735
980 zpb_aw.execute('pop '||l_vs);
981 return;
982 end if;
983
984 -- only validate this task if it is not selecting all lines
985 -- because all_lines will always be equal to the Solveline items list
986
987 if l_selection_type <> 'ALL_LINE_ITEMS_SELECTION_TYPE' then
988 l_query_name := l_query_path ||'/' || l_query_name;
989
990 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'query is :' ||l_query_name);
991 zpb_aw_status.get_status(l_dataAw,l_query_name);
992
993 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr values('||l_vs||')'),1,254) );
994 if not zpb_aw.interpbool('shw inlist(values(SV.LN.DIM) values('||
995 l_vs||'))') then
996
997 x_isvalid := 'N';
998 zpb_aw.execute('lmt '||l_vs||' remove values(SV.LN.DIM)');
999 l_dim_members := zpb_aw.interp('shw joinchars(joincols(filterlines(values('||l_vs||') joinchars(''\'''' value ''\'''')) '',''))');
1000 if length(l_dim_members) > 0 then
1001 l_dim_members := substr(l_dim_members,1,length(l_dim_members)-1);
1002 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(l_dim_members,1,254));
1003 end if;
1004 -- construct the list. Also check for the max length case(highly unlikely)
1005 if nvl(length(x_dim_members),0) + length(l_dim_members) < MAX_LENGTH then
1006 x_dim_members := x_dim_members || ',' ||l_dim_members;
1007 end if;
1008 x_task_name := l_task_name || ',' ||x_task_name;
1009 end if;
1010 else
1011 x_isvalid := 'Y';
1012 end if; -- all_line_items_selection_type
1013
1014 end loop;
1015
1016 -- if task not defined properly then return success
1017 if l_task_exists <> 'y' then
1018 x_isvalid := 'Y';
1019 zpb_aw.execute('pop SV.LN.DIM ');
1020 -- for bug 4771735
1021 zpb_aw.execute('pop '||l_vs);
1022 return;
1023 end if;
1024
1025 zpb_aw.execute('pop SV.LN.DIM ');
1026 -- for bug 4771735
1027 zpb_aw.execute('pop '||l_vs);
1028
1029 if length(x_task_name) > 0 then
1030 x_task_name := substr(x_task_name,1,length(x_task_name)-1);
1031 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(x_task_name,1,254));
1032 end if;
1033 if length(x_dim_members) > 0 then
1034 x_dim_members := substr(x_dim_members,2,length(x_dim_members)-1);
1035 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(x_dim_members,1,254));
1036 end if;
1037 return;
1038
1039 exception
1040 when others then
1044 -- for bug 4771735
1041 x_isvalid := 'Y';
1042 if l_pushed_solve = 'Y' then
1043 zpb_aw.execute('pop SV.LN.DIM ');
1045 zpb_aw.execute('pop '||l_vs);
1046 end if;
1047 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
1048
1049 end val_solve_gt_than_load;
1050
1051 -- this procedure returns two possible output values
1052 -- 'Y': There doesn't exist a Generate Worksheet Task whose source Line items
1053 -- are all Loaded or Calculated
1054 -- 'N': there exists 1 or more Load Data Tasks which have line members that are
1055 -- not a subset of the line members of Solve
1056 procedure validate_generate_worksheet(
1057 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
1058 x_isvalid OUT NOCOPY VARCHAR2,
1059 x_invalid_tasks_list OUT NOCOPY VARCHAR2) IS
1060
1061 l_api_name CONSTANT VARCHAR2(30) := 'val_solve_eq_model';
1062 l_line_dim zpb_cycle_model_dimensions.dimension_name%type;
1063 l_query_name zpb_cycle_model_dimensions.query_object_path%type;
1064 l_query_path zpb_cycle_model_dimensions.query_object_path%type;
1065 l_task_name zpb_task_parameters.name%type;
1066 l_task_id zpb_analysis_cycle_tasks.task_id%type;
1067 l_vs varchar2(100);
1068 l_dataAw varchar2(100);
1069 l_task_exists varchar2(1);
1070 l_path_exists varchar2(1);
1071 l_name_exists varchar2(1);
1072 l_pushed_solve varchar2(2) := 'N';
1073
1074 cursor generate_task is
1075 select task_id, task_name
1076 from zpb_analysis_cycle_tasks
1077 where analysis_cycle_id = p_analysis_cycle_id
1078 and wf_process_name = 'GENERATE_TEMPLATE';
1079
1080 cursor generate_worksheet_query is
1081 select name,value
1082 from zpb_task_parameters
1083 where task_id = l_task_id
1084 and name in ('TEMPLATE_DATAENTRY_OBJ_PATH','TEMPLATE_DATAENTRY_OBJ_NAME');
1085 begin
1086
1087 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name, 'Validating if all generate worksheet tasks have an input line member ' || to_char(p_analysis_cycle_id));
1088 zpb_aw.execute('push SV.LN.DIM ');
1089 l_pushed_solve := 'Y';
1090 x_invalid_tasks_list := '';
1091 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw;
1092
1093 l_line_dim := zpb_aw.interp('shw CM.GETLINEDIM('''||l_dataAw||''')');
1094
1095 zpb_aw.execute('lmt SV.LN.DIM keep (SV.DEF.VAR(SV.DEF.PROP.DIM ''TYPE'') eq ''INPUT'' or SV.DEF.VAR(SV.DEF.PROP.DIM ''TYPE'') eq ''INITIALIZED'')');
1096
1097
1098 -- get the valuseset name
1099 l_vs := '&' || 'joinchars('''||l_dataAw||'!'' obj(prp ''LASTQUERYVS'' '||''''
1100 ||l_dataAw||'!' ||l_line_dim ||'''))';
1101
1102 l_task_exists := 'n';
1103 for each in generate_task loop
1104 l_task_exists := 'y';
1105 l_path_exists := 'n';
1106 l_name_exists := 'n';
1107 l_task_id := each.task_id;
1108 l_task_name := each.task_name;
1109 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,to_char(l_task_id));
1110
1111 for each in generate_worksheet_query loop
1112 if (each.name = 'TEMPLATE_DATAENTRY_OBJ_PATH') then
1113 l_path_exists := 'y';
1114 l_query_path := each.value;
1115 end if;
1116 if (each.name = 'TEMPLATE_DATAENTRY_OBJ_NAME') then
1117 l_name_exists := 'y';
1118 l_query_name := each.value;
1119 end if;
1120 end loop;
1121
1122 -- if any query is not properly defined then donot perform any validation
1123 -- and return
1124 if l_path_exists <> 'y' or l_name_exists <> 'y' then
1125 x_isvalid := 'Y';
1126 zpb_aw.execute('pop SV.LN.DIM ');
1127 return;
1128 end if;
1129
1130 l_query_name := l_query_path ||'/' || l_query_name;
1131 -- l_query_name := 'System Private/Controller/AC11736/MODEL_QUERY_5894';
1132
1133 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'query is :' ||l_query_name);
1134 zpb_aw_status.get_status(l_dataAw,l_query_name);
1135
1136 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr values('||l_vs||')'),1,254) );
1137 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr values(SV.LN.DIM)'),1,254) );
1138 zpb_aw.execute('lmt '|| l_vs || ' keep values(SV.LN.DIM)');
1139
1140 -- add to the list of invalid tasks if the validation fails
1141 if zpb_aw.interpbool('shw statlen('||l_vs||') gt 0 ') then
1142 x_isvalid := 'Y';
1143 else
1144 x_isvalid := 'N';
1145 x_invalid_tasks_list := x_invalid_tasks_list || ',' ||l_task_name;
1146 end if;
1147
1148 end loop;
1149
1150 -- if task not defined properly then return success
1151 if l_task_exists <> 'y' then
1152 x_isvalid := 'Y';
1153 end if;
1154
1155 zpb_aw.execute('pop SV.LN.DIM ');
1156
1157 -- remove extra comma from the front
1158 if x_isvalid = 'N' and length(x_invalid_tasks_list) > 0 then
1159 x_invalid_tasks_list := substr(x_invalid_tasks_list,2);
1160 end if;
1161
1162 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'invalid tasks:'|| x_invalid_tasks_list);
1163 return;
1164
1165 exception
1166 when others then
1170 end if;
1167 x_isvalid := 'Y';
1168 if l_pushed_solve = 'Y' then
1169 zpb_aw.execute('pop SV.LN.DIM ');
1171 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
1172
1173 end validate_generate_worksheet;
1174
1175 PROCEDURE validate_input_selections(
1176 p_api_version IN NUMBER,
1177 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1178 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1179 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1180 x_return_status OUT NOCOPY VARCHAR2 ,
1181 x_msg_count OUT NOCOPY NUMBER,
1182 x_msg_data OUT NOCOPY VARCHAR2,
1183 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
1184 p_inputDims IN VARCHAR2,
1185 x_isvalid OUT NOCOPY VARCHAR2,
1186 x_invalid_dim_list OUT NOCOPY VARCHAR2,
1187 x_invalid_linemem_list OUT NOCOPY VARCHAR2) IS
1188
1189 l_api_name CONSTANT VARCHAR2(30) := 'validate_input_selections';
1190 l_api_version CONSTANT NUMBER := 1.0;
1191 l_dataAw VARCHAR2(4000);
1192 l_currentDim zpb_solve_input_selections.dimension%type ;
1193 l_fetchedDim zpb_solve_input_selections.dimension%type;
1194 l_inputSelection zpb_solve_input_selections.selection_name%type;
1195 l_currentLine zpb_solve_input_selections.member%type;
1196 l_dimCount integer;
1197 i integer := 1;
1198 l_currpos integer := 1;
1199 l_nextpos integer := 1;
1200 l_length integer := 0;
1201 l_dimValid varchar2(1) := 'Y';
1202 l_source_type number;
1203 l_timedim varchar2(30);
1204 l_alldims_invalid varchar2(1);
1205 l_hierdim varchar(50);
1206 l_cuminputvs varchar2(250);
1207 l_hierarchy varchar2(50);
1208 l_parentRel varchar2(50);
1209 l_lineDim varchar2(100);
1210
1211 cursor member_c is
1212 select member
1213 from zpb_solve_member_defs
1214 where analysis_cycle_id = p_analysis_cycle_id
1215 and source_type in (1000,1100,1130)
1216 and member not in (select member
1217 from zpb_solve_input_selections
1218 where analysis_cycle_id = p_analysis_cycle_id);
1219
1220 -- find all the null selections
1221 cursor nullselections_c (p_dim in varchar2, p_time_dim in varchar2) is
1222 select i.member,i.dimension, i.selection_name
1223 from zpb_solve_input_selections i, zpb_solve_member_defs d,
1224 zpb_line_dimensionality l
1225 where d.member = i.member
1226 and d.analysis_cycle_id = i.analysis_cycle_id
1227 and d.analysis_cycle_id = p_analysis_cycle_id
1228 and l.dimension = i.dimension
1229 and l.member = i.member
1230 and l.analysis_cycle_id = i.analysis_cycle_id
1231 and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
1232 and ( nvl(l.force_input_flag,'N') = 'Y'
1233 OR nvl(l.sum_members_flag,'N') = 'N')
1234 and d.source_type in (1000,1100,1130)
1235 and i.dimension = p_time_dim
1236 and i.dimension = p_dim
1237 and i.selection_name is null
1238 union all
1239 select i.member, i.dimension,i.selection_name
1240 from zpb_solve_input_selections i, zpb_solve_member_defs d,
1241 zpb_line_dimensionality l
1242 where d.member = i.member
1243 and d.analysis_cycle_id = i.analysis_cycle_id
1244 and d.analysis_cycle_id = p_analysis_cycle_id
1245 and l.dimension = i.dimension
1246 and l.member = i.member
1247 and l.analysis_cycle_id = i.analysis_cycle_id
1248 and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
1249 and ( nvl(l.force_input_flag,'N') = 'Y'
1250 OR nvl(l.sum_members_flag,'N') = 'N')
1251 and d.source_type in (1000)
1252 and i.dimension = p_dim
1253 and i.dimension <> p_time_dim
1254 and i.selection_name is null
1255 union all
1256 select i.member, i.dimension,i.selection_name
1257 from zpb_solve_input_selections i, zpb_solve_member_defs d
1258 where d.member = i.member
1259 and d.analysis_cycle_id = i.analysis_cycle_id
1260 and d.analysis_cycle_id = p_analysis_cycle_id
1261 and d.source_type in (1100,1130)
1262 and i.dimension = p_dim
1263 and i.dimension <> p_time_dim
1264 and i.selection_name is null;
1265
1266 -- find all the non-null selections and evaluate them
1267 cursor nonnullselections_c(p_dim in varchar2, p_time_dim in varchar2) is
1268 select distinct i.selection_name
1269 from zpb_solve_input_selections i, zpb_solve_member_defs d,
1270 zpb_line_dimensionality l
1271 where i.member = d.member
1272 and i.dimension = p_time_dim
1273 and i.dimension = p_dim
1274 and i.analysis_cycle_id = d.analysis_cycle_id
1275 and l.dimension = i.dimension
1276 and l.member = i.member
1277 and l.analysis_cycle_id = i.analysis_cycle_id
1278 and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
1279 and ( nvl(l.force_input_flag,'N') = 'Y'
1280 OR nvl(l.sum_members_flag,'N') = 'N')
1281 and d.source_type in (1000,1100,1130)
1282 and i.analysis_cycle_id = p_analysis_cycle_id
1283 and i.selection_name is not null
1284 union all
1285 select distinct i.selection_name
1286 from zpb_solve_input_selections i, zpb_solve_member_defs d,
1290 and i.dimension = p_dim
1287 zpb_line_dimensionality l
1288 where i.member = d.member
1289 and i.dimension <> p_time_dim
1291 and i.analysis_cycle_id = d.analysis_cycle_id
1292 and l.dimension = i.dimension
1293 and l.member = i.member
1294 and l.analysis_cycle_id = i.analysis_cycle_id
1295 and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
1296 and ( nvl(l.force_input_flag,'N') = 'Y'
1297 OR nvl(l.sum_members_flag,'N') = 'N')
1298 and d.source_type in (1000)
1299 and i.analysis_cycle_id = p_analysis_cycle_id
1300 and i.selection_name is not null
1301 union all
1302 select distinct i.selection_name
1303 from zpb_solve_input_selections i, zpb_solve_member_defs d
1304 where i.member = d.member
1305 and i.dimension <> p_time_dim
1306 and i.dimension = p_dim
1307 and i.analysis_cycle_id = d.analysis_cycle_id
1308 and d.source_type in (1100,1130)
1309 and i.analysis_cycle_id = p_analysis_cycle_id
1310 and i.selection_name is not null;
1311
1312 -- find out the distinct output hierarchies on a dimension
1313 cursor outputhierarchy_c(p_dim in varchar2, p_input_line in varchar2) is
1314 select distinct o.hierarchy
1315 from zpb_solve_output_selections o
1316 where o.analysis_cycle_id = p_analysis_cycle_id
1317 and o.hierarchy <> 'NONE'
1318 and o.dimension = p_dim
1319 and o.member=p_input_line
1320 AND NVL(o.match_input_flag, 'N') <> 'Y';
1321
1322 cursor hiermember_c(p_dim in varchar2, l_selection_name in varchar2,
1323 l_hierarchy in varchar2) is
1324 select i.member
1325 from zpb_solve_input_selections i,
1326 zpb_solve_output_selections o
1327 where i.dimension = p_dim
1328 and i.analysis_cycle_id = p_analysis_cycle_id
1329 and i.member = o.member
1330 and i.analysis_cycle_id = o.analysis_cycle_id
1331 and i.selection_name = l_selection_name
1332 and o.hierarchy = l_hierarchy
1333 and i.dimension = o.dimension
1334 AND NVL(o.match_input_flag, 'N') <> 'Y';
1335
1336 cursor selection_member_c(p_dim in varchar2, l_selection_name in varchar2) is
1337 select member
1338 from zpb_solve_input_selections
1339 where selection_name = l_selection_name
1340 and dimension = p_dim
1341 and analysis_cycle_id = p_analysis_cycle_id;
1342
1343
1344 begin
1345 -- Standard Start of API savepoint
1346 SAVEPOINT validate_input_selections;
1347 -- Standard call to check for call compatibility.
1348 IF NOT FND_API.Compatible_API_Call( l_api_version,
1349 p_api_version,
1350 l_api_name,
1351 G_PKG_NAME)
1352 THEN
1353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1354 END IF;
1355
1356 -- Initialize message list if p_init_msg_list is set to TRUE.
1357 IF FND_API.to_Boolean(p_init_msg_list) THEN
1358 FND_MSG_PUB.initialize;
1359 END IF;
1360
1361 -- Initialize API return status to success
1362 x_return_status := FND_API.G_RET_STS_SUCCESS;
1363 x_isvalid := 'Y';
1364 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name,'validating solve input levels');
1365 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw || '!';
1366 l_alldims_invalid := 'N';
1367 zpb_aw.execute('lmt ' || l_dataAw ||'instance to '''|| p_analysis_cycle_id ||'''');
1368 l_lineDim := zpb_aw.interp('shw CM.GETLINEDIM(''SHARED'')');
1369 zpb_aw.execute('lmt ' || l_dataAw ||l_lineDim || ' to values(sv.ln.dim)');
1370
1371 -- first find all lines which have no input selections on any
1372 -- dimension
1373 open member_c;
1374 fetch member_c into l_currentLine;
1375 while member_c%found loop
1376
1377 x_isvalid := 'N';
1378 if x_invalid_linemem_list is null or length(x_invalid_linemem_list) < 1950 then
1379 x_invalid_linemem_list := x_invalid_linemem_list ||','''
1380 || l_currentLine ||'''';
1381 end if;
1382 if l_alldims_invalid <> 'Y' then
1383 x_invalid_dim_list := x_invalid_dim_list || substr(p_inputDims,1,length(p_inputDims)-1);
1384 end if;
1385 l_alldims_invalid := 'Y';
1386 -- get the next line member
1387 fetch member_c into l_currentLine;
1388 end loop; -- while member loop
1389 close member_c;
1390
1391 l_timedim := zpb_aw.interp('shw dl.gettimedim');
1392 -- run the validation for every input level row
1393 -- initialize for traversing the list of dimensions
1394 l_length := nvl(length(p_inputDims),0);
1395
1396 -- bail with success if no input dimensions
1397 if l_length < 2 then
1398 return;
1399 end if;
1400
1401
1402 l_currpos := 1;
1403 l_nextpos := 1;
1404
1405 while l_currpos < l_length loop
1406
1407 l_nextpos := instr(p_inputDims,',', l_currpos);
1408
1409 l_currentDim := substr(p_inputDims,l_currpos,l_nextpos - l_currpos);
1410 l_dimValid := 'Y';
1411 l_hierdim := zpb_aw.interp('shw obj(prp ''HIERDIM'' ' ||''''
1412 ||l_dataAw ||l_currentdim ||''')');
1413
1414 l_cuminputvs := zpb_aw.interp('shw obj(prp ''DINPUTVS'' ' ||''''
1415 ||l_dataAw||l_currentdim ||''')')||'(' ||
1416 l_dataAw ||l_lineDim || ' ' || l_dataAw || 'DINPUTVSPOINTER'
1420 --dbms_output.put_line(l_cuminputvs);
1417 || '(' || l_dataAw || 'UNIVDIM ''' || l_currentDim
1418 || '''))' ;
1419
1421 l_parentRel := zpb_aw.interp('shw obj(prp ''PARENTREL'' ' ||''''
1422 ||l_dataAw ||l_currentdim ||''')');
1423
1424 -- check that there are no line items which have null query objects
1425 open nullselections_c(l_currentDim, l_timedim);
1426 fetch nullselections_c into l_currentLine, l_fetchedDim, l_inputSelection;
1427
1428 -- verify that a row exists and also that the selection_name
1429 -- is defined properly
1430 while nullselections_c%found loop
1431 -- dbms_output.put_line('found null sel ' || l_currentDim || l_currentLine);
1432 l_dimValid := 'N';
1433 x_isvalid := 'N';
1434 if x_invalid_linemem_list is null or length(x_invalid_linemem_list) < 1950 then
1435 x_invalid_linemem_list := x_invalid_linemem_list ||','''
1436 || l_currentLine ||'''';
1437 end if;
1438
1439 fetch nullselections_c into l_currentLine,l_fetchedDim, l_inputSelection;
1440 end loop;
1441 -- close the cursor
1442 close nullselections_c;
1443
1444
1445 -- check that there are no line items which have null query objects
1446
1447 open nonnullselections_c(l_currentDim, l_timedim);
1448 fetch nonnullselections_c into l_inputSelection;
1449
1450
1451 while nonnullselections_c%found loop
1452
1453 -- get a line member that corresponds to this input selection.
1454 -- this line member will be used to limit the input selection valueset
1455
1456 open selection_member_c(l_currentDim,l_inputSelection);
1457 fetch selection_member_c into l_currentLine;
1458 close selection_member_c;
1459
1460 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name, 'cur line = '
1461 || l_currentLine||l_currentDim||l_inputSelection );
1462
1463
1464 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'cuminputvs ' ||
1465 substr(zpb_aw.interp('shw values('||
1466 l_dataAw|| l_cuminputvs||')'),1,200));
1467
1468
1469 zpb_aw.execute('lmt ' || l_dataAw ||l_lineDim || ' to ''' ||
1470 l_currentLine||'''');
1471
1472 -- verify that no parent-child comination exists on any
1473 -- output hierarchy
1474 --Bug#5673968, validate based on the hierarchies
1475 --for the current line member only
1476 open outputhierarchy_c(l_currentDim,l_currentLine);
1477 fetch outputhierarchy_c into l_hierarchy;
1478
1479 while outputhierarchy_c%found loop
1480
1481 zpb_aw.execute('lmt '|| l_dataAw || l_hierdim || ' to ''' || l_hierarchy || '''');
1482 /*dbms_output.put_line(substr('shw statlen('||l_dataAw||l_cuminputvs||
1483 ') eq statlen(lmt('||l_dataAw||l_cuminputvs
1484 || ' remove ancestors using '|| l_dataAw || l_parentRel ||'))',1,250));
1485 */
1486 if not zpb_aw.interpbool('shw statlen('||l_dataAw||l_cuminputvs||
1487 ') eq statlen(lmt('||l_dataAw||l_cuminputvs
1488 || ' remove ancestors using '|| l_dataAw || l_parentRel ||'))') OR
1489 zpb_aw.interpbool('shw statlen('||l_dataAw||l_cuminputvs||
1490 ') eq 0') then
1491
1492 -- get all the line members that use this input selection
1493 l_dimvalid := 'N';
1494 x_isvalid := 'N';
1495 open hiermember_c(l_currentDim,l_inputSelection, l_hierarchy);
1496 fetch hiermember_c into l_currentLine;
1497 while hiermember_c%found loop
1498 if x_invalid_linemem_list is null or length(x_invalid_linemem_list) < 1950 then
1499 x_invalid_linemem_list := x_invalid_linemem_list ||','''
1500 || l_currentLine ||'''';
1501 end if;
1502 fetch hiermember_c into l_currentLine;
1503 end loop;
1504 close hiermember_c;
1505
1506 end if;
1507 fetch outputhierarchy_c into l_hierarchy;
1508 end loop; -- loop over hierarchy
1509 close outputhierarchy_c;
1510
1511 fetch nonnullselections_c into l_inputSelection;
1512 end loop; -- loop over input selections
1513
1514 -- close the cursor
1515 close nonnullselections_c;
1516
1517 if l_dimValid = 'N' and l_alldims_invalid = 'N' then
1518 x_invalid_dim_list := x_invalid_dim_list ||','||l_currentDim;
1519 end if;
1520
1521 -- traverse the input dim list
1522 l_currpos := l_nextpos + 1;
1523 end loop; -- outer loop for dim list
1524
1525 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,'Returning :' || x_isvalid);
1526 -- remove the extra comma
1527 if x_isvalid = 'N' then
1528 x_invalid_dim_list := substr(x_invalid_dim_list,2,length(x_invalid_dim_list)-1);
1529 x_invalid_linemem_list := substr(x_invalid_linemem_list,2,length(x_invalid_linemem_list)-1);
1530 end if;
1531
1532 exception
1533 WHEN FND_API.G_EXC_ERROR THEN
1534 ROLLBACK TO validate_input_selections;
1535 x_return_status := FND_API.G_RET_STS_ERROR;
1536 FND_MSG_PUB.Count_And_Get(
1537 p_count => x_msg_count,
1538 p_data => x_msg_data
1539 );
1540 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1544 p_count => x_msg_count,
1541 ROLLBACK TO validate_input_selections;
1542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1543 FND_MSG_PUB.Count_And_Get(
1545 p_data => x_msg_data
1546 );
1547 WHEN OTHERS THEN
1548 ROLLBACK TO validate_input_selections;
1549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1550 x_isvalid := 'N';
1551 --dbms_output.put_line(to_char(sqlcode) || substr(sqlerrm,1,200));
1552 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
1553 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1554 FND_MSG_PUB.Add_Exc_Msg(
1555 G_PKG_NAME,
1556 l_api_name
1557 );
1558 END IF;
1559 FND_MSG_PUB.Count_And_Get(
1560 p_count => x_msg_count,
1561 p_data => x_msg_data
1562 );
1563
1564 end validate_input_selections;
1565
1566
1567 --------------------------------------------------------
1568 -- validate_solve_levels:
1569 -- Return Value(s): -1 if the input selections do not have matching output
1570 -- selections
1571 -- 0 if they are the same
1572 -- 1 if the output selections do not have matching input
1573 -- selections
1574 -- 2 if input and output selections have missing matches
1575 -- Example:
1576 -- Lets say the hierarchy is as below:
1577 -- 1
1578 -- / \
1579 -- 2 3
1580 -- /\ /\
1581 -- 4 5 6 7
1582 ----------------------------------------------
1583 --| Scenario | Return value |
1584 --|-------------------------------------------
1585
1586
1587 PROCEDURE validate_solve_levels(
1588 p_api_version IN NUMBER,
1589 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1590 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1591 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1592 x_return_status OUT NOCOPY VARCHAR2 ,
1593 x_msg_count OUT NOCOPY NUMBER,
1594 x_msg_data OUT NOCOPY VARCHAR2,
1595 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
1596 p_outputDims IN VARCHAR2,
1597 x_isvalid OUT NOCOPY VARCHAR2,
1598 x_invalid_linemem_list OUT NOCOPY VARCHAR2) IS
1599
1600 l_api_name CONSTANT VARCHAR2(30) := 'validate_solve_levels';
1601 l_api_version CONSTANT NUMBER := 1.0;
1602 l_dim zpb_solve_output_selections.dimension%type;
1603 l_currentLine zpb_solve_output_selections.member%type;
1604 l_hierarchy zpb_solve_output_selections.hierarchy%type;
1605 l_outputSelection zpb_solve_output_selections.selection_name%type;
1606 l_dataAw varchar2(100);
1607 l_dataAwQual varchar2(100);
1608 l_inp_level_found varchar2(1);
1609 l_input_valid varchar2(10);
1610 l_source_type number;
1611 l_outputvs varchar2(200);
1612 l_timedim varchar2(100);
1613 l_currentDim zpb_solve_output_selections.dimension%type;
1614 l_inputsel_bigger boolean;
1615 l_outputsel_bigger boolean;
1616 l_parentrel varchar2(100);
1617 l_hierdim varchar2(100);
1618 l_vs varchar2(100);
1619 l_length integer := 0;
1620 l_currpos integer := 1;
1621 l_nextpos integer := 1;
1622 l_cuminputvs varchar2(250);
1623 l_lineDim varchar2(100);
1624
1625 -- find all the non-null selections and evaluate them
1626 cursor nonnullselections_c(p_dim in varchar2, p_time_dim in varchar2) is
1627 select distinct o.selection_name, o.hierarchy
1628 from zpb_solve_output_selections o, zpb_solve_member_defs d,
1629 zpb_line_dimensionality l
1630 where o.member = d.member
1631 and o.dimension = p_time_dim
1632 and o.dimension = p_dim
1633 and o.analysis_cycle_id = d.analysis_cycle_id
1634 and l.dimension = o.dimension
1635 and l.member = o.member
1636 and l.analysis_cycle_id = o.analysis_cycle_id
1637 and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
1638 and ( nvl(l.force_input_flag,'N') = 'Y'
1639 OR nvl(l.sum_members_flag,'N') = 'N')
1640 and d.source_type in (1000,1100,1130)
1641 and o.analysis_cycle_id = p_analysis_cycle_id
1642 and nvl(o.selection_name,'DEFAULT') <> 'DEFAULT'
1643 AND NVL(o.match_input_flag, 'N') <> 'Y'
1644 union all
1645 select distinct o.selection_name, o.hierarchy
1646 from zpb_solve_output_selections o, zpb_solve_member_defs d,
1647 zpb_line_dimensionality l
1648 where o.member = d.member
1649 and o.dimension <> p_time_dim
1650 and o.dimension = p_dim
1651 and o.analysis_cycle_id = d.analysis_cycle_id
1652 and l.dimension = o.dimension
1653 and l.member = o.member
1654 and l.analysis_cycle_id = o.analysis_cycle_id
1655 and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
1656 and ( nvl(l.force_input_flag,'N') = 'Y'
1657 OR nvl(l.sum_members_flag,'N') = 'N')
1658 and d.source_type in (1000)
1659 and o.analysis_cycle_id = p_analysis_cycle_id
1660 and nvl(o.selection_name,'DEFAULT') <> 'DEFAULT'
1661 AND NVL(o.match_input_flag, 'N') <> 'Y'
1662 union all
1663 select distinct o.selection_name, o.hierarchy
1664 from zpb_solve_output_selections o, zpb_solve_member_defs d
1665 where o.member = d.member
1669 and d.source_type in (1100,1130)
1666 and o.dimension = p_time_dim
1667 and o.dimension = p_dim
1668 and o.analysis_cycle_id = d.analysis_cycle_id
1670 and o.analysis_cycle_id = p_analysis_cycle_id
1671 and nvl(o.selection_name,'DEFAULT') <> 'DEFAULT'
1672 AND NVL(o.match_input_flag, 'N') <> 'Y';
1673
1674 -- returns a member that uses an output selection
1675 cursor selection_member_c(p_dim in varchar2, l_selection_name in varchar2) is
1676 select o.member
1677 from zpb_solve_output_selections o, zpb_solve_member_defs d
1678 where o.selection_name = l_selection_name
1679 and o.member = d.member
1680 and o.dimension = p_dim
1681 and o.analysis_cycle_id = d.analysis_cycle_id
1682 and d.source_type in (1000,1100,1130)
1683 and o.analysis_cycle_id = p_analysis_cycle_id
1684 AND NVL(o.match_input_flag, 'N') <> 'Y';
1685
1686 cursor hiermember_c(p_dim in varchar2, l_selection_name in varchar2,
1687 l_hierarchy in varchar2) is
1688 select o.member
1689 from zpb_solve_output_selections o
1690 where o.dimension = p_dim
1691 and o.analysis_cycle_id = p_analysis_cycle_id
1692 and o.selection_name = l_selection_name
1693 and o.hierarchy = l_hierarchy
1694 AND NVL(o.match_input_flag, 'N') <> 'Y';
1695 begin
1696 -- Standard Start of API savepoint
1697 SAVEPOINT validate_solve_levels;
1698 -- Standard call to check for call compatibility.
1699 IF NOT FND_API.Compatible_API_Call( l_api_version,
1700 p_api_version,
1701 l_api_name,
1702 G_PKG_NAME)
1703 THEN
1704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1705 END IF;
1706
1707 -- Initialize message list if p_init_msg_list is set to TRUE.
1708 IF FND_API.to_Boolean(p_init_msg_list) THEN
1709 FND_MSG_PUB.initialize;
1710 END IF;
1711
1712 -- Initialize API return status to success
1713 x_return_status := FND_API.G_RET_STS_SUCCESS;
1714 x_isvalid := 'Y';
1715
1716 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name,'validating solve levels');
1717
1718 l_timedim := zpb_aw.interp('shw dl.gettimedim');
1719
1720 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw || '!';
1721 zpb_aw.execute('lmt ' || l_dataAw ||'instance to '''|| p_analysis_cycle_id ||'''');
1722 l_lineDim := zpb_aw.interp('shw CM.GETLINEDIM(''SHARED'')');
1723 zpb_aw.execute('lmt ' || l_dataAw ||l_lineDim || ' to values(sv.ln.dim)');
1724
1725
1726 -- initialize for traversing the list of dimensions
1727 l_length := nvl(length(p_outputDims),0);
1728
1729 -- bail with success if no input dimensions
1730 if l_length < 2 then
1731 return;
1732 end if;
1733
1734 l_currpos := 1;
1735 l_nextpos := 1;
1736 while l_currpos < l_length loop
1737 l_nextpos := instr(p_outputDims,',', l_currpos);
1738 --dbms_output.put_line('getting cur dim');
1739 l_currentDim := substr(p_outputDims,l_currpos,l_nextpos - l_currpos);
1740 --dbms_output.put_line(l_currentDim);
1741 l_hierdim := zpb_aw.interp('shw obj(prp ''HIERDIM'' ' ||''''
1742 ||l_dataAw ||l_currentdim ||''')');
1743 l_cuminputvs := zpb_aw.interp('shw obj(prp ''DINPUTVS'' ' ||''''
1744 ||l_dataAw||l_currentdim ||''')')||'(' ||
1745 l_dataAw ||l_lineDim || ' ' || l_dataAw || 'DINPUTVSPOINTER'
1746 || '(' || l_dataAw || 'UNIVDIM ''' || l_currentDim
1747 || '''))' ;
1748 l_parentRel := zpb_aw.interp('shw obj(prp ''PARENTREL'' ' ||''''
1749 ||l_dataAw ||l_currentdim ||''')');
1750 l_outputvs := zpb_aw.interp('shw obj(prp ''HOUTPUTVS'' ' ||''''
1751 ||l_dataAw ||l_currentdim ||''')') ||'(' ||
1752 l_dataAw ||l_lineDim || ' ' || l_dataAw || 'HOUTPUTVSPOINTER.'
1753 || zpb_aw.interp('shw obj(prp ''NAMEFRAGMENT'' ' ||''''
1754 ||l_dataAw ||l_currentdim ||''')')
1755 || ')' ;
1756 -- check that there are no line items which have null query objects
1757
1758 open nonnullselections_c(l_currentDim, l_timedim);
1759 fetch nonnullselections_c into l_outputSelection, l_hierarchy;
1760
1761 while nonnullselections_c%found loop
1762 -- get a line member that corresponds to this input selection.
1763 -- this line member will be used to limit the input selection valueset
1764
1765 open selection_member_c(l_currentDim,l_outputSelection);
1766 fetch selection_member_c into l_currentLine;
1767 close selection_member_c;
1768
1769 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name, 'cur line = '
1770 || l_currentLine||l_currentDim||l_outputSelection );
1771
1772 zpb_aw.execute('lmt ' || l_dataAw ||l_lineDim || ' to ''' ||
1773 l_currentLine||'''');
1774
1775 -- verify that no parent-child comination exists on any
1776 -- output hierarchy
1777 zpb_aw.execute('lmt '|| l_dataAw || l_hierdim || ' to ''' || l_hierarchy || '''');
1778 /*dbms_output.put_line('shw statlen(lmt('||l_dataAw||l_outputvs
1779 || ' remove lmt('||l_dataAw||l_cuminputvs ||
1780 ' add descendants using ' || l_dataAw ||
1781 l_parentRel || '))) ne 0');
1782 */ -- check 1:
1786 --
1783 -- check that there is no output selection which is not being
1784 -- "fed" by an input selection (by being in the i/s or being
1785 -- a descendent of an i/s
1787 -- check 2:
1788 -- there is at least one i/s member who is feeding an o/s
1789 -- member by being its ancestor or equal to it
1790 if zpb_aw.interpbool('shw statlen(lmt('||l_dataAw||l_outputvs
1791 || ' remove lmt('||l_dataAw||l_cuminputvs ||
1792 ' add descendants using ' || l_dataAw ||
1793 l_parentRel || '))) ne 0')
1794 then
1795 -- get all the line members that use this output selection
1796 x_isvalid := 'N';
1797 open hiermember_c(l_currentDim,l_outputSelection, l_hierarchy);
1798 fetch hiermember_c into l_currentLine;
1799 while hiermember_c%found loop
1800 if x_invalid_linemem_list is null or length(x_invalid_linemem_list) < 1950 then
1801 x_invalid_linemem_list := x_invalid_linemem_list ||','''
1802 || l_currentLine ||'''';
1803 end if;
1804 fetch hiermember_c into l_currentLine;
1805 end loop;
1806 close hiermember_c;
1807
1808 end if;
1809
1810 fetch nonnullselections_c into l_outputSelection, l_hierarchy;
1811 end loop; -- loop over output selections
1812
1813 -- close the cursor
1814 close nonnullselections_c;
1815
1816 -- traverse the input dim list
1817 l_currpos := l_nextpos + 1;
1818 end loop; -- outer loop for dim list
1819
1820 -- remove the extra comma
1821 if x_isvalid = 'N' then
1822 x_invalid_linemem_list := substr(x_invalid_linemem_list,2,length(x_invalid_linemem_list)-1);
1823 end if;
1824
1825 exception
1826 WHEN FND_API.G_EXC_ERROR THEN
1827 ROLLBACK TO validate_solve_levels;
1828 x_return_status := FND_API.G_RET_STS_ERROR;
1829 FND_MSG_PUB.Count_And_Get(
1830 p_count => x_msg_count,
1831 p_data => x_msg_data
1832 );
1833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1834 ROLLBACK TO validate_solve_levels;
1835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1836 FND_MSG_PUB.Count_And_Get(
1837 p_count => x_msg_count,
1838 p_data => x_msg_data
1839 );
1840 WHEN OTHERS THEN
1841 ROLLBACK TO validate_solve_levels;
1842 --dbms_output.put_line(to_char(sqlcode) || substr(sqlerrm,1,190));
1843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1844 x_isvalid := 'N';
1845 --dbms_output.put_line(to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
1846 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
1847 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1848 FND_MSG_PUB.Add_Exc_Msg(
1849 G_PKG_NAME,
1850 l_api_name
1851 );
1852 END IF;
1853 FND_MSG_PUB.Count_And_Get(
1854 p_count => x_msg_count,
1855 p_data => x_msg_data
1856 );
1857
1858 end validate_solve_levels;
1859
1860 PROCEDURE val_template_le_model(
1861 p_api_version IN NUMBER,
1862 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1863 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1864 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1865 x_return_status OUT NOCOPY VARCHAR2 ,
1866 x_msg_count OUT NOCOPY NUMBER,
1867 x_msg_data OUT NOCOPY VARCHAR2,
1868 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
1869 x_isvalid OUT NOCOPY VARCHAR2,
1870 x_invalid_tasks_list OUT NOCOPY VARCHAR2) IS
1871
1872 l_api_name CONSTANT VARCHAR2(30) := 'val_template_le_model';
1873 l_api_version CONSTANT NUMBER := 1.0;
1874 l_task_id zpb_analysis_cycle_tasks.task_id%type;
1875 l_line_dim zpb_cycle_model_dimensions.dimension_name%type;
1876 l_template_query zpb_cycle_model_dimensions.query_object_path%type;
1877 l_template_path zpb_cycle_model_dimensions.query_object_path%type;
1878 l_model_query zpb_cycle_model_dimensions.query_object_path%type;
1879 l_vs varchar2(100);
1880 l_pushed_solve varchar2(2) := 'N';
1881 l_lines_compare integer;
1882 l_task_name zpb_task_parameters.name%type;
1883 l_dataAw varchar2(100);
1884 l_task_exists varchar2(1);
1885 l_path_exists varchar2(1);
1886 l_name_exists varchar2(1);
1887
1888 cursor query_model is
1889 select query_object_path|| '/' || query_object_name
1890 from zpb_cycle_model_dimensions
1891 where dimension_name = l_line_dim
1892 and analysis_cycle_id = p_analysis_cycle_id;
1893
1894 cursor generate_task is
1895 select task_id, task_name
1896 from zpb_analysis_cycle_tasks
1897 where analysis_cycle_id = p_analysis_cycle_id
1898 and wf_process_name = 'GENERATE_TEMPLATE';
1899
1900 cursor generate_worksheet_query is
1901 select name,value
1902 from zpb_task_parameters
1903 where task_id = l_task_id
1904 and name in ('TEMPLATE_DATAENTRY_OBJ_PATH','TEMPLATE_DATAENTRY_OBJ_NAME');
1905
1906 begin
1907
1908 -- Standard Start of API savepoint
1909 SAVEPOINT val_gentemp_le_model;
1910 -- Standard call to check for call compatibility.
1914 G_PKG_NAME)
1911 IF NOT FND_API.Compatible_API_Call( l_api_version,
1912 p_api_version,
1913 l_api_name,
1915 THEN
1916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1917 END IF;
1918
1919 -- Initialize message list if p_init_msg_list is set to TRUE.
1920 IF FND_API.to_Boolean(p_init_msg_list) THEN
1921 FND_MSG_PUB.initialize;
1922 END IF;
1923
1924 -- Initialize API return status to success
1925 x_return_status := FND_API.G_RET_STS_SUCCESS;
1926
1927 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name, 'Validating if template lines exist in model ' || to_char(p_analysis_cycle_id) || '.');
1928 x_isvalid := 'Y';
1929 -- initialize_solve_object(p_analysis_cycle_id);
1930 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw;
1931
1932 l_line_dim := zpb_aw.interp('shw CM.GETLINEDIM('''||l_dataAw||''')');
1933
1934 open query_model;
1935 fetch query_model into l_model_query;
1936
1937 -- cycle not defined properly yet. so return without doing any validation
1938 -- not information will be provided in the validation page
1939 if query_model%notfound then
1940 x_isvalid := 'Y';
1941 return;
1942 end if;
1943
1944 close query_model;
1945
1946 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'query is :' ||l_model_query);
1947
1948 l_task_exists := 'n';
1949 for each in generate_task loop
1950 l_task_exists := 'y';
1951 l_path_exists := 'n';
1952 l_name_exists := 'n';
1953 l_task_id := each.task_id;
1954 l_task_name := each.task_name;
1955 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,to_char(l_task_id));
1956
1957 for each in generate_worksheet_query loop
1958 if (each.name = 'TEMPLATE_DATAENTRY_OBJ_PATH') then
1959 l_path_exists := 'y';
1960 l_template_path := each.value;
1961 end if;
1962 if (each.name = 'TEMPLATE_DATAENTRY_OBJ_NAME') then
1963 l_name_exists := 'y';
1964 l_template_query := each.value;
1965 end if;
1966 end loop;
1967
1968 -- if any query is not properly defined then donot perform any validation
1969 -- and return
1970 if l_path_exists <> 'y' or l_name_exists <> 'y' then
1971 x_isvalid := 'Y';
1972 return;
1973 end if;
1974
1975 l_template_query := l_template_path ||'/' || l_template_query;
1976 -- l_query_name := 'System Private/Controller/AC11736/MODEL_QUERY_5894';
1977
1978 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'query is :' ||l_template_query);
1979
1980 l_lines_compare := compare_queries(l_dataAw,l_model_query, l_template_query,l_line_dim);
1981 if l_lines_compare = 3 OR l_lines_compare = 2 then
1982 x_isvalid := 'N';
1983 x_invalid_tasks_list := x_invalid_tasks_list || ',' ||l_task_name;
1984 end if;
1985
1986 end loop;
1987 -- if task not defined properly then return success
1988 if l_task_exists <> 'y' then
1989 x_isvalid := 'Y';
1990 end if;
1991
1992 -- remove extra comma from the front
1993 if x_isvalid = 'N' and length(x_invalid_tasks_list) > 0 then
1994 x_invalid_tasks_list := substr(x_invalid_tasks_list,2);
1995 end if;
1996
1997 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'invalid tasks:'|| x_invalid_tasks_list);
1998 return;
1999
2000
2001 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name, ' Validation completed for Analysis Cycle' || p_analysis_cycle_id || '.');
2002
2003 exception
2004 WHEN FND_API.G_EXC_ERROR THEN
2005 ROLLBACK TO val_gentemp_le_model;
2006 x_return_status := FND_API.G_RET_STS_ERROR;
2007 FND_MSG_PUB.Count_And_Get(
2008 p_count => x_msg_count,
2009 p_data => x_msg_data
2010 );
2011 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2012 ROLLBACK TO val_gentemp_le_model;
2013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2014 FND_MSG_PUB.Count_And_Get(
2015 p_count => x_msg_count,
2016 p_data => x_msg_data
2017 );
2018 WHEN OTHERS THEN
2019 ROLLBACK TO val_gentemp_le_model;
2020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2021 x_isvalid := 'N';
2022 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2023 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2024 FND_MSG_PUB.Add_Exc_Msg(
2025 G_PKG_NAME,
2026 l_api_name
2027 );
2028 END IF;
2029 FND_MSG_PUB.Count_And_Get(
2030 p_count => x_msg_count,
2031 p_data => x_msg_data
2032 );
2033
2034 end val_template_le_model;
2035
2036
2037 -- this procedure returns two possible output values
2038 -- 'Y': The union of Line Members of ALL Generate Template Tasks is equal to the
2039 -- NON_INITIALIZED inputted line members of Solve
2040 -- 'N': The union of Line Members of ALL Generate Template Tasks is different from the
2041 -- NON_INITIALIZED inputted line members of Solve
2042 procedure val_solveinp_eq_gentemp(
2043 p_api_version IN NUMBER,
2044 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2045 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2046 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2050 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
2047 x_return_status OUT NOCOPY VARCHAR2 ,
2048 x_msg_count OUT NOCOPY NUMBER,
2049 x_msg_data OUT NOCOPY VARCHAR2,
2051 x_isvalid OUT NOCOPY VARCHAR2,
2052 x_dim_members OUT NOCOPY VARCHAR2) IS
2053
2054 l_api_name CONSTANT VARCHAR2(30) := 'val_solveinp_eq_gentemp';
2055 l_api_version CONSTANT NUMBER := 1.0;
2056 l_line_dim zpb_cycle_model_dimensions.dimension_name%type;
2057 l_query_name zpb_cycle_model_dimensions.query_object_path%type;
2058 l_query_path zpb_cycle_model_dimensions.query_object_path%type;
2059 l_task_id zpb_analysis_cycle_tasks.task_id%type;
2060 l_vs varchar2(100);
2061 l_dataAw varchar2(100);
2062 l_task_exists varchar2(1);
2063 l_path_exists varchar2(1);
2064 l_name_exists varchar2(1);
2065 l_selection_type varchar2(30);
2066 l_pushed_solve varchar2(2) := 'N';
2067
2068 cursor generate_task is
2069 select task_id
2070 from zpb_analysis_cycle_tasks
2071 where analysis_cycle_id = p_analysis_cycle_id
2072 and wf_process_name = 'GENERATE_TEMPLATE';
2073
2074 cursor load_data_query is
2075 select name,value
2076 from zpb_task_parameters
2077 where task_id = l_task_id
2078 and name in ('TEMPLATE_DATAENTRY_OBJ_PATH','TEMPLATE_DATAENTRY_OBJ_NAME');
2079
2080 begin
2081 -- Standard Start of API savepoint
2082 SAVEPOINT val_gentemp_le_model;
2083 -- Standard call to check for call compatibility.
2084 IF NOT FND_API.Compatible_API_Call( l_api_version,
2085 p_api_version,
2086 l_api_name,
2087 G_PKG_NAME)
2088 THEN
2089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2090 END IF;
2091
2092 -- Initialize message list if p_init_msg_list is set to TRUE.
2093 IF FND_API.to_Boolean(p_init_msg_list) THEN
2094 FND_MSG_PUB.initialize;
2095 END IF;
2096
2097 -- Initialize API return status to success
2098 x_return_status := FND_API.G_RET_STS_SUCCESS;
2099
2100 -- initialize_solve_object(p_analysis_cycle_id);
2101 zpb_aw.execute('push SV.LN.DIM ');
2102 l_pushed_solve := 'Y';
2103 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw;
2104
2105 l_line_dim := zpb_aw.interp('shw CM.GETLINEDIM('''||l_dataAw||''')');
2106
2107 -- limit dimension to non-initialized and input
2108 zpb_aw.execute('lmt SV.LN.DIM keep SV.DEF.VAR(SV.DEF.PROP.DIM ''TYPE'') eq ''INPUT'' and nafill(SV.DEF.VAR(SV.DEF.PROP.DIM ''DATA_SOURCE''),'''') eq ''''');
2109
2110
2111 -- get the valuseset name
2112 l_vs := '&' || 'joinchars('''||l_dataAw||'!'' obj(prp ''LASTQUERYVS'' '||''''
2113 ||l_dataAw||'!' ||l_line_dim ||'''))';
2114
2115 l_task_exists := 'n';
2116 for each in generate_task loop
2117 l_task_exists := 'y';
2118 l_path_exists := 'n';
2119 l_name_exists := 'n';
2120
2121 l_task_id := each.task_id;
2122 for each in load_data_query loop
2123 if (each.name = 'TEMPLATE_DATAENTRY_OBJ_PATH') then
2124 l_path_exists := 'y';
2125 l_query_path := each.value;
2126 end if;
2127 if (each.name = 'TEMPLATE_DATAENTRY_OBJ_NAME') then
2128 l_name_exists := 'y';
2129 l_query_name := each.value;
2130 end if;
2131 end loop;
2132
2133 -- if any query is not properly defined then donot perform any validation
2134 -- and return. Allso return if all line items are being selected
2135 if l_path_exists <> 'y' or l_name_exists <> 'y' then
2136 x_isvalid := 'Y';
2137 zpb_aw.execute('pop SV.LN.DIM ');
2138 return;
2139 end if;
2140
2141 l_query_name := l_query_path ||'/' || l_query_name;
2142 -- l_query_name := 'System Private/Controller/AC11736/MODEL_QUERY_5894';
2143
2144 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,'query is :' ||l_query_name);
2145 zpb_aw_status.get_status(l_dataAw,l_query_name);
2146
2147 --zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr values('||l_vs||')'),1,254) );
2148
2149 zpb_aw.execute(' lmt SV.LN.DIM keep filterlines(values(sv.ln.dim) if inlist(values('||l_vs||') value) then na else value)');
2150 end loop;
2151
2152
2153 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(zpb_aw.interp('rpr values(SV.LN.DIM)'),1,255) );
2154
2155 if zpb_aw.interpbool('shw statlen(SV.LN.DIM) gt 0 ') then
2156 x_isvalid := 'N';
2157 x_dim_members := zpb_aw.interp('shw joinchars(joincols(filterlines(values(SV.LN.DIM) joinchars(''\'''' value ''\'''')) '',''))');
2158 if length(x_dim_members) > 0 then
2159 x_dim_members := substr(x_dim_members,1,length(x_dim_members)-1);
2160 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,substr(x_dim_members,1,254));
2161 end if;
2162 else
2163 x_isvalid := 'Y';
2164 end if;
2165
2166 zpb_aw.execute('pop SV.LN.DIM ');
2167 return;
2168
2169 exception
2170 WHEN FND_API.G_EXC_ERROR THEN
2171 ROLLBACK TO val_gentemp_le_model;
2172 x_return_status := FND_API.G_RET_STS_ERROR;
2173 FND_MSG_PUB.Count_And_Get(
2174 p_count => x_msg_count,
2175 p_data => x_msg_data
2176 );
2177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2178 ROLLBACK TO val_gentemp_le_model;
2182 p_data => x_msg_data
2179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2180 FND_MSG_PUB.Count_And_Get(
2181 p_count => x_msg_count,
2183 );
2184 WHEN OTHERS THEN
2185 ROLLBACK TO val_gentemp_le_model;
2186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2187 x_isvalid := 'N';
2188 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2189 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2190 FND_MSG_PUB.Add_Exc_Msg(
2191 G_PKG_NAME,
2192 l_api_name
2193 );
2194 END IF;
2195 FND_MSG_PUB.Count_And_Get(
2196 p_count => x_msg_count,
2197 p_data => x_msg_data
2198 );
2199
2200 end val_solveinp_eq_gentemp;
2201
2202
2203 -- this procedure validates the solve input and output levels .
2204 -- it ensures that they share a hierarchy and the input level
2205 -- is not lower than the output level
2206 -- it returns 2 output variables
2207 -- x_isvalid: this variable is a boolean and can contain either
2208 -- 'Y' or 'N'
2209 -- x_invalid_linemem_list: this variable will contain a list
2210 -- of invalid line member ids if the x_isvalid
2211 -- is equal to 'N' i.e validation failed
2212 PROCEDURE val_solve_input_higher_levels(
2213 p_api_version IN NUMBER,
2214 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2215 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2216 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2217 x_return_status OUT NOCOPY VARCHAR2 ,
2218 x_msg_count OUT NOCOPY NUMBER,
2219 x_msg_data OUT NOCOPY VARCHAR2,
2220 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
2221 x_isvalid OUT NOCOPY VARCHAR2,
2222 x_invalid_linemem_list OUT NOCOPY VARCHAR2) IS
2223
2224 l_api_name CONSTANT VARCHAR2(30) := 'val_solve_input_higher_levels';
2225 l_api_version CONSTANT NUMBER := 1.0;
2226 l_dim zpb_solve_output_selections.dimension%type;
2227 l_line_mem zpb_solve_output_selections.member%type;
2228 l_hierarchy zpb_solve_output_selections.hierarchy%type;
2229 l_output_selection_name zpb_solve_output_selections.selection_name%type;
2230 l_input_selection_name zpb_solve_input_selections.selection_name%type;
2231 l_dataAw varchar2(100);
2232 l_inp_level_found varchar2(1);
2233 l_common_hier varchar2(10);
2234 l_input_valid varchar2(10);
2235 l_timedim varchar2(100);
2236 l_source_type NUMBER;
2237
2238 cursor output_info is
2239 select o.member, o.dimension, o.hierarchy, o.selection_name,
2240 m.source_type
2241 from zpb_solve_output_selections o, zpb_solve_member_defs m
2242 where m.analysis_cycle_id = p_analysis_cycle_id
2243 and m.analysis_cycle_id = o.analysis_cycle_id
2244 and m.member = o.member
2245 and m.source_type <> 1200;
2246
2247 cursor input_info(p_line_mem in varchar2, p_dim in varchar2) is
2248 select selection_name
2249 from zpb_solve_input_selections
2250 where analysis_cycle_id = p_analysis_cycle_id
2251 and member = p_line_mem
2252 and dimension = p_dim;
2253 begin
2254
2255 -- Standard Start of API savepoint
2256 SAVEPOINT val_solve_input_higher_levels;
2257 -- Standard call to check for call compatibility.
2258 IF NOT FND_API.Compatible_API_Call( l_api_version,
2259 p_api_version,
2260 l_api_name,
2261 G_PKG_NAME)
2262 THEN
2263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2264 END IF;
2265
2266 -- Initialize message list if p_init_msg_list is set to TRUE.
2267 IF FND_API.to_Boolean(p_init_msg_list) THEN
2268 FND_MSG_PUB.initialize;
2269 END IF;
2270
2271 -- Initialize API return status to success
2272 x_return_status := FND_API.G_RET_STS_SUCCESS;
2273
2274
2275 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name,'validating solve levels');
2276 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw(fnd_global.user_id);
2277 x_isvalid := 'Y';
2278 l_timedim := zpb_aw.interp('shw dl.gettimedim');
2279
2280 open output_info;
2281
2282 -- run the validation for every output level row
2283 loop
2284 fetch output_info into l_line_mem, l_dim, l_hierarchy, l_output_selection_name,
2285 l_source_type;
2286 if output_info%notfound then
2287 exit;
2288 end if;
2289
2290 l_inp_level_found := 'n';
2291 l_input_valid := 'y';
2292
2293 open input_info(l_line_mem,l_dim);
2294 fetch input_info into l_input_selection_name;
2295
2296 while input_info%found loop
2297 l_inp_level_found := 'y';
2298 l_common_hier := zpb_aw.interp('shw cm.cmp.level('''||l_dataAw||
2299 ''','''||l_input_selection_name ||
2300 ''','''||l_output_selection_name ||
2301 ''','''||l_hierarchy ||
2302 ''','''||l_dim||''')');
2303 if l_common_hier = '2' OR l_common_hier = '1' then
2304 l_input_valid := 'n';
2305 x_isvalid := 'N';
2306 end if;
2310 close input_info;
2307 fetch input_info into l_input_selection_name;
2308 end loop;
2309
2311
2312 -- if the input level row was not found then
2313 -- we can now only return failure if it was:
2314 -- a loaded line OR the dim was CAL_PERIODS
2315 --
2316 -- No change in behavior if the selection_name was invalid
2317 if (l_inp_level_found = 'n' and (l_source_type <> 1100 or
2318 l_dim = l_timedim)) OR l_input_valid = 'n' then
2319 x_isvalid := 'N';
2320 if x_invalid_linemem_list is null or length(x_invalid_linemem_list) < 1950 then
2321 x_invalid_linemem_list := '''' || l_line_mem || ''''
2322 ||',' || x_invalid_linemem_list;
2323 end if;
2324 end if;
2325 end loop;
2326 close output_info;
2327
2328 -- remove the trailing comma
2329 if x_isvalid = 'N' then
2330 x_invalid_linemem_list := substr(x_invalid_linemem_list,1,
2331 length(x_invalid_linemem_list)-1);
2332 end if;
2333
2334 exception
2335 WHEN FND_API.G_EXC_ERROR THEN
2336 ROLLBACK TO val_solve_input_higher_levels;
2337 x_return_status := FND_API.G_RET_STS_ERROR;
2338 FND_MSG_PUB.Count_And_Get(
2339 p_count => x_msg_count,
2340 p_data => x_msg_data
2341 );
2342 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2343 ROLLBACK TO val_solve_input_higher_levels;
2344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2345 FND_MSG_PUB.Count_And_Get(
2346 p_count => x_msg_count,
2347 p_data => x_msg_data
2348 );
2349 WHEN OTHERS THEN
2350 ROLLBACK TO val_solve_input_higher_levels;
2351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2352 x_isvalid := 'N';
2353 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2354 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2355 FND_MSG_PUB.Add_Exc_Msg(
2356 G_PKG_NAME,
2357 l_api_name
2358 );
2359 END IF;
2360 FND_MSG_PUB.Count_And_Get(
2361 p_count => x_msg_count,
2362 p_data => x_msg_data
2363 );
2364
2365 end val_solve_input_higher_levels;
2366
2367 PROCEDURE validate_output_selections(
2368 p_api_version IN NUMBER,
2369 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2370 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2371 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2372 x_return_status OUT NOCOPY VARCHAR2 ,
2373 x_msg_count OUT NOCOPY NUMBER,
2374 x_msg_data OUT NOCOPY VARCHAR2,
2375 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
2376 p_outputDims IN VARCHAR2,
2377 x_isvalid OUT NOCOPY VARCHAR2,
2378 x_invalid_dim_list OUT NOCOPY VARCHAR2,
2379 x_invalid_linemem_list OUT NOCOPY VARCHAR2) IS
2380
2381 l_api_name CONSTANT VARCHAR2(30) := 'validate_output_selections';
2382 l_api_version CONSTANT NUMBER := 1.0;
2383 l_dataAw VARCHAR2(4000);
2384 l_currentDim zpb_solve_output_selections.dimension%type ;
2385 l_fetchedDim zpb_solve_output_selections.dimension%type;
2386 l_outputSelection zpb_solve_output_selections.selection_name%type;
2387 l_currentLine zpb_solve_output_selections.member%type;
2388 l_dimCount integer;
2389 i integer := 1;
2390 l_currpos integer := 1;
2391 l_nextpos integer := 1;
2392 l_length integer := 0;
2393 l_dimValid varchar2(1) := 'Y';
2394 l_alldims_invalid varchar2(1);
2395 l_hierdim varchar(50);
2396 l_cuminputvs varchar2(250);
2397 l_outputvs varchar2(250);
2398 l_hierarchy varchar2(50);
2399 l_timedim varchar2(50);
2400 l_parentRel varchar2(50);
2401 l_lineDim varchar2(100);
2402
2403 cursor member_c is
2404 select member
2405 from zpb_solve_member_defs
2406 where analysis_cycle_id = p_analysis_cycle_id
2407 and source_type in (1000,1100,1130)
2408 and member not in (select member
2409 from zpb_solve_output_selections
2410 where analysis_cycle_id = p_analysis_cycle_id);
2411
2412 -- find all the null selections
2413 cursor nullselections_c (p_dim in varchar2, p_time_dim in varchar2) is
2414 select o.member,o.dimension, o.selection_name
2415 from zpb_solve_output_selections o, zpb_solve_member_defs d,
2416 zpb_line_dimensionality l
2417 where d.member = o.member
2418 and d.analysis_cycle_id = o.analysis_cycle_id
2419 and d.analysis_cycle_id = p_analysis_cycle_id
2420 and l.dimension = o.dimension
2421 and l.member = o.member
2422 and l.analysis_cycle_id = o.analysis_cycle_id
2423 and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
2424 and ( nvl(l.force_input_flag,'N') = 'Y'
2425 OR nvl(l.sum_members_flag,'N') = 'N')
2426 and d.source_type in (1000,1100,1130)
2427 and o.dimension = p_time_dim
2428 and o.dimension = p_dim
2429 and o.selection_name is null
2430 AND NVL(o.match_input_flag, 'N') <> 'Y'
2431 union all
2432 select o.member, o.dimension,o.selection_name
2433 from zpb_solve_output_selections o, zpb_solve_member_defs d,
2434 zpb_line_dimensionality l
2435 where d.member = o.member
2436 and d.analysis_cycle_id = o.analysis_cycle_id
2440 and l.analysis_cycle_id = o.analysis_cycle_id
2437 and d.analysis_cycle_id = p_analysis_cycle_id
2438 and l.dimension = o.dimension
2439 and l.member = o.member
2441 and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
2442 and ( nvl(l.force_input_flag,'N') = 'Y'
2443 OR nvl(l.sum_members_flag,'N') = 'N')
2444 and d.source_type in (1000)
2445 and o.dimension = p_dim
2446 and o.dimension <> p_time_dim
2447 and o.selection_name is null
2448 AND NVL(o.match_input_flag, 'N') <> 'Y'
2449 union all
2450 select o.member, o.dimension,o.selection_name
2451 from zpb_solve_output_selections o, zpb_solve_member_defs d
2452 where d.member = o.member
2453 and d.analysis_cycle_id = o.analysis_cycle_id
2454 and d.analysis_cycle_id = p_analysis_cycle_id
2455 and d.source_type in (1100,1130)
2456 and o.dimension = p_dim
2457 and o.dimension <> p_time_dim
2458 and o.selection_name is null
2459 AND NVL(o.match_input_flag, 'N') <> 'Y';
2460
2461 -- find all the non-null selections and evaluate them
2462 cursor nonnullselections_c(p_dim in varchar2, p_time_dim in varchar2) is
2463 select distinct o.selection_name, o.hierarchy
2464 from zpb_solve_output_selections o, zpb_solve_member_defs d,
2465 zpb_line_dimensionality l
2466 where o.member = d.member
2467 and o.dimension = p_time_dim
2468 and o.dimension = p_dim
2469 and o.analysis_cycle_id = d.analysis_cycle_id
2470 and l.dimension = o.dimension
2471 and l.member = o.member
2472 and l.analysis_cycle_id = o.analysis_cycle_id
2473 and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
2474 and ( nvl(l.force_input_flag,'N') = 'Y'
2475 OR nvl(l.sum_members_flag,'N') = 'N')
2476 and d.source_type in (1000,1100,1130)
2477 and o.analysis_cycle_id = p_analysis_cycle_id
2478 and o.selection_name is not null
2479 AND NVL(o.match_input_flag, 'N') <> 'Y'
2480 union all
2481 select distinct o.selection_name, o.hierarchy
2482 from zpb_solve_output_selections o, zpb_solve_member_defs d,
2483 zpb_line_dimensionality l
2484 where o.member = d.member
2485 and o.dimension <> p_time_dim
2486 and o.dimension = p_dim
2487 and o.analysis_cycle_id = d.analysis_cycle_id
2488 and o.dimension = l.dimension
2489 and o.member = l.member
2490 and o.analysis_cycle_id = l.analysis_cycle_id
2491 and nvl(l.exclude_from_solve_flag,'N') <> 'Y'
2492 and ( nvl(l.force_input_flag,'N') = 'Y'
2493 OR nvl(l.sum_members_flag,'N') = 'N')
2494 and d.source_type in (1000)
2495 and o.analysis_cycle_id = p_analysis_cycle_id
2496 and o.selection_name is not null
2497 AND NVL(o.match_input_flag, 'N') <> 'Y'
2498 union all
2499 select distinct o.selection_name, o.hierarchy
2500 from zpb_solve_output_selections o, zpb_solve_member_defs d
2501 where o.member = d.member
2502 and o.dimension <> p_time_dim
2503 and o.dimension = p_dim
2504 and o.analysis_cycle_id = d.analysis_cycle_id
2505 and d.source_type in (1100,1130)
2506 and o.analysis_cycle_id = p_analysis_cycle_id
2507 and o.selection_name is not null
2508 AND NVL(o.match_input_flag, 'N') <> 'Y';
2509
2510 -- returns a member that uses an output selection
2511 cursor selection_member_c(p_dim in varchar2, l_selection_name in varchar2) is select a.member
2512 from zpb_solve_output_selections a, zpb_line_dimensionality b,
2513 zpb_solve_member_defs c
2514 where a.member = b.member
2515 AND a.dimension = b.dimension
2516 AND a.member = c.member
2517 AND a.analysis_cycle_id = c.analysis_cycle_id
2518 AND a.analysis_cycle_id = b.analysis_cycle_id
2519 AND b.analysis_cycle_id = p_analysis_cycle_id
2520 AND c.source_type = 1000
2521 AND b.exclude_from_solve_flag = 'N'
2522 AND selection_name = l_selection_name
2523 AND a.dimension= p_dim
2524 AND NVL(a.match_input_flag, 'N') <> 'Y'
2525 union
2526 select a.member
2527 from zpb_solve_output_selections a,
2528 zpb_solve_member_defs b
2529 where a.member = b.member
2530 AND a.analysis_cycle_id = b.analysis_cycle_id
2531 AND b.analysis_cycle_id = p_analysis_cycle_id
2532 AND b.source_type <> 1000
2533 AND selection_name = l_selection_name
2534 AND a.dimension= p_dim
2535 AND NVL(a.match_input_flag, 'N') <> 'Y';
2536
2537
2538 cursor hiermember_c(p_dim in varchar2, l_selection_name in varchar2,
2539 l_hierarchy in varchar2) is
2540 select o.member
2541 from zpb_solve_output_selections o
2542 where o.dimension = p_dim
2543 and o.analysis_cycle_id = p_analysis_cycle_id
2544 and o.selection_name = l_selection_name
2545 and o.hierarchy = l_hierarchy
2546 AND NVL(o.match_input_flag, 'N') <> 'Y';
2547
2548
2549 begin
2550 -- Standard Start of API savepoint
2551 SAVEPOINT validate_output_selections;
2552 -- Standard call to check for call compatibility.
2553 IF NOT FND_API.Compatible_API_Call( l_api_version,
2554 p_api_version,
2555 l_api_name,
2556 G_PKG_NAME)
2557 THEN
2558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2562 IF FND_API.to_Boolean(p_init_msg_list) THEN
2559 END IF;
2560
2561 -- Initialize message list if p_init_msg_list is set to TRUE.
2563 FND_MSG_PUB.initialize;
2564 END IF;
2565
2566 -- Initialize API return status to success
2567 x_return_status := FND_API.G_RET_STS_SUCCESS;
2568 x_isvalid := 'Y';
2569 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name,'validating solve output levels');
2570 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw || '!';
2571 l_alldims_invalid := 'N';
2572 zpb_aw.execute('lmt ' || l_dataAw ||'instance to '''|| p_analysis_cycle_id ||'''');
2573 l_lineDim := zpb_aw.interp('shw CM.GETLINEDIM(''SHARED'')');
2574 zpb_aw.execute('lmt ' || l_dataAw ||l_lineDim || ' to values(sv.ln.dim)');
2575 -- first find all lines which have no output selections on any
2576 -- dimension
2577 open member_c;
2578 fetch member_c into l_currentLine;
2579 while member_c%found loop
2580 x_isvalid := 'N';
2581 if x_invalid_linemem_list is null or length(x_invalid_linemem_list) < 1950 then
2582 x_invalid_linemem_list := x_invalid_linemem_list ||','''
2583 || l_currentLine ||'''';
2584 end if;
2585 if l_alldims_invalid <> 'Y' then
2586 x_invalid_dim_list := x_invalid_dim_list || substr(p_outputDims,1,length(p_outputDims)-1);
2587 end if;
2588 l_alldims_invalid := 'Y';
2589
2590 -- get the next line member
2591 fetch member_c into l_currentLine;
2592 end loop; -- while member loop
2593 close member_c;
2594
2595
2596 l_timedim := zpb_aw.interp('shw dl.gettimedim');
2597 -- run the validation for every output selection row
2598 -- initialize for traversing the list of dimensions
2599 l_length := nvl(length(p_outputDims),0);
2600
2601 -- bail with success if no output dimensions
2602 if l_length < 2 then
2603 return;
2604 end if;
2605
2606 l_currpos := 1;
2607 l_nextpos := 1;
2608
2609 while l_currpos < l_length loop
2610
2611 l_nextpos := instr(p_outputDims,',', l_currpos);
2612 l_currentDim := substr(p_outputDims,l_currpos,l_nextpos - l_currpos);
2613 l_dimValid := 'Y';
2614 l_hierdim := l_dataAw || zpb_aw.interp('shw obj(prp ''HIERDIM'' ' ||''''
2615 ||l_dataAw ||l_currentdim ||''')');
2616 l_outputvs := l_dataAw || zpb_aw.interp('shw obj(prp ''HOUTPUTVS'' ' ||''''
2617 ||l_dataAw ||l_currentdim ||''')') ||'(' ||
2618 l_dataAw ||l_lineDim || ' ' || l_dataAw || 'HOUTPUTVSPOINTER.'
2619 || zpb_aw.interp('shw obj(prp ''NAMEFRAGMENT'' ' ||''''
2620 ||l_dataAw ||l_currentdim ||''')')
2621 || ')' ;
2622
2623 --dbms_output.put_line(l_outputvs);
2624 l_cuminputvs := l_dataAw || zpb_aw.interp('shw obj(prp ''DINPUTVS'' ' ||''''
2625 ||l_dataAw ||l_currentdim ||''')');
2626 l_parentRel := l_dataAw || zpb_aw.interp('shw obj(prp ''PARENTREL'' ' ||''''
2627 ||l_dataAw ||l_currentdim ||''')');
2628
2629 -- check that there are no line items which have null query objects
2630 open nullselections_c(l_currentDim, l_timedim);
2631 fetch nullselections_c into l_currentLine, l_fetchedDim, l_outputSelection;
2632
2633 -- found a row with null query object
2634 while nullselections_c%found loop
2635 l_dimValid := 'N';
2636 x_isvalid := 'N';
2637 if x_invalid_linemem_list is null or length(x_invalid_linemem_list) < 1950 then
2638 x_invalid_linemem_list := x_invalid_linemem_list ||','''
2639 || l_currentLine ||'''';
2640 end if;
2641
2642 fetch nullselections_c into l_currentLine,l_fetchedDim, l_outputSelection;
2643 end loop;
2644 -- close the cursor
2645 close nullselections_c;
2646
2647 -- check that there are no line items which have non-null query objects
2648 -- and the members in the query are ancestors of a input selection member
2649
2650 open nonnullselections_c(l_currentDim, l_timedim);
2651 fetch nonnullselections_c into l_outputSelection,l_hierarchy;
2652
2653
2654 while nonnullselections_c%found loop
2655
2656 -- get a line member that corresponds to this output selection.
2657 -- this line member will be used to limit the output selection valueset
2658
2659 open selection_member_c(l_currentDim,l_outputSelection);
2660 fetch selection_member_c into l_currentLine;
2661 close selection_member_c;
2662 -- dbms_output.put_line('cur line = ' || l_currentLine||l_currentDim||l_outputSelection );
2663 zpb_aw.execute('lmt ' || l_dataAw ||l_lineDim || ' to ''' || l_currentLine||'''');
2664
2665 -- verify that no output selection member is an ancestor of
2666 -- an input selection member
2667 zpb_aw.execute('lmt '|| l_hierdim || ' to ''' || l_hierarchy||'''');
2668
2669
2670
2671
2672 zpb_log.write_statement(G_PKG_NAME||'.'||l_api_name,zpb_aw.interp('shw statlen(' || l_outputvs ||') ne statlen('||
2673 'lmt(' || l_outputvs|| ' remove lmt(lmt('
2674 ||l_cuminputvs || ' add ancestors using '||l_parentRel||
2675 ') remove ' || l_cuminputvs ||')))'));
2676
2677 -- condition 1: check that none of the ancestors of the cumulative input selection exists
2678 -- in the output selection
2679
2683
2680 -- condition 2: output selection valueset is empty
2681
2682 -- condition 3: no parent-child relation in the output hierarchy itself
2684 if zpb_aw.interpbool('shw statlen(' || l_outputvs ||') ne statlen('||
2685 'lmt(' || l_outputvs|| ' remove lmt(lmt(' ||
2686 l_cuminputvs || ' add ancestors using '||l_parentRel||
2687 ') remove ' || l_cuminputvs ||')))')
2688 OR
2689 zpb_aw.interpbool('shw statlen('|| l_outputvs|| ') eq 0')
2690 OR
2691 zpb_aw.interpbool('shw statlen('||l_outputvs||') ne ' ||
2692 ' statlen(lmt(' ||l_outputvs ||
2693 ' remove ancestors using ' || l_parentRel || '))') then
2694
2695 -- get all the line members that use this output selection
2696 l_dimvalid := 'N';
2697 x_isvalid := 'N';
2698
2699 -- dbms_output.put_line(p_analysis_cycle_id || ' ' || l_currentDim || l_outputSelection || l_hierarchy);
2700
2701 open hiermember_c(l_currentDim,l_outputSelection, l_hierarchy);
2702 fetch hiermember_c into l_currentLine;
2703 while hiermember_c%found loop
2704 if x_invalid_linemem_list is null or length(x_invalid_linemem_list) < 1950 then
2705 x_invalid_linemem_list := x_invalid_linemem_list ||','''
2706 || l_currentLine ||'''';
2707 end if;
2708 fetch hiermember_c into l_currentLine;
2709 end loop;
2710 close hiermember_c;
2711
2712 end if;
2713 fetch nonnullselections_c into l_outputSelection, l_hierarchy;
2714 end loop; -- loop over output selections
2715
2716 -- close the cursor
2717 close nonnullselections_c;
2718
2719 if l_dimValid = 'N' and l_alldims_invalid = 'N' then
2720 x_invalid_dim_list := x_invalid_dim_list ||','||l_currentDim;
2721 end if;
2722
2723 -- traverse the output dim list
2724 l_currpos := l_nextpos + 1;
2725 end loop; -- outer loop for dim list
2726
2727 -- remove the extra comma
2728 if x_isvalid = 'N' then
2729 x_invalid_dim_list := substr(x_invalid_dim_list,2,length(x_invalid_dim_list)-1);
2730 x_invalid_linemem_list := substr(x_invalid_linemem_list,2,length(x_invalid_linemem_list)-1);
2731 end if;
2732 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,'Returning :' || x_isvalid);
2733
2734 exception
2735 WHEN FND_API.G_EXC_ERROR THEN
2736 ROLLBACK TO validate_output_selections;
2737 x_return_status := FND_API.G_RET_STS_ERROR;
2738 FND_MSG_PUB.Count_And_Get(
2739 p_count => x_msg_count,
2740 p_data => x_msg_data
2741 );
2742 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2743 ROLLBACK TO validate_output_selections;
2744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2745 FND_MSG_PUB.Count_And_Get(
2746 p_count => x_msg_count,
2747 p_data => x_msg_data
2748 );
2749 WHEN OTHERS THEN
2750 ROLLBACK TO validate_output_selections;
2751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2752 x_isvalid := 'N';
2753 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2754 -- dbms_output.put_line(to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2755 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2756 FND_MSG_PUB.Add_Exc_Msg(
2757 G_PKG_NAME,
2758 l_api_name
2759 );
2760 END IF;
2761 FND_MSG_PUB.Count_And_Get(
2762 p_count => x_msg_count,
2763 p_data => x_msg_data
2764 );
2765
2766 end validate_output_selections;
2767
2768 -- this procedure validates that the solve input and output selections .
2769 -- share a hierarchy with the horizon start and end levels
2770 -- it returns 1 output variable
2771 -- x_isvalid: this variable is a boolean and can contain either
2772 -- 'Y' or 'N'
2773 PROCEDURE val_solve_hrzselections(
2774 p_api_version IN NUMBER,
2775 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2776 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2777 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2778 x_return_status OUT NOCOPY VARCHAR2 ,
2779 x_msg_count OUT NOCOPY NUMBER,
2780 x_msg_data OUT NOCOPY VARCHAR2,
2781 p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type,
2782 p_hrz_level IN VARCHAR2,
2783 x_isvalid OUT NOCOPY VARCHAR2) IS
2784
2785 l_api_name CONSTANT VARCHAR2(30) := 'val_solve_hrzselections';
2786 l_api_version CONSTANT NUMBER := 1.0;
2787 l_hierarchy zpb_solve_output_selections.hierarchy%type;
2788 l_dataAw varchar2(100);
2789 l_timedim varchar2(100);
2790 l_hierdim varchar2(100);
2791 l_hierlvlvs varchar2(100);
2792 l_hierlist varchar2(4000);
2793 l_hiername varchar2(100);
2794 sql_stmt varchar2(4000);
2795 x_analysis_cycle_id zpb_analysis_cycles.analysis_cycle_id%type;
2796
2797
2798 TYPE selections_cur is REF CURSOR;
2799 input_selections_cur selections_cur;
2800 output_selections_cur selections_cur;
2801
2802 begin
2803
2807 IF NOT FND_API.Compatible_API_Call( l_api_version,
2804 -- Standard Start of API savepoint
2805 SAVEPOINT val_solve_hrzselections;
2806 -- Standard call to check for call compatibility.
2808 p_api_version,
2809 l_api_name,
2810 G_PKG_NAME)
2811 THEN
2812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2813 END IF;
2814
2815 -- Initialize message list if p_init_msg_list is set to TRUE.
2816 IF FND_API.to_Boolean(p_init_msg_list) THEN
2817 FND_MSG_PUB.initialize;
2818 END IF;
2819
2820 -- Initialize API return status to success
2821 x_return_status := FND_API.G_RET_STS_SUCCESS;
2822
2823 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name,'validating solve levels');
2824 l_dataAw := zpb_aw.get_schema||'.'||zpb_aw.get_shared_aw||'!';
2825 x_isvalid := 'Y';
2826 l_timedim := zpb_aw.interp('shw dl.gettimedim');
2827
2828 -- find all the hierarchies that this level belongs to
2829 l_hierdim := l_dataAw || zpb_aw.interp('shw obj(prp ''HIERDIM'' ' ||''''
2830 ||l_dataAw ||l_timedim||''')');
2831 l_hierlvlvs := l_dataAw || zpb_aw.interp('shw obj(prp ''HIERLEVELVS'' ' ||''''
2832 ||l_hierdim ||''')');
2833
2834 zpb_aw.execute('lmt ' || l_hierdim ||' to instat( ' || l_hierlvlvs || ' ''' ||
2835 p_hrz_level||''')');
2836 l_hierlist := zpb_aw.interp('shw joinchars(joincols(filterlines(charlist('||
2837 l_hierdim|| ') joinchars(''\'''' value ''\'''')) '',''))');
2838 -- remove the trailing comma
2839 l_hierlist := '(' || substr(l_hierlist,1,length(l_hierlist)-1) || ')';
2840
2841
2842 --check that there isn't any member that does not share any input hierarchy
2843 -- with the hierarchy list
2844
2845 sql_stmt := ' select member from ' ||
2846 ' zpb_solve_input_selections a where ' ||
2847 ' :1 = a.analysis_cycle_id and ' ||
2848 ' :2 = a.dimension ' ||
2849 ' and not exists ( select b.hierarchy from ' ||
2850 ' zpb_solve_input_selections b where ' ||
2851 ' a.analysis_cycle_id = b.analysis_cycle_id ' ||
2852 ' and a.member = b.member ' ||
2853 ' and a.dimension = b.dimension ' ||
2854 ' and b.hierarchy in ' || l_hierlist||')';
2855
2856
2857 open input_selections_cur for sql_stmt using p_analysis_cycle_id,
2858 l_timedim;
2859
2860 fetch input_selections_cur into l_hiername;
2861
2862 if input_selections_cur%found then
2863 -- dbms_output.put_line('i' || ' ' ||l_hierName);
2864 x_isvalid := 'N';
2865 end if;
2866 close input_selections_cur;
2867
2868 --check that there isn't any member that does not share any output hierarchy
2869 -- with the hierarchy list
2870 sql_stmt := ' select member from ' ||
2871 ' zpb_solve_output_selections a where ' ||
2872 ' :1 = a.analysis_cycle_id and ' ||
2873 ' :2 = a.dimension ' ||
2874 ' and not exists ( select b.hierarchy from ' ||
2875 ' zpb_solve_output_selections b where ' ||
2876 ' a.analysis_cycle_id = b.analysis_cycle_id ' ||
2877 ' and a.member = b.member ' ||
2878 ' and a.dimension = b.dimension ' ||
2879 ' and b.hierarchy in ' || l_hierlist || ')';
2880
2881 open output_selections_cur for sql_stmt using p_analysis_cycle_id,
2882 l_timedim;
2883 fetch output_selections_cur into l_hiername;
2884 if output_selections_cur%found then
2885 -- dbms_output.put_line('i' || ' ' ||l_hierName);
2886 x_isvalid := 'N';
2887 end if;
2888 close output_selections_cur;
2889
2890 exception
2891 WHEN FND_API.G_EXC_ERROR THEN
2892 ROLLBACK TO val_solve_hrzselections;
2893 x_return_status := FND_API.G_RET_STS_ERROR;
2894 FND_MSG_PUB.Count_And_Get(
2895 p_count => x_msg_count,
2896 p_data => x_msg_data
2897 );
2898 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2899 ROLLBACK TO val_solve_hrzselections;
2900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2901 FND_MSG_PUB.Count_And_Get(
2902 p_count => x_msg_count,
2903 p_data => x_msg_data
2904 );
2905 WHEN OTHERS THEN
2906 ROLLBACK TO val_solve_hrzselections;
2907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2908 x_isvalid := 'N';
2909 -- dbms_output.put_line(to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2910
2911 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2912 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2913 FND_MSG_PUB.Add_Exc_Msg(
2914 G_PKG_NAME,
2915 l_api_name
2916 );
2917 END IF;
2918 FND_MSG_PUB.Count_And_Get(
2919 p_count => x_msg_count,
2920 p_data => x_msg_data
2921 );
2922
2923 end val_solve_hrzselections;
2924 -------------------------------------------------------------------------------
2925
2926 -- To delete view for an active instance
2927
2928 PROCEDURE delete_view(p_analysis_cycle_id IN zpb_analysis_cycles.analysis_cycle_id%type)
2932 l_status_code varchar2(30);
2929
2930 IS
2931 l_pub_ac_id zpb_analysis_cycles.analysis_cycle_id%type;
2933 l_api_name CONSTANT VARCHAR2(30) := 'delete_view';
2934 l_api_version CONSTANT NUMBER := 1.0;
2935
2936 cursor published_ac is
2937 select status_code
2938 from zpb_analysis_cycles
2939 where analysis_cycle_id = p_analysis_cycle_id;
2940
2941 BEGIN
2942 -- Standard Start of API savepoint
2943 SAVEPOINT zpb_acval_pvt_delete_view;
2944 open published_ac;
2945 fetch published_ac into l_status_code;
2946 close published_ac;
2947
2948 update zpb_analysis_cycles set status_code = 'MARKED_FOR_DELETION' where analysis_cycle_id = p_analysis_cycle_id ;
2949 update ZPB_DC_OBJECTS set DELETE_INSTANCE_MEASURES_FLAG = 'Y' where ac_instance_id = p_analysis_cycle_id ;
2950
2951 delete FROM zpb_measure_scope WHERE instance_ac_id = p_analysis_cycle_id;
2952 delete FROM zpb_measure_scope_exempt_users WHERE BUSINESS_PROCESS_ENTITY_ID = p_analysis_cycle_id;
2953
2954
2955 ZPB_LOG.WRITE(G_PKG_NAME || '.' || l_api_name, ' View Deleted with Analysis Cycleid' || p_analysis_cycle_id || '.');
2956
2957 EXCEPTION
2958 WHEN FND_API.G_EXC_ERROR THEN
2959 ROLLBACK TO zpb_acval_pvt_delete_view;
2960 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2961
2962 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2963 ROLLBACK TO zpb_acval_pvt_delete_view;
2964 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2965
2966
2967 WHEN OTHERS THEN
2968 ROLLBACK TO zpb_acval_pvt_delete_view;
2969 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2970
2971 end delete_view;
2972 -------------------------------------------------------------------------------
2973
2974 PROCEDURE has_validation_errors(
2975 itemtype IN varchar2,
2976 itemkey IN varchar2,
2977 actid IN number,
2978 funcmode IN varchar2,
2979 resultout OUT nocopy varchar2) AS
2980 l_bp_id zpb_analysis_cycles.analysis_cycle_id%type;
2981 CURSOR c_val_res IS SELECT distinct message_type FROM
2982 ZPB_BP_VALIDATION_RESULTS WHERE BUS_PROC_ID = l_bp_id;
2983
2984 CURSOR c_override_rt_warn IS SELECT value
2985 FROM zpb_ac_param_values WHERE analysis_cycle_id = l_bp_id
2986 AND param_id = (select to_number(tag) FROM fnd_lookup_values_vl
2987 WHERE LOOKUP_TYPE = 'ZPB_PARAMS'
2988 AND LOOKUP_CODE = 'IGNORE_RT_BP_VAL_WARNINGS');
2989
2990 l_ignore_warn VARCHAR2(2);
2991 BEGIN
2992 l_bp_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2993 Itemkey => ItemKey,
2994 aname => 'ACID');
2995
2996 resultout := 'COMPLETE:SUCCESS';
2997
2998 FOR each IN c_val_res LOOP
2999 IF each.message_type = 'E' THEN
3000 resultout := 'COMPLETE:ERROR';
3001 ELSE
3002 IF resultout <> 'COMPLETE:ERROR' THEN
3003 resultout := 'COMPLETE:WARN';
3004 END IF;
3005 END IF;
3006 END LOOP;
3007
3008 IF resultout = 'COMPLETE:WARN' THEN
3009 OPEN c_override_rt_warn;
3010 FETCH c_override_rt_warn INTO l_ignore_warn;
3011 CLOSE c_override_rt_warn;
3012 IF l_ignore_warn = 'Y' THEN
3013 resultout := 'COMPLETE:WARN_REQ_NO_RESP';
3014 END IF;
3015 END IF;
3016
3017 EXCEPTION
3018 WHEN OTHERS THEN
3019 IF c_val_res%ISOPEN THEN
3020 CLOSE c_val_res;
3021 END IF;
3022 IF c_override_rt_warn%ISOPEN THEN
3023 CLOSE c_override_rt_warn;
3024 END IF;
3025
3026 END has_validation_errors;
3027
3028 END zpb_acval_pvt;
[Home] [Help]