Creating Routings and Assigning Resources via API in Oracle EBS

Introduction/ Issue: This blog contains an API and that can be used in Oracle EBS to create Routings and assign resources.

Why we need to do / Cause of the issue: The business requires to create Routings and assign resources using an API without manual creation.

How do we solve: Create an Oracle package to create Routings and assign resources under that.

CREATE OR REPLACE EDITIONABLE PROCEDURE "APPS"."XX_MAINTAIN_ROUTES_PRC"

(errbuf OUT VARCHAR2,RETCODE OUT NUMBER)

AS

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:=0;

l_ret_text VARCHAR2 (4000);

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;

l_hdritem_cnt number;

CURSOR C1_ENG IS

SELECT DISTINCT t.assembly_item_name,

t.organization_code

--t.transaction_type

FROM APPS.XX_MAINTENANCE_ROUTES t

WHERE PROCESS_FLAG = 'P'

ORDER BY 2,1;

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.operation_code,

t.referenced,

t.department_code,

t.transaction_type,

LTRIM (RTRIM (T.LONG_DESCRIPTION)) LONG_DESCRIPTION,

LTRIM (RTRIM (T.OPERATION_DESCRIPTION)) OPERATION_DESCRIPTION

FROM APPS.XX_MAINTENANCE_ROUTES T

WHERE t.assembly_item_name = P_ASSEMBLY_ITEM

AND t.organization_code = p_org_code

and t.operation_sequence_number is not null

AND PROCESS_FLAG = 'P'

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.operation_code, t.referenced, t.department_code,

t.resource_sequence_number, t.resource_code, t.basis_type,

t.usage_rate_or_amount, t.usage_rate_or_amount_inverse,

t.ASSIGNED_UNITS, t.transaction_type

FROM APPS.XX_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

and t.resource_sequence_number is not null

AND PROCESS_FLAG in( 'S')

ORDER BY t.resource_sequence_number;

l_operation_sequence_id number;

BEGIN

DBMS_OUTPUT.PUT_LINE ('Starting of XX_MAINTENANCE_ROUTES_PRC Procedure');

FND_FILE.PUT_LINE (FND_FILE.LOG,'Starting of XX_MAINTENANCE_ROUTES_PRC Procedure');

FND_GLOBAL.apps_initialize (7617, 57675, 702, 0);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'Initializing Apps');

-- FND_GLOBAL.Apps_Initialize (FND_GLOBAL.USER_ID,

-- FND_GLOBAL.RESP_ID,

-- FND_GLOBAL.RESP_APPL_ID);

----DBMS_OUTPUT.PUT_LINE ('P_MSG_COUNT' ||P_MSG_COUNT);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_MSG_COUNT' ||P_MSG_COUNT);

--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.LOG, 'loop start');

FND_FILE.PUT_LINE (FND_FILE.LOG, ' C_REC.ASSEMBLY_ITEM_NAME:' || C_REC.ASSEMBLY_ITEM_NAME);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC.ORGANIZATION_CODE:' ||C_REC.ORGANIZATION_CODE);

--FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_REC.ASSEMBLY_ITEM_NAME);

DBMS_OUTPUT.PUT_LINE ('Assembly Item Name'|| C_REC.ASSEMBLY_ITEM_NAME);

--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

----DBMS_OUTPUT.PUT_LINE ('C1_OPR Loop Start .....' );

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_OPR Loop Start .....' );

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;

p_message_list.delete;

P_OPERATION_REC.DELETE;

i := 1;

--P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER :=null;

BEGIN

SELECT count(msi.segment1)

INTO l_hdritem_cnt

FROM apps.BOM_OPERATIONAL_ROUTINGS bor,

apps.mtl_system_items_b msi,

apps.org_organization_definitions ood

WHERE msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID

AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID

AND msi.organization_id = ood.organization_id

AND bor.organization_id = ood.organization_id

AND ood.organization_code = C_REC.ORGANIZATION_CODE

AND msi.segment1 = C_REC.ASSEMBLY_ITEM_NAME;

EXCEPTION WHEN OTHERS THEN

l_hdritem_cnt := 0;

END;

----DBMS_OUTPUT.PUT_LINE ('C1_OPR Loop l_hdritem_cnt:' || l_hdritem_cnt);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_OPR Loop l_hdritem_cnt:' || l_hdritem_cnt);

P_HEADER_REC := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;

IF l_hdritem_cnt < 1 then

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; -- 2 for manufacturing 1- for Engineering

--P_HEADER_REC.alternate_routing_code := 1;

--P_HEADER_REC.cfm_routing_flag :=1;

P_HEADER_REC.transaction_type := 'CREATE';

FND_FILE.PUT_LINE (FND_FILE.LOG, 'Assembly Item / Organization is Invalid or Blank');

ELSE

P_HEADER_REC.ASSEMBLY_ITEM_NAME := NULL;

P_HEADER_REC.ORGANIZATION_CODE := NULL;

P_HEADER_REC.Eng_Routing_Flag := NULL; -- 2 for manufacturing 1- for Engineering

P_HEADER_REC.transaction_type := NULL;

END IF;

FND_FILE.PUT_LINE (FND_FILE.LOG, ' P_HEADER_REC.Eng_Routing_Flag:' ||P_HEADER_REC.Eng_Routing_Flag);

FND_FILE.PUT_LINE (FND_FILE.LOG, ' i:' ||i);

FND_FILE.PUT_LINE (FND_FILE.LOG, ' C_REC2.OPERATION_SEQUENCE_NUMBER:' ||C_REC2.OPERATION_SEQUENCE_NUMBER);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC2.OPERATION_CODE:' ||C_REC2.OPERATION_CODE);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC2.REFERENCED:' ||C_REC2.REFERENCED);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC2.DEPARTMENT_CODE:' ||C_REC2.DEPARTMENT_CODE);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC2.LONG_DESCRIPTION:' ||C_REC2.LONG_DESCRIPTION);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC2.OPERATION_DESCRIPTION:' ||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;

-- FND_FILE.PUT_LINE (FND_FILE.LOG, ' P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER11:' ||P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER);

P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER := C_REC2.OPERATION_SEQUENCE_NUMBER;

-- FND_FILE.PUT_LINE (FND_FILE.LOG, ' P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER22:' ||P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER);

P_OPERATION_REC(i).STANDARD_OPERATION_CODE := C_REC2.OPERATION_CODE;

if (C_REC2.REFERENCED ='Yes' or C_REC2.REFERENCED ='YES') then

P_OPERATION_REC(i).REFERENCE_FLAG := 1;

else

P_OPERATION_REC(i).REFERENCE_FLAG := 2;

end if;

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 alan bir a?

P_OPERATION_REC(i).OPERATION_DESCRIPTION := C_REC2.OPERATION_DESCRIPTION;

P_OPERATION_REC(i).Operation_Type := 1;

IF C_REC2.TRANSACTION_TYPE LIKE '%CREATE%' THEN

P_OPERATION_REC(i).transaction_type := 'CREATE';

P_OPERATION_REC(i).Start_Effective_Date := SYSDATE;

P_OPERATION_REC(i).Disable_Date := null;

FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.1-C_REC2. ' ||P_OPERATION_REC(i).transaction_type);

ELSE

P_OPERATION_REC(i).transaction_type := 'UPDATE';

BEGIN

SELECT bos.EFFECTIVITY_DATE

INTO P_OPERATION_REC(i).Start_Effective_Date

FROM apps.BOM_OPERATIONAL_ROUTINGS_V bor,

apps.mtl_system_items_b msi,

apps.BOM_OPERATION_SEQUENCES_V bos,

apps.org_organization_definitions ood

WHERE msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID

AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID

AND msi.ORGANIZATION_ID = ood.ORGANIZATION_ID

AND bor.ORGANIZATION_ID = ood.ORGANIZATION_ID

AND bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID

AND ood.ORGANIZATION_CODE = C_REC2.ORGANIZATION_CODE

and msi.segment1 = C_REC2.ASSEMBLY_ITEM_NAME

and bos.OPERATION_SEQ_NUM =C_REC2.OPERATION_SEQUENCE_NUMBER;

P_OPERATION_REC(i).Disable_Date := null;

EXCEPTION WHEN OTHERS THEN

P_OPERATION_REC(i).Start_Effective_Date := SYSDATE;

P_OPERATION_REC(i).Disable_Date := null;

END;

-- FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.1-C_REC2. ' || P_OPERATION_REC(i).transaction_type );

END IF;

BEGIN

SELECT bos.EFFECTIVITY_DATE

INTO P_OPERATION_REC(i).Start_Effective_Date

FROM apps.BOM_OPERATIONAL_ROUTINGS_V bor,

apps.mtl_system_items_b msi,

apps.BOM_OPERATION_SEQUENCES_V bos,

apps.org_organization_definitions ood

WHERE msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID

AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID

AND msi.ORGANIZATION_ID = ood.ORGANIZATION_ID

AND bor.ORGANIZATION_ID = ood.ORGANIZATION_ID

AND bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID

AND ood.ORGANIZATION_CODE = C_REC2.ORGANIZATION_CODE

and msi.segment1 = C_REC2.ASSEMBLY_ITEM_NAME

and bos.OPERATION_SEQ_NUM =C_REC2.OPERATION_SEQUENCE_NUMBER;

P_OPERATION_REC(i).Disable_Date := null;

EXCEPTION WHEN OTHERS THEN

P_OPERATION_REC(i).Start_Effective_Date := SYSDATE;

P_OPERATION_REC(i).Disable_Date := null;

END;

-- FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.1-C_REC2. ' || P_OPERATION_REC(i).transaction_type );

--x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;

P_RETURN_STATUS :=null;

P_MSG_COUNT :=0;

l_ret_text :=null;

--FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_RETURN_STATUS00' ||P_RETURN_STATUS);

--FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_MSG_COUNT00' ||P_MSG_COUNT);

--FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_ret_text00' ||l_ret_text);

begin

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);

commit;

exception when others then

DBMS_OUTPUT.PUT_LINE ('OPERATION exception' ||sqlerrm);

DBMS_OUTPUT.PUT_LINE ('OPERATION P_RETURN_STATUSEXP' ||P_RETURN_STATUS);

DBMS_OUTPUT.PUT_LINE ('OPERATION P_MSG_COUNTEXP' ||P_MSG_COUNT);

DBMS_OUTPUT.PUT_LINE ('OPERATION l_ret_textEXP' ||l_ret_text);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'OPERATION exception' ||sqlerrm);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'OPERATION P_RETURN_STATUSEXP' ||P_RETURN_STATUS);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'OPERATION P_MSG_COUNTEXP' ||P_MSG_COUNT);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'OPERATION l_ret_textEXP' ||l_ret_text);

end;

IF P_RETURN_STATUS <> 'S' THEN

error_handler.Get_Message_List (p_message_list);

l_ret_text := null;

FOR i IN 1 .. p_message_list.COUNT LOOP

IF i = 1 THEN

--if l_ret_text is null then

DBMS_OUTPUT.PUT_LINE ('p_Message_List (i).message_text,' ||p_Message_List (i).message_text);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_Message_List (i).message_text,' ||p_Message_List (i).message_text);

l_ret_text := substr(p_Message_List (i).message_text,1,250);

ELSE

DBMS_OUTPUT.PUT_LINE ('p_Message_List (i).message_textelse' ||p_Message_List (i).message_text);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_Message_List (i).message_textelse' ||p_Message_List (i).message_text);

l_ret_text := l_ret_text || '~'|| substr(p_Message_List (i).message_text,1,250);

END IF;

END LOOP;

--ROLLBACK;

DBMS_OUTPUT.PUT_LINE ('ERROR!!!!' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code||'-'||l_ret_text);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR!!!!' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code||'-'||l_ret_text);

UPDATE APPS.XX_MAINTENANCE_ROUTES xxinn

SET XXINN.ERROR_MESSAGE = l_ret_text,

PROCESS_FLAG ='E'

WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name

AND XXINN.ORGANIZATION_CODE = c_rec.organization_code

AND xxinn.operation_sequence_number = C_REC2.OPERATION_SEQUENCE_NUMBER;

commit;

ELSE

DBMS_OUTPUT.PUT_LINE ('The process is successful ..' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'The process is successful ..' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code);

UPDATE APPS.XX_MAINTENANCE_ROUTES xxinn

SET PROCESS_FLAG ='S'

WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name

AND XXINN.ORGANIZATION_CODE = c_rec.organization_code

AND xxinn.operation_sequence_number = C_REC2.OPERATION_SEQUENCE_NUMBER;

commit;

END IF;

dbms_lock.sleep(60);

begin

select bos.operation_sequence_id

into l_operation_sequence_id from

apps.BOM_OPERATIONAL_ROUTINGS_V bor,

apps.BOM_OPERATION_SEQUENCES_V bos,

apps.org_organization_definitions ood,

apps.mtl_system_items_b msi

where msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID

and bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID

AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID

AND msi.ORGANIZATION_ID = ood.ORGANIZATION_ID

AND bor.ORGANIZATION_ID = ood.ORGANIZATION_ID

and msi.segment1 =C_REC.ASSEMBLY_ITEM_NAME

and ood.ORGANIZATION_code =C_REC.ORGANIZATION_CODE

and bos.OPERATION_SEQ_NUM =C_REC2.OPERATION_SEQUENCE_NUMBER;

end;

--DBMS_OUTPUT.PUT_LINE ('l_operation_sequence_id.' || l_operation_sequence_id );

FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_operation_sequence_id.' || l_operation_sequence_id );

j := 0;

-- FND_FILE.PUT_LINE (FND_FILE.LOG, 'before C1_RES Loop Start .....'||C_REC.ASSEMBLY_ITEM_NAME||'-'||C_REC.ORGANIZATION_CODE||'-'|| C_REC2.OPERATION_SEQUENCE_NUMBER);

FOR C_rec3 IN C1_RES (C_REC.ASSEMBLY_ITEM_NAME,

C_REC.ORGANIZATION_CODE,

C_REC2.OPERATION_SEQUENCE_NUMBER) LOOP

--DBMS_OUTPUT.PUT_LINE ('C1_RES Loop Start .....' );

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_RES Loop Start .....' );

P_RESOURCE_REC.DELETE;

--j := j + 1;

j:=1;

BEGIN

SELECT count(msi.segment1)

INTO l_hdritem_cnt

FROM apps.BOM_OPERATIONAL_ROUTINGS bor,

apps.mtl_system_items_b msi,

apps.org_organization_definitions ood

WHERE msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID

AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID

AND msi.organization_id = ood.organization_id

AND bor.organization_id = ood.organization_id

AND ood.organization_code = C_REC.ORGANIZATION_CODE

AND msi.segment1 = C_REC.ASSEMBLY_ITEM_NAME;

EXCEPTION

WHEN OTHERS

THEN

l_hdritem_cnt := 0;

END;

--DBMS_OUTPUT.PUT_LINE ('C1_RES Loop l_hdritem_cnt:' || l_hdritem_cnt);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_RES Loop l_hdritem_cnt:' || l_hdritem_cnt);

P_HEADER_REC := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;

IF l_hdritem_cnt < 1 then

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; -- 2 for manufacturing 1- for Engineering

--P_HEADER_REC.alternate_routing_code := 1;

--P_HEADER_REC.cfm_routing_flag :=1;

P_HEADER_REC.transaction_type := 'CREATE';

ELSE

P_HEADER_REC.ASSEMBLY_ITEM_NAME := NULL;

P_HEADER_REC.ORGANIZATION_CODE :=NULL;

P_HEADER_REC.Eng_Routing_Flag := NULL; -- 2 for manufacturing 1- for Engineering

P_HEADER_REC.transaction_type := NULL;

END IF;

FND_FILE.PUT_LINE (FND_FILE.LOG, ' j:' ||j);

FND_FILE.PUT_LINE (FND_FILE.LOG, ' P_HEADER_REC.Eng_Routing_Flag:' ||P_HEADER_REC.Eng_Routing_Flag);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC3.OPERATION_SEQUENCE_NUMBER:' ||C_REC3.OPERATION_SEQUENCE_NUMBER);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC3.RESOURCE_SEQUENCE_NUMBER:' ||C_REC3.RESOURCE_SEQUENCE_NUMBER);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC3.RESOURCE_CODE:' ||C_REC3.RESOURCE_CODE);

FND_FILE.PUT_LINE (FND_FILE.LOG, ' C_REC3.BASIS_TYPE:' || C_REC3.BASIS_TYPE);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC3.USAGE_RATE_OR_AMOUNT:' ||C_REC3.USAGE_RATE_OR_AMOUNT);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C_REC3.USAGE_RATE_OR_AMOUNT_INVERSE:' ||C_REC3.USAGE_RATE_OR_AMOUNT_INVERSE);

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;

DBMS_OUTPUT.PUT_LINE ('P_RESOURCE_REC(j).OPERATION_SEQUENCE_NUMBER:' ||P_RESOURCE_REC(j).OPERATION_SEQUENCE_NUMBER);

DBMS_OUTPUT.PUT_LINE ('P_RESOURCE_REC(j).RESOURCE_SEQUENCE_NUMBER:' ||P_RESOURCE_REC(j).RESOURCE_SEQUENCE_NUMBER);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_RESOURCE_REC(j).OPERATION_SEQUENCE_NUMBER:' ||P_RESOURCE_REC(j).OPERATION_SEQUENCE_NUMBER);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_RESOURCE_REC(j).RESOURCE_SEQUENCE_NUMBER:' ||P_RESOURCE_REC(j).RESOURCE_SEQUENCE_NUMBER);

if (C_REC3.BASIS_TYPE ='Lot' or C_REC3.BASIS_TYPE ='LOT') then

P_RESOURCE_REC(j).BASIS_TYPE := 2;

else

P_RESOURCE_REC(j).BASIS_TYPE := 1;

end if;

--DBMS_OUTPUT.PUT_LINE ('P_RESOURCE_REC(j).BASIS_TYPE :' ||P_RESOURCE_REC(j).BASIS_TYPE );

FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_RESOURCE_REC(j).BASIS_TYPE :' ||P_RESOURCE_REC(j).BASIS_TYPE );

P_RESOURCE_REC(j).USAGE_RATE_OR_AMOUNT := C_REC3.USAGE_RATE_OR_AMOUNT;

P_RESOURCE_REC(j).USAGE_RATE_OR_AMOUNT_INVERSE := C_REC3.USAGE_RATE_OR_AMOUNT_INVERSE;

P_RESOURCE_REC(j).Operation_Type := 1;

--P_RESOURCE_REC(j).Op_Start_Effective_Date := SYSDATE;

--P_RESOURCE_REC(j).Transaction_Type: = 'CREATE';

/*IF C_REC3.TRANSACTION_TYPE LIKE '%CREATE%'

THEN

P_RESOURCE_REC(j).Transaction_Type := 'CREATE'; -- l_rec.TRANSACTION_TYPE;--'CREATE';

FND_FILE.PUT_LINE ( FND_FILE.LOG, 'sss1.1- ' ||P_RESOURCE_REC(j).Transaction_Type);

ELSE

P_RESOURCE_REC(j).Transaction_Type := 'UPDATE';

FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.2- ' || P_RESOURCE_REC(j).Transaction_Type );

END IF;*/

IF C_REC2.TRANSACTION_TYPE LIKE '%CREATE%' THEN

P_RESOURCE_REC(j).Op_Start_Effective_Date := SYSDATE;

IF P_OPERATION_REC(i).STANDARD_OPERATION_CODE is not null and P_OPERATION_REC(i).DEPARTMENT_CODE is null then

--P_OPERATION_REC(i).transaction_type := 'UPDATE';

P_RESOURCE_REC(j).Transaction_Type := 'UPDATE';

/*IF j <= 1 then

P_OPERATION_REC(i).transaction_type := 'CREATE';

P_RESOURCE_REC(j).Transaction_Type := 'UPDATE';

else

P_OPERATION_REC(i).transaction_type := 'UPDATE';

P_RESOURCE_REC(j).Transaction_Type := 'UPDATE';

end if;*/

ELSIF P_OPERATION_REC(i).STANDARD_OPERATION_CODE is null and P_OPERATION_REC(i).DEPARTMENT_CODE is not null then

--P_OPERATION_REC(i).transaction_type := 'UPDATE';

P_RESOURCE_REC(j).Transaction_Type := 'CREATE';

end if;

ELSE

BEGIN

SELECT bos.EFFECTIVITY_DATE

INTO P_RESOURCE_REC(j).Op_Start_Effective_Date

FROM apps.BOM_OPERATIONAL_ROUTINGS_V bor,

apps.mtl_system_items_b msi,

apps.BOM_OPERATION_SEQUENCES_V bos,

apps.BOM_OPERATION_RESOURCES_V borv,

apps.org_organization_definitions ood

WHERE msi.inventory_item_id = bor.ASSEMBLY_ITEM_ID

AND msi.ORGANIZATION_ID = bor.ORGANIZATION_ID

AND msi.ORGANIZATION_ID = ood.ORGANIZATION_ID

AND bor.ORGANIZATION_ID = ood.ORGANIZATION_ID

AND bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID

AND bos.operation_sequence_id = borv.operation_sequence_id

AND ood.ORGANIZATION_CODE = C_REC3.ORGANIZATION_CODE

AND msi.segment1 = C_REC3.ASSEMBLY_ITEM_NAME

AND bos.OPERATION_SEQ_NUM =C_REC2.OPERATION_SEQUENCE_NUMBER

AND borv.RESOURCE_SEQ_NUM =C_REC3.RESOURCE_SEQUENCE_NUMBER;

EXCEPTION WHEN OTHERS THEN

P_RESOURCE_REC(j).Op_Start_Effective_Date := SYSDATE;

END;

--P_RESOURCE_REC(j).Transaction_Type := 'CREATE';

P_RESOURCE_REC(j).Transaction_Type := 'UPDATE';

END IF;

--FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.1- ' || P_OPERATION_REC(i).transaction_type );

FND_FILE.PUT_LINE (FND_FILE.LOG, 'sss1.2- ' ||P_RESOURCE_REC(j).Transaction_Type );

--DBMS_OUTPUT.PUT_LINE ('sss1.2- ' ||P_RESOURCE_REC(j).Transaction_Type );

P_RESOURCE_REC(j).Schedule_Flag := 2;

P_RESOURCE_REC(j).ASSIGNED_UNITS := C_REC3.ASSIGNED_UNITS;

BEGIN

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);

exception when others then

FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCEexception' ||sqlerrm);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCEP_RETURN_STATUSEXP' ||P_RETURN_STATUS);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCEP_MSG_COUNTEXP' ||P_MSG_COUNT);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCEl_ret_textEXP' ||l_ret_text);

DBMS_OUTPUT.PUT_LINE ('RESOURCEexception' ||sqlerrm);

DBMS_OUTPUT.PUT_LINE ('RESOURCEP_RETURN_STATUSEXP' ||P_RETURN_STATUS);

DBMS_OUTPUT.PUT_LINE ('RESOURCEP_MSG_COUNTEXP' ||P_MSG_COUNT);

DBMS_OUTPUT.PUT_LINE ('RESOURCEl_ret_textEXP' ||l_ret_text);

end;

IF P_RETURN_STATUS <> 'S' THEN

error_handler.Get_Message_List (p_message_list);

l_ret_text := null;

FOR i IN 1 .. p_message_list.COUNT LOOP

IF i = 1 THEN

--if l_ret_text is null then

DBMS_OUTPUT.PUT_LINE ('p_Message_List (i).message_text,' ||p_Message_List (i).message_text);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_Message_List (i).message_text,' ||p_Message_List (i).message_text);

l_ret_text := substr(p_Message_List (i).message_text,1,250);

ELSE

DBMS_OUTPUT.PUT_LINE ('p_Message_List (i).message_text else,' ||p_Message_List (i).message_text);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'p_Message_List (i).message_text else,' ||p_Message_List (i).message_text);

l_ret_text := l_ret_text || '~'|| substr(p_Message_List (i).message_text,1,250);

END IF;

END LOOP;

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);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCE_ERROR!!!!' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code||'-'||l_ret_text);

UPDATE APPS.XX_MAINTENANCE_ROUTES xxinn

SET XXINN.ERROR_MESSAGE = l_ret_text,

PROCESS_FLAG ='E'

WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name

AND XXINN.ORGANIZATION_CODE = c_rec.organization_code

AND xxinn.operation_sequence_number = C_REC2.OPERATION_SEQUENCE_NUMBER

and xxinn.RESOURCE_SEQUENCE_NUMBER = C_rec3.RESOURCE_SEQUENCE_NUMBER;

commit;

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 ..');

FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCE_The process is successful ..' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code);

UPDATE APPS.XX_MAINTAIN_ROUTES xxinn

SET --XXINN.ERROR_MESSAGE = null

PROCESS_FLAG ='S'

WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name

AND XXINN.ORGANIZATION_CODE = c_rec.organization_code

AND xxinn.operation_sequence_number = C_REC2.OPERATION_SEQUENCE_NUMBER

and xxinn.RESOURCE_SEQUENCE_NUMBER = C_rec3.RESOURCE_SEQUENCE_NUMBER;

commit;

END IF;

END LOOP; --C1_RES

DBMS_OUTPUT.PUT_LINE ('C1_RES END LOOP' );

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_RES END LOOP' );

END LOOP; -- C1_OPR

DBMS_OUTPUT.PUT_LINE ('C1_OPR END LOOP' );

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_OPR END LOOP' );

END LOOP; --C1_ENG

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_ENG END LOOP' );

FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_ENG END LOOP' );

EXCEPTION WHEN OTHERS THEN

FND_FILE.PUT_LINE (FND_FILE.LOG, 'Main_Eexception' ||sqlerrm);

END XX_MAINTENANCE_ROUTES_PRC;

/

Comments

Post a Comment

Popular posts from this blog

Oracle R12: A PL/SQL Script to Report Customer OAF Personalizations Instance-Wide

ORACLE R12 • OAF • PL/SQL Extract All OAF Personalizations Across Every Application in Oracle R12 A ready-to-run PL/SQL script that scans every installed application in your Oracle E-Business Suite R12 instance and reports only the customer-created OAF page personalizations — Oracle-seeded customizations are automatically filtered out. If you've ever tried to answer the question "what has actually been personalized on this instance?" , you already know the pain: Oracle Application Framework (OAF) personalizations are stored in MDS (Metadata Services), and by default a metadata dump mixes your team's custom personalizations together with Oracle's own seeded verticalization and translation layers . This script walks every installed application, lists every personalized document under its MDS path, and classifies each customizat...
Read more

Quick Guide: Activating Excel4apps Plugin in Excel for Oracle Reporting

Image
ORACLE • EXCEL • FINANCE Excel4apps: Bring Oracle Data Directly into Excel Learn how to enable the Excel4apps add-in and work with Oracle EBS or Oracle Fusion data directly from Microsoft Excel. What if you could query Oracle data, analyze financial information, drill down into transactions, and even upload journal entries — without constantly switching between Oracle and Excel? That's where Excel4apps comes in. It provides Excel-based add-ins such as GL Wand, FDM Anywhere, and Wand for Oracle EBS/Fusion , allowing finance and functional users to interact with Oracle data using the familiar Excel interface. 💡 The big advantage: Instead of manually exporting data from Oracle and then working on it in Excel, Excel4apps can provide a more direct and refreshable way of working with Oracle information from within Excel. 📌 In This...
Read more

From EBS to Fusion Cloud: 10 Things Every Oracle Technical Developer Should Know

ORACLE EBS → FUSION CLOUD From EBS to Fusion Cloud: 10 Things Every Oracle Technical Developer Should Know A practical guide for experienced Oracle EBS developers preparing for the next generation of Oracle Cloud applications. The Oracle technical landscape is changing. For years, Oracle E-Business Suite developers have built enterprise solutions using Forms, OAF, PL/SQL, Concurrent Programs, interface tables, Oracle Reports and database-driven customizations. With Oracle Fusion Cloud Applications, the architecture, development approach and customization strategy have evolved significantly. But does moving from EBS to Fusion mean starting your technical career from scratch? Absolutely not. Your EBS experience remains extremely valuable. The key is learning how to apply that experience in a cloud-first, API-driven and extension-oriented environment. "Don't leave your EBS exper...
Read more