9 Replies Latest reply: Jan 4, 2013 2:59 AM by 959687 RSS

    How to debug and find the exact constraint violation error

    959687
      {122712 14:22:36:ErrorCode -1 with ErrorMessage as ORA-00001: unique constraint (OPS$CMS.PK_TB_ML_EXER_UPLOAD) violated has occured for [SSO16063259009], [CMSCOST_USER] pk_xop_subsales.pr_process_exer }

      {PROCEDURE pr_process_exer(
      p_voucher_num           tb_xop_order_manager_t.voucher_num%TYPE,
      p_status_type           tb_xop_order_manager_t.status_type%type,
      p_dest                    tb_xop_order_manager_t.dest%type,
      p_reference_key            tb_xop_order_manager_t.reference_key%type,
      p_seq_num                tb_xop_order_manager_t.seq_num%type,
      p_return_code            tb_xop_order_manager_t.return_code%type,
      p_reason_desc           tb_xop_order_manager_t.reason_desc%type,
      p_activity_qty            tb_xop_order_manager_t.activity_qty%type,
      p_leaves_qty            tb_xop_order_manager_t.leaves_qty%type,
      p_exec_price            tb_xop_order_manager_t.exec_price%type,
      p_current_status        tb_xop_order_manager_t.current_status%type,
      p_err_cur          OUT  ref_cursor)
      IS
      CURSOR get_order_dtls(v_voucher_num tb_xop_order_manager_t.voucher_num%type)
      IS
      SELECT *
      FROM   tb_xop_order_manager_t
      WHERE  voucher_num = v_voucher_num;

      CURSOR get_mail_cd(v_opt_num OPTIONEE.opt_num%TYPE) IS
            SELECT mail_cd, sp_mail_cd
            FROM XOP_OPTIONEE
            WHERE opt_num = v_opt_num;

      cursor get_opt_dtls(v_opt_num OPTIONEE.opt_num%TYPE) IS
      select
      SUBSTR(trim(O.name_first) || ' ' || trim(O.name_mi) || ' ' ||
                trim(O.name_last), 1, 35) p_name,
              SUBSTR(O.address1, 1, 35) opt_addr1,
                SUBSTR(O.address2, 1, 35) opt_addr2,
                SUBSTR(O.address3, 1, 35) opt_addr3,
                SUBSTR(O.address4, 1, 35) opt_addr4,
                SUBSTR(O.address5, 1, 35) opt_addr5,
                SUBSTR(O.address6, 1, 35) opt_addr6,
              SUBSTR(trim(O.city) || ' ' || trim(O.state) || ' ' ||
              trim(O.zip) || ' ' || trim(O.country), 1, 35) city_state_zip_country,
              trim(O.city) city,
              trim(O.state) state,
              trim(O.zip) zip,
              trim(O.country) country
      from  optionee o
      where o.opt_num = v_opt_num;
      --CQ:PCTUP00210726- Added wire instructions audit for deleted wire info.
      CURSOR c_wire_instruction (
            in_wire_seq_no           TB_XOP_WIRE_INSTRUCTIONS.wire_seq_no%TYPE ) IS
            SELECT ml_brok_acct_num,
                   ssn,
                   plan_num,
                   instr_type,
                   aba_routing_num,
                   swift_routing_code,
                   bank_name,
                   bank_acct_num,
                   name_on_account,
                   bank_addr_1,
                   bank_addr_2,
                   bank_addr_3,
                   city,
                   state,
                   country,
                   zip,
                   bank_ident_num,
                   addtl_info
              FROM TB_XOP_WIRE_INSTRUCTIONS
             WHERE wire_seq_no = in_wire_seq_no
             UNION
             SELECT ml_brok_acct_num,
                   ssn,
                   plan_num,
                   instr_type,
                   aba_routing_num,
                   swift_routing_code,
                   bank_name,
                   bank_acct_num,
                   name_on_account,
                   bank_addr_1,
                   bank_addr_2,
                   bank_addr_3,
                   city,
                   state,
                   country,
                   zip,
                   bank_ident_num,
                   addtl_info
              FROM TB_XOP_WIRE_INSTRUCTIONS_AUDIT
             WHERE wire_seq_no = in_wire_seq_no;

      CURSOR c_order_qty IS
      SELECT order_qty
      FROM tb_xop_order_manager
      WHERE voucher_num = p_voucher_num;
      v_order_qty     tb_xop_order_manager.order_qty%TYPE;

      v_wire_instruction             c_wire_instruction%ROWTYPE;
      v_order_dtls      get_order_dtls%rowtype;
      v_opt_dtls        get_opt_dtls%rowtype;
      v_settle_dt       tb_ml_exer_upload.settle_dt%type;
      v_cusip_num       corp.cusip_num%type;
      v_err_cd          number(12):=0;
      v_err_msg         varchar2(4000);
      v_compy_nme       tb_fc_Compy.compy_nme%type;
      v_ml_sec_num      tb_fc_compy.ml_sec_num%type;
      v_mail_cd         xop_optionee.mail_cd%type;
      v_count1            PLS_INTEGER := 0;
      v_sum_activity_qty  tb_xop_order_manager_t.activity_qty%TYPE;
      v_transact_no       PLS_INTEGER;

      v_ivr_plan_num      tb_fc_compy.ivr_plan_num%TYPE;

      wait_for_more       EXCEPTION;
      exceeds_order_qty   EXCEPTION;
      --Added for CQ# PCTUP00481233
      v_sub_totfee number;
      v_sub_fixedfee1 number;
      v_sub_fixedfee2 number;
      v_sub_fixedfee3 number;
      v_sub_secfee number;
      v_sub_feenum number;
      --Added for CQ# PCTUP00481233
      v_fixedfee1 tb_xop_order_manager_t.fixed_fee1%TYPE; --SPIF# 43161- variable to hold fixed fee1

      BEGIN

          OPEN c_order_qty;
          FETCH c_order_qty INTO v_order_qty;
          CLOSE c_order_qty;
          dbms.output.put_line('completed1');
          IF v_order_qty <> p_activity_qty THEN
            dbms.output.put_line('completed2');
              IF p_status_type = 'EO' AND v_order_qty < p_activity_qty THEN
                  RAISE exceeds_order_qty;
                    dbms.output.put_line('completed3');
              ELSIF p_status_type = 'EO' AND v_order_qty > p_activity_qty THEN
                  -- Partial Execution
                  INSERT INTO tb_xop_hold_multi_orders
                      ( voucher_num
                      , reference_key
                      , seq_num
                      , return_code
                      , reason_desc
                      , status_type
                      , activity_qty
                      , leaves_qty
                      , exec_price
                      , current_status
                      , waiting
                      ,activ_dt )
                  VALUES
                      ( p_voucher_num
                      , p_reference_key
                      , p_seq_num
                      , p_return_code
                      , p_reason_desc
                      , p_status_type
                      , p_activity_qty
                      , p_leaves_qty
                      , p_exec_price
                      , p_current_status
                      , 'Y'
                      ,SYSTIMESTAMP );
                        dbms.output.put_line('completed4');
              ELSE
                  IF p_status_type = 'BE' THEN
                      SELECT COUNT(1) INTO v_count1
                      FROM tb_xop_hold_multi_orders
                      WHERE voucher_num = p_voucher_num;
                        dbms.output.put_line('completed5');
                      IF v_count1 > 0 THEN
                          INSERT INTO tb_xop_hold_multi_orders
                              ( voucher_num
                              , reference_key
                              , seq_num
                              , return_code
                              , reason_desc
                              , status_type
                              , activity_qty
                              , leaves_qty
                              , exec_price
                              , current_status
                              , waiting
                              ,activ_dt)
                          VALUES
                              ( p_voucher_num
                              , p_reference_key
                              , p_seq_num
                              , p_return_code
                              , p_reason_desc
                              , p_status_type
                              , (-1) * p_activity_qty
                              , p_leaves_qty
                              , p_exec_price
                              , p_current_status
                              , 'Y'
                              ,SYSTIMESTAMP );
                          UPDATE tb_xop_hold_multi_orders
                          SET waiting = 'Y'
                          WHERE voucher_num = p_voucher_num;
                            dbms.output.put_line('completed6');
                      END IF;
                  END IF;
              END IF;
                dbms.output.put_line('completed7');
              SELECT SUM(NVL(activity_qty,0)) INTO v_sum_activity_qty
              FROM tb_xop_hold_multi_orders
              WHERE waiting = 'Y'
              AND voucher_num = p_voucher_num;

              IF v_sum_activity_qty > 0 THEN
                  IF v_sum_activity_qty <> v_order_qty THEN
                      RAISE wait_for_more;
                  ELSE
                      -- final order in the partial execution; complete the process
                      UPDATE tb_xop_hold_multi_orders
                      SET waiting = 'N'
                          , process_dt = SYSDATE
                      WHERE voucher_num = p_voucher_num;
                  END IF;
              END IF;
          END IF;

          UPDATE tb_xop_order_manager_t
          SET activ_dt = TO_CHAR( SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) || '.000'
              , dest = p_dest
              , reference_key = p_reference_key
              , seq_num = p_seq_num
              , return_code = p_return_code
              , reason_desc = p_reason_desc
              , status_type = p_status_type
              , activity_qty = v_order_qty --p_activity_qty
              , leaves_qty = 0 --p_leaves_qty
              , exec_price = p_exec_price
          WHERE voucher_num = p_voucher_num;
          dbms.output.put_line('completed8');
          /*
           * SPIF# 43161- Update of current status to PO moved at the end, same as how PROD works.
           */

      IF (p_status_type = 'EO') THEN

        BEGIN
          select cusip_num
            into   v_cusip_num
            from   corp;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                SELECT cusip_num
                  INTO v_cusip_num
                  FROM tb_xop_espp_compy
                 WHERE compy_acronym = replace(replace(user,'CMS'),'_USER');
            WHEN OTHERS THEN
                NULL;
        END;

         select compy_nme,ml_sec_num, ivr_plan_num
         into   v_compy_nme,v_ml_sec_num, v_ivr_plan_num
         from   tb_fc_compy
         where compy_acronym = replace(replace(user,'CMS'),'_USER')
         UNION ALL
         select compy_nme, je_ml_security_number, ivr_plan_num
         from tb_xop_espp_compy
         where compy_acronym = replace(replace(user,'CMS'),'_USER');
      --   where  ivr_plan_num = replace(substr(p_voucher_num,1,7),'SSO','XOP');

        open get_order_dtls(p_voucher_num);
        FETCH get_order_dtls INTO v_order_dtls;

         v_settle_dt := fn_xop_bankopen_bizday(TRUNC(v_order_dtls.exer_date + 1));
         v_settle_dt := fn_xop_bankopen_bizday(TRUNC(v_settle_dt + 1));
         v_settle_dt := fn_xop_bankopen_bizday(TRUNC(v_settle_dt + 1));

         FOR c_mail_cd IN get_mail_cd(v_order_dtls.opt_num)
         LOOP
           v_mail_cd := c_mail_cd.mail_cd;
           -- Do we need to get sp_mail_cd and overlay with the sp_mail_cd in order_manager_t???
         END LOOP;

         OPEN get_opt_dtls(v_order_dtls.opt_num);
         FETCH get_opt_dtls INTO v_opt_dtls;
         CLOSE get_opt_dtls;

        v_fixedfee1 := nvl(v_order_dtls.fixed_fee1, 0);
        /*
         * SPIF# 43161- atleast one handling fee per day of transaction
         */
        v_fixedfee1 := pk_xop_enh_exerupdate.chrg_tranxfixed_fee(v_fixedfee1,
                                                                 v_order_dtls.symbol,
                                                                 v_order_dtls.corp_acronym,
                                                                 v_order_dtls.opt_num);
        --Insert into tb_ml_exer_upload
        --Added for CQ# PCTUP00481233
         Pk_Xop_Transactmodel.GET_FEE(v_order_dtls.GROUP_ID,v_order_dtls.SVC_ID,'SQ',v_order_dtls.opts_exer,
                                      round(v_order_dtls.exec_price, 4),v_sub_totfee,v_sub_fixedfee2,v_sub_fixedfee1,
                                      v_sub_fixedfee3,v_sub_secfee,v_sub_feenum,lpad(v_order_dtls.ssn,9,0));
         v_order_dtls.sec_fee:=v_sub_secfee;
         UPDATE tb_xop_order_manager_t
          SET sec_fee=v_sub_secfee
          WHERE voucher_num = p_voucher_num;
         
          dbms.output.put_line('completed8');
        --- End CQ # PCTUP00481233
        INSERT INTO tb_ml_exer_upload
                              (exer_num,exer_seq,
                               exer_dt, written_flag, backout_flag, output_line,
                               je_flag, opts_exer,
                               tot_tax,shrs_sold,comm_value,tot_fee,
                               mkt_prc,exer_type, soc_sec,
                               name_first, name_mi,
                               name_last,check_addr_1,check_addr_2,check_addr_3,
                               check_addr_4,check_addr_5,city,state,zip,country,
                               city_state_zip_country,
                               p_name,opt_addr1,opt_addr2,opt_addr3,opt_addr4,
                               opt_addr5,opt_addr6,
                               settle_dt,send_to_citibank_flag,
                               dom_chek_distr,foreign_currency_code,
                               distribution_method,wire_seq_no,
                               cusip_num, ml_retail_account,multi_curr_handling_fee,
                               ml_sec_num, corp_name,upd_optionee_addr,
                               outbound_flag, -- make it N
                               corp_symbol,taxwire_approve, -- make it not applicaplabe
                               exersource,
                               mail_cd, sp_mail_cd,
                               backup_withholding,
                               user_id,
                               acct_num_othr)
         VALUES(v_order_dtls.exer_num,0,v_order_dtls.exer_date,'N','N',NULL,
      -- PG 12/12/05 Changed to populate the je_flag based on dom_chek_distr         'D',
      -- PG 12/16/05 JE should not be generated for international checks.. So 'Y' always
                'Y', --je_flag
                v_order_dtls.opts_exer,0,--total tax will be 0.
                v_order_dtls.opts_exer, --shrs_sold,
                v_order_dtls.comm_value,
                --SPIF# 43161- replaced nvl(v_order_dtls.fixed_fee1, 0) with v_fixedfee1
                v_fixedfee1 + nvl(v_order_dtls.fixed_fee3,0) + nvl(v_order_dtls.sec_fee,0), --v_order_dtls.fees_amt,
                round(v_order_dtls.exec_price, 4),v_order_dtls.exer_type,
                LPAD(v_order_dtls.ssn,9,'0'), --v_order_dtls.ssn, Modified by Suresh on 02/08/07 for SPIF # 37210
                v_order_dtls.name_first,v_order_dtls.name_mi,v_order_dtls.name_last,
                v_order_dtls.check_addr_1,v_order_dtls.check_addr_2,v_order_dtls.check_addr_3,
                v_order_dtls.check_addr_4,v_order_dtls.check_addr_5,nvl(v_order_dtls.city,v_opt_dtls.city),
                nvl(v_order_dtls.state,v_opt_dtls.state),nvl(v_order_dtls.zip,v_opt_dtls.zip),
                nvl(v_order_dtls.country,v_opt_dtls.country),
                SUBSTR(nvl(v_order_dtls.city||v_order_dtls.state||v_order_dtls.zip||v_order_dtls.country,
                    v_opt_dtls.city_state_zip_country),1,34), -- COLUMN LENGTH MAX IS 35
                v_opt_dtls.p_name,v_opt_dtls.opt_addr1,v_opt_dtls.opt_addr2,v_opt_dtls.opt_addr3,
                v_opt_dtls.opt_addr4,v_opt_dtls.opt_addr5,v_opt_dtls.opt_addr6,
                v_settle_dt,decode(v_order_dtls.distribution_method,'W','Y','D','Y','C','Y','D'),
                v_order_dtls.dom_chek_distr,v_order_dtls.foreign_currency_code,
                v_order_dtls.distribution_method,v_order_dtls.wire_seq_no,
                v_cusip_num,v_order_dtls.acct_num,v_order_dtls.multi_curr_handling_fee,
      --          nvl(fn_get_sec_num(replace(substr(p_voucher_num,1,7),'SSO','XOP'),v_order_dtls.symbol),v_ml_sec_num),
                nvl(fn_get_sec_num(v_ivr_plan_num, v_order_dtls.symbol),v_ml_sec_num),
                v_compy_nme,v_order_dtls.upd_optionee_addr,
                'N', --'N' is for outbound flag.
                v_order_dtls.symbol,'D', --'D' for taxwires disabled
                'S', -- 'S' for source being subsequent sale.
                v_mail_cd,  --need to get mail_Cd??
                v_order_dtls.sp_mail_cd,v_order_dtls.backup_withholding,
                user,v_order_dtls.acct_num_othr);

      IF (NVL(v_order_dtls.wire_seq_no,0) > 0) THEN

         OPEN c_wire_instruction(v_order_dtls.wire_seq_no);
         FETCH c_wire_instruction INTO v_wire_instruction;
         CLOSE c_wire_instruction;

        INSERT INTO TB_XOP_TRANSACT_WIRE_INSTR
                             (user_id, exer_num, wire_seq_no, ml_brok_acct_num, ssn,
                              plan_num, instr_type, aba_routing_num,
                              swift_routing_code, bank_name, bank_acct_num,
                              name_on_account, bank_addr_1, bank_addr_2, bank_addr_3,
                              city, state, country, zip, bank_ident_num, addtl_info )
                          VALUES
                             (USER,v_order_dtls.exer_num, v_order_dtls.wire_seq_no,
                              v_wire_instruction.ml_brok_acct_num, v_wire_instruction.ssn,
                              v_wire_instruction.plan_num, v_wire_instruction.instr_type, v_wire_instruction.aba_routing_num,
                              v_wire_instruction.swift_routing_code, v_wire_instruction.bank_name, v_wire_instruction.bank_acct_num,
                              v_wire_instruction.name_on_account, v_wire_instruction.bank_addr_1, v_wire_instruction.bank_addr_2,
                              v_wire_instruction.bank_addr_3, v_wire_instruction.city, v_wire_instruction.state, v_wire_instruction.country, v_wire_instruction.zip,
                               v_wire_instruction.bank_ident_num, v_wire_instruction.addtl_info);
              dbms.output.put_line('completed10');
      END IF;


      /* PG 01/05/06 Call the check conversion only for international checks and wires */
      IF v_order_dtls.dom_chek_distr = 'N' AND v_order_dtls.distribution_method IN ('W', 'D', 'C') THEN
          /* PG 12/20/05 To process subsequent sales with wire or foreign currency distribution */
           v_transact_no := 88;
           pk_xop_citibank_forex.pr_cashconversion_ins_request( v_order_dtls.acct_num
                                                                , LPAD(v_order_dtls.ssn,9,'0') --v_order_dtls.ssn, Modified by Suresh on 02/15/07 for SPIF # 37210
                                                                , v_order_dtls.distribution_method
                                                                , v_order_dtls.foreign_currency_code
                                                                , v_order_dtls.wire_seq_no
                                                                , ROUND((v_order_dtls.opts_exer * round(v_order_dtls.exec_price, 4)),2)
                                                                  - ROUND(v_order_dtls.comm_value,2)
                                                                  - ROUND(nvl(v_order_dtls.fixed_fee1,0) + nvl(v_order_dtls.fixed_fee3,0) + nvl(v_order_dtls.sec_fee,0),2)
                                                                  - nvl(v_order_dtls.multi_curr_handling_fee,0)
                                                                  - nvl(v_order_dtls.backup_withholding,0) -- net proceeds
                                                                , v_order_dtls.multi_curr_handling_fee --handling fee
                      --Modified by Bhaskar/Suresh on 03/02/2006    --, v_order_dtls.login_name
                                                                , CASE v_order_dtls.login_name WHEN 'CLIENT/' THEN v_order_dtls.login_name||'SSO' ELSE v_order_dtls.login_name||'/SSO' END
                                                                , v_transact_no -- transact_no as place holder for exer_type 88
                                                                , v_order_dtls.check_addr_1
                                                                , v_order_dtls.check_addr_2
                                                                , v_order_dtls.check_addr_3
                                                                , v_order_dtls.check_addr_4
                                                                , v_order_dtls.upd_optionee_addr
                                                                , v_order_dtls.city
                                                                , v_order_dtls.state
                                                                , v_order_dtls.zip
                                                                , v_order_dtls.country
                                                                , v_order_dtls.login_ipaddress
                                                                , v_order_dtls.fcnum
                                                                , v_order_dtls.opt_num
                                                                , v_settle_dt,
                                                                p_exer_num => v_order_dtls.exer_num
                                                                );
      END IF;

      COMMIT;

      close get_order_dtls;

      END IF;

      /*
        * SPIF# 43161- Update to current status into PO is moved to this part, same as PROD.
        */
      UPDATE tb_xop_order_manager_t
      SET exec_dttime = decode(p_status_type,'EO',to_char(sysdate,'DD-MON-YYYY HH24:mi:ss'),null)
           , exer_date  = TRUNC(SYSDATE)
           , cancel_dttime = decode(p_status_type,'CX',to_char(sysdate,'DD-MON-YYYY HH24:mi:ss'),'UR',to_char(sysdate,'DD-MON-YYYY HH24:mi:ss'),null)
           , current_status = 'PO'
           , sum_status = decode(p_status_type,'EO','X','C')
           , sum_stat_dttime = SYSDATE
      WHERE voucher_num = p_voucher_num;

            v_err_cd := sqlcode;
            v_err_msg := sqlerrm;

            open p_err_cur for select v_err_cd err_code,v_err_msg err_msg from dual;

      EXCEPTION
              dbms.output.put_line('completed200');
      /* PG 02/10 handled 'partial fill order' exception */
        when wait_for_more then
            v_err_cd := 0;
            v_err_msg := 'ORA-0000: normal, successful completion';
            pr_xop_log_errors('Partially filled; Waiting for more - Activity qty:' || to_char(v_sum_activity_qty)|| ' pk_xop_subsales.pr_process_exer');
            open p_err_cur for select v_err_cd err_code,v_err_msg err_msg from dual;
        when exceeds_order_qty then
            v_err_cd := sqlcode;
            v_err_msg := sqlerrm;
            open p_err_cur for select v_err_cd err_code,v_err_msg err_msg from dual;
            pr_xop_log_errors('Activity quantity exceeded the Order_qty - Activity qty: ' || to_char(p_activity_qty) || ' pk_xop_subsales.pr_process_exer');
        when others then
            v_err_cd := sqlcode;
            v_err_msg := sqlerrm;
            open p_err_cur for select v_err_cd err_code,v_err_msg err_msg from dual;
            pr_xop_log_errors('ErrorCode '||SQLCODE||' with ErrorMessage as '||SQLERRM||' has occured for '||user||'pk_xop_subsales.pr_process_exer');
                  dbms.output.put_line('completed125');
      END pr_process_exer;
      }

      Hi friends, any1 help me out how to define the ref-cursor in declaration section, and how to find at what situation the constraint error occurred..
        • 1. Third thread opend
          Marwim
          And why don't you post at one of the other threads?
          and how to find at what situation the constraint error occurred..
          And which of the solutions suggested in the other threads did you try?

          {message:id=10770767}
          {message:id=10770553}

          Regards
          Marcus

          Edited by: Marwim on 03.01.2013 14:36

          BTW: From your profile
          Total Questions:      22 (19 unresolved)
          It seems you are not interested to mark your questions as answered or are they still unanswered? Maybe when you open new threads instead of following a single one.
          • 2. Re: How to debug and find the exact constraint violation error
            Solomon Yakobson
            Isn't it in the error message?
            {122712 14:22:36:ErrorCode -1 with ErrorMessage as ORA-00001: unique constraint (<font color=red>OPS$CMS.PK_TB_ML_EXER_UPLOAD</font>) violated has occured for [SSO16063259009], [CMSCOST_USER] pk_xop_subsales.pr_process_exer }
            SY.
            • 3. Re: How to debug and find the exact constraint violation error
              959687
              yes, that is my error i got..
              • 4. Re: How to debug and find the exact constraint violation error
                959687
                Hi,.

                Friends plz help me out ..how to debug and find the exact position of the constraint violation..thank you for the help..
                • 5. Re: How to debug and find the exact constraint violation error
                  Purvesh K
                  956684 wrote:
                  Hi,.

                  Friends plz help me out ..how to debug and find the exact position of the constraint violation..thank you for the help..
                  There isn't a way to trace it unless you have caught the exception raised.

                  As a way to start the debug, you will have to monitor
                  1. All DML's against the Table on which you have constraint. More specifically, the DML's that act on the column you have constraint on.
                  2. Use Exception Handling, to log the Error and the data that causes the constraint to fail.
                  3. Do not forget to monitor the Triggers, if they are used, that would write some data into the column you have constraint on.

                  Or

                  Another way to trace is:
                  select *
                    from user_source
                  where lower(text) like '%your_table_name%';
                  order by type, name, line;
                  Look at the lines, exclude that are in Declaration or in SELECT statements and target the DML's.

                  Looking at the un-formatted code you posted, this statement looks like a culprit.
                  INSERT INTO tb_ml_exer_upload
                  (exer_num,exer_seq,
                  exer_dt, written_flag, backout_flag, output_line,
                  je_flag, opts_exer,
                  tot_tax,shrs_sold,comm_value,tot_fee,
                  mkt_prc,exer_type, soc_sec,
                  name_first, name_mi,
                  name_last,check_addr_1,check_addr_2,check_addr_3,
                  check_addr_4,check_addr_5,city,state,zip,country,
                  city_state_zip_country,
                  p_name,opt_addr1,opt_addr2,opt_addr3,opt_addr4,
                  opt_addr5,opt_addr6,
                  settle_dt,send_to_citibank_flag,
                  dom_chek_distr,foreign_currency_code,
                  distribution_method,wire_seq_no,
                  cusip_num, ml_retail_account,multi_curr_handling_fee,
                  ml_sec_num, corp_name,upd_optionee_addr,
                  outbound_flag, -- make it N
                  corp_symbol,taxwire_approve, -- make it not applicaplabe
                  exersource,
                  mail_cd, sp_mail_cd,
                  backup_withholding,
                  user_id,
                  acct_num_othr)
                  VALUES(v_order_dtls.exer_num,0,v_order_dtls.exer_date,'N','N',NULL,
                  -- PG 12/12/05 Changed to populate the je_flag based on dom_chek_distr 'D',
                  -- PG 12/16/05 JE should not be generated for international checks.. So 'Y' always
                  'Y', --je_flag
                  v_order_dtls.opts_exer,0,--total tax will be 0.
                  v_order_dtls.opts_exer, --shrs_sold,
                  v_order_dtls.comm_value,
                  --SPIF# 43161- replaced nvl(v_order_dtls.fixed_fee1, 0) with v_fixedfee1
                  v_fixedfee1 + nvl(v_order_dtls.fixed_fee3,0) + nvl(v_order_dtls.sec_fee,0), --v_order_dtls.fees_amt,
                  round(v_order_dtls.exec_price, 4),v_order_dtls.exer_type,
                  LPAD(v_order_dtls.ssn,9,'0'), --v_order_dtls.ssn, Modified by Suresh on 02/08/07 for SPIF # 37210
                  v_order_dtls.name_first,v_order_dtls.name_mi,v_order_dtls.name_last,
                  v_order_dtls.check_addr_1,v_order_dtls.check_addr_2,v_order_dtls.check_addr_3,
                  v_order_dtls.check_addr_4,v_order_dtls.check_addr_5,nvl(v_order_dtls.city,v_opt_dtls.city),
                  nvl(v_order_dtls.state,v_opt_dtls.state),nvl(v_order_dtls.zip,v_opt_dtls.zip),
                  nvl(v_order_dtls.country,v_opt_dtls.country),
                  SUBSTR(nvl(v_order_dtls.city||v_order_dtls.state||v_order_dtls.zip||v_order_dtls.country,
                  v_opt_dtls.city_state_zip_country),1,34), -- COLUMN LENGTH MAX IS 35
                  v_opt_dtls.p_name,v_opt_dtls.opt_addr1,v_opt_dtls.opt_addr2,v_opt_dtls.opt_addr3,
                  v_opt_dtls.opt_addr4,v_opt_dtls.opt_addr5,v_opt_dtls.opt_addr6,
                  v_settle_dt,decode(v_order_dtls.distribution_method,'W','Y','D','Y','C','Y','D'),
                  v_order_dtls.dom_chek_distr,v_order_dtls.foreign_currency_code,
                  v_order_dtls.distribution_method,v_order_dtls.wire_seq_no,
                  v_cusip_num,v_order_dtls.acct_num,v_order_dtls.multi_curr_handling_fee,
                  -- nvl(fn_get_sec_num(replace(substr(p_voucher_num,1,7),'SSO','XOP'),v_order_dtls.symbol),v_ml_sec_num),
                  nvl(fn_get_sec_num(v_ivr_plan_num, v_order_dtls.symbol),v_ml_sec_num),
                  v_compy_nme,v_order_dtls.upd_optionee_addr,
                  'N', --'N' is for outbound flag.
                  v_order_dtls.symbol,'D', --'D' for taxwires disabled
                  'S', -- 'S' for source being subsequent sale.
                  v_mail_cd, --need to get mail_Cd??
                  v_order_dtls.sp_mail_cd,v_order_dtls.backup_withholding,
                  user,v_order_dtls.acct_num_othr);
                  What is the Table structure of tb_ml_exer_upload and what columns do you have constraints on?
                  Which column of the table tb_ml_exer_upload is your Primary Key (because constraint name mentions pk_tb_ml_exer_upload)?

                  Edited by: Purvesh K on Jan 4, 2013 12:57 PM
                  • 6. Re: How to debug and find the exact constraint violation error
                    Marwim
                    I don't think he will tell us anything useful since he didn't follow any advice given in the other threads.
                    • 7. Re: How to debug and find the exact constraint violation error
                      Purvesh K
                      Marwim wrote:
                      I don't think he will tell us anything useful since he didn't follow any advice given in the other threads.
                      :)
                      That be OP's choice then. (s)he has to help us to get help.
                      • 8. Re: How to debug and find the exact constraint violation error
                        Marwim
                        :)
                        That be OP's choice then. (s)he has to help us to get help.
                        And that seems to be the problem, the third thread about this topic without showing any attempt to implement any of the suggested solutions. Whenever (s)he will post more informations I will try to help. Until then I can only point to the given suggestions, this also to help others to save their time to help those who are willing to cooperate :-)

                        Marcus
                        • 9. Re: How to debug and find the exact constraint violation error
                          959687
                          Hi Purvesh K,

                          Thank you very much as per your guidance, i found the place where i am struck with..thank you so much