[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sel_clause := 'select decode(v.name,null,
fnd_message.get_string(''POA'', ''POA_DBI_APL_UNASSIGNED''), v.name) VIEWBY,
decode(v.commodity_id,null, -1, v.commodity_id) VIEWBYID,';
l_sel_clause := 'select v.value VIEWBY, v.id VIEWBYID, ';
(select (rank() over
(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col_name || ', base_uom )) - 1 rnk,'
|| p_view_by_col_name || ', base_uom ,
POA_MEASURE12, POA_PERCENT1, POA_MEASURE1, POA_PERCENT2, POA_MEASURE2,
POA_MEASURE3, POA_PERCENT3
from
(select ' || p_view_by_col_name || ',
' || p_view_by_col_name || ' VIEWBY, base_uom,
decode(base_uom,null,to_number(null),nvl(c_nz_quantity,0)) POA_MEASURE12,
nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity) POA_MEASURE1,
(((nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity)) -
(nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity)))/
(nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity))) * 100 POA_PERCENT1,
nvl(c_purchase_amt,0) POA_MEASURE2,
((nvl(c_purchase_amt,0)- nvl(p_purchase_amt,0)) /
decode(p_purchase_amt,0,null,p_purchase_amt))*100 POA_PERCENT2,
nvl(c_purchase_amt_total,0) POA_MEASURE3,
((nvl(c_purchase_amt_total,0)-nvl(p_purchase_amt_total,0))/
(decode(p_purchase_amt_total,0,null,p_purchase_amt_total))) * 100 POA_PERCENT3 ' ;
x_custom_sql := 'Select
poh.segment1 POA_ATTRIBUTE1,
poorg.name POA_ATTRIBUTE8,
uom.description POA_ATTRIBUTE10,
substrb(perf.first_name,1,1) || ''. ''|| perf.last_name POA_ATTRIBUTE3,
POA_MEASURE12,
POA_MEASURE1,
POA_MEASURE5,
POA_MEASURE4,
POA_MEASURE2,
POA_MEASURE13,
POA_MEASURE3,
i.po_header_id POA_ATTRIBUTE6,
''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||poh.po_header_id||''&addBreadCrumb=Y&retainAM=Y'' POA_ATTRIBUTE4
from
(select (rank() over
(&ORDER_BY_CLAUSE nulls last, po_header_id,org_id,buyer_id,base_uom)) - 1 rnk,
po_header_id,
org_id,
buyer_id,
base_uom,
decode(base_uom,null,to_number(null),nvl(POA_MEASURE12,0)) POA_MEASURE12,
POA_MEASURE3 / decode(POA_MEASURE13, 0, null, POA_MEASURE13) POA_MEASURE1,
POA_MEASURE5, POA_MEASURE4, nvl(POA_MEASURE2,0) POA_MEASURE2,
POA_MEASURE13, nvl(POA_MEASURE3,0) POA_MEASURE3
from
(select po_header_id, base_uom, buyer_id, org_id,
decode(base_uom,null,to_number(null),nvl(quantity,0)) POA_MEASURE12,
total_purch_amt /decode(total_quantity,0,null,total_quantity) POA_MEASURE1,
purch_amt / decode(quantity,0,null, quantity) POA_MEASURE5,
(purch_amt / decode(quantity,0,null, quantity) -
total_purch_amt /decode(total_quantity,0,null,total_quantity)) POA_MEASURE4,
purch_amt POA_MEASURE2,
decode(base_uom,null,to_number(null),nvl(total_quantity,0)) POA_MEASURE13,
total_purch_amt POA_MEASURE3
from
(select fact.po_header_id,
fact.base_uom,
fact.buyer_id,
fact.org_id,
sum(quantity) quantity,
sum(purchase_amt_' || l_cur_suffix || ') purch_amt,
sum(sum(quantity)) over () total_quantity,
sum(sum(purchase_amt_' || l_cur_suffix || ')) over () total_purch_amt
from poa_dbi_pod_f_v fact
where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE
and &BIS_CURRENT_ASOF_DATE
and fact.consigned_code <> 1
and fact.purchase_amt_'|| l_cur_suffix || ' > 0
and fact.base_uom = &BASEUOM '|| l_where_clause ||'
group by fact.po_header_id, fact.base_uom, fact.org_id , fact.buyer_id))
where coalesce( decode(POA_MEASURE5,0,null,POA_MEASURE5),
decode(POA_MEASURE2,0,null,POA_MEASURE2)) is not null )
i,
po_headers_all poh,
per_all_people_f perf,
mtl_units_of_measure_vl uom,
hr_all_organization_units_vl poorg
where i.po_header_id = poh.po_header_id
and i.buyer_id = perf.person_id
and sysdate between perf.effective_start_date and perf.effective_end_date
and i.base_uom = uom.unit_of_measure(+)
and i.org_id = poorg.organization_id
and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
ORDER BY rnk ' ;
l_sel_clause := 'select cal.name VIEWBY,';
l_query := '(select '
|| l_add_sel_col
|| l_group_and_sel_clause
|| l_col_names
|| fnd_global.newline||' from '
|| p_fact_name
|| ' fact'
|| l_join_tables
|| l_full_where_clause
|| ' and nvl(fact.base_uom ,''-1'') = ''' || p_uom_code || ''''
|| l_grpid_clause
|| fnd_global.newline
|| l_group_by
|| l_group_and_sel_clause
|| l_paren_str;
l_sel_clause := 'select decode(v.name,null,
fnd_message.get_string(''POA'', ''POA_DBI_APL_UNASSIGNED''), v.name) VIEWBY,
decode(v.commodity_id,null, -1, v.commodity_id) VIEWBYID,';
l_sel_clause := 'select v.value VIEWBY, v.id VIEWBYID, ';
(select (rank() over
(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ')) - 1 rnk,'
|| p_view_by_col || ', base_uom ,
POA_MEASURE12, POA_MEASURE13, POA_PERCENT1, POA_MEASURE1, POA_PERCENT2, POA_MEASURE2,
POA_MEASURE3, POA_PERCENT3 , POA_MEASURE4, POA_MEASURE5 , POA_MEASURE6,
POA_PERCENT4
from
( select ' || p_view_by_col || ', base_uom ,
POA_MEASURE12, POA_MEASURE13, POA_PERCENT1, POA_MEASURE1, POA_PERCENT2, POA_MEASURE2,
POA_MEASURE3, POA_PERCENT3 , (POA_MEASURE2 - POA_MEASURE12*min_avg) POA_MEASURE4,
(POA_MEASURE3 - POA_MEASURE13*min_avg) POA_MEASURE5 ,
(POA_MEASURE3/decode(POA_MEASURE13,0,null,POA_MEASURE13)) POA_MEASURE6,
POA_PERCENT4
from
(select ' || p_view_by_col || ', base_uom ,
' || p_view_by_col || ' VIEWBY,
decode(base_uom,null,to_number(null), nvl(c_nz_quantity,0)) POA_MEASURE12,
decode(base_uom,null,to_number(null),nvl(c_nz_quantity_total,0)) POA_MEASURE13,
min(nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity)) over() min_avg ,
nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity) POA_MEASURE1,
(((nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity)) -
(nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity)))/
(nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity)))*100 POA_PERCENT1,
nvl(c_purchase_amt,0) POA_MEASURE2,
((nvl(c_purchase_amt,0)- nvl(p_purchase_amt,0)) /
(decode(p_purchase_amt,0,null,p_purchase_amt)))*100 POA_PERCENT2,
nvl(c_purchase_amt_total,0) POA_MEASURE3,
((nvl(c_purchase_amt_total,0)-nvl(p_purchase_amt_total,0))/
(decode(p_purchase_amt_total,0,null,p_purchase_amt_total))) * 100 POA_PERCENT3 ,
(((nvl(c_purchase_amt_total,0)/decode(c_nz_quantity_total,0,null,c_nz_quantity_total)) -
(nvl(p_purchase_amt_total,0)/decode(p_nz_quantity_total,0,null,p_nz_quantity_total)))/
(nvl(p_purchase_amt_total,0)/decode(p_nz_quantity_total,0,null,p_nz_quantity_total)))*100 POA_PERCENT4
' ;