API to create Routing and assign resources

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;

/

 

Recent Posts