Creating Maintenance Routes with BOM_RTG_PUB API (You can use this API for discrete manufacturing)
Firstly I create a custom table then I insert the datas from excel sheet.
CREATE TABLE XXINN_MAINTENANCE_ROUTES(
ASSEMBLY_ITEM_NAME VARCHAR2(240 BYTE),
ORGANIZATION_CODE VARCHAR2(3 BYTE),
LONG_DESCRIPTION VARCHAR2(4000 BYTE),
OPERATION_DESCRIPTION VARCHAR2(400 BYTE),
OPERATION_SEQUENCE_NUMBER NUMBER,
DEPARTMENT_CODE VARCHAR2(10 BYTE),
RESOURCE_SEQUENCE_NUMBER NUMBER,
RESOURCE_CODE VARCHAR2(10 BYTE),
USAGE_RATE_OR_AMOUNT NUMBER,
ASSIGNED_UNITS NUMBER,
ERROR VARCHAR2(2000 BYTE)
)
Then I have written this procedure for creating the maintenance routes.
CREATE OR REPLACE PROCEDURE APPS.XXINN_CREATE_MAINTNCE_ROUTE(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER) IS
P_HEADER_REC Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_OPERATION_REC Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
P_RESOURCE_REC Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
p_RETURN_STATUS VARCHAR2(240);
p_MSG_COUNT NUMBER;
l_ret_text VARCHAR2(32000);
p_Message_List Error_Handler.Error_Tbl_Type;
i NUMBER;
j NUMBER;
x_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
x_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
x_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
x_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
x_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
x_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
CURSOR C1_ENG IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code
FROM APPS.XXINN_MAINTENANCE_ROUTES t;
CURSOR C1_OPR(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.department_code,
LTRIM (RTRIM(T.LONG_DESCRIPTION)) LONG_DESCRIPTION,
translate (LTRIM(RTRIM( t.OPERATION_DESCRIPTION)),'ÇĞŞİÜÖ','CGSIUO') OPERATION_DESCRIPTION
FROM APPS.XXINN_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
ORDER BY t.operation_sequence_number;
CURSOR C1_RES(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2, p_seq_number NUMBER) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.department_code,
t.resource_sequence_number,
t.resource_code,
t.usage_rate_or_amount,
t.ASSIGNED_UNITS
FROM APPS.XXINN_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
AND t.operation_sequence_number = p_seq_number
ORDER BY t.resource_sequence_number;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(1117, 23118, 426); --user_id, resp_id, resp_appl_id
FOR C_REC IN C1_ENG LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, C_REC.ASSEMBLY_ITEM_NAME);
DBMS_OUTPUT.PUT_LINE(C_REC.ASSEMBLY_ITEM_NAME);
P_HEADER_REC := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_HEADER_REC.ASSEMBLY_ITEM_NAME := C_REC.ASSEMBLY_ITEM_NAME;
P_HEADER_REC.ORGANIZATION_CODE := C_REC.ORGANIZATION_CODE;
P_HEADER_REC.Eng_Routing_Flag := 2; -- 1 for manufacturing 2- for Engineering
P_HEADER_REC.Transaction_Type := 'CREATE'; --
P_HEADER_REC.Return_Status := NULL; --
i := 0;
j := 0;
P_OPERATION_REC.DELETE;
P_RESOURCE_REC.DELETE;
FOR c_rec2 IN C1_OPR(C_REC.ASSEMBLY_ITEM_NAME, C_REC.ORGANIZATION_CODE) LOOP
i := i + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
' ' || C_REC2.OPERATION_SEQUENCE_NUMBER || '-' ||
C_REC2.DEPARTMENT_CODE);
DBMS_OUTPUT.PUT_LINE(' ' ||
C_REC2.OPERATION_SEQUENCE_NUMBER || '-' ||
C_REC2.DEPARTMENT_CODE||'-'||C_REC2.OPERATION_DESCRIPTION);
P_OPERATION_REC(i).ASSEMBLY_ITEM_NAME := C_REC2.ASSEMBLY_ITEM_NAME;
P_OPERATION_REC(i).ORGANIZATION_CODE := C_REC2.ORGANIZATION_CODE;
P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER := C_REC2.OPERATION_SEQUENCE_NUMBER;
P_OPERATION_REC(i).DEPARTMENT_CODE := C_REC2.DEPARTMENT_CODE;
P_OPERATION_REC(i).LONG_DESCRIPTION := C_REC2.LONG_DESCRIPTION; --Ana tabloda long description diye bir alan aç
P_OPERATION_REC(i).OPERATION_DESCRIPTION := C_REC2.OPERATION_DESCRIPTION;
P_OPERATION_REC(i).Operation_Type := 1; --
P_OPERATION_REC(i).Start_Effective_Date := SYSDATE; --
P_OPERATION_REC(i).Transaction_Type := 'CREATE'; --
FOR c_rec3 IN C1_RES(C_REC.ASSEMBLY_ITEM_NAME,
C_REC.ORGANIZATION_CODE,
C_REC2.OPERATION_SEQUENCE_NUMBER) LOOP
j := j + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
' ' ||
C_REC3.RESOURCE_SEQUENCE_NUMBER || '-' ||
C_REC3.RESOURCE_CODE);
DBMS_OUTPUT.PUT_LINE(' ' ||
C_REC3.RESOURCE_SEQUENCE_NUMBER || '-' ||
C_REC3.RESOURCE_CODE);
P_RESOURCE_REC(j).ASSEMBLY_ITEM_NAME := C_REC3.ASSEMBLY_ITEM_NAME;
P_RESOURCE_REC(j).ORGANIZATION_CODE := C_REC3.ORGANIZATION_CODE;
P_RESOURCE_REC(j).OPERATION_SEQUENCE_NUMBER := C_REC3.OPERATION_SEQUENCE_NUMBER;
P_RESOURCE_REC(j).RESOURCE_SEQUENCE_NUMBER := C_REC3.RESOURCE_SEQUENCE_NUMBER;
P_RESOURCE_REC(j).RESOURCE_CODE := C_REC3.RESOURCE_CODE;
P_RESOURCE_REC(j).USAGE_RATE_OR_AMOUNT := C_REC3.USAGE_RATE_OR_AMOUNT;
P_RESOURCE_REC(j).Operation_Type := 1; --
P_RESOURCE_REC(j).Op_Start_Effective_Date := SYSDATE; --
P_RESOURCE_REC(j).Transaction_Type := 'CREATE'; --
P_RESOURCE_REC(j).Schedule_Flag := 1;
P_RESOURCE_REC(J).ASSIGNED_UNITS := C_REC3.ASSIGNED_UNITS;
END LOOP; --res
END LOOP; -- opr
x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
x_rtg_revision_tbl.delete;
x_operation_tbl.delete;
x_op_resource_tbl.delete;
x_sub_resource_tbl.delete;
x_op_network_tbl.delete;
APPS.BOM_RTG_PUB.PROCESS_RTG(p_init_msg_list => TRUE,
p_rtg_header_rec => P_HEADER_REC,
p_operation_tbl => P_OPERATION_REC,
p_op_resource_tbl => P_RESOURCE_REC,
x_rtg_header_rec => x_rtg_header_rec,
x_rtg_revision_tbl => x_rtg_revision_tbl,
x_operation_tbl => x_operation_tbl,
x_op_resource_tbl => x_op_resource_tbl,
x_sub_resource_tbl => x_sub_resource_tbl,
x_op_network_tbl => x_op_network_tbl,
x_return_status => p_RETURN_STATUS,
X_MSG_COUNT => p_MSG_COUNT);
IF p_RETURN_STATUS <> 'S' THEN
error_handler.Get_Message_List(p_message_list);
FOR i IN 1 .. p_message_list.COUNT LOOP
IF i = 1 THEN
l_ret_text := p_Message_List(i).message_text;
ELSE
l_ret_text := l_ret_text || chr(10) || p_Message_List(i).message_text;
END IF;
END LOOP;
-- ROLLBACK;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' ERROR!!!!!!!' || chr(10) || 'Error:' ||p_Message_List(i).message_type||'-'||p_Message_List(i).message_name||
l_ret_text);
DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' ERROR!!!!!!!' || chr(10) || 'Error:' ||p_Message_List(i).message_type||'-'||p_Message_List(i).message_name||
l_ret_text);
UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR = l_ret_text
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code;
ELSE
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' the process is successful..');
DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' the process is successful..');
UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR = null
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code;
END IF;
END LOOP;
END
;
/
Firstly I create a custom table then I insert the datas from excel sheet.
CREATE TABLE XXINN_MAINTENANCE_ROUTES(
ASSEMBLY_ITEM_NAME VARCHAR2(240 BYTE),
ORGANIZATION_CODE VARCHAR2(3 BYTE),
LONG_DESCRIPTION VARCHAR2(4000 BYTE),
OPERATION_DESCRIPTION VARCHAR2(400 BYTE),
OPERATION_SEQUENCE_NUMBER NUMBER,
DEPARTMENT_CODE VARCHAR2(10 BYTE),
RESOURCE_SEQUENCE_NUMBER NUMBER,
RESOURCE_CODE VARCHAR2(10 BYTE),
USAGE_RATE_OR_AMOUNT NUMBER,
ASSIGNED_UNITS NUMBER,
ERROR VARCHAR2(2000 BYTE)
)
Then I have written this procedure for creating the maintenance routes.
CREATE OR REPLACE PROCEDURE APPS.XXINN_CREATE_MAINTNCE_ROUTE(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER) IS
P_HEADER_REC Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_OPERATION_REC Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
P_RESOURCE_REC Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
p_RETURN_STATUS VARCHAR2(240);
p_MSG_COUNT NUMBER;
l_ret_text VARCHAR2(32000);
p_Message_List Error_Handler.Error_Tbl_Type;
i NUMBER;
j NUMBER;
x_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
x_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
x_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
x_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
x_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
x_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;
CURSOR C1_ENG IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code
FROM APPS.XXINN_MAINTENANCE_ROUTES t;
CURSOR C1_OPR(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.department_code,
LTRIM (RTRIM(T.LONG_DESCRIPTION)) LONG_DESCRIPTION,
translate (LTRIM(RTRIM( t.OPERATION_DESCRIPTION)),'ÇĞŞİÜÖ','CGSIUO') OPERATION_DESCRIPTION
FROM APPS.XXINN_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
ORDER BY t.operation_sequence_number;
CURSOR C1_RES(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2, p_seq_number NUMBER) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.department_code,
t.resource_sequence_number,
t.resource_code,
t.usage_rate_or_amount,
t.ASSIGNED_UNITS
FROM APPS.XXINN_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
AND t.operation_sequence_number = p_seq_number
ORDER BY t.resource_sequence_number;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(1117, 23118, 426); --user_id, resp_id, resp_appl_id
FOR C_REC IN C1_ENG LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, C_REC.ASSEMBLY_ITEM_NAME);
DBMS_OUTPUT.PUT_LINE(C_REC.ASSEMBLY_ITEM_NAME);
P_HEADER_REC := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_HEADER_REC.ASSEMBLY_ITEM_NAME := C_REC.ASSEMBLY_ITEM_NAME;
P_HEADER_REC.ORGANIZATION_CODE := C_REC.ORGANIZATION_CODE;
P_HEADER_REC.Eng_Routing_Flag := 2; -- 1 for manufacturing 2- for Engineering
P_HEADER_REC.Transaction_Type := 'CREATE'; --
P_HEADER_REC.Return_Status := NULL; --
i := 0;
j := 0;
P_OPERATION_REC.DELETE;
P_RESOURCE_REC.DELETE;
FOR c_rec2 IN C1_OPR(C_REC.ASSEMBLY_ITEM_NAME, C_REC.ORGANIZATION_CODE) LOOP
i := i + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
' ' || C_REC2.OPERATION_SEQUENCE_NUMBER || '-' ||
C_REC2.DEPARTMENT_CODE);
DBMS_OUTPUT.PUT_LINE(' ' ||
C_REC2.OPERATION_SEQUENCE_NUMBER || '-' ||
C_REC2.DEPARTMENT_CODE||'-'||C_REC2.OPERATION_DESCRIPTION);
P_OPERATION_REC(i).ASSEMBLY_ITEM_NAME := C_REC2.ASSEMBLY_ITEM_NAME;
P_OPERATION_REC(i).ORGANIZATION_CODE := C_REC2.ORGANIZATION_CODE;
P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER := C_REC2.OPERATION_SEQUENCE_NUMBER;
P_OPERATION_REC(i).DEPARTMENT_CODE := C_REC2.DEPARTMENT_CODE;
P_OPERATION_REC(i).LONG_DESCRIPTION := C_REC2.LONG_DESCRIPTION; --Ana tabloda long description diye bir alan aç
P_OPERATION_REC(i).OPERATION_DESCRIPTION := C_REC2.OPERATION_DESCRIPTION;
P_OPERATION_REC(i).Operation_Type := 1; --
P_OPERATION_REC(i).Start_Effective_Date := SYSDATE; --
P_OPERATION_REC(i).Transaction_Type := 'CREATE'; --
FOR c_rec3 IN C1_RES(C_REC.ASSEMBLY_ITEM_NAME,
C_REC.ORGANIZATION_CODE,
C_REC2.OPERATION_SEQUENCE_NUMBER) LOOP
j := j + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
' ' ||
C_REC3.RESOURCE_SEQUENCE_NUMBER || '-' ||
C_REC3.RESOURCE_CODE);
DBMS_OUTPUT.PUT_LINE(' ' ||
C_REC3.RESOURCE_SEQUENCE_NUMBER || '-' ||
C_REC3.RESOURCE_CODE);
P_RESOURCE_REC(j).ASSEMBLY_ITEM_NAME := C_REC3.ASSEMBLY_ITEM_NAME;
P_RESOURCE_REC(j).ORGANIZATION_CODE := C_REC3.ORGANIZATION_CODE;
P_RESOURCE_REC(j).OPERATION_SEQUENCE_NUMBER := C_REC3.OPERATION_SEQUENCE_NUMBER;
P_RESOURCE_REC(j).RESOURCE_SEQUENCE_NUMBER := C_REC3.RESOURCE_SEQUENCE_NUMBER;
P_RESOURCE_REC(j).RESOURCE_CODE := C_REC3.RESOURCE_CODE;
P_RESOURCE_REC(j).USAGE_RATE_OR_AMOUNT := C_REC3.USAGE_RATE_OR_AMOUNT;
P_RESOURCE_REC(j).Operation_Type := 1; --
P_RESOURCE_REC(j).Op_Start_Effective_Date := SYSDATE; --
P_RESOURCE_REC(j).Transaction_Type := 'CREATE'; --
P_RESOURCE_REC(j).Schedule_Flag := 1;
P_RESOURCE_REC(J).ASSIGNED_UNITS := C_REC3.ASSIGNED_UNITS;
END LOOP; --res
END LOOP; -- opr
x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
x_rtg_revision_tbl.delete;
x_operation_tbl.delete;
x_op_resource_tbl.delete;
x_sub_resource_tbl.delete;
x_op_network_tbl.delete;
APPS.BOM_RTG_PUB.PROCESS_RTG(p_init_msg_list => TRUE,
p_rtg_header_rec => P_HEADER_REC,
p_operation_tbl => P_OPERATION_REC,
p_op_resource_tbl => P_RESOURCE_REC,
x_rtg_header_rec => x_rtg_header_rec,
x_rtg_revision_tbl => x_rtg_revision_tbl,
x_operation_tbl => x_operation_tbl,
x_op_resource_tbl => x_op_resource_tbl,
x_sub_resource_tbl => x_sub_resource_tbl,
x_op_network_tbl => x_op_network_tbl,
x_return_status => p_RETURN_STATUS,
X_MSG_COUNT => p_MSG_COUNT);
IF p_RETURN_STATUS <> 'S' THEN
error_handler.Get_Message_List(p_message_list);
FOR i IN 1 .. p_message_list.COUNT LOOP
IF i = 1 THEN
l_ret_text := p_Message_List(i).message_text;
ELSE
l_ret_text := l_ret_text || chr(10) || p_Message_List(i).message_text;
END IF;
END LOOP;
-- ROLLBACK;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' ERROR!!!!!!!' || chr(10) || 'Error:' ||p_Message_List(i).message_type||'-'||p_Message_List(i).message_name||
l_ret_text);
DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' ERROR!!!!!!!' || chr(10) || 'Error:' ||p_Message_List(i).message_type||'-'||p_Message_List(i).message_name||
l_ret_text);
UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR = l_ret_text
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code;
ELSE
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' the process is successful..');
DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' the process is successful..');
UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR = null
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code;
END IF;
END LOOP;
END
;
/
OK
ReplyDelete