AnsweredAssumed Answered

Trigger on update - Clarity v13.2

Question asked by nmanoha on Apr 7, 2014
Latest reply on Apr 7, 2014 by Dave
Hi All,
 
We are currently in v13.2  and using Oracle 11g.
 
I have a custom sub - object  of Investment object in Clarity which captures few financial information and sums it up to update a attribute on Investment object.
I have created a below trigger to call a Stored procedure to update FIN_FINANCIALS table with the summation of investment object for each row.
I want to make sure if the below trigger and procedure is correct as the update via application is not happening. 
How do I validate if the trigger is passing the correct internal id to the procedure ?
 
 
TRIGGER:
create or replace 
trigger NIKU.Investments_Benefits_upd
AFTER UPDATE
ON odf_ca_investment_hle
FOR EACH ROW
declare
 v_hleid NUMBER;
begin
 v_hleid := :NEW.id;
IF v_hleid is not null  THEN 
inv_ben_upd(v_hleid);
 end if;
 end;
 
 
PROCEDURE
 
create or replace 
PROCEDURE inv_ben_upd(hle_id number) is
 
/*SP Name: inv_ben_upd
  This Stored Procedure is used to update the total of investments benefits
*/
Total_Project_Spend number;
begin
           Update fin_financials 
           set    planned_cst_total =  ( Select ( (cespry + eespry + tispry) + 
        (celfuy + celply + eelfuy + eelply) +
        (cilfuy + cilply + eilfuy + eilply) + 
        (cswfuy + cswply + eswfuy + eswply) +
        (chwfuy + chwply + ehwfuy + ehwply) 
       ) 
   from   odf_ca_investment_hle
WHERE  ID = hle_id)
           where  id in
                  ( 
                    select secondary_object_instance_id from odf_object_instance_mapping 
                    where  secondary_object_instance_code = 'financials'
                    and    primary_object_instance_code in ('project','idea')
                    and    primary_object_instance_id in 
                          (
                            select id from inv_investments 
                            where  id in 
                                   (
                                     select odf_parent_id from odf_ca_investment_hle where id = hle_id
                                   )
                          )
                  );
                    DBMS_OUTPUT.PUT_LINE('I got here:'||hle_id||'update completed'); 
          commit;
        
    exception
    when others then  
         dbms_output.put_line(SQLCODE||' error msg: '||SQLERRM);
        null;--    
end;
 
 
Thanks
Pavithra

Outcomes