[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
INTO l_dummy
FROM mtl_kanban_cards
WHERE organization_id = p_organization_id
AND kanban_card_number = p_kanban_card_number
AND((p_kanban_card_id IS NULL)
OR(kanban_card_id <> p_kanban_card_id));
SELECT kanban_card_id
, kanban_card_number
, pull_sequence_id
, inventory_item_id
, organization_id
, subinventory_name
, supply_status
, card_status
, kanban_card_type
, source_type
, kanban_size
, last_update_date
, last_updated_by
, creation_date
, created_by
, locator_id
, supplier_id
, supplier_site_id
, source_organization_id
, source_subinventory
, source_locator_id
, wip_line_id
, current_replnsh_cycle_id
, ERROR_CODE
, last_update_login
, last_print_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, request_id
, program_application_id
, program_id
, program_update_date
INTO l_kanban_card_rec.kanban_card_id
, l_kanban_card_rec.kanban_card_number
, l_kanban_card_rec.pull_sequence_id
, l_kanban_card_rec.inventory_item_id
, l_kanban_card_rec.organization_id
, l_kanban_card_rec.subinventory_name
, l_kanban_card_rec.supply_status
, l_kanban_card_rec.card_status
, l_kanban_card_rec.kanban_card_type
, l_kanban_card_rec.source_type
, l_kanban_card_rec.kanban_size
, l_kanban_card_rec.last_update_date
, l_kanban_card_rec.last_updated_by
, l_kanban_card_rec.creation_date
, l_kanban_card_rec.created_by
, l_kanban_card_rec.locator_id
, l_kanban_card_rec.supplier_id
, l_kanban_card_rec.supplier_site_id
, l_kanban_card_rec.source_organization_id
, l_kanban_card_rec.source_subinventory
, l_kanban_card_rec.source_locator_id
, l_kanban_card_rec.wip_line_id
, l_kanban_card_rec.current_replnsh_cycle_id
, l_kanban_card_rec.ERROR_CODE
, l_kanban_card_rec.last_update_login
, l_kanban_card_rec.last_print_date
, l_kanban_card_rec.attribute_category
, l_kanban_card_rec.attribute1
, l_kanban_card_rec.attribute2
, l_kanban_card_rec.attribute3
, l_kanban_card_rec.attribute4
, l_kanban_card_rec.attribute5
, l_kanban_card_rec.attribute6
, l_kanban_card_rec.attribute7
, l_kanban_card_rec.attribute8
, l_kanban_card_rec.attribute9
, l_kanban_card_rec.attribute10
, l_kanban_card_rec.attribute11
, l_kanban_card_rec.attribute12
, l_kanban_card_rec.attribute13
, l_kanban_card_rec.attribute14
, l_kanban_card_rec.attribute15
, l_kanban_card_rec.request_id
, l_kanban_card_rec.program_application_id
, l_kanban_card_rec.program_id
, l_kanban_card_rec.program_update_date
FROM mtl_kanban_cards
WHERE kanban_card_id = p_kanban_card_id;
SELECT a.meaning
, b.meaning
INTO l_supply_status_from
, l_supply_status_to
FROM mfg_lookups a, mfg_lookups b
WHERE a.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
AND a.lookup_code = p_from_supply_status
AND b.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
AND b.lookup_code = p_to_supply_status;
PROCEDURE insert_row(
x_return_status OUT NOCOPY VARCHAR2
, p_kanban_card_id IN OUT NOCOPY NUMBER
, p_kanban_card_number IN OUT NOCOPY VARCHAR2
, p_pull_sequence_id NUMBER
, p_inventory_item_id NUMBER
, p_organization_id NUMBER
, p_subinventory_name VARCHAR2
, p_supply_status IN OUT NOCOPY NUMBER
, p_card_status IN OUT NOCOPY NUMBER
, p_kanban_card_type NUMBER
, p_source_type NUMBER
, p_kanban_size NUMBER
, p_last_update_date DATE
, p_last_updated_by NUMBER
, p_creation_date DATE
, p_created_by NUMBER
, p_last_update_login NUMBER
, p_last_print_date DATE
, p_locator_id NUMBER
, p_supplier_id NUMBER
, p_supplier_site_id NUMBER
, p_source_organization_id NUMBER
, p_source_subinventory VARCHAR2
, p_source_locator_id NUMBER
, p_wip_line_id NUMBER
, p_current_replnsh_cycle_id IN OUT NOCOPY NUMBER
, p_document_type NUMBER
, p_document_header_id NUMBER
, p_document_detail_id NUMBER
, p_error_code NUMBER
, p_attribute_category VARCHAR2
, p_attribute1 VARCHAR2
, p_attribute2 VARCHAR2
, p_attribute3 VARCHAR2
, p_attribute4 VARCHAR2
, p_attribute5 VARCHAR2
, p_attribute6 VARCHAR2
, p_attribute7 VARCHAR2
, p_attribute8 VARCHAR2
, p_attribute9 VARCHAR2
, p_attribute10 VARCHAR2
, p_attribute11 VARCHAR2
, p_attribute12 VARCHAR2
, p_attribute13 VARCHAR2
, p_attribute14 VARCHAR2
, p_attribute15 VARCHAR2
, p_request_id NUMBER
, p_program_application_id NUMBER
, p_program_id NUMBER
, p_program_update_date DATE
, p_release_kanban_flag NUMBER
) IS
l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
SELECT mtl_kanban_cards_s.NEXTVAL
INTO l_kanban_card_rec.kanban_card_id
FROM DUAL;
SELECT 'X'
INTO l_dummy
FROM mtl_kanban_cards
WHERE kanban_card_number = TO_CHAR(l_kanban_card_rec.kanban_card_id)
AND organization_id = p_organization_id;
l_kanban_card_rec.last_update_date := p_last_update_date;
l_kanban_card_rec.last_updated_by := p_last_updated_by;
l_kanban_card_rec.last_update_login := p_last_update_login;
l_kanban_card_rec.program_update_date := p_program_update_date;
INSERT INTO mtl_kanban_cards
(
kanban_card_id
, kanban_card_number
, pull_sequence_id
, inventory_item_id
, organization_id
, subinventory_name
, supply_status
, card_status
, kanban_card_type
, source_type
, kanban_size
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, last_print_date
, locator_id
, supplier_id
, supplier_site_id
, source_organization_id
, source_subinventory
, source_locator_id
, wip_line_id
, current_replnsh_cycle_id
, ERROR_CODE
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, request_id
, program_application_id
, program_id
, program_update_date
)
VALUES (
l_kanban_card_rec.kanban_card_id
, l_kanban_card_rec.kanban_card_number
, l_kanban_card_rec.pull_sequence_id
, l_kanban_card_rec.inventory_item_id
, l_kanban_card_rec.organization_id
, l_kanban_card_rec.subinventory_name
, DECODE(
l_kanban_card_rec.supply_status
, inv_kanban_pvt.g_supply_status_empty, inv_kanban_pvt.g_supply_status_new
, l_kanban_card_rec.supply_status
)
, l_kanban_card_rec.card_status
, l_kanban_card_rec.kanban_card_type
, l_kanban_card_rec.source_type
, l_kanban_card_rec.kanban_size
, l_kanban_card_rec.last_update_date
, l_kanban_card_rec.last_updated_by
, l_kanban_card_rec.creation_date
, l_kanban_card_rec.created_by
, l_kanban_card_rec.last_update_login
, l_kanban_card_rec.last_print_date
, l_kanban_card_rec.locator_id
, l_kanban_card_rec.supplier_id
, l_kanban_card_rec.supplier_site_id
, l_kanban_card_rec.source_organization_id
, l_kanban_card_rec.source_subinventory
, l_kanban_card_rec.source_locator_id
, l_kanban_card_rec.wip_line_id
, l_kanban_card_rec.current_replnsh_cycle_id
, l_kanban_card_rec.ERROR_CODE
, l_kanban_card_rec.attribute_category
, l_kanban_card_rec.attribute1
, l_kanban_card_rec.attribute2
, l_kanban_card_rec.attribute3
, l_kanban_card_rec.attribute4
, l_kanban_card_rec.attribute5
, l_kanban_card_rec.attribute6
, l_kanban_card_rec.attribute7
, l_kanban_card_rec.attribute8
, l_kanban_card_rec.attribute9
, l_kanban_card_rec.attribute10
, l_kanban_card_rec.attribute11
, l_kanban_card_rec.attribute12
, l_kanban_card_rec.attribute13
, l_kanban_card_rec.attribute14
, l_kanban_card_rec.attribute15
, l_kanban_card_rec.request_id
, l_kanban_card_rec.program_application_id
, l_kanban_card_rec.program_id
, l_kanban_card_rec.program_update_date
);
inv_kanbancard_pkg.update_row(
x_return_status => l_return_status
, 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_supply_status
, p_card_status => l_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 => l_kanban_card_rec.last_update_date
, p_last_updated_by => l_kanban_card_rec.last_updated_by
, p_creation_date => l_kanban_card_rec.creation_date
, p_created_by => l_kanban_card_rec.created_by
, p_last_update_login => l_kanban_card_rec.last_update_login
, p_last_print_date => l_kanban_card_rec.last_print_date
, 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_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_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
);
insert_activity_for_card(l_kanban_card_rec);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'Insert_Row');
END insert_row;
SELECT *
FROM mtl_kanban_cards
WHERE kanban_card_id = p_kanban_card_id
FOR UPDATE OF organization_id NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_row(
x_return_status OUT NOCOPY VARCHAR2
, p_kanban_card_id NUMBER
, p_kanban_card_number VARCHAR2
, p_pull_sequence_id NUMBER
, p_inventory_item_id NUMBER
, p_organization_id NUMBER
, p_subinventory_name VARCHAR2
, p_supply_status IN OUT NOCOPY NUMBER
, p_card_status IN OUT NOCOPY NUMBER
, p_kanban_card_type NUMBER
, p_source_type NUMBER
, p_kanban_size NUMBER
, p_last_update_date DATE
, p_last_updated_by NUMBER
, p_creation_date DATE
, p_created_by NUMBER
, p_last_update_login NUMBER
, p_last_print_date DATE
, p_locator_id NUMBER
, p_supplier_id NUMBER
, p_supplier_site_id NUMBER
, p_source_organization_id NUMBER
, p_source_subinventory VARCHAR2
, p_source_locator_id NUMBER
, p_wip_line_id NUMBER
, p_current_replnsh_cycle_id IN OUT NOCOPY NUMBER
, p_document_type NUMBER
, p_document_header_id NUMBER
, p_document_detail_id NUMBER
, p_error_code NUMBER
, p_attribute_category VARCHAR2
, p_attribute1 VARCHAR2
, p_attribute2 VARCHAR2
, p_attribute3 VARCHAR2
, p_attribute4 VARCHAR2
, p_attribute5 VARCHAR2
, p_attribute6 VARCHAR2
, p_attribute7 VARCHAR2
, p_attribute8 VARCHAR2
, p_attribute9 VARCHAR2
, p_attribute10 VARCHAR2
, p_attribute11 VARCHAR2
, p_attribute12 VARCHAR2
, p_attribute13 VARCHAR2
, p_attribute14 VARCHAR2
, p_attribute15 VARCHAR2
, p_lot_item_id NUMBER DEFAULT NULL
, p_lot_number VARCHAR2 DEFAULT NULL
, p_lot_item_revision VARCHAR2 DEFAULT NULL
, p_lot_subinventory_code VARCHAR2 DEFAULT NULL
, p_lot_location_id NUMBER DEFAULT NULL
, p_lot_quantity NUMBER DEFAULT NULL
, p_replenish_quantity NUMBER DEFAULT NULL
, p_need_by_date DATE DEFAULT NULL
, p_source_wip_entity_id NUMBER DEFAULT NULL
) IS
l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
mydebug('Inside update_row 2');
l_kanban_card_rec.last_update_date := p_last_update_date;
l_kanban_card_rec.last_updated_by := p_last_updated_by;
l_kanban_card_rec.last_update_login := p_last_update_login;
UPDATE mtl_kanban_cards
SET pull_sequence_id = l_kanban_card_rec.pull_sequence_id
, inventory_item_id = l_kanban_card_rec.inventory_item_id
, organization_id = l_kanban_card_rec.organization_id
, subinventory_name = l_kanban_card_rec.subinventory_name
, supply_status = l_kanban_card_rec.supply_status
, card_status = l_kanban_card_rec.card_status
, kanban_card_type = l_kanban_card_rec.kanban_card_type
, source_type = l_kanban_card_rec.source_type
, kanban_size = l_kanban_card_rec.kanban_size
, last_update_date = l_kanban_card_rec.last_update_date
, last_updated_by = l_kanban_card_rec.last_updated_by
, creation_date = l_kanban_card_rec.creation_date
, created_by = l_kanban_card_rec.created_by
, last_update_login = l_kanban_card_rec.last_update_login
, last_print_date = l_kanban_card_rec.last_print_date
, locator_id = l_kanban_card_rec.locator_id
, supplier_id = l_kanban_card_rec.supplier_id
, supplier_site_id = l_kanban_card_rec.supplier_site_id
, source_organization_id = l_kanban_card_rec.source_organization_id
, source_subinventory = l_kanban_card_rec.source_subinventory
, source_locator_id = l_kanban_card_rec.source_locator_id
, wip_line_id = l_kanban_card_rec.wip_line_id
, current_replnsh_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id
, ERROR_CODE = l_kanban_card_rec.ERROR_CODE
, attribute_category = l_kanban_card_rec.attribute_category
, attribute1 = l_kanban_card_rec.attribute1
, attribute2 = l_kanban_card_rec.attribute2
, attribute3 = l_kanban_card_rec.attribute3
, attribute4 = l_kanban_card_rec.attribute4
, attribute5 = l_kanban_card_rec.attribute5
, attribute6 = l_kanban_card_rec.attribute6
, attribute7 = l_kanban_card_rec.attribute7
, attribute8 = l_kanban_card_rec.attribute8
, attribute9 = l_kanban_card_rec.attribute9
, attribute10 = l_kanban_card_rec.attribute10
, attribute11 = l_kanban_card_rec.attribute11
, attribute12 = l_kanban_card_rec.attribute12
, attribute13 = l_kanban_card_rec.attribute13
, attribute14 = l_kanban_card_rec.attribute14
, attribute15 = l_kanban_card_rec.attribute15
WHERE kanban_card_id = p_kanban_card_id;
insert_activity_for_card(l_kanban_card_rec);
-- Retained old update statement.
mydebug(' Updating the document header id with :' ||
l_kanban_card_rec.document_header_id);
UPDATE mtl_kanban_card_activity
SET document_header_id = l_kanban_card_rec.document_header_id
WHERE
kanban_card_number = l_kanban_card_rec.kanban_card_number AND
supply_status = INV_KANBAN_PVT.G_Supply_Status_InProcess AND
document_type = INV_KANBAN_PVT.G_Doc_type_lot_job AND
replenishment_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id;
-- Added new update statement to update the kanban_size with p_replenish_quantity
mydebug(' Updating the document header id with :' || l_kanban_card_rec.document_header_id||
' kanban_size : '||p_replenish_quantity);
UPDATE mtl_kanban_card_activity
SET document_header_id = l_kanban_card_rec.document_header_id,
kanban_size = p_replenish_quantity
WHERE
kanban_card_number = l_kanban_card_rec.kanban_card_number AND
supply_status = INV_KANBAN_PVT.G_Supply_Status_InProcess AND
document_type = INV_KANBAN_PVT.G_Doc_type_lot_job AND
replenishment_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id;
fnd_msg_pub.add_exc_msg(g_pkg_name, 'update_row');
END update_row;
PROCEDURE update_row(p_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type) IS
l_return_status VARCHAR2(1);
mydebug('Inside update_row 1');
update_row(
x_return_status => l_return_status
, p_kanban_card_id => p_kanban_card_rec.kanban_card_id
, p_kanban_card_number => p_kanban_card_rec.kanban_card_number
, p_pull_sequence_id => p_kanban_card_rec.pull_sequence_id
, p_inventory_item_id => p_kanban_card_rec.inventory_item_id
, p_organization_id => p_kanban_card_rec.organization_id
, p_subinventory_name => p_kanban_card_rec.subinventory_name
, p_supply_status => l_supply_status
, p_card_status => l_card_status
, p_kanban_card_type => p_kanban_card_rec.kanban_card_type
, p_source_type => p_kanban_card_rec.source_type
, p_kanban_size => p_kanban_card_rec.kanban_size
, p_last_update_date => p_kanban_card_rec.last_update_date
, p_last_updated_by => p_kanban_card_rec.last_updated_by
, p_creation_date => p_kanban_card_rec.creation_date
, p_created_by => p_kanban_card_rec.created_by
, p_last_update_login => p_kanban_card_rec.last_update_login
, p_last_print_date => p_kanban_card_rec.last_print_date
, p_locator_id => p_kanban_card_rec.locator_id
, p_supplier_id => p_kanban_card_rec.supplier_id
, p_supplier_site_id => p_kanban_card_rec.supplier_site_id
, p_source_organization_id => p_kanban_card_rec.source_organization_id
, p_source_subinventory => p_kanban_card_rec.source_subinventory
, p_source_locator_id => p_kanban_card_rec.source_locator_id
, p_wip_line_id => p_kanban_card_rec.wip_line_id
, p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
, p_document_type => p_kanban_card_rec.document_type
, p_document_header_id => p_kanban_card_rec.document_header_id
, p_document_detail_id => p_kanban_card_rec.document_detail_id
, p_error_code => p_kanban_card_rec.ERROR_CODE
, p_attribute_category => p_kanban_card_rec.attribute_category
, p_attribute1 => p_kanban_card_rec.attribute1
, p_attribute2 => p_kanban_card_rec.attribute2
, p_attribute3 => p_kanban_card_rec.attribute3
, p_attribute4 => p_kanban_card_rec.attribute4
, p_attribute5 => p_kanban_card_rec.attribute5
, p_attribute6 => p_kanban_card_rec.attribute6
, p_attribute7 => p_kanban_card_rec.attribute7
, p_attribute8 => p_kanban_card_rec.attribute8
, p_attribute9 => p_kanban_card_rec.attribute9
, p_attribute10 => p_kanban_card_rec.attribute10
, p_attribute11 => p_kanban_card_rec.attribute11
, p_attribute12 => p_kanban_card_rec.attribute12
, p_attribute13 => p_kanban_card_rec.attribute13
, p_attribute14 => p_kanban_card_rec.attribute14
, p_attribute15 => p_kanban_card_rec.attribute15
, p_lot_item_id => p_kanban_card_rec.lot_item_id
, p_lot_number => p_kanban_card_rec.lot_number
, p_lot_subinventory_code => p_kanban_card_rec.lot_subinventory_code
, p_lot_item_revision => p_kanban_card_rec.lot_item_revision
, p_lot_location_id => p_kanban_card_rec.lot_location_id
, p_lot_quantity => p_kanban_card_rec.lot_quantity
, p_replenish_quantity => p_kanban_card_rec.replenish_quantity
, p_need_by_date => p_kanban_card_rec.need_by_date
, p_source_wip_entity_id => p_kanban_card_rec.source_wip_entity_id
);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'Update_Row');
END update_row;
PROCEDURE update_card_status(p_kanban_card_rec IN OUT NOCOPY inv_kanban_pvt.kanban_card_rec_type, p_card_status IN NUMBER) IS
l_return_status VARCHAR2(1);
update_row(
x_return_status => l_return_status
, p_kanban_card_id => p_kanban_card_rec.kanban_card_id
, p_kanban_card_number => p_kanban_card_rec.kanban_card_number
, p_pull_sequence_id => p_kanban_card_rec.pull_sequence_id
, p_inventory_item_id => p_kanban_card_rec.inventory_item_id
, p_organization_id => p_kanban_card_rec.organization_id
, p_subinventory_name => p_kanban_card_rec.subinventory_name
, p_supply_status => p_kanban_card_rec.supply_status
, p_card_status => l_card_status
, p_kanban_card_type => p_kanban_card_rec.kanban_card_type
, p_source_type => p_kanban_card_rec.source_type
, p_kanban_size => p_kanban_card_rec.kanban_size
, p_last_update_date => p_kanban_card_rec.last_update_date
, p_last_updated_by => p_kanban_card_rec.last_updated_by
, p_creation_date => p_kanban_card_rec.creation_date
, p_created_by => p_kanban_card_rec.created_by
, p_last_update_login => p_kanban_card_rec.last_update_login
, p_last_print_date => p_kanban_card_rec.last_print_date
, p_locator_id => p_kanban_card_rec.locator_id
, p_supplier_id => p_kanban_card_rec.supplier_id
, p_supplier_site_id => p_kanban_card_rec.supplier_site_id
, p_source_organization_id => p_kanban_card_rec.source_organization_id
, p_source_subinventory => p_kanban_card_rec.source_subinventory
, p_source_locator_id => p_kanban_card_rec.source_locator_id
, p_wip_line_id => p_kanban_card_rec.wip_line_id
, p_current_replnsh_cycle_id => p_kanban_card_rec.current_replnsh_cycle_id
, p_document_type => p_kanban_card_rec.document_type
, p_document_header_id => p_kanban_card_rec.document_header_id
, p_document_detail_id => p_kanban_card_rec.document_detail_id
, p_error_code => p_kanban_card_rec.ERROR_CODE
, p_attribute_category => p_kanban_card_rec.attribute_category
, p_attribute1 => p_kanban_card_rec.attribute1
, p_attribute2 => p_kanban_card_rec.attribute2
, p_attribute3 => p_kanban_card_rec.attribute3
, p_attribute4 => p_kanban_card_rec.attribute4
, p_attribute5 => p_kanban_card_rec.attribute5
, p_attribute6 => p_kanban_card_rec.attribute6
, p_attribute7 => p_kanban_card_rec.attribute7
, p_attribute8 => p_kanban_card_rec.attribute8
, p_attribute9 => p_kanban_card_rec.attribute9
, p_attribute10 => p_kanban_card_rec.attribute10
, p_attribute11 => p_kanban_card_rec.attribute11
, p_attribute12 => p_kanban_card_rec.attribute12
, p_attribute13 => p_kanban_card_rec.attribute13
, p_attribute14 => p_kanban_card_rec.attribute14
, p_attribute15 => p_kanban_card_rec.attribute15
);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'Update_Card_Status');
END update_card_status;
PROCEDURE delete_row(x_return_status OUT NOCOPY VARCHAR2, p_kanban_card_id NUMBER) IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
DELETE FROM mtl_kanban_cards
WHERE kanban_card_id = p_kanban_card_id;
delete_activity_for_card(p_kanban_card_id);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Row');
END delete_row;
PROCEDURE insert_activity_for_card(p_kanban_card_rec IN inv_kanban_pvt.kanban_card_rec_type) IS
BEGIN
INSERT INTO mtl_kanban_card_activity
(
kanban_activity_id
, replenishment_cycle_id
, kanban_card_id
, kanban_card_number
, inventory_item_id
, organization_id
, subinventory_name
, supply_status
, card_status
, kanban_card_type
, source_type
, kanban_size
, last_update_date
, last_updated_by
, creation_date
, created_by
, locator_id
, supplier_id
, supplier_site_id
, source_organization_id
, source_subinventory
, source_locator_id
, wip_line_id
, document_type
, document_header_id
, document_detail_id
, ERROR_CODE
, last_update_login
, last_print_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, request_id
, program_application_id
, program_id
, program_update_date
, source_wip_entity_id
)
VALUES (
mtl_kanban_card_activity_s.NEXTVAL
, NVL(p_kanban_card_rec.current_replnsh_cycle_id, -1)
, p_kanban_card_rec.kanban_card_id
, p_kanban_card_rec.kanban_card_number
, p_kanban_card_rec.inventory_item_id
, p_kanban_card_rec.organization_id
, p_kanban_card_rec.subinventory_name
, p_kanban_card_rec.supply_status
, p_kanban_card_rec.card_status
, p_kanban_card_rec.kanban_card_type
, p_kanban_card_rec.source_type
, NVL(p_kanban_card_rec.replenish_quantity, p_kanban_card_rec.kanban_size)
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, p_kanban_card_rec.locator_id
, p_kanban_card_rec.supplier_id
, p_kanban_card_rec.supplier_site_id
, p_kanban_card_rec.source_organization_id
, p_kanban_card_rec.source_subinventory
, p_kanban_card_rec.source_locator_id
, p_kanban_card_rec.wip_line_id
, p_kanban_card_rec.document_type
, p_kanban_card_rec.document_header_id
, p_kanban_card_rec.document_detail_id
, p_kanban_card_rec.ERROR_CODE
, fnd_global.login_id
, p_kanban_card_rec.last_print_date
, p_kanban_card_rec.attribute_category
, p_kanban_card_rec.attribute1
, p_kanban_card_rec.attribute2
, p_kanban_card_rec.attribute3
, p_kanban_card_rec.attribute4
, p_kanban_card_rec.attribute5
, p_kanban_card_rec.attribute6
, p_kanban_card_rec.attribute7
, p_kanban_card_rec.attribute8
, p_kanban_card_rec.attribute9
, p_kanban_card_rec.attribute10
, p_kanban_card_rec.attribute11
, p_kanban_card_rec.attribute12
, p_kanban_card_rec.attribute13
, p_kanban_card_rec.attribute14
, p_kanban_card_rec.attribute15
, p_kanban_card_rec.request_id
, p_kanban_card_rec.program_application_id
, p_kanban_card_rec.program_id
, p_kanban_card_rec.program_update_date
, p_kanban_card_rec.source_wip_entity_id
);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'Insert_Activity_For_Card');
END insert_activity_for_card;
PROCEDURE delete_cards_for_pull_seq(p_pull_sequence_id NUMBER) IS
BEGIN
inv_kanbancard_pkg.delete_activity_for_pull_seq(p_pull_sequence_id);
DELETE FROM mtl_kanban_cards
WHERE pull_sequence_id = p_pull_sequence_id;
fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Cards_For_Pull_Seq');
END delete_cards_for_pull_seq;
PROCEDURE delete_activity_for_card(p_kanban_card_id NUMBER) IS
BEGIN
DELETE FROM mtl_kanban_card_activity
WHERE kanban_card_id = p_kanban_card_id;
fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Activity_For_Card');
END delete_activity_for_card;
PROCEDURE delete_activity_for_pull_seq(p_pull_sequence_id NUMBER) IS
BEGIN
DELETE FROM mtl_kanban_card_activity act
WHERE EXISTS(SELECT 'x'
FROM mtl_kanban_cards crd
WHERE crd.kanban_card_id = act.kanban_card_id
AND crd.pull_sequence_id = p_pull_sequence_id);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Activity_For_Pull_Seq');
END delete_activity_for_pull_seq;