Home » Developer & Programmer » Forms » Looping to add new data of system generated number (Oracle Form Builder V9)
Looping to add new data of system generated number [message #675404] Wed, 27 March 2019 20:36 Go to next message
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

Re: Looping to add new data of system generated number [message #675406 is a reply to message #675404] Wed, 27 March 2019 20:49 Go to previous messageGo to next message
BlackSwan
Messages: 26703
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

> As an example i the number saved in database is 1 3 5 6,
Where? I don't see where any numbers above are saved in database.

Re: Looping to add new data of system generated number [message #675417 is a reply to message #675406] Thu, 28 March 2019 04:20 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
This is obviously forms code so should be in the forms sub-forum. I've moved it.

It'd help if you described the complication.
Previous Topic: Oracle forms
Next Topic: How to insert CSV Column values into data block using Oracle Forms?
Goto Forum:
  


Current Time: Fri May 29 06:34:28 CDT 2020