PACKAGE MMD_INTERORG_SUBINVENTORY_TRANS IS
PROCEDURE TRANSACT_ITEMS;
END;
PACKAGE BODY MMD_INTERORG_SUBINVENTORY_TRANS IS
PROCEDURE TRANSACT_ITEMS IS
CURSOR C_ITEMS_TRAN IS
SELECT m.mis_number,M.FROM_ORG,M.TO_ORG,M.ORG_TXFR,m.from_whse,m.to_whse,m.reason_code,l.*,
lt.lot_id,lt.lot_number,lt.sublot,lt.lot_grade,lt.lot_status,
lt.from_location,lt.to_location,lt.issue_qty,lt.received_qty
FROM mmd_trans_mst m,mmd_trans_lines l,mmd_trans_lot lt
where m.mis_trn_id = l.mis_trn_id
AND l.mis_line_id = lt.mis_line_id
AND m.from_whse = :sddl_mis_mst.from_whse
AND m.mis_number = :sddl_mis_mst.mis_number
AND M.ORG_TXFR = 'N'
AND nvl(lt.received_qty,0) > 0
ORDER BY l.mis_line_id,lt.mis_lot_id;
-- Mahammad added cursor for inter_org transfer on 16.SEP.12
CURSOR C_ITEMS_TRAN_INTORG IS
SELECT M.MIS_NUMBER,M.FROM_ORG,M.TO_ORG,M.ORG_TXFR,M.FROM_WHSE,M.TO_WHSE,M.REASON_CODE,L.*,
LT.LOT_ID,LT.LOT_NUMBER,LT.SUBLOT,LT.LOT_GRADE,LT.LOT_STATUS,
LT.FROM_LOCATION,LT.TO_LOCATION,LT.ISSUE_QTY,LT.RECEIVED_QTY
FROM mmd_trans_mst m,mmd_trans_lines l,mmd_trans_lot lt
WHERE M.MIS_TRN_ID = L.MIS_TRN_ID
AND L.MIS_LINE_ID = LT.MIS_LINE_ID
AND M.TO_ORG=:SDDL_MIS_MST.TO_ORG
AND m.from_whse = :sddl_mis_mst.from_whse
AND M.MIS_NUMBER = :SDDL_MIS_MST.MIS_NUMBER
AND M.ORG_TXFR = 'Y'
AND nvl(lt.received_qty,0) > 0
ORDER BY l.mis_line_id,lt.mis_lot_id;
--
l_TRANSACTION_INTERFACE_ID NUMBER;
l_transaction_header_id NUMBER;
l_primary_UOM VARCHAR2(10);
l_sec_UOM VARCHAR2(10);
l_org_id number;
l_return_status VARCHAR2(100);
L_TRANS_COUNT NUMBER;
L_MSG_DATA VARCHAR2(200);
L_MSG_CNT NUMBER;
l_init_msg_list VARCHAr2(10):= 'T';
l_commit VARCHAr2(10):= 'T';
l_validation_level NUMBER:= 1;
V_RET_VAL NUMBER;
l_from_loc_id NUMBER;
l_to_loc_id NUMBER;
V_GROUP_ID NUMBER;
v_header_id number;
l_TRANSACT_INTERFACE_ID number;
l_from_orgid number;
l_to_orgid number;
BEGIN
---- Mahammad added for inter_org transfer on 16.SEP.12
begin
if :SDDL_MIS_MST.ORG_TXFR='Y' then
FOR R_ITEMS_TRAN_INTORG IN C_ITEMS_TRAN_INTORG
LOOP
select organization_id
into l_from_orgid
from org_organization_definitions
where organization_code=R_ITEMS_TRAN_INTORG.FROM_ORG;
select organization_id
into l_to_orgid
from org_organization_definitions
where organization_code=R_ITEMS_TRAN_INTORG.TO_ORG;
IF R_ITEMS_TRAN_INTORG.from_location IS NOT NULL THEN
select inventory_location_id
INTO l_from_loc_id
from MTL_ITEM_LOCATIONS_KFV MIL
where mil.Concatenated_segments=R_ITEMS_TRAN_INTORG.from_location
and Organization_id=l_from_orgid;
END IF;
select inventory_location_id
INTO l_to_loc_id
from MTL_ITEM_LOCATIONS_KFV MIL
where mil.inventory_item_id=R_ITEMS_TRAN_INTORG.item_id
and Organization_id=l_to_orgid;
select primary_uom_code,secondary_uom_code
INTO l_primary_UOM,l_sec_UOM
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID=R_ITEMS_TRAN_INTORG.item_id
and Organization_id=:GLOBAL.ORG_ID;
select RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
into l_transaction_INTERFACE_ID
from DUAL;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_TRANSACT_INTERFACE_ID
FROM DUAL;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_transaction_header_id
FROM DUAL;
select RCV_INTERFACE_GROUPS_S.NEXTVAL
into V_GROUP_ID
from dual;
select rcv_headers_interface_s.nextval
into v_header_id
from dual;
INSERT INTO MTL_TRANSACTIONS_INTERFACE
( CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
last_update_login ,
SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
PROCESS_FLAG,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSFER_ORGANIZATION,
TRANSFER_SUBINVENTORY,
TRANSACTION_MODE,
REVISION,
SHIPMENT_NUMBER,
TRANSACTION_HEADER_ID ,
TRANSACTION_INTERFACE_ID,
TRANSFER_LOCATOR ,
locator_id
)
VALUES
(sysdate,
1234,
sysdate,
1234,
2213,
R_ITEMS_TRAN_INTORG.MIS_NUMBER,
R_ITEMS_TRAN_INTORG.MIS_LINE_ID,
R_ITEMS_TRAN_INTORG.MIS_TRN_ID,
1,--process_flag,
R_ITEMS_TRAN_INTORG.item_id,
l_from_orgid,
R_ITEMS_TRAN_INTORG.FROM_ORG,
3,--TRANSACTION_TYPE_ID
(R_ITEMS_TRAN_INTORG.received_qty)*(-1),
l_primary_UOM,
sysdate,
l_to_orgid,
R_ITEMS_TRAN_INTORG.TO_ORG,
1,--TRANSACTION_MODE
null,
R_ITEMS_TRAN_INTORG.MIS_NUMBER,
l_transaction_header_id,
l_TRANSACT_INTERFACE_ID,
l_to_loc_id,
l_from_loc_id
);
INSERT
INTO MTL_TRANSACTION_LOTS_INTERFACE
(
TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
LOT_NUMBER,
TRANSACTION_QUANTITY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
last_update_login
)
VALUES
(l_TRANSACT_INTERFACE_ID,
R_ITEMS_TRAN_INTORG.MIS_NUMBER,
R_ITEMS_TRAN_INTORG.MIS_LINE_ID,
R_ITEMS_TRAN_INTORG.LOT_NUMBER,
(R_ITEMS_TRAN_INTORG.received_qty)*(-1),
1234,
sysdate,
1234,
sysdate,
2213
);
INSERT INTO RCV_HEADERS_INTERFACE
(
HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
AUTO_TRANSACT_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
VALIDATION_FLAG,
COMMENTS,
SHIPMENT_NUM,
FROM_ORGANIZATION_ID,
SHIP_TO_ORGANIZATION_ID
)
VALUES
(
V_HEADER_ID, -- HEADER_INTERFACE_ID,
V_GROUP_ID, -- GROUP_ID,
'PENDING', -- PROCESSING_STATUS_CODE,
'INVENTORY',-- RECEIPT_SOURCE_CODE,
'NEW',-- TRANSACTION_TYPE,
'DELIVER',-- AUT_TRANSACT_CODE,
SYSDATE, -- LAST_UPDATE_DATE,
1234,-- LAST_UPDATE_BY,
SYSDATE, -- CREATION_DATE,
1234,-- CREATED_BY,
'Y',-- VALIDATION_FLAG,
'Receiving Through Interface',-- COMMENTS,
R_ITEMS_TRAN_INTORG.MIS_NUMBER,-- SHIPMENT_NUM,
l_from_orgid,-- FROM_ORGANIZATION_ID,
l_to_orgid-- SHIP_TO_ORGANIZATION_ID
);
insert into RCV_TRANSACTIONS_INTERFACE
(
INTERFACE_TRANSACTION_ID,
HEADER_INTERFACE_ID,
GROUP_ID,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
RECEIPT_SOURCE_CODE,
DESTINATION_TYPE_CODE,
AUTO_TRANSACT_CODE,
SOURCE_DOCUMENT_CODE,
UNIT_OF_MEASURE,
ITEM_ID,
UOM_CODE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
TO_ORGANIZATION_ID,
SUBINVENTORY,
FROM_ORGANIZATION_ID,
FROM_SUBINVENTORY
)
VALUES
(
L_TRANSACTION_INTERFACE_ID,-- INTERFACE_TRANSACTION_ID,
V_HEADER_ID, --- HEADER_INTERFACE_ID,
V_GROUP_ID, --- GROUP_ID,
'RECEIVE',-- TRANSACTION_TYPE,
SYSDATE, -- TRANSACTION_DATE,
'PENDING',-- PROCESSING_STATUS_CODE,
'BATCH',-- PROCESSING_MODE_CODE,
'PENDING',-- TRANSACTION_STATUS_CODE,
(R_ITEMS_TRAN_INTORG.received_qty),-- QUANTITY,
SYSDATE, -- LAST_UPDATE_DATE,
1234,-- LAST_UPDATED_BY,
SYSDATE, -- CREATION_DATE,
1234,-- CREATED_BY,
'INVENTORY',-- RECEIPT_SOURCE_CODE,
'INVENTORY',-- DESTINATION_TYPE_CODE,
'DELIVER',-- AUTO_TRANSACT_CODE,
'INVENTORY',-- SOURCE_DOCUMENT_CODE,
l_primary_UOM,-- UNIT_OF_MEASURE,
R_ITEMS_TRAN_INTORG.item_id,-- ITEM_ID,
l_primary_UOM,-- UOM_CODE,
V_HEADER_ID,-- SHIPMENT_HEADER_ID,
V_HEADER_ID,-- SHIPMENT_LINE_ID,
l_to_orgid,-- TO_ORGANIZATION_ID,
R_ITEMS_TRAN_INTORG.TO_ORG,-- SUBINVENTORY_ID,
l_from_orgid,-- FROM_ORGANIZATION_ID,
R_ITEMS_TRAN_INTORG.FROM_ORG -- FROM_SUBINVENTORY
);
V_RET_VAL:=INV_TXN_MANAGER_PUB.process_transactions
( p_api_version => 1.0 ,
p_init_msg_list => l_init_msg_list ,
p_commit => l_commit ,
p_validation_level =>l_validation_level ,
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 => L_TRANSACTION_HEADER_ID);
IF (l_return_status <> 'S') THEN
l_msg_cnt:=nvl(l_msg_cnt,0)+1;
For i IN 1..l_msg_cnt LOOP
IF i = 1 then
l_msg_data :='Error for Item : '|| R_ITEMS_TRAN_INTORG.LOT_NUMBER ||chr(10) || fnd_msg_pub.get(i,'F');
ELSE
l_msg_data := fnd_msg_pub.get(i,'F');
END IF;
fnd_message.set_string(substrb(l_msg_data,1,2000));
fnd_message.show;
raise form_trigger_failure;
END LOOP;
END IF;
end loop;
end if;
exception
when others then
message('cursor in exception of interorg transfer'||sqlerrm);
end;
--
-- if codition for subinventory transfer
begin
if :SDDL_MIS_MST.ORG_TXFR='N' then
FOR R_ITEMS_TRAN IN C_ITEMS_TRAN
LOOP
IF R_ITEMS_TRAN.from_location IS NOT NULL THEN
select inventory_location_id
INTO l_from_loc_id
from MTL_ITEM_LOCATIONS_KFV MIL
where mil.Concatenated_segments=R_ITEMS_TRAN.from_location
and Organization_id=:GLOBAL.ORG_ID;
END IF;
IF R_ITEMS_TRAN.to_location IS NOT NULL THEN
select inventory_location_id
INTO l_to_loc_id
from MTL_ITEM_LOCATIONS_KFV MIL
where mil.Concatenated_segments=R_ITEMS_TRAN.to_location
and Organization_id=:GLOBAL.ORG_ID;
END IF;
select primary_uom_code,secondary_uom_code
INTO l_primary_UOM,l_sec_UOM
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID=R_ITEMS_TRAN.item_id
and Organization_id=:GLOBAL.ORG_ID;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_TRANSACTION_INTERFACE_ID
FROM DUAL;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_transaction_header_id
FROM DUAL;
INSERT INTO MTL_TRANSACTIONS_INTERFACE
(
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
organization_id,
inventory_item_id ,
subinventory_code ,
locator_id ,
process_flag ,
transaction_mode ,
transaction_source_name,
source_code ,
source_header_id ,
source_line_id ,
transaction_date ,
transaction_header_id ,
transaction_interface_id ,
transaction_type_id ,
transaction_quantity ,
transaction_uom ,
transfer_locator ,
transfer_subinventory
)
VALUES
(1234,
sysdate,
1234,
sysdate,
2213,
:GLOBAL.ORG_ID,
R_ITEMS_TRAN.item_id,
R_ITEMS_TRAN.from_whse,
l_from_loc_id,
1,--process_flag,
2,--transaction_mode
R_ITEMS_TRAN.MIS_NUMBER,
R_ITEMS_TRAN.MIS_NUMBER,
R_ITEMS_TRAN.MIS_TRN_ID,
R_ITEMS_TRAN.MIS_LINE_ID,
sysdate,
l_transaction_header_id, --header_id
l_TRANSACTION_INTERFACE_ID, -- interface_id
2,--subinventory
(R_ITEMS_TRAN.received_qty)*(-1),
l_primary_UOM,
l_to_loc_id,
R_ITEMS_TRAN.to_whse
);
INSERT
INTO MTL_TRANSACTION_LOTS_INTERFACE
(
TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
LOT_NUMBER,
TRANSACTION_QUANTITY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(l_TRANSACTION_INTERFACE_ID,
R_ITEMS_TRAN.MIS_NUMBER,
R_ITEMS_TRAN.MIS_LINE_ID,
R_ITEMS_TRAN.LOT_NUMBER,
(R_ITEMS_TRAN.received_qty)*(-1),
1234,
sysdate,
1234,
sysdate,
2213
);
V_RET_VAL:=INV_TXN_MANAGER_PUB.process_transactions
( p_api_version => 1.0 ,
p_init_msg_list => l_init_msg_list ,
p_commit => l_commit ,
p_validation_level =>l_validation_level ,
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 => L_TRANSACTION_HEADER_ID);
IF (l_return_status <> 'S') THEN
l_msg_cnt:=nvl(l_msg_cnt,0)+1;
For i IN 1..l_msg_cnt LOOP
IF i = 1 then
l_msg_data :='Error for Item : '|| R_ITEMS_TRAN.LOT_NUMBER ||chr(10) || fnd_msg_pub.get(i,'F');
ELSE
l_msg_data := fnd_msg_pub.get(i,'F');
END IF;
fnd_message.set_string(substrb(l_msg_data,1,2000));
fnd_message.show;
raise form_trigger_failure;
END LOOP;
END IF;
END LOOP;
end if;
exception
when others then
message('cursor in exception of subinventory transfer'||sqlerrm);
end;
-- end if for subinventory transfer
END TRANSACT_ITEMS;
END;
PROCEDURE TRANSACT_ITEMS;
END;
PACKAGE BODY MMD_INTERORG_SUBINVENTORY_TRANS IS
PROCEDURE TRANSACT_ITEMS IS
CURSOR C_ITEMS_TRAN IS
SELECT m.mis_number,M.FROM_ORG,M.TO_ORG,M.ORG_TXFR,m.from_whse,m.to_whse,m.reason_code,l.*,
lt.lot_id,lt.lot_number,lt.sublot,lt.lot_grade,lt.lot_status,
lt.from_location,lt.to_location,lt.issue_qty,lt.received_qty
FROM mmd_trans_mst m,mmd_trans_lines l,mmd_trans_lot lt
where m.mis_trn_id = l.mis_trn_id
AND l.mis_line_id = lt.mis_line_id
AND m.from_whse = :sddl_mis_mst.from_whse
AND m.mis_number = :sddl_mis_mst.mis_number
AND M.ORG_TXFR = 'N'
AND nvl(lt.received_qty,0) > 0
ORDER BY l.mis_line_id,lt.mis_lot_id;
-- Mahammad added cursor for inter_org transfer on 16.SEP.12
CURSOR C_ITEMS_TRAN_INTORG IS
SELECT M.MIS_NUMBER,M.FROM_ORG,M.TO_ORG,M.ORG_TXFR,M.FROM_WHSE,M.TO_WHSE,M.REASON_CODE,L.*,
LT.LOT_ID,LT.LOT_NUMBER,LT.SUBLOT,LT.LOT_GRADE,LT.LOT_STATUS,
LT.FROM_LOCATION,LT.TO_LOCATION,LT.ISSUE_QTY,LT.RECEIVED_QTY
FROM mmd_trans_mst m,mmd_trans_lines l,mmd_trans_lot lt
WHERE M.MIS_TRN_ID = L.MIS_TRN_ID
AND L.MIS_LINE_ID = LT.MIS_LINE_ID
AND M.TO_ORG=:SDDL_MIS_MST.TO_ORG
AND m.from_whse = :sddl_mis_mst.from_whse
AND M.MIS_NUMBER = :SDDL_MIS_MST.MIS_NUMBER
AND M.ORG_TXFR = 'Y'
AND nvl(lt.received_qty,0) > 0
ORDER BY l.mis_line_id,lt.mis_lot_id;
--
l_TRANSACTION_INTERFACE_ID NUMBER;
l_transaction_header_id NUMBER;
l_primary_UOM VARCHAR2(10);
l_sec_UOM VARCHAR2(10);
l_org_id number;
l_return_status VARCHAR2(100);
L_TRANS_COUNT NUMBER;
L_MSG_DATA VARCHAR2(200);
L_MSG_CNT NUMBER;
l_init_msg_list VARCHAr2(10):= 'T';
l_commit VARCHAr2(10):= 'T';
l_validation_level NUMBER:= 1;
V_RET_VAL NUMBER;
l_from_loc_id NUMBER;
l_to_loc_id NUMBER;
V_GROUP_ID NUMBER;
v_header_id number;
l_TRANSACT_INTERFACE_ID number;
l_from_orgid number;
l_to_orgid number;
BEGIN
---- Mahammad added for inter_org transfer on 16.SEP.12
begin
if :SDDL_MIS_MST.ORG_TXFR='Y' then
FOR R_ITEMS_TRAN_INTORG IN C_ITEMS_TRAN_INTORG
LOOP
select organization_id
into l_from_orgid
from org_organization_definitions
where organization_code=R_ITEMS_TRAN_INTORG.FROM_ORG;
select organization_id
into l_to_orgid
from org_organization_definitions
where organization_code=R_ITEMS_TRAN_INTORG.TO_ORG;
IF R_ITEMS_TRAN_INTORG.from_location IS NOT NULL THEN
select inventory_location_id
INTO l_from_loc_id
from MTL_ITEM_LOCATIONS_KFV MIL
where mil.Concatenated_segments=R_ITEMS_TRAN_INTORG.from_location
and Organization_id=l_from_orgid;
END IF;
select inventory_location_id
INTO l_to_loc_id
from MTL_ITEM_LOCATIONS_KFV MIL
where mil.inventory_item_id=R_ITEMS_TRAN_INTORG.item_id
and Organization_id=l_to_orgid;
select primary_uom_code,secondary_uom_code
INTO l_primary_UOM,l_sec_UOM
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID=R_ITEMS_TRAN_INTORG.item_id
and Organization_id=:GLOBAL.ORG_ID;
select RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
into l_transaction_INTERFACE_ID
from DUAL;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_TRANSACT_INTERFACE_ID
FROM DUAL;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_transaction_header_id
FROM DUAL;
select RCV_INTERFACE_GROUPS_S.NEXTVAL
into V_GROUP_ID
from dual;
select rcv_headers_interface_s.nextval
into v_header_id
from dual;
INSERT INTO MTL_TRANSACTIONS_INTERFACE
( CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
last_update_login ,
SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
PROCESS_FLAG,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSFER_ORGANIZATION,
TRANSFER_SUBINVENTORY,
TRANSACTION_MODE,
REVISION,
SHIPMENT_NUMBER,
TRANSACTION_HEADER_ID ,
TRANSACTION_INTERFACE_ID,
TRANSFER_LOCATOR ,
locator_id
)
VALUES
(sysdate,
1234,
sysdate,
1234,
2213,
R_ITEMS_TRAN_INTORG.MIS_NUMBER,
R_ITEMS_TRAN_INTORG.MIS_LINE_ID,
R_ITEMS_TRAN_INTORG.MIS_TRN_ID,
1,--process_flag,
R_ITEMS_TRAN_INTORG.item_id,
l_from_orgid,
R_ITEMS_TRAN_INTORG.FROM_ORG,
3,--TRANSACTION_TYPE_ID
(R_ITEMS_TRAN_INTORG.received_qty)*(-1),
l_primary_UOM,
sysdate,
l_to_orgid,
R_ITEMS_TRAN_INTORG.TO_ORG,
1,--TRANSACTION_MODE
null,
R_ITEMS_TRAN_INTORG.MIS_NUMBER,
l_transaction_header_id,
l_TRANSACT_INTERFACE_ID,
l_to_loc_id,
l_from_loc_id
);
INSERT
INTO MTL_TRANSACTION_LOTS_INTERFACE
(
TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
LOT_NUMBER,
TRANSACTION_QUANTITY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
last_update_login
)
VALUES
(l_TRANSACT_INTERFACE_ID,
R_ITEMS_TRAN_INTORG.MIS_NUMBER,
R_ITEMS_TRAN_INTORG.MIS_LINE_ID,
R_ITEMS_TRAN_INTORG.LOT_NUMBER,
(R_ITEMS_TRAN_INTORG.received_qty)*(-1),
1234,
sysdate,
1234,
sysdate,
2213
);
INSERT INTO RCV_HEADERS_INTERFACE
(
HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
AUTO_TRANSACT_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
VALIDATION_FLAG,
COMMENTS,
SHIPMENT_NUM,
FROM_ORGANIZATION_ID,
SHIP_TO_ORGANIZATION_ID
)
VALUES
(
V_HEADER_ID, -- HEADER_INTERFACE_ID,
V_GROUP_ID, -- GROUP_ID,
'PENDING', -- PROCESSING_STATUS_CODE,
'INVENTORY',-- RECEIPT_SOURCE_CODE,
'NEW',-- TRANSACTION_TYPE,
'DELIVER',-- AUT_TRANSACT_CODE,
SYSDATE, -- LAST_UPDATE_DATE,
1234,-- LAST_UPDATE_BY,
SYSDATE, -- CREATION_DATE,
1234,-- CREATED_BY,
'Y',-- VALIDATION_FLAG,
'Receiving Through Interface',-- COMMENTS,
R_ITEMS_TRAN_INTORG.MIS_NUMBER,-- SHIPMENT_NUM,
l_from_orgid,-- FROM_ORGANIZATION_ID,
l_to_orgid-- SHIP_TO_ORGANIZATION_ID
);
insert into RCV_TRANSACTIONS_INTERFACE
(
INTERFACE_TRANSACTION_ID,
HEADER_INTERFACE_ID,
GROUP_ID,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
RECEIPT_SOURCE_CODE,
DESTINATION_TYPE_CODE,
AUTO_TRANSACT_CODE,
SOURCE_DOCUMENT_CODE,
UNIT_OF_MEASURE,
ITEM_ID,
UOM_CODE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
TO_ORGANIZATION_ID,
SUBINVENTORY,
FROM_ORGANIZATION_ID,
FROM_SUBINVENTORY
)
VALUES
(
L_TRANSACTION_INTERFACE_ID,-- INTERFACE_TRANSACTION_ID,
V_HEADER_ID, --- HEADER_INTERFACE_ID,
V_GROUP_ID, --- GROUP_ID,
'RECEIVE',-- TRANSACTION_TYPE,
SYSDATE, -- TRANSACTION_DATE,
'PENDING',-- PROCESSING_STATUS_CODE,
'BATCH',-- PROCESSING_MODE_CODE,
'PENDING',-- TRANSACTION_STATUS_CODE,
(R_ITEMS_TRAN_INTORG.received_qty),-- QUANTITY,
SYSDATE, -- LAST_UPDATE_DATE,
1234,-- LAST_UPDATED_BY,
SYSDATE, -- CREATION_DATE,
1234,-- CREATED_BY,
'INVENTORY',-- RECEIPT_SOURCE_CODE,
'INVENTORY',-- DESTINATION_TYPE_CODE,
'DELIVER',-- AUTO_TRANSACT_CODE,
'INVENTORY',-- SOURCE_DOCUMENT_CODE,
l_primary_UOM,-- UNIT_OF_MEASURE,
R_ITEMS_TRAN_INTORG.item_id,-- ITEM_ID,
l_primary_UOM,-- UOM_CODE,
V_HEADER_ID,-- SHIPMENT_HEADER_ID,
V_HEADER_ID,-- SHIPMENT_LINE_ID,
l_to_orgid,-- TO_ORGANIZATION_ID,
R_ITEMS_TRAN_INTORG.TO_ORG,-- SUBINVENTORY_ID,
l_from_orgid,-- FROM_ORGANIZATION_ID,
R_ITEMS_TRAN_INTORG.FROM_ORG -- FROM_SUBINVENTORY
);
V_RET_VAL:=INV_TXN_MANAGER_PUB.process_transactions
( p_api_version => 1.0 ,
p_init_msg_list => l_init_msg_list ,
p_commit => l_commit ,
p_validation_level =>l_validation_level ,
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 => L_TRANSACTION_HEADER_ID);
IF (l_return_status <> 'S') THEN
l_msg_cnt:=nvl(l_msg_cnt,0)+1;
For i IN 1..l_msg_cnt LOOP
IF i = 1 then
l_msg_data :='Error for Item : '|| R_ITEMS_TRAN_INTORG.LOT_NUMBER ||chr(10) || fnd_msg_pub.get(i,'F');
ELSE
l_msg_data := fnd_msg_pub.get(i,'F');
END IF;
fnd_message.set_string(substrb(l_msg_data,1,2000));
fnd_message.show;
raise form_trigger_failure;
END LOOP;
END IF;
end loop;
end if;
exception
when others then
message('cursor in exception of interorg transfer'||sqlerrm);
end;
--
-- if codition for subinventory transfer
begin
if :SDDL_MIS_MST.ORG_TXFR='N' then
FOR R_ITEMS_TRAN IN C_ITEMS_TRAN
LOOP
IF R_ITEMS_TRAN.from_location IS NOT NULL THEN
select inventory_location_id
INTO l_from_loc_id
from MTL_ITEM_LOCATIONS_KFV MIL
where mil.Concatenated_segments=R_ITEMS_TRAN.from_location
and Organization_id=:GLOBAL.ORG_ID;
END IF;
IF R_ITEMS_TRAN.to_location IS NOT NULL THEN
select inventory_location_id
INTO l_to_loc_id
from MTL_ITEM_LOCATIONS_KFV MIL
where mil.Concatenated_segments=R_ITEMS_TRAN.to_location
and Organization_id=:GLOBAL.ORG_ID;
END IF;
select primary_uom_code,secondary_uom_code
INTO l_primary_UOM,l_sec_UOM
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID=R_ITEMS_TRAN.item_id
and Organization_id=:GLOBAL.ORG_ID;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_TRANSACTION_INTERFACE_ID
FROM DUAL;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_transaction_header_id
FROM DUAL;
INSERT INTO MTL_TRANSACTIONS_INTERFACE
(
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
organization_id,
inventory_item_id ,
subinventory_code ,
locator_id ,
process_flag ,
transaction_mode ,
transaction_source_name,
source_code ,
source_header_id ,
source_line_id ,
transaction_date ,
transaction_header_id ,
transaction_interface_id ,
transaction_type_id ,
transaction_quantity ,
transaction_uom ,
transfer_locator ,
transfer_subinventory
)
VALUES
(1234,
sysdate,
1234,
sysdate,
2213,
:GLOBAL.ORG_ID,
R_ITEMS_TRAN.item_id,
R_ITEMS_TRAN.from_whse,
l_from_loc_id,
1,--process_flag,
2,--transaction_mode
R_ITEMS_TRAN.MIS_NUMBER,
R_ITEMS_TRAN.MIS_NUMBER,
R_ITEMS_TRAN.MIS_TRN_ID,
R_ITEMS_TRAN.MIS_LINE_ID,
sysdate,
l_transaction_header_id, --header_id
l_TRANSACTION_INTERFACE_ID, -- interface_id
2,--subinventory
(R_ITEMS_TRAN.received_qty)*(-1),
l_primary_UOM,
l_to_loc_id,
R_ITEMS_TRAN.to_whse
);
INSERT
INTO MTL_TRANSACTION_LOTS_INTERFACE
(
TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
LOT_NUMBER,
TRANSACTION_QUANTITY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(l_TRANSACTION_INTERFACE_ID,
R_ITEMS_TRAN.MIS_NUMBER,
R_ITEMS_TRAN.MIS_LINE_ID,
R_ITEMS_TRAN.LOT_NUMBER,
(R_ITEMS_TRAN.received_qty)*(-1),
1234,
sysdate,
1234,
sysdate,
2213
);
V_RET_VAL:=INV_TXN_MANAGER_PUB.process_transactions
( p_api_version => 1.0 ,
p_init_msg_list => l_init_msg_list ,
p_commit => l_commit ,
p_validation_level =>l_validation_level ,
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 => L_TRANSACTION_HEADER_ID);
IF (l_return_status <> 'S') THEN
l_msg_cnt:=nvl(l_msg_cnt,0)+1;
For i IN 1..l_msg_cnt LOOP
IF i = 1 then
l_msg_data :='Error for Item : '|| R_ITEMS_TRAN.LOT_NUMBER ||chr(10) || fnd_msg_pub.get(i,'F');
ELSE
l_msg_data := fnd_msg_pub.get(i,'F');
END IF;
fnd_message.set_string(substrb(l_msg_data,1,2000));
fnd_message.show;
raise form_trigger_failure;
END LOOP;
END IF;
END LOOP;
end if;
exception
when others then
message('cursor in exception of subinventory transfer'||sqlerrm);
end;
-- end if for subinventory transfer
END TRANSACT_ITEMS;
END;
