[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');
(select (rank() over ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
(select ' || p_view_by_col || ',
' || p_view_by_col || ' VIEWBY,';
l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0') ;
(select (rank() over
( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ')) - 1 rnk,'
|| p_view_by_col;
(select ' || p_view_by_col || ',
' || p_view_by_col || ' VIEWBY,';
l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0') ;
(select (rank() over
( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ')) - 1 rnk,'
|| p_view_by_col;
(select ' || p_view_by_col || ',
' || p_view_by_col || ' VIEWBY,';
'select poh.segment1 || decode(rel.release_num, null, null, ''-'' || rel.release_num) POA_ATTRIBUTE1, --PO Number
pol.line_num POA_ATTRIBUTE2, --Line Number
poorg.name POA_ATTRIBUTE3, --Operating Unit
item.value POA_ATTRIBUTE4, --Item
uom.description POA_ATTRIBUTE5, --UOM
POA_MEASURE1, --Quantity
POA_MEASURE2, --Benchmark Price
POA_MEASURE3, --PO Price
POA_MEASURE4, --Price Difference
POA_MEASURE5, --Price Savings Amount
POA_MEASURE6, --Current Amount At PO Price
POA_MEASURE7, --Price Saving Total
POA_MEASURE8, --Cur amt po price total
i.po_header_id POA_ATTRIBUTE6, -- Header_id (hidden)
i.po_release_id POA_ATTRIBUTE7 -- release_id (hidden)
from
( select (rank() over (&ORDER_BY_CLAUSE nulls last,
po_header_id, po_line_id, po_item_id, base_uom,
po_release_id, org_id, POA_MEASURE2, POA_MEASURE3)) - 1 rnk,
po_header_id,
po_line_id,
po_item_id,
org_id,
base_uom,
po_release_id,
decode(base_uom,null,to_number(null),nvl(POA_MEASURE1,0)) POA_MEASURE1,
POA_MEASURE2,
POA_MEASURE3,
POA_MEASURE4,
nvl(POA_MEASURE5,0) POA_MEASURE5,
nvl(POA_MEASURE6,0) POA_MEASURE6,
nvl(POA_MEASURE7,0) POA_MEASURE7,
nvl(POA_MEASURE8,0) POA_MEASURE8
from
( select f.po_header_id,
f.po_line_id,
f.po_item_id,
f.base_uom,
f.po_release_id,
f.org_id,
sum(f.quantity) POA_MEASURE1,
nvl(f.pip_amt_' || l_cur_suffix || '/f.pip_quantity, cip.purchase_amt_' || l_cur_suffix || '/cip.quantity) POA_MEASURE2,
f.purchase_amt_' || l_cur_suffix || '/f.quantity POA_MEASURE3,
((nvl(f.pip_amt_' || l_cur_suffix || '/f.pip_quantity, cip.purchase_amt_' || l_cur_suffix || '/cip.quantity))-(
f.purchase_amt_' || l_cur_suffix || '/f.quantity)) POA_MEASURE4,
sum(f.quantity * (nvl(f.pip_amt_' || l_cur_suffix || '/f.pip_quantity, cip.purchase_amt_' || l_cur_suffix || '/cip.quantity) - f.purchase_amt_' || l_cur_suffix || '/f.quantity)) POA_MEASURE5,
sum(f.purchase_amt_' || l_cur_suffix || ') POA_MEASURE6,
sum(sum(f.quantity * (nvl(f.pip_amt_' || l_cur_suffix || '/f.pip_quantity, cip.purchase_amt_' || l_cur_suffix || '/cip.quantity) - f.purchase_amt_' || l_cur_suffix || '/f.quantity))) over () POA_MEASURE7,
sum(sum(f.purchase_amt_' || l_cur_suffix || ')) over () POA_MEASURE8
from poa_bm_item_o_mv cip,
( select /*+ NO_MERGE */ fact.po_header_id,
fact.po_line_id,
fact.po_item_id,
fact.base_uom,
fact.po_release_id,
fact.org_id,
fact.ent_year_id,
fact.pip_amt_b,
fact.pip_amt_g,
fact.pip_amt_sg,
fact.purchase_amt_b,
fact.purchase_amt_g,
fact.purchase_amt_sg,
fact.quantity,
fact.pip_quantity
from poa_pqc_bs_j2_mv fact
' || l_in_join_tables || '
where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
and fact.complex_work_flag = ''N''
and fact.consigned_code <> 1
and fact.order_type = ''QUANTITY''
' || l_where_clause;
select VIEWBY,
CASE WHEN start_date > &BIS_CURRENT_ASOF_DATE
THEN to_number(NULL)
ELSE c_cumulative_ps_amt END POA_MEASURE1,
p_cumulative_ps_amt POA_MEASURE2,
CASE WHEN start_date >= &BIS_CURRENT_ASOF_DATE
THEN to_number(NULL)
ELSE c_ps_amt END POA_MEASURE3,
p_ps_amt POA_MEASURE4
from
( select month_name VIEWBY,
sum(ent_period_id) period_id,
max(start_date) start_date,
sum(p_cumulative_ps_amt) p_cumulative_ps_amt,
sum(c_cumulative_ps_amt) c_cumulative_ps_amt,
sum(p_ps_amt) p_ps_amt,
sum(c_ps_amt) c_ps_amt
from
(
( select
substr(cal.name,1,3) month_name,
ent_period_id,
cal.start_date,
c_ps_amt,
sum(nvl(c_ps_amt,0)) over ( ORDER BY ent_period_id ROWS UNBOUNDED PRECEDING) c_cumulative_ps_amt,
null p_ps_amt,
null p_cumulative_ps_amt
from
( SELECT
cal.start_date,
sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') c_ps_amt
FROM ' || l_mv || ' fact,
FII_TIME_ENT_PERIOD cal,
fii_time_rpt_struct_v n
' || l_in_join_tables || '
WHERE
' || l_cur_where_clause || l_where_clause || '
GROUP BY cal.start_date,cal.end_date
) iset,
FII_TIME_ENT_PERIOD cal
where cal.start_date = iset.start_date(+)
AND cal.start_date <= :POA_CURR_END
AND cal.end_date >= :POA_CURR_START
)
UNION ALL
( select
substr(cal.name,1,3) month_name,
null ent_period_id,
null start_date,
null c_ps_amt,
null c_cumulative_ps_amt,
p_ps_amt,
sum(nvl(p_ps_amt,0)) over ( ORDER BY ent_period_id ROWS UNBOUNDED PRECEDING) p_cumulative_ps_amt
from
( SELECT
cal.start_date,
sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') p_ps_amt
FROM ' || l_mv || ' fact,
FII_TIME_ENT_PERIOD cal,
fii_time_rpt_struct_v n
' || l_in_join_tables || '
WHERE
' || l_prev_where_clause || l_where_clause || '
GROUP BY cal.start_date,cal.end_date
) iset,
FII_TIME_ENT_PERIOD cal
where cal.start_date = iset.start_date(+)
AND cal.start_date <= :POA_PRIOR_END
AND cal.end_date >= :POA_PRIOR_START
)';
( select
substr(cal.name,1,3) month_name,
ent_period_id,
cal.start_date,
c_ps_amt,
sum(nvl(c_ps_amt,0)) over ( ORDER BY ent_period_id ROWS UNBOUNDED PRECEDING) c_cumulative_ps_amt,
null p_ps_amt,
null p_cumulative_ps_amt
from
( SELECT
cal.start_date,
sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') c_ps_amt
FROM ' || l_mv2 || ' fact,
FII_TIME_ENT_PERIOD cal,
fii_time_rpt_struct_v n
' || l_in_join_tables2 || '
WHERE
' || l_cur_where_clause || l_where_clause2 || '
GROUP BY cal.start_date,cal.end_date
) iset,
FII_TIME_ENT_PERIOD cal
where cal.start_date = iset.start_date(+)
AND cal.start_date <= :POA_CURR_END
AND cal.end_date >= :POA_CURR_START
)
UNION ALL
( select
substr(cal.name,1,3) month_name,
null ent_period_id,
null start_date,
null c_ps_amt,
null c_cumulative_ps_amt,
p_ps_amt,
sum(nvl(p_ps_amt,0)) over ( ORDER BY ent_period_id ROWS UNBOUNDED PRECEDING) p_cumulative_ps_amt
from
( SELECT
cal.start_date,
sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') p_ps_amt
FROM ' || l_mv2 || ' fact,
FII_TIME_ENT_PERIOD cal,
fii_time_rpt_struct_v n
' || l_in_join_tables2 || '
WHERE
' || l_prev_where_clause || l_where_clause2 || '
GROUP BY cal.start_date,cal.end_date
) iset,
FII_TIME_ENT_PERIOD cal
where cal.start_date = iset.start_date(+)
AND cal.start_date <= :POA_PRIOR_END
AND cal.end_date >= :POA_PRIOR_START
)';
select days VIEWBY,
sum(DECODE(SIGN(report_date - &BIS_CURRENT_ASOF_DATE),
1, NULL,
decode(SIGN(:POA_CURR_START-report_date),1,NULL,c_cumulative_ps_amt)))
POA_MEASURE1,
SUM(DECODE(SIGN(report_date - :POA_PRIOR_END),
1, NULL, p_cumulative_ps_amt)) POA_MEASURE2,
sum(DECODE(SIGN(report_date - &BIS_CURRENT_ASOF_DATE),
1, NULL,
decode(SIGN(:POA_CURR_START-report_date),1,NULL,nvl(c_ps_amt,0))))
POA_MEASURE3,
SUM(DECODE(SIGN(report_date - :POA_PRIOR_END),
1, NULL, nvl(p_ps_amt,0))) POA_MEASURE4
from
(
( select
cal.report_date - :POA_CURR_START + to_number('
|| l_adjust1 || ') days,
report_date,
c_ps_amt,
sum(nvl(c_ps_amt,0)) over (
ORDER BY
(cal.report_date - :POA_CURR_START + to_number('
|| l_adjust1 || '))
ROWS UNBOUNDED PRECEDING) c_cumulative_ps_amt,
null p_ps_amt,
null p_cumulative_ps_amt
from
( SELECT
cal.start_date,
cal.end_date,
sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') c_ps_amt
FROM ' || l_mv || ' fact,
fii_time_day cal,
fii_time_rpt_struct_v n
' || l_in_join_tables || '
WHERE
' || l_cur_where_clause || l_where_clause || '
GROUP BY cal.start_date,cal.end_date
) iset, fii_time_day cal
where cal.start_date = iset.start_date(+)
AND cal.start_date <= :POA_CURR_END
AND cal.end_date >= :POA_CURR_START
)
UNION ALL
( select
cal.report_date - :POA_PRIOR_START + to_number('|| l_adjust2 || ') days,
report_date,
null c_ps_amt,
null c_cumulative_ps_amt,
p_ps_amt,
sum(nvl(p_ps_amt,0)) over (
ORDER BY
cal.report_date - :POA_PRIOR_START + to_number('||l_adjust2||')
ROWS UNBOUNDED PRECEDING) p_cumulative_ps_amt
from
( SELECT
cal.start_date,
cal.end_date,
sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') p_ps_amt
FROM ' || l_mv || ' fact,
fii_time_day cal,
fii_time_rpt_struct_v n
' || l_in_join_tables || '
WHERE
' || l_prev_where_clause || l_where_clause || '
GROUP BY cal.start_date,cal.end_date
) iset, fii_time_day cal
where cal.start_date = iset.start_date(+)
AND cal.start_date <= :POA_PRIOR_END
AND cal.end_date >= :POA_PRIOR_START
)';
( select
cal.report_date - :POA_CURR_START + to_number('
|| l_adjust1 || ') days,
report_date,
c_ps_amt,
sum(nvl(c_ps_amt,0)) over (
ORDER BY
(cal.report_date - :POA_CURR_START + to_number('
|| l_adjust1 || '))
ROWS UNBOUNDED PRECEDING) c_cumulative_ps_amt,
null p_ps_amt,
null p_cumulative_ps_amt
from
( SELECT
cal.start_date,
cal.end_date,
sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') c_ps_amt
FROM ' || l_mv2 || ' fact,
fii_time_day cal,
fii_time_rpt_struct_v n
' || l_in_join_tables2 || '
WHERE
' || l_cur_where_clause || l_where_clause2 || '
GROUP BY cal.start_date,cal.end_date
) iset, fii_time_day cal
where cal.start_date = iset.start_date(+)
AND cal.start_date <= :POA_CURR_END
AND cal.end_date >= :POA_CURR_START
)
UNION ALL
( select
cal.report_date - :POA_PRIOR_START + to_number('|| l_adjust2 || ') days,
report_date,
null c_ps_amt,
null c_cumulative_ps_amt,
p_ps_amt,
sum(nvl(p_ps_amt,0)) over (
ORDER BY
cal.report_date - :POA_PRIOR_START + to_number('||l_adjust2||')
ROWS UNBOUNDED PRECEDING) p_cumulative_ps_amt
from
( SELECT
cal.start_date,
cal.end_date,
sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') p_ps_amt
FROM ' || l_mv2 || ' fact,
fii_time_day cal,
fii_time_rpt_struct_v n
' || l_in_join_tables2 || '
WHERE
' || l_prev_where_clause || l_where_clause2 || '
GROUP BY cal.start_date,cal.end_date
) iset, fii_time_day cal
where cal.start_date = iset.start_date(+)
AND cal.start_date <= :POA_PRIOR_END
AND cal.end_date >= :POA_PRIOR_START
)';