SELECT 
  /*+ index (pd pk_promo_dates) index(p pk_promotions) index(u 1x_upc_id_store_promo) */ p.promo_id, 
  u.upc_id, 
  u.store, 
  pd.beg_date, 
  pd.end_date, 
  p.AD_TYPE, 
  p.AD_QTY, 
  p.AD_AMT, 
  p.AD_MIX_MATCH, 
  p.LQD_MAX_QTY, 
  u.tag_auth_override, 
  p.PURCHASE_REQ, 
  p.PURCHASE_TYPE, 
  u.AD_VEHICLE, 
  u.AD_START, 
  u.AD_END, 
  p.AD_NOTES, 
  p.SURVEY_EXCLUDE_FLAG, 
  u.IMMED_FLAG, 
  u.IMMED_DATE, 
  u.mini_special_buy, 
  p.CONTRACT_ID, 
  p.CONTRACT_NOTES, 
  u.UPDATE_ID, 
  u.UPDATE_DATE 
FROM 
  promo_dates pd, 
  upc_id_store_promo u, 
  store s, 
  promotions p 
WHERE 
  u.PROMO_ID = pd.PROMO_ID and 
  u.promo_id = p.promo_id and 
  u.store = s.store and 
  s.day_code = pd.day_code

familyvar VARCHAR2(50);
weekendvar DATE;
datevar DATE;
cntr NUMBER := 0;

--CURSOR c_buyer IS
--SELECT DISTINCT buyer_id FROM BUYER_MASTER;

CURSOR c_upc IS
SELECT *
  FROM improd.upc_master
 WHERE upc=primary_upc
   AND upc_status='A';

CURSOR c_ad_long (upcidparam IN NUMBER) IS
SELECT u.upc_id, u.store,
  	   pd.beg_date, pd.end_date,
  	   p.AD_TYPE, p.AD_QTY, p.AD_AMT,
  	   p.AD_MIX_MATCH, p.LQD_MAX_QTY,
  	   u.tag_auth_override,
  	   p.PURCHASE_REQ, p.PURCHASE_TYPE,
  	   u.AD_VEHICLE, u.AD_START, u.AD_END,
  	   p.AD_NOTES, p.SURVEY_EXCLUDE_FLAG,
  	   u.IMMED_FLAG, u.IMMED_DATE,
  	   u.mini_special_buy,
  	   p.CONTRACT_ID, p.CONTRACT_NOTES,
  	   u.UPDATE_ID, u.UPDATE_DATE,
	   workspace.buyer_id_function(improd.vendor_buyer_function(u.upc_id,u.store)) buyer_id,
	   improd.vendor_pack_function(u.upc_id,u.store,trunc(sysdate)) pack
  FROM improd.promo_dates pd,
  	   improd.upc_id_store_promo u,
  	   improd.store s,
  	   improd.promotions p
 WHERE u.PROMO_ID = pd.PROMO_ID
   AND u.promo_id = p.promo_id
   AND u.store = s.store
   AND s.day_code = pd.day_code
   AND u.upc_id = upcidparam
   AND pd.end_date >= trunc(sysdate)
   AND u.store not in (164,798)
 ORDER BY u.store;

--and end_date-beg_date > 7

CURSOR c_pend_date_long (upcidparam IN NUMBER) IS
SELECT upc_id, week_end_date
  FROM (
    SELECT u.upc_id,
  		   next_day(trunc(pd.beg_date), 'saturday') week_end_date
      FROM improd.promo_dates pd,
  	   	   improd.upc_id_store_promo u,
  	   	   improd.store s,
  	   	   improd.promotions p
     WHERE u.PROMO_ID = pd.PROMO_ID
       AND u.promo_id = p.promo_id
   	   AND u.store = s.store
   	   AND s.day_code = pd.day_code
       AND u.upc_id=upcidparam
       AND pd.end_date-pd.beg_date > 7
	   AND pd.beg_date > trunc(sysdate)
	   AND u.store = (SELECT min(u2.store)
  	   	                FROM improd.promo_dates pd2,
  	   					 	 improd.upc_id_store_promo u2,
  	   					 	 improd.store s2,
  	   					 	 improd.promotions p2
                       WHERE u2.PROMO_ID = pd2.PROMO_ID
   				         AND u2.promo_id = p2.promo_id
   				     	 AND u2.store 	 = s2.store
   				     	 AND s2.day_code = pd2.day_code
                     	 AND u2.upc_id	 = upcidparam
   				     	 AND u2.store not in (164,798)
   					 	 AND pd2.end_date - pd2.beg_date > 7
   					 	 AND pd2.beg_date > trunc(sysdate)))
 group by upc_id, week_end_date;

CURSOR c_pend_long (upcidparam IN NUMBER,dateparam IN DATE) IS
SELECT u.upc_id, u.store,
  	   pd.beg_date, pd.end_date,
  	   p.AD_TYPE, p.AD_QTY, p.AD_AMT,
  	   p.AD_MIX_MATCH, p.LQD_MAX_QTY,
  	   u.tag_auth_override,
  	   p.PURCHASE_REQ, p.PURCHASE_TYPE,
  	   u.AD_VEHICLE, u.AD_START, u.AD_END,
  	   p.AD_NOTES, p.SURVEY_EXCLUDE_FLAG,
  	   u.IMMED_FLAG, u.IMMED_DATE,
  	   u.mini_special_buy,
  	   p.CONTRACT_ID, p.CONTRACT_NOTES,
  	   u.UPDATE_ID, u.UPDATE_DATE,
	   workspace.buyer_id_function(improd.vendor_buyer_function(u.upc_id,u.store)) buyer_id,
	   improd.vendor_pack_function(u.upc_id,u.store,trunc(sysdate)) pack
  FROM improd.promo_dates pd,
  	   improd.upc_id_store_promo u,
  	   improd.store s,
  	   improd.promotions p
 WHERE u.PROMO_ID = pd.PROMO_ID
   AND u.promo_id = p.promo_id
   AND u.store = s.store
   AND s.day_code = pd.day_code
   AND u.upc_id = upcidparam
   AND dateparam BETWEEN pd.beg_date AND pd.end_date
   AND pd.end_date - pd.beg_date > 7
   AND pd.beg_date > trunc(sysdate)
   AND u.store not in (164,798)
 ORDER BY u.store;

CURSOR c_pend_date (upcidparam IN NUMBER) IS
SELECT upc_id, week_end_date
  FROM (
       SELECT u.upc_id,
     		  next_day(trunc(pd.beg_date), 'saturday') week_end_date
         FROM improd.promo_dates pd,
     	   	  improd.upc_id_store_promo u,
     	   	  improd.store s,
     	   	  improd.promotions p
        WHERE u.PROMO_ID = pd.PROMO_ID
          AND u.promo_id = p.promo_id
      	  AND u.store = s.store
      	  AND s.day_code = pd.day_code
	      AND u.upc_id= upcidparam
		  AND pd.beg_date > trunc(sysdate)
		  AND not exists (
		  	  SELECT '1'
                FROM improd.promo_dates pd2,
            	   	 improd.upc_id_store_promo u2,
            	   	 improd.store s2,
            	   	 improd.promotions p2
               WHERE u2.PROMO_ID = pd2.PROMO_ID
                 AND u2.promo_id = p2.promo_id
             	 AND u2.store 	 = s2.store
             	 AND s2.day_code = pd2.day_code
			     AND u2.upc_id 	 = u.upc_id
			     AND u2.store 	 = u.store
				 AND u2.upc_id 	 = upcidparam
				 AND pd2.end_date - pd2.beg_date > 7
				 AND u2.store = (SELECT min(u3.store)
                                   FROM improd.promo_dates pd3,
                               	   	 	improd.upc_id_store_promo u3,
                               	   	 	improd.store s3,
                               	   	 	improd.promotions p3
                                  WHERE u3.PROMO_ID = pd3.PROMO_ID
                                    AND u3.promo_id = p3.promo_id
                                	AND u3.store 	= s3.store
                                	AND s3.day_code = pd3.day_code
								    AND u3.upc_id = upcidparam
								    AND u3.store not in (164,798)
								    AND pd3.end_date - pd3.beg_date > 7
								    AND pd3.beg_date > trunc(sysdate)  )))
 GROUP BY upc_id,week_end_date;



CURSOR c_pend (upcidparam IN NUMBER,dateparam IN DATE) IS
SELECT p.promo_id, u.upc_id, u.store,
  	   pd.beg_date, pd.end_date,
  	   p.AD_TYPE, p.AD_QTY, p.AD_AMT,
  	   p.AD_MIX_MATCH, p.LQD_MAX_QTY,
  	   u.tag_auth_override,
  	   p.PURCHASE_REQ, p.PURCHASE_TYPE,
  	   u.AD_VEHICLE, u.AD_START, u.AD_END,
  	   p.AD_NOTES, p.SURVEY_EXCLUDE_FLAG,
  	   u.IMMED_FLAG, u.IMMED_DATE,
  	   u.mini_special_buy,
  	   p.CONTRACT_ID, p.CONTRACT_NOTES,
  	   u.UPDATE_ID, u.UPDATE_DATE,
	   workspace.buyer_id_function(improd.vendor_buyer_function(u.upc_id,u.store)) buyer_id,
	   improd.vendor_pack_function(u.upc_id,u.store,trunc(sysdate)) pack
  FROM improd.promo_dates pd,
  	   improd.upc_id_store_promo u,
  	   improd.store s,
  	   improd.promotions p
 WHERE u.PROMO_ID = pd.PROMO_ID
   AND u.promo_id = p.promo_id
   AND u.store 	  = s.store
   AND s.day_code = pd.day_code
   AND u.upc_id	  = upcidparam
   AND dateparam BETWEEN pd.beg_date AND pd.end_date
   AND pd.beg_DATE > trunc(sysdate)
   AND u.store not in (164,798)
   AND not exists (
   	   SELECT '1'
         FROM improd.promo_dates pd2,
     	   	  improd.upc_id_store_promo u2,
     	   	  improd.store s2,
     	   	  improd.promotions p2
        WHERE u2.PROMO_ID = pd2.PROMO_ID
          AND u2.promo_id = p2.promo_id
      	  AND u2.store 	  = s2.store
      	  AND s2.day_code = pd2.day_code
		  AND u2.upc_id   = u.upc_id
		  AND u2.store    = u.store
		  AND u2.upc_id  = upcidparam
		  AND dateparam BETWEEN pd2.beg_date AND pd2.end_date
		  AND pd2.beg_DATE > trunc(sysdate)
		  AND pd2.end_date - pd2.beg_date > 7
		  AND pd2.beg_Date = pd.beg_Date
		  AND pd2.end_date = pd.end_date)
 ORDER BY STORE;

BEGIN
dbms_output.enable(1000000);
--FOR v_buyer IN c_buyer LOOP

  FOR v_upc IN c_upc  LOOP

   BEGIN
	    SELECT family_group_name
		  INTO familyvar
		  FROM improd.FAMILY_GROUP
		 WHERE family_group_nbr=v_upc.retail_family_group_nbr;
	  EXCEPTION
	    WHEN NO_DATA_FOUND THEN
		 familyvar := NULL;
	END;
	cntr := 0;
   FOR v_ad_long IN c_ad_long(v_upc.upc_id) LOOP

  IF cntr < 1 THEN
	weekendvar := v_ad_long.beg_Date;
	datevar := v_ad_long.beg_Date;
	cntr := 0;

	WHILE   datevar < v_ad_long.end_date LOOP

	SELECT NEXT_DAY(TRUNC(datevar),'saturday')
	  INTO weekendvar
	  FROM DUAL;

	BEGIN
	  INSERT INTO SURVEY_WORK_TABLE  VALUES (
	  v_ad_long.buyer_id,
	  v_upc.upc,
	  v_upc.upc_id,
	  weekendvar,
	  v_upc.item_div,
	  v_upc.item_nbr,
	  v_upc.product_description,
	  v_ad_long.pack,
	  v_upc.display_size,
	  v_upc.whse_nbr,
	  familyvar,
	  v_ad_long.ad_type,
	  v_ad_long.beg_date,
	  v_ad_long.end_date,
	  v_ad_long.ad_qty,
	  v_ad_long.ad_amt,
	  v_ad_long.ad_vehicle,
	  v_ad_long.survey_exclude_flag,
	  trunc(sysdate),v_upc.department);
	  COMMIT;
	 EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    dbms_output.put_line(v_ad_long.buyer_id||','||v_upc.upc||','||
		v_upc.upc_id||','||weekendvar);
	 END;
	 datevar := datevar + 7;
	END LOOP;
	cntr := cntr + 1;
  ELSE
   EXIT ;
  END IF;
  END LOOP;

  FOR v_pend_date_long IN c_pend_date_long (v_upc.upc_id) LOOP
  cntr := 0;
   FOR v_pend_long IN c_pend_long(v_upc.upc_id,v_pend_date_long.week_end_date) LOOP

   IF cntr < 1 THEN
   	  /*
      BEGIN
	    SELECT family_grp_name INTO familyvar FROM FAMILY_GROUP WHERE
		family_grp_nbr=v_upc.family_grp_nbr;
	  EXCEPTION
	    WHEN NO_DATA_FOUND THEN
		 familyvar := NULL;
	  END;
	  */
	weekendvar := v_pend_long.beg_Date;
	datevar := v_pend_long.beg_Date;
	cntr := 0;

	WHILE   datevar < v_pend_long.end_date LOOP

	SELECT NEXT_DAY(TRUNC(datevar),'saturday') INTO weekendvar
	FROM DUAL;

     BEGIN
	  INSERT INTO SURVEY_WORK_TABLE (
	  buyer_id,
	  upc,
	  upc_id,
	  week_ending_date,
	  item_div,
	  item_nbr,
	  description,
	  pack,
	  display_size,
	  whse_nbr,
	  family_group_name,
	  ad_type,
	  ad_beg,
	  ad_end,
	  ad_qty,
	  ad_amt,
	  ad_vehicle,
	  survey_exclude_flag,
	  update_date,department )
	  VALUES (
	  v_pend_long.buyer_id,
	  v_upc.upc,
	  v_upc.upc_id,
	  weekendvar,
	  v_upc.item_div,
	  v_upc.item_nbr,
	  v_upc.product_description,
	  v_pend_long.pack,
	  v_upc.display_size,
	  v_upc.whse_nbr,
	  familyvar,
	  v_pend_long.ad_type,
	  v_pend_long.beg_date,
	  v_pend_long.end_date,
	  v_pend_long.ad_qty,
	  v_pend_long.ad_amt,
	  v_pend_long.ad_vehicle,
	  v_pend_long.survey_exclude_flag,
	  trunc(sysdate),
	  v_upc.department);
	  COMMIT;
	 EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    UPDATE SURVEY_WORK_TABLE
		   SET ad_type=v_pend_long.ad_type,
		   	   ad_beg=v_pend_long.beg_date,
			   ad_end=v_pend_long.end_date,
			   ad_qty=v_pend_long.ad_qty,
			   ad_amt=v_pend_long.ad_amt,
			   ad_vehicle=v_pend_long.ad_vehicle,
			   survey_exclude_flag=v_pend_long.survey_exclude_flag
		 WHERE buyer_id=v_pend_long.buyer_id
		   AND upc=v_upc.upc
		   AND upc_id=v_upc.upc_id
		   AND week_ending_date=weekendvar;
		COMMIT;
	  END;

	 datevar := datevar + 7;
	END LOOP;
	cntr := cntr + 1;
  ELSE
   EXIT ;
  END IF;
  END LOOP;
 END LOOP;


 FOR v_pend_date IN c_pend_date (v_upc.upc_id) LOOP
cntr := 0;
   FOR v_pend IN c_pend(v_upc.upc_id,v_pend_date.week_end_date) LOOP

  IF cntr < 1 THEN
   	  /*
      BEGIN
	    SELECT family_grp_name INTO familyvar FROM FAMILY_GROUP WHERE
		family_grp_nbr=v_upc.family_grp_nbr;
	  EXCEPTION
	    WHEN NO_DATA_FOUND THEN
		 familyvar := NULL;
	  END;
	  */
	weekendvar := v_pend.beg_Date;
	datevar := v_pend.beg_Date;
	cntr := 0;

	WHILE   datevar < v_pend.end_date LOOP

	SELECT NEXT_DAY(TRUNC(datevar),'saturday') INTO weekendvar
	FROM DUAL;

     BEGIN
	  INSERT INTO SURVEY_WORK_TABLE (
	  buyer_id,
	  upc,
	  upc_id,
	  week_ending_date,
	  item_div,
	  item_nbr,
	  description,
	  pack,
	  display_size,
	  whse_nbr,
	  family_group_name,
	  ad_type,
	  ad_beg,
	  ad_end,
	  ad_qty,
	  ad_amt,
	  ad_vehicle,
	  survey_exclude_flag,
	  update_date,department )
	  VALUES (
	  v_pend.buyer_id,
	  v_upc.upc,
	  v_upc.upc_id,
	  weekendvar,
	  v_upc.item_div,
	  v_upc.item_nbr,
	  v_upc.product_description,
	  v_pend.pack,
	  v_upc.display_size,
	  v_upc.whse_nbr,
	  familyvar,
	  v_pend.ad_type,
	  v_pend.beg_date,
	  v_pend.end_date,
	  v_pend.ad_qty,
	  v_pend.ad_amt,
	  v_pend.ad_vehicle,
	  v_pend.survey_exclude_flag,trunc(sysdate),
	  v_upc.department);
	  COMMIT;
	 EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    UPDATE SURVEY_WORK_TABLE
		  SET ad_type=v_pend.ad_type,
		  	  ad_beg=v_pend.beg_date,
			  ad_end=v_pend.end_date,
			  ad_qty=v_pend.ad_qty,
			  ad_amt=v_pend.ad_amt,
			  ad_vehicle=v_pend.ad_vehicle,
			  survey_exclude_flag=v_pend.survey_exclude_flag
		 WHERE buyer_id=v_pend.buyer_id
		   AND upc=v_upc.upc
		   AND upc_id=v_upc.upc_id
		   AND week_ending_date=weekendvar;
		COMMIT;
	  END;

	 datevar := datevar + 7;
	END LOOP;
	cntr := cntr + 1;
  ELSE
   EXIT ;
  END IF;
  END LOOP;
 END LOOP;

 END LOOP;

--END LOOP;

END;