Oracle trigger and/or procedure not working

Below is the insert statement contained in the procedure which works when run in SQL Plus (I had to use a fixed po_item_id to test it)...  I have also included the trigger, procedure and sql scripts generated to create both tables involved.  I am using Oracle 10g database.  Your help is greatly appreciated!

INSERT INTO INVEN_ITEMS_TRACKING (PROJECT_PART_NUM, PO_NUMBER, PO_LINE, TRACKING_TYPE, ADDED_BY_NAME) SELECT PROJECT_PART_NUM, PO_NUMBER, PO_LINE, 'On Order', 'PO Item Creation' FROM INVEN_PO_ITEM_TRACKING WHERE PO_ITEM_ID = 6358;

===========

Below is the trigger but I can't tell if it is even firing.  I have tried both :old.po_item_id and :new.po_item_id

create or replace TRIGGER "OSILAS".CREATE_POITEM_TRACKING_RECORD AFTER INSERT ON INVEN_PO_ITEM_TRACKING
FOR EACH ROW
begin create_poitem_tracking_rec(:new.po_item_id);
end;

===========

Following is the procedure...

create or replace procedure create_poitem_tracking_rec(id3 number) as
pragma autonomous_transaction;
begin
  INSERT INTO INVEN_ITEMS_TRACKING (PROJECT_PART_NUM, PO_NUMBER, PO_LINE, TRACKING_TYPE, ADDED_BY_NAME) SELECT PROJECT_PART_NUM, PO_NUMBER, PO_LINE, 'On Order', 'PO Item Creation' FROM INVEN_PO_ITEM_TRACKING WHERE PO_ITEM_ID = id3;
 commit;
end;





Following are the scripts for creating the tables (it also includes the scripts above)
==============================================

REM START OSILAS INVEN_PO_ITEM_TRACKING

  CREATE TABLE "OSILAS"."INVEN_PO_ITEM_TRACKING"
   (      "INVEN_MASTER_ID" NUMBER(10,0),
      "PO_ITEM_ID" NUMBER(12,0) NOT NULL ENABLE,
      "PO_NUMBER" VARCHAR2(40 BYTE) NOT NULL ENABLE,
      "VENDOR" VARCHAR2(60 BYTE),
      "PO_LINE" VARCHAR2(40 BYTE),
      "TYPE" VARCHAR2(55 BYTE),
      "DESCRIPTION" VARCHAR2(120 BYTE),
      "PO_QTY" NUMBER(15,3),
      "ACCEPTED_QTY" NUMBER(15,3),
      "RELEASED_QTY" NUMBER(15,3),
      "LAST_DATE_RELEASED" DATE,
      "CANCELLED" VARCHAR2(3 BYTE),
      "PUBLIC_NOTES" CLOB,
      "PRIVATE_NOTES" CLOB,
      "USER_FIELD1" VARCHAR2(60 BYTE),
      "PROJECT_PART_NUM" VARCHAR2(40 BYTE),
      "MANUFACTURE_PART_NUM" VARCHAR2(40 BYTE),
      "ACCEPTED_BY" VARCHAR2(40 BYTE),
      "USER_FIELD2" VARCHAR2(60 BYTE),
      "USER_DATE1" DATE,
      "DATE_ADDED" DATE DEFAULT sysdate,
      "UNIT_PRICE" NUMBER(14,4),
      "EXTENDED_PRICE" NUMBER(14,4),
      "BACKORDERED" NUMBER(14,4),
      "VENDOR_PART_NUM" VARCHAR2(40 BYTE),
      "UNIT_TYPE" VARCHAR2(40 BYTE),
      "VENDOR_ID" NUMBER(6,0),
       CONSTRAINT "INVEN_PO_ITEM_TRACKING_PK" PRIMARY KEY ("PO_ITEM_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "OSILAS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 917504 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "OSILAS"
 LOB ("PUBLIC_NOTES") STORE AS (
  TABLESPACE "OSILAS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
 LOB ("PRIVATE_NOTES") STORE AS (
  TABLESPACE "OSILAS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
 
REM END OSILAS INVEN_PO_ITEM_TRACKING

REM START OSILAS INVEN_PO_ITEM_TRACKING

   COMMENT ON COLUMN "OSILAS"."INVEN_PO_ITEM_TRACKING"."UNIT_TYPE" IS 'unit measure -- gallons, inches, feet, etc.';
 
REM END OSILAS INVEN_PO_ITEM_TRACKING

REM START OSILAS CREATE_POITEM_TRACKING_RECORD

  CREATE OR REPLACE TRIGGER "OSILAS"."CREATE_POITEM_TRACKING_RECORD" AFTER INSERT ON INVEN_PO_ITEM_TRACKING
FOR EACH ROW
begin create_poitem_tracking_rec(:new.po_item_id);
end;

/
ALTER TRIGGER "OSILAS"."CREATE_POITEM_TRACKING_RECORD" ENABLE;
 
REM END OSILAS CREATE_POITEM_TRACKING_RECORD

REM START OSILAS INS_PO_ITEM_TRACKING_TRG

  CREATE OR REPLACE TRIGGER "OSILAS"."INS_PO_ITEM_TRACKING_TRG"
 BEFORE INSERT ON INVEN_PO_ITEM_TRACKING
FOR EACH ROW
BEGIN
  SELECT PO_ITEM_TRACKING_SEQ.NEXTVAL INTO :NEW.PO_ITEM_ID FROM DUAL;
END;


/
ALTER TRIGGER "OSILAS"."INS_PO_ITEM_TRACKING_TRG" ENABLE;
 
REM END OSILAS INS_PO_ITEM_TRACKING_TRG


=================================
REM START OSILAS INVEN_ITEMS_TRACKING

  CREATE TABLE "OSILAS"."INVEN_ITEMS_TRACKING"
   (      "INVEN_MASTER_ID" NUMBER(10,0),
      "INVEN_TRACKING_ID" NUMBER(10,0) NOT NULL ENABLE,
      "PROJECT_PART_NUM" VARCHAR2(40 BYTE),
      "PO_NUMBER" VARCHAR2(40 BYTE),
      "PO_LINE" VARCHAR2(40 BYTE),
      "QTY_IN" NUMBER(15,3),
      "QTY_OUT" NUMBER(15,3),
      "QTY_CURRENT" NUMBER(15,3),
      "LOCATION" VARCHAR2(60 BYTE),
      "TRANSPORTED_TO" VARCHAR2(60 BYTE),
      "TRANSPORTED_VIA" VARCHAR2(60 BYTE),
      "DEPART_DATE" DATE,
      "ARRIVE_DATE" DATE,
      "TRACKING_TYPE" VARCHAR2(30 BYTE),
      "UNIQUE_ID" VARCHAR2(40 BYTE),
      "UNIQUE_ID_TYPE" VARCHAR2(40 BYTE),
      "ISSUED_BY" VARCHAR2(40 BYTE),
      "RECEIVED_BY" VARCHAR2(40 BYTE),
      "NOTES" CLOB,
      "ADDED_ON" DATE,
      "ADDED_BY" NUMBER(6,0),
      "ADDED_BY_NAME" VARCHAR2(40 BYTE),
      "FROM_TRACKING_ID" NUMBER(10,0),
      "USER1" VARCHAR2(60 BYTE),
      "USER2" VARCHAR2(60 BYTE),
      "USER3" VARCHAR2(60 BYTE),
      "USER4" VARCHAR2(60 BYTE),
      "USER5" VARCHAR2(60 BYTE),
       CONSTRAINT "INVEN_ITEMS_TRACKING_PK" PRIMARY KEY ("INVEN_TRACKING_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "OSILAS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "OSILAS"
 LOB ("NOTES") STORE AS (
  TABLESPACE "OSILAS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
 
REM END OSILAS INVEN_ITEMS_TRACKING

REM START OSILAS INVEN_ITEMS_TRACKING_TRG

  CREATE OR REPLACE TRIGGER "OSILAS"."INVEN_ITEMS_TRACKING_TRG"
 BEFORE INSERT ON INVEN_ITEMS_TRACKING
FOR EACH ROW
BEGIN
  SELECT INVEN_ITEMS_TRACKING_SEQ.NEXTVAL INTO :NEW.INVEN_TRACKING_ID FROM DUAL;
END;


/
ALTER TRIGGER "OSILAS"."INVEN_ITEMS_TRACKING_TRG" ENABLE;
 
REM END OSILAS INVEN_ITEMS_TRACKING_TRG

Start Free Trial