This discussion is archived
10 Replies Latest reply: Mar 21, 2013 2:43 AM by Purvesh K RSS

Trigger Issue

user11876003 Newbie
Currently Being Moderated
In case of following trigger

CREATE OR REPLACE TRIGGER gwp_trigger
AFTER INSERT OR UPDATE
ON T_DEBIT_CREDIT_NOTE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
--vdebitnumber VARCHAR2(50) := :NEW.DCN_DEBIT_CREDIT_NOTE_NO;
BEGIN
gwp_report_pak_trigger(vdebitnumber => :NEW.DCN_DEBIT_CREDIT_NOTE_NO);

END gwp_trigger;


It is not working AFTER INSERT But It work UPDATE can anyone explain why trigger is not fire after INSERT
  • 1. Re: Trigger Issue
    ranit B Expert
    Currently Being Moderated
    What is - gwp_report_pak_trigger ? Purpose?

    Try adding an EXCEPTION block to the trigger check if some exception is happening.
  • 2. Re: Trigger Issue
    996818 Newbie
    Currently Being Moderated
    These virtual columns :NEW & :OLD is not applicable for INSERT statement because insert itself new and dont have a old value, Hence your insert will not work.
  • 3. Re: Trigger Issue
    Karthick_Arp Guru
    Currently Being Moderated
    user11876003 wrote:
    In case of following trigger

    CREATE OR REPLACE TRIGGER gwp_trigger
    AFTER INSERT OR UPDATE
    ON T_DEBIT_CREDIT_NOTE
    REFERENCING OLD AS OLD NEW AS NEW
    FOR EACH ROW
    DECLARE
    --vdebitnumber VARCHAR2(50) := :NEW.DCN_DEBIT_CREDIT_NOTE_NO;
    BEGIN
    gwp_report_pak_trigger(vdebitnumber => :NEW.DCN_DEBIT_CREDIT_NOTE_NO);

    END gwp_trigger;


    It is not working AFTER INSERT But It work UPDATE can anyone explain why trigger is not fire after INSERT
    How did you determine that the trigger is not working?
  • 4. Re: Trigger Issue
    S10390 Journeyer
    Currently Being Moderated
    --vdebitnumber VARCHAR2(50) := :NEW.DCN_DEBIT_CREDIT_NOTE_NO;

    You have commented the variable. And try to add an exception block to catch the exception on what going wrong with the insert.
  • 5. Re: Trigger Issue
    BCV Journeyer
    Currently Being Moderated
    Hi,

    Its Perfectly working for me ,Check Your Codings With my example.

    Create one Package,
    CREATE OR REPLACE PACKAGE psdba.cv_dummy_pkg
    IS
       PROCEDURE cv_proce2 (lv_n_empno IN NUMBER);
    END;
    /
    
    CREATE OR REPLACE PACKAGE BODY PSDBA.cv_dummy_pkg
    AS
       
    
       PROCEDURE cv_proce2 (lv_n_empno IN NUMBER)
       AS
       BEGIN
          insert into cv(salary) values (lv_n_empno);
    --        FROM CV
    --       WHERE empno = lv_n_empno;
       EXCEPTION
          WHEN OTHERS
          THEN
             raise_application_error (-20001, 'NOT A VALID DATA');
       END;
    
       
    END;
    /
    After That Create Trigger,
    CREATE OR REPLACE TRIGGER mt_idu_tr
       AFTER INSERT OR DELETE OR UPDATE
       ON mt
       REFERENCING NEW AS NEW OLD AS OLD
       FOR EACH ROW
    BEGIN
       cv_dummy_pkg.cv_proce2 (lv_n_empno => :NEW.a);
    --commit;
    --end;
    END mt_idu_tr;
    Then update and Insert into table 'mt'.
    Coded Operation Is Done through Package Which Is In Trigger.



    Regards
    BCV.

    Edited by: BCV on Mar 21, 2013 11:41 AM
  • 6. Re: Trigger Issue
    Purvesh K Guru
    Currently Being Moderated
    How do you ascertain, it does not work?

    See below, it works for me:
    drop table test_table;
    drop table test_table_temp;
    
    create table test_table (col number);
    create table test_table_temp (col varchar2(50));
    
    create or replace
    trigger trg_test_table
    after insert or update
    on test_table
    for each row
    begin
      insert into test_table_temp values ('New Value of Col is ' || :new.col);
    end trg_test_table;
    
    insert into test_table values (1);
    insert into test_table values (2);
    update test_Table set col = 100 * col where col = 1;
    
    select *
      from test_table_temp;
    
    COL                                                
    -------------------------------------------------- 
    New Value of Col is 1                              
    New Value of Col is 2                              
    New Value of Col is 100
    Perhaps, the procedure gwp_report_pak_trigger, is not processing the data you are passing as parameter due to some conditional by-pass or there is an exception that is being suppressed like when others then dbms_output.put_line(SQLERRM) and is not followed by a RAISE.

    So, unless you post us how the Trigger after Insert does not work, it would not be possible to provide much help to you. Also, it would be lot helpful if you could post the code for gwp_report_pak_trigger.
  • 7. Re: Trigger Issue
    user11876003 Newbie
    Currently Being Moderated
    Thanks for your reply ,
    When I update a record in T_DEBIT_CREDIT_NOTE table then that record is insert into T_REPORT_DNCN_GWP_THU table,
    but after INSERT in to T_DEBIT_CREDIT_NOTE table
    doesn't Insert record to T_REPORT_DNCN_GWP_THU table , therefore I mention trigger is not working for INSERT
  • 8. Re: Trigger Issue
    Purvesh K Guru
    Currently Being Moderated
    user11876003 wrote:
    Thanks for your reply ,
    When I update a record in T_DEBIT_CREDIT_NOTE table then that record is insert into T_REPORT_DNCN_GWP_THU table,
    but after INSERT in to T_DEBIT_CREDIT_NOTE table
    doesn't Insert record to T_REPORT_DNCN_GWP_THU table , therefore I mention trigger is not working for INSERT
    Okay. As I mentioned earlier, there is no reason why an Update should work and Insert should not. Not, at least, you have some condition to by-pass the DML's or perhaps an exception that is preventing the DML modified data from logging into your destination table.
    Just a thought in my mind, since you did not mention, Does the table T_REPORT_DNCN_GWP_THU exist in same schema?

    Also, I shall stick to what I said earlier, unless we see the procedure that logs data into T_REPORT_DNCN_GWP_THU table, we would not be in a position to comment on the reason of happening.

    To demonstrate an example on your lines, to log message into a table using a Procedure:
    drop table test_table;
    drop table test_table_temp;
     
    create table test_table (col number);
    create table test_table_temp (col varchar2(50));
    
    create or replace procedure logMessage(msg varchar2)
    is
    begin
      insert into test_table_temp values (msg);
    end;
    
    create or replace
    trigger trg_test_table
    after insert or update
    on test_table
    for each row
    begin
      logMessage('New Value of Col is ' || :new.col);
    end trg_test_table;
     
    insert into test_table values (1);
    insert into test_table values (2);
    update test_table set col = 100 * col where col = 1;
    
    select *
      from test_table_temp;
    
    COL                                                
    -------------------------------------------------- 
    New Value of Col is 1                              
    New Value of Col is 2                              
    New Value of Col is 100 
    But now, if you have procedure implemented in below way, the exception logged may have been suppressed.
    create or replace procedure logMessage(msg varchar2)
    is
    begin
      insert into test_table_temp values (msg);
    exception
      when others then
        dbms_output.put_line(SQLERRM);
    end;
    Did you try debugging the procedure? What was the outcome?
  • 9. Re: Trigger Issue
    user11876003 Newbie
    Currently Being Moderated
    In my procedure gwp_report_pak_trigger(vdebitnumber => :NEW.DCN_DEBIT_CREDIT_NOTE_NO);
    like below


    CREATE OR REPLACE PROCEDURE gwp_report_pak_trigger(vDebitNumber STRING)
    IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_agent_code VARCHAR2(80);
    v_stake_code VARCHAR2(100);
    v_agent_name varchar2(2500);
    v_debit_no VARCHAR2(220);
    v_policy_no VARCHAR2(200);
    v_pol_start VARCHAR2(200);
    v_deb_date DATE;
    v_prod_code VARCHAR2(200);
    v_pol_status VARCHAR2(2500);
    v_pol_hol_name VARCHAR2(2500);
    v_dncn_indicator VARCHAR2(2050);
    --v_query_column VARCHAR2(2500);
    v_basic NUMBER(20,6);
    v_srcc NUMBER(20,6);
    v_tc NUMBER(20,6);
    v_gwp NUMBER(20,6);
    v_adminfee NUMBER(20,6);
    v_tax NUMBER(20,6);
    v_stamp NUMBER(20,6);
    v_policyfee NUMBER(20,6);
    v_nbt NUMBER(20,6);
    v_crsf NUMBER(20,6);
    v_total NUMBER(20,6);
    v_assurance_code VARCHAR2(25);
    v_matchvoucher VARCHAR2(1500);
    v_third VARCHAR2(50);
    v_curr VARCHAR2(3);
    v_rate NUMBER(20,6);
    v_pol_code VARCHAR2(20);
    v_address VARCHAR2(300);
    v_si NUMBER(20,6);
    v_vehicle VARCHAR2(300);
    v_match_unmatch VARCHAR2(10);
    v_daterange VARCHAR2(20);
    v_prod_name VARCHAR2(20);
    v_narration VARCHAR2(3000);
    v_unadjusted_amt NUMBER(30,6);
    v_sirange VARCHAR2(3);
    v_usage VARCHAR2(50);
    v_risktype VARCHAR2(100);
    v_bis_party VARCHAR2(200);
    v_chassis VARCHAR2(350);
    v_engin VARCHAR2(350);
    vCreditStatus VARCHAR2(350);
    vStatusWord VARCHAR2(250);
    vPayingParty VARCHAR2(350);

    CURSOR vgwpList
    IS
    SELECT distinct
    AGENT_NAME,
    STAK_CODE,
    AGENT_CODE,
    DEBIT_DATE,
    DNCN_NO,
    PRODUCT_CODE,
    POLICY_NO,
    DECODE(substr(DNCN_NO,1,2),'DN',
    DECODE(END_TYPE,'RN','RENEWAL','RE','REINSTATE','IS','NEW','AE','ADDITION','CA','CANCEL','DE','DELETION','PP','PPW-CANC','N/A'),'CN',
    DECODE(END_TYPE,'RN','RENEWAL','RE','REINSTATE','IS','NEW','AE','ADDITION','CA','CANCEL','DE','DELETION','PP','PPW-CANC','N/A')) POL_STATUS,
    POL_HOL_NAME,
    DNCN_INDICATOR,
    BASIC,
    SRCC,
    TC,
    ADMIN_FEE,
    TAX,
    STAMP,
    POLICY_FEE,
    NBT,
    CRSF,
    ASSCODE,
    MATCH_VOUCHER,
    THD,
    CURRANCY,
    RATE,
    POL_HOL_CODE,
    ADDRESS,
    SI,
    VEHNO,
    POLICY_START,
    MATCH_IND,
    TRUNC((((86400*(SYSDATE - DEBIT_DATE))/60)/60)/24) DATE_RANGE,
    DECODE(PRODUCT_CODE,'TEL','WCI','TME','MED','TCRME','MED','TMEI','MED','THG','MED',LOB_CODE) DEPT,
    NARRATION_DCN,
    UNADJUST_AMT,
    DEB_TOTAL
    ,(CASE WHEN SI <500000 THEN 'A'
    WHEN SI >=500000 and SI<=1000000 THEN 'B'
    WHEN SI >1000000 and SI <=1500000 THEN 'C'
    WHEN SI >1500000 and SI <=2000000 THEN 'D'
    WHEN SI >2000000 and SI <=2500000 THEN 'E'
    WHEN SI >2500000 and SI <=3000000 THEN 'F'
    WHEN SI >3000000 and SI <=4000000 THEN 'G'
    WHEN SI >4000000 and SI <=5000000 THEN 'H'
    WHEN SI >5000000 and SI <=6000000 THEN 'I'
    WHEN SI >6000000 and SI <=7000000 THEN 'J'
    WHEN SI >7000000 and SI <=10000000 THEN 'K'
    WHEN SI >10000000 THEN 'L' END ) SI_RANGE ,
    DECODE (CLASS_OF_VEHICLE,'2','Hiring','1','Private','3','Rent','4','Agricultural Usage','5','Route') USAGE_
    ,RISK_TYPE,
    BIS_PARTY,
    CHASSIS_NO,
    ENGINE_NO

    FROM(
    SELECT
    POL.POL_POLICY_NUMBER POLICY_NO,
    DCN.DCN_DEBIT_CREDIT_NOTE_NO DNCN_NO ,
    nvl(trunc(( SELECT
    max(pcs.pcs_payment_due_date)
    FROM
    t_payment_cycle pcs
    WHERE
    pcs.pcs_pmt_pol_policy_id = pol.pol_policy_id
    AND pcs.pcs_pmt_policy_payment_id IS NOT NULL
    AND pcs.pcs_payment_reference_no = dcn.dcn_debit_credit_note_no
    )),dcn.dcn_date) POLICY_START,
    DCN.DCN_DATE DEBIT_DATE,
    DCN.DCN_DR_CR_INDICATOR DNCN_INDICATOR ,
    DCN.DCN_NARRATION NARRATION,
    DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',DCN.DCN_TRAN_AMOUNT , (-1)*DCN.DCN_TRAN_AMOUNT) DEB_TOTAL,
    PROD.NAME PRODUCT_NAME,
    PROD.PRODUCT_CODE PRODUCT_CODE,
    NVL((SELECT REPLACE(REPLACE(REPLACE(PPT.PPA_PTY_PARTY_CODE,chr(10),''),chr(13),''),'-','')
    FROM T_POLICY_PARTY PPT
    WHERE PPT.PPA_POL_POLICY_ID = POL.POL_POLICY_ID
    AND PPT.PPA_SHR_STAKE_HOLDER_FN_CODE IN ('AGENT','BROKER','DIRECT','HNB_BANK','HNB_STAFF','DIR_SP','DIR_SPIND','ASSU_STAFF','Agent')
    AND PPT.PPA_EFFECTIVE_END_DATE IS NULL
    AND rownum=1),'-') AGENT_CODE,
    NVL((SELECT PPT.ppa_pty_party_name
    FROM T_POLICY_PARTY PPT
    WHERE PPT.PPA_POL_POLICY_ID = POL.POL_POLICY_ID
    AND PPT.PPA_SHR_STAKE_HOLDER_FN_CODE IN ('AGENT','BROKER','DIRECT','HNB_BANK','HNB_STAFF','DIR_SP','DIR_SPIND','ASSU_STAFF','Agent')
    AND PPT.PPA_EFFECTIVE_END_DATE IS NULL
    AND rownum=1),'-') AGENT_NAME,
    NVL((SELECT PPT.ppa_shr_stake_holder_fn_code
    FROM T_POLICY_PARTY PPT
    WHERE PPT.PPA_POL_POLICY_ID = POL.POL_POLICY_ID
    AND PPT.PPA_SHR_STAKE_HOLDER_FN_CODE IN ('AGENT','BROKER','DIRECT','HNB_BANK','HNB_STAFF','DIR_SP','DIR_SPIND','ASSU_STAFF','Agent')
    AND PPT.PPA_EFFECTIVE_END_DATE IS NULL
    AND rownum=1),'-') STAK_CODE,
    ( SELECT pp.PPA_PTY_PARTY_NAME
    FROM t_policy_party pp
    WHERE pp.ppa_pol_policy_id=pol.pol_policy_id
    AND pp.ppa_effective_end_date IS NULL
    AND pp.ppa_shr_stake_holder_fn_code = 'POLICY-HOL'
    and rownum=1) POL_HOL_NAME,
    ( select p.value_description FROM
    t_policy_risk_property t,
    t_parameter_value p
    WHERE t.prp_prk_pol_policy_id= pol.pol_policy_id
    AND p.parameter_id = t.prp_par_parameter_id
    AND p.alphanumeric_code_value =t.prp_value_alpha_code
    AND t.prp_effective_end_date IS NULL
    AND t.prp_par_parameter_name like 'Vehicle Type %' AND ROWNUM=1
    ) RISK_TYPE,
    substr(dcn.dcn_endorsement_number,1,2) END_TYPE,
    nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
    FROM
    t_accounting_trn_detail atd
    ,t_account acc
    WHERE
    atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
    AND acc.acc_account_id=atd.atd_acc_account_id
    AND acc.acc_account_type = 'IPMO02' and rownum=1),0.00) BASIC,
    nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
    FROM
    t_accounting_trn_detail atd
    ,t_account acc
    WHERE
    atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
    AND acc.acc_account_id=atd.atd_acc_account_id
    AND acc.acc_account_type = 'IPMO04' and rownum=1),0.00) SRCC,
    nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
    FROM
    t_accounting_trn_detail atd
    ,t_account acc
    WHERE
    atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
    AND acc.acc_account_id=atd.atd_acc_account_id
    AND acc.acc_account_type = 'IPMO05' and rownum=1),0.00) TC,
    nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
    FROM
    t_accounting_trn_detail atd
    ,t_account acc
    WHERE
    atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
    AND acc.acc_account_id=atd.atd_acc_account_id
    AND acc.acc_account_type = 'LCLO31' and rownum=1),0.00) ADMIN_FEE ,
    nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
    FROM
    t_accounting_trn_detail atd
    ,t_account acc
    WHERE
    atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
    AND acc.acc_account_id=atd.atd_acc_account_id
    AND acc.acc_account_type = 'LCLO15' and rownum=1),0.00) TAX ,
    nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
    FROM
    t_accounting_trn_detail atd
    ,t_account acc
    WHERE
    atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
    AND acc.acc_account_id=atd.atd_acc_account_id
    AND acc.acc_account_type = 'LCLO05' and rownum=1),0.00) STAMP ,
    nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
    FROM
    t_accounting_trn_detail atd
    ,t_account acc
    WHERE
    atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
    AND acc.acc_account_id=atd.atd_acc_account_id
    AND acc.acc_account_type = 'IOIO01' and rownum=1),0.00) POLICY_FEE,
    nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
    FROM
    t_accounting_trn_detail atd
    ,t_account acc
    WHERE
    atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
    AND acc.acc_account_id=atd.atd_acc_account_id
    AND acc.acc_account_type = 'LCLO03' and rownum=1),0.00) NBT,
    nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
    FROM
    t_accounting_trn_detail atd
    ,t_account acc
    WHERE
    atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
    AND acc.acc_account_id=atd.atd_acc_account_id
    AND acc.acc_account_type = 'LCLO04'),0.00) CRSF,
    nvl(( SELECT pop.pop_value_alpha_code
    FROM t_policy_property pop
    WHERE pop.pop_pol_policy_id=pol.pol_policy_id
    AND pop.pop_effective_end_date IS NULL
    AND pop.pop_par_parameter_name = 'Assurance Code'),POL.pol_policy_branch_code) ASSCODE,
    (select t.prp_value_alpha_code from t_policy_risk_property t
    WHERE t.prp_prk_pol_policy_id=pol.pol_policy_id
    AND t.prp_effective_end_date IS NULL
    AND t.prp_par_parameter_name like 'Class of vehicle%' AND ROWNUM=1) CLASS_OF_VEHICLE,
    GetRVNumbersForDNthu(DCN.dcn_debit_credit_note_no) MATCH_VOUCHER,
    decode((SELECT c.rv_meaning FROM cg_ref_codes c WHERE c.rv_domain='LOB'AND c.rv_low_value=PROD.lob and rownum=1) ,'MOTOR INSURANCE',decode((SELECT count(prc.prc_policy_risk_cover_id)
    FROM t_policy_risk_cover prc, t_cover cvr, t_policy_risk_cover_detail rcd
    WHERE prc.prc_prk_pol_policy_id=pol.pol_policy_id AND prc.prc_cvr_cover_id=cvr.cover_id
    AND cvr.cover_code='COMPR' AND rcd.rcd_prc_prk_policy_risk_id=prc.prc_prk_policy_risk_id
    and rcd.rcd_prc_prk_pol_policy_id=prc.prc_prk_pol_policy_id
    AND rcd.rcd_prc_policy_risk_cover_id=prc.prc_policy_risk_cover_id
    AND rcd.rcd_effective_end_date IS NULL and rownum=1),0,'THIRD_PARTY','COMPR'),'') THD,
    dcn.dcn_tran_currency_rate RATE
    ,dcn.dcn_transaction_currency CURRANCY,
    ( SELECT pp.ppa_pty_party_code
    FROM t_policy_party pp
    WHERE pp.ppa_pol_policy_id=pol.pol_policy_id
    AND pp.ppa_effective_end_date IS NULL
    AND pp.ppa_shr_stake_holder_fn_code = 'POLICY-HOL'
    and rownum=1) POL_HOL_CODE,
    NVL((select con.con_address_line_1||con.con_address_line_2||con.con_address_line_3 InsuredAddress
    from t_policy_party pr1 ,t_contact con
    where pr1.ppa_pol_policy_id =pol.pol_policy_id
    and pr1.ppa_shr_stake_holder_fn_code = 'POLICY-HOL'
    and pr1.ppa_effective_end_date is null
    and pr1.ppa_pfy_pty_party_id=con.con_pty_party_id
    and con.con_effective_end_date is null
    and con.con_type_of_contact like 'Mailing%'
    and rownum=1),' ') ADDRESS,
    (SELECT pdl.pod_sum_insured_assured FROM t_policy_detail pdl
    WHERE pdl.pod_pol_policy_id=POL.POL_POLICY_ID
    and pdl.pod_effective_end_date IS NULL and pdl.pod_effective_end_date IS NULL and rownum=1) SI
    ,nvl((SELECT
    prp.prp_value_description
    FROM
    t_policy_risk_property prp
    WHERE
    prp.prp_prk_pol_policy_id=pol.pol_policy_id
    AND prp.prp_effective_end_date IS NULL
    AND prp.prp_prk_policy_risk_id IS NOT NULL
    AND prp.prp_par_parameter_name ='Registration Number'
    AND ROWNUM=1),'N/A') VEHNO,
    DCN.dcn_matched_ind MATCH_IND,
    DECODE(PROD.LOB,'01','FIRE','02','MISC','03','MARINE','04','MOTOR','05','ENGG') LOB_CODE,
    DCN.dcn_narration NARRATION_DCN,
    DCN.dcn_unadjusted_amount UNADJUST_AMT,
    (SELECT PRV.VALUE_DESCRIPTION
    FROM T_POLICY_PROPERTY polp,
    T_PARAMETER_VALUE PRV
    WHERE polp.POP_POL_POLICY_ID=pol.pol_policy_id
    AND POLP.POP_PAR_PARAMETER_ID=PRV.PARAMETER_ID
    AND PRV.ALPHANUMERIC_CODE_VALUE=polp.pop_value_alpha_code
    AND polp.pop_par_parameter_name ='Business Party'
    AND polp.pop_effective_end_date is null
    and rownum=1) BIS_PARTY,
    (SELECT replace(replace(replace(prp.prp_value_description,chr(10),''),chr(13),''),'-','')
    FROM t_policy_risk_property prp
    WHERE prp.prp_prk_pol_policy_id=pol.pol_policy_id
    AND prp.prp_effective_end_date IS NULL
    AND prp.prp_prk_policy_risk_id IS NOT NULL
    AND prp.prp_par_parameter_name ='Chassis_No' AND ROWNUM=1) CHASSIS_NO,
    (SELECT replace(replace(replace(prp.prp_value_description,chr(10),''),chr(13),''),'-','')
    FROM t_policy_risk_property prp
    WHERE prp.prp_prk_pol_policy_id=pol.pol_policy_id
    AND prp.prp_effective_end_date IS NULL
    AND prp.prp_prk_policy_risk_id IS NOT NULL
    AND prp.prp_par_parameter_name ='Engine No.'
    AND ROWNUM=1 ) ENGINE_NO

    FROM T_DEBIT_CREDIT_NOTE DCN ,
    T_POLICY POL,
    T_PRODUCT PROD
    WHERE DCN.DCN_POL_POLICY_ID=POL.POL_POLICY_ID
    AND POL.POL_PRT_PRODUCT_ID =PROD.PRODUCT_ID
    AND DCN.DCN_DEBIT_CREDIT_NOTE_NO=vDebitNumber
    -- AND DCN.DCN_DATE <= to_date('30/12/2012','DD/MM/YYYY')
    -- AND DCN.dcn_narration NOT LIKE 'Credit Note for PPW Cancellation against debit note%'
    --AND DCN.DCN_CREATED_BY NOT LIKE 'PPWMIG'
    AND DCN.DCN_DEBIT_CREDIT_NOTE_NO NOT LIKE 'DC%'
    AND DCN.DCN_DEBIT_CREDIT_NOTE_NO NOT LIKE 'CC%'
    AND DCN.DCN_CREATED_BY <> 'PPWMIG'
    AND PROD.LOB NOT IN ('LIFE')
    AND DCN.DCN_CLM_CLAIM_ID IS NULL
    ORDER BY POL.POL_POLICY_NUMBER , DCN.DCN_DEBIT_CREDIT_NOTE_NO) ;

    BEGIN

    OPEN vgwpList;

    LOOP
    FETCH vgwpList

    INTO
    v_agent_name
    ,v_stake_code
    ,v_agent_code
    ,v_deb_date
    ,v_debit_no
    ,v_prod_code
    ,v_policy_no
    ,v_pol_status
    ,v_pol_hol_name
    ,v_dncn_indicator
    ,v_basic
    ,v_srcc
    ,v_tc
    ,v_adminfee
    ,v_tax
    ,v_stamp
    ,v_policyfee
    ,v_nbt
    ,v_crsf
    ,v_assurance_code
    ,v_matchvoucher
    ,v_third
    ,v_curr
    ,v_rate
    ,v_pol_code
    ,v_address
    ,v_si
    ,v_vehicle
    ,v_pol_start
    ,v_match_unmatch
    ,v_daterange
    ,v_prod_name
    ,v_narration
    ,v_unadjusted_amt
    ,v_total
    ,v_sirange
    ,v_usage
    ,v_risktype
    ,v_bis_party
    ,v_chassis
    ,v_engin;

    EXIT WHEN vgwpList%NOTFOUND;

    v_gwp:=(v_basic+v_srcc+v_tc);
    --v_total:= (v_gwp+v_adminfee+v_tax+v_stamp+v_policyfee+v_nbt+v_crsf);
    ---This 2012-12-03
    BEGIN
    IF substr(v_debit_no,1,2)='CN' AND (v_pol_status !='PPW-CANC' OR v_pol_status != 'N/A') THEN
    BEGIN
    select t4.pop_value_alpha_code
    INTO vCreditStatus
    from t_policy_property t4
    where t4.pop_pol_policy_id =(SELECT pol.pol_policy_id FROM T_POLICY POL WHERE POL.pol_Policy_number=v_policy_no)
    and t4.pop_par_parameter_name = 'PhysicalRefund'
    and t4.pop_effective_end_date is null;

    IF vCreditStatus ='YES' THEN
    BEGIN
    SELECT ATN.ATN_INVOLVED_PARTY_NAME
    INTO vPayingParty
    FROM T_ACCOUNTING_TRANSACTION ATN
    WHERE ATN.ATN_VOUCHER_NUMBER=v_debit_no;
    IF vPayingParty LIKE 'HNB ASSURANCE PLC%' THEN
    vStatusWord := 'UTILIZATION';
    ELSE
    vStatusWord := 'PHYSICAL REFUND';
    END IF;

    END;

    ELSIF vCreditStatus ='NO' THEN
    vStatusWord := 'NON PHYSICAL';
    END IF;

    EXCEPTION
    WHEN OTHERS THEN
    vStatusWord := 'N/A';
    END;
    ELSE
    vStatusWord := '';
    END IF;
    END;


    insert into T_REPORT_DNCN_GWP_THU (AGENT_NAME,BUSINESS_PARTY,AGENT_CODE,DEBIT_DATE,DNCN_NO,DEPT,POL_NO,
    POL_STATUS,POL_HOL_NAME,BASIC,SRCC,TC,GWP,ADMIN_FEE,TAX,STAMP,POLICY_FEE,NBT,CRSF,TOTAL,
    ASSURANCE_CODE,MATCHING_VOU,THIRD_PARTY,CURR,RATE,POL_HOL_CODE,ADDRESS,SI,PRODUCT,VEH_NO,START_DATE,
    MATCH_UNMATCH_IND,DATE_IND,REMARKS,UNADJUSTED_AMT,SI_RANGE,USAGE_OF_VEHICLE,RISK_TYPE,BIS_PARTY,CHASSIS_NO,ENGIN_NO,REFUND_IND)
    VALUES(v_agent_name,v_stake_code,v_agent_code,v_deb_date,v_debit_no,v_prod_name,v_policy_no,
    v_pol_status,v_pol_hol_name,v_basic,v_srcc,v_tc,v_gwp,v_adminfee,v_tax,v_stamp,v_policyfee,v_nbt,
    v_crsf,v_total,v_assurance_code,v_matchvoucher,v_third,v_curr,v_rate,v_pol_code,v_address,v_si
    ,v_prod_code,v_vehicle,v_pol_start,v_match_unmatch,v_daterange,v_narration,v_unadjusted_amt ,v_sirange,v_usage,v_risktype,v_bis_party,v_chassis,v_engin,vStatusWord);
    commit;

    END LOOP;

    CLOSE vgwpList;

    end GWP_REPORT_PAK_TRIGGER;




    I am using same table T_DEBIT_CREDIT_NOTE select statement
  • 10. Re: Trigger Issue
    Purvesh K Guru
    Currently Being Moderated
    Perhaps, a condition in the Cursor is not returning any data.
    where dcn.dcn_pol_policy_id = pol.pol_policy_id
            and pol.pol_prt_product_id = prod.product_id
            and dcn.dcn_debit_credit_note_no = vdebitnumber           --> Debit Number passed from Procedure
    My guess is, there is no entry in t_debit_credit_note dcn for the Debit number passed to the procedure and
    exit
    when vgwplist%notfound;
    causes the Loop to terminate once it does not find any data for the Debit Number.

    Please have a check in the above mentioned table, as It might hold some data when you are executing an Update but not when an Insert is fired.

    Although, I agree that you have a pretty long SQL, but doing a Row by Row processing is killing the performance, especially, when this would be executing for Each row of your Base Trigger table. Imagine the time taken for completion, when a procedure is executed a Million or more times!!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points