[Home] [Help]
3:
4: PROCEDURE get_org_kpi(
5: p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
6: ,p_exp_source_sql out NOCOPY VARCHAR2
7: ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
8: is
9: l_sqlstmt VARCHAR2(15000);
10: v_dummy number := 99;
11:
19:
20: l_total number;
21:
22: v_period varchar2(100);
23: l_bind_rec BIS_QUERY_ATTRIBUTES;
24: BEGIN
25:
26: /** 12.12.2005 npanandi: changed the construction of this query
27: to remove the NA text in the Org Cert KPI portlet
126: p_exp_source_sql := l_sqlstmt || l_qry1 || l_qry2 || l_qry3;
127: /***12.12.2005 npanandi: bug 4880422 fix ends ***/
128:
129: /**04.27.2006 npanandi: adding code for dynamic binding of time period dimensions**/
130: p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
131: l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
132:
133: p_exp_source_output.EXTEND;
134: l_bind_rec.attribute_name := ':TIME1';
143: -- the get_org_certification_result procedure is called by Organization Certification Summary report.
144: PROCEDURE get_org_certification_result(
145: p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
146: ,p_exp_source_sql out NOCOPY VARCHAR2
147: ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
148: is
149: l_multi_factor NUMBER;
150: l_sqlstmt VARCHAR2(15000);
151: l_act_sqlstmt varchar2(15000);
189:
190: v_yyyymm varchar2(6);
191:
192: v_period varchar2(100);
193: l_bind_rec BIS_QUERY_ATTRIBUTES;
194: BEGIN
195: l_issues_sql := ' (select roif.organization_id,count(distinct roif.change_id) as open_issues
196: from rci_open_issues_f roif,eng_engineering_changes eec
197: where eec.change_id=roif.change_id and roif.certification_id is not null and roif.organization_id is not null ';
444:
445: p_exp_source_sql := l_act_sqlstmt;
446:
447: /**04.27.2006 npanandi: adding code for dynamic binding of time period dimensions**/
448: p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
449: l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
450:
451: p_exp_source_output.EXTEND;
452: l_bind_rec.attribute_name := ':TIME1';
503: -- Organization Certification Result report.
504: PROCEDURE get_org_cert_prcnt(
505: p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
506: ,p_exp_source_sql out NOCOPY VARCHAR2
507: ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
508: is
509: l_multi_factor NUMBER;
510: l_sqlstmt VARCHAR2(15000);
511: l_act_sqlstmt varchar2(15000);
516:
517: l_total number;
518:
519: v_period varchar2(100);
520: l_bind_rec BIS_QUERY_ATTRIBUTES;
521: BEGIN
522:
523: l_getcnt := 'select count(1) from (
524: select distinct nvl(rocsf.fin_certification_id,0) certification_id,
654: /**p_exp_source_sql := l_sqlstmt || l_from_clause || l_grp_by;**/
655: p_exp_source_sql := l_act_sqlstmt;
656:
657: /**04.27.2006 npanandi: adding code for dynamic binding of time period dimensions**/
658: p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
659: l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
660:
661: p_exp_source_output.EXTEND;
662: l_bind_rec.attribute_name := ':TIME1';
671: -- Organization Certification Unmitigated Risks List report.
672: PROCEDURE get_unmitigated_risks(
673: p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
674: ,p_exp_source_sql out NOCOPY VARCHAR2
675: ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
676: is
677: l_sqlstmt VARCHAR2(15000);
678: ---12.22.2005 npanandi: added l_act_sqlstmt below
679: l_act_sqlstmt VARCHAR2(15000);
686:
687: l_drill_url varchar2(15000);
688:
689: v_period varchar2(100);
690: l_bind_rec BIS_QUERY_ATTRIBUTES;
691: BEGIN
692:
693: l_sqlstmt := 'select distinct art.name RCI_ORG_CERT_MEASURE1
694: ,aapv.project_name RCI_ORG_CERT_MEASURE2
860:
861: p_exp_source_sql := l_act_sqlstmt;
862:
863: /**04.27.2006 npanandi: adding code for dynamic binding of time period dimensions**/
864: p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
865: l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
866:
867: p_exp_source_output.EXTEND;
868: l_bind_rec.attribute_name := ':TIME1';
878: -- Organization Certification Control Detail List report.
879: PROCEDURE get_control_list(
880: p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
881: ,p_exp_source_sql out NOCOPY VARCHAR2
882: ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
883: is
884: l_sqlstmt VARCHAR2(15000);
885: ---12.22.2005 npanandi: added l_act_sqlstmt below
886: l_act_sqlstmt VARCHAR2(15000);
892: l_total number;
893: l_drill_url varchar2(15000);
894:
895: v_period varchar2(100);
896: l_bind_rec BIS_QUERY_ATTRIBUTES;
897: BEGIN
898:
899: l_sqlstmt := 'select distinct act.name RCI_ORG_CERT_MEASURE1
900: ,act.description RCI_ORG_CERT_MEASURE2
1058:
1059: p_exp_source_sql := l_act_sqlstmt;
1060:
1061: /**04.28.2006 npanandi: adding code for dynamic binding of time period dimensions**/
1062: p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
1063: l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1064:
1065: p_exp_source_output.EXTEND;
1066: l_bind_rec.attribute_name := ':TIME1';
1077: -- Issue Detail List report.
1078: PROCEDURE get_issue_detail(
1079: p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1080: ,p_exp_source_sql out NOCOPY VARCHAR2
1081: ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1082: is
1083: l_sqlstmt VARCHAR2(15000);
1084: l_act_sqlstmt varchar2(15000);
1085: l_from_clause varchar2(15000);
1273: -- Process Deficiency Detail report.
1274: PROCEDURE get_deficient_processes(
1275: p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1276: ,p_exp_source_sql out NOCOPY VARCHAR2
1277: ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1278: is
1279: l_sqlstmt VARCHAR2(15000);
1280: ---12.22.2005 npanandi: added l_act_sqlstmt below
1281: l_act_sqlstmt VARCHAR2(15000);
1287: l_total number;
1288: l_drill_url varchar2(15000);
1289:
1290: v_period varchar2(100);
1291: l_bind_rec BIS_QUERY_ATTRIBUTES;
1292: BEGIN
1293:
1294: /*** distinct is OK here, because the rolled up numbers from
1295: the previous page report (Organization Certification Summary)
1458:
1459: p_exp_source_sql := l_act_sqlstmt;
1460:
1461: /**04.28.2006 npanandi: adding code for dynamic binding of time period dimensions**/
1462: p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
1463: l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1464:
1465: p_exp_source_output.EXTEND;
1466: l_bind_rec.attribute_name := ':TIME1';
1475: -- Organization Certification Detail report.
1476: PROCEDURE get_org_certification_detail(
1477: p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1478: ,p_exp_source_sql out NOCOPY VARCHAR2
1479: ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1480: is
1481: l_sqlstmt VARCHAR2(15000);
1482: ---12.22.2005 npanandi: added l_act_sqlstmt below
1483: l_act_sqlstmt VARCHAR2(15000);
1490: l_total number;
1491: l_drill_url varchar2(15000);
1492:
1493: v_period varchar2(100);
1494: l_bind_rec BIS_QUERY_ATTRIBUTES;
1495: BEGIN
1496:
1497: l_sqlstmt := 'select distinct aauv.name RCI_ORG_CERT_MEASURE13
1498: , acv.certification_name RCI_ORG_CERT_MEASURE1
1640: p_exp_source_sql := l_act_sqlstmt;
1641: ----p_exp_source_sql := l_sqlstmt || l_from_clause || l_group_by;
1642:
1643: /**04.28.2006 npanandi: adding code for dynamic binding of time period dimensions**/
1644: p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
1645: l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1646:
1647: p_exp_source_output.EXTEND;
1648: l_bind_rec.attribute_name := ':TIME1';
1658: -- Organization Certification Detail + Significant Account Evaluation Summary report.
1659: PROCEDURE get_process_detail(
1660: p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1661: ,p_exp_source_sql out NOCOPY VARCHAR2
1662: ,p_exp_source_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1663: is
1664: l_sqlstmt VARCHAR2(15000);
1665: ---12.22.2005 npanandi: added l_act_sqlstmt below
1666: l_act_sqlstmt VARCHAR2(15000);
1672: l_total number;
1673: l_drill_url varchar2(15000);
1674:
1675: v_period varchar2(100);
1676: l_bind_rec BIS_QUERY_ATTRIBUTES;
1677: BEGIN
1678:
1679: /** 12.02.2005 npanandi: added distinct clause below ***/
1680: l_sqlstmt := 'select distinct apov.display_name RCI_ORG_CERT_MEASURE1
1836:
1837: p_exp_source_sql := l_act_sqlstmt;
1838:
1839: /**04.28.2006 npanandi: adding code for dynamic binding of time period dimensions**/
1840: p_exp_source_output := BIS_QUERY_ATTRIBUTES_TBL();
1841: l_bind_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1842:
1843: p_exp_source_output.EXTEND;
1844: l_bind_rec.attribute_name := ':TIME1';