Database Trigger Problems

user10886870

Good afternoon. We are trying to create a new audit and trigger and are getting a compilation error. Any thoughts?

Thanks in advance,

Sarah

SQL> 

SQL> CREATE OR REPLACE TRIGGER CART_TEMPL_INV_TR

 2 AFTER INSERT OR UPDATE OR DELETE ON PS_CART_TEMPL_INV

 3 FOR EACH ROW

 4 DECLARE

 5 V_AUDIT_OPRID VARCHAR2(64);

 6 BEGIN

 7 DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);

 8 IF INSERTING

 9 THEN

 10 INSERT INTO PS_AUDIT_CART_TMPL

 11 VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'A',:NEW.BUSINESS_UNIT,:NEW.INV_CART_ID,:NEW.INV_ITEM_ID,:NEW.COMPARTMENT,:NEW.COUNT_ORDER,:NEW.QTY_OPTIMAL,:NEW.UNIT_OF_MEASURE,:NEW.AVG_CART_USAGE,:NEW.DISTRIB_TYPE,:NEW.TRANSFER_COST,:NEW.PRICE_MARKUP_PCT,:NEW.CHARGE_CODE,:NEW.USGC_STATUS,:NEW.USGC_INSTANCE,:NEW.ACCOUNT,:NEW.ALTACCT,:NEW.DEPTID,:NEW.OPERATING_UNIT,:NEW.PRODUCT,:NEW.FUND_CODE,:NEW.CLASS_FLD,:NEW.PROGRAM_CODE,:NEW.BUDGET_REF,:NEW.AFFILIATE,:NEW.AFFILIATE_INTRA1,:NEW.AFFILIATE_INTRA2,:NEW.CHARTFIELD1,:NEW.CHARTFIELD2,:NEW.CHARTFIELD3,:NEW.BUSINESS_UNIT_PC,:NEW.PROJECT_ID,:NEW.ACTIVITY_ID,:NEW.RESOURCE_TYPE,:NEW.RESOURCE_CATEGORY,:NEW.RESOURCE_SUB_CAT,:NEW.ANALYSIS_TYPE,:NEW.FOQ,:NEW.QTY_MAXIMUM,:NEW.COUNT_REQUIRED,:NEW.CART_REPLEN_CTRL,:NEW.CART_REPLEN_OPT,:NEW.CONS_NON_STOCK,:NEW.BCKORDR_CNCL_FLAG,:NEW.SPEEDTYPE_KEY,:NEW.COMBO_VALID_FLG,:NEW.MATERIAL_RECON_FLG,:NEW.USG_TRCKNG_METHOD,:NEW.CHARGE_MARKUP_PCNT,:NEW.CHARGE_MARKUP_AMT,:NEW.TRANSFER_CALC_TYPE,:NEW.SYNCID,:NEW.SYNCDTTM);

 12 ELSE

 13 IF DELETING

 14 THEN

 15 INSERT INTO PS_AUDIT_CART_TMPL

 16 VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'D',:OLD.BUSINESS_UNIT,:OLD.INV_CART_ID,:OLD.INV_ITEM_ID,:OLD.COMPARTMENT,:OLD.COUNT_ORDER,:OLD.QTY_OPTIMAL,:OLD.UNIT_OF_MEASURE,:OLD.AVG_CART_USAGE,:OLD.DISTRIB_TYPE,:OLD.TRANSFER_COST,:OLD.PRICE_MARKUP_PCT,:OLD.CHARGE_CODE,:OLD.USGC_STATUS,:OLD.USGC_INSTANCE,:OLD.ACCOUNT,:OLD.ALTACCT,:OLD.DEPTID,:OLD.OPERATING_UNIT,:OLD.PRODUCT,:OLD.FUND_CODE,:OLD.CLASS_FLD,:OLD.PROGRAM_CODE,:OLD.BUDGET_REF,:OLD.AFFILIATE,:OLD.AFFILIATE_INTRA1,:OLD.AFFILIATE_INTRA2,:OLD.CHARTFIELD1,:OLD.CHARTFIELD2,:OLD.CHARTFIELD3,:OLD.BUSINESS_UNIT_PC,:OLD.PROJECT_ID,:OLD.ACTIVITY_ID,:OLD.RESOURCE_TYPE,:OLD.RESOURCE_CATEGORY,:OLD.RESOURCE_SUB_CAT,:OLD.ANALYSIS_TYPE,:OLD.FOQ,:OLD.QTY_MAXIMUM,:OLD.COUNT_REQUIRED,:OLD.CART_REPLEN_CTRL,:OLD.CART_REPLEN_OPT,:OLD.CONS_NON_STOCK,:OLD.BCKORDR_CNCL_FLAG,:OLD.SPEEDTYPE_KEY,:OLD.COMBO_VALID_FLG,:OLD.MATERIAL_RECON_FLG,:OLD.USG_TRCKNG_METHOD,:OLD.CHARGE_MARKUP_PCNT,:OLD.CHARGE_MARKUP_AMT,:OLD.TRANSFER_CALC_TYPE,:OLD.SYNCID,:OLD.SYNCDTTM);

 17 ELSE

 18 INSERT INTO PS_AUDIT_CART_TMPL

 19 VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'K',:OLD.BUSINESS_UNIT,:OLD.INV_CART_ID,:OLD.INV_ITEM_ID,:OLD.COMPARTMENT,:OLD.COUNT_ORDER,:OLD.QTY_OPTIMAL,:OLD.UNIT_OF_MEASURE,:OLD.AVG_CART_USAGE,:OLD.DISTRIB_TYPE,:OLD.TRANSFER_COST,:OLD.PRICE_MARKUP_PCT,:OLD.CHARGE_CODE,:OLD.USGC_STATUS,:OLD.USGC_INSTANCE,:OLD.ACCOUNT,:OLD.ALTACCT,:OLD.DEPTID,:OLD.OPERATING_UNIT,:OLD.PRODUCT,:OLD.FUND_CODE,:OLD.CLASS_FLD,:OLD.PROGRAM_CODE,:OLD.BUDGET_REF,:OLD.AFFILIATE,:OLD.AFFILIATE_INTRA1,:OLD.AFFILIATE_INTRA2,:OLD.CHARTFIELD1,:OLD.CHARTFIELD2,:OLD.CHARTFIELD3,:OLD.BUSINESS_UNIT_PC,:OLD.PROJECT_ID,:OLD.ACTIVITY_ID,:OLD.RESOURCE_TYPE,:OLD.RESOURCE_CATEGORY,:OLD.RESOURCE_SUB_CAT,:OLD.ANALYSIS_TYPE,:OLD.FOQ,:OLD.QTY_MAXIMUM,:OLD.COUNT_REQUIRED,:OLD.CART_REPLEN_CTRL,:OLD.CART_REPLEN_OPT,:OLD.CONS_NON_STOCK,:OLD.BCKORDR_CNCL_FLAG,:OLD.SPEEDTYPE_KEY,:OLD.COMBO_VALID_FLG,:OLD.MATERIAL_RECON_FLG,:OLD.USG_TRCKNG_METHOD,:OLD.CHARGE_MARKUP_PCNT,:OLD.CHARGE_MARKUP_AMT,:OLD.TRANSFER_CALC_TYPE,:OLD.SYNCID,:OLD.SYNCDTTM);

 20 INSERT INTO PS_AUDIT_CART_TMPL

 21 VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'N',:NEW.BUSINESS_UNIT,:NEW.INV_CART_ID,:NEW.INV_ITEM_ID,:NEW.COMPARTMENT,:NEW.COUNT_ORDER,:NEW.QTY_OPTIMAL,:NEW.UNIT_OF_MEASURE,:NEW.AVG_CART_USAGE,:NEW.DISTRIB_TYPE,:NEW.TRANSFER_COST,:NEW.PRICE_MARKUP_PCT,:NEW.CHARGE_CODE,:NEW.USGC_STATUS,:NEW.USGC_INSTANCE,:NEW.ACCOUNT,:NEW.ALTACCT,:NEW.DEPTID,:NEW.OPERATING_UNIT,:NEW.PRODUCT,:NEW.FUND_CODE,:NEW.CLASS_FLD,:NEW.PROGRAM_CODE,:NEW.BUDGET_REF,:NEW.AFFILIATE,:NEW.AFFILIATE_INTRA1,:NEW.AFFILIATE_INTRA2,:NEW.CHARTFIELD1,:NEW.CHARTFIELD2,:NEW.CHARTFIELD3,:NEW.BUSINESS_UNIT_PC,:NEW.PROJECT_ID,:NEW.ACTIVITY_ID,:NEW.RESOURCE_TYPE,:NEW.RESOURCE_CATEGORY,:NEW.RESOURCE_SUB_CAT,:NEW.ANALYSIS_TYPE,:NEW.FOQ,:NEW.QTY_MAXIMUM,:NEW.COUNT_REQUIRED,:NEW.CART_REPLEN_CTRL,:NEW.CART_REPLEN_OPT,:NEW.CONS_NON_STOCK,:NEW.BCKORDR_CNCL_FLAG,:NEW.SPEEDTYPE_KEY,:NEW.COMBO_VALID_FLG,:NEW.MATERIAL_RECON_FLG,:NEW.USG_TRCKNG_METHOD,:NEW.CHARGE_MARKUP_PCNT,:NEW.CHARGE_MARKUP_AMT,:NEW.TRANSFER_CALC_TYPE,:NEW.SYNCID,:NEW.SYNCDTTM);

 22 END IF;

 23 END IF;

 24 END CART_TEMPL_INV_TR;

 25 /

Warning: Trigger created with compilation errors.

Velu Angusamy

Sarah - Did you create the function GET_PS_OPRID prior creating trigger?

to identify what is the error do like this and post the snapshot.

-Velu

Velu Angusamy

Sarah - can you provide the snapshot like i shown ? Thats the only way we can get to real error.

As far as function is concerned its located in below path . Execute the script and reexecute the trigger with create or replace

$PS_HOME\scripts\getpsoprid.sql

-Velu

Velu Angusamy

Sarah - Did you create the function GET_PS_OPRID prior creating trigger?

to identify what is the error do like this and post the snapshot.

-Velu

user10886870

Velu,

No errors were returned. I have one of the developers checking also. No, I did not create the function, where is that done?

Velu Angusamy

Sarah - can you provide the snapshot like i shown ? Thats the only way we can get to real error.

As far as function is concerned its located in below path . Execute the script and reexecute the trigger with create or replace

$PS_HOME\scripts\getpsoprid.sql

-Velu

user10886870

Velu,

Good morning! Thanks so much. We did add the script to create and this did go to a success and we are able to use now. THANK YOU!