Monday 18 July 2016

How to remove special characters in oracle from field using form personalization

Condition
:INV_SUM_FOLDER.INVOICE_NUM <>   REGEXP_REPLACE(:INV_SUM_FOLDER.INVOICE_NUM,'[^a-zA-Z0-9]')  

Error Message :

 Invoice #. should be alphanumeric only

Test Script :

 

 Example code:-

PROCEDURE INV_NUM_Pro
  IS
  ln_bill_no VARCHAR2(100); -- INV_SUM_FOLDER.INVOICE_NUM
  ln_length        NUMBER := 0; 
  
  BEGIN       
      ln_bill_no := :INV_SUM_FOLDER.INVOICE_NUM;
      IF(:INV_SUM_FOLDER.INVOICE_NUM IS NOT NULL) THEN
       ln_length  := LENGTH(REPLACE(translate(UPPER(ln_bill_no),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','000000000000000000000000000000000000'),'0',''));
          IF( NVL(ln_length,0) > 0) THEN
              fnd_message.set_string('Courier bill No. should be alphanumeric only');
              fnd_message.error;     
          RAISE form_trigger_failure;
          END IF;
      END IF;
  END INV_NUM_Pro

 Example code 1:-

BEGIN
IF(:XX_RECEIPT_ALLOCATION.COURIER_BILL_NO IS NOT NULL) THEN
  
    IF  :XX_RECEIPT_ALLOCATION.COURIER_BILL_NO <>   REGEXP_REPLACE(:XX_RECEIPT_ALLOCATION.COURIER_BILL_NO,'[^a-zA-Z0-9]')   THEN
              fnd_message.set_string('Courier bill No. should be alphanumeric only');
              fnd_message.error;     
            RAISE form_trigger_failure;
    END IF;
END IF;
END; 

No comments:

Post a Comment