Step 1: AAACP0278J Pan Number is registered with Another Supplier.
Step 2: User Entering the Same PAN Number (i.e.,AAACP0278J) to Other Supplier.
Trigger Not Allowed to enter during Process.
Trigger:
Version 1.0
Step 2: User Entering the Same PAN Number (i.e.,AAACP0278J) to Other Supplier.
Trigger Not Allowed to enter during Process.
Trigger:
Version 1.0
/*==============================================================================================
*
*===============================================================================================
* File :
* Object Name : DUPLICATE_PAN
* @author : LOKANADHAM THANDLAM
* @since :
01-Mar-2014
*
* Change History:
*
* Author Date Version Change (include Bug#IF appropriate)
*+-------------+---------------+----------+----------------------------------------------------+
* LOKANADHAM THANDLAM 01-Mar-2014 1.0
Initial Creation
*+-------------+---------------+----------+----------------------------------------------------+
* DESCRIPTION:
*+---------------------------------------------------------------------------------------------+*/
--DROP TRIGGER
APPS.DUPLICATE_PAN;
CREATE OR REPLACE TRIGGER APPS.DUPLICATE_PAN
BEFORE INSERT
ON JAI_AP_TDS_VENDOR_HDRS
FOR EACH ROW
DECLARE
a NUMBER (10);
BEGIN
SELECT COUNT (*)
INTO a
FROM JAI_AP_TDS_VENDOR_HDRS
WHERE PAN_NO = :NEW.PAN_NO
AND VENDOR_ID != :NEW.VENDOR_ID
AND VENDOR_SITE_ID != :NEW.VENDOR_SITE_ID;
IF a > 1
THEN
raise_application_error (-20454, 'PAN number is already exists
for another supplier');
END IF;
END;
/
Version 2.0 Small Changes.
Version 2.0 Small Changes.
--DROP TRIGGER APPS.DUPLICATE_PAN;
CREATE
OR REPLACE
TRIGGER APPS.DUPLICATE_PAN
BEFORE INSERT
ON JAI_AP_TDS_VENDOR_HDRS
FOR EACH ROW
DECLARE
A NUMBER (10);
BEGIN
SELECT COUNT (*)
INTO A
FROM JAI_AP_TDS_VENDOR_HDRS
WHERE PAN_NO = :NEW.PAN_NO
--AND VENDOR_ID = :NEW.VENDOR_ID
AND VENDOR_SITE_ID = :NEW.VENDOR_SITE_ID
AND TDS_VENDOR_TYPE_LOOKUP_CODE = 'OTHERS-IND';
IF A > 0
THEN
RAISE_APPLICATION_ERROR
(-20454, 'PAN NUMBER ALREADY EXISTS');
END IF;
END;
/