Looping to add new data of system generated number [message #675404] |
Wed, 27 March 2019 20:36  |
 |
an.anis
Messages: 1 Registered: March 2019
|
Junior Member |
|
|
Hi All,
I have a requirement to count for number of addresses maintained for the customer. The number should be Auto-generated by system. It should fill any gap of number that has not been saved in database. As an example i the number saved in database is 1 3 5 6, so the number that should be auto generated are 2 4 7 8 9. These are the code but there is complication.
DECLARE
v_count_addrseq NUMBER(1);
max_addr NUMBER(1):= 9;
BEGIN
IF :system.last_record = 'TRUE' THEN
icba_msg(false,'000200',1);
SELECT COUNT(addrseq)
INTO v_count_addrseq
FROM cf01addr
WHERE addrtyp = '1'
AND cifkey =(SELECT cifkey
FROM cf01cif
WHERE idno = :cf01cif.idno
);
IF v_count_addrseq < max_addr THEN
icba_msg(true,'300000',2,'Do you want add another address?');
IF msg_vb.alert_answer = 1 THEN
icba_msg(false,'300000',2,'addrseq'
|| :addrseq
|| 'V_ADDRSEQ'
|| :global.v_addrseq);
first_record;
LOOP
IF :addrseq = :global.temp THEN
:global.temp := :global.temp + 1;
END IF;
EXIT WHEN :system.last_record = 'TRUE';
next_record;
END LOOP;
IF :global.temp > :addrseq THEN
first_record;
LOOP
EXIT WHEN :system.last_record = 'TRUE';
IF :addrseq = :global.temp THEN
:global.temp := :global.temp + 1;
END IF;
next_record;
END LOOP;
END IF;
IF :global.temp > max_addr THEN
icba_msg(true,300000,1,'Only 9 addresses are allowed to be insert!');
END IF;
create_record;
duplicate_record;
:cf01addr.addrseq := :global.temp;
:cf01addr.addr1 := NULL;
:cf01addr.addr2 := NULL;
:cf01addr.addr3 := NULL;
:cf01addr.postcd := NULL;
:cf01addr.city := NULL;
:cf01addr.statecd := NULL;
:cf01addr.statecd_descr := NULL;
:cf01addr.cntry := NULL;
:cf01addr.cntrycd_descr := NULL;
:cf01addr.corraddr := 'N';
END IF;
END IF;
END IF;
END;
Thank you,
ANIS
[EDITED by LF: fixed formatting, applied [code] tags]
[Updated on: Thu, 28 March 2019 17:04] by Moderator Report message to a moderator
|
|
|
|
|