CREATE OR REPLACE PROCEDURE NewRegICAPriceRoland(inbruker IN ANVANDARE.ANVANDARID%TYPE,
                          inUnderlagid IN PBK.PRISREGISTER.UNDERLAGID%TYPE,
						  inDatum IN VARCHAR2) IS
	-- This  procedure registers prices in table PRISREGISTER
	-- The prices registered are selected from shops
	-- gods (article) databases.

	-- Note ONLY PRICES FOR SHOPS IN DWH are registered by this
	-- routine. Other prices are registered by routine RegImportprice
    -- Author : Mikael Sjöblom  2000-10-17

	-- Modified by Mikael Sjöblom and Roland Sköldblom 2001-10-16
	-- Added code for data on different computers
	-- Added code to select price for PLU- articles from imported data.

	TYPE t_ref_cur IS REF CURSOR;

	lvSQL VARCHAR2(2800);
	ButikExist VARCHAR2(50);
	AvdNr RIK2.AVD.AVDNR%TYPE;
 	ButikId PBK.BUTIKKORGBUTIKREL.BUTIKID%TYPE;
	varenr PBK.VARUKORGEANREL.VARENR%TYPE;
 	levnr PBK.VARUKORGEANREL.LEVNR%TYPE;
 	vare_snr PBK.VARUKORGEANREL.VARE_SNR%TYPE;
	lagstapris PBK.VARUKORGEANREL.LAGSTAPRIS%TYPE;
    Ean PBK.VARUKORGEANREL.EAN%TYPE;
	EanLPVara PBK.VARUKORGEANREL.EAN%TYPE;
	substitutean PBK.LPKORGEANREL.EANREL%TYPE;
	tmpsubstitutean PBK.LPKORGEANREL.EANREL%TYPE;
	strsubstitutean VARCHAR2(100);
    ButikPris PBK.PRISREGISTER.Pris%TYPE;
    ButikFSGPris PBK.PRISREGISTER.Pris%TYPE;
    ButikPantBelopp PBK.PRISREGISTER.Pris%TYPE;
	strButikPris VARCHAR2(10);
    SubstitutPris PBK.PRISREGISTER.Pris%TYPE;
    tmpSubstitutPris PBK.PRISREGISTER.Pris%TYPE;
    IcaStatus PBK.PRISREGISTER.REGICASTATUS%TYPE;
    Period PBK.PRISREGISTER.PERIOD%TYPE;
    ButikKorgId  BUTIKKORG.BUTIKKORGID%TYPE;
	VaruKorgId  VARUKORG.VARUKORGID%TYPE;
	varutyp PBK.PRISREGISTER.VARUTYP%TYPE;
	SubstitutVarutyp PBK.LPKORGEANREL.VARUTYP%TYPE;
	tmpSubstitutVarutyp PBK.LPKORGEANREL.VARUTYP%TYPE;
    lastAvdnr  Varchar2(1);
	lookUpServerName PBK.SERVER_NAME.SERVER_NAME%TYPE;
	borttags_flagg number;
    v_ErrorCode number;
    v_ErrorText VarChar2(500);
	eanost boolean;

	c_t_Butik t_ref_cur;              -- all shops
	c_t_Varu  t_ref_cur;              -- all articles
	c_t_Pris  t_ref_cur;              -- Used to fetch prices for a shop
	c_t_PrisSubstitut  t_ref_cur;     -- Used to fetch substitute prices for a shop
    c_t_ButikExist t_ref_cur;         -- Used to check if shop data exists
    c_t_LPKORG     t_ref_cur;         -- Used to Find LP

	BEGIN
         SELECT BUTIKKORGID,VARUKORGID,PERIOD into ButikKorgId, VaruKorgId, Period
		 FROM PBK.UNDERLAG
		 WHERE PBK.UNDERLAG.UNDERLAGID = inUnderlagid;
        DBMS_OUTPUT.PUT_LINE('ButikKorgId= '||ButikKorgId);
         DBMS_OUTPUT.PUT_LINE('VaruKorgId= '||VaruKorgId);
         DBMS_OUTPUT.PUT_LINE('Period= '||Period);

		 lvSQL := 'SELECT AVDNR,BUTIKS_NR,SUBSTR(avdnr,LENGTH(AVDNR),1) ' ||
		          'FROM RIK2.AVD,PBK.BUTIKKORGBUTIKREL ' ||
		          'WHERE PBK.BUTIKKORGBUTIKREL.BUTIKKORGID=' ||ButikKorgId|| ' ' ||
		          'AND PBK.BUTIKKORGBUTIKREL.BUTIKTYP=0 ' ||
				  'AND RIK2.AVD.BUTIKS_NR=PBK.BUTIKKORGBUTIKREL.BUTIKID ' ||
				  'AND RIK2.AVD.SELSKAP=''11'' ' ||
				  'AND RIK2.AVD.DT_SLUTT is null';

  	     OPEN c_t_Butik FOR
         lvSQL;
		 LOOP
		 	 FETCH c_t_Butik INTO Avdnr,ButikId,lastAvdnr;
		 	 EXIT WHEN c_t_Butik%NOTFOUND;

             -- Code to find computer thar stores information
			 Select Server_Name Into LookUpServerName
			 From PBK.SERVER_NAME
			 Where Server_ID=lastAvdnr;

			 lvSQL := 'SELECT TABLE_NAME FROM ALL_TABLES@'||LookUpServerName|| ' '||
                      'WHERE TABLE_NAME=''ICA_ARTIKEL'' ' ||
                      'AND OWNER=''A'||Avdnr||'''';

					   DBMS_OUTPUT.PUT_LINE(lvSQL);


			 OPEN c_t_ButikExist FOR
			 lvSQL;
		 	 FETCH c_t_ButikExist INTO ButikExist;

		     IF c_t_ButikExist%FOUND THEN

			 	-- Fetch all articles in Varukorg
			 	lvSQL := 'SELECT EAN,VARENR,LEVNR,VARE_SNR,LAGSTAPRIS,VARUTYP ' ||
		        	     'FROM PBK.VARUKORGEANREL ' ||
		              	 'WHERE PBK.VARUKORGEANREL.VARUKORGID=' ||VaruKorgId;

						 DBMS_OUTPUT.PUT_LINE(lvSQL);

			 	OPEN c_t_Varu FOR
             	lvSQL;
			 LOOP
				    /* Kommentar för att hantera ostar tab ort kommentar när klart
				 	FETCH c_t_Varu INTO Ean,varenr,levnr,vare_snr,lagstapris,varutyp;
			 	    EXIT WHEN c_t_Varu%NOTFOUND;
                    */
					/*Ta bort all kod inom loopen när ostar ar klara */
                   Loop
				 	FETCH c_t_Varu INTO Ean,varenr,levnr,vare_snr,lagstapris,varutyp;
						EXIT WHEN c_t_Varu%NOTFOUND;
				 	    if ean >599 and ean <699 then
						  eanost := false;  --'ndrat denna rad till false
					else
					 eanost := false;
			end if;
				EXIT WHEN eanost = false;
				end loop;
				 	EXIT WHEN c_t_Varu%NOTFOUND;
				    SubstitutPris := null;
					substitutean := null;
					ButikPris := null;
					ButikPantBelopp := null;
					SubstitutVarutyp := null;

					if lagstapris = 1 then -- Is this article marked for "Lägsta pris"
 			 	 	   -- If Plu article.Try to look up price in imported data from shop
					   if varutyp = 4 then
					      lvSQL := 'SELECT PRIS ' ||
				                   'FROM PBK.PLUBUTIKPRISREL '||
				                   'WHERE PERIOD='''||Period|| ''' ' ||
                                   'AND BUTIKS_NR='||butikId||' ' ||
							  	   'AND PBKPLU='|| ean;
                          DBMS_OUTPUT.PUT_LINE(lvSQL);
			 	 	      OPEN c_t_Pris FOR
				 	      lvSQL;
			 	 	      FETCH c_t_Pris into ButikPris;
				          CLOSE c_t_Pris;
                       end if;
	                   --insert into pbk.Error (Err_Code,Err_Text,SQL_TEXT) Values
	                    --(1,'PLU LP',lvSQL);
						--commit;
                       -- If PLU price NOT found in imported data from shop
					   -- Or if this is not a PLU then
					   -- Retreive prices for articles according to the LP-relational table.
					   if ButikPris is null then
						  lvSQL := 'SELECT EANREL ' ||
				                   'FROM PBK.LPKORGEANREL ' ||
				                   'WHERE EANLP='||Ean|| ' ' ||
								   'AND VARENRLP='||varenr|| ' ' ||
							       'AND VARE_SNRLP='||vare_snr;
			 	 		  OPEN c_t_LPKorg FOR
				 		  lvSQL;
						  Loop
			 	 		    FETCH c_t_LPKorg into EanLPVara;
				 	        EXIT WHEN c_t_LPKorg%NOTFOUND;
                            tmpSubstitutPris := null;
							ButikPantBelopp := null;
							tmpsubstitutean := null;
							tmpSubstitutVarutyp := null;
							tmpSubstitutEan:=EanLPVara;


					        lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.BORTTAGS_FLAGG,ICA_ARTIKEL.PANTBELOPP ' ||
								  --PBK.LPKORGEANREL.EANREL,PBK.LPKORGEANREL.VARUTYP ' ||
				                     'FROM A'||AvdNr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' ||
									 --PBK.LPKORGEANREL ' ||
				                     'WHERE ICA_ARTIKEL.EAN=' || EanLPVara || ' ' ||
                                     'AND ICA_ARTIKEL.DATUM<TO_DATE('''||inDatum||''',''YYYY-MM-DD'') ' ||
									-- 'AND ICA_ARTIKEL.BORTTAGS_FLAGG = 0 ' ||
					                 'ORDER BY DATUM DESC';

									 DBMS_OUTPUT.PUT_LINE(lvSQL);


			 	 		    OPEN c_t_PrisSubstitut FOR
				 		    lvSQL;
			 	 		    FETCH c_t_PrisSubstitut into tmpSubstitutPris,ButikPantBelopp;
							--tmpsubstitutean,tmpSubstitutVarutyp;
				    	    CLOSE c_t_PrisSubstitut;
						    if not ButikPantBelopp  is null then
						  	   tmpSubstitutPris := tmpSubstitutPris - ButikPantBelopp;
						    end if;
							if  SubstitutPris is null then
							   SubstitutPris := tmpSubstitutPris;
							   substitutean := tmpsubstitutean;
							   SubstitutVarutyp :=tmpSubstitutVarutyp;
							elsif tmpSubstitutPris < SubstitutPris then
							   SubstitutPris := tmpSubstitutPris;
							   substitutean := tmpsubstitutean;
							   SubstitutVarutyp :=tmpSubstitutVarutyp;
							end if;
/*


/Nytt från Mikael
Här bör dina förändringar vara klara.
*/
				    	  End Loop;
						  CLOSE c_t_LPKorg;
						end if;
					end if;

					-- All Plu articles have LP = 1 Price is handled above.
					-- Don't handle PLU Price here
                    if varutyp <> 4 then
				       lvSQL := 'SELECT ICA_ARTIKEL.FSGPRIS,ICA_ARTIKEL.PANTBELOPP ' ||
			                    'FROM A'||Avdnr||'.ICA_ARTIKEL@'||LookUpServerName|| ' ' ||
			                    'WHERE ICA_ARTIKEL.EAN='||Ean|| ' ' ||
                                'AND ICA_ARTIKEL.DATUM<TO_DATE('''||inDatum||''',''YYYY-MM-DD'') ' ||
					--'AND ICA_ARTIKEL.BORTTAGS_FLAGG != 1 ' ||
						                'ORDER BY DATUM DESC';
		 	 	       OPEN c_t_Pris FOR
			 	       lvSQL;
			           ButikPris := null;
					   ButikPantBelopp := null;
		 	 	       FETCH c_t_Pris into ButikPris,ButikPantBelopp;
			           CLOSE c_t_Pris;
					   if not ButikPantBelopp is null then
						  	 ButikPris := ButikPris - ButikPantBelopp;
					   end if;
					end if;

					if (ButikPris is null) and (substitutean is null) then
					   ButikPris := null;
					   IcaStatus := 2;
					else
					   IcaStatus := 1;
					end if;

					if lagstapris = 1 then -- check for lowest price
					   if ButikPris is null then
					   	  if Substitutpris is null then
			 	 	   		 IcaStatus := 2;
						  else
						  	 ButikPris := SubstitutPris;
						  end if;
					   elsif SubstitutPris is null then
 		 	 	   		  IcaStatus := 2;
					   else
							if SubstitutPris < ButikPris then
							   Butikpris := SubstitutPris;
						--	   varutyp := Substitutvarutyp;
							end if;
					   end if;
					end if;

					if ButikPris is null then
					   strButikPris := 'NULL';
					else
					   strButikPris := to_char(ButikPris);
					end if;

                    if substitutean is null then
					  -- strsubstitutean := NULL;
					   strsubstitutean := 'NULL';
					else
					   strsubstitutean := to_char(substitutean);
					end if;


					 lvSQL := 'INSERT INTO PBK.PRISREGISTER_kopia_wed(' ||
                      'BUTIKID,' ||
					  'EAN,' ||
					  'PERIOD,' ||
					  'LAGSTAPRIS,' ||
					  'PRIS,' ||
					  'UNDERLAGID,' ||
					  'SUBSTITUT,' ||
					  'KOMMENTAR,' ||
					  'BUTIKTYP,' ||
					  'VARUTYP,' ||
					  'REGICASTATUS,' ||
					  'VARENR,' ||
					  'LEVNR,' ||
					  'VARE_SNR,' ||
					  'BORTTAGS_FLAGG) ' ||
			          'VALUES ('||
				      ButikID||','||        -- BUTIKID
				 	  Ean||','''||          -- EAN
					  Period||''', ' ||     -- PERIOD
					  LagstaPris||','||     -- LAGSTAPRIS
					  strButikPris ||',' ||    -- PRIS
					  inUnderlagid ||',' || -- UNDERLAGSID
					  strsubstitutean || ',''' ||       -- SUBSTITUT (null or LPKORGEANREL.EANREL)
					  --''null', '||          -- KOMMENTAR
					  'null'', '||          -- KOMMENTAR
					  '0' || ' , ' ||        -- BUTIKTYP
					  --'0' || ', ' ||        -- BUTIKTYP
					  varutyp || ', ' ||        -- VARUTYP (0 or according to LPKORGEANREL)
					  IcaStatus||', ' ||     -- REGICASTATUS
					  varenr || ', ' ||     -- VARUNR (original VARENR, not the substitutes)
					  levnr || ', ' ||
					  vare_snr || ', ' ||      -- LEVNR (original LEVNR, not the substitutes)      -- LEVNR (original LEVNR, not the substitutes)
					  borttags_flagg || ')';       -- VARE_SNR (original VARE_SNR, not the substitutes)


DBMS_OUTPUT.PUT_LINE(lvSQL);




					/*


             	    lvSQL := 'INSERT INTO PBK.PRISREGISTER(' ||
                      'BUTIKID,' ||
					  'EAN,' ||
					  'PERIOD,' ||
					  'LAGSTAPRIS,' ||
					  'PRIS,' ||
					  'UNDERLAGID,' ||
					  'SUBSTITUT,' ||
					  'KOMMENTAR,' ||
					  'BUTIKTYP,' ||
					  'VARUTYP,' ||
					  'REGICASTATUS,' ||
					  'VARENR,' ||
					  'LEVNR,' ||
					  'VARE_SNR) ' ||
			          'VALUES ('||
				      ButikID||','||        -- BUTIKID
				 	  Ean||','''||          -- EAN
					  Period||''', ' ||     -- PERIOD
					  LagstaPris||','||     -- LAGSTAPRIS
					  strButikPris ||',' ||    -- PRIS
					  inUnderlagid ||',' || -- UNDERLAGSID
					  strsubstitutean || ', ' ||
				--	  ''' ||       -- SUBSTITUT (null or LPKORGEANREL.EANREL)
					  --''null'', '||          -- KOMMENTAR
					  'null'', '||          -- KOMMENTAR
					  '0' || ', ' ||        -- BUTIKTYP
					  varutyp || ', ' ||        -- VARUTYP (0 or according to LPKORGEANREL)
					  IcaStatus||', ' ||     -- REGICASTATUS
					  varenr || ', ' ||     -- VARUNR (original VARENR, not the substitutes)
					  levnr || ', ' ||      -- LEVNR (original LEVNR, not the substitutes)
					  vare_snr || ')';       -- VARE_SNR (original VARE_SNR, not the substitutes)
*/
--                  DBMS_OUTPUT.PUT_LINE(lvSQL);
 	                EXECUTE IMMEDIATE lvSQL;
               END LOOP;
				COMMIT;
				close c_t_Varu;

--             ELSE
			       -- Write code to tell user that this shop has no data
			 END IF;
			 CLOSE c_t_ButikExist;
		 END LOOP;
		 CLOSE c_t_Butik;
	EXCEPTION
			When OTHERS THEN
    			 v_ErrorCode := SQLCODE;
    			 v_ErrorText := Substr(SQLERRM,1,500);

	        insert into pbk.Error (Err_Code,Err_Text,SQL_TEXT) Values
	                    (V_ErrorCode,V_ErrorText,lvSQL);

	END;
/