[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'
,'7.1');
(select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(
p_view_by_dim,
'PO',
'7.1'
);
(select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
,'PO'
,'7.1');
(select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
,'PO'
,'7.1');
(select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
l_sel_clause := 'select cal.name VIEWBY,';
l_sel_clause := 'select cal.name VIEWBY,';
l_sel_clause := 'select cal.name VIEWBY,';
select
prh.segment1 POA_MEASURE1, -- Requisition Number
prl.line_num POA_PERCENT1, -- Line Num
rorg.name POA_MEASURE5, -- Req Creation OU
substrb(perf.first_name,1,1) || ''. '' || perf.last_name POA_MEASURE2, -- Requestor Name
POA_PERCENT2 POA_PERCENT2, -- Req Approved Date
POA_MEASURE7 POA_MEASURE7, -- Processed Date
POA_MEASURE8 POA_MEASURE8, -- Fulfilled Date
item.value POA_PERCENT3, -- Item
supplier.value POA_PERCENT4, -- Supplier
i.POA_MEASURE3 POA_MEASURE3, -- Amount
decode(pll.po_release_id,null,
poh.segment1,
poh.segment1||''-''||por.release_num) POA_PERCENT5, -- PO Number
porg.name POA_MEASURE4, -- PO OU
POA_MEASURE6 POA_MEASURE6, -- Grand Total for Amount
prh.requisition_header_id POA_ATTRIBUTE3,
prl.requisition_line_id POA_ATTRIBUTE4,
poh.po_header_id POA_ATTRIBUTE5,
pll.po_release_id POA_ATTRIBUTE6
from (select (rank() over (&ORDER_BY_CLAUSE nulls last, req_header_id, req_line_id))-1 rnk,
req_header_id,
req_line_id,
req_creation_ou_id,
requester_id,
POA_PERCENT2 POA_PERCENT2,
POA_MEASURE7 POA_MEASURE7,
POA_MEASURE8 POA_MEASURE8,
POA_MEASURE9 POA_MEASURE9,
po_item_id,
supplier_id,
nvl(POA_MEASURE3,0) POA_MEASURE3,
nvl(POA_MEASURE6,0) POA_MEASURE6,
po_line_location_id,
po_creation_ou_id
from ( select
fact.req_header_id,
fact.req_line_id,
fact.req_creation_ou_id,
fact.requester_id,
fact.req_approved_date POA_PERCENT2,
fact.po_approved_date POA_MEASURE7,
fact.req_fulfilled_date POA_MEASURE8,
fact.expected_date POA_MEASURE9,
fact.po_item_id,
fact.supplier_id,
fact.line_amount_'||l_cur_suffix||' POA_MEASURE3,
sum(fact.line_amount_'||l_cur_suffix||') over() POA_MEASURE6,
fact.po_line_location_id,
fact.po_creation_ou_id
from
poa_dbi_req_f fact
where fact.po_approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_EFFECTIVE_END_DATE + (86399/86400) '
|| fnd_global.newline || l_where_clause
|| fnd_global.newline || l_bucket_where ||
')
) i,
po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_headers_all poh,
po_line_locations_all pll,
poa_items_v item,
poa_suppliers_v supplier,
per_all_people_f perf,
hr_all_organization_units_vl rorg,
hr_all_organization_units_vl porg,
po_releases_all por
where i.req_header_id=prh.requisition_header_id
and i.req_line_id=prl.requisition_line_id
and prh.requisition_header_id=prl.requisition_header_id
and i.po_item_id=item.id
and i.req_creation_ou_id=rorg.organization_id
and i.requester_id=perf.person_id
and sysdate between perf.effective_start_date and perf.effective_end_date
and i.supplier_id=supplier.id(+)
and i.po_line_location_id=pll.line_location_id(+)
and pll.po_header_id=poh.po_header_id(+)
and poh.org_id=porg.organization_id(+)
and pll.po_header_id = por.po_header_id(+)
and pll.po_release_id = por.po_release_id(+)
and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
ORDER BY rnk ';