Firstly I create a custom table then I insert the datas from excel sheet.<\/p>\n
[sql]CREATE TABLE XXINN_MAINTENANCE_ROUTES(
\nASSEMBLY_ITEM_NAME VARCHAR2(240 BYTE),
\nORGANIZATION_CODE VARCHAR2(3 BYTE),
\nLONG_DESCRIPTION VARCHAR2(4000 BYTE),
\nOPERATION_DESCRIPTION VARCHAR2(400 BYTE),
\nOPERATION_SEQUENCE_NUMBER NUMBER,
\nDEPARTMENT_CODE VARCHAR2(10 BYTE),
\nRESOURCE_SEQUENCE_NUMBER NUMBER,
\nRESOURCE_CODE VARCHAR2(10 BYTE),
\nUSAGE_RATE_OR_AMOUNT NUMBER,
\nASSIGNED_UNITS NUMBER,
\nERROR VARCHAR2(2000 BYTE)
\n)[\/sql]<\/p>\n
Then I have written this procedure for creating the maintenance routes. <\/p>\n
[sql]CREATE OR REPLACE PROCEDURE APPS.XXINN_CREATE_MAINTNCE_ROUTE(ERRBUF OUT VARCHAR2,
\nRETCODE OUT NUMBER) IS
\nP_HEADER_REC Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
\nP_OPERATION_REC Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
\nP_RESOURCE_REC Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
\np_RETURN_STATUS VARCHAR2(240);
\np_MSG_COUNT NUMBER;
\nl_ret_text VARCHAR2(32000);
\np_Message_List Error_Handler.Error_Tbl_Type;
\ni NUMBER;
\nj NUMBER;
\nx_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type;
\nx_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
\nx_operation_tbl Bom_Rtg_Pub.Operation_Tbl_Type;
\nx_op_resource_tbl Bom_Rtg_Pub.Op_Resource_Tbl_Type;
\nx_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
\nx_op_network_tbl Bom_Rtg_Pub.Op_Network_Tbl_Type;<\/p>\n
CURSOR C1_ENG IS
\nSELECT DISTINCT t.assembly_item_name,
\nt.organization_code
\nFROM APPS.XXINN_MAINTENANCE_ROUTES t;<\/p>\n
CURSOR C1_OPR(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2) IS
\nSELECT DISTINCT t.assembly_item_name,
\nt.organization_code,
\nt.operation_sequence_number,
\nt.department_code,
\nLTRIM (RTRIM(T.LONG_DESCRIPTION)) LONG_DESCRIPTION,
\ntranslate (LTRIM(RTRIM( t.OPERATION_DESCRIPTION)),’\u00c7\u011e\u015e\u0130\u00dc\u00d6’,’CGSIUO’) OPERATION_DESCRIPTION
\nFROM APPS.XXINN_MAINTENANCE_ROUTES t
\nWHERE t.assembly_item_name = p_ASSEMBLY_ITEM
\nAND t.organization_code = p_org_code
\nORDER BY t.operation_sequence_number;<\/p>\n
CURSOR C1_RES(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2, p_seq_number NUMBER) IS
\nSELECT DISTINCT t.assembly_item_name,
\nt.organization_code,
\nt.operation_sequence_number,
\nt.department_code,
\nt.resource_sequence_number,
\nt.resource_code,
\nt.usage_rate_or_amount,
\nt.ASSIGNED_UNITS
\nFROM APPS.XXINN_MAINTENANCE_ROUTES t
\nWHERE t.assembly_item_name = p_ASSEMBLY_ITEM
\nAND t.organization_code = p_org_code
\nAND t.operation_sequence_number = p_seq_number
\nORDER BY t.resource_sequence_number;<\/p>\n
BEGIN
\nFND_GLOBAL.APPS_INITIALIZE(1117, 23118, 426); –user_id, resp_id, resp_appl_id
\nFOR C_REC IN C1_ENG LOOP
\nFND_FILE.PUT_LINE(FND_FILE.OUTPUT, C_REC.ASSEMBLY_ITEM_NAME);
\nDBMS_OUTPUT.PUT_LINE(C_REC.ASSEMBLY_ITEM_NAME);<\/p>\n
P_HEADER_REC := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
\nP_HEADER_REC.ASSEMBLY_ITEM_NAME := C_REC.ASSEMBLY_ITEM_NAME;
\nP_HEADER_REC.ORGANIZATION_CODE := C_REC.ORGANIZATION_CODE;
\nP_HEADER_REC.Eng_Routing_Flag := 2;
\nP_HEADER_REC.Transaction_Type := ‘CREATE’; —
\nP_HEADER_REC.Return_Status := NULL; —
\ni := 0;
\nj := 0;
\nP_OPERATION_REC.DELETE;
\nP_RESOURCE_REC.DELETE;<\/p>\n
FOR c_rec2 IN C1_OPR(C_REC.ASSEMBLY_ITEM_NAME, C_REC.ORGANIZATION_CODE) LOOP
\ni := i + 1;
\nFND_FILE.PUT_LINE(FND_FILE.OUTPUT,
\n‘ ‘ || C_REC2.OPERATION_SEQUENCE_NUMBER || ‘-‘ ||
\nC_REC2.DEPARTMENT_CODE);
\nDBMS_OUTPUT.PUT_LINE(‘ ‘ ||
\nC_REC2.OPERATION_SEQUENCE_NUMBER || ‘-‘ ||
\nC_REC2.DEPARTMENT_CODE||’-‘||C_REC2.OPERATION_DESCRIPTION);
\nP_OPERATION_REC(i).ASSEMBLY_ITEM_NAME := C_REC2.ASSEMBLY_ITEM_NAME;
\nP_OPERATION_REC(i).ORGANIZATION_CODE := C_REC2.ORGANIZATION_CODE;
\nP_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER := C_REC2.OPERATION_SEQUENCE_NUMBER;
\nP_OPERATION_REC(i).DEPARTMENT_CODE := C_REC2.DEPARTMENT_CODE;
\nP_OPERATION_REC(i).LONG_DESCRIPTION := C_REC2.LONG_DESCRIPTION; –Ana tabloda long description diye bir alan a\u00e7
\nP_OPERATION_REC(i).OPERATION_DESCRIPTION := C_REC2.OPERATION_DESCRIPTION;
\nP_OPERATION_REC(i).Operation_Type := 1; —
\nP_OPERATION_REC(i).Start_Effective_Date := SYSDATE; —
\nP_OPERATION_REC(i).Transaction_Type := ‘CREATE’; —<\/p>\n
FOR c_rec3 IN C1_RES(C_REC.ASSEMBLY_ITEM_NAME,
\nC_REC.ORGANIZATION_CODE,
\nC_REC2.OPERATION_SEQUENCE_NUMBER) LOOP
\nj := j + 1;
\nFND_FILE.PUT_LINE(FND_FILE.OUTPUT,
\n‘ ‘ ||
\nC_REC3.RESOURCE_SEQUENCE_NUMBER || ‘-‘ ||
\nC_REC3.RESOURCE_CODE);
\nDBMS_OUTPUT.PUT_LINE(‘ ‘ ||
\nC_REC3.RESOURCE_SEQUENCE_NUMBER || ‘-‘ ||
\nC_REC3.RESOURCE_CODE);
\nP_RESOURCE_REC(j).ASSEMBLY_ITEM_NAME := C_REC3.ASSEMBLY_ITEM_NAME;
\nP_RESOURCE_REC(j).ORGANIZATION_CODE := C_REC3.ORGANIZATION_CODE;
\nP_RESOURCE_REC(j).OPERATION_SEQUENCE_NUMBER := C_REC3.OPERATION_SEQUENCE_NUMBER;
\nP_RESOURCE_REC(j).RESOURCE_SEQUENCE_NUMBER := C_REC3.RESOURCE_SEQUENCE_NUMBER;
\nP_RESOURCE_REC(j).RESOURCE_CODE := C_REC3.RESOURCE_CODE;
\nP_RESOURCE_REC(j).USAGE_RATE_OR_AMOUNT := C_REC3.USAGE_RATE_OR_AMOUNT;
\nP_RESOURCE_REC(j).Operation_Type := 1; —
\nP_RESOURCE_REC(j).Op_Start_Effective_Date := SYSDATE; —
\nP_RESOURCE_REC(j).Transaction_Type := ‘CREATE’; —
\nP_RESOURCE_REC(j).Schedule_Flag := 1;
\nP_RESOURCE_REC(J).ASSIGNED_UNITS := C_REC3.ASSIGNED_UNITS;<\/p>\n
END LOOP; –res<\/p>\n
END LOOP; — opr<\/p>\n
x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
\nx_rtg_revision_tbl.delete;
\nx_operation_tbl.delete;
\nx_op_resource_tbl.delete;
\nx_sub_resource_tbl.delete;
\nx_op_network_tbl.delete;<\/p>\n
APPS.BOM_RTG_PUB.PROCESS_RTG(p_init_msg_list => TRUE,
\np_rtg_header_rec => P_HEADER_REC,
\np_operation_tbl => P_OPERATION_REC,
\np_op_resource_tbl => P_RESOURCE_REC,
\nx_rtg_header_rec => x_rtg_header_rec,
\nx_rtg_revision_tbl => x_rtg_revision_tbl,
\nx_operation_tbl => x_operation_tbl,
\nx_op_resource_tbl => x_op_resource_tbl,
\nx_sub_resource_tbl => x_sub_resource_tbl,
\nx_op_network_tbl => x_op_network_tbl,
\nx_return_status => p_RETURN_STATUS,
\nX_MSG_COUNT => p_MSG_COUNT);<\/p>\n
IF p_RETURN_STATUS <> ‘S’ THEN
\nerror_handler.Get_Message_List(p_message_list);
\nFOR i IN 1 .. p_message_list.COUNT LOOP<\/p>\n
IF i = 1 THEN
\nl_ret_text := p_Message_List(i).message_text;<\/p>\n
ELSE
\nl_ret_text := l_ret_text || chr(10) || p_Message_List(i).message_text;
\nEND IF;<\/p>\n
END LOOP;<\/p>\n
— ROLLBACK;<\/p>\n
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
\nP_HEADER_REC.ASSEMBLY_ITEM_NAME ||
\n‘ ERROR!!!!!!!’ || chr(10) || ‘Error:’ ||p_Message_List(i).message_type||’-‘||p_Message_List(i).message_name||
\nl_ret_text);<\/p>\n
DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
\n‘ ERROR!!!!!!!’ || chr(10) || ‘Error:’ ||p_Message_List(i).message_type||’-‘||p_Message_List(i).message_name||
\nl_ret_text);<\/p>\n
UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
\nSET XXINN.ERROR = l_ret_text
\nWHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
\nAND XXINN.ORGANIZATION_CODE = c_rec.organization_code;<\/p>\n
ELSE<\/p>\n
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,<\/p>\n
P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
\n‘ the process is successful..’);<\/p>\n
DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
\n‘ the process is successful..’);<\/p>\n
UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
\nSET XXINN.ERROR = null
\nWHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
\nAND XXINN.ORGANIZATION_CODE = c_rec.organization_code;<\/p>\n
END IF;<\/p>\n
END LOOP;<\/p>\n
END
\n–\u00a9 EnginH
\n;
\n\/[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"
Firstly I create a custom table then I insert the datas from excel sheet. [sql]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) )[\/sql] Then I have written this procedure for creating […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[108,109,106,107],"class_list":["post-605","post","type-post","status-publish","format-standard","hentry","category-oracle-e-is-yonetim-sistemi","tag-api","tag-bom_rtg_pub","tag-creating-routes","tag-route"],"_links":{"self":[{"href":"https:\/\/enginhafizoglu.com\/index.php?rest_route=\/wp\/v2\/posts\/605","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enginhafizoglu.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enginhafizoglu.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enginhafizoglu.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/enginhafizoglu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=605"}],"version-history":[{"count":6,"href":"https:\/\/enginhafizoglu.com\/index.php?rest_route=\/wp\/v2\/posts\/605\/revisions"}],"predecessor-version":[{"id":606,"href":"https:\/\/enginhafizoglu.com\/index.php?rest_route=\/wp\/v2\/posts\/605\/revisions\/606"}],"wp:attachment":[{"href":"https:\/\/enginhafizoglu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=605"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enginhafizoglu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=605"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enginhafizoglu.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=605"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}