API used to create EAM Construction Estimation headers and lines creation.
This used in for creation any construction like building etc to estimate how much labour and how much cost of items in EAM
CREATE OR REPLACE PACKAGE APPS.MLGWEAM_ARCFM_CE_COST_PKG
IS
/*+-----------------------------------------------------------------------------------+
-- ================================== Record History =================================
---------------------------------------------------------------------------------------
--
--
-- Oracle Applications Release 12.1.3
--
-- Type : Interface
-- Reference : IN003 - MD050 - ArcFM to EAM WO Ready for Release - B
-- Description : Create Construction Estimate Header and Lines and to calculate Cost
-- Module Name : Enterprise Asset Management
-- Program Name : MLGWEAM_ARCFM_CE_COST_PKG
-- Schema Owner : APPS
-- Run environment : PL/SQL
--
+----------------------------------------------------------------------------------+ */
/*+-----------------------------------------------------------------------------------+
Procedure "CREATE_CE_LINES_PROC" to create CE and Lines and to calculate Cost
/*+-----------------------------------------------------------------------------------+*/
procedure CREATE_CE_LINES_PROC
(P_Errbuf OUT VARCHAR2,
P_Retcode OUT NUMBER,
p_estimate_number in varchar2
);
/*+-----------------------------------------------------------------------------------+
Declaring Global Variables.
/*+-----------------------------------------------------------------------------------+*/
g_count NUMBER := 1;
g_error_mesg VARCHAR2 (4000) := NULL;
g_error_log_flag VARCHAR2 (10) := 'N';
g_error_flag VARCHAR2 (1) := 'N';
g_org_id VARCHAR2 (4) := fnd_profile.VALUE ('ORG_ID');
g_request_id NUMBER := fnd_global.conc_request_id;
g_user_id NUMBER := fnd_profile.VALUE ('USER_ID');
g_resp_appl_id NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');
G_RESPONSIBILITY_ID NUMBER := FND_PROFILE.VALUE ('RESP_ID');
END MLGWEAM_ARCFM_CE_COST_PKG;
/
CREATE OR REPLACE PACKAGE BODY APPS.MLGWEAM_ARCFM_CE_COST_PKG
IS
/*+-----------------------------------------------------------------------------------+
-- ================================== Record History =================================
---------------------------------------------------------------------------------------
--
--
-- Oracle Applications Release 12.1.3
--
-- Type : Interface
-- Reference : IN003 - MD050 - ArcFM to EAM WO Ready for Release - B
-- Description : Create Construction Estimate Header and Lines and to calculate Cost
-- Module Name : Enterprise Asset Management
-- Program Name : MLGWEAM_ARCFM_CE_COST_PKG
-- Schema Owner : APPS
-- Run environment : PL/SQL
--
--
--
/*+-----------------------------------------------------------------------------------+
Procedure "CREATE_CE_LINES_PROC" to create CE and Lines and to calculate Cost
/*+-----------------------------------------------------------------------------------+*/
PROCEDURE CREATE_CE_LINES_PROC(
P_ERRBUF OUT VARCHAR2,
P_RETCODE OUT NUMBER,
P_ESTIMATE_NUMBER IN VARCHAR2 )
IS
X_EAM_CE_REC EAM_EST_DATASTRUCTURES_PUB.EAM_CONSTRUCTION_ESTIMATE_REC;
X_EAM_CE_UPDATE_REC EAM_EST_DATASTRUCTURES_PUB.EAM_CONSTRUCTION_ESTIMATE_REC;
X_EAM_CE_LINES_TBL EAM_EST_DATASTRUCTURES_PUB.EAM_CE_WORK_ORDER_LINES_TBL;
X_P_API_VERSION NUMBER;
X_P_INIT_MSG_LIST VARCHAR2 (100) := FND_API.G_FALSE;
X_P_COMMIT VARCHAR2 (100) := FND_API.G_FALSE;
X_RETURN_STATUS VARCHAR2 (1000) := NULL;
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2 (1000);
X_P_VALIDATION_LEVEL NUMBER;
X_ESTIMATE_ID_HEADER NUMBER;
X_WIP_ENTITY_ID NUMBER;
X_INVENTORY_ITEM_ID NUMBER;
X_RES_REQUIRED_QUANTITY NUMBER := 0;
X_RES_COST NUMBER := 0;
X_BOM_REQUIRED_QUANTITY NUMBER := 0;
X_BOM_COST NUMBER := 0;
X_ESTIMATE_ID NUMBER := NULL;
X_PROCESS_FLAG VARCHAR2 (1) := NULL;
X_ERROR_MESSAGE VARCHAR2 (2000);
X_SQL_ERR_FLAG VARCHAR2 (2) := 0;
X_TEST_CU_QTY NUMBER;
X_TEST_CU_SPAN_LENGTH NUMBER;
X_TEST_FIXED_HOURS NUMBER;
X_TEST_STD_HOURS NUMBER;
X_TEST_FIXED_QTY NUMBER;
X_TEST_VARIABLE_QTY NUMBER;
X_RESOURCE_MULTIFLIER NUMBER;
X_DIFFICULTY_ID NUMBER;
X_CU_DIFFICULTY VARCHAR2 (100);
X_CU_CONDITION VARCHAR2 (100);
X_CU_FUNCTION VARCHAR2 (100);
X_WORK_ORDER_TYPE VARCHAR2 (100);
X_LINES_WORK_ORDER_TYPE VARCHAR2 (100);
X_ESTIMATE_TYPE VARCHAR2 (100);
X_CU_TEMPORARY VARCHAR2 (100);
X_COUNT_CE NUMBER;
X_WIP_ACCT_CLASS VARCHAR2 (100);
X_ITEM_ID NUMBER;
X_ORGANIZATION_ID NUMBER;
X_ERR_COUNT NUMBER;
X_CE_PROCESS_FLAG VARCHAR2(10);
J NUMBER:= 0;
K NUMBER:= 0;
X_SRC_ITEM_ID NUMBER;
X_DUP_ESTIMATE_NUMBER VARCHAR2(30);
X_CE_DESCRIPTION VARCHAR2(240):= NULL;
X_CE_HEADER_WO_TYPE VARCHAR2(240):= NULL;
X_CE_LINES_WO_TYPE NUMBER := NULL;
X_PROJECT_ID NUMBER := NULL;
X_TASK_ID NUMBER := NULL;
X_MAINTENANCE_OBJECT_ID NUMBER := NULL;
X_MAINTENANCE_OBJECT_TYPE NUMBER := NULL;
X_OWNING_DEPARTMENT_ID NUMBER := NULL;
-------CE Line API Variables-------------
X_V_P_API_VERSION NUMBER;
X_V_P_INIT_MSG_LIST VARCHAR2 (100) := FND_API.G_FALSE;
X_V_P_COMMIT VARCHAR2 (100) := FND_API.G_FALSE;
X_V_X_RETURN_STATUS VARCHAR2 (1000);
X_V_X_MSG_COUNT NUMBER;
X_V_X_MSG_DATA VARCHAR2 (1000);
X_V_P_VALIDATION_LEVEL NUMBER;
I NUMBER := 1;
X_CE_UPDATE_DELETE VARCHAR2(100):= NULL;--addded by uday on 24 march 2014
X_LATEST_COUNT NUMBER;
X_START_DATE DATE;
x_COMPLETION_DATE DATE;
X_WO_WORK_ORDER_NUMBER VARCHAR2(40);
X_WO_WORK_ORDER_DESCRIPTION VARCHAR2(240);
X_WO_STATUS_TYPE NUMBER;
X_WO_ACCT_CLASS_CODE VARCHAR2(100);
X_WO_WORK_ORDER_SEQ_NUM NUMBER;
x_ce_processed_cnt number;
x_ce_succ_processed_cnt number;
x_ce_error_processed_cnt number;
CURSOR C_EAMMLGW_DISTINCT_CE
IS
SELECT ESTIMATE_NAME,
PARENT_WORK_ORDER ,
ESTIMATE_TYPE,
BATCH_ID
FROM MLGWEAM_CE_STG MCS
WHERE 1 = 1
AND PROCESS_FLAG = 'N'
AND ESTIMATE_NAME = NVL (P_ESTIMATE_NUMBER, ESTIMATE_NAME)
GROUP BY ESTIMATE_NAME,
PARENT_WORK_ORDER ,
ESTIMATE_TYPE,
BATCH_ID;
CURSOR C_EAMMLGW_CE_HEADER(P_ESTIMATE_NO VARCHAR2)
IS
SELECT MCS.*,
MCS.ROWID RID
FROM MLGWEAM_CE_STG MCS
WHERE 1 = 1
AND PROCESS_FLAG = 'N'
AND ESTIMATE_NAME = P_ESTIMATE_NO;
CURSOR C_EAMMLGW_CE_LINES (P_ESTIMATE_NUM VARCHAR2)
IS
SELECT MCS.*,
MCS.ROWID RID
FROM MLGWEAM_CE_STG MCS
WHERE 1 = 1
AND ESTIMATE_NAME = P_ESTIMATE_NUM
AND PROCESS_FLAG = 'V';
CURSOR C_EAMMLGW_CE_LINES_ATT(P_ESTIMATE_NUM VARCHAR2)
IS
SELECT MCS.URL,
MCS.ESTIMATE_NAME
FROM MLGWEAM_CE_STG MCS
WHERE 1 = 1
AND ESTIMATE_NAME = P_ESTIMATE_NUM
-- AND PROCESS_FLAG = 'S' --prashant 04-29
GROUP BY MCS.URL,
MCS.ESTIMATE_NAME;
CURSOR C_DELETE_WO_LINES (P_ESTIMATE_NUM VARCHAR2)
IS
SELECT ECWOL.*
FROM EAM_CONSTRUCTION_ESTIMATES ECE,
EAM_CE_WORK_ORDER_LINES ECWOL
WHERE 1 =1
AND ECE.ESTIMATE_ID = ECWOL.ESTIMATE_ID
AND ECE.ESTIMATE_NUMBER = P_ESTIMATE_NUM;
CURSOR C_EAMMLGW_RES_DATA (P_INVENTORY_ITEM_ID NUMBER)
IS
SELECT BOMOS.OPERATION_DESCRIPTION,
BOMOS.LONG_DESCRIPTION,
BOMD.DEPARTMENT_CODE,
BOMOS.DEPARTMENT_ID,
BOMOS.OPERATION_SEQ_NUM,
BOMRES.RESOURCE_ID,
BOMRES.RESOURCE_CODE,
BOMRES.UNIT_OF_MEASURE,
BOMORES.RESOURCE_SEQ_NUM,
BOMORES.SCHEDULE_FLAG,
BOMORES.ASSIGNED_UNITS,
BOMORES.USAGE_RATE_OR_AMOUNT,
BOMORES.BASIS_TYPE,
CRC.RESOURCE_RATE,
BOMOS.ATTRIBUTE8 OP_ATTRIBUTE8,
BOMOS.ATTRIBUTE9 OP_ATTRIBUTE9,
BOMOS.ATTRIBUTE10 OP_ATTRIBUTE10,
BOMOS.ATTRIBUTE11 OP_ATTRIBUTE11,
BOMOS.ATTRIBUTE12 OP_ATTRIBUTE12,
BOMOS.ATTRIBUTE13 OP_ATTRIBUTE13,
BOMOS.ATTRIBUTE14 OP_ATTRIBUTE14,
BOMOS.ATTRIBUTE15 OP_ATTRIBUTE15,
BOMORES.ATTRIBUTE8 RES_ATTRIBUTE8,
BOMORES.ATTRIBUTE9 RES_ATTRIBUTE9,
BOMORES.ATTRIBUTE10 RES_ATTRIBUTE10,
BOMORES.ATTRIBUTE11 RES_ATTRIBUTE11,
BOMORES.ATTRIBUTE12 RES_ATTRIBUTE12,
BOMORES.ATTRIBUTE13 RES_ATTRIBUTE13,
BOMORES.ATTRIBUTE14 RES_ATTRIBUTE14,
BOMORES.ATTRIBUTE15 RES_ATTRIBUTE15,
BOMOR.ATTRIBUTE1 CU_SEGMENT1, --PRASHANT
BOMOR.ATTRIBUTE11 PHASE_REQUIRED--PRASHANT
FROM BOM_OPERATIONAL_ROUTINGS BOMOR,
BOM_OPERATION_SEQUENCES BOMOS,
BOM_OPERATION_RESOURCES BOMORES,
BOM_RESOURCES BOMRES,
BOM_DEPARTMENTS BOMD,
MTL_SYSTEM_ITEMS_B MSIB,
CST_RESOURCE_COSTS CRC,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE 1 = 1
AND BOMOR.ASSEMBLY_ITEM_ID = P_INVENTORY_ITEM_ID
AND BOMOR.ASSEMBLY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND OOD.ORGANIZATION_CODE = 'LGW'
AND MSIB.ITEM_TYPE = 'AA'
AND BOMOR.ROUTING_SEQUENCE_ID = BOMOS.ROUTING_SEQUENCE_ID
AND BOMOS.OPERATION_SEQUENCE_ID = BOMORES.OPERATION_SEQUENCE_ID
AND BOMORES.RESOURCE_ID = BOMRES.RESOURCE_ID
AND BOMOS.DEPARTMENT_ID = BOMD.DEPARTMENT_ID
AND CRC.RESOURCE_ID = BOMRES.RESOURCE_ID
AND OOD.ORGANIZATION_ID = MSIB.ORGANIZATION_ID;
CURSOR C_EAMMLGW_BOM_DATA (P_INVENTORY_ITEM_ID NUMBER)
IS
SELECT MSIB.SEGMENT1,
MSIB.INVENTORY_ITEM_ID,
BBOM.BILL_SEQUENCE_ID,
BIC.COMPONENT_ITEM_ID,
BIC.COMPONENT_QUANTITY,
BIC.COMPONENT_SEQUENCE_ID,
(SELECT CIC.MATERIAL_COST
FROM CST_ITEM_COSTS CIC
WHERE 1 =1
AND CIC.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = CIC.ORGANIZATION_ID
) MATERIAL_COST,
(SELECT NVL(SUM(TRANSACTION_QUANTITY),0)
FROM MTL_ONHAND_QUANTITIES
WHERE 1 =1
AND INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
AND ORGANIZATION_ID = OOD.ORGANIZATION_ID
) ON_HAND_QTY,
BOMOS.OPERATION_DESCRIPTION,
BOMOS.LONG_DESCRIPTION,
BOMD.DEPARTMENT_CODE,
BOMOS.DEPARTMENT_ID,
BIC.OPERATION_SEQ_NUM,
BIC.ATTRIBUTE1 MAT_ATTRIBUTE1,
BIC.ATTRIBUTE2 MAT_ATTRIBUTE2,
BIC.ATTRIBUTE3 MAT_ATTRIBUTE3,
BIC.ATTRIBUTE4 MAT_ATTRIBUTE4,
BIC.ATTRIBUTE5 MAT_ATTRIBUTE5,
BIC.ATTRIBUTE6 MAT_ATTRIBUTE6,
BOMOS.ATTRIBUTE8 OP_MAT_ATTRIBUTE8,
BOMOS.ATTRIBUTE9 OP_MAT_ATTRIBUTE9,
BOMOS.ATTRIBUTE10 OP_MAT_ATTRIBUTE10,
BOMOS.ATTRIBUTE11 OP_MAT_ATTRIBUTE11,
BOMOS.ATTRIBUTE12 OP_MAT_ATTRIBUTE12,
BOMOS.ATTRIBUTE13 OP_MAT_ATTRIBUTE13,
BOMOS.ATTRIBUTE14 OP_MAT_ATTRIBUTE14,
BOMOS.ATTRIBUTE15 OP_MAT_ATTRIBUTE15,
BBOM.ASSEMBLY_ITEM_ID,
BIC.AUTO_REQUEST_MATERIAL,
BIC.UNIT_PRICE,
BOMOR.ATTRIBUTE1 CU_SEGMENT1, --PRASHANT
BOMOR.ATTRIBUTE11 PHASE_REQUIRED --PRASHANT
FROM BOM_BILL_OF_MATERIALS BBOM,
MTL_SYSTEM_ITEMS_B MSIB,
BOM_INVENTORY_COMPONENTS BIC,
--cst_item_costs cic,
BOM_OPERATIONAL_ROUTINGS BOMOR,
BOM_OPERATION_SEQUENCES BOMOS,
BOM_DEPARTMENTS BOMD,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE 1 = 1
AND BBOM.ASSEMBLY_ITEM_ID = P_INVENTORY_ITEM_ID
AND OOD.ORGANIZATION_CODE = 'LGW'
AND BBOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND MSIB.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
-- AND cic.inventory_item_id = msib.inventory_item_id
--AND msib.organization_id = cic.organization_id
AND BOMOR.ASSEMBLY_ITEM_ID = BBOM.ASSEMBLY_ITEM_ID
AND BOMOR.ROUTING_SEQUENCE_ID = BOMOS.ROUTING_SEQUENCE_ID
AND BOMOS.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM---added by uday on 05feb2014
AND BOMOS.DEPARTMENT_ID = BOMD.DEPARTMENT_ID
AND OOD.ORGANIZATION_ID = MSIB.ORGANIZATION_ID;
X_CE_LINES_VALID_FLAG VARCHAR2(1);
X_CE_API_VALID_FLAG VARCHAR2(1);
X_CE_DELETE_WITH_BATCH_ID varchar2(1);
BEGIN
FND_GLOBAL.APPS_INITIALIZE (G_USER_ID,
G_RESPONSIBILITY_ID,
G_RESP_APPL_ID );
FND_FILE.PUT_LINE (FND_FILE.log, 'User Id : ' || G_USER_ID);
FND_FILE.PUT_LINE (FND_FILE.log, 'Responsibility Id : ' || G_RESPONSIBILITY_ID );
FND_FILE.PUT_LINE (FND_FILE.log, 'Resp Application Id : ' || G_RESP_APPL_ID );
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request ID : ' || G_REQUEST_ID );
IF P_ESTIMATE_NUMBER IS NOT NULL
THEN
UPDATE MLGWEAM_CE_STG
SET PROCESS_FLAG = 'N'
WHERE ESTIMATE_NAME = P_ESTIMATE_NUMBER
AND PROCESS_FLAG = 'E';
COMMIT;
END IF;
---ADDED BY UDAY ON 12-MAY-2014
select count(1)
into x_ce_processed_cnt
from MLGWEAM_CE_STG
where ESTIMATE_NAME = NVL(P_ESTIMATE_NUMBER,ESTIMATE_NAME)
and PROCESS_FLAG = 'N';
--------------------------------------
-- Printing Summary Information
--------------------------------------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,LPAD('Records Summary Information',40));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,LPAD('----------------------------',40));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
fnd_file.put_line(fnd_file.output,'Total Number of Estimate to be Processed : '||x_ce_processed_cnt);
FOR CX_EAMMLGW_DISTINCT_CE IN C_EAMMLGW_DISTINCT_CE
LOOP
--/* prashant -- to delete the existing estimate records
BEGIN
DELETE FROM MLGWEAM_CE_STG
WHERE BATCH_ID <>
(SELECT MAX(BATCH_ID) FROM MLGWEAM_CE_STG
WHERE ESTIMATE_NAME=CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME
)
AND ESTIMATE_NAME=CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME
AND PROCESS_FLAG ='E';
COMMIT;
END;
--/* prashant -- to delete the existing estimate records
J := 0;
K := 0;
X_DUP_ESTIMATE_NUMBER := NULL;
X_PROCESS_FLAG := NULL;
X_LATEST_COUNT := NULL;
X_RETURN_STATUS := NULL;
X_ERROR_MESSAGE := NULL;
fnd_file.put_line(fnd_file.log,'Estimate Number : '||CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME);
BEGIN
SELECT ESTIMATE_NAME
INTO X_DUP_ESTIMATE_NUMBER
FROM MLGWEAM_CE_STG MCS
WHERE 1 = 1
AND PROCESS_FLAG = 'N'
AND ESTIMATE_NAME = CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME
GROUP BY ESTIMATE_NAME ,
PARENT_WORK_ORDER ,
ESTIMATE_TYPE ;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' No new records found in staging table to process for Estimate Number: ' || CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME||' - ');
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' No new records found in staging table to process for Estimate Number: ' || CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Estimate Number: ' || CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME||' has duplicate details of Parent Work Order or Estimate Type'||' - '||SQLERRM ||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Estimate Number: ' || CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME||' has duplicate details of Parent Work Order or Estimate Type' ||' - '||SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
X_ERR_COUNT := NULL;
X_CE_PROCESS_FLAG := NULL;
X_CE_LINES_VALID_FLAG := NULL;
IF X_PROCESS_FLAG IS NULL
THEN ---added by uday on 03april2014
FOR CX_EAMMLGW_CE_HEADER IN C_EAMMLGW_CE_HEADER(CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME)
LOOP
X_ESTIMATE_ID_HEADER := NULL;
X_WIP_ENTITY_ID := NULL;
X_WORK_ORDER_TYPE := NULL;
X_WIP_ENTITY_ID := NULL;
X_ESTIMATE_TYPE := NULL;
X_INVENTORY_ITEM_ID := NULL;
X_TEST_CU_QTY := NULL;
X_TEST_CU_SPAN_LENGTH := NULL;
X_RESOURCE_MULTIFLIER := NULL;
X_DIFFICULTY_ID := NULL;
X_CU_DIFFICULTY := NULL;
X_CU_CONDITION := NULL;
X_CU_FUNCTION := NULL;
X_LINES_WORK_ORDER_TYPE := NULL;
X_ERROR_MESSAGE := NULL;
X_SQL_ERR_FLAG := NULL;
X_CU_TEMPORARY := NULL;
X_COUNT_CE := NULL;
X_WIP_ACCT_CLASS := NULL;
X_ITEM_ID := NULL;
X_ORGANIZATION_ID := NULL;
X_CE_DESCRIPTION := NULL;
X_CE_HEADER_WO_TYPE := NULL;
X_CE_LINES_WO_TYPE := NULL;
X_PROJECT_ID := NULL;
X_TASK_ID := NULL;
X_MAINTENANCE_OBJECT_ID := NULL;
X_MAINTENANCE_OBJECT_TYPE := NULL;
X_OWNING_DEPARTMENT_ID := NULL;
X_CE_UPDATE_DELETE := NULL;--addded by uday on 24 march 2014
----added by uday on 16april2014
X_WO_WORK_ORDER_NUMBER := null;
X_WO_WORK_ORDER_DESCRIPTION:= null;
X_WO_STATUS_TYPE := NULL;
X_WO_ACCT_CLASS_CODE := null;
X_WO_WORK_ORDER_SEQ_NUM := NULL;
X_START_DATE := NULL;
X_COMPLETION_DATE := NULL;
/* code to validate on Estimate Name*/
BEGIN
SELECT ESTIMATE_ID
INTO X_ESTIMATE_ID_HEADER
FROM EAM_CONSTRUCTION_ESTIMATES
WHERE 1 = 1
AND ESTIMATE_NUMBER = CX_EAMMLGW_CE_HEADER.ESTIMATE_NAME;
/* added by uday on 24march2014 for update and delete wo lines and insert wo lines*/
IF CX_EAMMLGW_CE_HEADER.ESTIMATE_TYPE <> 'AS BUILT'
THEN
NULL;
X_CE_UPDATE_DELETE := 'Y';
BEGIN
IF CX_EAMMLGW_CE_HEADER.PARENT_WORK_ORDER IS NOT NULL
THEN
BEGIN
SELECT wip_entity_id
INTO x_wip_entity_id
FROM wip_entities
WHERE 1 = 1
AND wip_entity_name = CX_EAMMLGW_CE_HEADER.PARENT_WORK_ORDER;
/* added on 07022014 to extract asset description of parent work order
and insert as Construction Estimate Description*/
BEGIN
select cii.INSTANCE_DESCRIPTION
, ml.meaning
, ml.lookup_code
, wdj.project_id
, wdj.task_id
, wdj.MAINTENANCE_OBJECT_ID
, wdj.MAINTENANCE_OBJECT_TYPE
, WDJ.OWNING_DEPARTMENT
, WE.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, START_DATE
, COMPLETION_DATE
, we.DESCRIPTION
, wdj.STATUS_TYPE
, wdj.class_code
,'1' WORK_ORDER_SEQ_NUM
into x_ce_description
, x_ce_header_wo_type
, x_ce_lines_wo_type
, x_project_id
, x_task_id
, x_MAINTENANCE_OBJECT_ID
, x_MAINTENANCE_OBJECT_TYPE
, X_OWNING_DEPARTMENT_id
, X_WIP_ENTITY_ID
, X_WO_WORK_ORDER_NUMBER
, X_START_DATE
, x_COMPLETION_DATE
, X_WO_WORK_ORDER_DESCRIPTION
, X_WO_STATUS_TYPE
, X_WO_ACCT_CLASS_CODE
, X_WO_WORK_ORDER_SEQ_NUM
FROM CSI_ITEM_INSTANCES CII,
WIP_DISCRETE_JOBS WDJ,
WIP_EAM_WORK_ORDER_DTLS_V WE,
MFG_LOOKUPS ML
where 1=1
and cii.instance_id= wdj.MAINTENANCE_OBJECT_ID
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
and ml.lookup_code = wdj.work_order_type
AND ML.LOOKUP_TYPE = 'WIP_EAM_WORK_ORDER_TYPE'
and we.wip_entity_name = cx_eammlgw_ce_header.parent_work_order;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Error in getting work order header details and Asset Details for Parent work order: ' || CX_EAMMLGW_CE_HEADER.PARENT_WORK_ORDER||' - '||SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Error in getting work order header details and Asset Details for Parent work order: ' || CX_EAMMLGW_CE_HEADER.PARENT_WORK_ORDER||' - '||SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Parent Work Order: ' || CX_EAMMLGW_CE_HEADER.PARENT_WORK_ORDER ||' does not exist.'||' - '||SQLERRM||' - ');
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Parent Work Order: ' || CX_EAMMLGW_CE_HEADER.PARENT_WORK_ORDER ||' does not exist.'||' - '||SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
END IF;
END ;
BEGIN
SELECT MSIB.INVENTORY_ITEM_ID
INTO X_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B MSIB,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE 1 = 1
AND MSIB.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND OOD.ORGANIZATION_CODE = 'LGW'
AND MSIB.EAM_ITEM_TYPE = 2 ---for Asset Activity
AND MSIB.ITEM_TYPE = 'AA'
AND MSIB.SEGMENT1 = CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Acivity:'|| CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY ||' is not defined as Item - ' || SQLERRM||' - ');
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Acivity:'|| CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY ||' is not defined as Item - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
/*End code to validate Parent Work order*/
/* Not neccessary on header work order type as of now*/
/*
if cx_eammlgw_ce_header.header_work_order_type is not null then
BEGIN
SELECT ffv.flex_value
INTO x_work_order_type
FROM fnd_flex_value_sets ffvs, fnd_flex_values ffv
WHERE 1 = 1
AND ffvs.flex_value_set_name = 'MLGW_WORK_ORDER_TYPE'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value =
cx_eammlgw_ce_header.header_work_order_type;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error at Header Work Order Type'
|| cx_eammlgw_ce_header.header_work_order_type
);
x_process_flag := 'E';
x_error_message :=
x_error_message
|| 'Error at Heeader Work Order Type'
|| cx_eammlgw_ce_header.header_work_order_type;
x_sql_err_flag := 1;
p_retcode := 2;
END;
end if;
*/
/* End Not neccessary on header work order type as of now*/
/*
if cx_eammlgw_ce_header.estimate_type is not null then
BEGIN
SELECT ffv.flex_value
INTO x_estimate_type
FROM fnd_flex_value_sets ffvs, fnd_flex_values ffv
WHERE 1 = 1
AND ffvs.flex_value_set_name =
'MLGW_DESIGN_ESTIMATE_STATUS'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND upper(ffv.flex_value) = upper(cx_eammlgw_ce_header.estimate_type)
and ffv.end_date_active is null;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error at Estimate Type '
|| cx_eammlgw_ce_header.estimate_type
);
x_process_flag := 'E';
x_error_message :=
x_error_message
|| 'Error at Estmate Type'
|| cx_eammlgw_ce_header.estimate_type;
x_sql_err_flag := 1;
p_retcode := 2;
END;
end if;
*/
BEGIN
SELECT WCA.CLASS_CODE
INTO X_WIP_ACCT_CLASS
FROM WIP_ACCOUNTING_CLASSES WCA,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE 1 = 1
AND WCA.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND OOD.ORGANIZATION_CODE = 'LGW'
AND ((WCA.DISABLE_DATE IS NULL)
OR (WCA.DISABLE_DATE > SYSDATE) )
AND WCA.CLASS_CODE = 'LGWMTCE'
--cx_eammlgw_ce_header.wip_accounting_class
AND WCA.CLASS_TYPE = 6; --Maintenance
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Invalid WIP Accounting Class - ' || SQLERRM ||' - ');
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Invalid WIP Accounting Class - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
IF CX_EAMMLGW_CE_HEADER.CU_QUANTITY IS NOT NULL
THEN
--prashant
BEGIN
SELECT TO_NUMBER (CU_QUANTITY)
INTO X_TEST_CU_QTY
FROM MLGWEAM_CE_STG
WHERE 1 = 1
AND CU_QUANTITY = CX_EAMMLGW_CE_HEADER.CU_QUANTITY
AND ESTIMATE_NAME = CX_EAMMLGW_CE_HEADER.ESTIMATE_NAME
AND ROWID = CX_EAMMLGW_CE_HEADER.RID ;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU quantity should be number - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU quantity should be number - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
END IF;
IF CX_EAMMLGW_CE_HEADER.CU_SPAN_LENGTH IS NOT NULL
THEN
BEGIN
SELECT TO_NUMBER (CU_SPAN_LENGTH)
INTO X_TEST_CU_SPAN_LENGTH
FROM MLGWEAM_CE_STG
WHERE 1 = 1
AND CU_SPAN_LENGTH = CX_EAMMLGW_CE_HEADER.CU_SPAN_LENGTH
AND ESTIMATE_NAME = CX_EAMMLGW_CE_HEADER.ESTIMATE_NAME
AND ROWID = CX_EAMMLGW_CE_HEADER.RID ;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU Span Length should be number - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU Span Length should be number - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
END IF;
BEGIN
SELECT MSIB.INVENTORY_ITEM_ID,
OOD.ORGANIZATION_ID
INTO X_INVENTORY_ITEM_ID,
X_ORGANIZATION_ID
FROM BOM_OPERATIONAL_ROUTINGS BOMOR,
BOM_OPERATION_SEQUENCES BOMOS,
BOM_OPERATION_RESOURCES BOMORES,
BOM_RESOURCES BOMRES,
BOM_DEPARTMENTS BOMD,
MTL_SYSTEM_ITEMS_B MSIB,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE 1 = 1
AND MSIB.SEGMENT1 = CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY
AND BOMOR.ASSEMBLY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND OOD.ORGANIZATION_CODE = 'LGW'
AND MSIB.ITEM_TYPE = 'AA'
AND BOMOR.ROUTING_SEQUENCE_ID = BOMOS.ROUTING_SEQUENCE_ID
AND BOMOS.OPERATION_SEQUENCE_ID = BOMORES.OPERATION_SEQUENCE_ID
AND BOMORES.RESOURCE_ID = BOMRES.RESOURCE_ID
AND BOMOS.DEPARTMENT_ID = BOMD.DEPARTMENT_ID
GROUP BY MSIB.INVENTORY_ITEM_ID,
OOD.ORGANIZATION_ID ;
BEGIN --PRASHANT
FOR CX_EAMMLGW_RES_DATA IN C_EAMMLGW_RES_DATA (X_INVENTORY_ITEM_ID)
LOOP
X_TEST_STD_HOURS := null;
X_TEST_FIXED_HOURS := NULL;
BEGIN
SELECT TO_NUMBER (CX_EAMMLGW_RES_DATA.OP_ATTRIBUTE9)
INTO X_TEST_STD_HOURS
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU STD Man Hours(Extended) should be number - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU STD Man Hours(Extended) should be number - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
BEGIN
SELECT TO_NUMBER (CX_EAMMLGW_RES_DATA.OP_ATTRIBUTE10)
INTO X_TEST_FIXED_HOURS
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU Fixed Man Hours(Extended) should be number - '||CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY ||' - '|| SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU Fixed Man Hours(Extended) should be number - '||CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY ||' - '|| SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
END LOOP;
FOR CX_EAMMLGW_BOM_DATA IN C_EAMMLGW_BOM_DATA (X_INVENTORY_ITEM_ID)
LOOP
X_TEST_FIXED_QTY := NULL;
X_TEST_VARIABLE_QTY := NULL;
BEGIN
SELECT TO_NUMBER (CX_EAMMLGW_BOM_DATA.MAT_ATTRIBUTE3)
INTO X_TEST_FIXED_QTY
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU Fixed Quantity should be number - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU Fixed Quantity should be number - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
BEGIN
SELECT TO_NUMBER (CX_EAMMLGW_BOM_DATA.MAT_ATTRIBUTE4)
INTO X_TEST_VARIABLE_QTY
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU Variable Quantity should be number - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU Variable Quantity should be number - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
END LOOP;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE (FND_FILE.log, ' Activity either not defined or it does not have Resource -' || CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Activity either not defined or it does not have Resource -' || CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.log, ' Error at Activity - ' || CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY||' - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Error at Activity - ' || CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY||' - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
--/*--PRAHSANT FEB28
BEGIN
SELECT DIFFICULTY_RESOURCE_MULTIPLIER,
DIFFICULTY_ID
INTO X_RESOURCE_MULTIFLIER,
X_DIFFICULTY_ID
FROM EAM_DIFFICULTY_CODES
WHERE 1 = 1
AND DIFFICULTY_CODE = CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY
||' '
||CX_EAMMLGW_CE_HEADER.DIFFICULTY_CODES;
EXCEPTION
WHEN NO_DATA_FOUND THEN
X_RESOURCE_MULTIFLIER := 1;
X_DIFFICULTY_ID := NULL;
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Error at Difficulty Code - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Error at Difficulty Code - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
--*/--PRASHANT FEB28
IF CX_EAMMLGW_CE_HEADER.CU_FUNCTION IS NULL
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU Function can not be null - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU Function can not be null - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
ELSE
BEGIN
SELECT FFV.FLEX_VALUE
INTO X_CU_FUNCTION
FROM FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_VALUES FFV
WHERE 1 = 1
AND FFVS.FLEX_VALUE_SET_NAME = 'CU Function'
AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
AND UPPER(FFV.FLEX_VALUE) = UPPER(CX_EAMMLGW_CE_HEADER.CU_FUNCTION);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Invalid CU Function - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Invalid CU Function - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
END IF;
-- PRASHANT FEB28
-- /*
IF CX_EAMMLGW_CE_HEADER.CONDITION IS NOT NULL
THEN
BEGIN
SELECT FFV.FLEX_VALUE
INTO X_CU_CONDITION
FROM FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_VALUES FFV
WHERE 1 = 1
AND FFVS.FLEX_VALUE_SET_NAME = 'CU Condition'
AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
AND UPPER(FFV.FLEX_VALUE) = UPPER(CX_EAMMLGW_CE_HEADER.CONDITION);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Invalid CU Condition - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Invalid CU Condition - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
END IF;
-- */
/* --start PRASHANT FEB 28
if cx_eammlgw_ce_header.difficulty is not null then
BEGIN
SELECT ffv.flex_value
INTO x_cu_difficulty
FROM fnd_flex_value_sets ffvs, fnd_flex_values ffv
WHERE 1 = 1
AND ffvs.flex_value_set_name = 'CU Difficulty'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND upper(ffv.flex_value) = upper(cx_eammlgw_ce_header.difficulty);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Invalid CU Difficutly - ' || SQLERRM
);
x_process_flag := 'E';
x_error_message :=
x_error_message
|| 'Invalid CU Difficutly - '
|| SQLERRM;
x_sql_err_flag := 1;
p_retcode := 2;
END;
END IF;
*/--end PRASHANT FEB28
/* Not neccessary on lines work order type as of now*/
/* if cx_eammlgw_ce_header.work_order_type is not null then
BEGIN
SELECT lookup_code
INTO x_lines_work_order_type
FROM apps.fnd_lookup_values_vl
WHERE 1 = 1
AND lookup_type = 'WIP_EAM_WORK_ORDER_TYPE'
AND lookup_code = cx_eammlgw_ce_header.work_order_type;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error at lines Work Order Type'
|| cx_eammlgw_ce_header.work_order_type
);
x_process_flag := 'E';
x_error_message :=
x_error_message
|| 'Error at lines Work Order Type'
|| cx_eammlgw_ce_header.work_order_type;
x_sql_err_flag := 1;
p_retcode := 2;
END;
end if;*/
/* end Not neccessary on lines work order type as of now*/
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Cannot update the existing Estimate Number ' || CX_EAMMLGW_CE_HEADER.ESTIMATE_NAME||', as the Estimate type is '||CX_EAMMLGW_CE_HEADER.ESTIMATE_TYPE||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Cannot update the existing Estimate Number ' || CX_EAMMLGW_CE_HEADER.ESTIMATE_NAME||', as the Estimate type is '||CX_EAMMLGW_CE_HEADER.ESTIMATE_TYPE||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END IF;
/* end added by uday on 24march2014 for update and delete wo lines and insert wo lines*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
x_ce_update_delete := 'N';
/* code to validate Parent Work order*/
IF cx_eammlgw_ce_header.parent_work_order IS NOT NULL
THEN
BEGIN
SELECT wip_entity_id
INTO x_wip_entity_id
FROM wip_entities
WHERE 1 = 1
AND wip_entity_name = cx_eammlgw_ce_header.parent_work_order;
/* added on 07022014 to extract asset description of parent work order
and insert as Construction Estimate Description*/
begin
select cii.INSTANCE_DESCRIPTION
, ml.meaning
, ml.lookup_code
, wdj.project_id
, wdj.task_id
, wdj.MAINTENANCE_OBJECT_ID
, wdj.MAINTENANCE_OBJECT_TYPE
, WDJ.OWNING_DEPARTMENT
, WE.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, START_DATE
, COMPLETION_DATE
, we.DESCRIPTION
, wdj.STATUS_TYPE
, wdj.class_code
,'1' WORK_ORDER_SEQ_NUM
into x_ce_description
, x_ce_header_wo_type
, x_ce_lines_wo_type
, x_project_id
, x_task_id
, x_MAINTENANCE_OBJECT_ID
, x_MAINTENANCE_OBJECT_TYPE
, X_OWNING_DEPARTMENT_id
, X_WIP_ENTITY_ID
, X_WO_WORK_ORDER_NUMBER
, X_START_DATE
, x_COMPLETION_DATE
, X_WO_WORK_ORDER_DESCRIPTION
, X_WO_STATUS_TYPE
, X_WO_ACCT_CLASS_CODE
, X_WO_WORK_ORDER_SEQ_NUM
FROM CSI_ITEM_INSTANCES CII,
wip_discrete_jobs wdj,
WIP_EAM_WORK_ORDER_DTLS_V WE,
mfg_lookups ml
where 1=1
and cii.instance_id= wdj.MAINTENANCE_OBJECT_ID
and we.wip_entity_id = wdj.wip_entity_id
and ml.lookup_code = wdj.work_order_type
AND ml.lookup_type = 'WIP_EAM_WORK_ORDER_TYPE'
and we.wip_entity_name = cx_eammlgw_ce_header.parent_work_order;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Error in getting work order header details and Asset Details for Parent work order: ' || CX_EAMMLGW_CE_HEADER.PARENT_WORK_ORDER||', - '||SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Error in getting work order header details and Asset Details for Parent work order: ' || CX_EAMMLGW_CE_HEADER.PARENT_WORK_ORDER||', - '||SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Parent Work Order: ' || CX_EAMMLGW_CE_HEADER.PARENT_WORK_ORDER ||' does not exist.'||', - '||SQLERRM||' - ');
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || 'Parent Work Order: ' || CX_EAMMLGW_CE_HEADER.PARENT_WORK_ORDER ||' does not exist.'||', - '||SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
END IF;
BEGIN
SELECT msib.inventory_item_id
INTO x_item_id
FROM mtl_system_items_b msib,
org_organization_definitions ood
WHERE 1 = 1
AND msib.organization_id = ood.organization_id
AND ood.organization_code = 'LGW'
AND msib.eam_item_type = 2 ---for Asset Activity
AND msib.item_type = 'AA'
AND msib.segment1 = cx_eammlgw_ce_header.CU_CODE_OR_ACTIVITY;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Acivity:'|| CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY ||' is not defined as Item - ' || SQLERRM||' - ');
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Acivity:'|| CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY ||' is not defined as Item - ' || SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
/*End code to validate Parent Work order*/
/* Not neccessary on header work order type as of now*/
/*
if cx_eammlgw_ce_header.header_work_order_type is not null then
BEGIN
SELECT ffv.flex_value
INTO x_work_order_type
FROM fnd_flex_value_sets ffvs, fnd_flex_values ffv
WHERE 1 = 1
AND ffvs.flex_value_set_name = 'MLGW_WORK_ORDER_TYPE'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value =
cx_eammlgw_ce_header.header_work_order_type;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error at Header Work Order Type'
|| cx_eammlgw_ce_header.header_work_order_type
);
x_process_flag := 'E';
x_error_message :=
x_error_message
|| 'Error at Heeader Work Order Type'
|| cx_eammlgw_ce_header.header_work_order_type;
x_sql_err_flag := 1;
p_retcode := 2;
END;
end if;
*/
/* End Not neccessary on header work order type as of now*/
/*
if cx_eammlgw_ce_header.estimate_type is not null then
BEGIN
SELECT ffv.flex_value
INTO x_estimate_type
FROM fnd_flex_value_sets ffvs, fnd_flex_values ffv
WHERE 1 = 1
AND ffvs.flex_value_set_name =
'MLGW_DESIGN_ESTIMATE_STATUS'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND upper(ffv.flex_value) = upper(cx_eammlgw_ce_header.estimate_type)
and ffv.end_date_active is null;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error at Estimate Type '
|| cx_eammlgw_ce_header.estimate_type
);
x_process_flag := 'E';
x_error_message :=
x_error_message
|| 'Error at Estmate Type'
|| cx_eammlgw_ce_header.estimate_type;
x_sql_err_flag := 1;
p_retcode := 2;
END;
end if;
*/
BEGIN
SELECT wca.class_code
INTO x_wip_acct_class
FROM wip_accounting_classes wca,
org_organization_definitions ood
WHERE 1 =1
AND wca.organization_id = ood.organization_id
AND ood.organization_code = 'LGW'
AND ((wca.disable_date IS NULL)
OR (wca.disable_date > SYSDATE) )
AND wca.class_code ='LGWMTCE'
--cx_eammlgw_ce_header.wip_accounting_class
AND wca.class_type = 6; --Maintenance
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Invalid WIP Accounting Class - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Invalid WIP Accounting Class - ' || SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
END;
IF CX_EAMMLGW_CE_HEADER.CU_QUANTITY IS NOT NULL
THEN
--prashant
BEGIN
SELECT TO_NUMBER (cu_quantity)
INTO x_test_cu_qty
FROM mlgweam_ce_stg
WHERE 1 = 1
AND cu_quantity = cx_eammlgw_ce_header.cu_quantity
AND estimate_name = cx_eammlgw_ce_header.estimate_name
AND rowid =cx_eammlgw_ce_header.rid ;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU quantity should be number - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU quantity should be number - ' || SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
END;
--prashant
END IF;
IF CX_EAMMLGW_CE_HEADER.CU_SPAN_LENGTH IS NOT NULL
THEN
BEGIN
SELECT TO_NUMBER (cu_span_length)
INTO x_test_cu_span_length
FROM mlgweam_ce_stg
WHERE 1 = 1
AND cu_span_length = cx_eammlgw_ce_header.cu_span_length
AND estimate_name = cx_eammlgw_ce_header.estimate_name
AND rowid =cx_eammlgw_ce_header.rid ;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU Span Length should be number - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU Span Length should be number - ' || SQLERRM||' - ';
x_sql_err_flag := 1;
P_RETCODE := 2;
END;
END IF;
BEGIN
SELECT msib.inventory_item_id,
ood.organization_id
INTO x_inventory_item_id,
x_organization_id
FROM bom_operational_routings bomor,
bom_operation_sequences bomos,
bom_operation_resources bomores,
bom_resources bomres,
bom_departments bomd,
mtl_system_items_b msib,
org_organization_definitions ood
WHERE 1 = 1
AND msib.segment1 = cx_eammlgw_ce_header.cu_code_or_activity
--'PIN PLT 2 ED'
AND bomor.assembly_item_id = msib.inventory_item_id
AND msib.organization_id = ood.organization_id
AND ood.organization_code = 'LGW'
AND msib.item_type = 'AA'
AND bomor.routing_sequence_id = bomos.routing_sequence_id
AND bomos.operation_sequence_id = bomores.operation_sequence_id
AND bomores.resource_id = bomres.resource_id
AND bomos.department_id = bomd.department_id
GROUP BY msib.inventory_item_id,
OOD.ORGANIZATION_ID ;
FOR CX_EAMMLGW_RES_DATA IN C_EAMMLGW_RES_DATA (X_INVENTORY_ITEM_ID)
LOOP
x_test_std_hours := NULL;
x_test_fixed_hours := NULL;
BEGIN
SELECT TO_NUMBER (cx_eammlgw_res_data.op_attribute9)
INTO x_test_std_hours
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU STD Man Hours(Extended) should be number - ' || SQLERRM||' - ' );
x_process_flag := 'E';
x_error_message := x_error_message || ' CU STD Man Hours(Extended) should be number - ' || SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
END;
BEGIN
SELECT TO_NUMBER (cx_eammlgw_res_data.op_attribute10)
INTO x_test_fixed_hours
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU Fixed Man Hours(Extended) should be number - '||CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU Fixed Man Hours(Extended) should be number - '||CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY || SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
END;
END LOOP;
FOR cx_eammlgw_bom_data IN c_eammlgw_bom_data (x_inventory_item_id)
LOOP
x_test_fixed_qty := NULL;
x_test_variable_qty := NULL;
BEGIN
SELECT TO_NUMBER (cx_eammlgw_bom_data.mat_attribute3)
INTO x_test_fixed_qty
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU Fixed Quantity should be number - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU Fixed Quantity should be number - ' || SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
END;
BEGIN
SELECT TO_NUMBER (cx_eammlgw_bom_data.mat_attribute4)
INTO x_test_variable_qty
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' CU Variable Quantity should be number - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' CU Variable Quantity should be number - ' || SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
END;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE (FND_FILE.log, ' Activity either not defined or it does not have Resource -' || CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY ||' - ');
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Activity either not defined or it does not have Resource -' || CX_EAMMLGW_CE_HEADER.CU_CODE_OR_ACTIVITY||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.log, 'Error at Activity - ' || SQLERRM );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || 'Error at Activity - ' || SQLERRM;
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
--/*--PRAHSANT FEB28
BEGIN
SELECT difficulty_resource_multiplier,
difficulty_id
INTO x_resource_multiflier,
x_difficulty_id
FROM eam_difficulty_codes
WHERE 1 = 1
AND DIFFICULTY_CODE = cx_eammlgw_ce_header.cu_code_or_activity
||' '
||cx_eammlgw_ce_header.DIFFICULTY_CODES;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_resource_multiflier := 1;
x_difficulty_id := NULL;
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Error at Difficulty Code - ' || SQLERRM||' - ' );
x_process_flag := 'E';
x_error_message := X_ERROR_MESSAGE || ' Error at Difficulty Code - ' || SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
END;
--*/--PRASHANT FEB28
IF cx_eammlgw_ce_header.cu_function IS NULL THEN
fnd_file.put_line (fnd_file.LOG, ' CU Function can not be null - ' );
x_process_flag := 'E';
x_error_message := x_error_message || ' CU Function can not be null - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
ELSE
BEGIN
SELECT ffv.flex_value
INTO x_cu_function
FROM fnd_flex_value_sets ffvs,
fnd_flex_values ffv
WHERE 1 = 1
AND ffvs.flex_value_set_name = 'CU Function'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND upper(ffv.flex_value) = upper(cx_eammlgw_ce_header.cu_function);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Invalid CU Function - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Invalid CU Function - ' || SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
END;
END IF;
-- PRASHANT FEB28
-- /*
IF CX_EAMMLGW_CE_HEADER.CONDITION IS NOT NULL
THEN
BEGIN
SELECT ffv.flex_value
INTO x_cu_condition
FROM fnd_flex_value_sets ffvs,
fnd_flex_values ffv
WHERE 1 = 1
AND ffvs.flex_value_set_name = 'CU Condition'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND upper(ffv.flex_value) = upper(cx_eammlgw_ce_header.condition);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Invalid CU Condition - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
x_error_message := X_ERROR_MESSAGE || ' Invalid CU Condition - ' || SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
END;
END IF;
-- */
/* --start PRASHANT FEB 28
if cx_eammlgw_ce_header.difficulty is not null then
BEGIN
SELECT ffv.flex_value
INTO x_cu_difficulty
FROM fnd_flex_value_sets ffvs, fnd_flex_values ffv
WHERE 1 = 1
AND ffvs.flex_value_set_name = 'CU Difficulty'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND upper(ffv.flex_value) = upper(cx_eammlgw_ce_header.difficulty);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Invalid CU Difficutly - ' || SQLERRM
);
x_process_flag := 'E';
x_error_message :=
x_error_message
|| 'Invalid CU Difficutly - '
|| SQLERRM;
x_sql_err_flag := 1;
p_retcode := 2;
END;
END IF;
*/--end PRASHANT FEB28
/* Not neccessary on lines work order type as of now*/
/* if cx_eammlgw_ce_header.work_order_type is not null then
BEGIN
SELECT lookup_code
INTO x_lines_work_order_type
FROM apps.fnd_lookup_values_vl
WHERE 1 = 1
AND lookup_type = 'WIP_EAM_WORK_ORDER_TYPE'
AND lookup_code = cx_eammlgw_ce_header.work_order_type;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error at lines Work Order Type'
|| cx_eammlgw_ce_header.work_order_type
);
x_process_flag := 'E';
x_error_message :=
x_error_message
|| 'Error at lines Work Order Type'
|| cx_eammlgw_ce_header.work_order_type;
x_sql_err_flag := 1;
p_retcode := 2;
END;
end if;*/
/* end Not neccessary on lines work order type as of now*/
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.log, ' Error in finding the existence of Estimate Number - ' ||CX_EAMMLGW_CE_HEADER.ESTIMATE_NAME||' - '|| SQLERRM ||' - ');
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' Error in finding the existence of Estimate Number - ' ||CX_EAMMLGW_CE_HEADER.ESTIMATE_NAME||' - '|| SQLERRM||' - ';
X_SQL_ERR_FLAG := 1;
P_RETCODE := 2;
END;
/* added by uday on 28 march*/ --04-29 prashant
-- IF X_PROCESS_FLAG = 'E'
-- THEN
-- x_ce_lines_valid_flag := 'N';--if any ce lines validation record is fail
-- END IF;
/**/
FND_FILE.PUT_LINE(FND_FILE.LOG,'***************************************************************');
END LOOP; --end of header
-- END IF; --04-03
/* added by uday on 28 march*/
IF X_PROCESS_FLAG = 'E'
THEN
UPDATE mlgweam_ce_stg
SET process_flag = 'E'
WHERE 1 = 1
AND estimate_name = cx_eammlgw_distinct_ce.estimate_name;
ELSIF X_PROCESS_FLAG IS NULL
THEN
UPDATE mlgweam_ce_stg
SET process_flag = 'V'
WHERE 1 = 1
AND estimate_name = cx_eammlgw_distinct_ce.estimate_name;
END IF;
/* end added by uday on 28 march*/
COMMIT;
BEGIN
X_RETURN_STATUS := NULL;
X_CE_API_VALID_FLAG := NULL;
IF X_PROCESS_FLAG IS NULL AND X_CE_UPDATE_DELETE = 'N'
THEN ---if all the records are value
x_eam_ce_rec.estimate_id := NULL;
x_eam_ce_rec.organization_id := x_organization_id;
x_eam_ce_rec.estimate_number := cx_eammlgw_distinct_ce.estimate_name;
x_eam_ce_rec.estimate_description := x_ce_description;
x_eam_ce_rec.grouping_option := NULL;
x_eam_ce_rec.parent_wo_id := x_wip_entity_id;
x_eam_ce_rec.create_parent_wo_flag := 'N';
x_eam_ce_rec.attribute1 := cx_eammlgw_distinct_ce.estimate_type;--x_estimate_type;
x_eam_ce_rec.attribute2 := x_ce_header_wo_type;
x_eam_ce_rec.attribute3 := NULL;
x_eam_ce_rec.attribute4 := NULL;
x_eam_ce_rec.attribute5 := NULL;
x_eam_ce_rec.attribute6 := NULL;
x_eam_ce_rec.attribute7 := NULL;
x_eam_ce_rec.attribute8 := NULL;
x_eam_ce_rec.attribute9 := NULL;
x_eam_ce_rec.attribute10 := NULL;
x_eam_ce_rec.attribute11 := NULL;
x_eam_ce_rec.attribute12 := NULL;
X_EAM_CE_REC.ATTRIBUTE13 := NULL;
X_EAM_CE_REC.ATTRIBUTE14 := NULL;
X_EAM_CE_REC.ATTRIBUTE15 := NULL;
EAM_CONSTRUCTION_EST_PUB.CREATE_ESTIMATE
(p_api_version => 1,
p_commit => x_p_commit,
p_init_msg_list => x_p_init_msg_list,
p_validation_level => 1,
px_estimate_rec => x_eam_ce_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
--commit;
IF X_RETURN_STATUS != 'S'
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' API Error in Creating the Estimate - '||CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME||' - '||x_msg_data);
x_process_flag := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || ' API Error in Creating the Estimate - '||CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME||' - '||x_msg_data;
x_sql_err_flag := 1;
p_retcode := 2;
x_ce_api_valid_flag := 'N';
END IF;
/* added by uday on 24march2014*/
ELSIF X_PROCESS_FLAG IS NULL AND X_CE_UPDATE_DELETE = 'Y'
THEN
NULL;
x_eam_ce_update_rec.estimate_id := x_estimate_id_header;
x_eam_ce_update_rec.organization_id := x_organization_id;
x_eam_ce_update_rec.estimate_number := cx_eammlgw_distinct_ce.estimate_name;
x_eam_ce_update_rec.estimate_description := x_ce_description;
X_EAM_CE_UPDATE_REC.PARENT_WO_ID := X_WIP_ENTITY_ID;
X_EAM_CE_UPDATE_REC.ATTRIBUTE1 := CX_EAMMLGW_DISTINCT_CE.ESTIMATE_TYPE;--x_estimate_type;
X_EAM_CE_UPDATE_REC.ATTRIBUTE2 := X_CE_HEADER_WO_TYPE;
EAM_CONSTRUCTION_EST_PUB.UPDATE_ESTIMATE(
p_api_version => 1,
p_commit => x_p_commit ,
p_init_msg_list => x_p_init_msg_list,
p_validation_level => 1,
p_estimate_rec => x_eam_ce_update_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
--commit;
IF X_RETURN_STATUS != 'S'
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Update Estimate API Error - ' ||x_msg_data);
X_PROCESS_FLAG := 'E';
X_ERROR_MESSAGE := X_ERROR_MESSAGE || 'API Error in Update Estimate process - - '||x_msg_data;
x_sql_err_flag := 1;
p_retcode := 2;
x_ce_api_valid_flag := 'N';
-- END IF;
ELSE
-- IF X_RETURN_STATUS = 'S'
-- THEN
FOR cx_delete_wo_lines IN c_delete_wo_lines(cx_eammlgw_distinct_ce.estimate_name)
LOOP
EAM_CONSTRUCTION_EST_PVT.DELETE_WO_LINE(
p_api_version => 1
, p_init_msg_list => x_p_init_msg_list
, p_commit => x_p_commit
, p_work_order_line_id => cx_delete_wo_lines.ESTIMATE_WORK_ORDER_LINE_ID
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
IF X_RETURN_STATUS != 'S'
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' Delete Estimate lines API Error - '||x_msg_data );
x_process_flag := 'E';
X_ERROR_MESSAGE := x_error_message || 'Error in Delete Estimate lines API process - - '||x_msg_data;
x_sql_err_flag := 1;
p_retcode := 2;
X_CE_API_VALID_FLAG := 'N';
ELSE
fnd_attached_documents2_pkg.delete_attachments
(X_entity_name =>'EAM_CONSTRUCTION_ESTIMATES',
X_pk1_value => TO_CHAR(x_estimate_id_header),
X_pk2_value => NULL ,
X_pk3_value => NULL ,
X_pk4_value => NULL ,
X_pk5_value => NULL ,
X_delete_document_flag => 'Y' ,
X_AUTOMATICALLY_ADDED_FLAG => NULL);
--end loop;
END IF;
END LOOP;
END IF;
END IF;
--------------------API to Insert CE lines-------------------
IF x_return_status = 'S' AND x_ce_api_valid_flag IS NULL ---- AND x_process_flag = 'N'
THEN
FOR cx_eammlgw_ce_lines IN c_eammlgw_ce_lines (cx_eammlgw_distinct_ce.estimate_name)
LOOP
K := K+10;
x_estimate_id := NULL;
x_cu_temporary := NULL;
x_resource_multiflier := NULL;
x_difficulty_id := NULL;
x_src_item_id := NULL;
IF CX_EAMMLGW_CE_LINES.CU_QUANTITY IS NULL
THEN
CX_EAMMLGW_CE_LINES.CU_QUANTITY := '1';
END IF;
IF CX_EAMMLGW_CE_LINES.CU_SPAN_LENGTH IS NULL
THEN
cx_eammlgw_ce_lines.cu_span_length := '1';
END IF;
--PRASHANT
-- IF cx_eammlgw_ce_lines.cu_temporary IS NULL
-- THEN
-- x_cu_temporary := 'N';
-- END IF;
BEGIN
SELECT difficulty_resource_multiplier,
difficulty_id
INTO x_resource_multiflier,
x_difficulty_id
FROM eam_difficulty_codes
WHERE 1 = 1
AND DIFFICULTY_CODE = cx_eammlgw_ce_lines.cu_code_or_activity
||' '
||cx_eammlgw_ce_lines.DIFFICULTY_CODES;
EXCEPTION
WHEN NO_DATA_FOUND THEN
x_resource_multiflier := 1;
x_difficulty_id := NULL;
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error At Difficulty Code selection with activity' || cx_eammlgw_ce_lines.cu_code_or_activity||' '||cx_eammlgw_ce_lines.DIFFICULTY_CODES||' - '||SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
x_error_message := x_error_message || 'Error At Difficulty Code selection with activity' || cx_eammlgw_ce_lines.cu_code_or_activity||' '||cx_eammlgw_ce_lines.DIFFICULTY_CODES||' - '||SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
x_ce_api_valid_flag := 'N';
END;
BEGIN
SELECT msib.inventory_item_id
INTO x_src_item_id
FROM mtl_system_items_b msib,
org_organization_definitions ood
WHERE 1 = 1
AND msib.organization_id = ood.organization_id
AND ood.organization_code = 'LGW'
AND msib.eam_item_type = 2 ---for Asset Activity
AND msib.item_type = 'AA'
AND msib.segment1 = cx_eammlgw_ce_lines.CU_CODE_OR_ACTIVITY;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Acivity is not defined as Item for lines API' || cx_eammlgw_ce_lines.CU_CODE_OR_ACTIVITY||' - '||SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
x_error_message := x_error_message || 'Acivity is not defined as Item for line API' || cx_eammlgw_ce_lines.CU_CODE_OR_ACTIVITY||' - '||SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
x_ce_api_valid_flag := 'N';
END;
BEGIN
SELECT estimate_id
INTO x_estimate_id
FROM eam_construction_estimates
WHERE 1 = 1
AND estimate_number = cx_eammlgw_ce_lines.estimate_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.LOG, 'Estimate Name not defined -' || cx_eammlgw_distinct_ce.estimate_name );
x_process_flag := 'E';
x_error_message := x_error_message || 'Estimate Name not defined - -' || cx_eammlgw_distinct_ce.estimate_name;
x_sql_err_flag := 1;
p_retcode := 2;
x_ce_api_valid_flag := 'N';
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, 'Error at Estimate Name - ' || SQLERRM||' - ' );
X_PROCESS_FLAG := 'E';
x_error_message := x_error_message || 'Error at Estimate Name - ' || SQLERRM||' - ';
x_sql_err_flag := 1;
p_retcode := 2;
x_ce_api_valid_flag := 'N';
END;
BEGIN
IF x_process_flag is null and x_ce_api_valid_flag is null
THEN
x_eam_ce_lines_tbl.DELETE;
FOR cx_eammlgw_res_data IN
c_eammlgw_res_data (x_src_item_id)
LOOP
-- i:= i+1;
--PRASHANT
IF cx_eammlgw_res_data.CU_SEGMENT1='CDR'
AND CX_EAMMLGW_RES_DATA.PHASE_REQUIRED='Y'
THEN
CX_EAMMLGW_CE_LINES.CU_QUANTITY := LENGTH(NVL(CX_EAMMLGW_CE_LINES.PHASE,1));
END IF;
--PRASHANT
if cx_eammlgw_res_data.op_attribute9 is null then
cx_eammlgw_res_data.op_attribute9 := '1';
end if;
if cx_eammlgw_res_data.op_attribute10 is null then
cx_eammlgw_res_data.op_attribute10:= '1';
end if;
IF upper(cx_eammlgw_ce_lines.cu_function) IN
('INSTALL', 'REMOVE', 'DO')
THEN
IF UPPER(CX_EAMMLGW_RES_DATA.OP_ATTRIBUTE8) = 'Y'
THEN
X_RES_REQUIRED_QUANTITY :=
( ( ( TO_NUMBER
(cx_eammlgw_res_data.op_attribute9)
* x_resource_multiflier
)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_quantity)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_span_length)
)
+ (TO_NUMBER
(cx_eammlgw_res_data.op_attribute10)
* TO_NUMBER
(CX_EAMMLGW_CE_LINES.CU_QUANTITY)));
ELSE
X_RES_REQUIRED_QUANTITY :=
( ( ( TO_NUMBER
(cx_eammlgw_res_data.op_attribute9)
* x_resource_multiflier
)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_quantity)
)
+ (TO_NUMBER
(cx_eammlgw_res_data.op_attribute10)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_quantity)));
END IF;
ELSIF upper(cx_eammlgw_ce_lines.cu_function) = 'ABANDON'
THEN
-- x_res_required_quantity :=
-- TO_NUMBER (cx_eammlgw_res_data.op_attribute9)
-- * 1;
--PRASHANT
X_RES_REQUIRED_QUANTITY :=
( TO_NUMBER (CX_EAMMLGW_RES_DATA.OP_ATTRIBUTE9)
* X_RESOURCE_MULTIFLIER);
--PRASHANT
ELSIF upper(cx_eammlgw_ce_lines.cu_function) = 'TRANSFER'
THEN
IF upper(cx_eammlgw_res_data.op_attribute8) = 'Y'
THEN
x_res_required_quantity :=
( ( TO_NUMBER
(cx_eammlgw_res_data.op_attribute9)
* x_resource_multiflier
)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_quantity)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_span_length)
);
ELSE
x_res_required_quantity :=
( ( TO_NUMBER
(cx_eammlgw_res_data.op_attribute9)
* x_resource_multiflier
)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_quantity)
);
END IF;
END IF;
x_res_cost :=
x_res_required_quantity
* cx_eammlgw_res_data.resource_rate;
/* call ce line api*/
x_eam_ce_lines_tbl (i).estimate_work_order_line_id :=
null;
x_eam_ce_lines_tbl (i).src_cu_id := NULL;
x_eam_ce_lines_tbl (i).src_activity_id :=
x_src_item_id;
x_eam_ce_lines_tbl (i).src_activity_qty :=
cx_eammlgw_ce_lines.cu_quantity;
x_eam_ce_lines_tbl (i).src_op_seq_num :=k;
-- cx_eammlgw_res_data.operation_seq_num;
x_eam_ce_lines_tbl (i).src_acct_class_code := X_WO_ACCT_CLASS_CODE;
x_eam_ce_lines_tbl (i).estimate_id := x_estimate_id;
x_eam_ce_lines_tbl (i).organization_id := x_organization_id;
x_eam_ce_lines_tbl (i).work_order_seq_num := X_WO_WORK_ORDER_SEQ_NUM;
x_eam_ce_lines_tbl (i).work_order_number := X_WO_WORK_ORDER_NUMBER;
x_eam_ce_lines_tbl (i).work_order_description := X_WO_WORK_ORDER_DESCRIPTION;
x_eam_ce_lines_tbl (i).ref_wip_entity_id := NULL;
x_eam_ce_lines_tbl (i).primary_item_id := NULL;
X_EAM_CE_LINES_TBL (I).STATUS_TYPE := null;
X_EAM_CE_LINES_TBL (I).ACCT_CLASS_CODE :=
X_WIP_ACCT_CLASS;
--prashant
X_EAM_CE_LINES_TBL (I).SCHEDULED_START_DATE :=
X_START_DATE;
X_EAM_CE_LINES_TBL (I).SCHEDULED_COMPLETION_DATE :=
X_COMPLETION_DATE;
x_eam_ce_lines_tbl (i).project_id := x_project_id;
x_eam_ce_lines_tbl (i).task_id := x_task_id;
x_eam_ce_lines_tbl (i).maintenance_object_id := x_maintenance_object_id;
x_eam_ce_lines_tbl (i).maintenance_object_type := x_maintenance_object_type;
x_eam_ce_lines_tbl (i).maintenance_object_source :=
NULL;
x_eam_ce_lines_tbl (i).owning_department_id :=
X_OWNING_DEPARTMENT_id;
x_eam_ce_lines_tbl (i).user_defined_status_id := NULL;
x_eam_ce_lines_tbl (i).op_seq_num :=k;
-- cx_eammlgw_res_data.operation_seq_num;
x_eam_ce_lines_tbl (i).op_description :=
cx_eammlgw_res_data.operation_description;
x_eam_ce_lines_tbl (i).standard_operation_id := NULL;
x_eam_ce_lines_tbl (i).op_department_id :=
cx_eammlgw_res_data.department_id;
x_eam_ce_lines_tbl (i).op_long_description :=
cx_eammlgw_res_data.long_description;
x_eam_ce_lines_tbl (i).res_seq_num :=
cx_eammlgw_res_data.resource_seq_num;
x_eam_ce_lines_tbl (i).res_id :=
cx_eammlgw_res_data.resource_id;
x_eam_ce_lines_tbl (i).res_uom :=
cx_eammlgw_res_data.unit_of_measure;
x_eam_ce_lines_tbl (i).res_basis_type :=
cx_eammlgw_res_data.basis_type;
x_eam_ce_lines_tbl (i).res_usage_rate_or_amount :=
cx_eammlgw_res_data.usage_rate_or_amount;
x_eam_ce_lines_tbl (i).res_required_units := NULL;
x_eam_ce_lines_tbl (i).res_assigned_units :=
cx_eammlgw_res_data.assigned_units;
x_eam_ce_lines_tbl (i).item_type := NULL;
x_eam_ce_lines_tbl (i).required_quantity :=
x_res_required_quantity;
x_eam_ce_lines_tbl (i).unit_price := NULL;
x_eam_ce_lines_tbl (i).uom := NULL;
x_eam_ce_lines_tbl (i).basis_type := NULL;
x_eam_ce_lines_tbl (i).suggested_vendor_name := NULL;
x_eam_ce_lines_tbl (i).suggested_vendor_id := NULL;
x_eam_ce_lines_tbl (i).suggested_vendor_site := NULL;
x_eam_ce_lines_tbl (i).suggested_vendor_site_id :=
NULL;
x_eam_ce_lines_tbl (i).mat_inventory_item_id := NULL;
x_eam_ce_lines_tbl (i).mat_component_seq_num := NULL;
x_eam_ce_lines_tbl (i).mat_supply_subinventory := NULL;
x_eam_ce_lines_tbl (i).mat_supply_locator_id := NULL;
x_eam_ce_lines_tbl (i).di_amount := NULL;
x_eam_ce_lines_tbl (i).di_order_type_lookup_code :=
NULL;
x_eam_ce_lines_tbl (i).di_description := NULL;
x_eam_ce_lines_tbl (i).di_purchase_category_id := NULL;
x_eam_ce_lines_tbl (i).di_auto_request_material :=
NULL;
x_eam_ce_lines_tbl (i).di_need_by_date := NULL;
x_eam_ce_lines_tbl (i).work_order_line_cost :=
cx_eammlgw_res_data.resource_rate;
x_eam_ce_lines_tbl (i).difficulty_qty :=
x_resource_multiflier;
x_eam_ce_lines_tbl (i).difficulty_id :=
x_difficulty_id;
x_eam_ce_lines_tbl (i).cu_qty :=
cx_eammlgw_ce_lines.cu_quantity;
x_eam_ce_lines_tbl (i).item_comments := NULL;
x_eam_ce_lines_tbl (i).work_order_type :=
x_ce_lines_wo_type;
x_eam_ce_lines_tbl (i).activity_type := NULL;
x_eam_ce_lines_tbl (i).activity_cause := NULL;
x_eam_ce_lines_tbl (i).activity_source := NULL;
x_eam_ce_lines_tbl (i).available_quantity := NULL;
x_eam_ce_lines_tbl (i).res_scheduled_flag :=
cx_eammlgw_res_data.schedule_flag;
x_eam_ce_lines_tbl (i).op_attribute1 :=
cx_eammlgw_ce_lines.construction_point;
x_eam_ce_lines_tbl (i).op_attribute2 :=
cx_eammlgw_ce_lines.cu_quantity;
x_eam_ce_lines_tbl (i).op_attribute3 :=cx_eammlgw_ce_lines.cu_span_length;
-- cx_eammlgw_ce_lines.cu_span_length;
x_eam_ce_lines_tbl (i).op_attribute4 := cx_eammlgw_ce_lines.cu_function;
x_eam_ce_lines_tbl (i).op_attribute5 := cx_eammlgw_ce_lines.condition;
x_eam_ce_lines_tbl (i).op_attribute6 :=
cx_eammlgw_ce_lines.difficulty;
x_eam_ce_lines_tbl (i).op_attribute7 :=
cx_eammlgw_ce_lines.phase;
x_eam_ce_lines_tbl (i).op_attribute8 :=
cx_eammlgw_res_data.op_attribute8;
x_eam_ce_lines_tbl (i).op_attribute9 :=
cx_eammlgw_res_data.op_attribute9;
x_eam_ce_lines_tbl (i).op_attribute10 :=
cx_eammlgw_res_data.op_attribute10;
X_EAM_CE_LINES_TBL (I).OP_ATTRIBUTE11 :=
NVL(cx_eammlgw_res_data.op_attribute11,'N'); --PRASHANT
x_eam_ce_lines_tbl (i).op_attribute12 :=
cx_eammlgw_res_data.op_attribute12;
x_eam_ce_lines_tbl (i).op_attribute13 :=
cx_eammlgw_res_data.op_attribute13;
x_eam_ce_lines_tbl (i).op_attribute14 :=
cx_eammlgw_res_data.op_attribute14;
x_eam_ce_lines_tbl (i).op_attribute15 :=
cx_eammlgw_res_data.op_attribute15;
x_eam_ce_lines_tbl (i).res_attribute8 :=
cx_eammlgw_res_data.res_attribute8;
x_eam_ce_lines_tbl (i).res_attribute9 :=
cx_eammlgw_res_data.res_attribute9;
x_eam_ce_lines_tbl (i).res_attribute10 :=
cx_eammlgw_res_data.res_attribute10;
x_eam_ce_lines_tbl (i).res_attribute11 :=
cx_eammlgw_res_data.res_attribute11;
x_eam_ce_lines_tbl (i).res_attribute12 :=
cx_eammlgw_res_data.res_attribute12;
x_eam_ce_lines_tbl (i).res_attribute13 :=
cx_eammlgw_res_data.res_attribute13;
x_eam_ce_lines_tbl (i).res_attribute14 :=
cx_eammlgw_res_data.res_attribute14;
x_eam_ce_lines_tbl (i).res_attribute15 :=
cx_eammlgw_res_data.res_attribute15;
eam_construction_est_pub.insert_all_wo_lines
(p_api_version => 1.0,
p_init_msg_list => x_v_p_init_msg_list,
p_commit => x_v_p_commit,
p_estimate_id => x_estimate_id,
p_eam_ce_wo_lines_tbl => x_eam_ce_lines_tbl,
x_return_status => x_v_x_return_status,
x_msg_count => x_v_x_msg_count,
X_MSG_DATA => X_V_X_MSG_DATA
);
IF X_V_X_RETURN_STATUS != 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'CE Lines API Error while inserting Resource Data' ||x_v_x_msg_data);
x_process_flag := 'E';
x_error_message := x_error_message || 'CE Lines API Error while inserting Resource Data '||x_v_x_msg_data;
x_sql_err_flag := 1;
p_retcode := 2;
x_ce_api_valid_flag := 'N';
END IF;
END LOOP;
IF x_process_flag is null and x_ce_api_valid_flag is null
THEN
x_eam_ce_lines_tbl.DELETE;
FOR cx_eammlgw_bom_data IN
c_eammlgw_bom_data (x_src_item_id)
LOOP
--PRASHANT
IF cx_eammlgw_bom_data.CU_SEGMENT1='CDR'
AND cx_eammlgw_bom_data.PHASE_REQUIRED='Y'
THEN
CX_EAMMLGW_CE_LINES.CU_QUANTITY := LENGTH(NVL(CX_EAMMLGW_CE_LINES.PHASE,1));
END IF;
--PRASHANT
--added by uday on 11feb2014--for item specific
if cx_eammlgw_bom_data.material_cost is null then
cx_eammlgw_bom_data.material_cost := cx_eammlgw_bom_data.unit_price;
end if;--this is for non stock inventory
if cx_eammlgw_bom_data.on_hand_qty = 0 then
cx_eammlgw_bom_data.on_hand_qty := 2;--making item_type as 2 i.e i,e non stocked inventiry
else
cx_eammlgw_bom_data.on_hand_qty := 1;--making item_type as 1 i,e stocked inventiry
end if;
--end added by uday on 11feb2014--
if cx_eammlgw_bom_data.mat_attribute3 is null then
cx_eammlgw_bom_data.mat_attribute3 := '1';
end if;
if cx_eammlgw_bom_data.mat_attribute4 is null then
cx_eammlgw_bom_data.mat_attribute4:= '1';
end if;
IF UPPER(cx_eammlgw_ce_lines.cu_function) IN
('INSTALL', 'DO')
THEN
IF UPPER(CX_EAMMLGW_BOM_DATA.OP_MAT_ATTRIBUTE8)='Y' --PRASHANT
THEN
x_bom_required_quantity :=
( TO_NUMBER
(cx_eammlgw_bom_data.mat_attribute3)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_quantity)
)
+ ( TO_NUMBER
(cx_eammlgw_bom_data.mat_attribute4)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_span_length)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_quantity)
);
--PRASHANT
ELSE
x_bom_required_quantity :=
( TO_NUMBER
(cx_eammlgw_bom_data.mat_attribute3)
* TO_NUMBER
(cx_eammlgw_ce_lines.cu_quantity)
)
+ ( TO_NUMBER
(cx_eammlgw_bom_data.mat_attribute4)
* TO_NUMBER
(CX_EAMMLGW_CE_LINES.CU_QUANTITY)
);
END IF;
--PRASHANT
x_bom_cost :=
x_bom_required_quantity
* cx_eammlgw_bom_data.material_cost;
x_eam_ce_lines_tbl (i).estimate_work_order_line_id :=
null ;
x_eam_ce_lines_tbl (i).src_cu_id := NULL;
x_eam_ce_lines_tbl (i).src_activity_id :=
x_src_item_id;
x_eam_ce_lines_tbl (i).src_activity_qty :=
cx_eammlgw_ce_lines.cu_quantity;
x_eam_ce_lines_tbl (i).src_op_seq_num :=k;
--cx_eammlgw_bom_data.operation_seq_num;
x_eam_ce_lines_tbl (i).src_acct_class_code :=
X_WO_ACCT_CLASS_CODE;
x_eam_ce_lines_tbl (i).estimate_id :=
x_estimate_id;
x_eam_ce_lines_tbl (i).organization_id := x_organization_id;
x_eam_ce_lines_tbl (i).work_order_seq_num :=
X_WO_WORK_ORDER_SEQ_NUM;
x_eam_ce_lines_tbl (i).work_order_number := X_WO_WORK_ORDER_NUMBER;
x_eam_ce_lines_tbl (i).work_order_description :=
X_WO_WORK_ORDER_DESCRIPTION;
x_eam_ce_lines_tbl (i).ref_wip_entity_id := NULL;
x_eam_ce_lines_tbl (i).primary_item_id := NULL;
X_EAM_CE_LINES_TBL (I).STATUS_TYPE := null ;
X_EAM_CE_LINES_TBL (I).ACCT_CLASS_CODE :=
X_WIP_ACCT_CLASS;
--prashant
X_EAM_CE_LINES_TBL (I).SCHEDULED_START_DATE :=X_START_DATE;
x_eam_ce_lines_tbl (i).scheduled_completion_date :=X_COMPLETION_DATE;
x_eam_ce_lines_tbl (i).project_id := x_project_id;
x_eam_ce_lines_tbl (i).task_id := x_task_id;
x_eam_ce_lines_tbl (i).maintenance_object_id :=
x_MAINTENANCE_OBJECT_ID;
x_eam_ce_lines_tbl (i).maintenance_object_type :=
x_MAINTENANCE_OBJECT_TYPE;
x_eam_ce_lines_tbl (i).maintenance_object_source :=
NULL;
x_eam_ce_lines_tbl (i).owning_department_id :=
X_OWNING_DEPARTMENT_id;
x_eam_ce_lines_tbl (i).user_defined_status_id :=
NULL;
x_eam_ce_lines_tbl (i).op_seq_num :=k;
--cx_eammlgw_bom_data.operation_seq_num;
x_eam_ce_lines_tbl (i).op_description :=
cx_eammlgw_bom_data.operation_description;
x_eam_ce_lines_tbl (i).standard_operation_id :=
NULL;
x_eam_ce_lines_tbl (i).op_department_id :=
cx_eammlgw_bom_data.department_id;
x_eam_ce_lines_tbl (i).op_long_description :=
cx_eammlgw_bom_data.long_description;
x_eam_ce_lines_tbl (i).res_usage_rate_or_amount :=
cx_eammlgw_bom_data.component_quantity;
x_eam_ce_lines_tbl (i).res_required_units :=
NULL;
x_eam_ce_lines_tbl (i).item_type := NULL;
x_eam_ce_lines_tbl (i).required_quantity :=
x_bom_required_quantity;
x_eam_ce_lines_tbl (i).unit_price := cx_eammlgw_bom_data.unit_price;
x_eam_ce_lines_tbl (i).uom := NULL;
x_eam_ce_lines_tbl (i).basis_type := NULL;
x_eam_ce_lines_tbl (i).suggested_vendor_name :=
NULL;
x_eam_ce_lines_tbl (i).suggested_vendor_id :=
NULL;
x_eam_ce_lines_tbl (i).suggested_vendor_site :=
NULL;
x_eam_ce_lines_tbl (i).suggested_vendor_site_id :=
NULL;
x_eam_ce_lines_tbl (i).mat_inventory_item_id :=
cx_eammlgw_bom_data.component_item_id;
x_eam_ce_lines_tbl (i).mat_component_seq_num :=
cx_eammlgw_bom_data.component_sequence_id;
x_eam_ce_lines_tbl (i).mat_supply_subinventory :=
NULL;
x_eam_ce_lines_tbl (i).mat_supply_locator_id :=
NULL;
x_eam_ce_lines_tbl (i).di_amount := NULL;
x_eam_ce_lines_tbl (i).di_order_type_lookup_code :=
NULL;
x_eam_ce_lines_tbl (i).di_description := NULL;
x_eam_ce_lines_tbl (i).di_purchase_category_id :=
NULL;
x_eam_ce_lines_tbl (i).di_auto_request_material :=
NULL;
x_eam_ce_lines_tbl (i).di_need_by_date := NULL;
x_eam_ce_lines_tbl (i).work_order_line_cost :=
cx_eammlgw_bom_data.material_cost;
x_eam_ce_lines_tbl (i).difficulty_qty :=
x_resource_multiflier;
x_eam_ce_lines_tbl (i).difficulty_id :=
x_difficulty_id;
x_eam_ce_lines_tbl (i).cu_qty :=
cx_eammlgw_ce_lines.cu_quantity;
x_eam_ce_lines_tbl (i).item_comments := NULL;
x_eam_ce_lines_tbl (i).work_order_type :=
x_ce_lines_wo_type;
x_eam_ce_lines_tbl (i).activity_type := NULL;
x_eam_ce_lines_tbl (i).activity_cause := NULL;
x_eam_ce_lines_tbl (i).activity_source := NULL;
x_eam_ce_lines_tbl (i).available_quantity :=
NULL;
x_eam_ce_lines_tbl (i).di_auto_request_material :=cx_eammlgw_bom_data.AUTO_REQUEST_MATERIAL;
-- 'Y';
x_eam_ce_lines_tbl (i).item_type := cx_eammlgw_bom_data.on_hand_qty;
x_eam_ce_lines_tbl (i).op_attribute1 :=
cx_eammlgw_ce_lines.construction_point;
x_eam_ce_lines_tbl (i).op_attribute2 :=
cx_eammlgw_ce_lines.cu_quantity;
x_eam_ce_lines_tbl (i).op_attribute3 :=
cx_eammlgw_ce_lines.cu_span_length;
x_eam_ce_lines_tbl (i).op_attribute4 :=
cx_eammlgw_ce_lines.cu_function;
x_eam_ce_lines_tbl (i).op_attribute5 :=
cx_eammlgw_ce_lines.condition;
x_eam_ce_lines_tbl (i).op_attribute6 :=
cx_eammlgw_ce_lines.difficulty;
x_eam_ce_lines_tbl (i).op_attribute7 :=
cx_eammlgw_ce_lines.phase;
x_eam_ce_lines_tbl (i).op_attribute8 :=
cx_eammlgw_bom_data.op_mat_attribute8;
x_eam_ce_lines_tbl (i).op_attribute9 :=
cx_eammlgw_bom_data.op_mat_attribute9;
x_eam_ce_lines_tbl (i).op_attribute10 :=
cx_eammlgw_bom_data.op_mat_attribute10;
x_eam_ce_lines_tbl (i).op_attribute11 := NVL(cx_eammlgw_bom_data.op_mat_attribute11,'N');
--x_cu_temporary;
x_eam_ce_lines_tbl (i).op_attribute12 :=
cx_eammlgw_bom_data.op_mat_attribute12;
x_eam_ce_lines_tbl (i).op_attribute13 :=
cx_eammlgw_bom_data.op_mat_attribute13;
x_eam_ce_lines_tbl (i).op_attribute14 :=
cx_eammlgw_bom_data.op_mat_attribute14;
x_eam_ce_lines_tbl (i).op_attribute15 :=
cx_eammlgw_bom_data.op_mat_attribute15;
x_eam_ce_lines_tbl (i).mat_attribute1 :=
cx_eammlgw_bom_data.mat_attribute1;
x_eam_ce_lines_tbl (i).mat_attribute2 :=
cx_eammlgw_bom_data.mat_attribute2;
x_eam_ce_lines_tbl (i).mat_attribute3 :=
cx_eammlgw_bom_data.mat_attribute3;
x_eam_ce_lines_tbl (i).mat_attribute4 :=
cx_eammlgw_bom_data.mat_attribute4;
x_eam_ce_lines_tbl (i).mat_attribute5 :=
cx_eammlgw_bom_data.mat_attribute5;
X_EAM_CE_LINES_TBL (I).MAT_ATTRIBUTE6 :=
CX_EAMMLGW_BOM_DATA.MAT_ATTRIBUTE6;
EAM_CONSTRUCTION_EST_PUB.INSERT_ALL_WO_LINES
(p_api_version => 1.0,
p_init_msg_list => x_v_p_init_msg_list,
p_commit => x_v_p_commit,
p_estimate_id => x_estimate_id,
p_eam_ce_wo_lines_tbl => x_eam_ce_lines_tbl,
x_return_status => x_v_x_return_status,
x_msg_count => x_v_x_msg_count,
x_msg_data => x_v_x_msg_data
);
IF x_v_x_return_status != 'S'
THEN
fnd_file.put_line (fnd_file.LOG, 'CE Lines API Error while inserting BOM Data'||x_v_x_msg_data );
x_process_flag := 'E';
x_error_message := x_error_message || 'CE Lines API Error while inserting BOM Data '||x_v_x_msg_data;
x_sql_err_flag := 1;
p_retcode := 2;
x_ce_api_valid_flag := 'N';
END IF;
END IF;
END LOOP;
END IF;
END IF;
END;
fnd_file.put_line(fnd_file.LOG,'---------------------------------------------------');
fnd_file.put_line(fnd_file.LOG,' ');
END LOOP;
/* added by uday on 28 march*/
IF X_CE_API_VALID_FLAG = 'N'
THEN
ROLLBACK;
X_PROCESS_FLAG := 'E';
X_SQL_ERR_FLAG := 1;
p_retcode := 2;
-- UPDATE mlgweam_ce_stg
-- SET process_flag = 'E'
-- WHERE 1 = 1
-- AND estimate_name = cx_eammlgw_distinct_ce.estimate_name;
-- COMMIT;
elsif x_ce_api_valid_flag IS NULL--x_ce_lines_valid_flag
THEN
-- UPDATE mlgweam_ce_stg
-- SET process_flag = 'S'
-- WHERE 1 = 1
-- AND ESTIMATE_NAME = CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME;
FOR CX_EAMMLGW_CE_LINES_ATT IN C_EAMMLGW_CE_LINES_ATT(CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME)
loop
IF CX_EAMMLGW_CE_LINES_ATT.URL IS NOT NULL
then
j:=j+10;
BEGIN
FND_WEBATTCH.ADD_ATTACHMENT
(seq_num => j,
CATEGORY_ID => 1,
document_description => x_ce_description,--cx_eammlgw_ce_lines.estimate_description,--prashant
datatype_id => 5,
text => NULL,
file_name => NULL,
url => cx_eammlgw_ce_lines_att.url,
function_name => 'EAM_CU_CONSTRUCTION_ESTIMATE',
entity_name => 'EAM_CONSTRUCTION_ESTIMATES',
pk1_value => x_estimate_id,
pk2_value => NULL,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => NULL,
user_id => g_user_id,
usage_type => 'O',
title => cx_eammlgw_ce_lines_att.estimate_name
);
END;
end if;
end loop;
-- commit;
end if;
ELSE
ROLLBACK;
X_PROCESS_FLAG := 'E';
X_SQL_ERR_FLAG := 1;
p_retcode := 2;
-- UPDATE mlgweam_ce_stg
-- SET process_flag = 'E'
-- WHERE 1 = 1
-- AND estimate_name = cx_eammlgw_distinct_ce.estimate_name;
-- commit;
/* end added by uday on 28 march1013*/
END IF;
end;
-- BEGIN
-- IF x_sql_err_flag = 1
-- THEN
-- MLGWFND_ERR_REJECT_IN_PKG.MLGWFND_INSERT_ERR_PROC
-- (p_interface_id => 'IN003',
-- P_ENTITY_ID => CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME,
-- p_entity_name => 'Estimate number: '||cx_eammlgw_distinct_ce.estimate_name,
-- p_err_msg => x_error_message
-- );
-- COMMIT;
-- END IF;
-- END;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&');
-- /*
ELSE
X_PROCESS_FLAG := 'E';
X_SQL_ERR_FLAG := 1;
p_retcode := 2;
-- UPDATE mlgweam_ce_stg
-- SET process_flag = 'E'
-- WHERE 1 = 1
-- AND estimate_name = cx_eammlgw_distinct_ce.estimate_name;
-- BEGIN
-- IF x_sql_err_flag = 1
-- THEN
-- mlgwfnd_err_reject_in_pkg.mlgwfnd_insert_err_proc
-- (p_interface_id => 'IN003',
-- P_ENTITY_ID => CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME,
-- p_entity_name => 'Estimate number: '||cx_eammlgw_distinct_ce.estimate_name,
-- p_err_msg => x_error_message
-- );
-- END IF;
-- END;
-- COMMIT;
END IF;
-- */
BEGIN
IF X_PROCESS_FLAG = 'E'
THEN
UPDATE mlgweam_ce_stg
SET PROCESS_FLAG = 'E'
WHERE 1 = 1
AND ESTIMATE_NAME = CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME;
ELSE
UPDATE mlgweam_ce_stg
SET process_flag = 'S'
WHERE 1 = 1
AND ESTIMATE_NAME = CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME;
END IF;
BEGIN
IF x_sql_err_flag = 1
THEN
mlgwfnd_err_reject_in_pkg.mlgwfnd_insert_err_proc
(p_interface_id => 'IN003',
P_ENTITY_ID => CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME,
p_entity_name => 'CONCURRENT REQUEST ID: '||g_request_id||' FOR ESTIMATE NUMBER: '||cx_eammlgw_distinct_ce.estimate_name||' - AND SOA INSTANCE ID FOR ArcFM: '||cx_eammlgw_distinct_ce.batch_id,
p_err_msg => x_error_message
);
END IF;
END;
END;
COMMIT;
SELECT COUNT(1)
INTO x_ce_succ_processed_cnt
FROM MLGWEAM_CE_STG
where ESTIMATE_NAME = NVL(P_ESTIMATE_NUMBER,CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME)
and PROCESS_FLAG = 'S';
SELECT COUNT(1)
INTO x_ce_error_processed_cnt
FROM MLGWEAM_CE_STG
WHERE ESTIMATE_NAME = NVL(P_ESTIMATE_NUMBER,CX_EAMMLGW_DISTINCT_CE.ESTIMATE_NAME)
AND PROCESS_FLAG = 'E';
END LOOP;
--------------------------------------
-- Printing Summary Information
--------------------------------------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
fnd_file.put_line(fnd_file.output,'Total Number of Estimates which are Processed : '||x_ce_succ_processed_cnt );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
fnd_file.put_line(fnd_file.output,'Total Number of Estimates with errors and which did not Processed : '||x_ce_error_processed_cnt );
--------------------end API to insert CE lines--------------
--PRASHANT
-- /*
DELETE FROM MLGWEAM_CE_STG
WHERE PROCESS_FLAG = 'S';
COMMIT;
-- */
--PRASHANT
END;
END MLGWEAM_ARCFM_CE_COST_PKG;
/
I discovered your site ideal for me. It consists of wonderful and useful posts. I've read many of them and also got so much from them. In my experience, you do the truly amazing. Truly I’m impressed out of this publish. Construction estimation
ReplyDelete