Item Sub Inventory Taansfer
CREATE OR REPLACe PROCEDURE APPS.xx_EXP_LOT_TRANSFER_AUTO_P (errbuf OUT VARCHAR2,retcode OUT VARCHAR2)
is
cursor c1 is
SELECT
moq.inventory_item_id item_id,
moq.organization_id organization_id,
sum(moq.primary_transaction_quantity) qty,
moq.subinventory_code from_subinv,
moq.locator_id from_locator_id,
mln.lot_number from_lot_number,
moq.transaction_uom_code uom_code,
mln.expiration_date exp_date
FROM apps.mtl_onhand_quantities_detail moq,apps.mtl_lot_numbers mln,MTL_ITEM_LOCATIONS mtl
WHERE moq.organization_id = mln.organization_id
AND moq.inventory_item_id = mln.inventory_item_id
and moq.lot_number=mln.lot_number
and mln.expiration_date <= sysdate
and moq.inventory_item_id IN (114,
772670,
16991,
278932,
17642,
768202,
3878997,
12072,
17756,
1882231,
4654,
776043)
and moq.organization_id = mtl.organization_id
AND moq.locator_id = mtl.inventory_location_id
AND mtl.segment1<>'07'
and upper(moq.subinventory_code) not like '%DIS%'
and not exists (select 1 from mtl_secondary_inventories where upper(secondary_inventory_name)=upper(moq.subinventory_code) and upper(description) like '%DISPOSAL%')
and not exists (select * from MTL_PHYSICAL_INVENTORIES where upper(physical_inventory_name) =upper(moq.subinventory_code) and last_adjustment_date is null)
group by moq.inventory_item_id, moq.organization_id,moq.subinventory_code,moq.locator_id, mln.lot_number,moq.transaction_uom_code,mln.expiration_date
order by moq.inventory_item_id, moq.organization_id,moq.subinventory_code, mln.lot_number;
lv_transaction_header_id number;
lv_transaction_interface_id number;
lv_transaction_type_id number;
lv_to_subinv varchar2(240);
lv_to_locator_id number;
l_return_status varchar2(100);
l_msg_cnt number;
l_msg_data varchar2(500);
l_trans_count varchar2(100);
l1_return_status VARCHAR2(1);
l1_msg_count NUMBER;
l1_msg_data VARCHAR2(2000);
lv_interface_error VARCHAR2(2000);
retval number;
begin
fnd_file.put_line(fnd_file.log,'--------------------------Start------------------------');
for i in c1
loop
lv_to_subinv:=null;
lv_to_locator_id:=null;
lv_interface_error:=null;
fnd_file.put_line(fnd_file.log,'*----------------------------------------------------------------------------------------------------*');
lv_transaction_header_id:=mtl_material_transactions_s.nextval;
lv_transaction_interface_id:=mtl_material_transactions_s.nextval;
--------------------Store transaction type id in Any lookup, so that it can be used in future customizations---------
select transaction_type_id
into lv_transaction_type_id
from mtl_transaction_types
where transaction_type_name = 'Subinventory Transfer';
begin
select msl.subinventory_code to_subinv,secondary_locator to_locator_id
into lv_to_subinv,lv_to_locator_id
from MTL_SECONDARY_LOCATORS msl,MTL_ITEM_LOCATIONS mil
where 1=1
and msl.secondary_locator=mil.inventory_location_id
and mil.organization_id=mil.organization_id
and msl.inventory_item_id=i.item_id
and mil.organization_id=i.organization_id
and description='Automated Expired'
and rownum=1;
exception when others then
lv_to_subinv:=null;
lv_to_locator_id:=null;
end;
if lv_to_subinv is null then
select distinct msl.subinventory_code to_subinv,inventory_location_id to_locator_id
into lv_to_subinv,lv_to_locator_id
from MTL_SECONDARY_LOCATORS msl,MTL_ITEM_LOCATIONS mil
where 1=1
and msl.subinventory_code=mil.subinventory_code
and msl.organization_id=mil.organization_id
and mil.organization_id=i.organization_id
and description='Automated Expired'
AND mil.segment1='07'
and upper(msl.subinventory_code) like '%DIS%'
and rownum=1;
begin
INV_LOC_WMS_PUB.CREATE_LOC_ITEM_TIE(
x_return_status => l1_return_status
,x_msg_count => l1_msg_count
,x_msg_data => l1_msg_data
,p_inventory_item_id => i.item_id
,p_item => NULL
,p_organization_id => i.organization_id
,p_organization_code => NULL
,p_subinventory_code => lv_to_subinv
,p_inventory_location_id => lv_to_locator_id
,p_locator => NULL
,p_status_id => NULL);
IF (l1_return_status = 'S') then
dbms_output.put_line('Successful Added');
fnd_file.put_line(fnd_file.log,'Item ID '||i.item_id||' has Assigned to Sub Inventory '||lv_to_subinv||' and Locator ID'||lv_to_locator_id);
commit;
ELSIF (l1_msg_count = 1) THEN
dbms_output.put_line(replace(l1_msg_data,chr(0),' '));
fnd_file.put_line(fnd_file.log,' Error to assgn Item to Locator '||l1_msg_data);
ELSE
For I in 1..l1_msg_count LOOP
l1_msg_data := fnd_msg_pub.get(I,'F');
dbms_output.put_line(replace(l1_msg_data,chr(0),' '));
END LOOP;
fnd_file.put_line(fnd_file.log,' Error to assgn Item to Locator '||l1_msg_data);
END IF;
end;
else
l1_return_status:='S';
end if;
if l1_return_status='S' then
begin
dbms_output.put_line('2');
insert into mtl_transactions_interface
(source_code,
source_header_id,
source_line_id,
process_flag,
lock_flag,
transaction_mode,
inventory_item_id,
organization_id,
subinventory_code,
locator_id,
transaction_quantity,
transaction_uom,
transaction_date,
transaction_type_id,
transfer_subinventory,
transfer_locator,
last_update_date,
last_updated_by,
created_by,
creation_date,
transaction_header_id,
transaction_interface_id
)
values ('OMNICEL_TO_ORACLE',
mtl_material_transactions_s.nextval,
mtl_material_transactions_s.currval,
1,
2,
3,
i.item_id,
i.organization_id,
i.from_subinv,
i.from_locator_id,
i.qty,
i.uom_code,
sysdate,
lv_transaction_type_id,
lv_to_subinv,
lv_to_locator_id,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
lv_transaction_header_id,
lv_transaction_interface_id
);
exception
when others then
fnd_file.put_line(fnd_file.log,'Error while inserting into MTL_TRANSACTION_INTERFACE '||sqlerrm);
dbms_output.put_line('Error while inserting into MTL_TRANSACTION_INTERFACE'||sqlerrm);
end;
begin
insert into mtl_transaction_lots_interface
(transaction_interface_id,
source_code,
source_line_id,
lot_number,
lot_expiration_date,
transaction_quantity,
last_update_date,
last_updated_by,
creation_date,
created_by
)
values (lv_transaction_interface_id,
'OMNICEL_TO_ORACLE',
mtl_material_transactions_s.currval,
i.from_lot_number,
i.exp_date,
i.qty,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
);
exception
when others then
fnd_file.put_line(fnd_file.log,'Error while inserting into MTL_TRANSACTIONS_LOT_INTERFACE'||sqlerrm);
dbms_output.put_line('Error while inserting into MTL_TRANSACTIONS_LOT_INTERFACE'||sqlerrm);
end;
retval := apps.inv_txn_manager_pub.process_transactions
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
p_validation_level => 100,
x_return_status => l_return_status,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
x_trans_count => l_trans_count,
p_table => 1,
p_header_id =>lv_transaction_header_id
);
fnd_file.put_line(fnd_file.log,'Item ID:- '||i.item_id);
fnd_file.put_line(fnd_file.log,'Organziation ID :- '||i.organization_id);
fnd_file.put_line(fnd_file.log,'Quantity :- '||i.qty);
fnd_file.put_line(fnd_file.log,'Transaction Header ID :- '||lv_transaction_header_id);
fnd_file.put_line(fnd_file.log,'MSG Data :- '||l_msg_data);
fnd_file.put_line(fnd_file.log,'Return Status :- '||l_return_status);
if l_return_status='S' then
commit;
else
rollback;
begin
select error_explanation into lv_interface_error
from mtl_transactions_interface
where source_code='OMNICEL_TO_ORACLE'
and rownum=1
and transaction_header_id=lv_transaction_header_id;
exception
when others then
null;
end;
fnd_file.put_line(fnd_file.log,'Rollback Due to :- '||l_msg_data);
fnd_file.put_line(fnd_file.log,' Interface Error :- '||lv_interface_error);
end if;
fnd_file.put_line(fnd_file.log,'*----------------------------------------------------------------------------------------------------*');
end if;
end loop;
end xx_Exp_Lot_Transfer_Auto_p;
Subscribe to:
Posts (Atom)