[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function Delete_Children(Interface_id in number)
return number is
begin
delete from mtl_transaction_lots_interface
where transaction_interface_id = Interface_Id ;
delete from mtl_serial_numbers_interface
where transaction_interface_id = Interface_Id ;
delete from mtl_serial_numbers_interface msni
where msni.transaction_interface_id in
(select serial_transaction_temp_id
from mtl_transaction_lots_interface mtli
where mtli.transaction_interface_id = Interface_Id);
end Delete_Children ;
Function Delete_Substitutes(p_parent_id number)
return number is
begin
delete from mtl_transactions_interface
where parent_id = p_parent_id
and substitution_type_id is not null;
end Delete_Substitutes;
select transaction_action_id, Sign(transaction_quantity)
into x_txn_act_id, x_direction
from mtl_transactions_interface
where transaction_interface_id = p_interface_id ;
Update mtl_transactions_interface
set transaction_source_type_id = nvl(transaction_source_type_id,5),
flow_schedule = nvl(flow_schedule,'Y'),
transaction_action_id = Decode( x_txn_act_id,
31,Decode(Sign(transaction_quantity),
-1, 1,
33),
32,Decode(Sign(transaction_quantity),
-1,34 ,
27),
30,Decode(x_direction,
1,Decode(Sign(transaction_quantity),
-1, 1,
33),
-1,Decode(Sign(transaction_quantity),
-1,34 ,
27))),
transaction_type_id = Decode( x_txn_act_id,
31, Decode(Sign(transaction_quantity),
-1, 35,
38),
32, Decode(Sign(transaction_quantity),
-1, 48,
43),
30, Decode(x_direction,
1,Decode(Sign(transaction_quantity),
-1, 35,
38),
-1,Decode(Sign(transaction_quantity),
-1, 48,
43))),
source_project_id = decode(p_src_prj_id, -1, null,
p_src_prj_id),
source_task_id = decode(p_src_tsk_id, -1, null,
p_src_tsk_id),
transaction_header_id = p_txn_hdr_id,
transaction_source_id = p_wip_entity_id,
transaction_date = to_date(p_transaction_date,WIP_CONSTANTS.DT_NOSEC_FMT)
where
substitution_type_id is NULL
AND process_flag = 2
AND parent_id = p_interface_id
AND organization_id = p_org_id ;
* - update the originals txn_id to the txn_id of
* - the substitutes
* 2. Delete :
* - Make sure the children are deleted.
*
* 3. Addition :
* - There is no impact for this, as the child
* - Information will be stored already.
*
* 4. Lot/Serial :
* - The transaction interface id of the original
* should be modfied to that of the substitution
*
* At the end of it all, it makes sure that the Substitutes are deleted
* In case of exception the calling function would be returned, so the
* calling program can perform the Roll Back.
************************************************************************/
function Cmp_Merge_Subs(
interface_id in number,
organization_id in number,
err_num in out nocopy number,
err_mesg in out nocopy varchar2
) return number is
/************************************************************************
-- THis Cursor is used for going through the substitutes one after the other
-- the order in which it would go through is :
-- 1. Initially Replacment
-- 2. Deletion
-- 3. Addition and
-- 4. Replace
************************************************************************/
CURSOR Substitute_Cursor(interface_id NUMBER,
Org_Id NUMBER) is
Select
SUBSTITUTION_TYPE_ID,
TRANSACTION_INTERFACE_ID,
OPERATION_SEQ_NUM,
INVENTORY_ITEM_ID,
SUBSTITUTION_ITEM_ID,
REVISION,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_UOM,
TRANSACTION_QUANTITY,
REASON_ID,
TRANSACTION_REFERENCE,
ORGANIZATION_ID
from mtl_transactions_interface
where parent_id = interface_id
and substitution_type_id is not null
and process_flag = 2
and organization_id = Org_Id
order by substitution_type_id;
SELECT Transaction_Interface_Id,
operation_seq_num,
inventory_item_id,
transaction_quantity
FROM mtl_transactions_interface
WHERE parent_id = interface_Id
AND substitution_type_id is NULL
AND process_flag = 2
AND operation_seq_num = Op_Seq
AND inventory_item_id = Source_Item
AND organization_id = Org_Id ;
-- 3 Delete
-- 4 Lot/Serial
-- I execute this cursor first as I believe that the
-- substitions information will be small compared
-- to the backflushed information
-- Note: The enteries for the Substitution Item Id and the
-- Inventory Item for the various kind of operation is
-- listed below :
-- Operation Subst. Item Inv Item
--
-- Replace X X
-- Deletion X
-- Addition X
-- Lot/Serial X
*****************************************************/
-- Get Reason, Op_Seq, Source, Substitute, Revision,
-- Supply_Locator, Supply_Subinv, Quantity, UOM, Department
For Substitute_Record IN
Substitute_Cursor(Interface_Id, Organization_Id) LOOP
-- This is for Replacement
if (Substitute_Record.Substitution_Type_Id = 1 ) then
/******************************************************
-- Conditions for Replacement
-- 1. Replace
-- a. If Op Seq and Item Exists
-- - Replace the Item
-- b. Else
-- (Cases: Op. Seq doesn't exist,
-- Op. Seq Exists but item doesn't)
-- - Error it out
******************************************************/
OPEN BackFlush_Cursor( interface_id,
Substitute_Record.operation_seq_num,
Substitute_Record.Inventory_Item_Id,
Substitute_Record.Organization_Id );
DELETE from mtl_transactions_interface
WHERE OPERATION_SEQ_NUM
= Substitute_Record.operation_seq_num
AND INVENTORY_ITEM_ID
= Substitute_Record.inventory_item_id
AND ORGANIZATION_ID
= Substitute_Record.organization_id
AND parent_id
= interface_id
AND Transaction_Interface_Id
= Txn_Interface_Id
AND Substitution_Type_Id is NULL;
UPDATE mtl_transactions_interface
SET INVENTORY_ITEM_ID =
Substitute_Record.Substitution_item_id,
Substitution_item_id =
NULL,
Substitution_type_id =
NULL ,
transaction_quantity =
NVL(transaction_quantity, x_transaction_qty)
where Transaction_Interface_id =
Substitute_Record.transaction_interface_id ;
-- 2. Delete
-- a. If Op Seq and Item Exists
-- - Delete1 the Item
-- b. Else
-- (Cases: Op. Seq doesn't exist,
-- Op. Seq Exists but item doesn't)
-- - Error it out
******************************************************/
OPEN BackFlush_Cursor( interface_id,
Substitute_Record.operation_seq_num,
Substitute_Record.inventory_item_id,
Substitute_Record.Organization_Id) ;
DELETE from mtl_transactions_interface
WHERE OPERATION_SEQ_NUM
= Substitute_Record.operation_seq_num
AND INVENTORY_ITEM_ID
= Substitute_Record.inventory_item_id
AND ORGANIZATION_ID
= Substitute_Record.organization_id
AND parent_id
= interface_id
AND Transaction_Interface_Id
= Txn_Interface_Id
AND Substitution_Type_Id is NULL;
if(Delete_Children(Txn_Interface_Id)=0) then
fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_DELETE');
fnd_message.set_name('WIP', 'WIP_ERROR_MERGE_DELETE');
UPDATE mtl_transactions_interface
SET SUBSTITUTION_TYPE_ID = NULL,
INVENTORY_ITEM_ID =
Substitute_Record.Substitution_Item_Id,
SUBSTITUTION_ITEM_ID =
NULL
where TRANSACTION_INTERFACE_ID =
Substitute_Record.Transaction_Interface_Id;
DELETE from mtl_transactions_interface
WHERE transaction_interface_id =
Substitute_Record.transaction_interface_id;
UPDATE mtl_transactions_interface
SET transaction_interface_id =
Substitute_Record.transaction_interface_id,
subinventory_code =
Substitute_Record.subinventory_code
where TRANSACTION_INTERFACE_ID =
Txn_Interface_Id ;
if(Delete_Substitutes(Interface_Id) = 0) then
return 0;