I have a senario where am trying to get the record of an updated order status and then pass on the order_number to a stored procedure. How can I do this? please see my trigger and stored proc:

I would want to update the analysis codes on nltranm, the table nltranm is updated when the status on poheadm is updated to 'C' i.e. Invoiced.
So I want to take the order number from this table and use it as input to the stored proc. The stored proc will be responsible for updating the table based on the conditions specified where the order number = @order_no(obtained from the trigger).

This is my objective:
When I capture I purchase order, I enter the description of the line details,
But unfortunately the system doesn't populate these descriptions in my general ledger, so am trying to come up with an automated solution to extract these descriptions from the original order to the g/L into 3 free analysis fields in the gl transaction table. There is no direct link between this table and the gl table, the link is achieved though linking several tables as shown in my insert query. I have established that the table pohead's status column is updated to 'C' when the transaction has been posted to the ledger, that is why I want to put my after update trigger there to get order number as input to the second part. How best can I achieve the above?

order
CREATE OR REPLACE TRIGGER trig_poheadm_invoiced
After update ON ucdem.poheadm
FOR EACH ROW
WHEN (NEW.STATUS = 'C')
DECLARE
order_no CHAR(10);
BEGIN
order_no := :NEW.order_no ;
sp_poheadm_invoiced(:NEW.order_no);

END;


CREATE PROCEDURE sp_poheadm_invoiced(new_order_no IN ucdem.poheadm.order_no%type) IS


begin

INSERT INTO manager.temp_nl_anals
(seq,
supplier,order_no, batch_number ,invoice,long_description,transaction_group,tran_code1,tran_code2,local_total_actual)


select e.seq,
a.supplier,b.order_no,SUBSTR(c.batch_number,1,6) as batch_number ,c.invoice,substr(b.long_description,1,10) as long_description,d.transaction_group,a.tran_code1,a.tran_code2,b.local_total_actual
from ucdem.podetm b, ucdem.poheadm a , ucdem.Poinvhm c, ucdem.plitemm d, ucdem.nltranm e where a.ORDER_NO = b.ORDER_NO
and b.line_type <> 'C'
and c.REFERNCE = b.order_no and c.supplier = a.supplier and c.REFERNCE = d.REFERNCE and c.supplier = c.supplier
and c.invoice = d.item and d.transaction_group = e.transaction_group
and substr(e.batch_reference,1,6) = substr(c.batch_number,1,6) AND substr(e.JOURNAL_NUMBER,1,6) = substr(c.invoice,1,6)
and b.local_total_actual = e.journal_amount
and b.order_no = new_order_no;




update (SELECT * FROM ucDEM.nltranm A , manager.temp_nl_anals B
where A.SEQ = B.SEQ )
SET analysis_code1 = tran_code1, analysis_code2 = tran_code2, analysis_code3 = long_description;


END;

CREATE PROCEDURE sp_poheadm_invoiced( p_job in number
IS
    l_order_no number;
begin
    select order_no into l_order_no
      from trig_poheadm_invoiced_job
     where job_no = P_JOB;

    ... your logic ...

    delete from trig_poheadm_invoiced_job
     where job_no = P_JOB;
END;
/

CREATE OR REPLACE TRIGGER trig_poheadm_invoiced
After update ON ucdem.poheadm 
FOR EACH ROW 
WHEN (NEW.STATUS = 'C') 
DECLARE 
order_no CHAR(10);
BEGIN 
    dbms_job.submit( l_job, 'sp_poheadm_invoiced(JOB);' );

    insert into trig_poheadm_invoiced_job
    (job_no,order_no)
    values
    (l_job, :new.order_no);
END;
/