[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,'|| p_view_by_col || ' VIEWBYID,';
'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
supplier.value POA_ATTRIBUTE8, --Supplier
item.value POA_ATTRIBUTE4, --Item
uom.description POA_ATTRIBUTE5, --UOM
POA_MEASURE1, --Quantity
POA_MEASURE2, --Supplier Benchmark Price
POA_MEASURE3, --PO Price
POA_MEASURE4, --Price Difference
POA_MEASURE5, --Price Change Amount
POA_MEASURE6, --PO Purchases Amount
POA_MEASURE7, --Price Ch.Amt Total
POA_MEASURE8, --PO Purch amt Total
i.po_header_id POA_ATTRIBUTE6, --PO Header Id (hidden)
i.po_release_id POA_ATTRIBUTE7 --PO 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,
supplier_id, POA_MEASURE2, POA_MEASURE6)) - 1 rnk,
po_header_id,
po_line_id,
po_item_id,
org_id,
supplier_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,
f.supplier_id,
sum(f.quantity) POA_MEASURE1,
nvl(f.pisp_amt_'
|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
|| '/f.pisp_quantity, cisp.purchase_amt_'
|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
|| '/cisp.quantity)'
|| (case l_cur_suffix when 'b' then '/decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate)' end)
|| ' POA_MEASURE2,
f.purchase_amt_' || l_cur_suffix || '/f.quantity POA_MEASURE3,
((f.purchase_amt_' || l_cur_suffix || '/f.quantity) - (
nvl(f.pisp_amt_'
|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
|| '/f.pisp_quantity, cisp.purchase_amt_'
|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
|| '/cisp.quantity)'
|| (case l_cur_suffix when 'b' then '/decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate)' end)
|| ')) POA_MEASURE4 ,
sum(f.quantity * (f.purchase_amt_'
--Start fix for bug#5227377
--|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
|| l_cur_suffix
--End fix for bug#5227377
|| '/f.quantity - nvl(f.pisp_amt_'
|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
|| '/f.pisp_quantity, cisp.purchase_amt_'
|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
|| '/cisp.quantity)'
|| (case l_cur_suffix when 'b' then '/decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate)' end) || ')) POA_MEASURE5,
sum(f.purchase_amt_' || l_cur_suffix || ') POA_MEASURE6,
sum(sum(f.quantity * (f.purchase_amt_'
--Start fix for bug#5353831
--|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
|| l_cur_suffix
--End fix for bug#5353831
|| '/f.quantity - nvl(f.pisp_amt_'
|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
|| '/f.pisp_quantity, cisp.purchase_amt_'
|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
|| '/cisp.quantity)'
|| (case l_cur_suffix when 'b' then '/decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate)' end)
|| '))) over () POA_MEASURE7,
sum(sum(f.purchase_amt_' || l_cur_suffix || ')) over () POA_MEASURE8
from poa_bm_item_s_mv cisp,
(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.supplier_id,
fact.global_cur_conv_rate,
fact.ent_year_id,
fact.purchase_amt_b,
fact.purchase_amt_sg,
fact.purchase_amt_g,
fact.pisp_quantity,
fact.pisp_amt_g,
fact.pisp_amt_sg,
fact.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.consigned_code <> 1
and fact.order_type = ''QUANTITY''
and fact.complex_work_flag = ''N''
' || l_where_clause ||') f
where f.ent_year_id = cisp.ent_year_id
and f.supplier_id = cisp.supplier_id
and f.po_item_id = cisp.po_item_id
and f.base_uom = cisp.base_uom
group by f.po_header_id, f.po_line_id, f.po_item_id, f.base_uom, f.po_release_id, f.org_id, f.supplier_id, f.global_cur_conv_rate,
nvl(f.pisp_amt_' || (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end) || '/f.pisp_quantity, cisp.purchase_amt_' || (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end) || '/cisp.quantity),
f.purchase_amt_' || (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end) || '/f.quantity,
f.purchase_amt_b/f.quantity
)'
|| ' where ' || l_filter_where ||
' ) i,
po_headers_all poh,
po_lines_all pol,
po_releases_all rel,
poa_items_v item,
poa_suppliers_v supplier,
mtl_units_of_measure_vl uom,
hr_all_organization_units_vl poorg
where i.po_header_id = poh.po_header_id
and i.po_line_id = pol.po_line_id
and i.po_item_id = item.id
and i.base_uom = uom.unit_of_measure
and i.org_id = poorg.organization_id
and i.supplier_id = supplier.id
and i.po_release_id = rel.po_release_id (+)
and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
ORDER BY rnk';