For Loop 1

[tjones9034]

Hi Guys,

Can someone tell me what is wrong with this For Loop code. In a nut shell, the code is simply for assigning cards to our clients depending on how many cards they requested for. The problem is they have been complaining that when they click on the "Request For A Card" button, after they have input how many cards they wanted, they get a pop up window "Are you sure you want to assign card(s) to Customer"......immediatelly they click on "YES", they get the same pop up again, when they click on "NO".......the system will go ahead and assign the card anyway, making two cards. Can someone help point to the problem or an add on to the code. I saw no error with the code along with other developers. Is is a matter of adding an "Exit When" to the code before the end loop? Here is the "When Button Pressed Trigger" code below. Thanks.


DECLARE
V_Alert_Button NUMBER;
V_Item VARCHAR2(5000);
V_Max_Card NUMBER;
V_Start_Card NUMBER;
V_ISO VARCHAR2(11);
V_Err_Msg VARCHAR2(2000);
V_Instr NUMBER;
BEGIN
IF :Card_Unassigned.First_Avail_Card_No IS NULL
OR :Card_Unassigned.Seq_Qty_Avail = 0 THEN
Display_Alert('Error_Alert', 'There are no cards available to assign.',V_Alert_Button);
:Card_Unassigned.Start_Card_NO := NULL;
:Card_Unassigned.Qty := NULL;
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :Card_Unassigned.Start_Card_No IS NULL THEN
Display_Alert('Error_Alert', 'Please enter a starting card number.',V_Alert_Button);
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :Card_Unassigned.Qty IS NULL THEN
Display_Alert('Error_Alert', 'Please enter a quantity of cards.',V_Alert_Button);
RAISE FORM_TRIGGER_FAILURE;
END IF;

IF :Card_Unassigned.Qty > :Card_Unassigned.Seq_Qty_Avail THEN
Display_Alert('Error_Alert', 'There are only '||:Card_unassigned.Seq_Qty_Avail||' cards available to assign starting '||
'with card number '||:Card_Unassigned.Start_Card_No||'.',V_Alert_Button);
RAISE FORM_TRIGGER_FAILURE;
END IF;
Check_Pre_Emboss;


SET_ALERT_PROPERTY('Commit_Alert',TITLE,'Commit Alert');
Display_Alert('Commit_Alert', 'Are you sure you want to assign card(s) to Customer '||:Cust.Cust_Id ||' - '||:Cust.Name|| '?',V_Alert_Button);
IF V_Alert_Button = ALERT_BUTTON1 THEN
V_ISO := SUBSTRCard_Unassigned.Start_Card_No, 1,11);
V_Max_Card := TO_NUMBER(SUBSTRCard_Unassigned.Start_Card_No, 12,6)) + Card_Unassigned.Qty -1) ;
V_Start_Card := TO_NUMBER(SUBSTRCard_Unassigned.Start_Card_No,12, 6));
FOR i IN V_Start_Card .. V_Max_Card
LOOP
UPDATE Card_Unassigned
SET Cust_Id = :Cust.Cust_Id, Upd_Date = SYSDATE, Upd_User = arameter.P_Name
WHERE Card_No = V_Iso||LPAD(TO_CHAR(V_Start_Card),6, '0')
AND Lic_Id = arameter.P_Lic_Id
AND Cut_Date IS NOT NULL
AND Cust_Id IS NULL;

V_Start_Card := V_Start_Card + 1;

END LOOP;

IF NOT FORM_FAILURE THEN
/* Show Messages with Severity Level > 5 */
:SYSTEM.MESSAGE_LEVEL := '5';--trap error and suppress
--committing using DML statements
--(INSERT/UPDATE/DELETE)instead of
-- the default Forms default block commit processing.
COMMIT_FORM;

/* Reset Message Level to display all messages */
:SYSTEM.MESSAGE_LEVEL := '0';

Display_Alert('Note_Alert','Cards were successfully assigned to Customer '||:Cust.Cust_Id ||' - '||:Cust.Name|| '.',V_Alert_Button);
GO_ITEM('COntrol.Reset_Button');
EXECUTE_TRIGGER('WHEN-BUTTON-PRESSED');
ELSE
RAISE FORM_TRIGGER_FAILURE;
END IF;

END IF;
EXCEPTION
WHEN FORM_TRIGGER_FAILURE THEN
RAISE;
WHEN OTHERS THEN

/* Strip the additional Oracle error references, if any, from the error message,
display an alert, return failure */
V_Err_Msg := SQLERRM;
V_Instr := INSTR(V_Err_Msg, 'ORA-',2) - 1; --start at 2 and subtract 1 to check for null ORA
-- and to display "ORA-" if not null
IF V_Instr = -1 THEN -- If no ORA then display Message
V_Instr := LEAST(LENGTH(SQLERRM),2000);
END IF;
Display_Alert('Error_Alert',
SUBSTR(V_Err_Msg,1,V_Instr),
V_Alert_Button);
RAISE Form_Trigger_Failure;
END;

 

[lewisp]

I cant see what the problem might be at first glance. What code is in the Display_Alert procedure? Try changing the lines immediately after SET_ALERT_PROPERTY to

[tt]Set_Alert_Property('Commit_Alert',
ALERT_MESSAGE_TEXT,
'Are you sure you want to assign card(s) to Customer '||:Cust.Cust_Id ||' - '||:Cust.Name|| '?');
IF Show_Alert('Commit_Alert') = ALERT_BUTTON1 THEN
[/tt]

and see if that makes any difference.

 

[tjones9034]

Thanks, that worked. I was waiting to see if it goes through in production. The error did not occur anymore.