[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select concatenated_segments,organization_code,
subinventory_name,locator_id,pull.organization_id
into x_item_name,x_org_code,x_subinventory,l_locator_id,
l_organization_id
from mtl_system_items_kfv a , mtl_parameters b,
mtl_kanban_pull_sequences pull
where a.inventory_item_id = pull.inventory_item_id
and a.organization_id = Pull.organization_id
and b.organization_id = Pull.organization_id
and pull.pull_sequence_id = p_Pull_sequence_id;
Select concatenated_segments
into x_loc_name
from mtl_item_locations_kfv
where inventory_location_id = l_locator_id
and organization_id = l_organization_id;
PROCEDURE Delete_Pull_Sequence
(x_return_status Out NOCOPY Varchar2,
p_kanban_plan_id Number)
IS
Cursor Get_Pull_Sequences IS
Select pull_sequence_id
From mtl_kanban_pull_sequences
Where kanban_plan_id = p_kanban_plan_id;
If Ok_To_Delete_Pull_Sequence(pull_sequences.pull_sequence_id)
then
INV_PullSequence_Pkg.delete_Row(l_return_status,pull_sequences.pull_sequence_id);
Delete from Mtl_kanban_pull_sequences
Where kanban_plan_id = p_kanban_plan_id;
End Delete_Pull_Sequence;
PROCEDURE Insert_Pull_Sequence
(x_return_status Out NOCOPY Varchar2,
p_Pull_Sequence_Rec INV_Kanban_PVT.Pull_sequence_Rec_Type)
IS
l_Pull_Sequence_Rec INV_Kanban_PVT.Pull_sequence_Rec_Type;
l_pull_sequence_rec.last_update_date := SYSDATE;
l_pull_sequence_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_pull_sequence_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
INV_PullSequence_PKG.Insert_Row(l_pull_sequence_rec);
, 'Insert_Pull_sequence'
);
End Insert_Pull_sequence;
PROCEDURE Update_Pull_sequence
(x_return_status Out NOCOPY Varchar2,
x_Pull_Sequence_Rec IN OUT NOCOPY INV_Kanban_PVT.Pull_sequence_Rec_Type)
IS
l_Pull_Sequence_Rec INV_Kanban_PVT.Pull_Sequence_Rec_Type;
Select Pull_Sequence_Id
Into l_Pull_Sequence_Rec.Pull_Sequence_Id
From Mtl_Kanban_Pull_Sequences
Where Kanban_Plan_Id = l_pull_Sequence_Rec.Kanban_plan_id
And Organization_Id = l_pull_Sequence_Rec.Organization_Id
And Inventory_Item_Id = l_pull_Sequence_Rec.Inventory_Item_Id
And Subinventory_Name = l_pull_Sequence_Rec.Subinventory_Name
And Nvl(Locator_Id,-1) = Nvl(l_pull_Sequence_Rec.Locator_Id,-1);
Select concatenated_segments,organization_code
into l_item_name,l_org_code
from mtl_system_items_kfv a , mtl_parameters b
where a.inventory_item_id =
l_Pull_Sequence_Rec.inventory_item_id
and a.organization_id =
l_Pull_Sequence_Rec.organization_id
and b.organization_id =
l_Pull_Sequence_Rec.organization_id;
Select concatenated_segments
into l_loc_name
from mtl_item_locations_kfv
where inventory_location_id =
l_Pull_Sequence_Rec.locator_id
and organization_id =
l_Pull_Sequence_Rec.organization_id;
l_pull_sequence_rec.last_update_date := SYSDATE;
l_pull_sequence_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_pull_sequence_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
INV_PullSequence_PKG.Update_Row(l_pull_sequence_Rec);
, 'Update_Pull_sequence'
);
End Update_Pull_sequence;
PROCEDURE Update_Pull_sequence_Tbl (x_return_status Out NOCOPY Varchar2,
p_Pull_Sequence_tbl INV_Kanban_PVT.Pull_sequence_Id_Tbl_Type,
x_update_flag Varchar2,
p_operation_tbl INV_Kanban_PVT.operation_tbl_type := G_operation_tbl)
IS
l_record_count NUMBER := 0;
l_operation_tbl INV_Kanban_PVT.operation_tbl_type; --Operation to be performed update/insert
CURSOR C2 IS SELECT mtl_kanban_pull_sequences_s.nextval FROM sys.dual;
SELECT MTL_KANBAN_CARD_PRINT_TEMP_S.nextval
INTO l_report_generate_id from DUAL;
SELECT MTL_KANBAN_CARD_PRINT_TEMP_S.nextval
INTO l_report_print_id from DUAL;
IF X_UPDATE_FLAG = 'Y' THEN
v_generate_cards := TRUE;
IF(l_pull_sequence_rec.planning_update_status = 1) THEN
l_pull_sequence_rec.planning_update_status := NULL ;
UPDATE mtl_kanban_pull_sequences
SET planning_update_status = NULL
WHERE pull_sequence_id = l_pull_sequence_rec.pull_sequence_id;
INV_Kanban_PVT.update_pull_sequence(
l_return_status,
l_Pull_sequence_rec);
INV_Kanban_PVT.insert_pull_sequence(
l_return_status,
l_Pull_sequence_rec);
INV_Kanban_PVT.update_pull_sequence(
l_return_status,
l_Pull_sequence_rec);
insert into mtl_kanban_card_print_temp(
report_id,
kanban_card_id,
pull_sequence_id)
values (
l_report_id,
-1,
l_Pull_sequence_rec.pull_sequence_id
);
IF X_UPDATE_FLAG = 'Y' THEN
-- we should not delete old kanban cards but change their status
-- for historical purposes.
update_kanban_card_status
(p_card_status => g_card_status_cancel,
p_pull_sequence_id => l_Pull_Sequence_Rec.pull_sequence_id);
, 'Update_Pull_sequence'
);
End Update_Pull_sequence_tbl;
PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
p_Kanban_Card_Id Number,
p_Supply_Status Number,
p_Document_type Number,
p_Document_Header_Id Number,
p_Document_detail_Id NUMBER,
p_replenish_quantity NUMBER,
p_need_by_date DATE,
p_source_wip_entity_id NUMBER)
IS
l_kanban_card_rec INV_Kanban_PVT.Kanban_Card_Rec_Type;
l_update Boolean := TRUE; -- For Bug 3740514
SELECT kanban_card_id
INTO l_temp
FROM MTL_KANBAN_CARDS
WHERE kanban_card_id = p_kanban_Card_Id
FOR UPDATE NOWAIT;
a new procedure update_card_and_card_status to check if the correct
Release is being updated.*/
IF ( p_supply_status IN (INV_Kanban_PVT.G_Supply_Status_InProcess,INV_Kanban_PVT.G_Supply_Status_Full) AND
l_Kanban_Card_Rec.source_type = INV_Kanban_PVT.G_Source_Type_Supplier) THEN
update_card_and_card_status(
p_kanban_card_id => l_kanban_card_rec.kanban_card_id,
p_supply_status => p_supply_status,/*4490269*/
p_update => l_update);
/*Bug 3740514--Only if l_update is TRUE will the kanban card details be updated to the
new values.*/
IF (l_update) THEN
mydebug('Supply status change OK');
l_kanban_card_rec.last_update_date := SYSDATE;
l_kanban_card_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_kanban_card_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
mydebug('calling INV_KanbanCard_PKG.Update_Row');
INV_KanbanCard_PKG.Update_Row(l_kanban_card_rec);
, 'Update_Card_Supply_Status'
);
End Update_Card_Supply_Status;
PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
p_Kanban_card_Id Number,
p_Supply_Status Number)
IS
l_document_type Number;
select line_id, nvl(quantity,0), nvl(reference_type_code,0)
into l_move_order_line_id, l_quantity, l_reference_type_code
from mtl_txn_request_lines
where reference_type_code = 1
and reference_id = p_Kanban_card_Id
and line_status in (3,7);
select sum(abs(transaction_quantity)) into l_quantity_delivered from
mtl_material_transactions where
move_order_line_id = l_move_order_line_id and
transaction_quantity < 0;
Update_Card_Supply_Status(X_Return_Status => x_Return_Status,
p_kanban_card_Id => p_Kanban_Card_Id,
p_Supply_Status => l_Supply_Status,
p_Document_type => l_document_type,
p_Document_Header_Id => l_document_header_id,
p_Document_detail_Id => l_Document_detail_id);
, 'Update_Card_Supply_Status'
);
End Update_Card_Supply_Status;
PROCEDURE Update_Card_Supply_Status
( p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_Kanban_Card_Id Number
,p_Supply_Status NUMBER
,p_Document_type IN NUMBER DEFAULT NULL
,p_Document_Header_Id IN NUMBER DEFAULT NULL
,p_Document_detail_Id IN NUMBER DEFAULT NULL
,p_replenish_quantity IN NUMBER DEFAULT NULL
,p_need_by_date IN DATE DEFAULT NULL
,p_source_wip_entity_id IN NUMBER DEFAULT NULL)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30):= 'Update_Card_Supply_Status';
mydebug('Inside Update_Card_Supply_Status 1');
mydebug('Calling Update_Card_Supply_Status 2');
Update_Card_Supply_Status(X_Return_Status => l_Return_Status,
p_kanban_card_Id => p_Kanban_Card_Id,
p_Supply_Status => p_Supply_Status,
p_Document_type => l_document_type,
p_Document_Header_Id => l_document_header_id,
p_Document_detail_Id => l_document_detail_id,
p_replenish_quantity => p_replenish_quantity,
p_need_by_date => p_need_by_date,
p_source_wip_entity_id => p_source_wip_entity_id);
End Update_Card_Supply_Status;
PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
p_Kanban_Card_Id Number,
p_Supply_Status Number,
p_Document_type Number,
p_Document_Header_Id Number)
IS
l_Document_detail_id Number := FND_API.G_MISS_NUM;
Update_Card_Supply_Status(X_Return_Status => x_Return_Status,
p_kanban_card_Id => p_Kanban_Card_Id,
p_Supply_Status => p_Supply_Status,
p_Document_type => p_document_type,
p_Document_Header_Id => p_document_header_id,
p_Document_detail_Id => l_Document_detail_id);
, 'Update_Card_Supply_Status'
);
End Update_Card_Supply_Status;
Select 'x'
INTO l_dummy
FROM MTL_KANBAN_CARDS
WHERE pull_sequence_id = p_pull_sequence_id
AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
card_status = INV_Kanban_PVT.G_Card_Status_Hold);
Select 'x'
INTO l_dummy
FROM MTL_KANBAN_CARDS
WHERE pull_sequence_id = p_pull_sequence_id
AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
card_status = INV_Kanban_PVT.G_Card_Status_Hold)
-- AND nvl(p_kanban_size,-1) > 0
-- AND kanban_size <> nvl(p_kanban_size,-1)
AND source_type = nvl(p_source_type,-1)
AND (((source_type = 1 or source_type = 3)
and nvl(source_organization_id,-1) = nvl(p_source_organization_id,-1)
and nvl(source_subinventory,'#?#?') = nvl(p_source_subinventory,'#?#?')
and nvl(source_locator_id,-1) = nvl(p_source_locator_id,-1))
OR
(source_type = 2
and ((nvl(supplier_id,-1) = nvl(p_supplier_id,-1)
and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1))
or p_supplier_id is null))
OR
(source_type = 4
and (nvl(wip_line_id,-1) = nvl(p_wip_line_id,-1)
or p_wip_line_id is null))
)
AND supply_status in (3,5) --sbitra
;
Select 'x'
INTO l_dummy
FROM MTL_KANBAN_CARDS
WHERE pull_sequence_id = p_pull_sequence_id
AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
card_status = INV_Kanban_PVT.G_Card_Status_Hold)
AND nvl(p_kanban_size,-1) > 0
AND kanban_size <> nvl(p_kanban_size,-1)
AND source_type = nvl(p_source_type,-1)
AND (((source_type = 1 or source_type = 3)
and nvl(source_organization_id,-1) = nvl(p_source_organization_id,-1)
and nvl(source_subinventory,'#?#?') = nvl(p_source_subinventory,'#?#?')
and nvl(source_locator_id,-1) = nvl(p_source_locator_id,-1))
OR
(source_type = 2
and ((nvl(supplier_id,-1) = nvl(p_supplier_id,-1)
and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1))
or p_supplier_id is null))
OR
(source_type = 4
and (nvl(wip_line_id,-1) = nvl(p_wip_line_id,-1)
or p_wip_line_id is null))
)
;
FUNCTION Ok_To_Delete_Pull_Sequence(p_Pull_sequence_id number)
RETURN BOOLEAN
IS
l_org_code varchar2(3);
FND_MESSAGE.SET_NAME('INV','INV_CANNOT_DELETE_PULLSEQ');
END Ok_To_Delete_Pull_sequence;
Select concatenated_segments,organization_code,kanban_card_number
into x_item_name,x_org_code,x_kanban_num
from mtl_system_items_kfv a , mtl_parameters b, mtl_kanban_cards card
where a.inventory_item_id = p_item_id
and a.organization_id = p_org_id
and b.organization_id = p_org_id
and card.kanban_card_id = p_kanban_id;
Select concatenated_segments
into x_loc_name
from mtl_item_locations_kfv
where inventory_location_id = p_loc_id
and organization_id = p_loc_id;
select subinventory_name, nvl(locator_id,0), inventory_item_id,
organization_id, source_type, supply_status
into l_subinventory, l_loc_id, l_item_id,
l_org_id, l_source_type, l_supply_status
from mtl_kanban_cards
where kanban_card_id = p_kanban_id;
select 'x' into l_dummy
from mtl_kanban_card_activity
where kanban_card_id = p_kanban_id
and organization_id = p_org_id
and document_header_id = p_wip_entity_id
and source_type = INV_KANBAN_PVT.G_Source_Type_Production
and supply_status = INV_KANBAN_PVT.G_Supply_Status_Full;
PROCEDURE Delete_Kanban_Cards(p_Pull_sequence_id number)
IS
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
INV_KanbanCard_PKG.Delete_Cards_For_Pull_Seq(p_pull_sequence_id);
END Delete_Kanban_Cards;
l_kanban_Card_tbl.Delete;
update_kanban_card_status
(p_card_status => g_card_status_cancel,
p_pull_sequence_id => p_Pull_Sequence_Rec.pull_sequence_id);
INV_KanbanCard_PKG.Insert_Row(
X_Return_Status => l_Return_Status,
P_Kanban_Card_Id => l_Kanban_Card_Id,
P_Kanban_Card_Number => l_Kanban_Card_Number,
P_Pull_Sequence_Id => p_Pull_Sequence_Rec.Pull_Sequence_Id,
P_Inventory_item_id => p_Pull_Sequence_Rec.Inventory_item_id,
P_Organization_id => p_Pull_Sequence_Rec.Organization_id,
P_Subinventory_name => p_Pull_Sequence_Rec.Subinventory_name,
P_Supply_Status => l_Supply_Status,
P_Card_Status => l_Card_Status,
P_Kanban_Card_Type => Nvl(p_pull_sequence_rec.kanban_card_type,INV_Kanban_Pvt.g_card_type_replenishable),
P_Source_type => p_Pull_Sequence_Rec.Source_type,
P_Kanban_size => nvl(p_Pull_Sequence_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 => p_Pull_Sequence_Rec.Locator_id,
P_Supplier_id => p_Pull_Sequence_Rec.Supplier_id,
P_Supplier_site_id => p_Pull_Sequence_Rec.Supplier_site_id,
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_id => p_Pull_Sequence_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 => NULL,
P_Attribute1 => NULL,
P_Attribute2 => NULL,
P_Attribute3 => NULL,
P_Attribute4 => NULL,
P_Attribute5 => NULL,
P_Attribute6 => NULL,
P_Attribute7 => NULL,
P_Attribute8 => NULL,
P_Attribute9 => NULL,
P_Attribute10 => NULL,
P_Attribute11 => NULL,
P_Attribute12 => NULL,
P_Attribute13 => NULL,
P_Attribute14 => NULL,
P_Attribute15 => NULL,
P_Request_Id => NULL,
P_Program_application_Id => NULL,
P_Program_Id => NULL,
P_Program_Update_date => NULL,
p_release_kanban_flag => p_Pull_Sequence_Rec.release_kanban_flag);
Select MTL_KANBAN_REPLENISH_CYCLE_S.NextVal
Into l_next_replenish_cycle_Id
From Dual;
SELECT NVL(project_reference_enabled,2)
INTO l_project_reference_enabled
FROM mtl_parameters
WHERE organization_id = p_kanban_card_rec_tbl(1).organization_id;
SELECT project_id
INTO l_project_id
FROM mtl_item_locations
WHERE inventory_location_id = p_kanban_card_rec_tbl(1).locator_id
AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
SELECT task_id
INTO l_task_id
FROM mtl_item_locations
WHERE NVL(project_id,-999) = NVL(l_project_id, -111)
AND inventory_location_id =p_kanban_card_rec_tbl(1).locator_id
AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
insert into po_requisitions_interface_all
(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PREPARER_ID,
INTERFACE_SOURCE_CODE,
REQUISITION_TYPE,
AUTHORIZATION_STATUS,
SOURCE_TYPE_CODE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
DESTINATION_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,
DELIVER_TO_REQUESTOR_ID,
DESTINATION_TYPE_CODE,
DELIVER_TO_LOCATION_ID,
ITEM_ID,
ITEM_REVISION,
ITEM_DESCRIPTION,
UOM_CODE,
QUANTITY,
NEED_BY_DATE,
GL_DATE,
CHARGE_ACCOUNT_ID,
ACCRUAL_ACCOUNT_ID,
VARIANCE_ACCOUNT_ID,
BUDGET_ACCOUNT_ID,
AUTOSOURCE_FLAG,
ORG_ID,
SUGGESTED_VENDOR_ID,
SUGGESTED_VENDOR_SITE_ID,
-- SUGGESTED_BUYER_ID, /* Bug 1456782 */
Kanban_card_Id,
Batch_Id,
PROJECT_ID,
TASK_ID,
PROJECT_ACCOUNTING_CONTEXT
)
Values
(
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
p_buyer_Id,
p_interface_Source_Code,
p_requisition_type,
p_approval,
p_source_type_code,
p_kanban_card_Rec_Tbl(1).Source_organization_Id,
p_kanban_card_Rec_Tbl(1).Source_Subinventory,
p_kanban_card_Rec_Tbl(1).organization_Id,
p_kanban_card_Rec_Tbl(1).Subinventory_Name,
p_buyer_Id,
p_destination_type_code,
p_deliver_location_id,
p_kanban_card_Rec_Tbl(1).Inventory_Item_Id,
p_revision,
p_Item_description,
p_Primary_uom_Code,
p_kanban_card_rec_tbl(l_order_count).kanban_size,
(trunc(p_need_by_date) + 1 - (1/(24*60*60))),
SYSDATE,
p_Charge_Account_Id,
p_Accrual_Account_Id,
p_Invoice_Var_Account_Id,
p_Budget_Account_Id,
p_autosource_flag,
p_po_org_id,
p_kanban_card_Rec_Tbl(1).Supplier_ID,
p_kanban_card_Rec_Tbl(1).Supplier_Site_ID,
-- p_Buyer_ID, /* Bug 1456782 */
p_kanban_card_rec_tbl(l_order_count).kanban_card_id,
p_kanban_card_rec_tbl(1).current_replnsh_cycle_id,
l_project_id,
l_task_id,
l_project_accounting_context
);
Insert into po_requisition_interface_all with
Org_Id Operating Unit
Preparer ID Buyer ID
Item_Id Inventory_item_id
Item_Description item_description
Accrual_account_id Org level ap_accrual_account
Authorization_status 'APPROVED'
Autosource_Flag 'Y'
Budget_Account_id Encumbrance_account Item Sub level/Sub level/Item level/Org level
Charge_Account_Id For inventory_asset_flag='Y' use sub level/org level
material_account else use sub level/item level/org level expense
account
Variance_Account_Id Org level - invoice_price_variance_account
Created_By Userid
Created_date Sysdate
Last_Updated_By Userid
Last_Update_Date Sysdate
Default_to_location_Id Default location for the org in HR_LOCATIONS
that has a customer in po_assosiation_locations
Deliver_to_requestor_ID Buyer id of item
Destination_Organization_id Org_id
Destination_Subinventory Subinventory
Destination_type_code 'INVENTORY'
Quantity Order Quantity
Requisition_Type 'INTERNAL'/'PURCHASE'
Source Organization Id Source Org
Source Subinventory Source Sub
Source Type Code 'INVENTORY'/'VENDOR'
GL_date sysdate
Interface_source_code 'INV'
UOM_CODE Primary UOM
Requisition_type 'INTERNAL'/'PURCHASE'
Suggested_vendor_id Supplier_ID
Suggested_vendor_site Supplier_Site_Id
Suggested_Buyer_Id Buyer_Id
*/
EXCEPTION
WHEN FND_API.G_EXC_ERROR THEN
Raise FND_API.G_EXC_ERROR;
l_trohdr_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_trohdr_rec.last_update_date := sysdate;
l_trohdr_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
select location_control_code
into l_item_locator_control_code
from mtl_system_items
where organization_id = p_kanban_card_rec_tbl(1).organization_id
and inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id;
select locator_type
into l_from_sub_locator_type
from mtl_secondary_inventories
where organization_id = p_kanban_card_rec_tbl(1).organization_id
and secondary_inventory_name = p_kanban_card_rec_tbl(1).source_subinventory;
select locator_type
into l_to_sub_locator_type
from mtl_secondary_inventories
where organization_id = p_kanban_card_rec_tbl(1).organization_id
and secondary_inventory_name = p_kanban_card_rec_tbl(1).subinventory_name;
select stock_locator_control_code
into l_org_locator_control_code
from mtl_parameters
where organization_id = p_kanban_card_rec_tbl(1).organization_id;
l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
l_trolin_tbl(l_order_count).last_update_date := sysdate;
l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
SELECT project_id,task_id
INTO l_trolin_tbl(l_order_count).project_id,
l_trolin_tbl(l_order_count).task_id
FROM mtl_item_locations
WHERE inventory_location_id = p_kanban_card_rec_tbl(1).source_locator_id and organization_id = p_kanban_card_rec_tbl(1).organization_id;
SELECT MKP.auto_allocate_flag INTO l_auto_Allocate_flag
FROM Mtl_Kanban_Pull_Sequences MKP
WHERE MKP.pull_sequence_id=p_kanban_card_rec_tbl(1).pull_sequence_id;
SELECT MTRH.request_number INTO l_mo_request_number
FROM Mtl_Txn_Request_Headers MTRH
WHERE MTRH.Header_id = p_kanban_card_rec_tbl(1).document_header_id;
SELECT NVL(project_reference_enabled,2)
INTO l_project_reference_enabled
FROM mtl_parameters
WHERE organization_id = p_kanban_card_rec_tbl(1).organization_id;
SELECT project_id
INTO l_project_id
FROM mtl_item_locations
WHERE inventory_location_id = p_kanban_card_rec_tbl(1).locator_id
AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
SELECT task_id
INTO l_task_id
FROM mtl_item_locations
WHERE NVL(project_id,-999) = NVL(l_project_id, -111)
AND inventory_location_id =p_kanban_card_rec_tbl(1).locator_id
AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
Insert into WIP_JOB_SCHEDULE_INTERFACE
(LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
GROUP_ID,
PROCESS_PHASE,
PROCESS_STATUS,
SOURCE_CODE,
ORGANIZATION_ID,
LOAD_TYPE,
FIRST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE,
PRIMARY_ITEM_ID,
START_QUANTITY,
STATUS_TYPE,
LINE_ID,
kanban_card_id,
project_id,
task_id
)
values
(
sysdate ,
FND_GLOBAL.USER_ID,
sysdate ,
FND_GLOBAL.USER_ID,
p_kanban_card_rec_tbl(1).current_replnsh_cycle_id,
2,
1,
'INV',
p_kanban_card_rec_tbl(1).organization_id,
1, /* Discrete job */
l_first_unit_start_date,
l_last_unit_completion_date,
p_kanban_card_rec_tbl(1).inventory_item_id,
p_kanban_card_rec_tbl(l_order_count).Kanban_size,
3,
p_kanban_card_rec_tbl(1).wip_line_id,
p_kanban_card_rec_tbl(l_order_count).kanban_card_id,
l_project_id,
l_task_id
);
select MAXIMUM_RATE into line_rate
from WIP_LINES
where
LINE_ID = p_kanban_card_rec_tbl(1).wip_line_id AND
organization_id = p_kanban_card_rec_tbl(1).organization_id;
select 'Y' into rep_sched_exist
from WIP_JOB_SCHEDULE_INTERFACE
where
primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
organization_id = p_kanban_card_rec_tbl(1).organization_id AND
line_id = p_kanban_card_rec_tbl(1).wip_line_id AND
load_type = 2 AND
process_phase <> 4 AND
to_date(creation_date,'DD-MON-RR') = to_date(SYSDATE,'DD-MON-RR')
For Update of start_quantity NOWAIT;
SELECT NVL(project_reference_enabled,2)
INTO l_project_reference_enabled
FROM mtl_parameters
WHERE organization_id = p_kanban_card_rec_tbl(1).organization_id;
SELECT project_id
INTO l_project_id
FROM mtl_item_locations
WHERE inventory_location_id = p_kanban_card_rec_tbl(1).locator_id
AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
SELECT task_id
INTO l_task_id
FROM mtl_item_locations
WHERE NVL(project_id,-999) = NVL(l_project_id, -111)
AND inventory_location_id =p_kanban_card_rec_tbl(1).locator_id
AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
Insert into WIP_JOB_SCHEDULE_INTERFACE
( LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
GROUP_ID,
PROCESS_PHASE,
PROCESS_STATUS,
SOURCE_CODE,
ORGANIZATION_ID,
LOAD_TYPE,
FIRST_UNIT_START_DATE,
-- FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE,
DAILY_PRODUCTION_RATE,
PROCESSING_WORK_DAYS,
PRIMARY_ITEM_ID,
START_QUANTITY,
STATUS_TYPE,
line_id,
project_id,
task_id
)
values
(
SYSDATE ,
FND_GLOBAL.USER_ID,
SYSDATE ,
FND_GLOBAL.USER_ID,
p_kanban_card_rec_tbl(1).current_replnsh_cycle_id,
2,
1,
'INV',
p_kanban_card_rec_tbl(1).organization_id,
2, /* Rep schedule */
l_first_unit_start_date,
l_last_unit_completion_date,
-- SYSDATE,
-- SYSDATE+(p_fixed_lead_time +
-- (p_var_lead_time*p_kanban_card_rec_tbl(l_order_count).kanban_size)),
line_rate,
p_kanban_card_rec_tbl(l_order_count).Kanban_size / line_rate ,
p_kanban_card_rec_tbl(1).inventory_item_id,
p_kanban_card_rec_tbl(l_order_count).Kanban_size,
1,
p_kanban_card_rec_tbl(1).wip_line_id,
l_project_id,
l_task_id
);
Update WIP_JOB_SCHEDULE_INTERFACE
set START_QUANTITY = START_QUANTITY + total_qty ,
-- LAST_UNIT_COMPLETION_DATE = SYSDATE + (p_fixed_lead_time +
-- p_var_lead_time*(START_QUANTITY + total_qty)),
PROCESSING_WORK_DAYS = (START_QUANTITY + total_qty)/ line_rate,
GROUP_ID = p_kanban_card_rec_tbl(1).current_replnsh_cycle_id
where
primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
organization_id = p_kanban_card_rec_tbl(1).organization_id AND
line_id = p_kanban_card_rec_tbl(1).wip_line_id AND
load_type = 2 AND
process_phase = 2 AND
group_id = p_kanban_card_rec_tbl(1).current_replnsh_cycle_id;
l_flow_schedule_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_flow_schedule_rec.last_update_date := sysdate;
select wsm_lot_sm_ifc_header_s.nextval
into l_header_id
from dual;
select wsm_lot_job_interface_s.NEXTVAL
into l_group_id
from dual;
select FND_Profile.value('WIP_JOB_PREFIX')||wip_job_number_s.nextval
INTO l_job_name
from dual;
INSERT INTO WSM_LOT_JOB_INTERFACE
(mode_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
group_id,
source_line_id,
organization_id,
load_type,
status_type,
primary_item_id,
job_name,
start_Quantity,
process_Status,
first_unit_start_date,
last_unit_completion_date,
scheduling_method,
completion_subinventory,
completion_locator_id,
class_code,
description,
bom_revision_date,
routing_revision_date,
header_id,
kanban_card_id)
VALUES
(l_mode_flag,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_group_id,
Decode(l_mode_flag, 1,null,l_header_id),
p_kanban_card_rec_tbl(1).organization_id,
5, --job creation
3, --1:unreleased, 3: released
p_kanban_card_rec_tbl(1).inventory_item_id,
l_job_name,
Nvl(p_kanban_card_rec_tbl(l_order_count).replenish_quantity,p_kanban_card_rec_tbl(l_order_count).kanban_size),
1,
l_first_unit_start_date,
l_last_unit_completion_date,
l_scheduling_method,
p_kanban_card_rec_tbl(1).subinventory_name,
p_kanban_card_rec_tbl(1).locator_id,
'',
null,
'',
'',
l_header_id,
p_kanban_card_rec_tbl(l_order_count).kanban_card_id);
insert into wsm_starting_lots_interface
(header_id,
lot_number,
inventory_item_id,
revision,
organization_id,
quantity,
subinventory_code,
locator_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login )
values
( l_header_id,
p_kanban_card_rec_tbl(l_order_count).lot_number,
p_kanban_card_rec_tbl(l_order_count).lot_item_id,
p_kanban_card_rec_tbl(l_order_count).lot_item_revision,
p_kanban_card_rec_tbl(l_order_count).organization_id,
p_kanban_card_rec_tbl(l_order_count).lot_quantity,
p_kanban_card_rec_tbl(l_order_count).lot_subinventory_code,
p_kanban_card_rec_tbl(l_order_count).lot_location_id ,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id);
select nvl(repetitive_planning_flag,'N'), nvl(fixed_lead_time,0),
nvl(variable_lead_time,0)
into v_rep_flag, v_fixed_lead_time, v_var_lead_time
from MTL_SYSTEM_ITEMS_KFV
where
inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
organization_id = p_kanban_card_rec_tbl(1).organization_id;
select nvl(cfm_routing_flag,0),line_id into v_cfm_flag, v_wip_line_id
from BOM_OPERATIONAL_ROUTINGS
where
assembly_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
organization_id = p_kanban_card_rec_tbl(1).organization_id AND
alternate_routing_designator is NULL;
SELECT 'Y' INTO l_is_lot_control
FROM dual WHERE exists
(SELECT 1 FROM mtl_system_items
WHERE
organization_id = p_kanban_card_rec_tbl(1).organization_id
AND inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
AND lot_control_code = 2);
select line_id
into v_wip_line_id
from wip_repetitive_items
where load_distribution_priority =
(select min(load_distribution_priority)
from wip_repetitive_items
where organization_id = p_kanban_card_rec_tbl(1).organization_id
and primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
group by organization_id,primary_item_id)
and organization_id = p_kanban_card_rec_tbl(1).organization_id
and primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
and rownum < 2;
select nvl(cfm_routing_flag,0) into v_cfm_flag
from BOM_OPERATIONAL_ROUTINGS
where
assembly_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
organization_id = p_kanban_card_rec_tbl(1).organization_id AND
line_id = p_kanban_card_rec_tbl(1).wip_line_id AND
nvl(priority,0) = ( select min(nvl(priority,0))
from bom_operational_routings
where
assembly_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
organization_id = p_kanban_card_rec_tbl(1).organization_id AND
line_id = p_kanban_card_rec_tbl(1).wip_line_id ) AND
rownum < 2 ;
SELECT 'Y' INTO l_is_lot_control
FROM dual WHERE exists
(SELECT 1 FROM mtl_system_items
WHERE
organization_id = p_kanban_card_rec_tbl(1).organization_id
AND inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
AND lot_control_code = 2);
Select msi.Description,Primary_Uom_Code,Inventory_Asset_Flag,
nvl(mss.ENCUMBRANCE_ACCOUNT,
nvl(msi.Encumbrance_Account,Org.Encumbrance_Account)),
decode(msi.inventory_asset_flag, 'Y', mss.material_account,
nvl(mss.expense_account,nvl(msi.expense_account,org.expense_account))),
Org.Ap_accrual_account,
Org.invoice_price_var_account,
nvl(mss.preprocessing_lead_time,nvl(msi.preprocessing_lead_time,0)),
nvl(mss.processing_lead_time,nvl(msi.full_lead_time,0)),
nvl(mss.postprocessing_lead_time,nvl(msi.postprocessing_lead_time,0)),
msi.revision_qty_control_code
Into l_Item_Description,
l_Primary_Uom_Code,
l_Inventory_Asset_Flag,
l_Encumb_Account_Id,
l_Charge_Account_Id,
l_Accrual_Account_Id,
l_Invoice_Var_Account_Id,
l_PreProcess_lead_Time,
l_Process_lead_Time,
l_PostProcess_lead_Time,
l_Revision_qty_control_code
From Mtl_System_Items msi,
mtl_Parameters org,
mtl_secondary_inventories mss
Where Msi.Organization_Id = l_kanban_card_Rec_Tbl(1).Organization_id
And Msi.Inventory_Item_Id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
And org.Organization_Id = l_kanban_card_Rec_Tbl(1).Organization_Id
And mss.Organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
And mss.secondary_inventory_name = l_kanban_card_Rec_Tbl(1).Subinventory_Name;
Select nvl(ENCUMBRANCE_ACCOUNT,l_Encumb_Account_Id),
nvl(preprocessing_lead_time,l_PreProcess_lead_Time)
+ nvl(processing_lead_time,l_Process_lead_Time)
+ nvl(postprocessing_lead_time,l_PostProcess_lead_Time)
Into l_budget_Account_Id,
l_need_by_time
From mtl_item_sub_inventories
Where Organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
And Inventory_Item_Id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
And secondary_inventory = l_kanban_card_Rec_Tbl(1).Subinventory_Name;
select nvl(f.req_encumbrance_flag,'N'),o.operating_unit
into l_encumb_flag,l_po_org_Id
from financials_system_params_all f,
org_organization_definitions o
where o.organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
And nvl(f.org_id,-99) = nvl(o.operating_unit,-99);
select c1.calendar_date
into l_need_by_date
from mtl_parameters o,
bom_calendar_dates c1,
bom_calendar_dates c
where o.organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
and c1.calendar_code = c.calendar_code
and c1.exception_set_id = c.exception_set_id
and c1.seq_num = (c.next_seq_num + trunc(nvl(p_lead_time,l_need_by_time)))
and c.calendar_code = o.CALENDAR_CODE
and c.exception_set_id = o.CALENDAR_EXCEPTION_SET_ID
and c.calendar_date = trunc(sysdate);
select distinct org.location_id
into l_deliver_location_id
from hr_organization_units org,
hr_locations loc,
po_location_associations_all pla
where org.organization_id =
l_kanban_card_Rec_Tbl(1).Organization_id
and org.location_id = loc.location_id
and pla.location_id = loc.location_id;
select org.location_id
into l_deliver_location_id
from hr_organization_units org,
hr_locations loc
where org.organization_id =
l_kanban_card_Rec_Tbl(1).Organization_id
and org.location_id = loc.location_id;
select MAX(revision)
into l_revision
from mtl_item_revisions mir
where inventory_item_id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
and organization_id = l_kanban_card_Rec_Tbl(1).organization_Id
and effectivity_date < SYSDATE
and implementation_date is not null /* Added for bug 7110794 */
and effectivity_date =
(
select MAX(effectivity_date)
from mtl_item_revisions mir1
where mir1.inventory_item_id = mir.inventory_item_id
and mir1.organization_id = mir.organization_id
and implementation_date is not null /* Added for bug 7110794 */
and effectivity_date < SYSDATE
);
select employee_id
into l_buyer_id
from fnd_user
where user_id = FND_GLOBAL.USER_ID;
select meaning into msg
from mfg_lookups
where lookup_type = 'INV_MMX_RPT_MSGS'
and lookup_code = 1;
Select Kanban_card_Id,kanban_size,
NULL lot_item_id,null lot_number,NULL lot_item_revision,
NULL lot_subinventory_code,NULL lot_location_id,NULL lot_quantity,
NULL replenish_quantity
From Mtl_Kanban_Cards
Where Pull_Sequence_Id = p_Kanban_Card_Rec.Pull_Sequence_Id
-- And Card_Status = INV_Kanban_PVT.G_Card_Status_Active
And Supply_Status = INV_Kanban_PVT.G_Supply_Status_Wait
And Nvl(Supplier_Id,-1) = Nvl(p_Kanban_Card_Rec.Supplier_Id,-1)
And Nvl(Supplier_Site_Id,-1) =
Nvl(p_Kanban_Card_Rec.Supplier_Site_Id,-1)
And Nvl(Source_Organization_Id,-1) =
Nvl(p_Kanban_Card_Rec.Source_Organization_Id,-1)
And Nvl(Source_Subinventory,'#?#') =
Nvl(p_Kanban_Card_Rec.Source_Subinventory,'#?#')
And Nvl(Source_Locator_Id,-1) =
Nvl(p_Kanban_Card_Rec.Source_Locator_Id,-1)
And Nvl(wip_line_id,-1) =
Nvl(p_Kanban_Card_Rec.wip_line_id,-1)
-- Following condition added as a bugfix for bug#3389681 to prevent consideration of
-- current card if it is in wait status as it will be considered twice.
And Kanban_card_Id <> p_Kanban_Card_Rec.Kanban_card_Id
For Update Of Supply_Status NoWait;
l_Kanban_Card_tbl.delete;
l_Wait_Kanban_card_Tbl.Delete;
Select *
Into l_Pull_Sequence_Rec
From Mtl_Kanban_Pull_Sequences
Where Pull_Sequence_Id = P_Kanban_Card_Rec.Pull_Sequence_Id
For Update Of Minimum_Order_Quantity NOWait;
Update Mtl_Kanban_Cards
Set Supply_Status = p_card_supply_status,
Current_Replnsh_Cycle_Id = l_Current_Replenish_Cycle_Id,
Last_Update_Date = SYSDATE,
Last_Updated_By = FND_GLOBAL.USER_ID
Where Kanban_Card_Id = l_Kanban_card_Tbl(l_Card_Count).Kanban_card_Id;
INV_KanbanCard_PKG.Insert_Activity_For_Card(l_Kanban_Card_Rec);
keep in synch with the call to procedure update_pull_sequence_tbl */
l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
For pull_seq_rec IN (select pull_sequence_id from MTL_KANBAN_PULL_SEQUENCES
Where Kanban_plan_id = -1 ) LOOP
i := i + 1;
l_operation_tbl(i) := 0; --Storing 0 for update
update_pull_sequence_tbl( l_return_status, l_pull_seq_id_tbl, 'Y',l_operation_tbl );
PROCEDURE update_kanban_card_status
(p_Card_Status IN Number,
p_pull_sequence_id IN Number)
IS
l_kanban_card_rec INV_Kanban_PVT.kanban_card_rec_type;
SELECT kanban_card_id
FROM mtl_kanban_cards
WHERE pull_sequence_id = p_pull_sequence_id ;
inv_kanbancard_pkg.update_card_status
(p_kanban_card_rec =>l_kanban_card_rec,
p_card_status => p_card_status);
END update_kanban_card_status;
select
Nvl(repetitive_planning_flag,'N'), lot_control_code
into
l_rep_flag, l_lot_control
from MTL_SYSTEM_ITEMS
where
inventory_item_id = l_assembly_item_id AND
organization_id = l_organization_id;
SELECT nvl(cfm_routing_flag,0)
into l_cfm_flag
from BOM_OPERATIONAL_ROUTINGS
where
assembly_item_id = l_assembly_item_id AND
organization_id = l_organization_id AND
alternate_routing_designator is NULL;
/*Bug 3740514--New procedure to check if the card status should be updated.*/
PROCEDURE update_card_and_card_status(p_kanban_card_id IN NUMBER, p_supply_status IN NUMBER, p_update OUT NOCOPY BOOLEAN) IS
CURSOR mtl_kca IS
SELECT replenishment_cycle_id
, document_header_id
, document_detail_id
FROM mtl_kanban_card_activity
WHERE kanban_card_id = p_kanban_card_id
AND document_header_id IS NOT NULL
ORDER BY kanban_activity_id DESC;
SELECT NVL(quantity_delivered, 0)
FROM po_distributions_all
WHERE po_release_id = po_rel_id
AND po_distribution_id = po_dist_id;
p_update := TRUE; -- By Default update the kanban card and kanban card activity
SELECT document_type
, replenishment_cycle_id
INTO l_doc_type_id
, l_max_rep_id
FROM mtl_kanban_card_activity
WHERE kanban_card_id = p_kanban_card_id
AND document_type IS NOT NULL
AND document_type <> fnd_api.g_miss_num
AND replenishment_cycle_id = (SELECT MAX(replenishment_cycle_id)
FROM mtl_kanban_card_activity
WHERE kanban_card_id = p_kanban_card_id);
mydebug('Multiple document types and maximum replenishment cycle id returned for kanban card Id ' || p_kanban_card_id||' ;Hence disallowing Supply status update');
p_update := FALSE;
p_update := FALSE;
Release is trying to update the card and card activity status, which should not be allowed */
IF (l_del_qty = 0) THEN
p_update := FALSE;
SELECT max(requisition_line_id) into l_max_req
FROM po_requisition_lines
WHERE kanban_card_id = p_kanban_card_id;
SELECT 1 INTO l_req
FROM po_requisitions_interface
WHERE kanban_card_id = p_kanban_card_id;
SELECT 1 INTO l_req
FROM po_requisition_lines
WHERE kanban_card_id = p_kanban_card_id
AND requisition_line_id = l_max_req
AND line_location_id IS NULL;
SELECT 1 INTO l_req
FROM po_requisition_lines prl1
WHERE prl1.kanban_card_id = p_kanban_card_id
AND prl1.requisition_line_id = l_max_req
AND prl1.line_location_id IS NOT NULL
AND (EXISTS (SELECT '1' FROM po_headers poh
WHERE EXISTS ( SELECT '1' FROM po_line_locations pll
WHERE pll.line_location_id = prl1.line_location_id
AND pll.po_header_id = poh.po_header_id
AND nvl(poh.authorization_status,'%%') <> 'APPROVED'))
OR EXISTS (SELECT '1' FROM po_releases pr
WHERE EXISTS ( SELECT '1' FROM po_line_locations pll
WHERE pll.line_location_id = prl1.line_location_id
AND pll.po_release_id = pr.po_release_id
AND nvl(pr.authorization_status,'%%') <> 'APPROVED')));
p_update := FALSE;
END update_card_and_card_status;
SELECT MTRL.line_id , MTRL.inventory_item_id,MTRL.organization_id,MTRL.quantity
FROM MTL_TXN_REQUEST_LINES MTRL
WHERE MTRL.header_id = p_mo_header_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_header_id
FROM DUAL;
SELECT DECODE(serial_number_control_code,1,'F','T')
INTO l_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_mo_line_rec.inventory_item_id
AND organization_id = l_mo_line_rec.organization_id;
update mtl_txn_request_lines
set quantity_detailed = l_detailed_quantity
where line_id=l_mo_line_rec.line_id;