[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Select clause functions
****************************************/
-- Warehouse Storage Utilized (Table) select function
FUNCTION get_stor_tbl_sel_clause (p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2;
Returns the select clause for the Warehouse Storage Utilized (Table)
report query.
Input
Outputs
1. l_sel_clause - Select clause of the report query
History
Date Author Action
12/17/04 Dinkar Gupta Wrote Function
*/
FUNCTION get_stor_tbl_sel_clause (p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
l_sel_clause VARCHAR2 (32767);
l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
(p_join_tbl);
'SELECT
' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
|| l_description || ' OPI_ATTRIBUTE1,
' || l_uom || ' OPI_ATTRIBUTE2,
' || 'oset.OPI_MEASURE1,
' || 'oset.OPI_MEASURE2,
' || 'oset.OPI_MEASURE3,
' || 'oset.OPI_MEASURE4,
' || 'oset.OPI_MEASURE5,
' || 'oset.OPI_MEASURE6,
' || 'oset.OPI_MEASURE7,
' || 'oset.OPI_MEASURE8,
' || 'oset.OPI_MEASURE9,
' || 'oset.OPI_MEASURE10,
' || 'oset.OPI_MEASURE11,
' || 'oset.OPI_MEASURE12,
' || 'oset.OPI_MEASURE13
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE1,
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE5,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE10,
' || 'OPI_MEASURE11,
' || 'OPI_MEASURE12,
' || 'OPI_MEASURE13
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.raw_str ('c_stored_qty')
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.raw_str ('p_utilized_volume')
|| ' OPI_MEASURE2,
' || opi_dbi_rpt_util_pkg.raw_str ('c_utilized_volume')
|| ' OPI_MEASURE3,
' || opi_dbi_rpt_util_pkg.change_str ('c_utilized_volume',
'p_utilized_volume',
'p_utilized_volume',
'OPI_MEASURE4') || ',
' || opi_dbi_rpt_util_pkg.raw_str ('p_weight_stored')
|| ' OPI_MEASURE5,
' || opi_dbi_rpt_util_pkg.raw_str ('c_weight_stored')
|| ' OPI_MEASURE6,
' || opi_dbi_rpt_util_pkg.change_str ('c_weight_stored',
'p_weight_stored',
'p_weight_stored',
'OPI_MEASURE7') || ',
' || opi_dbi_rpt_util_pkg.raw_str ('p_utilized_volume_total')
|| ' OPI_MEASURE8,
' || opi_dbi_rpt_util_pkg.raw_str ('c_utilized_volume_total')
|| ' OPI_MEASURE9,
' || opi_dbi_rpt_util_pkg.change_str ('c_utilized_volume_total',
'p_utilized_volume_total',
'p_utilized_volume_total',
'OPI_MEASURE10') || ',
' || opi_dbi_rpt_util_pkg.raw_str ('p_weight_stored_total')
|| ' OPI_MEASURE11,
' || opi_dbi_rpt_util_pkg.raw_str ('c_weight_stored_total')
|| ' OPI_MEASURE12,
' || opi_dbi_rpt_util_pkg.change_str ('c_weight_stored_total',
'p_weight_stored_total',
'p_weight_stored_total',
'OPI_MEASURE13') ;
Returns the select clause for the Warehouse Storage Utilized Trend
report query.
Input
Outputs
1. l_sel_clause - Select clause of the report query
History
Date Author Action
11/30/04 Dinkar Gupta Wrote Function
*/
FUNCTION get_stor_trd_sel_clause (p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
l_sel_clause VARCHAR2 (32767);
'SELECT
' || ' cal.name VIEWBY,
' || opi_dbi_rpt_util_pkg.raw_str ('iset.p_utilized_volume')
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.raw_str ('iset.c_utilized_volume')
|| ' OPI_MEASURE2,
' || opi_dbi_rpt_util_pkg.change_str ('c_utilized_volume',
'p_utilized_volume',
'p_utilized_volume',
'OPI_MEASURE3') || ',
' || opi_dbi_rpt_util_pkg.raw_str ('iset.p_weight_stored')
|| ' OPI_MEASURE4,
' || opi_dbi_rpt_util_pkg.raw_str ('iset.c_weight_stored')
|| ' OPI_MEASURE5,
' || opi_dbi_rpt_util_pkg.change_str ('c_weight_stored',
'p_weight_stored',
'p_weight_stored',
'OPI_MEASURE6') ;
Returns the select clause for the Current Capacity Utilization
report query.
Input
Outputs
1. l_sel_clause - Select clause of the report query
History
Date Author Action
12/16/04 Dinkar Gupta Wrote Function
*/
FUNCTION get_curr_utz_sel_clause (p_view_by_dim IN VARCHAR2,
p_join_tbl IN
poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
RETURN VARCHAR2
IS
l_sel_clause VARCHAR2 (32767);
l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
(p_join_tbl);
'SELECT
' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
|| l_description || ' OPI_ATTRIBUTE1,
' || l_uom || ' OPI_ATTRIBUTE2,
' || 'oset.OPI_MEASURE1,
' || 'oset.OPI_MEASURE2,
' || 'oset.OPI_MEASURE3,
' || 'oset.OPI_MEASURE4,
' || 'oset.OPI_MEASURE5,
' || 'oset.OPI_MEASURE6,
' || 'oset.OPI_MEASURE7,
' || 'oset.OPI_MEASURE8,
' || 'oset.OPI_MEASURE9,
' || 'oset.OPI_MEASURE10,
' || 'oset.OPI_MEASURE11,
' || 'oset.OPI_MEASURE12,
' || 'oset.OPI_MEASURE13
' || 'FROM
' || '(SELECT (rank () over
' || ' (&ORDER_BY_CLAUSE nulls last,
' || l_view_by_fact_col || ')) - 1 rnk,
' || l_view_by_fact_col || ',
' || 'OPI_MEASURE1,
' || 'OPI_MEASURE2,
' || 'OPI_MEASURE3,
' || 'OPI_MEASURE4,
' || 'OPI_MEASURE5,
' || 'OPI_MEASURE6,
' || 'OPI_MEASURE7,
' || 'OPI_MEASURE8,
' || 'OPI_MEASURE9,
' || 'OPI_MEASURE10,
' || 'OPI_MEASURE11,
' || 'OPI_MEASURE12,
' || 'OPI_MEASURE13
' || 'FROM
' || '(SELECT
' || l_view_by_fact_col || ',
' || opi_dbi_rpt_util_pkg.raw_str ('c_stored_qty')
|| ' OPI_MEASURE1,
' || opi_dbi_rpt_util_pkg.raw_str ('c_utilized_volume')
|| ' OPI_MEASURE2,
' || opi_dbi_rpt_util_pkg.raw_str ('c_volume_capacity')
|| ' OPI_MEASURE3,
' || opi_dbi_rpt_util_pkg.percent_str_basic ('c_utilized_volume',
l_vol_utz_denom,
'OPI_MEASURE4') || ',
' || opi_dbi_rpt_util_pkg.raw_str ('c_stored_weight')
|| ' OPI_MEASURE5,
' || opi_dbi_rpt_util_pkg.raw_str ('c_weight_capacity')
|| ' OPI_MEASURE6,
' || opi_dbi_rpt_util_pkg.percent_str_basic ('c_stored_weight',
l_wt_utz_denom,
'OPI_MEASURE7') || ',
' || opi_dbi_rpt_util_pkg.raw_str ('c_utilized_volume_total')
|| ' OPI_MEASURE8,
' || opi_dbi_rpt_util_pkg.raw_str ('c_volume_capacity_total')
|| ' OPI_MEASURE9,
' || opi_dbi_rpt_util_pkg.percent_str_basic
('c_utilized_volume_total',
'c_volume_capacity_total',
'OPI_MEASURE10') || ',
' || opi_dbi_rpt_util_pkg.raw_str ('c_stored_weight_total')
|| ' OPI_MEASURE11,
' || opi_dbi_rpt_util_pkg.raw_str ('c_weight_capacity_total')
|| ' OPI_MEASURE12,
' || opi_dbi_rpt_util_pkg.percent_str_basic
('c_stored_weight_total',
'c_weight_capacity_total',
'OPI_MEASURE13') ;
select
sum (weight_capacity) over (),
sum (volume_capacity) over ()
from opi_dbi_wms_curr_utz_sub_f
where .... (conditions calculated by utility packages).
History
Date Author Action
12/16/04 Dinkar Gupta Wrote Function
*/
FUNCTION curr_utz_item_cat_sub_cap_sql (p_fact_name IN VARCHAR2,
p_where_clause IN VARCHAR2,
p_col_name IN
poa_dbi_util_pkg.POA_DBI_COL_TBL)
RETURN VARCHAR2
IS
l_query VARCHAR2 (32767);
l_query := 'select distinct ';
select sum (....)
...
rank (...)
....
from
(
select
item.viewby,
item.qty,
item.wt,
item.vol,
item.qty_tot,
item.wt_tot,
item.vol_tot,
sub.wt_cap,
sub.vol_cap,
sub.wt_cap_tot,
sub.vol_cap_tot
from
(select
viewby,
sum (stored_qty) qty,
sum (stored_weight) wt,
sum (utilized_volume) vol,
sum (stored_qty) over () qty_tot,
sum (stored_weight) over () wt_tot,
sum (utilized_volume) over () vol_tot
from opi_dbi_wms_curr_utz_item_f
where ..... (all where clauses)
) item,
(select
viewby,
sum (weight_capacity) wt_cap,
sum (volume_capacity) vol_cap,
sum (weight_capacity) over () wt_cap_tot,
sum (volume_capacity) over () vol_cap_tot
from opi_dbi_wms_curr_utz_sub_f
where ... (no item/cat where clause)
) sub
where sub.viewby = item.viewby
)
.....
*/
-- Get the item section of the status sql without the ranking
-- clause. This inline view contains the utilization in
-- each sub/org.
l_item_inner_status_sql :=
poa_dbi_template_pkg.status_sql (
p_fact_name => l_item_mv,
p_where_clause => l_item_where_clause,
p_join_tables => l_join_tbl,
p_use_windowing => 'Y',
p_col_name => l_item_col_tbl,
p_use_grpid => 'N',
p_paren_count => 1,
p_filter_where => NULL,
p_generate_viewby => 'N',
p_in_join_tables => NULL);
select ' || '
oset02.' || l_view_by_col || ' ' || l_view_by_col || ',
CASE WHEN oset01.' || l_view_by_col || ' IS NULL THEN 0
ELSE c_stored_qty
END c_stored_qty,
CASE WHEN c_weight_capacity IS NULL THEN NULL
WHEN oset01.' || l_view_by_col || ' IS NULL THEN 0
ELSE c_stored_weight
END c_stored_weight,
sum (CASE WHEN c_weight_capacity IS NULL THEN NULL
WHEN oset01.' || l_view_by_col || ' IS NULL THEN 0
ELSE c_stored_weight
END) over () c_stored_weight_total,
CASE WHEN c_volume_capacity IS NULL THEN NULL
WHEN oset01.' || l_view_by_col || ' IS NULL THEN 0
ELSE c_utilized_volume
END c_utilized_volume,
sum (CASE WHEN c_volume_capacity IS NULL THEN NULL
WHEN oset01.' || l_view_by_col || ' IS NULL THEN 0
ELSE c_utilized_volume
END) over () c_utilized_volume_total,
c_weight_capacity,
sum (c_weight_capacity) over () c_weight_capacity_total,
c_volume_capacity,
sum (c_volume_capacity) over () c_volume_capacity_total
from
' || l_item_inner_status_sql || ') oset01,
' || l_sub_status_sql || ') oset02
' || ' where oset01.' || l_view_by_col ||
' ' || l_sub_item_outer_join ||
' = oset02.' || l_view_by_col || '
' || ' ) ) ) oset,
' || l_viewby_rank_clause;
select sum (....)
...
rank (...)
....
(select
items.qty,
items.wt,
items.vol,
items.qty_tot,
items.wt_tot,
items.vol_tot,
NULL wt_cap,
NULL vol_cap,
subs.wt_cap_tot,
subs.vol_cap_tot
from
(
select
viewby,
sum (stored_qty) qty,
sum (stored_weight) wt,
sum (utilized_volume) vol,
sum (stored_qty) over () qty_tot,
sum (stored_weight) over () wt_tot,
sum (utilized_volume) over () vol_tot
from opi_dbi_wms_curr_utz_item_f
where ..... (all where clauses)
) items,
(
select distinct
sum (wt_cap) over () wt_cap_tot,
sum (vol_cap) over () vol_cap_tot
from opi_dbi_wms_curr_utz_sub_f
where ... (no item/cat where clause)
) subs
.....
*/
-- Need to filter rows if all relevant measures are
-- N/A.
l_filter_clause := get_curr_utz_filter_clause (l_view_by);