| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> PL/SQL problems
Hallo,
I would like to know how to solve this problem:
When this procedure runs, which makes an insert into table pbk.prisregister, it inserts the same value in field substitut where there exists a substitut.
Please help me with this?
It should be null in that field in case thre is a substiut for that ean number in the original table.
PROCEDURE RegICAPrice(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
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);
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;
v_ErrorCode number;
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||'''';
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;
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 := true;
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;
lvSQL := 'SELECT
ICA_ARTIKEL.FSGPRIS,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'') ' ||
'ORDER BY DATUM DESC';
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;
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'') ' ||
'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';
else
strsubstitutean := to_char(substitutean);
end if;
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
'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)
insert into pbk.Error (Err_Code,Err_Text,SQL_TEXT) Values
(V_ErrorCode,V_ErrorText,lvSQL);
END;
Thanks in advance
Roland S
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Roland.Skoldblom_at_ica.se Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Nov 29 2001 - 05:05:17 CST
 |
 |