[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
into l_valid_status
from fnd_lookup_values
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
and language = userenv('LANG')
and lookup_code = p_supply_status_to;
select 0 into l_replace_flag
from mtl_pull_seq_suppliers
WHERE pull_sequence_id = p_pull_sequence_id
and supplier_id = p_supplier_id
and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,nvl(supplier_site_id,-1))
and rownum =1;
PROCEDURE UPDATE_CARD_STATUS( P_KANBAN_CARD_NUM IN VARCHAR2,
P_ORG_CODE IN VARCHAR2,
P_SUPPLY_STATUS_FROM IN NUMBER DEFAULT NULL,
P_SUPPLY_STATUS_TO IN NUMBER DEFAULT NULL,
P_CARD_STATUS_TO IN NUMBER DEFAULT NULL,
P_COMMIT_FLAG IN VARCHAR DEFAULT 'N',
X_RETCODE OUT NOCOPY VARCHAR2,
X_RET_MSG_CODE OUT NOCOPY NUMBER,
X_ERR_MSG OUT NOCOPY VARCHAR2)
IS
l_kanban_card_id number;
select kanban_card_id into l_kanban_card_id
from mtl_kanban_cards where
kanban_card_number = P_KANBAN_CARD_NUM
and organization_id = (select organization_id from mtl_parameters
where organization_code = P_ORG_CODE);
UPDATE_CARD_STATUS( P_KANBAN_ID => l_kanban_card_id,
P_SUPPLY_STATUS_FROM => P_SUPPLY_STATUS_FROM,
P_SUPPLY_STATUS_TO => P_SUPPLY_STATUS_TO,
P_CARD_STATUS_TO => P_CARD_STATUS_TO,
P_VERIFY => 'N',
X_RETCODE => X_RETCODE,
X_RET_MSG_CODE => X_RET_MSG_CODE,
X_ERR_MSG => X_ERR_MSG);
END UPDATE_CARD_STATUS;
PROCEDURE UPDATE_CARD_STATUS( P_KANBAN_ID IN NUMBER,
P_SUPPLY_STATUS_FROM IN NUMBER DEFAULT NULL,
P_SUPPLY_STATUS_TO IN NUMBER DEFAULT NULL,
P_CARD_STATUS_TO IN NUMBER DEFAULT NULL,
P_VERIFY IN VARCHAR DEFAULT 'N',
X_RETCODE OUT NOCOPY VARCHAR2,
X_RET_MSG_CODE OUT NOCOPY NUMBER,
X_ERR_MSG OUT NOCOPY VARCHAR2)
IS
l_card_status_to NUMBER;
UPDATE_CARD( p_kanban_card_id => l_kanban_card_rec.kanban_card_id,
p_kanban_card_number => l_kanban_card_rec.kanban_card_number,
p_pull_sequence_id => l_kanban_card_rec.pull_sequence_id,
p_inventory_item_id => l_kanban_card_rec.inventory_item_id,
p_organization_id => l_kanban_card_rec.organization_id,
p_subinventory_name => l_kanban_card_rec.subinventory_name,
p_supply_status => l_kanban_card_rec.supply_status,
p_supply_status_to => nvl(p_supply_status_to,l_kanban_card_rec.supply_status),
p_card_status => l_kanban_card_rec.card_status,
p_card_status_to => nvl(p_card_status_to,l_kanban_card_rec.card_status),
p_kanban_card_type => l_kanban_card_rec.kanban_card_type,
p_source_type => l_kanban_card_rec.source_type,
p_kanban_size => l_kanban_card_rec.kanban_size,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_creation_date => l_kanban_card_rec.creation_date,
p_created_by => l_kanban_card_rec.created_by,
p_locator_id => l_kanban_card_rec.locator_id,
p_supplier_id => l_kanban_card_rec.supplier_id,
p_supplier_site_id => l_kanban_card_rec.supplier_site_id,
p_source_organization_id => l_kanban_card_rec.source_organization_id,
p_source_subinventory => l_kanban_card_rec.source_subinventory,
p_source_locator_id => l_kanban_card_rec.source_locator_id,
p_wip_line_id => l_kanban_card_rec.wip_line_id,
p_current_replnsh_cycle_id => l_kanban_card_rec.current_replnsh_cycle_id,
p_document_type => l_kanban_card_rec.document_type,
p_document_header_id => l_kanban_card_rec.document_header_id,
p_document_detail_id => l_kanban_card_rec.document_detail_id,
p_error_code => l_kanban_card_rec.error_code,
p_last_update_login => fnd_global.login_id,
p_last_print_date => l_kanban_card_rec.last_print_date,
p_attribute_category => l_kanban_card_rec.attribute_category,
p_attribute1 => l_kanban_card_rec.attribute1,
p_attribute2 => l_kanban_card_rec.attribute2,
p_attribute3 => l_kanban_card_rec.attribute3,
p_attribute4 => l_kanban_card_rec.attribute4,
p_attribute5 => l_kanban_card_rec.attribute5,
p_attribute6 => l_kanban_card_rec.attribute6,
p_attribute7 => l_kanban_card_rec.attribute7,
p_attribute8 => l_kanban_card_rec.attribute8,
p_attribute9 => l_kanban_card_rec.attribute9,
p_attribute10 => l_kanban_card_rec.attribute10,
p_attribute11 => l_kanban_card_rec.attribute11,
p_attribute12 => l_kanban_card_rec.attribute12,
p_attribute13 => l_kanban_card_rec.attribute13,
p_attribute14 => l_kanban_card_rec.attribute14,
p_attribute15 => l_kanban_card_rec.attribute15,
p_request_id => l_kanban_card_rec.request_id,
p_program_application_id => l_kanban_card_rec.program_application_id,
p_program_id => l_kanban_card_rec.program_id,
p_program_update_date => l_kanban_card_rec.program_update_date,
p_lot_item_id => l_kanban_card_rec.lot_item_id,
p_lot_number => l_kanban_card_rec.lot_number,
p_lot_item_revision => l_kanban_card_rec.lot_item_revision,
p_lot_subinventory_code => l_kanban_card_rec.lot_subinventory_code,
p_lot_location_id => l_kanban_card_rec.lot_location_id,
p_lot_quantity => l_kanban_card_rec.lot_quantity,
p_replenish_quantity => l_kanban_card_rec.replenish_quantity,
p_need_by_date => l_kanban_card_rec.need_by_date,
p_source_wip_entity_id => l_kanban_card_rec.source_wip_entity_id,
p_replenishment_count => l_kanban_card_rec.replenishment_count,
p_max_replenishments => l_kanban_card_rec.max_replenishments,
p_disable_date => l_kanban_card_rec.disable_date,
p_replacement_flag => l_kanban_card_rec.replacement_flag,
p_verify => p_verify,
X_RETCODE => X_RETCODE,
X_RET_MSG_CODE => X_RET_MSG_CODE,
X_ERR_MSG => X_ERR_MSG);
END UPDATE_CARD_STATUS;
PROCEDURE UPDATE_CARD( p_kanban_card_id IN NUMBER,
p_kanban_card_number IN VARCHAR2,
p_pull_sequence_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_subinventory_name IN VARCHAR2,
p_supply_status IN NUMBER,
p_supply_status_to IN NUMBER,
p_card_status IN NUMBER,
p_card_status_to IN NUMBER,
p_kanban_card_type IN NUMBER,
p_source_type IN NUMBER,
p_kanban_size IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_locator_id IN NUMBER,
p_supplier_id IN NUMBER,
p_supplier_site_id IN NUMBER,
p_source_organization_id IN NUMBER,
p_source_subinventory IN VARCHAR2,
p_source_locator_id IN NUMBER,
p_wip_line_id IN NUMBER,
p_current_replnsh_cycle_id IN NUMBER,
p_document_type IN NUMBER,
p_document_header_id IN NUMBER,
p_document_detail_id IN NUMBER,
p_error_code IN VARCHAR2,
p_last_update_login IN NUMBER,
p_last_print_date IN DATE,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_request_id IN NUMBER ,
p_program_application_id IN NUMBER ,
p_program_id IN NUMBER ,
p_program_update_date IN DATE ,
p_lot_item_id IN NUMBER ,
p_lot_number IN VARCHAR2,
p_lot_item_revision IN VARCHAR2,
p_lot_subinventory_code IN VARCHAR2,
p_lot_location_id IN NUMBER,
p_lot_quantity IN NUMBER,
p_replenish_quantity IN NUMBER,
p_need_by_date IN DATE,
p_source_wip_entity_id IN NUMBER,
p_replenishment_count IN NUMBER,
p_max_replenishments IN NUMBER,
p_disable_date IN DATE,
p_replacement_flag IN NUMBER,
p_verify IN VARCHAR DEFAULT 'N',
X_RETCODE OUT NOCOPY VARCHAR2,
X_RET_MSG_CODE OUT NOCOPY NUMBER,
X_ERR_MSG OUT NOCOPY VARCHAR2)
IS
l_card_status_to NUMBER;
select count(1)
into l_active_cards_count
from mtl_kanban_cards
where card_status in (INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold)
and pull_sequence_id = p_pull_sequence_id
and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
and max_replenishments is null
and disable_date is null;
UPDATE MTL_KANBAN_PULL_SEQUENCES SET
number_of_cards = nvl(future_no_of_cards,number_of_cards),
kanban_size = nvl(future_card_size,kanban_size),
future_no_of_cards = null,
future_card_size = null,
planning_effectivity = null
where pull_sequence_id = p_pull_sequence_id;
INV_KanbanCard_PKG.insert_row( x_return_status => l_return_status
, p_kanban_card_id => l_new_card_id
, p_kanban_card_number => l_new_card_number
, p_pull_sequence_id => l_pull_seq_rec.Pull_Sequence_Id
, p_inventory_item_id => l_pull_seq_rec.Inventory_item_id
, p_organization_id => l_pull_seq_rec.Organization_id
, p_subinventory_name => l_pull_seq_rec.Subinventory_name
, p_supply_status => l_supply_status_to
, p_card_status => l_card_status_to
, p_kanban_card_type => Nvl(l_pull_seq_rec.kanban_card_type,INV_Kanban_Pvt.g_card_type_replenishable)
, p_source_type => l_pull_seq_rec.Source_type
, p_kanban_size => nvl(l_new_card_size,0)
, p_last_update_date => SYSDATE
, p_last_updated_by => FND_GLOBAL.USER_ID
, p_creation_date => SYSDATE
, p_created_by => FND_GLOBAL.USER_ID
, p_last_update_login => FND_GLOBAL.LOGIN_ID
, p_last_print_date => NULL
, p_locator_id => l_pull_seq_rec.Locator_id
, p_supplier_id => l_supplier_id
, p_supplier_site_id => l_supplier_site_id
, p_source_organization_id => l_pull_seq_rec.Source_Organization_id
, p_source_subinventory => l_pull_seq_rec.Source_Subinventory
, p_source_locator_id => l_pull_seq_rec.Source_Locator_id
, p_wip_line_id => l_pull_seq_rec.wip_line_id
, p_current_replnsh_cycle_id => l_new_replenish_cycle_id
, p_document_type => NULL
, p_document_header_id => NULL
, p_document_detail_id => NULL
, p_error_code => NULL
, p_attribute_category => p_attribute_category
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_request_id => NULL
, p_program_application_id => NULL
, p_program_id => NULL
, p_program_update_date => NULL
, p_release_kanban_flag => l_pull_seq_rec.release_kanban_flag
, p_replenishment_count => NULL
, p_max_replenishments => p_max_replenishments
, p_disable_date => p_disable_date
, p_replacement_flag => NULL );
inv_kanbancard_pkg.update_row(x_return_status => l_return_status
, p_kanban_card_id => p_kanban_card_id
, p_kanban_card_number => p_kanban_card_number
, p_pull_sequence_id => p_pull_sequence_id
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_subinventory_name => p_subinventory_name
, p_supply_status => l_supply_status_to
, p_card_status => l_card_status_to
, p_kanban_card_type => p_kanban_card_type
, p_source_type => p_source_type
, p_kanban_size => p_kanban_size
, p_last_update_date => sysdate
, p_last_updated_by => fnd_global.user_id
, p_creation_date => p_creation_date
, p_created_by => p_created_by
, p_last_update_login => fnd_global.login_id
, p_last_print_date => p_last_print_date
, p_locator_id => p_locator_id
, p_supplier_id => p_supplier_id
, p_supplier_site_id => p_supplier_site_id
, p_source_organization_id => p_source_organization_id
, p_source_subinventory => p_source_subinventory
, p_source_locator_id => p_source_locator_id
, p_wip_line_id => p_wip_line_id
, p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
, p_document_type => p_document_type
, p_document_header_id => p_document_header_id
, p_document_detail_id => p_document_detail_id
, p_error_code => p_ERROR_CODE
, p_attribute_category => p_attribute_category
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_replenishment_count => p_replenishment_count
, p_max_replenishments => p_max_replenishments
, p_disable_date => p_disable_date
, p_replacement_flag => p_replacement_flag);
inv_kanbancard_pkg.update_row(x_return_status => l_return_status
, p_kanban_card_id => p_kanban_card_id
, p_kanban_card_number => p_kanban_card_number
, p_pull_sequence_id => p_pull_sequence_id
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_subinventory_name => p_subinventory_name
, p_supply_status => l_supply_status_to
, p_card_status => l_card_status_to
, p_kanban_card_type => p_kanban_card_type
, p_source_type => p_source_type
, p_kanban_size => p_kanban_size
, p_last_update_date => sysdate
, p_last_updated_by => fnd_global.user_id
, p_creation_date => p_creation_date
, p_created_by => p_created_by
, p_last_update_login => fnd_global.login_id
, p_last_print_date => p_last_print_date
, p_locator_id => p_locator_id
, p_supplier_id => p_supplier_id
, p_supplier_site_id => p_supplier_site_id
, p_source_organization_id => p_source_organization_id
, p_source_subinventory => p_source_subinventory
, p_source_locator_id => p_source_locator_id
, p_wip_line_id => p_wip_line_id
, p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
, p_document_type => p_document_type
, p_document_header_id => p_document_header_id
, p_document_detail_id => p_document_detail_id
, p_error_code => p_ERROR_CODE
, p_attribute_category => p_attribute_category
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_replenishment_count => p_replenishment_count
, p_max_replenishments => p_max_replenishments
, p_disable_date => p_disable_date
, p_replacement_flag => p_replacement_flag);
inv_kanbancard_pkg.update_row(
x_return_status => l_return_status
, p_kanban_card_id => p_kanban_card_id
, p_kanban_card_number => p_kanban_card_number
, p_pull_sequence_id => p_pull_sequence_id
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_subinventory_name => p_subinventory_name
, p_supply_status => l_supply_status_to
, p_card_status => l_card_status_to
, p_kanban_card_type => p_kanban_card_type
, p_source_type => p_source_type
, p_kanban_size => p_kanban_size
, p_last_update_date => sysdate
, p_last_updated_by => fnd_global.user_id
, p_creation_date => p_creation_date
, p_created_by => p_created_by
, p_last_update_login => fnd_global.login_id
, p_last_print_date => p_last_print_date
, p_locator_id => p_locator_id
, p_supplier_id => p_supplier_id
, p_supplier_site_id => p_supplier_site_id
, p_source_organization_id => p_source_organization_id
, p_source_subinventory => p_source_subinventory
, p_source_locator_id => p_source_locator_id
, p_wip_line_id => p_wip_line_id
, p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
, p_document_type => p_document_type
, p_document_header_id => p_document_header_id
, p_document_detail_id => p_document_detail_id
, p_error_code => p_ERROR_CODE
, p_attribute_category => p_attribute_category
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_replenishment_count => p_replenishment_count
, p_max_replenishments => p_max_replenishments
, p_disable_date => p_disable_date
, p_replacement_flag => p_replacement_flag);
SELECT a.meaning,b.meaning
INTO l_supply_status_code_from, l_supply_status_code_to
FROM mfg_lookups a, mfg_lookups b
WHERE a.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS' AND a.lookup_code = p_supply_status
AND b.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS' AND b.lookup_code = p_supply_status_to;
inv_kanbancard_pkg.update_row(
x_return_status => l_return_status
, p_kanban_card_id => p_kanban_card_id
, p_kanban_card_number => p_kanban_card_number
, p_pull_sequence_id => p_pull_sequence_id
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_subinventory_name => p_subinventory_name
, p_supply_status => l_supply_status_to
, p_card_status => l_card_status_to
, p_kanban_card_type => p_kanban_card_type
, p_source_type => p_source_type
, p_kanban_size => p_kanban_size
, p_last_update_date => sysdate
, p_last_updated_by => fnd_global.user_id
, p_creation_date => p_creation_date
, p_created_by => p_created_by
, p_last_update_login => fnd_global.login_id
, p_last_print_date => p_last_print_date
, p_locator_id => p_locator_id
, p_supplier_id => p_supplier_id
, p_supplier_site_id => p_supplier_site_id
, p_source_organization_id => p_source_organization_id
, p_source_subinventory => p_source_subinventory
, p_source_locator_id => p_source_locator_id
, p_wip_line_id => p_wip_line_id
, p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
, p_document_type => p_document_type
, p_document_header_id => p_document_header_id
, p_document_detail_id => p_document_detail_id
, p_error_code => p_ERROR_CODE
, p_attribute_category => p_attribute_category
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_replenishment_count => p_replenishment_count
, p_max_replenishments => p_max_replenishments
, p_disable_date => p_disable_date
, p_replacement_flag => p_replacement_flag);
inv_kanbancard_pkg.update_row(
x_return_status => l_return_status
, p_kanban_card_id => p_kanban_card_id
, p_kanban_card_number => p_kanban_card_number
, p_pull_sequence_id => p_pull_sequence_id
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_subinventory_name => p_subinventory_name
, p_supply_status => l_supply_status_to
, p_card_status => l_card_status_to
, p_kanban_card_type => p_kanban_card_type
, p_source_type => p_source_type
, p_kanban_size => p_kanban_size
, p_last_update_date => sysdate
, p_last_updated_by => fnd_global.user_id
, p_creation_date => p_creation_date
, p_created_by => p_created_by
, p_last_update_login => fnd_global.login_id
, p_last_print_date => p_last_print_date
, p_locator_id => p_locator_id
, p_supplier_id => p_supplier_id
, p_supplier_site_id => p_supplier_site_id
, p_source_organization_id => p_source_organization_id
, p_source_subinventory => p_source_subinventory
, p_source_locator_id => p_source_locator_id
, p_wip_line_id => p_wip_line_id
, p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
, p_document_type => p_document_type
, p_document_header_id => p_document_header_id
, p_document_detail_id => p_document_detail_id
, p_error_code => p_ERROR_CODE
, p_attribute_category => p_attribute_category
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_replenishment_count => p_replenishment_count
, p_max_replenishments => p_max_replenishments
, p_disable_date => p_disable_date
, p_replacement_flag => p_replacement_flag);
END UPDATE_CARD;
SELECT Nvl(pda.quantity_delivered,0)
INTO l_total_received_quantity
FROM mtl_kanban_card_activity mkca,
po_distributions_all pda,
mtl_kanban_cards mkc
WHERE mkc.kanban_card_id = p_Kanban_Card_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.document_detail_id = pda.po_distribution_id
AND mkca.kanban_card_id = pda.kanban_card_id
AND mkca.replenishment_cycle_id = mkc.current_replnsh_cycle_id
AND mkca.kanban_card_id = mkc.kanban_card_id
AND mkc.kanban_card_id = pda.kanban_card_id;
select nvl(sum(qty),0)
into l_total_received_quantity
from ( SELECT nvl(mmt.primary_quantity,0) qty
FROM mtl_material_transactions mmt,
mtl_kanban_card_activity mkca,
mtl_kanban_cards mkc
WHERE mkca.kanban_card_id = p_Kanban_Card_Id
AND mkca.document_type IS NOT NULL
AND mkca.document_header_id IS NOT NULL
AND mmt.organization_id = l_kanban_card_rec.organization_id
AND mmt.inventory_item_id = l_kanban_card_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 = l_kanban_card_rec.subinventory_name
AND mmt.trx_source_line_id = mkca.document_detail_id
AND mkc.kanban_card_id = mkca.kanban_card_id
AND mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id
);
SELECT intransit_type into l_intransit_type
FROM MTL_INTERORG_PARAMETERS
WHERE from_organization_id = l_kanban_card_rec.source_organization_id
and to_organization_id = l_kanban_card_rec.organization_id;
select nvl(sum(mmt.primary_quantity),0)
into l_total_received_quantity
from mtl_material_transactions mmt,
oe_order_lines_all ool,
(select mkca.document_header_id,mkca.document_detail_id
from mtl_kanban_cards mkc,mtl_kanban_card_activity mkca
where mkc.kanban_card_id = p_Kanban_Card_Id
and mkc.kanban_card_id = mkca.kanban_card_id
and mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id
and mkca.document_header_id is not null
and mkca.document_detail_id is not null
and rownum = 1) kanban
where
mmt.transaction_source_type_id = 7
and mmt.transaction_type_id = 95
and mmt.transaction_action_id = 3
and mmt.inventory_item_id = l_kanban_card_rec.inventory_item_id
and mmt.organization_id = l_kanban_card_rec.organization_id
and mmt.subinventory_code = l_kanban_card_rec.subinventory_name
and nvl(mmt.locator_id,-1) = nvl(l_kanban_card_rec.locator_id,-1)
and mmt.transaction_source_id= kanban.document_header_id
and ool.source_document_id = kanban.document_header_id
and ool.source_document_line_id = kanban.document_detail_id
and mmt.transaction_reference = ool.header_id
and mmt.trx_source_line_id = ool.line_id;
select nvl(sum(primary_quantity),0)
into l_total_received_quantity
from rcv_transactions rt,
mtl_kanban_card_activity mkca,
mtl_kanban_cards mkc
where rt.transaction_type = 'DELIVER'
and rt.requisition_line_id = mkca.document_detail_id
and rt.organization_id = mkc.organization_id
and rt.subinventory = mkc.subinventory_name
and nvl(rt.locator_id,-1) = nvl(mkc.locator_id,-1)
and mkca.kanban_card_id = p_Kanban_Card_Id
and mkca.document_type IS NOT NULL
and mkca.document_header_id IS NOT NULL
and mkc.kanban_card_id = mkca.kanban_card_id
and mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id;
select nvl(sum(qty),0)
INTO l_total_received_quantity
from ( SELECT nvl(mmt.primary_quantity,0) qty
FROM mtl_material_transactions mmt,
mtl_kanban_card_activity mkca,
mtl_kanban_cards mkc
WHERE mkca.kanban_card_id = p_Kanban_Card_Id
AND mkca.document_type IS NOT NULL
AND mkca.document_header_id IS NOT NULL
AND mmt.organization_id = l_kanban_card_rec.organization_id
AND mmt.inventory_item_id = l_kanban_card_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 = l_kanban_card_rec.subinventory_name
AND mkc.kanban_card_id = mkca.kanban_card_id
AND mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id);
mydebug('Calling update_card_status api');
UPDATE_CARD_STATUS( P_KANBAN_ID => p_Kanban_Card_Id,
P_SUPPLY_STATUS_TO => INV_KANBAN_PVT.G_Supply_Status_Full,
P_CARD_STATUS_TO => l_kanban_card_rec.card_status,
X_RETCODE => X_RETCODE,
X_RET_MSG_CODE => X_RET_MSG_CODE,
X_ERR_MSG => X_ERR_MSG);
mydebug('After update_card_status api: X_RETCODE='||X_RETCODE||', X_RET_MSG_CODE='||X_RET_MSG_CODE||', X_ERR_MSG='||X_ERR_MSG);