ORACLE MASTERMINDS: EAM Construction Estimation Creation

Monday, September 24, 2018

EAM Construction Estimation Creation

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

/*+-----------------------------------------------------------------------------------+

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

/*+-----------------------------------------------------------------------------------+

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

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

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)

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)

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)

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)

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)

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)

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;

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

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

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;

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;

1 comment:

ictstudyhomeJuly 7, 2019 at 12:30 AM
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
Replies

Add comment