The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT h.segment1
INTO l_doc_num
FROM po_distributions_all d,
po_headers_all h
WHERE d.po_distribution_id = p_doc_detail_id
AND h.po_header_id = d.po_header_id;
SELECT h.segment1
INTO l_doc_num
FROM po_distributions_all d,
po_headers_all h
WHERE d.po_distribution_id = p_doc_detail_id
AND h.po_header_id = d.po_header_id;
SELECT h.segment1
INTO l_doc_num
FROM po_requisition_headers_all h,
po_requisition_lines_all l
WHERE l.requisition_line_id = p_doc_detail_id
AND h.requisition_header_id = l.requisition_header_id;
SELECT h.request_number
INTO l_doc_num
FROM mtl_txn_request_headers h,
mtl_txn_request_lines l
WHERE l.line_id = p_doc_detail_id
AND h.header_id = l.header_id;
SELECT h.wip_entity_name
INTO l_doc_num
FROM wip_entities h
WHERE h.wip_entity_id = p_doc_header_id;
SELECT h.creation_date
INTO l_doc_creation_date
FROM po_distributions_all d,
po_headers_all h
WHERE d.po_distribution_id = p_doc_detail_id
AND h.po_header_id = d.po_header_id;
SELECT h.creation_date
INTO l_doc_creation_date
FROM po_distributions_all d,
po_headers_all h
WHERE d.po_distribution_id = p_doc_detail_id
AND h.po_header_id = d.po_header_id;
SELECT h.creation_date
INTO l_doc_creation_date
FROM po_requisition_headers_all h,
po_requisition_lines_all l
WHERE l.requisition_line_id = p_doc_detail_id
AND h.requisition_header_id = l.requisition_header_id;
SELECT h.creation_date
INTO l_doc_creation_date
FROM mtl_txn_request_headers h,
mtl_txn_request_lines l
WHERE l.line_id = p_doc_detail_id
AND h.header_id = l.header_id;
SELECT h.creation_date
INTO l_doc_creation_date
FROM wip_entities h
WHERE h.wip_entity_id = p_doc_header_id;
l_outer_stmt varchar2(8000) := 'select pull_sequence_id,
item_name,
organization_code,
destination,
source_type,
source_type_name,
decode(count(*),1,max(src_inner),''Multiple'') source,
inv_health_status,
inv_health_name,
planner_code,
buyer_name,
max(move_status) move_status,
sum(total_inner) number_of_cards';
l_inner_stmt varchar2(4000) := 'select mkc.pull_sequence_id,
msik.concatenated_segments item_name,
msik.planner_code,
panv.full_name buyer_name,
mp2.organization_code,
concat(mkc.subinventory_name,concat('':'', destloc.concatenated_segments)) destination,
mkc.source_type source_type,
ml.meaning source_type_name,
decode(mkc.source_type, 1, mp.organization_code, 2,psv.vendor_name,
3,concat(mkc.source_subinventory, concat('':'', srcloc.concatenated_segments)),null) src_inner,
mkps.inv_health_status,
ml2.meaning inv_health_name,
max(mkc.move_status) move_status,
count(*) total_inner';
for c_statusRec in (select lookup_code
from mfg_lookups
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
order by lookup_code) loop
l_outer_stmt := l_outer_stmt ||',sum(inner_'|| l_attr_num ||') attribute'|| l_attr_num;
l_outer_stmt varchar2(8000) := 'select pull_sequence_id,
item_name,
destination,
source_type,
source_type_name,
src_inner,
inv_health_status,
inv_health_name,
planner_code,
buyer_name,
max(move_status) move_status,
sum(total_inner) number_of_cards';
l_inner_stmt varchar2(4000) := 'select mkc.pull_sequence_id,
msik.concatenated_segments item_name,
msik.planner_code,
panv.full_name buyer_name,
concat(mkc.subinventory_name,concat('':'', destloc.concatenated_segments)) destination,
mkc.source_type source_type,
ml.meaning source_type_name,
decode(mkc.source_type, 1, mp.organization_code, 2,psv.vendor_name,
3,concat(mkc.source_subinventory, concat('':'', srcloc.concatenated_segments)),null) src_inner,
mkps.inv_health_status,
ml2.meaning inv_health_name,
max(mkc.move_status) move_status,
count(*) total_inner';
for c_statusRec in (select lookup_code
from mfg_lookups
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
order by lookup_code) loop
l_outer_stmt := l_outer_stmt ||',sum(inner_'|| l_attr_num ||') attribute'|| l_attr_num;
l_outer_stmt varchar2(8000) := 'select pull_sequence_id,
item_name,
subinventory_name,
locator,
destination,
source_type,
source_type_name,
decode(count(*),1,max(src_inner),''Multiple'') source,
inv_health_status,
inv_health_name,
planner_code,
buyer_name,
max(move_status) move_status,
sum(total_inner) number_of_cards';
l_inner_stmt varchar2(4000) := 'select mkps.pull_sequence_id,
msik.concatenated_segments item_name,
msik.planner_code,
panv.full_name buyer_name,
concat(mkc.subinventory_name,concat('':'', destloc.concatenated_segments)) destination,
mkps.source_type source_type,
ml.meaning source_type_name,
decode(mkc.source_type, 1, mp.organization_code, 2,psv.vendor_name,
3,concat(mkc.source_subinventory, concat('':'', srcloc.concatenated_segments)),null) src_inner,
mkps.subinventory_name,
milk.concatenated_segments AS locator,
mkps.inv_health_status,
ml2.meaning inv_health_name,
max(mkc.move_status) move_status,
count(*) total_inner';
for c_statusRec in (select lookup_code
from mfg_lookups
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
order by lookup_code) loop
l_outer_stmt := l_outer_stmt ||',sum(inner_'|| l_attr_num ||') attribute'|| l_attr_num;