The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select concatenated_segments,organization_code,subinventory_name,locator_id,pull.organization_id
into x_item_name,x_org_code,x_subinventory,l_locator_id,l_organization_id
from mtl_system_items_kfv a , mtl_parameters b,
mtl_kanban_pull_sequences pull
where a.inventory_item_id = pull.inventory_item_id
and a.organization_id = Pull.organization_id
and b.organization_id = Pull.organization_id
and pull.pull_sequence_id = p_Pull_sequence_id;
Select concatenated_segments
into x_loc_name
from mtl_item_locations_kfv
where inventory_location_id = l_locator_id
and organization_id = l_organization_id;
SELECT mkps.*,
msi.LOT_CONTROL_CODE,
msi.SERIAL_NUMBER_CONTROL_CODE,
msi.REVISION_QTY_CONTROL_CODE
FROM MTL_KANBAN_PULL_SEQUENCES mkps,
mtl_system_items msi
WHERE mkps.replenishment_type = 1 --'Logical'
AND mkps.organization_id = p_organizationID
AND mkps.inventory_item_id = msi.inventory_item_id
AND mkps.organization_id = msi.organization_id
ORDER BY mkps.pull_sequence_id;
SELECT *
FROM mtl_kanban_cards
WHERE organization_id = p_organizationID
AND pull_sequence_id = pull_seq_id;
SELECT mks.*
FROM mtl_pull_seq_suppliers mks,
mtl_kanban_pull_sequences mkps
WHERE mks.pull_sequence_id = pull_seq_id
AND mks.pull_sequence_id = mkps.pull_sequence_id
AND mkps.organization_id = p_organizationID;
SELECT Nvl(pda.quantity_delivered,0)
INTO l_received_quantity
FROM mtl_kanban_card_activity mkca,
po_distributions_all pda
WHERE mkca.kanban_card_id = kanban_rec.kanban_card_id
AND mkca.organization_id = kanban_rec.organization_id
AND mkca.inventory_item_id = kanban_rec.inventory_item_id
AND mkca.document_type IS NOT NULL
AND mkca.document_header_id IS NOT NULL
AND mkca.document_header_id = pda.po_header_id
AND mkca.kanban_card_id = pda.kanban_card_id
AND mkca.replenishment_cycle_id = (SELECT Max(replenishment_cycle_id)
FROM mtl_kanban_card_activity
WHERE kanban_card_id = kanban_rec.kanban_card_id);
SELECT Nvl(Sum(mmt.transaction_quantity),0)
INTO l_received_quantity
FROM mtl_material_transactions mmt,
mtl_kanban_card_activity mkca,
mtl_kanban_cards mkc
WHERE mkca.kanban_card_id = kanban_rec.Kanban_Card_Id
AND mkca.document_type IS NOT NULL
AND mkca.document_header_id IS NOT NULL
AND mmt.organization_id = kanban_rec.organization_id
AND mmt.inventory_item_id = kanban_rec.inventory_item_id
AND mmt.transaction_source_type_id = l_transaction_source_type_id
AND mmt.transaction_source_id = mkca.document_header_id
AND mmt.subinventory_code = kanban_rec.subinventory_name
AND mkc.kanban_card_id = mkca.kanban_card_id
AND mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id;
SELECT Count(*)
INTO l_prev_replenished
FROM mtl_kanban_cards
WHERE pull_sequence_id = pull_seq_rec.pull_sequence_id
AND card_status = 1
AND trunc(supply_status) in (3,4,5,6,7,8); -- Wait, Empty, In-process, -In-transit, Wait for consolidation cards
SELECT kanban_card_id,kanban_card_number
FROM mtl_kanban_cards
WHERE pull_sequence_id = p_pull_sequence_id
AND supply_status = Trunc(p_supply_status) --use trunc to consider custom status
AND card_status = 1 -- Active
AND nvl(supplier_id, -1) = Nvl(p_supplier_id, -1)
AND nvl(supplier_site_id,-1) = Nvl(p_supplier_site_id, -1);
flm_kanbancard_pub.update_card_status(p_kanban_id => replenish_cards_rec.kanban_card_id,
p_supply_status_to => 4,
x_retcode => l_retcode,
x_ret_msg_code => l_ret_msg_code,
x_err_msg => ERRBUF);