Inventory Sub Inventory Transfer Conversion
XYKA_SUBINV_TRANSFER_PROC
CREATE TABLE APPS.XYKA_SUBINV_TRANS_STG
(
X_ITEM VARCHAR2(30 BYTE),
FROM_SUB VARCHAR2(50 BYTE),
TO_SUB_INV VARCHAR2(50 BYTE),
QUANTITY NUMBER,
ORG_CODE VARCHAR2(5 BYTE),
FROM_LOCATOR VARCHAR2(50 BYTE),
TO_LOCATOR VARCHAR2(50 BYTE),
INVENTORY_ITEM_ID NUMBER
)
CREATE TABLE APPS.XYKA_SUBINV_TRANS_STG_ERROR
(
X_ITEM VARCHAR2(50 BYTE),
FROM_SUB VARCHAR2(50 BYTE),
TO_SUB_INV VARCHAR2(50 BYTE),
QUANTITY NUMBER,
ORG_CODE VARCHAR2(5 BYTE),
FROM_LOCATOR VARCHAR2(50 BYTE),
TO_LOCATOR VARCHAR2(50 BYTE),
INVENTORY_ITEM_ID NUMBER,
ITEM_NUMBER VARCHAR2(30 BYTE),
LOCATOR_ID NUMBER,
LOCK_FLAG NUMBER(1),
MATERIAL_ACCOUNT NUMBER,
ORGANIZATION_ID NUMBER,
PROCESS_FLAG NUMBER(1),
SCHEDULED_FLAG NUMBER,
SOURCE_CODE VARCHAR2(30 BYTE),
SOURCE_HEADER_ID NUMBER,
SOURCE_LINE_ID NUMBER,
SUBINVENTORY_CODE VARCHAR2(10 BYTE),
TRANSACTION_DATE DATE,
TRANSACTION_HEADER_ID NUMBER,
TRANSACTION_INTERFACE_ID NUMBER,
TRANSACTION_MODE NUMBER,
TRANSACTION_QUANTITY NUMBER,
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_UOM VARCHAR2(3 BYTE),
TRANSFER_ORG_ID NUMBER,
TRANSFER_LOCATOR NUMBER,
TRANSFER_SUBINVENTORY VARCHAR2(10 BYTE),
TRANSACTION_COST NUMBER,
TRANSACTION_REFERENCE VARCHAR2(240 BYTE),
REASON_ID NUMBER,
DISTRIBUTION_ACCOUNT_ID NUMBER,
STATUS VARCHAR2(30 BYTE),
ERROR_MASSAGE VARCHAR2(240 BYTE)
)
CREATE OR REPLACE PROCEDURE APPS.xyka_Subinv_transfer_proc
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER
)
IS
cursor onhand_cur
is
select * from XYKA_SUBINV_TRANS_STG;
v_transaction_header_id NUMBER;
v_transaction_interface_id NUMBER;
v_inventory_item_id NUMBER;
v_segment1 VARCHAR2 (30);
v_sysdate VARCHAR2 (30);
v_org_id NUMBER;
v_sub_inv VARCHAR2 (30);
v_uom VARCHAR2 (30);
v_material_account NUMBER;
v_from_locator_id NUMBER;
v_from_locator_code VARCHAR2 (30);
v_to_locator_id NUMBER;
v_to_locator_code VARCHAR2 (30);
v_retval VARCHAR2 (3);
l_return_status VARCHAR2 (50);
l_msg_cnt NUMBER;
l_msg_data VARCHAR2 (3000);
l_trans_count NUMBER;
v_row_count NUMBER := 0;
v_resp_id NUMBER := FND_PROFILE.VALUE ('RESP_ID');
v_appl_id NUMBER := FND_PROFILE.VALUE ('RESP_APPL_ID');
v_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
v_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
Begin
Begin
/*
* APPS_INITIALIZE Required because indirectly use profile options
-- FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,l_resp_appl_id);
*/
FND_GLOBAL.APPS_INITIALIZE(1295,50683,401);
End;
FOR yk_onhand_cur IN onhand_cur
LOOP
SELECT mtl_material_transactions_s.NEXTVAL
INTO v_transaction_interface_id
FROM DUAL;
Begin
SELECT organization_id
INTO v_org_id
FROM org_organization_definitions
WHERE organization_code = yk_onhand_cur.ORG_CODE;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid Org Code ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid Org Code ' || SQLERRM);
End;
Begin
SELECT msib.inventory_item_id
INTO v_inventory_item_id
FROM mtl_system_items_b msib
WHERE msib.segment1 = yk_onhand_cur.X_ITEM
AND msib.organization_id = v_org_id
AND ROWNUM = 1;
exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid Item Code ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid Item Code ' || SQLERRM);
End ;
Begin
SELECT msib.PRIMARY_UOM_CODE
INTO v_uom
FROM mtl_system_items_b msib
WHERE msib.segment1 = yk_onhand_cur.X_ITEM
AND msib.organization_id = v_org_id
AND ROWNUM = 1;
exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid PRIMARY_UOM_CODE ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid PRIMARY_UOM_CODE ' || SQLERRM);
End ;
Begin
SELECT msi.material_account
INTO v_material_account
FROM mtl_secondary_inventories msi
WHERE msi.organization_id = v_org_id
AND msi.SECONDARY_INVENTORY_NAME = yk_onhand_cur.TO_SUB_INV;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid material_account ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid material_account ' || SQLERRM);
End;
Begin
SELECT inventory_location_id
INTO v_from_locator_id
FROM mtl_item_locations
WHERE --SEGMENT1 ||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5 = yk_onhand_cur.FROM_LOCATOR -- On Prod
SEGMENT1 ||'|'||SEGMENT2||'|'||SEGMENT3||'|'||SEGMENT4||'|'||SEGMENT5 = yk_onhand_cur.FROM_LOCATOR -- On Dev
AND subinventory_code = NVL(yk_onhand_cur.FROM_SUB,subinventory_code)
AND organization_id = v_org_id ;
SELECT segment1
||'|'||segment2
||'|'||segment3
||'|'||segment4
INTO v_from_locator_code
FROM mtl_item_locations
WHERE inventory_location_id = v_from_locator_id
AND subinventory_code = NVL(yk_onhand_cur.FROM_SUB,subinventory_code)
AND organization_id = v_org_id ;
Begin
SELECT inventory_location_id
INTO v_to_locator_id
FROM mtl_item_locations
WHERE segment1||'|'||segment2||'|'||segment3||'|'||segment4 = v_from_locator_code
AND subinventory_code = NVL(yk_onhand_cur.TO_SUB_inv,subinventory_code)
AND organization_id = v_org_id ;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid To Locator ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid To Locator ' || SQLERRM);
End ;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid From Locator ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid From Locator ' || SQLERRM);
End;
Begin
INSERT INTO MTL_TRANSACTIONS_INTERFACE (created_by,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
inventory_item_id,
locator_id,
lock_flag,
material_account,
organization_id,
process_flag,
program_id,
program_update_date,
scheduled_flag,
source_code,
source_header_id,
source_line_id,
subinventory_code,
transaction_date,
transaction_header_id,
transaction_interface_id,
transaction_mode,
transaction_quantity,
transaction_type_id,
transaction_uom,
transfer_locator,
transfer_subinventory,
transaction_cost,
transaction_reference,
reason_id,
distribution_account_id)
Values (
-1 --created_by
,-1 --last_updated_by
,SYSDATE --last_update_date
,-1 --last_update_login
,SYSDATE --creation_date
,v_inventory_item_id --inventory_item_id
,v_from_locator_id --locator_id
,2 --lock_flag
,v_material_account --material_account
,v_org_id
,1 --process_flag
,NULL --program_id
,NULL --program_update_date
,NULL --scheduled_flag
,'Interface' --source_code
,oe_order_headers_s.nextval -- source_header_id
,oe_order_lines_s.nextval -- source_line_id
,yk_onhand_cur.from_sub --source subinventory_code
,SYSDATE --transaction_date
,v_transaction_header_id --transaction_header_id
,v_transaction_interface_id --transaction_interface_id
,3 --transaction_mode
,yk_onhand_cur.quantity
,2 --transaction_type_id
,v_uom --transaction_uom
,v_to_locator_id --transfer_locator
,yk_onhand_cur.TO_SUB_INV --transfer_subinventory
,NULL --transaction_cost
,NULL
,NULL
,NULL --distribution_account_id validation_required
);
BEGIN
INSERT INTO XYKA_SUBINV_TRANS_STG_ERROR (
X_ITEM ,
FROM_SUB ,
TO_SUB_INV ,
QUANTITY ,
ORG_CODE ,
FROM_LOCATOR ,
TO_LOCATOR ,
inventory_item_id ,
item_number ,
locator_id ,
lock_flag ,
material_account ,
organization_id ,
process_flag ,
scheduled_flag ,
source_code ,
-- source_line_id ,
-- source_header_id ,
subinventory_code ,
transaction_date ,
transaction_header_id ,
transaction_interface_id ,
transaction_mode ,
transaction_quantity ,
transaction_type_id ,
transaction_uom ,
transfer_locator ,
transfer_subinventory ,
transaction_cost ,
transaction_reference ,
reason_id ,
distribution_account_id ,
status ,
error_massage )
Values(
yk_onhand_cur.X_item
,yk_onhand_cur.from_sub
,yk_onhand_cur.to_sub_inv
,yk_onhand_cur.quantity
,yk_onhand_cur.org_code
,yk_onhand_cur.from_locator
,yk_onhand_cur.to_locator
,v_inventory_item_id
,yk_onhand_cur.X_item -- item_number
,v_from_locator_id --locator_id
,2 -- lock_flag
,v_material_account --material_account
,v_org_id
,1 --process_flag
,NULL --scheduled_flag ,
,'Interface' --source_code ,
,yk_onhand_cur.from_sub --subinventory_code ,
,SYSDATE -- transaction_date ,
,v_transaction_header_id --transaction_header_id ,
,v_transaction_interface_id --transaction_interface_id ,
,3 -- transaction_mode ,
,yk_onhand_cur.quantity -- transaction_quantity ,
,2 --transaction_type_idtransaction_type_id ,
,v_uom --transaction_uom ,
,v_to_locator_id --transfer_locatortransfer_locator ,
,yk_onhand_cur.to_sub_inv -- transfer_subinventory ,
,yk_onhand_cur.quantity -- transaction_cost ,
,NULL --transaction_reference ,
,NULL --reason_id ,
,NULL --distribution_account_id ,
,'Success' --status ,
,'NO ERROR' --error_massage
);
END;
Exception
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.PUT_LINE ('Too many rows found while insert into MTL_TRANSACTIONS_INTERFACE ' || SQLERRM);
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Unexpected error while inserting data in MTL_TRANSACTIONS_INTERFACE '||'-' || SQLERRM );
End;
v_row_count := onhand_cur%ROWCOUNT;
--DBMS_OUTPUT.PUT_LINE ('Number Of rows in MTL_TRANSACTIONS_INTERFACE '||'= ' || v_row_count );
END LOOP;
Commit;
IF v_row_count > 0
THEN
v_retval :=
INV_TXN_MANAGER_PUB.process_transactions (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
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 => v_transaction_header_id
);
COMMIT;
IF l_return_status = 'S'
THEN
l_return_status := 'Transactions processed successfully';
DBMS_OUTPUT.PUT_LINE ('Success '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
ELSIF l_return_status = 'E'
THEN
l_return_status := 'Transactions Processing Error';
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
ELSIF l_return_status IS NULL
THEN
l_return_status := 'Transactions Processing Error';
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
END IF;
IF l_return_status = 'E' THEN
UPDATE XYKA_SUBINV_TRANS_STG_ERROR yst
SET (yst.status , yst.error_massage) = ( SELECT 'FAILD', mti.error_explanation FROM
mtl_transactions_interface mti
WHERE mti.source_header_id = yst.source_header_id
AND mti.source_line_id = yst.source_line_id
AND mti.transaction_header_id = v_transaction_header_id)
WHERE yst.transaction_header_id = v_transaction_header_id
AND EXISTS (SELECT * FROM mtl_transactions_interface mtic
WHERE mtic.source_header_id = yst.source_header_id
AND mtic.source_line_id = yst.source_line_id);
COMMIT;
END IF;
END IF;
End;
/
XYKA_SUBINV_TRANSFER_PROC
CREATE TABLE APPS.XYKA_SUBINV_TRANS_STG
(
X_ITEM VARCHAR2(30 BYTE),
FROM_SUB VARCHAR2(50 BYTE),
TO_SUB_INV VARCHAR2(50 BYTE),
QUANTITY NUMBER,
ORG_CODE VARCHAR2(5 BYTE),
FROM_LOCATOR VARCHAR2(50 BYTE),
TO_LOCATOR VARCHAR2(50 BYTE),
INVENTORY_ITEM_ID NUMBER
)
CREATE TABLE APPS.XYKA_SUBINV_TRANS_STG_ERROR
(
X_ITEM VARCHAR2(50 BYTE),
FROM_SUB VARCHAR2(50 BYTE),
TO_SUB_INV VARCHAR2(50 BYTE),
QUANTITY NUMBER,
ORG_CODE VARCHAR2(5 BYTE),
FROM_LOCATOR VARCHAR2(50 BYTE),
TO_LOCATOR VARCHAR2(50 BYTE),
INVENTORY_ITEM_ID NUMBER,
ITEM_NUMBER VARCHAR2(30 BYTE),
LOCATOR_ID NUMBER,
LOCK_FLAG NUMBER(1),
MATERIAL_ACCOUNT NUMBER,
ORGANIZATION_ID NUMBER,
PROCESS_FLAG NUMBER(1),
SCHEDULED_FLAG NUMBER,
SOURCE_CODE VARCHAR2(30 BYTE),
SOURCE_HEADER_ID NUMBER,
SOURCE_LINE_ID NUMBER,
SUBINVENTORY_CODE VARCHAR2(10 BYTE),
TRANSACTION_DATE DATE,
TRANSACTION_HEADER_ID NUMBER,
TRANSACTION_INTERFACE_ID NUMBER,
TRANSACTION_MODE NUMBER,
TRANSACTION_QUANTITY NUMBER,
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_UOM VARCHAR2(3 BYTE),
TRANSFER_ORG_ID NUMBER,
TRANSFER_LOCATOR NUMBER,
TRANSFER_SUBINVENTORY VARCHAR2(10 BYTE),
TRANSACTION_COST NUMBER,
TRANSACTION_REFERENCE VARCHAR2(240 BYTE),
REASON_ID NUMBER,
DISTRIBUTION_ACCOUNT_ID NUMBER,
STATUS VARCHAR2(30 BYTE),
ERROR_MASSAGE VARCHAR2(240 BYTE)
)
CREATE OR REPLACE PROCEDURE APPS.xyka_Subinv_transfer_proc
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER
)
IS
cursor onhand_cur
is
select * from XYKA_SUBINV_TRANS_STG;
v_transaction_header_id NUMBER;
v_transaction_interface_id NUMBER;
v_inventory_item_id NUMBER;
v_segment1 VARCHAR2 (30);
v_sysdate VARCHAR2 (30);
v_org_id NUMBER;
v_sub_inv VARCHAR2 (30);
v_uom VARCHAR2 (30);
v_material_account NUMBER;
v_from_locator_id NUMBER;
v_from_locator_code VARCHAR2 (30);
v_to_locator_id NUMBER;
v_to_locator_code VARCHAR2 (30);
v_retval VARCHAR2 (3);
l_return_status VARCHAR2 (50);
l_msg_cnt NUMBER;
l_msg_data VARCHAR2 (3000);
l_trans_count NUMBER;
v_row_count NUMBER := 0;
v_resp_id NUMBER := FND_PROFILE.VALUE ('RESP_ID');
v_appl_id NUMBER := FND_PROFILE.VALUE ('RESP_APPL_ID');
v_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
v_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
Begin
Begin
/*
* APPS_INITIALIZE Required because indirectly use profile options
-- FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,l_resp_appl_id);
*/
FND_GLOBAL.APPS_INITIALIZE(1295,50683,401);
End;
FOR yk_onhand_cur IN onhand_cur
LOOP
SELECT mtl_material_transactions_s.NEXTVAL
INTO v_transaction_interface_id
FROM DUAL;
Begin
SELECT organization_id
INTO v_org_id
FROM org_organization_definitions
WHERE organization_code = yk_onhand_cur.ORG_CODE;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid Org Code ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid Org Code ' || SQLERRM);
End;
Begin
SELECT msib.inventory_item_id
INTO v_inventory_item_id
FROM mtl_system_items_b msib
WHERE msib.segment1 = yk_onhand_cur.X_ITEM
AND msib.organization_id = v_org_id
AND ROWNUM = 1;
exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid Item Code ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid Item Code ' || SQLERRM);
End ;
Begin
SELECT msib.PRIMARY_UOM_CODE
INTO v_uom
FROM mtl_system_items_b msib
WHERE msib.segment1 = yk_onhand_cur.X_ITEM
AND msib.organization_id = v_org_id
AND ROWNUM = 1;
exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid PRIMARY_UOM_CODE ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid PRIMARY_UOM_CODE ' || SQLERRM);
End ;
Begin
SELECT msi.material_account
INTO v_material_account
FROM mtl_secondary_inventories msi
WHERE msi.organization_id = v_org_id
AND msi.SECONDARY_INVENTORY_NAME = yk_onhand_cur.TO_SUB_INV;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid material_account ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid material_account ' || SQLERRM);
End;
Begin
SELECT inventory_location_id
INTO v_from_locator_id
FROM mtl_item_locations
WHERE --SEGMENT1 ||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5 = yk_onhand_cur.FROM_LOCATOR -- On Prod
SEGMENT1 ||'|'||SEGMENT2||'|'||SEGMENT3||'|'||SEGMENT4||'|'||SEGMENT5 = yk_onhand_cur.FROM_LOCATOR -- On Dev
AND subinventory_code = NVL(yk_onhand_cur.FROM_SUB,subinventory_code)
AND organization_id = v_org_id ;
SELECT segment1
||'|'||segment2
||'|'||segment3
||'|'||segment4
INTO v_from_locator_code
FROM mtl_item_locations
WHERE inventory_location_id = v_from_locator_id
AND subinventory_code = NVL(yk_onhand_cur.FROM_SUB,subinventory_code)
AND organization_id = v_org_id ;
Begin
SELECT inventory_location_id
INTO v_to_locator_id
FROM mtl_item_locations
WHERE segment1||'|'||segment2||'|'||segment3||'|'||segment4 = v_from_locator_code
AND subinventory_code = NVL(yk_onhand_cur.TO_SUB_inv,subinventory_code)
AND organization_id = v_org_id ;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid To Locator ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid To Locator ' || SQLERRM);
End ;
Exception
When no_data_found then
DBMS_OUTPUT.PUT_LINE ('Invalid From Locator ' || SQLERRM);
when Others then
DBMS_OUTPUT.PUT_LINE ('Invalid From Locator ' || SQLERRM);
End;
Begin
INSERT INTO MTL_TRANSACTIONS_INTERFACE (created_by,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
inventory_item_id,
locator_id,
lock_flag,
material_account,
organization_id,
process_flag,
program_id,
program_update_date,
scheduled_flag,
source_code,
source_header_id,
source_line_id,
subinventory_code,
transaction_date,
transaction_header_id,
transaction_interface_id,
transaction_mode,
transaction_quantity,
transaction_type_id,
transaction_uom,
transfer_locator,
transfer_subinventory,
transaction_cost,
transaction_reference,
reason_id,
distribution_account_id)
Values (
-1 --created_by
,-1 --last_updated_by
,SYSDATE --last_update_date
,-1 --last_update_login
,SYSDATE --creation_date
,v_inventory_item_id --inventory_item_id
,v_from_locator_id --locator_id
,2 --lock_flag
,v_material_account --material_account
,v_org_id
,1 --process_flag
,NULL --program_id
,NULL --program_update_date
,NULL --scheduled_flag
,'Interface' --source_code
,oe_order_headers_s.nextval -- source_header_id
,oe_order_lines_s.nextval -- source_line_id
,yk_onhand_cur.from_sub --source subinventory_code
,SYSDATE --transaction_date
,v_transaction_header_id --transaction_header_id
,v_transaction_interface_id --transaction_interface_id
,3 --transaction_mode
,yk_onhand_cur.quantity
,2 --transaction_type_id
,v_uom --transaction_uom
,v_to_locator_id --transfer_locator
,yk_onhand_cur.TO_SUB_INV --transfer_subinventory
,NULL --transaction_cost
,NULL
,NULL
,NULL --distribution_account_id validation_required
);
BEGIN
INSERT INTO XYKA_SUBINV_TRANS_STG_ERROR (
X_ITEM ,
FROM_SUB ,
TO_SUB_INV ,
QUANTITY ,
ORG_CODE ,
FROM_LOCATOR ,
TO_LOCATOR ,
inventory_item_id ,
item_number ,
locator_id ,
lock_flag ,
material_account ,
organization_id ,
process_flag ,
scheduled_flag ,
source_code ,
-- source_line_id ,
-- source_header_id ,
subinventory_code ,
transaction_date ,
transaction_header_id ,
transaction_interface_id ,
transaction_mode ,
transaction_quantity ,
transaction_type_id ,
transaction_uom ,
transfer_locator ,
transfer_subinventory ,
transaction_cost ,
transaction_reference ,
reason_id ,
distribution_account_id ,
status ,
error_massage )
Values(
yk_onhand_cur.X_item
,yk_onhand_cur.from_sub
,yk_onhand_cur.to_sub_inv
,yk_onhand_cur.quantity
,yk_onhand_cur.org_code
,yk_onhand_cur.from_locator
,yk_onhand_cur.to_locator
,v_inventory_item_id
,yk_onhand_cur.X_item -- item_number
,v_from_locator_id --locator_id
,2 -- lock_flag
,v_material_account --material_account
,v_org_id
,1 --process_flag
,NULL --scheduled_flag ,
,'Interface' --source_code ,
,yk_onhand_cur.from_sub --subinventory_code ,
,SYSDATE -- transaction_date ,
,v_transaction_header_id --transaction_header_id ,
,v_transaction_interface_id --transaction_interface_id ,
,3 -- transaction_mode ,
,yk_onhand_cur.quantity -- transaction_quantity ,
,2 --transaction_type_idtransaction_type_id ,
,v_uom --transaction_uom ,
,v_to_locator_id --transfer_locatortransfer_locator ,
,yk_onhand_cur.to_sub_inv -- transfer_subinventory ,
,yk_onhand_cur.quantity -- transaction_cost ,
,NULL --transaction_reference ,
,NULL --reason_id ,
,NULL --distribution_account_id ,
,'Success' --status ,
,'NO ERROR' --error_massage
);
END;
Exception
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.PUT_LINE ('Too many rows found while insert into MTL_TRANSACTIONS_INTERFACE ' || SQLERRM);
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Unexpected error while inserting data in MTL_TRANSACTIONS_INTERFACE '||'-' || SQLERRM );
End;
v_row_count := onhand_cur%ROWCOUNT;
--DBMS_OUTPUT.PUT_LINE ('Number Of rows in MTL_TRANSACTIONS_INTERFACE '||'= ' || v_row_count );
END LOOP;
Commit;
IF v_row_count > 0
THEN
v_retval :=
INV_TXN_MANAGER_PUB.process_transactions (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
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 => v_transaction_header_id
);
COMMIT;
IF l_return_status = 'S'
THEN
l_return_status := 'Transactions processed successfully';
DBMS_OUTPUT.PUT_LINE ('Success '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
ELSIF l_return_status = 'E'
THEN
l_return_status := 'Transactions Processing Error';
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
ELSIF l_return_status IS NULL
THEN
l_return_status := 'Transactions Processing Error';
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_return_status );
DBMS_OUTPUT.PUT_LINE ('Error '||'= ' || l_msg_data );
END IF;
IF l_return_status = 'E' THEN
UPDATE XYKA_SUBINV_TRANS_STG_ERROR yst
SET (yst.status , yst.error_massage) = ( SELECT 'FAILD', mti.error_explanation FROM
mtl_transactions_interface mti
WHERE mti.source_header_id = yst.source_header_id
AND mti.source_line_id = yst.source_line_id
AND mti.transaction_header_id = v_transaction_header_id)
WHERE yst.transaction_header_id = v_transaction_header_id
AND EXISTS (SELECT * FROM mtl_transactions_interface mtic
WHERE mtic.source_header_id = yst.source_header_id
AND mtic.source_line_id = yst.source_line_id);
COMMIT;
END IF;
END IF;
End;
/
outstanding work!!!!!!
ReplyDelete