Hello

I have a question, the 3 seperate procedures are all complied fine however --to alter..--
((p.attribute9 is null and p.attribute11 is not null) or
(p.attribute9 is not null and p.attribute11 is null) or
;
This part is not working correctly and my case is:

I have 4 case sencarios, Attribute9 is null and attribute 11 is null, this works fine, attribute 9 is not null and attribute11 is null, likewise attribute 11 is not null and attribute9 is null.. otherwise attribute11 and attribute9 are popluated (4th case), how do i make the above two lines in the first paragraph correct so I have all 4 possibliites, as i only have 2 real possibiltes at the moment?

Could i use a second cursor and if so how do I do this?


CREATE OR REPLACE PROCEDURE UPDATE_BLANK_RENEWAL_DATA AS
CURSOR cur_BLANK IS
SELECT P.PARTY_ID,
p.attribute2,
p.attribute9,
p.attribute11,
p.attribute7,
p.attribute15,
p.attribute6,
p.last_updated_by,
P.LAST_UPDATE_DATE
from hz_cust_accounts ca, hz_parties p
where ca.party_id = p.party_id and p.attribute15 <> 'ARC' and
p.attribute15 is not null and ca.account_number is not null and
((p.attribute9 is null and p.attribute11 is not null) or
(p.attribute9 is not null and p.attribute11 is null) or
(p.attribute9 is null and p.attribute11 is null));
row_count number :=0;
begin
for i in cur_blank loop
if i.attribute2 = 'Inactive' then
update ar.hz_parties
set attribute9 = sysdate,
attribute11 = to_char(sysdate + 6, 'DDMMYYYY'),
last_updated_by='100000001',
LAST_UPDATE_DATE= SYSDATE
where PARTY_ID = i.party_id;
END IF;
if row_count = 1000 then
commit;
row_count :=0;
IF i.attribute2 = 'Lower' then
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute11 = to_char(SYSDATE + 12, 'DDMMYYYY'),
attribute6 = 'RLL',
last_updated_by='100000001',
LAST_UPDATE_DATE= SYSDATE
where PARTY_ID = i.party_id;
end if;
if i.attribute2 = 'Upper' then
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute11 = to_char(SYSDATE + 12, 'DDMMYYYY'),
attribute6 = 'RUU',
last_updated_by='100000001',
LAST_UPDATE_DATE= SYSDATE
where PARTY_ID = i.party_id;
end if;
end loop;
COMMIT;
END UPDATE_BLANK_RENEWAL_DATA;
/

CREATE OR REPLACE PROCEDURE UPDATE_FUTURE_RENEWAL_DATA AS
CURSOR cur_future IS
Select p.party_id,
p.address1,
p.attribute9,
p.attribute2,
p.attribute11,
p.last_updated_by,
P.LAST_UPDATE_DATE
from hz_parties p, hz_cust_accounts ca
where ca.party_id = p.party_id and p.attribute15 <> 'ARC' and
p.attribute15 is not null
or to_date(p.attribute11, 'DDMMYYYY'> sysdate
and ca.account_number is not null
OR
p.address1 is null;

row_count number :=0;
begin
for i in cur_future loop
if (i.attribute2 = 'Lower' or i.attribute2 = 'Upper') then
update ar.hz_parties
set attribute11 = to_date(i.attribute9, 'ddmmyyyy') + 12,
last_updated_by = '100000001',
LAST_UPDATE_DATE = SYSDATE
where PARTY_ID = i.party_id;
end if;
if row_count = 1000 then
commit;
end loop;
Commit;
END UPDATE_FUTURE_RENEWAL_DATA;
/

CREATE OR REPLACE PROCEDURE UPDATE_PAST_RENEWAL_DATA AS
CURSOR cur_past IS
SELECT p.party_id,
p.attribute2,
p.attribute9,
p.attribute11,
p.attribute7,
p.last_updated_by
from hz_cust_accounts ca, hz_parties p
where ca.party_id = p.party_id and p.attribute15 <> 'ARC' and
p.attribute15 is not null and ca.account_number is not null and
to_date(p.attribute9, 'ddmmyyyy') >=
to_date(p.attribute11, 'ddmmyyyy');

row_count number :=0;
begin
for i in cur_past loop
if (i.attribute2 = 'Lower' or i.attribute2 = 'Upper') then
update ar.hz_parties
set attribute11 = to_date(i.attribute9, 'ddmmyyyy') + 12
where PARTY_ID = i.party_id;
end if;
end loop;
if row_count = 1000 then
commit;
COMMIT;
END UPDATE_PAST_RENEWAL_DATA;
/