declare
-- Test statements here
/* ------------------------------------------------------------*/
/* Procedure to update Person / Org without either
a card issue date (ATTRIBUTE9)
a card expiry date (ATTRIBUTE11)

The new card issue date would be set to SYSDATE
The new card expiry date would be set to SYSDATE + 12 months
or SYSDATE + 6 months depends on customer's incentive Level.

Selection C - Not Archived / either Attribute9 or/and Attribute11
IS NULL

Author Version Date Description
------------------------------------------------------------
Emer Ryan (Detica) 0.1 19Mar2004 Initial Version
*/
/* ------------------------------------------------------------*/


CURSOR cur_BLANK IS
SELECT P.PARTY_ID,
ca.account_number,
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.attribute2 IN ('Upper Tier', 'Lower Tier', 'Inactive') and
(p.attribute9 is null OR p.attribute11 is null);

row_count number := 0;
total_rec number := 0;
begin
-- OUPTUT THE START TIME
DBMS_OUTPUT.PUT_LINE('Running - UPDATE_BLANK card issue/ card renewal date');
DBMS_OUTPUT.PUT_LINE('-- Started at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MIS'));
/*enforced cursor to check if customers card issue date and card expiry date is blank when customers
incentive level is Inactive*/
for i in cur_blank loop

/* IF 1 - Inactive Customers */
if i.attribute2 = 'Inactive' then
if i.attribute9 is null and i.attribute11 is null then

/* Update both Card Issue and Card Expiry Dates */
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute11 = to_char(add_months(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

/* Update Card Issue Date */
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

/* Update Card Expiry Dates */
update ar.hz_parties
set attribute11 = to_char(add_months(sysdate,6),
'DDMMYYYY'),
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
end if;

/* IF 1 - Lower Customers */
IF i.attribute2 = 'Lower Tier' then
if i.attribute9 is null and i.attribute11 is null then

/* Update Card Status, Card Issue and Card Expiry Dates */
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute11 = to_char(add_months(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

/* Update Card Issue Date */
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

/* Update Card Statu and Card Expiry Date */
update ar.hz_parties
set attribute11 = to_char(add_months(sysdate,12),
'DDMMYYYY'),
attribute6 = 'RLL',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
end if;
end if;

/* IF 1 - Upper Customers */
if i.attribute2 = 'Upper Tier' then
if i.attribute9 is null and i.attribute11 is null then

/* Update Card Status, Card Issue and Card Expiry Dates */
update ar.hz_parties
set attribute9 = to_char(sysdate, 'DDMMYYYY'),
attribute11 = to_char(add_months(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

/* Update Card Issue Date */
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
update ar.hz_parties
set attribute11 = to_char(add_months(sysdate,12),
'DDMMYYYY'),
attribute6 = 'RUU',
Last_updated_by = '100000001',
LAST_UPDATE_DATE = sysdate
where PARTY_ID = i.party_id;
row_count := row_count + 1;
total_rec := total_rec + 1;
END IF;

/* IF 2 - Commit loop */
if row_count = 1000 then
commit;
row_count := 0;
end if;
/* -- IF 2 - END */
end IF;
/* IF 1 - END */

END LOOP;
COMMIT;

dbms_output.put_line('-- Total rec updated ' || total_rec);
dbms_output.put_line('-- Ended at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MIS'));
-- Standard Exception Handling
-- Handled by call to external procedure

-- EXIT 0;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('ERROR - Data Patch Error ' || 'Code:' || SQLCODE ||
'Error ' || SQLERRM);

-- EXIT 1;

end;
/
--exit '0'
EXIT;