HELP ME ADD EXCEPTION
-
I created a procedure to stop users from submitting all invoices having zero value. But the challenge am having now is to create exception in this procedure so that three authorized users can submit all such zero invoices. Please see below the created procedure: CREATE OR REPLACE PROCEDURE SIMS_ORN_LIVE.EV_INV_ITEM_VALUE_CHECK_SIMS(P_INVH_SYS_ID NUMBER ) AS CURSOR C1 IS SELECT Sum(INVI_FC_VAL) FROM OT_INVOICE_ITEM WHERE INVI_INVH_SYS_ID=P_INVH_SYS_ID; M_VAL NUMBER; BEGIN IF C1%ISOPEN THEN CLOSE C1; END IF; OPEN C1; FETCH C1 INTO M_VAL; CLOSE C1; IF Nvl(M_VAL,0)=0 THEN RAISE_APPLICATION('OP',585844 ,'YOU ARE NOT AUTHORIZED TO SUBMIT INVOICES WITH PROMO ITEMS ONLY, KINDLY CONTACT ED-OPS, MR. LAWRENCE, OR MRS NWABUIKE. ',' ',' ',' ',' ',' ',' ',' '); END IF; END ; /
-
I created a procedure to stop users from submitting all invoices having zero value. But the challenge am having now is to create exception in this procedure so that three authorized users can submit all such zero invoices. Please see below the created procedure: CREATE OR REPLACE PROCEDURE SIMS_ORN_LIVE.EV_INV_ITEM_VALUE_CHECK_SIMS(P_INVH_SYS_ID NUMBER ) AS CURSOR C1 IS SELECT Sum(INVI_FC_VAL) FROM OT_INVOICE_ITEM WHERE INVI_INVH_SYS_ID=P_INVH_SYS_ID; M_VAL NUMBER; BEGIN IF C1%ISOPEN THEN CLOSE C1; END IF; OPEN C1; FETCH C1 INTO M_VAL; CLOSE C1; IF Nvl(M_VAL,0)=0 THEN RAISE_APPLICATION('OP',585844 ,'YOU ARE NOT AUTHORIZED TO SUBMIT INVOICES WITH PROMO ITEMS ONLY, KINDLY CONTACT ED-OPS, MR. LAWRENCE, OR MRS NWABUIKE. ',' ',' ',' ',' ',' ',' ',' '); END IF; END ; /
This sort of business rule should be in your application not the database. Wrong tool for the job.
Never underestimate the power of human stupidity RAH