CREATE TABLE APPS.GEPS_MAINTENANCE_ROUTES
(
ORGANIZATION_CODE VARCHAR2(3 BYTE),
ASSEMBLY_ITEM_NAME VARCHAR2(40 BYTE),
REVISION VARCHAR2(40 BYTE),
ROUTING_DATE VARCHAR2(100 BYTE),
OPERATION_SEQUENCE_NUMBER VARCHAR2(500 BYTE),
OPERATION_CODE VARCHAR2(500 BYTE),
REFERENCED VARCHAR2(40 BYTE),
DEPARTMENT_CODE VARCHAR2(10 BYTE),
RESOURCE_SEQUENCE_NUMBER NUMBER,
RESOURCE_CODE VARCHAR2(10 BYTE),
BASIS_TYPE VARCHAR2(10 BYTE),
USAGE_RATE_OR_AMOUNT NUMBER,
USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
ASSIGNED_UNITS NUMBER,
LONG_DESCRIPTION VARCHAR2(4000 BYTE),
OPERATION_DESCRIPTION VARCHAR2(4000 BYTE),
PROCESS_FLAG VARCHAR2(10 BYTE),
ERROR_MESSAGE VARCHAR2(4000 BYTE),
ERROR_MESSAGE_TYPE VARCHAR2(100 BYTE),
TRANSACTION_TYPE VARCHAR2(20 BYTE),
TRANS_FLAG VARCHAR2(100 BYTE)
)
CREATE OR REPLACE PROCEDURE APPS.GEPS_MAINTENANCE_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.GEPS_MAINTENANCE_ROUTES t
WHERE PROCESS_FLAG = 'P';
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.GEPS_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.GEPS_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
FND_FILE.PUT_LINE (FND_FILE.LOG,'Starting of GEPS_MAINTENANCE_ROUTES_PRC Procedure');
--FND_GLOBAL.apps_initialize (1001255, 50326, 700, 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);
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 (C_REC.ASSEMBLY_ITEM_NAME);
/*BEGIN
SELECT count(msi.segment1)
INTO l_hdritem_cnt
FROM BOM_OPERATIONAL_ROUTINGS bor,
mtl_system_items_b msi,
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;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_hdritem_cnt:' || l_hdritem_cnt);
P_HEADER_REC := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;*/
--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
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 BOM_OPERATIONAL_ROUTINGS bor,
mtl_system_items_b msi,
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;
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';
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;
/*IF i <= 1 then
P_OPERATION_REC (i).transaction_type := 'CREATE';
else
P_OPERATION_REC (i).transaction_type := 'UPDATE';
end if;*/
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 BOM_OPERATIONAL_ROUTINGS_V bor,
mtl_system_items_b msi,
BOM_OPERATION_SEQUENCES_V bos,
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 BOM_OPERATIONAL_ROUTINGS_V bor,
mtl_system_items_b msi,
BOM_OPERATION_SEQUENCES_V bos,
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 => FALSE,
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, '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;
--FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_RETURN_STATUS' ||P_RETURN_STATUS);
--FND_FILE.PUT_LINE (FND_FILE.LOG, 'P_MSG_COUNT' ||P_MSG_COUNT);
--FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_ret_text' ||l_ret_text);
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
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
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;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR!!!!' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code||'-'||l_ret_text);
UPDATE APPS.GEPS_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
FND_FILE.PUT_LINE (FND_FILE.LOG, 'The process is successful ..' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code);
UPDATE APPS.GEPS_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
BOM_OPERATIONAL_ROUTINGS_V bor,
BOM_OPERATION_SEQUENCES_V bos,
org_organization_definitions ood,
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;
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
FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_RES 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;*/
P_RESOURCE_REC.DELETE;
--j := j + 1;
j:=1;
BEGIN
SELECT count(msi.segment1)
INTO l_hdritem_cnt
FROM BOM_OPERATIONAL_ROUTINGS bor,
mtl_system_items_b msi,
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;
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;
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;
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';
/*IF j <= 1 then
P_OPERATION_REC (i).transaction_type := 'CREATE';
P_RESOURCE_REC (j) .Transaction_Type := 'CREATE';
else
P_OPERATION_REC (i).transaction_type := 'UPDATE';
P_RESOURCE_REC (j) .Transaction_Type := 'CREATE';
end if;*/
end if;
ELSE
BEGIN
SELECT bos.EFFECTIVITY_DATE
INTO P_RESOURCE_REC (j) .Op_Start_Effective_Date
FROM BOM_OPERATIONAL_ROUTINGS_V bor,
mtl_system_items_b msi,
BOM_OPERATION_SEQUENCES_V bos,
BOM_OPERATION_RESOURCES_V borv,
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 );
P_RESOURCE_REC (j) .Schedule_Flag := 1;
P_RESOURCE_REC (J) .ASSIGNED_UNITS := C_REC3.ASSIGNED_UNITS;
/*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;*/
BEGIN
APPS.BOM_RTG_PUB.PROCESS_RTG (p_init_msg_list => FALSE,
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);
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
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
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;
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);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'RESOURCE_ERROR!!!!' ||c_rec.assembly_item_name||'-'|| c_rec.organization_code||'-'||l_ret_text);
UPDATE APPS.GEPS_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.GEPS_MAINTENANCE_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
FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1_RES END LOOP' );
END LOOP; -- C1_OPR
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' );
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Main_Eexception' ||sqlerrm);
END GEPS_MAINTENANCE_ROUTES_PRC;
/
No comments:
Post a Comment