CREATE OR REPLACE PROCEDURE GDC_PRC_TRANS_TASK_LOAD IS errn NUMBER(5); errmsg VARCHAR2(2000); tempCount NUMBER; v_count NUMBER; V_START_TIME DATE := SYSDATE; V_END_TIME DATE; BEGIN DBMS_OUTPUT.PUT_LINE('Starting to pull Task Details'); execute immediate 'truncate table PMS_TASK_DETAILS'; INSERT INTO PMS_TASK_DETAILS ( TASK_ID, PROJECT_ID, NAME, TASK_NAME, DESCRIPTION, STATUS, ASSIGN_DT, EMP_NO, EMP_NAME, PLAN_START_DT, PLAN_END_DT, ACT_START_DT, CR_RAISED_DT, NEGOTIATED_END_DT, BUCKET_NO, SIZE_MEASURE, KLOC, KLOC_ACT, REVISED_SIZE, METRICS_APPLICABLE, PLANNED_DEFECTS, ESTIMATED_EFFORT, ACTUAL_EFF, PERCENT_COMP, REMARKS, MAIN_ACTIVITY, SUB_ACTIVITY, TECHNOLOGY, ONHOLD_ST_DT, ONHOLD_END_DT, ACT_END_DT, ROW_OPERATION_DT,RECORD_LEVEL, DELETE_FLAG ) SELECT TRIM("task_id"),TRIM("project_id"),TRIM("name"), TRIM("task_name"),TRIM("description"),TRIM("status"), TO_DATE(TO_CHAR("assign_dt",'MM-DD-YYYY'),'MM-DD-YYYY'),"emp_no",TRIM("emp_name"), TO_DATE(TO_CHAR("plan_start_dt",'MM-DD-YYYY'),'MM-DD-YYYY'),TO_DATE(TO_CHAR("plan_end_dt",'MM-DD-YYYY'),'MM-DD-YYYY'),TO_DATE(TO_CHAR("act_start_dt",'MM-DD-YYYY'),'MM-DD-YYYY'), TO_DATE(TO_CHAR("cr_raised_dt",'MM-DD-YYYY'),'MM-DD-YYYY'),TO_DATE(TO_CHAR("negotiated_end_dt",'MM-DD-YYYY'),'MM-DD-YYYY'),"bucket_no", TRIM("size_measure"),"kloc","kloc_act", "revised_size",TRIM("metrics_applicable"),"planned_defects", "estimated_effort","actual_eff","percent_comp", TRIM("remarks"),TRIM("main_activity"),TRIM("sub_activity"), TRIM("technology"),TO_DATE(TO_CHAR("onhold_st_dt",'MM-DD-YYYY'),'MM-DD-YYYY'),TO_DATE(TO_CHAR("onhold_end_dt",'MM-DD-YYYY'),'MM-DD-YYYY'), TO_DATE(TO_CHAR("act_end_dt",'MM-DD-YYYY'),'MM-DD-YYYY'),"row_operation_dt","record_level", TRIM("delete_flag") FROM v_gdc_task_details@cormis; V_END_TIME := SYSDATE; INSERT INTO GDC_PROC_RUN_TIME ( GDC_PROC_NAME, GDC_START_TIME, GDC_END_TIME, GDC_TIME_UTILISED ) VALUES ( 'GDC_PRC_TRANS_TASK_LOAD',V_START_TIME, V_END_TIME,((V_END_TIME-V_START_TIME)*24*60) ); UPDATE GDC_PRC_RUN_FLAG SET GDC_PRC_UPLOAD_FLAG = 'Y', GDC_PRC_START_DATE = V_START_TIME WHERE TRIM(GDC_PRC_NAME) = 'GDC_PRC_TRANS_QD_LOAD'; IF SQL%ROWCOUNT = 0 THEN INSERT INTO GDC_PRC_RUN_FLAG(GDC_PRC_NAME,GDC_PRC_UPLOAD_FLAG,GDC_PRC_START_DATE ) VALUES ('GDC_PRC_TRANS_QD_LOAD','Y',V_START_TIME); END IF; COMMIT; DBMS_OUTPUT.PUT_LINE('Task Details Pull complete'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Err ' || SQLCODE || ' Message : ' || SQLERRM ); ROLLBACK; END GDC_PRC_TRANS_TASK_LOAD;