APPS.IGS_OR_VAL_IA SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 30

  		SELECT	count(*)
  		FROM	IGS_CO_ADDR_TYPE	adt
  		WHERE	adt.addr_type = p_addr_type;

Line: 34

  		SELECT	ia.addr_type,
  			ia.start_dt,
  			ia.end_dt
  		FROM	IGS_OR_INST_ADDR	ia
  		WHERE	ia.institution_cd = p_institution_cd AND
  			ia.addr_type <> p_addr_type AND
			ia.status = 'A' AND
  			ia.correspondence_ind = 'Y';

Line: 141

  		SELECT	count(*)
  		FROM	IGS_OR_INST_ADDR	ia
  		WHERE	ia.institution_cd = p_institution_cd AND
  			ia.addr_type = p_addr_type AND
			ia.status = 'A' AND
  			ia.start_dt <> p_start_dt AND
  			ia.end_dt IS NULL;

Line: 185

  		SELECT	ia.start_dt,
  			ia.end_dt
  		FROM	IGS_OR_INST_ADDR ia
  		WHERE	ia.institution_cd = cp_institution_cd AND
  			ia.addr_type = cp_addr_type AND
			ia.status = 'A' AND
  			ia.start_dt <> p_start_dt;

Line: 195

  	-- which is being created or updated, does not overlap
  	-- with an existing record of the same type for the institution.
  	-- Set the default message number
  	p_message_name := null;

Line: 205

  	-- Loop through the selected institution_addr records,
  	-- validating each record for date overlaps.
  	-- Do not validate against the record passed in.
  	-- Validation will fail if any of the following are true -
  	--	a. The current start date is between an existing date range.
  	-- 	b. The current end date is between an existing date range.
  	-- 	c. The current dates overlap an entire existing date range.
  	FOR	v_ia_rec	IN	c_ia(
  						p_institution_cd,
  						p_addr_type) LOOP
  		-- check if p_start_dt between existing date range.
  		IF (p_start_dt >= v_ia_rec.start_dt) AND
    		   	(p_start_dt <= NVL(v_ia_rec.end_dt,IGS_GE_DATE.IGSDATE('9999/01/01'))) THEN
  				p_message_name:= 'IGS_EN_ADDRESS_DATES_OVERLAP';