[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');
' from (select * from (select * from
(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 * from (select * from (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 * from (select * from (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 * from (select * from (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_context_where := ' and fact.expected_date <= to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'') ';
|| '(to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'')-fact.req_approved_date)'
|| ' >= &RANGE_LOW)'
|| fnd_global.newline
||' and (&RANGE_HIGH is null or '
||'(to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'')-fact.req_approved_date)'
|| ' < &RANGE_HIGH)';
select fnd_profile.value('VO_MAX_FETCH_SIZE')
into l_vo_max_fetch_size
from dual;
'select prh.segment1 POA_MEASURE1, -- Requisition Number
prl.line_num POA_MEASURE2, -- Requisition Line Num
rorg.name POA_ATTRIBUTE1, -- Req OU
substrb(perf.first_name,1,1) || ''. ''|| perf.last_name POA_ATTRIBUTE2, -- Requester
i.POA_ATTRIBUTE3 POA_ATTRIBUTE3, -- Req Approved Date
i.POA_ATTRIBUTE8 POA_ATTRIBUTE8, -- Processed Date
i.POA_ATTRIBUTE4 POA_ATTRIBUTE4, -- Expected Date
item.value POA_ATTRIBUTE5, -- Item
supplier.value POA_ATTRIBUTE6, -- Supplier
i.POA_MEASURE3 POA_MEASURE3, -- amount
decode(por.po_release_id,null,
poh.segment1,
poh.segment1||''-''||por.release_num) POA_MEASURE4,-- PO Number
porg.name POA_ATTRIBUTE7, -- PO Org
i.POA_MEASURE5 POA_MEASURE5, -- PO Revisions
i.POA_MEASURE6 POA_MEASURE6, -- Amount total
i.POA_MEASURE7 POA_MEASURE7, -- Revisions Total
i.req_header_id POA_ATTRIBUTE13, -- Req Header Id
decode(poh.segment1,null,null,
decode(i.POA_ATTRIBUTE8,null,
decode(pll.po_release_id,null,
''pFunctionName=POA_DBI_PDF_DRILL&DocumentId='' || poh.po_header_id || ''&RevisionNum=''
|| poh.revision_num || ''&LanguageCode='' || userenv(''LANG'')||''&DocumentType=PO&DocumentSubtype=STANDARD&OrgId='' || poh.org_id
|| ''&UserSecurity=Y&StoreFlag=N&ViewOrCommunicate=View&CallFromForm=N'',
''pFunctionName=POA_DBI_PDF_DRILL&DocumentId='' || por.po_release_id || ''&RevisionNum=''
|| por.revision_num || ''&LanguageCode='' || userenv(''LANG'')||''&DocumentType=RELEASE&DocumentSubtype=BLANKET&OrgId='' || por.org_id
|| ''&UserSecurity=Y&StoreFlag=N&ViewOrCommunicate=View&CallFromForm=N'')
,''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||poh.po_header_id ||''&PoReleaseId=''||pll.po_release_id
||''&addBreadCrumb=Y&retainAM=Y'')) POA_ATTRIBUTE14,
decode(i.POA_ATTRIBUTE8,null,null,''pFunctionName=POA_DBI_PCH_DRILL&CorePO=Y&CompareTo=ALL&addBreadCrumb=Y&retainAM=Y&PoHeaderId=''
|| poh.po_header_id
||''&PoReleaseId=''||pll.po_release_id
|| ''&PoNum='' || decode(por.po_release_id, null, poh.segment1, poh.segment1 || ''-'' || por.release_num)
|| ''&RevisionNum=''||i.POA_MEASURE5) POA_ATTRIBUTE15
from
(select * from (select * 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_ATTRIBUTE3 POA_ATTRIBUTE3,
POA_ATTRIBUTE4 POA_ATTRIBUTE4,
POA_ATTRIBUTE8 POA_ATTRIBUTE8,
po_item_id,
supplier_id,
nvl(POA_MEASURE3,0) POA_MEASURE3,
nvl(POA_MEASURE5,0) POA_MEASURE5,
nvl(POA_MEASURE6,0) POA_MEASURE6,
nvl(POA_MEASURE7,0) POA_MEASURE7,
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_ATTRIBUTE3,
fact.expected_date POA_ATTRIBUTE4,
fact.po_approved_date POA_ATTRIBUTE8,
fact.po_item_id,
fact.supplier_id,
fact.line_amount_'
|| l_cur_suffix ||
' POA_MEASURE3,
po_revisions POA_MEASURE5,
sum(fact.line_amount_'
|| l_cur_suffix || ') over() POA_MEASURE6,
sum(po_revisions)over() POA_MEASURE7,
fact.po_line_location_id,
fact.po_creation_ou_id
from
poa_dbi_req_f fact ' || l_in_join_tables || '
where
fact.req_approved_date is not null
and fact.req_fulfilled_date is null' || '
and fact.include_in_ufr=''Y'' ' || l_where_clause || fnd_global.newline ||
l_context_where || fnd_global.newline ||
l_aging_where ||
')) i2 where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1) ) i3 '||l_rownum_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(+)
ORDER BY rnk';