[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Purge_Kanban_Cards - This is the procedure used to delete the
Kanban Cards ('Cancelled Cards Only Or Cancelled and New/Active')
based on the Delete Option selected by the user.
Check_Restrictions - This procedure is used for the checking
the validations for the pull sequences . The following are the
restrictions before deletion
- the pull sequence is not appearing on any BOM
- if the sub/loc is NULL in BOM but the same is defined
in the Master Items
- if the sub/loc do not appear as point of supply in any other
pull sequence.
- If there are no cards against the pull sequence
If all validations are passed through then delete the pull
sequence else if only last the check failed them it is a
unreferenced pull sequence.
Purge_Kanban - This is the main procedure and is called from the
report with the user parameters. This has a cursor to pick up
the eligible information for the Purge from the main table
MTL_KANBAN_PULL_SEQUENCES . The above mentioned procedures are
called for every record in the curor.
*/
PROCEDURE PURGE_KANBAN_CARDS(
arg_pull_seq_id in number,
arg_org_id in number,
arg_item_id in number,
arg_subinv in varchar2,
arg_loc_id in number,
arg_delete_card in number,
arg_group_id in number,
retcode out NOCOPY number,
errbuf out NOCOPY varchar2
)
IS
l_record_count NUMBER := 0 ;
if (arg_delete_card = G_CANCELLED_CARDS_ONLY) then
DELETE mtl_kanban_cards
WHERE organization_id = arg_org_id
AND pull_sequence_id = arg_pull_seq_id
AND card_status = 3;
INSERT INTO flm_kanban_purge_temp
(organization_id,
item_id,
subinventory_code,
locator_id,
count,
type,
group_id)
VALUES
(arg_org_id,
arg_item_id,
arg_subinv,
arg_loc_id,
l_record_count,
G_KANBAN_CARD,
arg_group_id);
elsif (arg_delete_card = G_CANCELLED_AND_NEW) then
-- Delete both Cancelled and New/Active Cards .
l_stmt_num := 230;
DELETE mtl_kanban_cards
WHERE organization_id = arg_org_id
AND pull_sequence_id = arg_pull_seq_id
AND (
(card_status = 3) OR
(card_status = 1) AND (supply_status = 1)
);
INSERT INTO flm_kanban_purge_temp(
organization_id,
item_id,
subinventory_code,
locator_id,
count,
type,
group_id)
VALUES
(arg_org_id,
arg_item_id,
arg_subinv,
arg_loc_id,
l_record_count,
G_KANBAN_CARD,
arg_group_id);
SELECT count(*)
INTO l_records_found
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM BOM_INVENTORY_COMPONENTS BIC,
BOM_BILL_OF_MATERIALS BBM
WHERE BIC.bill_sequence_id = BBM.bill_sequence_id
AND BBM.organization_id = arg_org_id
AND BIC.component_item_id = arg_item_id
AND BIC.supply_subinventory = arg_subinv
AND nvl(BIC.supply_locator_id,-1) = nvl(arg_loc_id,-1));
SELECT count(*)
INTO l_records_found
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM BOM_INVENTORY_COMPONENTS BIC,
BOM_BILL_OF_MATERIALS BBM
WHERE BIC.bill_sequence_id = BBM.bill_sequence_id
AND BBM.organization_id = arg_org_id
AND BIC.component_item_id = arg_item_id
AND BIC.supply_subinventory IS NULL);
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
( SELECT 1
FROM MTL_SYSTEM_ITEMS
WHERE organization_id = arg_org_id
AND inventory_item_id = arg_item_id
AND wip_supply_subinventory = arg_subinv
AND nvl(wip_supply_locator_id,-1) = nvl(arg_loc_id,-1));
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
( SELECT 1
FROM MTL_KANBAN_PULL_SEQUENCES
WHERE organization_id = arg_org_id
AND source_organization_id = organization_id
AND inventory_item_id = arg_item_id
AND source_subinventory = arg_subinv
AND nvl(source_locator_id,-1) = nvl(arg_loc_id,-1));
SELECT COUNT(*)
into l_records_found
FROM DUAL
WHERE EXISTS
( SELECT 1
FROM MTL_KANBAN_CARDS
WHERE organization_id = arg_org_id
AND pull_sequence_id = arg_pull_seq_id );
INSERT INTO flm_kanban_purge_temp
(organization_id,
item_id,
subinventory_code,
locator_id,
count,
type,
group_id)
VALUES
(arg_org_id,
arg_item_id,
arg_subinv,
arg_loc_id,
1,
G_EXCEPTION,
arg_group_id);
DELETE MTL_KANBAN_PULL_SEQUENCES
WHERE organization_id = arg_org_id
AND inventory_item_id = arg_item_id
AND subinventory_name = arg_subinv
AND nvl(locator_id,-1) = nvl(arg_loc_id,-1) ;
INSERT INTO flm_kanban_purge_temp
(organization_id,
item_id,
subinventory_code,
locator_id,
count,
type,
group_id)
VALUES
(arg_org_id,
arg_item_id,
arg_subinv,
arg_loc_id,
1,
G_PULL_SEQUENCE,
arg_group_id);
arg_delete_card in number
)
IS
/* This cursor is executed for every record in the first cursor and
will be used fetch any pull sequence chain in the correct order so
that all the unreferenced pull sequences are deleted in a single loop*/
CURSOR Cur_Kanban(source in number,item_id in number,in_subinv in varchar2) is
Select pull_sequence_id,inventory_item_id,
subinventory_name,locator_id
from mtl_kanban_pull_sequences
where organization_id = arg_org_id
and inventory_item_id = item_id
and subinventory_name <= nvl(arg_subinv_from,subinventory_name)
and subinventory_name >= nvl(arg_subinv_to,subinventory_name)
and kanban_plan_id = -1
and ( source IS NULL
or (
((source_type = source)
and nvl(source_subinventory, -1) = nvl(arg_source_subinv, nvl(source_subinventory, -1) ) )
and nvl(source_organization_id, -1) = nvl(arg_source_org_id, nvl(source_organization_id, -1)) )
or ((source_type = source) and nvl(supplier_id,-1) = nvl(arg_supplier_id,nvl(supplier_id, -1) ) )
or ((source_type = source) and
nvl(source_subinventory, -1) = nvl(arg_source_subinv, nvl(source_subinventory, -1)) )
or ((source_type = source) and nvl(wip_line_id, -1) = nvl(arg_line_id, nvl(wip_line_id, -1)) ))
start with subinventory_name = in_subinv and inventory_item_id = item_id and organization_id = arg_org_id
connect by prior source_subinventory = subinventory_name and inventory_item_id = item_id
and organization_id = arg_org_id and nvl(prior source_locator_id, -1) = nvl(locator_id, -1);
MRP_UTIL.MRP_LOG('Delete Card --> '||to_char(arg_delete_card));
'SELECT pull_sequence_id, inventory_item_id,'||
'subinventory_name, locator_id'||
' FROM mtl_kanban_pull_sequences MKP1'||
' WHERE mkp1.organization_id = :org_id'||
' AND mkp1.subinventory_name >= nvl(:sub_from, subinventory_name)'||
' AND mkp1.subinventory_name <= nvl(:sub_to, subinventory_name)'||
' AND mkp1.kanban_plan_id = -1'||
' AND ( :source IS NULL'||
' OR ( ((mkp1.source_type = :source) AND (mkp1.source_type = 1) '||
' AND ( nvl(mkp1.source_subinventory, -1) = nvl(:source_subinv, nvl(mkp1.source_subinventory ,-1)) ) '||
' AND ( nvl(mkp1.source_organization_id, -1) = nvl(:source_org_id, nvl(mkp1.source_organization_id ,-1)) ))'||
' OR ((mkp1.source_type = :source) AND (mkp1.source_type = 2) and (nvl(mkp1.supplier_id,-1) = nvl(:supplier_id, nvl(mkp1.supplier_id, -1) )))'||
' OR ((mkp1.source_type = :source) AND (mkp1.source_type = 3) and (mkp1.source_subinventory = nvl(:source_subinv, mkp1.source_subinventory) ))'||
' OR ((mkp1.source_type = :source) AND (mkp1.source_type = 4) and nvl(mkp1.wip_line_id, -1) = nvl(:line_id, nvl(mkp1.wip_line_id ,-1)) )))';
' (select inventory_item_id from mtl_system_items ' ||
' where ' || l_where_item || ' and organization_id = :org_id) ';
' SELECT 1 FROM ' ||
' mtl_kanban_pull_sequences mkp2' ||
' where mkp2.inventory_item_id = mkp1.inventory_item_id '||
' and mkp2.source_subinventory = mkp1.subinventory_name '||
' and mkp2.source_organization_id = mkp2.organization_id '||
' and mkp2.subinventory_name >= nvl(:sub_from,mkp2.subinventory_name) '||
' and mkp2.subinventory_name <= nvl(:sub_to,mkp2.subinventory_name) '||
' and nvl(mkp2.locator_id,-1) = nvl(mkp1.source_locator_id,-1)) '||
' ORDER by mkp1.inventory_item_id ';
arg_delete_card,
l_group_id,
retcode,
errbuf);