I have created 3 Procedures all similar to this one:
I then created 3 shell sripts which will call the sql? finally created a calling script to call the procedure. I am a bit unsure how to this all works, can someone check my code and I am doing this right? Also could I add my procedure (first one to the calling procedure?)
Procedure:
CREATE OR REPLACE PROCEDURE UPDATE_BLANK_RENEWAL_DATA AS
CURSOR cur_BLANK IS
SELECT P.PARTY_ID,
p.attribute2, ---Incentive Level
p.attribute9, ---card Issue date
p.attribute11, ---card renewal date
p.attribute7,
p.attribute15, ---Internal Status
p.attribute6, ---Card Status
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' or
p.attribute15 is null) and ca.account_number is not null and
(p.attribute9 is null and p.attribute11 is null)
or
(p.attribute9 is not null and p.attribute11 is null)
or
(p.attribute9 is null and p.attribute11 is not null)
row_count number :=0;
begin
for i in cur_blank loop
if i.attribute2 = 'Inactive' then
if i.attribute9 is null and i.attribute11 is null then
dbms\_output.put_line\('in inactive'\);
update ar.hz_parties
set attribute9 = to_char
\(sysdate, 'DDMMYYYY'\),
attribute11 = to_char\(sysdate \+ 6, 'DDMMYYYY'\),
last\_updated_by = '100000001',
LAST\_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
if i.attribute9 is null and i.attribute11 is not null then
dbms\_output.put_line\('in inactive n-nn'\);
update ar.hz_parties
set attribute9 = to_char\(sysdate, 'DDMMYYYY'\),
last\_updated_by = '100000001',
LAST\_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
if i.attribute9 is not null and i.attribute11 is null then
dbms\_output.put_line\('in inactive nn-n'\);
update ar.hz_parties
set attribute11 = to\_char\(to_date\(i.attribute9,'DDMMYYYY'\) \+ 6, 'DDMMYYYY'\),
Last\_updated_by = '100000001',
LAST\_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
end if;
IF i.attribute2 = 'Lower Tier' then
if i.attribute9 is null and i.attribute11 is null then
dbms\_output.put_line\('in lower'\);
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.attribute9 is null and i.attribute11 is not null then
dbms_output.put_line('in lower n-nn');
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute6 ='RLL',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
if i.attribute9 is not null and i.attribute11 is null then
dbms_output.put_line('in lower nn-n');
update ar.hz_parties
set attribute11 = to_char(to_date(i.attribute9,'DDMMYYYY') + 12, 'DDMMYYYY'),
attribute6 ='RLL',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
end if;
if i.attribute2 = 'Upper Tier' then
if i.attribute9 is null and i.attribute11 is null then
dbms_output.put_line('in upper');
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;
if i.attribute9 is null and i.attribute11 is not null then
dbms_output.put_line('in upper n-nn');
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute6 ='RUU',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
if i.attribute9 is not null and i.attribute11 is null then
dbms_output.put_line('in upper nn-n');
update ar.hz_parties
set attribute11 = to_char(to_date(i.attribute9,'DDMMYYYY') + 12, 'DDMMYYYY'),
attribute6 ='RUU',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
row_count := row_count + 1;
END IF;
if row_count = 1000 then
commit;
row_count := 0;
end if;
end IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ERROR - Data Patch Error '||'Code:' || SQLCODE || 'Error ' || SQLERRM);
END UPDATE_BLANK_RENEWAL_DATA;
SHell script to call SQL:#******************************************************************************
# Module - T5_Update_blank_cards_data_patch.sh
# Description - Shell script to call SQL script to run Update customers who have no Card issue date or Card Expiry Date set.
#
# -----------------------------------------------------------------------------
#
# Version Author Update date Change Description
# Req No.
# -----------------------------------------------------------------------------
# 0.1 ER 19-MAR-04 Initial Version
#
#*****************************************************************************/
#!/bin/ksh
# Check parameters
if [[ $# -ne 0 ]]
then
echo "USAGE - T5_Update_blank_cards_data_patch.sql"
exit
fi
# SQL Plus - Process file
if [[ -f $ES_PATH/code/other/Data_patches/T5_Update_blank_cards_data_patch.sql ]]
then
sqlplus $ES_DB_STRING @$ES_PATH/code/other/Data_patches/T5_Update_blank_cards_data_patch.sql
EXIT_CODE=$?
else
echo "# ERROR - Script $ES_PATH/code/other/Data_patches/T5_Update_blank_cards_data_patch.sql not f
ound!"
exit 1
fi
### CODE END ###
and finally my Calling Procedure:
spool $ES_LOGS/T4_Update_blank_cards_data_patch.log
Whenever SQLERROR EXIT 1
Whenever OSERROR EXIT 1
-- The data Patch to give customers a renewal date for Customers with Blank CID and CRD
--
-----------------------------------------------------
-- create variable for exception reporting
-----------------------------------------------------
VAR incexe_except NUMBER;
-----------------------------------------------------
DECLARE
lc_success VARCHAR2(1) :=NULL;
BEGIN
T5\_Update\_blank\_cards\(lc_success\);
\---------------------------------------------------------
-- if error returned then set
-- variable for caller to check
\---------------------------------------------------------
END;
/
--*****************
--* END OF PL/SQL *
--*****************
--------------------------------------------------------------
-- Check if Exception record was created, if so, set the
-- exit code accordingly
--------------------------------------------------------------
set feedback off
set feedback on
spool off
exit