[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_date date := sysdate;
SELECT mkps.pull_sequence_id
FROM mtl_kanban_pull_sequences mkps
WHERE mkps.inventory_item_id = p_invitem_id
AND mkps.organization_id = p_org_id
AND mkps.kanban_plan_id = NVL(p_kan_plan_id,mkps.kanban_plan_id)
AND TRIM ( mkps.subinventory_name ) = TRIM( p_subinv_name )
AND NVL ( mkps.locator_id,-1 ) = NVL ( p_loc_id,-1 );
select 1
into l_exist
from mtl_kanban_pull_sequences
where pull_sequence_id = p_pull_seq_id;
select kanban_card_id
,kanban_card_number
into l_card_id
,l_card_number
from mtl_kanban_cards
where pull_sequence_id = p_pull_sequence_id
and kanban_card_id = NVL(p_kanban_card_id,kanban_card_id)
and kanban_card_number = NVL(p_kanban_card_number,kanban_card_number);
SELECT 1
INTO l_exist
FROM fnd_lookup_types FLT
, fnd_lookup_values FLV
WHERE FLT.lookup_type = p_lookup_type
AND FLV.lookup_type = FLT.lookup_type
AND FLV.lookup_code = p_lookup_code
AND FLV.language = UserEnv('LANG');
SELECT 1
INTO l_exist
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id;
SELECT 1
INTO l_exist
FROM mtl_item_locations mil
WHERE mil.subinventory_code = p_subinv_name
AND mil.inventory_location_id = p_locator_id
AND mil.organization_id = p_org_id;
SELECT mp.stock_locator_control_code,
mss.locator_type,
msi.location_control_code
FROM mtl_parameters mp,
mtl_secondary_inventories mss,
mtl_system_items_b msi
WHERE mp.organization_id = p_organization_id
AND mss.secondary_inventory_name = p_subinventory_name
AND mss.organization_id = p_organization_id
AND msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id;
PROCEDURE insert_supplier_row(x_return_status OUT NOCOPY Varchar2,
p_pull_sequence_id NUMBER,
p_organization_id NUMBER,
p_supplier_id NUMBER,
p_supplier_site_id NUMBER,
p_sourcing_percentage NUMBER,
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_login NUMBER)
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
mydebug('Inserting into MTL_PULL_SEQ_SUPPLIERS table.');
INSERT INTO MTL_PULL_SEQ_SUPPLIERS
(pull_sequence_id,
organization_id,
supplier_id,
supplier_site_id,
sourcing_percentage,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES
(p_pull_sequence_id,
p_organization_id,
p_supplier_id,
p_supplier_site_id,
p_sourcing_percentage,
p_last_update_date,
p_last_updated_by,
p_creation_date,
p_created_by,
p_last_update_login);
, 'insert_supplier_row'
);
END insert_supplier_row;
PROCEDURE insert_supplier_row(p_supplier_rec IN MTL_PULL_SEQ_SUPPLIERS%ROWTYPE)
IS
l_supplier_rec MTL_PULL_SEQ_SUPPLIERS%ROWTYPE := p_supplier_rec;
mydebug('In procedure insert_supplier_row.');
insert_supplier_row(
x_return_status => l_return_status,
p_pull_sequence_id => l_supplier_rec.pull_sequence_id,
p_organization_id => l_supplier_rec.organization_id,
p_supplier_id => l_supplier_rec.supplier_id,
p_supplier_site_id => l_supplier_rec.supplier_site_id,
p_sourcing_percentage => l_supplier_rec.sourcing_percentage,
p_last_update_date => l_supplier_rec.last_update_date,
p_last_updated_by => l_supplier_rec.last_updated_by,
p_creation_date => l_supplier_rec.creation_date,
p_created_by => l_supplier_rec.created_by,
p_last_update_login => l_supplier_rec.last_update_login);
, 'insert_supplier_row'
);
END insert_supplier_row;
PROCEDURE update_supplier_row(x_return_status OUT NOCOPY Varchar2,
p_pull_sequence_id NUMBER,
p_supplier_id NUMBER,
p_supplier_site_id NUMBER,
p_sourcing_percentage NUMBER,
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER)
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
UPDATE MTL_PULL_SEQ_SUPPLIERS
SET supplier_site_id = p_supplier_site_id,
sourcing_percentage = p_sourcing_percentage,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
WHERE pull_sequence_id = p_pull_sequence_id
AND supplier_id = p_supplier_id;
, 'update_supplier_row'
);
END update_supplier_row;
PROCEDURE update_supplier_row(p_supplier_rec IN MTL_PULL_SEQ_SUPPLIERS%ROWTYPE)
IS
l_supplier_rec MTL_PULL_SEQ_SUPPLIERS%ROWTYPE := p_supplier_rec;
mydebug('In procedure update_supplier_row.');
update_supplier_row(x_return_status => l_return_status,
p_pull_sequence_id => l_supplier_rec.pull_sequence_id,
p_supplier_id => l_supplier_rec.supplier_id,
p_supplier_site_id => l_supplier_rec.supplier_site_id,
p_sourcing_percentage => l_supplier_rec.sourcing_percentage,
p_last_update_date => l_supplier_rec.last_update_date,
p_last_updated_by => l_supplier_rec.last_updated_by,
p_last_update_login => l_supplier_rec.last_update_login);
, 'update_supplier_row'
);
END update_supplier_row;
PROCEDURE delete_supplier_row(x_return_status OUT NOCOPY VARCHAR2,
p_pull_sequence_id NUMBER,
p_supplier_id NUMBER)
IS
l_return_status varchar2(1) := FND_API.G_RET_STS_ERROR;
mydebug('In procedure delete_supplier_row.');
DELETE FROM MTL_PULL_SEQ_SUPPLIERS
WHERE pull_sequence_id = p_pull_sequence_id
AND supplier_id = p_supplier_id;
, 'delete_supplier_row'
);
END delete_supplier_row;
SELECT mkps.inventory_item_id
,mkps.organization_id
,mkps.kanban_plan_id
,mkps.subinventory_name
,mkps.locator_id
-- ,mkps.supplier_id
-- ,mkps.supplier_site_id
,mkps.source_organization_id
,mkps.source_subinventory
,mkps.source_locator_id
,mkps.source_type
,mkps.kanban_size
,mkps.calculate_kanban_flag
,mkps.number_of_cards
,mkps.release_kanban_flag
FROM mtl_kanban_pull_sequences mkps
WHERE mkps.inventory_item_id = p_invitem_id
AND mkps.organization_id = p_org_id
AND mkps.kanban_plan_id = p_kan_plan_id
AND trim ( mkps.subinventory_name ) = trim( p_sub_name )
AND nvl ( mkps.locator_id,-1 ) = nvl ( p_loc_id,-1 );*/
SELECT msi.inventory_item_id
,msi.planning_make_buy_code
,msi.purchasing_enabled_flag
,msi.internal_order_enabled_flag
,msi.mtl_transactions_enabled_flag
,msi.purchasing_item_flag
,msi.reservable_type
,msi.shippable_item_flag
,msi.returnable_flag
,msi.so_transactions_flag
,msi.restrict_subinventories_code
,msi.effectivity_control
,msi.inventory_item_flag
,msi.stock_enabled_flag
,msi.inventory_item_status_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id;
SELECT misi.secondary_inventory_name
FROM mtl_secondary_inventories misi
WHERE misi.secondary_inventory_name = p_subinv_name
AND ( misi.disable_date > SYSDATE
OR misi.disable_date IS NULL )
AND misi.organization_id = p_org_id;
SELECT NVL(p_kanban_size, mkps.kanban_size) kanban_size
,NVL(p_calculate_kanban_flag, mkps.calculate_kanban_flag) calculate_kanban_flag
,NVL(p_number_of_cards, mkps.number_of_cards) number_of_cards
,NVL(p_release_kanban_flag, mkps.release_kanban_flag) release_kanban_flag
,NVL(p_source_subinventory, mkps.source_subinventory) source_subinventory
,mkps.source_organization_id
,mkps.source_locator_id
FROM mtl_kanban_pull_sequences mkps
WHERE mkps.pull_sequence_id = p_pull_sequence_id;
FLM_KANBAN_MASSLOAD.kanban_delete)
then
FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
FLM_KANBAN_MASSLOAD.kanban_delete) THEN
IF p_pull_seq_rec.pull_sequence_id IS NULL THEN
l_pullseq_id := get_pull_sequence_id(p_pull_seq_rec.inventory_item_id,
p_pull_seq_rec.organization_id,
FLM_KANBAN_MASSLOAD.G_PRODUCTION_KANBAN,
p_pull_seq_rec.subinventory_name,
p_pull_seq_rec.locator_id);
SELECT
mkps.pull_sequence_id
,mkps.creation_date
,mkps.created_by
,mkps.inventory_item_id
,mkps.organization_id
,mkps.subinventory_name
,mkps.locator_id
,decode(p_pull_seq_rec.source_type,null,mkps.source_type,FND_API.G_MISS_NUM,null,p_pull_seq_rec.source_type)
,decode(p_pull_seq_rec.supplier_id,null,mkps.supplier_id,FND_API.G_MISS_NUM,null,p_pull_seq_rec.supplier_id)
,decode(p_pull_seq_rec.supplier_site_id,null,mkps.supplier_site_id,FND_API.G_MISS_NUM,null,p_pull_seq_rec.supplier_site_id)
,decode(p_pull_seq_rec.source_organization_id,null,mkps.source_organization_id,FND_API.G_MISS_NUM,null,p_pull_seq_rec.source_organization_id)
,decode(p_pull_seq_rec.source_subinventory,null,mkps.source_subinventory,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.source_subinventory)
,decode(p_pull_seq_rec.source_locator_id,null,mkps.source_locator_id,FND_API.G_MISS_NUM,null,p_pull_seq_rec.source_locator_id)
,decode(p_pull_seq_rec.wip_line_id,null,mkps.wip_line_id,FND_API.G_MISS_NUM,null,p_pull_seq_rec.wip_line_id)
,decode(p_pull_seq_rec.replenishment_lead_time,null,mkps.replenishment_lead_time,FND_API.G_MISS_NUM,null,p_pull_seq_rec.replenishment_lead_time)
,decode(p_pull_seq_rec.calculate_kanban_flag,null,mkps.calculate_kanban_flag,FND_API.G_MISS_NUM,null,p_pull_seq_rec.calculate_kanban_flag)
,decode(p_pull_seq_rec.kanban_size,null,mkps.kanban_size,FND_API.G_MISS_NUM,null,p_pull_seq_rec.kanban_size)
,decode(p_pull_seq_rec.fixed_lot_multiplier,null,mkps.fixed_lot_multiplier,FND_API.G_MISS_NUM,null,p_pull_seq_rec.fixed_lot_multiplier)
,decode(p_pull_seq_rec.safety_stock_days,null,mkps.safety_stock_days,FND_API.G_MISS_NUM,null,p_pull_seq_rec.safety_stock_days)
,decode(p_pull_seq_rec.number_of_cards,null,mkps.number_of_cards,FND_API.G_MISS_NUM,null,p_pull_seq_rec.number_of_cards)
,decode(p_pull_seq_rec.minimum_order_quantity,null,mkps.minimum_order_quantity,FND_API.G_MISS_NUM,null,p_pull_seq_rec.minimum_order_quantity)
,decode(p_pull_seq_rec.aggregation_type,null,mkps.aggregation_type,FND_API.G_MISS_NUM,null,p_pull_seq_rec.aggregation_type)
,decode(p_pull_seq_rec.allocation_percent,null,mkps.allocation_percent,FND_API.G_MISS_NUM,null,p_pull_seq_rec.allocation_percent)
,decode(p_pull_seq_rec.release_kanban_flag,null,mkps.release_kanban_flag,FND_API.G_MISS_NUM,null,p_pull_seq_rec.release_kanban_flag)
,decode(p_pull_seq_rec.attribute_category,null,mkps.attribute_category,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute_category)
,decode(p_pull_seq_rec.attribute1,null,mkps.attribute1,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute1)
,decode(p_pull_seq_rec.attribute2,null,mkps.attribute2,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute2)
,decode(p_pull_seq_rec.attribute3,null,mkps.attribute3,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute3)
,decode(p_pull_seq_rec.attribute4,null,mkps.attribute4,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute4)
,decode(p_pull_seq_rec.attribute5,null,mkps.attribute5,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute5)
,decode(p_pull_seq_rec.attribute6,null,mkps.attribute6,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute6)
,decode(p_pull_seq_rec.attribute7,null,mkps.attribute7,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute7)
,decode(p_pull_seq_rec.attribute8,null,mkps.attribute8,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute8)
,decode(p_pull_seq_rec.attribute9,null,mkps.attribute9,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute9)
,decode(p_pull_seq_rec.attribute10,null,mkps.attribute10,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute10)
,decode(p_pull_seq_rec.attribute11,null,mkps.attribute11,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute11)
,decode(p_pull_seq_rec.attribute12,null,mkps.attribute12,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute12)
,decode(p_pull_seq_rec.attribute13,null,mkps.attribute13,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute13)
,decode(p_pull_seq_rec.attribute14,null,mkps.attribute14,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute14)
,decode(p_pull_seq_rec.attribute15,null,mkps.attribute15,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute15)
,decode(p_pull_seq_rec.auto_request,null,mkps.auto_request,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.auto_request)
,decode(p_pull_seq_rec.auto_allocate_flag,null,mkps.auto_allocate_flag,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.auto_allocate_flag)
,decode(p_pull_seq_rec.replenishment_type,null,mkps.replenishment_type,FND_API.G_MISS_NUM,null,p_pull_seq_rec.replenishment_type)
,decode(p_pull_seq_rec.consolidation,null,mkps.consolidation,FND_API.G_MISS_NUM,null,p_pull_seq_rec.consolidation)
,decode(p_pull_seq_rec.consolidation_group,null,mkps.consolidation_group,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.consolidation_group)
,decode(p_pull_seq_rec.future_card_size,null,mkps.future_card_size,FND_API.G_MISS_NUM,null,p_pull_seq_rec.future_card_size)
,decode(p_pull_seq_rec.future_no_of_cards,null,mkps.future_no_of_cards,FND_API.G_MISS_NUM,null,p_pull_seq_rec.future_no_of_cards)
,decode(p_pull_seq_rec.planning_effectivity,null,mkps.planning_effectivity,FND_API.G_MISS_DATE,null,p_pull_seq_rec.planning_effectivity)
,decode(p_pull_seq_rec.avg_dependent_demand,null,mkps.avg_dependent_demand,FND_API.G_MISS_NUM,null,p_pull_seq_rec.avg_dependent_demand)
,decode(p_pull_seq_rec.avg_independent_demand,null,mkps.avg_independent_demand,FND_API.G_MISS_NUM,null,p_pull_seq_rec.avg_independent_demand)
,mkps.kanban_size
,mkps.number_of_cards
INTO
p_pull_seq_rec.pull_sequence_id
,p_pull_seq_rec.creation_date
,p_pull_seq_rec.created_by
,p_pull_seq_rec.inventory_item_id
,p_pull_seq_rec.organization_id
,p_pull_seq_rec.subinventory_name
,p_pull_seq_rec.locator_id
,p_pull_seq_rec.source_type
,p_pull_seq_rec.supplier_id
,p_pull_seq_rec.supplier_site_id
,p_pull_seq_rec.source_organization_id
,p_pull_seq_rec.source_subinventory
,p_pull_seq_rec.source_locator_id
,p_pull_seq_rec.wip_line_id
,p_pull_seq_rec.replenishment_lead_time
,p_pull_seq_rec.calculate_kanban_flag
,p_pull_seq_rec.kanban_size
,p_pull_seq_rec.fixed_lot_multiplier
,p_pull_seq_rec.safety_stock_days
,p_pull_seq_rec.number_of_cards
,p_pull_seq_rec.minimum_order_quantity
,p_pull_seq_rec.aggregation_type
,p_pull_seq_rec.allocation_percent
,p_pull_seq_rec.release_kanban_flag
,p_pull_seq_rec.attribute_category
,p_pull_seq_rec.attribute1
,p_pull_seq_rec.attribute2
,p_pull_seq_rec.attribute3
,p_pull_seq_rec.attribute4
,p_pull_seq_rec.attribute5
,p_pull_seq_rec.attribute6
,p_pull_seq_rec.attribute7
,p_pull_seq_rec.attribute8
,p_pull_seq_rec.attribute9
,p_pull_seq_rec.attribute10
,p_pull_seq_rec.attribute11
,p_pull_seq_rec.attribute12
,p_pull_seq_rec.attribute13
,p_pull_seq_rec.attribute14
,p_pull_seq_rec.attribute15
,p_pull_seq_rec.auto_request
,p_pull_seq_rec.auto_allocate_flag
,p_pull_seq_rec.replenishment_type
,p_pull_seq_rec.consolidation
,p_pull_seq_rec.consolidation_group
,p_pull_seq_rec.future_card_size
,p_pull_seq_rec.future_no_of_cards
,p_pull_seq_rec.planning_effectivity
,p_pull_seq_rec.avg_dependent_demand
,p_pull_seq_rec.avg_independent_demand
,l_kanban_size
,l_no_of_cards
FROM mtl_kanban_pull_sequences mkps
WHERE pull_sequence_id = l_pullseq_id;
--if item is not purchase enabled and Supplier source type selected
IF (NVL(lr_inv_item.purchasing_enabled_flag, 'N' ) <> 'Y') THEN
FND_MESSAGE.SET_NAME ( 'FLM' , 'FLM_PURCHASE_ENABLED_ITEM' );
--if the item is not transactable and source_type 'Intra-Org' is selected
IF (NVL(lr_inv_item.mtl_transactions_enabled_flag, 'N' ) <> 'Y') THEN
FND_MESSAGE.SET_NAME ( 'INV' , 'INV_ENTER_TRANSACT_ITEM' );
--if item is not production enabled and production source type selected
IF (NVL(lr_inv_item.planning_make_buy_code, 0 ) <> 1 ) THEN
FND_MESSAGE.SET_NAME ( 'INV' , 'INV_ENTER_MAKE_ITEM' );
During update via interface, if only supplier name is populated and
if the existing pull sequence has the supplier, new supplier
id will not be defaulted because there is a logic in the above code that
defaulted the supplier id from existing pull sequence if supplier id
is null in interface. Not fixing it currently, because
the code change might be a lot and we are almost at the end of development
cycle. In addition, the data model for supplier table is WRONG and update
will not work because if supplier_id is the PRIMARY KEY in the table,
there won't be a way to update supplier of existing pull sequence*/
-- Supplier defaulting for pull sequence level
if l_source_type = inv_kanban_pvt.G_Source_Type_Supplier then
-- check if multiple suppliers exists for pull seq
-- and updating supplier details at pull seq level
if l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_change then
DECLARE
l_count number;
SELECT COUNT(*)
INTO l_count
FROM MTL_PULL_SEQ_SUPPLIERS
WHERE pull_sequence_id = p_pull_seq_rec.pull_sequence_id;
r_pull_seq_rec.last_update_date := p_pull_sequence_tbl(i).last_update_date;
r_pull_seq_rec.last_updated_by := p_pull_sequence_tbl(i).last_updated_by;
r_pull_seq_rec.last_update_login := p_pull_sequence_tbl(i).last_update_login;
t_supplier_tbl.DELETE;
SELECT msi.inventory_item_id
,msi.planning_make_buy_code
,msi.purchasing_enabled_flag
,msi.internal_order_enabled_flag
,msi.mtl_transactions_enabled_flag
,msi.purchasing_item_flag
,msi.reservable_type
,msi.shippable_item_flag
,msi.returnable_flag
,msi.so_transactions_flag
,msi.restrict_subinventories_code
,msi.effectivity_control
,msi.inventory_item_flag
,msi.stock_enabled_flag
,msi.inventory_item_status_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id;
SELECT misi.secondary_inventory_name
FROM mtl_secondary_inventories misi
WHERE misi.secondary_inventory_name = p_subinv_name
AND (misi.disable_date > SYSDATE
OR misi.disable_date IS NULL)
AND misi.organization_id = p_org_id;
SELECT MKPS.organization_id
,MKPS.inventory_item_id
,MKPS.subinventory_name
,MKPS.locator_id
,MKPS.source_type
,MKPS.kanban_size
,MKPS.supplier_id
,MKPS.supplier_site_id
,MKPS.source_organization_id
,MKPS.source_subinventory
,MKPS.source_locator_id
,MKPS.wip_line_id
,MKPS.number_of_cards
,MKPS.future_no_of_cards
,MKPS.release_kanban_flag
FROM mtl_kanban_pull_sequences MKPS
WHERE pull_sequence_id = p_pullseq_id;
SELECT COUNT(MKC.kanban_card_id)
FROM MTL_KANBAN_CARDS MKC
WHERE MKC.pull_sequence_id = p_pull_sequence_id
AND MKC.card_status IN (INV_Kanban_PVT.G_Card_Status_Active, INV_Kanban_PVT.G_Card_Status_Hold);
SELECT kanban_card_id
,card_status
,supply_status
FROM MTL_KANBAN_CARDS MTC
WHERE MTC.kanban_card_id = p_kanban_card_id;
FLM_KANBAN_MASSLOAD.kanban_delete)
THEN
fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_DELETE THEN
--Fix bug 12430551, derive pull sequence id if it's null
IF r_kanban_card_rec.pull_sequence_id IS NULL THEN
r_kanban_card_rec.pull_sequence_id := get_pull_sequence_id(p_invitem_id => r_kanban_card_rec.inventory_item_id
,p_org_id => r_kanban_card_rec.organization_id
,p_kan_plan_id => NULL
,p_subinv_name => r_kanban_card_rec.subinventory_name
,p_loc_id => r_kanban_card_rec.locator_id
);
IF l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_DELETE THEN
/*IF l_supply_sts <> INV_Kanban_PVT.G_Supply_Status_New THEN
fnd_message.set_name ('FLM','FLM_CANNOT_DELETE_CARD');
fnd_message.set_name ('FLM','FLM_CANNOT_DELETE_CARD');
SELECT MKC.kanban_card_id
,MKC.kanban_card_number
,MKC.pull_sequence_id
,MKC.inventory_item_id
,MKC.organization_id
,MKC.subinventory_name
,MKC.locator_id
,MKC.kanban_card_type
,MKC.kanban_size
,MKC.source_type
,DECODE(r_kanban_card_rec.supply_status,NULL,MKC.supply_status,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.supply_status)
,DECODE(r_kanban_card_rec.card_status,NULL,MKC.card_status,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.card_status)
,MKC.creation_date
,MKC.created_by
,DECODE(r_kanban_card_rec.supplier_id,NULL,MKC.supplier_id,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.supplier_id)
,DECODE(r_kanban_card_rec.supplier_site_id,NULL,MKC.supplier_site_id,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.supplier_site_id)
,DECODE(r_kanban_card_rec.source_organization_id,NULL,MKC.source_organization_id,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.source_organization_id)
,DECODE(r_kanban_card_rec.source_subinventory,NULL,MKC.source_subinventory,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.source_subinventory)
,DECODE(r_kanban_card_rec.source_locator_id,NULL,MKC.source_locator_id,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.source_locator_id)
,MKC.current_replnsh_cycle_id
,DECODE(r_kanban_card_rec.kanban_error_code,NULL,MKC.error_code,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.kanban_error_code)
,DECODE(r_kanban_card_rec.wip_line_id,NULL,MKC.wip_line_id,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.wip_line_id)
,MKC.replenishment_count
,DECODE(r_kanban_card_rec.max_replenishments,NULL,MKC.max_replenishments,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.max_replenishments)
,DECODE(r_kanban_card_rec.disable_date,NULL,MKC.disable_date,FND_API.G_MISS_DATE,NULL,r_kanban_card_rec.disable_date)
,DECODE(r_kanban_card_rec.replacement_flag,NULL,MKC.replacement_flag,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.replacement_flag)
,DECODE(r_kanban_card_rec.attribute_category,NULL,MKC.attribute_category,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute_category)
,DECODE(r_kanban_card_rec.attribute1,NULL,MKC.attribute1,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute1)
,DECODE(r_kanban_card_rec.attribute2,NULL,MKC.attribute2,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute2)
,DECODE(r_kanban_card_rec.attribute3,NULL,MKC.attribute3,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute3)
,DECODE(r_kanban_card_rec.attribute4,NULL,MKC.attribute4,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute4)
,DECODE(r_kanban_card_rec.attribute5,NULL,MKC.attribute5,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute5)
,DECODE(r_kanban_card_rec.attribute6,NULL,MKC.attribute6,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute6)
,DECODE(r_kanban_card_rec.attribute7,NULL,MKC.attribute7,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute7)
,DECODE(r_kanban_card_rec.attribute8,NULL,MKC.attribute8,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute8)
,DECODE(r_kanban_card_rec.attribute9,NULL,MKC.attribute9,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute9)
,DECODE(r_kanban_card_rec.attribute10,NULL,MKC.attribute10,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute10)
,DECODE(r_kanban_card_rec.attribute11,NULL,MKC.attribute11,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute11)
,DECODE(r_kanban_card_rec.attribute12,NULL,MKC.attribute12,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute12)
,DECODE(r_kanban_card_rec.attribute13,NULL,MKC.attribute13,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute13)
,DECODE(r_kanban_card_rec.attribute14,NULL,MKC.attribute14,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute14)
,DECODE(r_kanban_card_rec.attribute15,NULL,MKC.attribute15,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute15)
,MKC.last_print_date
INTO r_kanban_card_rec.kanban_card_id
,r_kanban_card_rec.kanban_card_number
,r_kanban_card_rec.pull_sequence_id
,r_kanban_card_rec.inventory_item_id
,r_kanban_card_rec.organization_id
,r_kanban_card_rec.subinventory_name
,r_kanban_card_rec.locator_id
,r_kanban_card_rec.kanban_card_type
,r_kanban_card_rec.kanban_size
,r_kanban_card_rec.source_type
,r_kanban_card_rec.supply_status
,r_kanban_card_rec.card_status
,r_kanban_card_rec.creation_date
,r_kanban_card_rec.created_by
,r_kanban_card_rec.supplier_id
,r_kanban_card_rec.supplier_site_id
,r_kanban_card_rec.source_organization_id
,r_kanban_card_rec.source_subinventory
,r_kanban_card_rec.source_locator_id
,r_kanban_card_rec.current_replnsh_cycle_id
,r_kanban_card_rec.kanban_error_code
,r_kanban_card_rec.wip_line_id
,r_kanban_card_rec.replenishment_count
,r_kanban_card_rec.max_replenishments
,r_kanban_card_rec.disable_date
,r_kanban_card_rec.replacement_flag
,r_kanban_card_rec.attribute_category
,r_kanban_card_rec.attribute1
,r_kanban_card_rec.attribute2
,r_kanban_card_rec.attribute3
,r_kanban_card_rec.attribute4
,r_kanban_card_rec.attribute5
,r_kanban_card_rec.attribute6
,r_kanban_card_rec.attribute7
,r_kanban_card_rec.attribute8
,r_kanban_card_rec.attribute9
,r_kanban_card_rec.attribute10
,r_kanban_card_rec.attribute11
,r_kanban_card_rec.attribute12
,r_kanban_card_rec.attribute13
,r_kanban_card_rec.attribute14
,r_kanban_card_rec.attribute15
,r_kanban_card_rec.last_print_date
FROM MTL_KANBAN_CARDS MKC
WHERE MKC.kanban_card_id = l_card_id;
fnd_message.set_name ('FLM','FLM_CANNOT_UPDATE_CARD');
SELECT count(distinct replenishment_cycle_id)
INTO l_max_replenishments
FROM mtl_kanban_card_activity
WHERE kanban_card_id = r_kanban_card_rec.kanban_card_id and
replenishment_cycle_id <> -1;
select 1 into l_temp
from mtl_kanban_cards
where organization_id = r_kanban_card_rec.organization_id
and kanban_card_number = r_kanban_card_rec.kanban_card_number;
INV_KanbanCard_PKG.Insert_Row(
X_Return_Status => l_ret_status,
P_Kanban_Card_Id => r_kanban_card_rec.kanban_card_id,
P_Kanban_Card_Number => r_kanban_card_rec.kanban_card_number,
P_Pull_Sequence_Id => r_kanban_card_rec.pull_sequence_id,
P_Inventory_item_id => r_kanban_card_rec.inventory_item_id,
P_Organization_id => r_kanban_card_rec.organization_id,
P_Subinventory_name => r_kanban_card_rec.subinventory_name,
P_Supply_Status => r_kanban_card_rec.supply_status,
P_Card_Status => r_kanban_card_rec.card_status,
P_Kanban_Card_Type => Nvl(r_kanban_card_rec.kanban_card_type,INV_Kanban_Pvt.g_card_type_replenishable),
P_Source_type => r_kanban_card_rec.Source_type,
P_Kanban_size => nvl(r_kanban_card_rec.Kanban_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 => r_kanban_card_rec.Locator_id,
P_Supplier_id => l_supplier_id,
P_Supplier_site_id => l_supp_site_id,
P_Source_Organization_id => r_kanban_card_rec.Source_Organization_id,
P_Source_Subinventory => r_kanban_card_rec.Source_Subinventory,
P_Source_Locator_id => r_kanban_card_rec.Source_Locator_id,
P_wip_line_id => r_kanban_card_rec.wip_line_id,
P_Current_Replnsh_Cycle_Id=> l_Current_Replnsh_Cycle_Id,
P_document_type => NULL,
P_document_header_id => NULL,
P_document_detail_id => NULL,
P_error_code => null,
P_Attribute_Category => r_kanban_card_rec.attribute_category ,
P_Attribute1 => r_kanban_card_rec.attribute1 ,
P_Attribute2 => r_kanban_card_rec.attribute2 ,
P_Attribute3 => r_kanban_card_rec.attribute3 ,
P_Attribute4 => r_kanban_card_rec.attribute4 ,
P_Attribute5 => r_kanban_card_rec.attribute5 ,
P_Attribute6 => r_kanban_card_rec.attribute6 ,
P_Attribute7 => r_kanban_card_rec.attribute7 ,
P_Attribute8 => r_kanban_card_rec.attribute8 ,
P_Attribute9 => r_kanban_card_rec.attribute9 ,
P_Attribute10 => r_kanban_card_rec.attribute10,
P_Attribute11 => r_kanban_card_rec.attribute11,
P_Attribute12 => r_kanban_card_rec.attribute12,
P_Attribute13 => r_kanban_card_rec.attribute13,
P_Attribute14 => r_kanban_card_rec.attribute14,
P_Attribute15 => r_kanban_card_rec.attribute15,
P_Request_Id => NULL,
P_Program_application_Id => NULL,
P_Program_Id => NULL,
P_Program_Update_date => NULL,
p_release_kanban_flag => l_release_kanban_flag,
--eKanban Changes
p_replenishment_count => r_kanban_card_rec.replenishment_count,
p_max_replenishments => r_kanban_card_rec.max_replenishments,
p_disable_date => r_kanban_card_rec.disable_date,
p_replacement_flag => r_kanban_card_rec.replacement_flag);
mydebug('Transaction Type UPDATE');
SELECT MTC.supply_status
,MTC.card_status
INTO l_supply_status, l_card_status
FROM MTL_KANBAN_CARDS MTC
WHERE MTC.kanban_card_id = r_kanban_card_rec.kanban_card_id;
mydebug('Calling FLM_KANBANCARD_PUB.UPDATE_CARD');
FLM_KANBANCARD_PUB.UPDATE_CARD(p_kanban_card_id => r_kanban_card_rec.kanban_card_id,
p_kanban_card_number => r_kanban_card_rec.kanban_card_number,
p_pull_sequence_id => r_kanban_card_rec.pull_sequence_id,
p_inventory_item_id => r_kanban_card_rec.inventory_item_id,
p_organization_id => r_kanban_card_rec.organization_id,
p_subinventory_name => r_kanban_card_rec.subinventory_name,
p_supply_status => l_supply_status,
p_supply_status_to => NVL(r_kanban_card_rec.supply_status,l_supply_status),
p_card_status => l_card_status,
p_card_status_to => NVL(r_kanban_card_rec.card_status,l_card_status),
p_kanban_card_type => r_kanban_card_rec.kanban_card_type,
p_source_type => r_kanban_card_rec.source_type,
p_kanban_size => r_kanban_card_rec.kanban_size,
p_last_update_date => r_kanban_card_rec.last_update_date,
p_last_updated_by => r_kanban_card_rec.last_update_by,
p_creation_date => r_kanban_card_rec.creation_date,
p_created_by => r_kanban_card_rec.created_by,
p_locator_id => r_kanban_card_rec.locator_id,
p_supplier_id => r_kanban_card_rec.supplier_id,
p_supplier_site_id => r_kanban_card_rec.supplier_site_id,
p_source_organization_id => r_kanban_card_rec.source_organization_id,
p_source_subinventory => r_kanban_card_rec.source_subinventory,
p_source_locator_id => r_kanban_card_rec.source_locator_id,
p_wip_line_id => r_kanban_card_rec.wip_line_id,
p_current_replnsh_cycle_id => r_kanban_card_rec.current_replnsh_cycle_id,
p_document_type => NULL,
p_document_header_id => NULL,
p_document_detail_id => NULL,
p_error_code => r_kanban_card_rec.kanban_error_code,
p_last_update_login => r_kanban_card_rec.last_update_login,
p_last_print_date => r_kanban_card_rec.last_print_date,
p_attribute_category => r_kanban_card_rec.attribute_category,
p_attribute1 => r_kanban_card_rec.attribute1,
p_attribute2 => r_kanban_card_rec.attribute2,
p_attribute3 => r_kanban_card_rec.attribute3,
p_attribute4 => r_kanban_card_rec.attribute4,
p_attribute5 => r_kanban_card_rec.attribute5,
p_attribute6 => r_kanban_card_rec.attribute6,
p_attribute7 => r_kanban_card_rec.attribute7,
p_attribute8 => r_kanban_card_rec.attribute8,
p_attribute9 => r_kanban_card_rec.attribute9,
p_attribute10 => r_kanban_card_rec.attribute10,
p_attribute11 => r_kanban_card_rec.attribute11,
p_attribute12 => r_kanban_card_rec.attribute12,
p_attribute13 => r_kanban_card_rec.attribute13,
p_attribute14 => r_kanban_card_rec.attribute14,
p_attribute15 => r_kanban_card_rec.attribute15,
p_request_id => r_kanban_card_rec.request_id,
p_program_application_id => r_kanban_card_rec.program_application_id,
p_program_id => r_kanban_card_rec.program_id,
p_program_update_date => r_kanban_card_rec.program_update_date,
p_lot_item_id => NULL,
p_lot_number => NULL,
p_lot_item_revision => NULL,
p_lot_subinventory_code => NULL,
p_lot_location_id => NULL,
p_lot_quantity => NULL,
p_replenish_quantity => NULL,
p_need_by_date => NULL,
p_source_wip_entity_id => NULL,
p_replenishment_count => r_kanban_card_rec.replenishment_count,
p_max_replenishments => r_kanban_card_rec.max_replenishments,
p_disable_date => r_kanban_card_rec.disable_date,
p_replacement_flag => r_kanban_card_rec.replacement_flag,
P_VERIFY => 'N',
X_RETCODE => l_ret_status,
X_RET_MSG_CODE => l_ret_msg_code,
X_ERR_MSG => l_error_msg);
ELSIF p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_DELETE THEN
mydebug('Transaction Type DELETE');
INV_KanbanCard_PKG.delete_row(x_return_status => l_ret_status,
p_kanban_card_id => r_kanban_card_rec.kanban_card_id);
,p_last_update_date IN DATE DEFAULT NULL
,p_last_update_by IN NUMBER DEFAULT NULL
,p_creation_date IN DATE DEFAULT NULL
,p_created_by IN NUMBER DEFAULT NULL
,p_last_update_login IN NUMBER DEFAULT NULL
,p_request_id IN NUMBER DEFAULT NULL
,p_program_application_id IN NUMBER DEFAULT NULL
,p_program_id IN NUMBER DEFAULT NULL
,p_program_update_date IN DATE DEFAULT NULL
,p_replenishment_count IN NUMBER DEFAULT NULL
,p_max_replenishments IN NUMBER DEFAULT NULL
,p_disable_date IN DATE DEFAULT NULL
,p_replacement_flag IN NUMBER DEFAULT NULL
,p_transaction_type IN NUMBER
,x_ret_status OUT NOCOPY VARCHAR2
,x_err_msg OUT NOCOPY VARCHAR2
)
IS
r_kanban_card_rec flm_ekanban_pub.kanban_card_rec_type;
r_kanban_card_rec.last_update_date := g_last_update_date;
r_kanban_card_rec.last_update_by := g_user_id;
r_kanban_card_rec.last_update_login := g_user_login_id;
r_kanban_card_rec.program_update_date := p_program_update_date;
,p_last_update_date => p_kanban_card_rec.last_update_date
,p_last_update_by => p_kanban_card_rec.last_update_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_request_id => p_kanban_card_rec.request_id
,p_program_application_id => p_kanban_card_rec.program_application_id
,p_program_id => p_kanban_card_rec.program_id
,p_program_update_date => p_kanban_card_rec.program_update_date
,p_replenishment_count => p_kanban_card_rec.replenishment_count
,p_max_replenishments => p_kanban_card_rec.max_replenishments
,p_disable_date => p_kanban_card_rec.disable_date
,p_replacement_flag => p_kanban_card_rec.replacement_flag
,p_transaction_type => p_transaction_type
,x_ret_status => l_ret_status
,x_err_msg => l_err_msg
);
r_kanban_card_rec.last_update_date := p_card_int_tbl(i).last_update_date;
r_kanban_card_rec.last_update_by := p_card_int_tbl(i).last_updated_by;
r_kanban_card_rec.last_update_login := p_card_int_tbl(i).last_update_login;
r_kanban_card_rec.program_update_date := p_card_int_tbl(i).program_update_date;
,p_last_update_date IN DATE DEFAULT NULL
,p_last_updated_by IN NUMBER DEFAULT NULL
,p_creation_date IN DATE DEFAULT NULL
,p_created_by IN NUMBER DEFAULT NULL
,p_last_update_login IN NUMBER DEFAULT NULL
,p_inventory_item_id IN NUMBER DEFAULT NULL
,p_concatenated_segments IN VARCHAR2 DEFAULT NULL
,p_organization_code IN VARCHAR2 DEFAULT NULL
,p_organization_id IN NUMBER DEFAULT NULL
,p_subinventory_name IN VARCHAR2 DEFAULT NULL
,p_source_type IN NUMBER DEFAULT NULL
,p_locator_id IN NUMBER DEFAULT NULL
,p_supplier_id IN NUMBER DEFAULT NULL
,p_supplier_name IN VARCHAR2 DEFAULT NULL
,p_supplier_site_id IN NUMBER DEFAULT NULL
,p_supplier_site_code IN VARCHAR2 DEFAULT NULL
,p_source_organization_id IN NUMBER DEFAULT NULL
,p_source_subinventory IN VARCHAR2 DEFAULT NULL
,p_source_locator_id IN NUMBER DEFAULT NULL
,p_wip_line_code IN VARCHAR2 DEFAULT NULL
,p_wip_line_id IN NUMBER DEFAULT NULL
,p_replenishment_lead_time IN NUMBER DEFAULT NULL
,p_calculate_kanban_flag IN NUMBER DEFAULT NULL
,p_kanban_size IN NUMBER DEFAULT NULL
,p_fixed_lot_multiplier IN NUMBER DEFAULT NULL
,p_safety_stock_days IN NUMBER DEFAULT NULL
,p_number_of_cards IN NUMBER DEFAULT NULL
,p_minimum_order_quantity IN NUMBER DEFAULT NULL
,p_aggregation_type IN NUMBER DEFAULT NULL
,p_allocation_percent IN NUMBER DEFAULT NULL
,p_release_kanban_flag IN NUMBER DEFAULT NULL
,p_attribute_category IN VARCHAR2 DEFAULT NULL
,p_attribute1 IN VARCHAR2 DEFAULT NULL
,p_attribute2 IN VARCHAR2 DEFAULT NULL
,p_attribute3 IN VARCHAR2 DEFAULT NULL
,p_attribute4 IN VARCHAR2 DEFAULT NULL
,p_attribute5 IN VARCHAR2 DEFAULT NULL
,p_attribute6 IN VARCHAR2 DEFAULT NULL
,p_attribute7 IN VARCHAR2 DEFAULT NULL
,p_attribute8 IN VARCHAR2 DEFAULT NULL
,p_attribute9 IN VARCHAR2 DEFAULT NULL
,p_attribute10 IN VARCHAR2 DEFAULT NULL
,p_attribute11 IN VARCHAR2 DEFAULT NULL
,p_attribute12 IN VARCHAR2 DEFAULT NULL
,p_attribute13 IN VARCHAR2 DEFAULT NULL
,p_attribute14 IN VARCHAR2 DEFAULT NULL
,p_attribute15 IN VARCHAR2 DEFAULT NULL
,p_auto_request IN VARCHAR2 DEFAULT NULL
,p_auto_allocate_flag IN VARCHAR2 DEFAULT NULL
,p_replenishment_type IN NUMBER DEFAULT NULL
,p_consolidation IN NUMBER DEFAULT NULL
,p_consolidation_group IN VARCHAR2 DEFAULT NULL
,p_future_card_size IN NUMBER DEFAULT NULL
,p_future_no_of_cards IN NUMBER DEFAULT NULL
,p_planning_effectivity IN DATE DEFAULT NULL
,p_avg_dependent_demand IN NUMBER DEFAULT NULL
,p_avg_independent_demand IN NUMBER DEFAULT NULL
,p_transaction_type IN NUMBER
,x_ret_status OUT NOCOPY VARCHAR2
,x_error_msg OUT NOCOPY VARCHAR2)
IS
r_pull_seq_rec flm_ekanban_pub.pull_sequence_rec_type;
r_pull_seq_rec.last_update_date := g_last_update_date;
r_pull_seq_rec.last_updated_by := g_user_id;
r_pull_seq_rec.last_update_login := g_user_login_id;
l_pull_sequence_rec.last_update_date := r_pull_seq_rec.last_update_date;
l_pull_sequence_rec.last_updated_by := r_pull_seq_rec.last_updated_by;
l_pull_sequence_rec.last_update_login := r_pull_seq_rec.last_update_login;
l_pull_sequence_rec.updated_flag := null;
l_pull_sequence_rec.program_update_date := null;
l_pull_sequence_rec.planning_update_status := null;
l_pull_sequence_rec.program_update_date := SYSDATE;
mydebug('Calling INV_PullSequence_PKG.Insert_Row procedure.');
INV_PullSequence_PKG.Insert_Row(
x_return_status =>l_ret_status,
p_pull_sequence_id =>l_pull_sequence_rec.pull_sequence_id,
p_Inventory_item_id =>l_pull_sequence_rec.Inventory_item_id,
p_Organization_id =>l_pull_sequence_rec.Organization_id,
p_Subinventory_name =>l_pull_sequence_rec.Subinventory_name,
p_Kanban_Plan_id =>l_pull_sequence_rec.Kanban_Plan_id,
p_Source_type =>l_pull_sequence_rec.Source_type,
p_Last_Update_Date =>l_pull_sequence_rec.Last_Update_Date,
p_Last_Updated_By =>l_pull_sequence_rec.Last_Updated_By,
p_Creation_Date =>l_pull_sequence_rec.Creation_Date,
p_Created_By =>l_pull_sequence_rec.Created_By,
p_Last_Update_Login =>l_pull_sequence_rec.Last_Update_Login,
p_Locator_id =>l_pull_sequence_rec.Locator_id,
p_Supplier_id =>l_pull_sequence_rec.Supplier_id,
p_Supplier_site_id =>l_pull_sequence_rec.Supplier_site_id,
p_Source_Organization_id =>l_pull_sequence_rec.Source_Organization_id,
p_Source_Subinventory =>l_pull_sequence_rec.Source_Subinventory,
p_Source_Locator_id =>l_pull_sequence_rec.Source_Locator_id,
p_Wip_Line_id =>l_Pull_Sequence_Rec.Wip_Line_id,
p_Release_Kanban_Flag =>l_pull_sequence_rec.Release_Kanban_Flag,
p_Calculate_Kanban_Flag =>l_pull_sequence_rec.Calculate_Kanban_Flag,
p_Kanban_size =>l_pull_sequence_rec.Kanban_size,
p_Number_of_cards =>l_pull_sequence_rec.Number_of_cards,
p_Minimum_order_quantity =>l_pull_sequence_rec.Minimum_order_quantity,
p_Aggregation_type =>l_pull_sequence_rec.Aggregation_type,
p_Allocation_Percent =>l_pull_sequence_rec.Allocation_Percent,
p_Replenishment_lead_time =>l_pull_sequence_rec.Replenishment_lead_time,
p_Fixed_Lot_multiplier =>l_pull_sequence_rec.Fixed_Lot_multiplier,
p_Safety_Stock_Days =>l_pull_sequence_rec.Safety_Stock_Days,
p_Updated_Flag =>l_pull_sequence_rec.Updated_Flag,
p_Attribute_Category =>l_pull_sequence_rec.Attribute_Category,
p_Attribute1 =>l_pull_sequence_rec.Attribute1,
p_Attribute2 =>l_pull_sequence_rec.Attribute2,
p_Attribute3 =>l_pull_sequence_rec.Attribute3,
p_Attribute4 =>l_pull_sequence_rec.Attribute4,
p_Attribute5 =>l_pull_sequence_rec.Attribute5,
p_Attribute6 =>l_pull_sequence_rec.Attribute6,
p_Attribute7 =>l_pull_sequence_rec.Attribute7,
p_Attribute8 =>l_pull_sequence_rec.Attribute8,
p_Attribute9 =>l_pull_sequence_rec.Attribute9,
p_Attribute10 =>l_pull_sequence_rec.Attribute10,
p_Attribute11 =>l_pull_sequence_rec.Attribute11,
p_Attribute12 =>l_pull_sequence_rec.Attribute12,
p_Attribute13 =>l_pull_sequence_rec.Attribute13,
p_Attribute14 =>l_pull_sequence_rec.Attribute14,
p_Attribute15 =>l_pull_sequence_rec.Attribute15,
p_Request_Id =>l_pull_sequence_rec.Request_Id,
p_Program_application_Id =>l_pull_sequence_rec.Program_application_Id,
p_Program_Id =>l_pull_sequence_rec.Program_Id,
p_Program_Update_date =>l_pull_sequence_rec.Program_Update_date,
p_point_of_use_x =>l_pull_sequence_rec.point_of_use_x,
p_point_of_use_y =>l_pull_sequence_rec.point_of_use_y,
p_point_of_supply_x =>l_pull_sequence_rec.point_of_supply_x,
p_point_of_supply_y =>l_pull_sequence_rec.point_of_supply_y,
p_planning_update_status =>l_pull_sequence_rec.planning_update_status,
p_auto_request =>l_pull_sequence_rec.auto_request,
p_Auto_Allocate_Flag =>l_pull_sequence_rec.Auto_Allocate_Flag,
p_replenishment_type =>l_pull_sequence_rec.replenishment_type,
p_future_card_size =>l_pull_sequence_rec.future_card_size,
p_future_no_of_cards =>l_pull_sequence_rec.future_no_of_cards,
p_planning_effectivity =>l_pull_sequence_rec.planning_effectivity,
p_consolidation =>l_pull_sequence_rec.consolidation,
p_consolidation_group =>l_pull_sequence_rec.consolidation_group,
p_avg_dependent_demand =>l_pull_sequence_rec.avg_dependent_demand,
p_avg_independent_demand =>l_pull_sequence_rec.avg_independent_demand);
l_error_msg := 'Error in INV_PullSequence_PKG.Insert_Row';
mydebug('Calling INV_PullSequence_PKG.Update_Row procedure.');
INV_PullSequence_PKG.Update_Row(
x_return_status =>l_ret_status,
p_pull_sequence_id =>l_pull_sequence_rec.pull_sequence_id,
p_Inventory_item_id =>l_pull_sequence_rec.Inventory_item_id,
p_Organization_id =>l_pull_sequence_rec.Organization_id,
p_Subinventory_name =>l_pull_sequence_rec.Subinventory_name,
p_Kanban_Plan_id =>l_pull_sequence_rec.Kanban_Plan_id,
p_Source_type =>l_pull_sequence_rec.Source_type,
p_Last_Update_Date =>l_pull_sequence_rec.Last_Update_Date,
p_Last_Updated_By =>l_pull_sequence_rec.Last_Updated_By,
p_Creation_Date =>l_pull_sequence_rec.Creation_Date,
p_Created_By =>l_pull_sequence_rec.Created_By,
p_Last_Update_Login =>l_pull_sequence_rec.Last_Update_Login,
p_Locator_id =>l_pull_sequence_rec.Locator_id,
p_Supplier_id =>l_pull_sequence_rec.Supplier_id,
p_Supplier_site_id =>l_pull_sequence_rec.Supplier_site_id,
p_Source_Organization_id =>l_pull_sequence_rec.Source_Organization_id,
p_Source_Subinventory =>l_pull_sequence_rec.Source_Subinventory,
p_Source_Locator_id =>l_pull_sequence_rec.Source_Locator_id,
p_Wip_Line_id =>l_Pull_Sequence_Rec.Wip_Line_id,
p_Release_Kanban_Flag =>l_pull_sequence_rec.Release_Kanban_Flag,
p_Calculate_Kanban_Flag =>l_pull_sequence_rec.Calculate_Kanban_Flag,
p_Kanban_size =>l_pull_sequence_rec.Kanban_size,
p_Number_of_cards =>l_pull_sequence_rec.Number_of_cards,
p_Minimum_order_quantity =>l_pull_sequence_rec.Minimum_order_quantity,
p_Aggregation_type =>l_pull_sequence_rec.Aggregation_type,
p_Allocation_Percent =>l_pull_sequence_rec.Allocation_Percent,
p_Replenishment_lead_time =>l_pull_sequence_rec.Replenishment_lead_time,
p_Fixed_Lot_multiplier =>l_pull_sequence_rec.Fixed_Lot_multiplier,
p_Safety_Stock_Days =>l_pull_sequence_rec.Safety_Stock_Days,
p_Updated_Flag =>l_pull_sequence_rec.Updated_Flag,
p_Attribute_Category =>l_pull_sequence_rec.Attribute_Category,
p_Attribute1 =>l_pull_sequence_rec.Attribute1,
p_Attribute2 =>l_pull_sequence_rec.Attribute2,
p_Attribute3 =>l_pull_sequence_rec.Attribute3,
p_Attribute4 =>l_pull_sequence_rec.Attribute4,
p_Attribute5 =>l_pull_sequence_rec.Attribute5,
p_Attribute6 =>l_pull_sequence_rec.Attribute6,
p_Attribute7 =>l_pull_sequence_rec.Attribute7,
p_Attribute8 =>l_pull_sequence_rec.Attribute8,
p_Attribute9 =>l_pull_sequence_rec.Attribute9,
p_Attribute10 =>l_pull_sequence_rec.Attribute10,
p_Attribute11 =>l_pull_sequence_rec.Attribute11,
p_Attribute12 =>l_pull_sequence_rec.Attribute12,
p_Attribute13 =>l_pull_sequence_rec.Attribute13,
p_Attribute14 =>l_pull_sequence_rec.Attribute14,
p_Attribute15 =>l_pull_sequence_rec.Attribute15,
p_point_of_use_x =>l_pull_sequence_rec.point_of_use_x,
p_point_of_use_y =>l_pull_sequence_rec.point_of_use_y,
p_point_of_supply_x =>l_pull_sequence_rec.point_of_supply_x,
p_point_of_supply_y =>l_pull_sequence_rec.point_of_supply_y,
p_planning_update_status =>l_pull_sequence_rec.planning_update_status,
p_auto_request =>l_pull_sequence_rec.auto_request,
p_Auto_Allocate_Flag =>l_pull_sequence_rec.Auto_Allocate_Flag,
p_replenishment_type =>l_pull_sequence_rec.replenishment_type,
p_future_card_size =>l_pull_sequence_rec.future_card_size,
p_future_no_of_cards =>l_pull_sequence_rec.future_no_of_cards,
p_planning_effectivity =>l_pull_sequence_rec.planning_effectivity,
p_consolidation =>l_pull_sequence_rec.consolidation,
p_consolidation_group =>l_pull_sequence_rec.consolidation_group,
p_avg_dependent_demand =>l_pull_sequence_rec.avg_dependent_demand,
p_avg_independent_demand =>l_pull_sequence_rec.avg_independent_demand);
UPDATE MTL_KANBAN_CARDS
SET replacement_flag = 1
WHERE source_type <> l_pull_sequence_rec.source_type
AND source_subinventory <> l_pull_sequence_rec.source_subinventory
AND source_locator_id <> l_pull_sequence_rec.source_locator_id
AND wip_line_id <> l_pull_sequence_rec.wip_line_id
AND pull_sequence_id = l_pull_sequence_rec.pull_sequence_id;
l_error_msg := 'Error in INV_PullSequence_PKG.Update_Row';
elsif p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_DELETE then
-- delete pull sequence
mydebug('Calling INV_PullSequence_PKG.Delete_Row procedure.');
INV_PullSequence_PKG.Delete_Row(x_return_status => l_ret_status
,p_Pull_sequence_Id => l_pull_sequence_rec.pull_sequence_id);
FLM_KANBAN_MASSLOAD.kanban_delete)
then
FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
select count(*)
into l_supplier_count
from mtl_pull_seq_suppliers
where pull_sequence_id = p_supplier_rec.pull_sequence_id
and supplier_id = p_supplier_rec.supplier_id;
mydebug('Duplicate supplier is trying to inserted for current pull sequence');
select 1 into l_supplier_count
from mtl_pull_seq_suppliers
where pull_sequence_id = p_supplier_rec.pull_sequence_id
and supplier_id = p_supplier_rec.supplier_id;
select sourcing_percentage into l_sourcing_percentage
from mtl_pull_seq_suppliers
where pull_sequence_id = p_supplier_rec.pull_sequence_id
and supplier_id = p_supplier_rec.supplier_id;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_update_date := g_last_update_date;
l_last_updated_by := g_user_id;
l_last_update_login := g_user_login_id;
,p_last_update_date => p_pull_sequence_rec.last_update_date
,p_last_updated_by => p_pull_sequence_rec.last_updated_by
,p_creation_date => p_pull_sequence_rec.creation_date
,p_created_by => p_pull_sequence_rec.created_by
,p_last_update_login => p_pull_sequence_rec.last_update_login
,p_inventory_item_id => p_pull_sequence_rec.inventory_item_id
,p_concatenated_segments => p_pull_sequence_rec.concatenated_segments
,p_organization_code => p_pull_sequence_rec.organization_code
,p_organization_id => p_pull_sequence_rec.organization_id
,p_subinventory_name => p_pull_sequence_rec.subinventory_name
,p_source_type => p_pull_sequence_rec.source_type
,p_locator_id => p_pull_sequence_rec.locator_id
,p_supplier_id => p_pull_sequence_rec.supplier_id
,p_supplier_name => p_pull_sequence_rec.supplier_name
,p_supplier_site_id => p_pull_sequence_rec.supplier_site_id
,p_supplier_site_code => p_pull_sequence_rec.supplier_site_code
,p_source_organization_id => p_pull_sequence_rec.source_organization_id
,p_source_subinventory => p_pull_sequence_rec.source_subinventory
,p_source_locator_id => p_pull_sequence_rec.source_locator_id
,p_wip_line_code => p_pull_sequence_rec.wip_line_code
,p_wip_line_id => p_pull_sequence_rec.wip_line_id
,p_replenishment_lead_time => p_pull_sequence_rec.replenishment_lead_time
,p_calculate_kanban_flag => p_pull_sequence_rec.calculate_kanban_flag
,p_kanban_size => p_pull_sequence_rec.kanban_size
,p_fixed_lot_multiplier => p_pull_sequence_rec.fixed_lot_multiplier
,p_safety_stock_days => p_pull_sequence_rec.safety_stock_days
,p_number_of_cards => p_pull_sequence_rec.number_of_cards
,p_minimum_order_quantity => p_pull_sequence_rec.minimum_order_quantity
,p_aggregation_type => p_pull_sequence_rec.aggregation_type
,p_allocation_percent => p_pull_sequence_rec.allocation_percent
,p_release_kanban_flag => p_pull_sequence_rec.release_kanban_flag
,p_attribute_category => p_pull_sequence_rec.attribute_category
,p_attribute1 => p_pull_sequence_rec.attribute1
,p_attribute2 => p_pull_sequence_rec.attribute2
,p_attribute3 => p_pull_sequence_rec.attribute3
,p_attribute4 => p_pull_sequence_rec.attribute4
,p_attribute5 => p_pull_sequence_rec.attribute5
,p_attribute6 => p_pull_sequence_rec.attribute6
,p_attribute7 => p_pull_sequence_rec.attribute7
,p_attribute8 => p_pull_sequence_rec.attribute8
,p_attribute9 => p_pull_sequence_rec.attribute9
,p_attribute10 => p_pull_sequence_rec.attribute10
,p_attribute11 => p_pull_sequence_rec.attribute11
,p_attribute12 => p_pull_sequence_rec.attribute12
,p_attribute13 => p_pull_sequence_rec.attribute13
,p_attribute14 => p_pull_sequence_rec.attribute14
,p_attribute15 => p_pull_sequence_rec.attribute15
,p_auto_request => p_pull_sequence_rec.auto_request
,p_auto_allocate_flag => p_pull_sequence_rec.auto_allocate_flag
,p_replenishment_type => p_pull_sequence_rec.replenishment_type
,p_consolidation => p_pull_sequence_rec.consolidation
,p_consolidation_group => p_pull_sequence_rec.consolidation_group
,p_future_card_size => p_pull_sequence_rec.future_card_size
,p_future_no_of_cards => p_pull_sequence_rec.future_no_of_cards
,p_planning_effectivity => p_pull_sequence_rec.planning_effectivity
,p_avg_dependent_demand => p_pull_sequence_rec.avg_dependent_demand
,p_avg_independent_demand => p_pull_sequence_rec.avg_independent_demand
,p_transaction_type => p_transaction_type
,x_ret_status => l_ret_status
,x_error_msg => l_error_msg);
select mkps.source_type
into l_source_type
from mtl_kanban_pull_sequences mkps
WHERE pull_sequence_id = p_pull_sequence_rec.pull_sequence_id;
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,p_creation_date => l_creation_date
,p_created_by => l_created_by
,p_last_update_login => l_last_update_login
,p_transaction_type => p_supplier_tbl(i).transaction_type
,x_ret_status => l_ret_status
,x_error_msg => l_error_msg);
UPDATE mtl_kanban_pull_sequences
SET supplier_id = null
,supplier_site_id = null
WHERE pull_sequence_id = p_pull_sequence_rec.pull_sequence_id;
,p_last_update_date IN DATE DEFAULT NULL
,p_last_updated_by IN NUMBER DEFAULT NULL
,p_creation_date IN DATE DEFAULT NULL
,p_created_by IN NUMBER DEFAULT NULL
,p_last_update_login IN NUMBER DEFAULT NULL
,p_transaction_type IN NUMBER
,x_ret_status OUT NOCOPY VARCHAR2
,x_error_msg OUT NOCOPY VARCHAR2)
IS
r_supplier_rec flm_ekanban_pub.kanban_supplier_rec_type;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_update_date := g_last_update_date;
l_last_updated_by := g_user_id;
l_last_update_login := g_user_login_id;
v_supplier_rec.last_update_date := l_last_update_date;
v_supplier_rec.last_updated_by := l_last_updated_by;
v_supplier_rec.last_update_login := l_last_update_login;
mydebug('Calling insert_supplier_row procedure. ');
insert_supplier_row(p_supplier_rec => v_supplier_rec);
mydebug('Calling update_supplier_row procedure. ');
update_supplier_row(p_supplier_rec => v_supplier_rec);
elsif p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_DELETE then
-- call delete_supplier_row
mydebug('Calling delete_supplier_row procedure. ');
delete_supplier_row(x_return_status => l_ret_status
,p_pull_sequence_id => p_pull_sequence_id
,p_supplier_id => v_supplier_rec.supplier_id); --Fix bug 12419603
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 concatenated_segments,organization_code
into x_item_name,x_org_code
from mtl_system_items_kfv a , mtl_parameters b
where a.inventory_item_id =
p_inventory_item_id
and a.organization_id =
p_organization_id
and b.organization_id =
p_organization_id;
Select concatenated_segments
into x_loc_name
from mtl_item_locations_kfv
where inventory_location_id = p_locator_id
and organization_id = p_organization_id;
select organization_id
into l_org_id
from mtl_parameters
where organization_code = p_org_code;
select count(organization_id)
into l_count
from mtl_parameters
where organization_id = p_org_id;
select line_id
into l_wip_line_id
from wip_lines_val_v
where line_code = p_wip_line_code
and organization_id = p_org_id;
select count(1)
into l_count
from wip_lines_val_v
where line_id = p_wip_line_id;
SELECT vendor_id
INTO l_supplier_id
FROM PO_SUPPLIERS_VAL_V
WHERE vendor_name = p_supplier_name;
SELECT count(vendor_id)
into l_count
from PO_SUPPLIERS_VAL_V
where vendor_id = p_supplier_id;
SELECT vendor_site_id
INTO l_supplier_site_id
FROM MTL_SUPPLIER_SITES_V
WHERE vendor_site_code = p_supplier_site_code
AND vendor_id = p_supplier_id
AND organization_id = p_org_id;
SELECT count(vendor_site_id)
INTO l_count
FROM MTL_SUPPLIER_SITES_V
WHERE vendor_site_id = p_supplier_site_id
AND vendor_id = p_supplier_id
AND organization_id = p_org_id;
SELECT mpk.inventory_item_id
INTO l_inv_item_id
FROM mtl_system_items_kfv mpk
WHERE mpk.organization_id = p_org_id
AND UPPER(mpk.concatenated_segments) = UPPER (p_conc_segments);
SELECT kanban_card_id
INTO l_kanban_card_id
FROM MTL_KANBAN_CARDS
WHERE pull_sequence_id = p_pull_sequence_id
AND kanban_card_number = p_kanban_card_number;