CREATE OR REPLACE PROCEDURE Update_VPF_Status IS
-- get the equipment count territory/contract 
	 CURSOR get_above_threshold IS
	 select CTD.contract_nr, CTD.contract_territory_nr, CTD.min_screens, CTD.vpf_status, 
			CTD.version_id, count(EC.equipment_nr) current_total,CTD.contract_territory_details_nr,
			CTD.target_screens, CTD.min_release_bookings, CTD.min_contract_bookings,
			CTD.min_wide_release_bookings, CTD.vpf_fee, CTD.vpf_currency_code,
			CTD.vpf_effective_date, CTD.vpf_expiry_date, CTD.record_deleted,
                        CON.EFFECTIVE_DATE, CON.END_DATE, sm.territory_nr
	   from cms_equipment_contract EC,
			cms_equipment_location EL,
			cms_screen_master SM,
			cms_contract_terr_dtl CTD,
			cms_contract_territory_EIS CTE,
                        cms_contract CON
	  where CTD.contract_nr = EC.contract_nr
		and CTD.contract_territory_nr = CTE.contract_territory_nr
		and CTE.territory_nr = SM.territory_nr
		and SM.screen_master_nr = EL.screen_master_nr
		and EL.equipment_nr = EC.equipment_nr
                and EC.CONTRACT_NR = CON.contract_nr
		and CTD.record_deleted = 'N'
                and CTD.contract_nr = CON.contract_nr
                and sysdate >= CON.EFFECTIVE_DATE
                and sysdate <= nvl(CON.END_DATE, sysdate)
	  group by CTD.contract_nr, CTD.contract_territory_nr, CTD.min_screens, CTD.vpf_status, 
			CTD.version_id,CTD.contract_territory_details_nr,
			CTD.target_screens, CTD.min_release_bookings, CTD.min_contract_bookings,
			CTD.min_wide_release_bookings, CTD.vpf_fee, CTD.vpf_currency_code,
			CTD.vpf_effective_date, CTD.vpf_expiry_date, CTD.record_deleted,
                        CON.EFFECTIVE_DATE, CON.END_DATE, sm.territory_nr;
 
	v_VPF_status VARCHAR2(10 BYTE);
	
	BEGIN

		FOR v_rec IN get_above_threshold LOOP
		IF (v_rec.current_total >= v_rec.min_screens ) THEN
			--getting present VPF status
			select CTD.vpf_status 
			  into v_VPF_status
			  from cms_contract_terr_dtl CTD
			 where CTD.contract_territory_details_nr = v_rec.contract_territory_details_nr;

			IF (v_VPF_status != 'A')  THEN
	        --creating history record  
		    INSERT INTO CMS_CONT_TERR_DTL_HIS(contract_terr_dtl_hist_nr,
			   								contract_territory_details_nr,
											contract_nr,
											contract_territory_nr,
											vpf_status,
											target_screens,
											min_screens,
											min_release_bookings,
											min_contract_bookings,
											min_wide_release_bookings,
											vpf_fee,
											vpf_currency_code,
											vpf_effective_date,
											vpf_expiry_date,
											version_id,
											created_date,
											created_user,
											updated_date,
											updated_user)
							         VALUES(cms_cont_terr_dtl_his_nr_seq.nextval,
											  v_rec.contract_territory_details_nr,	
											  v_rec.contract_nr,
											  v_rec.contract_territory_nr,
											  v_rec.vpf_status,
											  v_rec.target_screens,
											  v_rec.min_screens,
											  v_rec.min_release_bookings,
											  v_rec.min_contract_bookings,
											  v_rec.min_wide_release_bookings,
											  v_rec.vpf_fee,
											  v_rec.vpf_currency_code,
											  v_rec.vpf_effective_date,
											  v_rec.vpf_expiry_date,
											  v_rec.version_id,
											  sysdate,
											  'Update VPF status',
											  sysdate,
											  'Update VPF status');   
											
			--upating vpf status
			UPDATE cms_contract_terr_dtl SET 
				   vpf_status = 'A',
				   vpf_effective_date = get_actual_vpf_effective_date(v_rec.contract_nr,v_rec.territory_nr, v_rec.min_Screens),
				   updated_date = sysdate,
				   updated_user = 'Update VPF Status',
				   version_id = v_rec.version_id + 1
			 WHERE contract_nr = v_rec.contract_nr
			   AND contract_territory_nr = v_rec.contract_territory_nr
			   AND v_rec.current_total >= v_rec.min_screens
			   AND record_deleted = 'N';
                        END IF;	
			
	    
		ELSIF (v_rec.current_total < v_rec.min_screens) THEN
			--getting present VPF status
			select CTD.vpf_status 
			  into v_VPF_status
			  from cms_contract_terr_dtl CTD
			 where CTD.contract_territory_details_nr = v_rec.contract_territory_details_nr;

			IF (v_VPF_status != 'I')  THEN
			--creating history record    
			INSERT INTO CMS_CONT_TERR_DTL_HIS(contract_terr_dtl_hist_nr,
											  contract_territory_details_nr,
											  contract_nr,
											  contract_territory_nr,
											  vpf_status,
											  target_screens,
											  min_screens,
											  min_release_bookings,
											  min_contract_bookings,
											  min_wide_release_bookings,
											  vpf_fee,
											  vpf_currency_code,
											  vpf_effective_date,
											  vpf_expiry_date,
											  version_id,
											  created_date,
											  created_user,
											  updated_date,
											  updated_user)
									   VALUES(cms_cont_terr_dtl_his_nr_seq.nextval,
												v_rec.contract_territory_details_nr, 	
												v_rec.contract_nr,
												v_rec.contract_territory_nr,
												v_rec.vpf_status,
												v_rec.target_screens,
												v_rec.min_screens,
												v_rec.min_release_bookings,
												v_rec.min_contract_bookings,
												v_rec.min_wide_release_bookings,
												v_rec.vpf_fee,
												v_rec.vpf_currency_code,
												v_rec.vpf_effective_date,
												v_rec.vpf_expiry_date,
												v_rec.version_id,
												sysdate,
												'Update VPF status',
												sysdate,
												'Update VPF status');        
	       
			
			--upating vpf status
			UPDATE cms_contract_terr_dtl SET 
				   vpf_status = 'I',
				   updated_date = sysdate,
				   updated_user = 'Update VPF Status',
				   version_id = v_rec.version_id + 1
			 WHERE contract_nr = v_rec.contract_nr
			   AND contract_territory_nr = v_rec.contract_territory_nr
			   AND v_rec.current_total < v_rec.min_screens
			   AND record_deleted = 'N';   
		 END IF; 	
		END IF;  
	    END LOOP;

   COMMIT;
END;
/