Interface for Item Onhand

Interface for Item Onhand 

TO MIGRATE ITEM ON HAND QUANTITY , LOT and SERIAL NO


Interface Tables are :-
------------------------
mtl_transactions_interface
mtl_transaction_lots_interface
mtl_serial_numbers_interface


How to run the Import Pgm :
-------------------------------
Inventory Super User ->
Oracle Inventory ->
Setup ->
Transactions ->
Interface Manager ->
SubInventory ->
Material Transaction ->
Tool from Menu ->
Launch Manager ->
Process Transaction Interface
-----------------------------------
If error is occurring go to Transactions -> Open Transaction Interface -> Error -> See error
After correcting the error apply the below code
/* update mtl_transactions_interface
set process_flag = 1,
lock_flag = 2,
transaction_mode = 3,
validation_required = null,
error_code = null,
error_explanation = null
where process_flag in (1,3); */

***********************************************************************************************

The script to migrate legacy data into interface tables


CREATE OR REPLACE procedure xxx_onhand_qty_update
as

x number:=null;
l_err_msg varchar2(2000);
l_row_status varchar2(3);
l_inventory_item_id number:=null;
l_organization_id number;
l_count number:=null;
l_inventory_location_id number := null;

cursor c_onhand is
select *
from xxx_tab_onhand_stg
where nvl(row_status,'N') = 'N'
order by from_serial;

begin

for c_onhand_rec in c_onhand
loop
l_row_status := 'Y';

begin
select organization_id
into l_organization_id
from org_organization_definitions
where organization_code = c_onhand_rec.sub_inventory_code;
exception
when others then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'No Organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
end;

select count(*) into l_count
from mtl_system_items_b
where upper(segment1) =upper(c_onhand_rec.item_name)
and organization_id =l_organization_id
and inventory_item_status_code='Active';

if l_count=0 then

update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'No item existing in Given organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
elsif l_count>1 then

update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Multiple items existing Given organization.'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
elsif l_count=1 then

select inventory_item_id into l_inventory_item_id
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id
and inventory_item_status_code='Active';

if l_inventory_item_id is not null then

update xxx_tab_onhand_stg
set transaction_uom=(select distinct primary_uom_code
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name))
,inventory_item_id=(select distinct(inventory_item_id)
from mtl_system_items_b
where upper(segment1)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id)
,dist_account=(select code_combination_id
from gl_code_combinations
where segment4 = 1327108)
,userid= (select user_id
from fnd_user
where user_name = 'XXXX')
,transaction_interface_id=mtl_material_transactions_s.nextval
,transaction_type_id = (select transaction_type_id
from mtl_transaction_types
where transaction_type_name = 'Miscellaneous receipt')
,row_status='Y'
,organization_id = (select organization_id
from org_organization_definitions
where organization_code = c_onhand_rec.sub_inventory_code)
where item_name = c_onhand_rec.item_name;
commit;
else
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Inventory Item Id is not Existing'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
end if;

else
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Item Name or Organization ID is not Valid'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
commit;
end if;
commit;

begin
select inventory_location_id
into l_inventory_location_id
from mtl_item_locations
where segment1 = c_onhand_rec.locator
and subinventory_code = trim(c_onhand_rec.sub_inventory_code);
exception
when others then
update xxx_tab_onhand_stg
set row_status='N'
,error_message=error_message||'Invalid Locator'
where item_name = c_onhand_rec.item_name;
l_row_status := 'N';
end;

if l_row_status = 'Y' then

--On hand quantity Migration
begin

insert into mtl_transactions_interface
(
transaction_type_id
,transaction_uom
,transaction_date
,organization_id
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,transaction_mode
,process_flag
,source_header_id
,source_line_id
,source_code
,lock_flag
,flow_schedule
,scheduled_flag
,transaction_header_id
,inventory_item_id
,transaction_interface_id
,subinventory_code
,distribution_account_id
,transaction_cost
,locator_id
,transaction_reference
)
select
transaction_type_id
,transaction_uom
,'30-DEC-2007'
,organization_id
,transaction_quantity
,sysdate
,userid
,sysdate
,userid
,3
,1
,transaction_interface_id
,transaction_interface_id
,'inv'
,2
,'Y'
,2
,transaction_interface_id
,inventory_item_id
,transaction_interface_id
,sub_inventory_code
,dist_account
,transaction_cost
, l_inventory_location_id
,'Migration'
from xxx_tab_onhand_stg
where upper(item_name)=upper(c_onhand_rec.item_name)
and organization_id=l_organization_id;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to mtl_transactions_interface : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;
end;
---- END
-----ON HAND QUANTITY LOTS MIGRATION
begin

insert into mtl_transaction_lots_interface
(
transaction_interface_id
,lot_number
,transaction_quantity
,last_update_date
,last_updated_by
,creation_date
,created_by
,product_code
,last_update_login
,product_transaction_id
,primary_quantity
,lot_expiration_date)
select transaction_interface_id
,lot_number
,transaction_quantity
,sysdate
,userid
,sysdate
,userid
,'inv'
,userid
,transaction_interface_id
,transaction_quantity
, expiry_date
from xxx_tab_onhand_stg
where item_name = c_onhand_rec.item_name;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to mtl_transaction_lots_interface : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;

end;
--- END
---- SERIAL NUMBER MIGRATION -----------
begin

insert into mtl_serial_numbers_interface
(
transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
fm_serial_number,
to_serial_number,
product_code,
product_transaction_id
)
select
transaction_interface_id,
sysdate,
userid,
sysdate,
userid,
userid,
from_serial,
to_serial,
'inv',
transaction_interface_id
from xxx_tab_onhand_stg
where item_name = c_onhand_rec.item_name;
commit;
exception
when others then
l_err_msg := sqlerrm;
update xxx_tab_onhand_stg
set error_message = 'Exception While Inserting to MTL_SERIAL_NUMBERS_INTERFACE : '||l_err_msg
,row_status = 'N'
where item_name = c_onhand_rec.item_name;
end;

end if;
end loop;


END xxx_onhand_qty_update;

API to Cancel a Sales Order (OE_ORDER_PUB.PROCESS_ORDER)

TEST INSTANCE:  R12.1.1

SET SERVEROUTPUT ON;

DECLARE

v_api_version_number           NUMBER  := 1;

v_return_status                VARCHAR2 (2000);

v_msg_count                    NUMBER;

v_msg_data                     VARCHAR2 (2000);

-- IN Variables --

v_header_rec                   oe_order_pub.header_rec_type;

v_line_tbl                     oe_order_pub.line_tbl_type;

v_action_request_tbl           oe_order_pub.request_tbl_type;

v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

-- OUT Variables --

v_header_rec_out               oe_order_pub.header_rec_type;

v_header_val_rec_out           oe_order_pub.header_val_rec_type;

v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;

v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;

v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;

v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;

v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;

v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;

v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;

v_line_tbl_out                 oe_order_pub.line_tbl_type;

v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;

v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;

v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;

v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;

v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;

v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;

v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;

v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;

v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;

v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;

v_action_request_tbl_out       oe_order_pub.request_tbl_type;

v_msg_index                    NUMBER;

v_data                         VARCHAR2 (2000);

v_loop_count                   NUMBER;

v_debug_file                   VARCHAR2 (200);

b_return_status                VARCHAR2 (200);

b_msg_count                    NUMBER;

b_msg_data                     VARCHAR2 (2000);

BEGIN

DBMS_OUTPUT.PUT_LINE('Starting of script');

-- Setting the Enviroment --

mo_global.init('ONT');

fnd_global.apps_initialize ( user_id      => 2585

                            ,resp_id      => 50864

                            ,resp_appl_id => 660);

mo_global.set_policy_context('S',83);

-- CANCEL HEADER --

v_header_rec                        := oe_order_pub.g_miss_header_rec;

v_header_rec.operation              := OE_GLOBALS.G_OPR_UPDATE;

v_header_rec.header_id              := 6006;

v_header_rec.cancelled_flag         := 'Y';

v_header_rec.change_reason          := 'Not provided';

DBMS_OUTPUT.PUT_LINE('Starting of API');

-- CALLING THE API TO CANCEL AN ORDER --

OE_ORDER_PUB.PROCESS_ORDER (

p_api_version_number            => v_api_version_number

, p_header_rec                  => v_header_rec

, p_line_tbl                    => v_line_tbl

, p_action_request_tbl          => v_action_request_tbl

, p_line_adj_tbl                => v_line_adj_tbl

-- OUT variables

, x_header_rec                  => v_header_rec_out

, x_header_val_rec              => v_header_val_rec_out

, x_header_adj_tbl              => v_header_adj_tbl_out

, x_header_adj_val_tbl          => v_header_adj_val_tbl_out

, x_header_price_att_tbl        => v_header_price_att_tbl_out

, x_header_adj_att_tbl          => v_header_adj_att_tbl_out

, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out

, x_header_scredit_tbl          => v_header_scredit_tbl_out

, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out

, x_line_tbl                    => v_line_tbl_out

, x_line_val_tbl                => v_line_val_tbl_out

, x_line_adj_tbl                => v_line_adj_tbl_out

, x_line_adj_val_tbl            => v_line_adj_val_tbl_out

, x_line_price_att_tbl          => v_line_price_att_tbl_out

, x_line_adj_att_tbl            => v_line_adj_att_tbl_out

, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out

, x_line_scredit_tbl            => v_line_scredit_tbl_out

, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out

, x_lot_serial_tbl              => v_lot_serial_tbl_out

, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out

, x_action_request_tbl          => v_action_request_tbl_out

, x_return_status               => v_return_status

, x_msg_count                   => v_msg_count

, x_msg_data                    => v_msg_data

);

DBMS_OUTPUT.PUT_LINE('Completion of API');

IF v_return_status = fnd_api.g_ret_sts_success THEN

    COMMIT;

    DBMS_OUTPUT.put_line ('Order Cancellation Success : '||v_header_rec_out.header_id);

ELSE

    DBMS_OUTPUT.put_line ('Order Cancellation failed:'||v_msg_data);

    ROLLBACK;

    FOR i IN 1 .. v_msg_count

    LOOP

      v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

      dbms_output.put_line( i|| ') '|| v_msg_data);

    END LOOP;

END IF;

END;

/