[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'select poh.segment1 || decode(rel.release_num, null, null, ''-'' || rel.release_num) POA_ATTRIBUTE1, -- PO Number
(case when (i.shipment_type in (''BLANKET'',''SCHEDULED'')) then poh.segment1
when (i.shipment_type = ''STANDARD'' and bl.type_lookup_code = ''BLANKET'') then bl.segment1
when (i.POA_MEASURE2 > 0) then ''Catalog''
else '''' end) POA_ATTRIBUTE2, -- Contract Number
item.value POA_ATTRIBUTE3, -- Item
item.description POA_ATTRIBUTE4, -- Description
uom.description POA_ATTRIBUTE5, -- UOM
POA_MEASURE1, -- Quantity
POA_MEASURE2, -- Contract Purchases Amt
POA_MEASURE3, -- Total Contract Purchases
i.po_header_id POA_ATTRIBUTE6, -- PO Header ID
i.po_release_id POA_ATTRIBUTE7, -- PO Release ID
poorg.name POA_ATTRIBUTE8, -- Operating Unit
(case when (i.shipment_type in (''BLANKET'',''SCHEDULED'')) then poorg.name
when (i.shipment_type = ''STANDARD'' and bl.type_lookup_code = ''BLANKET'') then blorg.name
when (i.POA_MEASURE2 > 0) then '' ''
else '''' end) POA_ATTRIBUTE9, -- Operating Unit
(case when (i.shipment_type in (''BLANKET'',''SCHEDULED''))
then ''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||poh.po_header_id||''&addBreadCrumb=Y&retainAM=Y''
when (i.shipment_type = ''STANDARD'' and bl.type_lookup_code = ''BLANKET'')
then ''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||bl.po_header_id||''&addBreadCrumb=Y&retainAM=Y''
when (i.POA_MEASURE2 > 0) then NULL
else NULL end) POA_ATTRIBUTE10 -- Contract Number Drill
from
( select (rank() over
(&ORDER_BY_CLAUSE nulls last, po_header_id, po_item_id, base_uom,
shipment_type, from_document_id, po_release_id, org_id)) - 1 rnk,
po_header_id,
po_item_id,
org_id,
base_uom,
shipment_type,
from_document_id,
po_release_id,
decode(base_uom,null,to_number(null),nvl(POA_MEASURE1,0)) POA_MEASURE1,
nvl(POA_MEASURE2,0) POA_MEASURE2,
nvl(POA_MEASURE3,0) POA_MEASURE3
from
( select fact.po_header_id,
fact.po_item_id,
fact.base_uom,
fact.shipment_type,
fact.from_document_id,
fact.po_release_id,
fact.org_id,
sum(quantity) POA_MEASURE1,
sum(contract_amt_' || l_cur_suffix || ') POA_MEASURE2,
sum(sum(contract_amt_' || l_cur_suffix || ')) over () POA_MEASURE3
from poa_dbi_pod_f_v fact
' || l_in_join_tables || '
where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE
and &BIS_CURRENT_ASOF_DATE
' || l_where_clause || '
and fact.consigned_code <> 1
and fact.contract_type is not null ';
'select poh.segment1 || decode(rel.release_num, null, null, ''-'' || rel.release_num) POA_ATTRIBUTE1, -- PO Number-release number
item.value POA_ATTRIBUTE2, -- Item
item.description POA_ATTRIBUTE3, -- Description
uom.description POA_ATTRIBUTE4, -- UOM
POA_MEASURE1, -- Quantity
POA_MEASURE2, -- NC Purchases Amount
POA_MEASURE3, -- Total NC Purchases
i.po_header_id POA_ATTRIBUTE5, -- PO Header ID
org.name POA_ATTRIBUTE6 -- OU
from
( select (rank() over (&ORDER_BY_CLAUSE nulls last,po_header_id, po_item_id, base_uom, org_id)) - 1 rnk,
po_header_id,
po_release_id,
po_item_id,
base_uom,
org_id,
decode(base_uom,null,to_number(null),nvl(POA_MEASURE1,0)) POA_MEASURE1,
nvl(POA_MEASURE2,0) POA_MEASURE2,
nvl(POA_MEASURE3,0) POA_MEASURE3
from
( select fact.po_header_id,
fact.po_release_id,
fact.po_item_id,
fact.base_uom,
fact.org_id,
sum(quantity) POA_MEASURE1,
sum(n_contract_amt_' || l_cur_suffix || ') POA_MEASURE2,
sum(sum(n_contract_amt_' || l_cur_suffix || ')) over () POA_MEASURE3
from poa_dbi_pod_f_v fact
' || l_in_join_tables || '
where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
' || l_where_clause || '
and fact.consigned_code <> 1
and fact.contract_type is null ';
'select poh.segment1 POA_ATTRIBUTE1, -- PO Number
poorg.name POA_ATTRIBUTE10, -- Unused Contract URL
sup.value POA_ATTRIBUTE2, -- Supplier
item.value POA_ATTRIBUTE3, -- Item
item.description POA_ATTRIBUTE4, -- Description
uom.description POA_ATTRIBUTE5, -- UOM
POA_MEASURE1, -- Quantity
POA_MEASURE3, -- Contract Leakage Amount
POA_MEASURE2, -- Leakage Impact Amount
bl.segment1 POA_ATTRIBUTE6, -- Unused Contract Number
blorg.name POA_ATTRIBUTE12, -- Operating Unit
bl_sup.value POA_ATTRIBUTE7, -- Unused Supplier
POA_MEASURE4, -- Total Leakage Impact Amount
POA_MEASURE5, -- Total Contract Leakage Amt
i.po_header_id POA_ATTRIBUTE8, -- PO Header ID
i.potential_contract_id POA_ATTRIBUTE9, -- PO Release ID
decode(bl.segment1,null,null,''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||i.potential_contract_id||''&PoReleaseId=&addBreadCrumb=Y&retainAM=Y'') POA_ATTRIBUTE11
from
( select (rank() over (&ORDER_BY_CLAUSE nulls last,po_header_id,po_item_id,
base_uom, potential_contract_id,
supplier_id, org_id)) - 1 rnk,
po_header_id,
po_item_id,
base_uom,
potential_contract_id,
supplier_id,
org_id,
decode(base_uom,null,to_number(null),nvl(POA_MEASURE1,0)) POA_MEASURE1,
nvl(POA_MEASURE2,0) POA_MEASURE2,
nvl(POA_MEASURE3,0) POA_MEASURE3,
nvl(POA_MEASURE4,0) POA_MEASURE4,
nvl(POA_MEASURE5,0) POA_MEASURE5
from
( select fact.po_header_id,
fact.po_item_id,
fact.base_uom,
fact.potential_contract_id,
fact.supplier_id,
fact.org_id,
sum(quantity) POA_MEASURE1,
sum(p_savings_amt_' || l_cur_suffix || ') POA_MEASURE2,
sum(p_contract_amt_' || l_cur_suffix || ') POA_MEASURE3,
sum(sum(p_savings_amt_' || l_cur_suffix || ')) over () POA_MEASURE4,
sum(sum(p_contract_amt_' || l_cur_suffix || ')) over () POA_MEASURE5
from poa_dbi_pod_f_v fact
' || l_in_join_tables || '
where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE
and &BIS_CURRENT_ASOF_DATE
' || l_where_clause || '
and fact.consigned_code <> 1
and fact.contract_type is null
and ((fact.p_contract_amt_b is not null and fact.p_contract_amt_b != 0) or (fact.p_savings_amt_b is not null and fact.p_savings_amt_b != 0)) ';