Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009
    Posts
    2

    Unanswered: unique constraint violated

    Hey, when i'm executing this package
    i'm getting an error message : ORA-00001: unique constraint (RENTAB2.RTB_CONTRACT_PREDICTION_PK) violated
    ORA-06512: at "RENTAB2.P_RTB_SECACC_PREDICTIONS", line 128
    ORA-06512: at line 1


    this is the package :

    CREATE OR REPLACE PACKAGE BODY RENTAB2.P_RTB_SECACC_PREDICTIONS AS
    /************************************************** ****************************
    NAME: P_RTB_SECACC_PREDICTIONS
    PURPOSE:

    REVISIONS:
    Ver Date Author Description
    --------- ---------- --------------- ------------------------------------
    1.0 28/05/2009 Kihel Najib 1. Created this package body.
    ************************************************** ****************************/
    PROCEDURE INSERT_RTB_CONTRACT_PRED (IN_JOB_RUN IN NUMBER) IS

    LV_FRST_DAY_MTH DATE := "ETL_LIB_RTB"."GET_FIRST_DAY_OF_MONTH"("ETL_LIB_DT WH"."GET_RTB2_MTH_BUS_DAT"()) ;
    LV_LST_DAY_MTH DATE := "ETL_LIB_RTB"."GET_LAST_DAY_OF_MONTH" ("ETL_LIB_DTWH"."GET_RTB2_MTH_BUS_DAT"()) ;
    LV_LST_DAY_PREV_YEAR DATE := LAST_DAY(ADD_MONTHS(TRUNC(ADD_MONTHS("ETL_LIB_DTWH "."GET_RTB2_MTH_BUS_DAT"(), -12),'SYYYY'), 11)) ;
    LV_END_DAT DATE := ADD_MONTHS("ETL_LIB_DTWH"."GET_RTB2_MTH_BUS_DAT"() , +11);
    LV_CNTRCT_ID th_deposit_contract_movements.contract_id%TYPE;
    LV_TOT_SUM_MOV th_deposit_contract_movements.movement_fc_amt%TYPE ;
    LV_NMBR_SECACC NUMBER(10);
    LV_SPLIT_SUM NUMBER(10);
    LV_DB_SRC VARCHAR2(10) := "ETL_LIB_RTB_SECACC"."GET_DB_SOURCE_SECACC"();

    CURSOR CURS_MOVEMENT IS
    SELECT CONTRACT_ID, sum(MOVEMENT_FC_AMT) as TOTAL_FEE_AMT
    FROM th_deposit_contract_movements
    where ACCOUNTING_DAT between LV_FRST_DAY_MTH and LV_LST_DAY_MTH
    and STANDARD_TEXT_COD = 'CT07'
    and ACCOUNTING_TYPE = '01'
    group by CONTRACT_ID;


    CURSOR CURS_COUNT_SECACC IS
    SELECT COUNT(*) as NUMBER_OF_SECURITY_ACCOUNT
    FROM th_deposit_contract_dim TDCD,
    th_deposit_balance_month TDBM
    WHERE TDCD.ORI_SEC_CAP_PRODUCT = '00'
    AND TDCD.LINK_CONTRACT_ID is not null
    AND tdcd.STATUS_DESCR = 'Open'
    AND TDCD.BLOCKADE_COD = '00'
    AND TDCD.DEPO_CONTRACT_DIM_IDN = TDBM.DEPO_CONTRACT_DIM_IDN
    AND TDBM.BALANCE_DAT = LV_LST_DAY_PREV_YEAR
    AND TDBM.BALANCE_LC_AMT > 0
    AND TDCD.LINK_CONTRACT_ID in (LV_CNTRCT_ID);



    CURSOR CURS_SPLIT_AMT IS
    SELECT TDCD.CONTRACT_ID as SECURITY_ACCOUNT
    FROM TH_DEPOSIT_CONTRACT_DIM TDCD,
    TH_DEPOSIT_BALANCE_MONTH TDBM
    WHERE TDCD.ORI_SEC_CAP_PRODUCT = '00'
    AND TDCD.LINK_CONTRACT_ID is not null
    AND tdcd.STATUS_DESCR = 'Open'
    AND TDCD.BLOCKADE_COD = '00'
    AND TDCD.DEPO_CONTRACT_DIM_IDN = TDBM.DEPO_CONTRACT_DIM_IDN
    AND TDBM.BALANCE_DAT = LV_LST_DAY_PREV_YEAR
    AND TDBM.BALANCE_LC_AMT > 0
    AND TDCD.LINK_CONTRACT_ID in (LV_CNTRCT_ID);

    REC_DATA CURS_SPLIT_AMT%ROWTYPE;

    BEGIN

    OPEN CURS_MOVEMENT;
    LOOP
    FETCH CURS_MOVEMENT INTO LV_CNTRCT_ID, LV_TOT_SUM_MOV ;
    EXIT WHEN CURS_MOVEMENT%NOTFOUND;

    OPEN CURS_COUNT_SECACC;
    LOOP
    FETCH CURS_COUNT_SECACC INTO LV_NMBR_SECACC ;
    EXIT WHEN CURS_MOVEMENT%NOTFOUND;

    LV_SPLIT_SUM := LV_TOT_SUM_MOV/LV_NMBR_SECACC;

    OPEN CURS_SPLIT_AMT;
    LOOP

    FETCH CURS_SPLIT_AMT INTO REC_DATA;
    EXIT WHEN CURS_MOVEMENT%NOTFOUND;

    BEGIN
    INSERT INTO RTB_CONTRACT_PREDICTION (
    CONTRACT_ID,
    COMPONENT_COD,
    START_DAT,
    END_DAT,
    AMOUNT,
    SPLITTED_FLG,
    FACTOR_PCT,
    DB_SOURCE,
    DURATION_MONTH,
    STEP,
    LAST_UPDATE_USER,
    LAST_UPDATE_DAT)
    VALUES( REC_DATA.SECURITY_ACCOUNT,
    'FEE_MFR',
    LV_FRST_DAY_MTH ,
    LV_END_DAT ,
    LV_SPLIT_SUM ,
    'Y',
    1,
    LV_DB_SRC ,
    12,
    1,
    USER ,
    SYSDATE);

    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
    END;

    END LOOP;
    CLOSE CURS_SPLIT_AMT;
    END LOOP;
    CLOSE CURS_COUNT_SECACC;
    END LOOP;
    CLOSE CURS_MOVEMENT;


    COMMIT;

    EXCEPTION
    WHEN OTHERS THEN
    RAISE;

    END INSERT_RTB_CONTRACT_PRED;

    END P_RTB_SECACC_PREDICTIONS;
    /

    can someone help me please?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What kind of help do you expect?

    INSERT tried to insert a record which violated unique constraint. Instead of (useless) WHEN OTHERS, display the current record in order to see what was going on.

    If values were OK, then unique constraint is not correctly set - recreate it.
    If constraint is OK, then don't insert values that violate the constraint.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •